Hi Brad, Can you tell me the actual values that you would see in the Sysprocesses column called waittype. I saw one that was displayed as CXWPACKET when looking at current activity with Enterprise Manager on our SQL 2K server. Thanks for any help!
From BOL. Wait Type in EM Indicates the name of the last or current wait type. In my instalation right now, value is Not Waiting. I suggest to use sp_who or sp_who2 to see current activity. Luis Martin Moderator SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
I have used sp_who and sp_who2 and also queried the sysprocesses table directly. What I am looking for is translation of the values that I see in waittype on the sysprocesses table. Looking at current activity via EM I saw a value of CXWPACKET on one row and I am trying to figure out what that means! Thanks! grega
Hi, I guess you mean CXPACKET. This wait type happens (in an SMP environment) when a statement is being processed parallely and one of the thread in this process is waiting for another thread to complete.
True, it means possible skew of data possible lock of a range for this CPU, meaning that one parallel process is behind, etc. The sysprocesses output shows multiple ECs (working on the behalf of a single spid) with some waiting on locks and others waiting on CXPACKET. The pattern is such that no direct EC correlation from either spid results in a standard lock waiter deadlock. Each lock waiter is blocked by an owner waiting on a CXPACKET resource. You can use information gathered from a query of the syslockinfo system table to verify this pattern. Check for parallelism using sp_configure 'max degree of parallelism'. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
This link is for SQL 7.0 waittypes. I haven't been able to find the corresponding SQL 2K list. Do you know a link to the SQL 2K list? Thanks for your help! quote:Originally posted by Argyle List of waittypes: http://support.microsoft.com/default.aspx?scid=KB;EN-US;244455
http://sqldev.net/misc/waittypes.htm for SQL 2K information. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks Chakri, yesterday I was shearching all web and never image it was CX instead CXW. Luis Martin Moderator SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
