Link to home
Start Free TrialLog in
Avatar of Graham Hirst
Graham HirstFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Unidentified blocking problem; server configuration? (SQL 2000, SP3)

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
Avatar of ptjcb
ptjcb
Flag of United States of America image

How large is your temporary file? Is it set to autogrow? In what dimensions?

You mention that you have large (what do you consider large?) tables. Do they have many indexes? Are they clustered?

Your server only has 4GB of RAM? Is this a production server?

Avatar of Graham Hirst

ASKER

The client has configured their tempdb, and our database as follows:

tempdb MDF: Grow by 10%, space allocated 850 MB
tempdb LDF: Grow by 10%, space allocated 83 MB

OurDatabase MDF: Grow by 10%, space allocated 18231 MB
OurDatabase LDF: Grow by 10%, space allocated 985 MB

The large tables aren't huge by MSSQL standards: The largest table has approximately 1.6 million rows, and a datasize of 1090 MB, it is 50 columns wide, with 27 indexes and only the primary key is clustered.

This production server only has 4GB of RAM, but the server still reports 1.5GB free and we haven't seen any obvious Memory bottlenecks through Performance Monitor.
ASKER CERTIFIED SOLUTION
Avatar of ptjcb
ptjcb
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial