May 8, 2007 at 8:35 am
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,
Tom
May 9, 2007 at 4:09 am
Check what the waitresource is for the blocked processes. If it's due to recompiles then the wait resource will read something like TAB 11:1:4525466845 (Compile)
Inserts and updates will take locks even if specified nolock. Nolock can only apply to read locks, not exclusive locks.
If you believe that recompiles are the issue, check perfmon and watch the counters compiles/sec and recompiles/sec. You can also use profiler to catch the recompile events. Make sure you get the subevent class if you do this, it'll show you the reason for the recompile
A parallelism set to 0 will not cause recompiles. 0 means that SQL can use all the processors for parallel if it chooses. If a server can run parallel threads then all queries are compiled with both a parallel and non-parallel query plan.
Most common causes of recompiles are temp tables. Creating a temp table in a stored proc will force a recompile. Referencing a temp table that was creaed in another proc will also force a recompile, as will chances in the number of rows.
Best bet for you right now is to check what the resources the blocking is on. Once you know that, diagnosing the cause is much easier.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2007 at 6:01 am
Thanks for your response Gail; I will review the waitresource shortly and revert accordingly.
We previously monitored the compiles/sec and recompiles/sec counters but didn't see anything untoward, there certainly wasn't any specific fluctuations around this period, but I guess it could well be automatic recompilation in light of the number of rows.
Do you know of any good web resources that explain the waitresource?
I was suggesting that other processes might be forced to wait for the UPDATE statement because of parallelism?
Also, I would be interested to hear your thoughts on our clients Memory and CPU configuration.
Thanks and Kind Regards,
Thomas Fordham
May 9, 2007 at 6:16 am
but I guess it could well be automatic recompilation in light of the number of rows
If it was, you'd see the number of recompiles/sec higher than expected. Automatic or forced recompiles both increase that counter.
Do you know of any good web resources that explain the waitresource
Google SQL wait type. There are a few sites that list the meanings, but I don't know one off hand.
If you see something suspicious, or something you don't understand, post it here.
I was suggesting that other processes might be forced to wait for the UPDATE statement because of parallelism
Other processes will have to wait for an update, whether it is running on one CPU or many. Parallelism should make updates faster, not slower.
As for config, It looks mostly OK. I'd suggest setting the min memory to 2.5 GB and the max to around 3 or 3.5. 3.8 is a little high, the OS does need memory. It you're concerned about massive parallel processes, you can set the max degree of parellelism to 4, but unless you're seeing massive amounts of parallelism I wouldn't worry too much.
Is AWE enabled? Is 3GB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply