SQL Server Performance Forum – Threads Archive

LastWaitType from Sysprocesses

Following on from my thread
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2275
I have located that the problem may be blocking, I have copied an output of the sysprocesses below, and the problem seems to be one SPID (mutliple threads).
This problem always has the same symptoms or manifestations in that the SPID which always causes the blocking problems has mutliple threads. I need to get more info on the LastWaittypes and what they actually mean,
The problem SPID has a lastwaittype of "PWAIT_CXPACKET0x208Waiting on packet synchronize up for exchange operator (parallel query)." Any info on why this SPID will not finish or resolve what the problem is. Snapshot of Sysprocesses is below
18537763220x00038907LCK_M_S KEY: 5:485576768:1 (0f003a32fd43)
18847283220x000D4329LCK_M_RS_S KEY: 5:485576768:1 (0f003a32fd43)
19146043220x000D7907LCK_M_RS_S KEY: 5:485576768:1 (0f003a32fd43)
19647123220x000322907LCK_M_S KEY: 5:485576768:1 (0f003a32fd43)
20245243220x000D25907LCK_M_RS_S KEY: 5:485576768:1 (0f003a32fd43)
20643243220x000325469LCK_M_S KEY: 5:485576768:1 (0f003a32fd43)
208000x00000LCK_M_RS_S KEY: 5:485576768:1 (0f003a32fd43)
21222043220x000D15516LCK_M_RS_S KEY: 5:485576768:1 (0f003a32fd43)
214000x00000NETWORKIO
2176243220x000323516LCK_M_S KEY: 5:485576768:1 (0f003a32fd43)
224278400x0200204EXCHANGE
224302800x02081643219CXPACKET
224462400x02081643235CXPACKET
224390400x02081643204CXPACKET
Anyhelp appreciated

http://support.microsoft.com/default.aspx?scidhttp://support.microsoft.com:80/support/kb/articles/Q244/4/55.ASP&NoWebContent=1 check it out. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Have read this already, thanks for help. I understand what the problem is, but still dont know whats causing it, Any suggestions on how to actual resolve the problem now would be gratfully accepted.

Take help of these KBAs: http://support.microsoft.com/default.aspx?scid=kb;EN-US;251004</a> – monitor SQL 7 blocking http://support.microsoft.com/default.aspx?scid=kb;EN-US;271509</a> – monitor SQL 2K blocking If many connections are simultaneously running the same stored procedure, and a compile lock must be acquired for that stored procedure each time it is run, it is possible that system process IDs may begin to block each other as they each try to acquire an exclusive compile lock on the object. The lastwaittype for the blocked and (usually) blocking SPIDs is LCK_M_X (exclusive) and waitresource is of the form "TAB: dbid<img src=’/community/emoticons/emotion-3.gif’ alt=’ :eek:

‘ />bject_id [[COMPILE]]," where "object_id" is the object ID of the stored procedure. How often you recompile your stored procedures? _________ Satya SKJ Moderator SQL-Server-Performance.Com

Its not a recompile of the proc I had considered that option, I had read the article you suggested and had checked the actual waitresource and its not recompiling the Proc.
Thanks again, this is where I had got to and now I am stumped, hopefully you will have a third suggestion. Thanks for you help.

Take help of above KBAs to resolve the blocking problem.
BTW what is H/w used, SQL settings for memory and RAM available? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

SQL CLustered Box is 4 GB memory, 8 CPU, advanced 2000 server. I think the Threads of the SPID in question are getting deadlocked on resources and are not releasing, we have a guy in from Microsoft, so will know the answer soon hopefully.
Will post the solution – if he gets one. Thanks for your help Satya.

This could be a bug/problem due to parallelism. Have you teseted by setting the parallelism option to use 1 processor and not "all available processors". Note that this doesn’t mean you will only use one CPU on the box and seven will be idle, just that each individual query will only use one CPU. I would test this to see if the problem is due to parallelism. /Argyle

Hello to all. After spending most of the day with Microsoft, we have identified the problem (as suggested by many of you) as an issue with Parallelism brought to light by a slow stored procedure.
They are taking away our dump logs and are going to come back with the actual behind the scenes solution, but in the short term we will set the query hint for parallelism to use 1 proc per call rather than all available processors. Will post full details tomorrow of problem and resolution. Thanks for all help.

Microsoft have now analysed the log files, dumps and results from sp_who and sysprocesses and said, "yes, its a problem with parallelism, some hanging threads dont recover and the SPID then will continue to block until user action"
Their solution is to disable parallelism if you see problems happening. Thanks for all your help…again.

]]>