Yesterday we were facing a severe performance issue on our SQL Server. We have a table in which one of our scribe DTS package pushes data before inserting it into MS-CRM. For some unknown reason the data was not getting inserted (Investigation on this issue would be posted in a different blog shortly). While investigating the issue, we were required to delete the data in the table so as to start with new set of data.
But somehow the data was not getting deleted. We even tried Truncating the table, but were unable to get through. However the system was not giving any error. This made us think that there is some sort of Deadlock being created on the table. Some process has lock on the data and is not releasing it.
On using the “sp_who2” command on the SQL editor, we found there are more than 200 processes being created by scribe. We thought that these processes were the reason why the performance was getting hampered. So many processes trying to access the same resource is bound create performance issue. We were also concerned whether the number of connections would go on increasing every time the scheduled package is run. Since these processes would keep on consuming precious memory and eventually exhausting all the resources and leading to memory leaks.
On further investigation we found out that scribe creates these processes to make sure memory is cached and is available to respond quickly to an actual process request. Most of these processes were in sleeping mode and were waiting for commands. One more interesting observation was that all of these sleeping processes were pointing to the ScribeInternal database. There were few other processes which were pointing to our custom databases and few of them were suspended, because of the deadlock issue. The system started working again after manually killing these processes.
The scribe processes only awakes when there is certain task to be done and hence do not consume any additional resources. These processes are being monitored by the Scribe Monitor Service.
Yogesh Lotlikar's Blog 