One of our clients has been experiencing intermittent periods of slowness using our application, and we have identified a number of processes in the SYSPROCESSES table which report a blocking process. We have been querying the SYSPROCESSES table at 1 minute intervals, and recording the executing SQL statement of a blocking process via the fn_get_sql(sql_handle) function as follows:
DECLARE @HANDLE BINARY(20)
SET @HANDLE = SELECT [SQL_HANDLE] FROM MASTER..SYSPROCESSES WHERE SPID = {Blocking SPID}
SELECT [TEXT] FROM ::FN_GET_SQL(@HANDLE)
Note: We have only been recording the SQL statement of the blocking process where the COUNT(BLOCKED) > 10 and WAITTIME > 10000 MS
In the first instance, we had hoped to identify a single statement which was causing a Row, Page or Tablelock forcing other SPIDs to be blocked during its execution, but have recorded a range of statements including UPDATEs, INSERTs and SELECTs, some of which include the NOLOCK query optimiser hint? In all of the aforementioned cases the blocks are never permanently held and can last anything from our 10 second threshold up to 7 minutes!
Performance Monitor hasn't highlighted any obvious Processor, Physical Disk or Memory bottlenecks on the SQL Server at the time of the fault, and the server is subject to a nightly index rebuild.
Whilst it's not ideal, several of our applications key 'large tables' include an INSERT, UPDATE, DELETE TRIGGER which inserts the ColumnName, OldValue and NewValue into a separate table for manipulation; Subject to the width of the table, this trigger may insert up to 97 rows. A simple self-update to this table via SQL Query Analyser can take up to 30 seconds until it has been cached, thereafter it takes approximately 2 seconds. My feeling is that common statements are falling out of the SYSCACHEOBJECTS table and forcing other statements to wait during their recompilation... Possibly because of Parallelism, which is set to 0?
We don't pretend to be MSSQL experts, but have been unable to repeat the problem in our environment or identify bottlenecks during the fault; therefore we believe the clients SQL Server Configuration to be at fault and would appreciate your thoughts, suggestions, experiences and opinions.
Server Specification as follows:
Microsoft SQL Server 2000 Enterprise Edition SP3 w/ Cumulative Patch MS03-031 (8.00.818), clustered on 2 x Compaq ProLiant DL580 G2's:
Microsoft Windows 2000 Advanced Server 5.0.2195 Service Pack 4 Build 2195
8 x x86 Family 15 Model 2 Stepping 5 GenuineIntel ~2799 Mhz
4GB RAM
SQL Server is set to use memory dynamically, Minimum (MB): 0, Maximum (MB): 3808, Use Processors: [0-7], Boost SQL Server Priority: 0, Max Worker Threads: 255, Parallelism: 0, Parallelism Threshold: 5.
Thanks and Kind Regards,
FSI FM
Start Free Trial
