SQL Server Performance

waittype

Discussion in 'Performance Tuning for DBAs' started by grega, Apr 27, 2004.

  1. grega New Member

    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!

  2. Luis Martin Moderator

    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.
  3. grega New Member


    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
  4. ykchakri New Member

    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.
  5. satya Moderator

    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.
  6. Argyle New Member

  7. grega New Member

  8. satya Moderator

    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.
  9. Luis Martin Moderator

    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.

Share This Page