Top 10 Wait State Issues Reporter

  • Rudy Panigas

    SSChampion

    Points: 10695

    Comments posted to this topic are about the item Top 10 Wait State Issues Reporter

    Rudy

  • Seed Vicious

    SSC Veteran

    Points: 297

    Hi Rudy, excelent script!

    but, you have a little bug at the en of the second comment... /* instead */ ;P

  • Rudy Panigas

    SSChampion

    Points: 10695

    Thanks for the comments! 🙂

    Could please tell me where the problem is? i don't see the problem.

    Thanks,

    Rudy

    Rudy

  • Rudy Panigas

    SSChampion

    Points: 10695

    Hello again,

    I found the problem. Change the /* to */ on the second comment at the end of the line. If not the complete code will be commented out.

    Sorry for the type-o.

    Thanks,

    Rudy

    Rudy

  • Arthur Kirchner

    Ten Centuries

    Points: 1046

    I am also delighted, thanks a lot!

    here a little correction

    'LOCK - Waiting to acquire a Schema Modify lock' WHERE dbo.[#results].[wait_type] = 'LCK_M_SCH_S'

    ... should be Share.

  • Rudy Panigas

    SSChampion

    Points: 10695

    Arthur Kirchner (11/22/2012)


    I am also delighted, thanks a lot!

    here a little correction

    'LOCK - Waiting to acquire a Schema Modify lock' WHERE dbo.[#results].[wait_type] = 'LCK_M_SCH_S'

    ... should be Share.

    Thanks for looking at the script. The LCK_M_SCH_S section is MODIFY not a SHARE as per Microsoft http://support.microsoft.com/kb/822101 The "_M_" stands for MODIFY.

    Please let me know where you have found your information from.

    Again thanks for your help,

    Rudy

    Rudy

  • Arthur Kirchner

    Ten Centuries

    Points: 1046

    I am sorry,

    I trusted Pinal Dave on this one, without crosschecking MS:

    http://blog.sqlauthority.com/2011/02/15/sql-server-lck_m_xxx-wait-type-day-15-of-28/

    who wrote

    "LCK_M_SCH_S - Occurs when a task is waiting to acquire a Schema Share lock."

    an correctly it's

    SCH_S <=> Schema stability

    SCH_M <=> Schema modification

  • Rudy Panigas

    SSChampion

    Points: 10695

    Arthur Kirchner (11/22/2012)


    I am sorry,

    I trusted Pinal Dave on this one, without crosschecking MS:

    http://blog.sqlauthority.com/2011/02/15/sql-server-lck_m_xxx-wait-type-day-15-of-28/

    who wrote

    "LCK_M_SCH_S - Occurs when a task is waiting to acquire a Schema Share lock."

    an correctly it's

    SCH_S <=> Schema stability

    SCH_M <=> Schema modification

    Hi Arthur,

    The reason I wrote this script is hopefully clearify the meaning of the wait stats as it can be quite confussing. So I just looked at Microsoft for answers but they too have has some errors in their discriptions too. So you (and Dave) could be right. If you find more information on this, please let me know as I want this script to help DBAs not confuse them.

    Really don't understand why this topic has to be so confusing. :hehe:

    Big thanks!

    Rudy

    Rudy

  • Caruncles

    Hall of Fame

    Points: 3285

    I'm not in the same league with you guys as far as what this all means. I apologize for any dumb questions, but is this reporting CURRENT activities or is it a history? If a history, over what time period?

    Wallace Houston
    Sunnyland Farms, Inc.

    "We must endeavor to persevere."

  • Rudy Panigas

    SSChampion

    Points: 10695

    Caruncles (11/22/2012)


    I'm not in the same league with you guys as far as what this all means. I apologize for any dumb questions, but is this reporting CURRENT activities or is it a history? If a history, over what time period?

    Hey no apologies required!! We are all here to learn and help when we can 🙂

    As for reporting (current or historial), see below as per Microsoft (http://msdn.microsoft.com/en-us/library/ms179984(v=sql.105).aspx)

    "Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches"

    But threads had to have executed so in a sense it is historical.

    Hope this help,

    Rudy

    PS. Please continue to ask question regardless as we all learn from them 🙂

    Rudy

  • Caruncles

    Hall of Fame

    Points: 3285

    Thanx Rudy! I see this as being helpful for us. I've been troubleshooting some timing-out errors lately. We're in our peak sales period so our system is getting hammered.

    Wallace Houston
    Sunnyland Farms, Inc.

    "We must endeavor to persevere."

  • Tony Trus

    SSChasing Mays

    Points: 654

    Thanks for the script. I had been using Glenn Berry's one from a year ago and I like how this one does some nice data manipulation so its neat and organized with descriptions.

    On a side note, anybody know the best way to keep formatting when doing a copy of the code from this site? When I select this wait script and paste it into notepad locally (don't have sql installed on my family machine) the formatting turns to jibberish. My current workaround is to first paste to Word and then copy and paste to notepad and that resolves. Sure would like an easier route. Cheers and happy Thanksgiving to all the folks here in USA

  • Rudy Panigas

    SSChampion

    Points: 10695

    Tony Trus (11/22/2012)


    Thanks for the script. I had been using Glenn Berry's one from a year ago and I like how this one does some nice data manipulation so its neat and organized with descriptions.

    On a side note, anybody know the best way to keep formatting when doing a copy of the code from this site? When I select this wait script and paste it into notepad locally (don't have sql installed on my family machine) the formatting turns to jibberish. My current workaround is to first paste to Word and then copy and paste to notepad and that resolves. Sure would like an easier route. Cheers and happy Thanksgiving to all the folks here in USA

    Thanks for the comments.

    As for the copy /paste issues, try using a different web browser.

    Rudy

  • Tony Trus

    SSChasing Mays

    Points: 654

    Thanks, Chrome was able to not clobber formatted so I used that instead of IE.

    Something else I just noticed. I get quite different results from Glenn's version versus this. I know the metrics are measured differently (Glenn's is in percentage) however it did make me wonder. For what its worth I have not dug into the logic. His code was a bit shorter and looked like this:

    WITH Waits AS

    (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,

    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

    FROM sys.dm_os_wait_stats

    WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'

    ,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'

    ,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'

    ,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'

    ,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))

    SELECT W1.wait_type,

    CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

    CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

    CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

    FROM Waits AS W1

    INNER JOIN Waits AS W2

    ON W2.rn <= W1.rn

    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

    HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

  • Rudy Panigas

    SSChampion

    Points: 10695

    Hi Tony,

    The code you are showing will produce percentages. My code shows information directly from SQL server along with the comments.

    I didn't change the numbers to percentage yet but could be included in my next version.

    Thanks,

    Rudy

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply