SQLServerCentral / Discuss content posted by Rudy Panigas / Article Discussions / Article Discussions by Author / Top 10 Wait State Issues Reporter / Latest Posts

RE: Top 10 Wait State Issues Reporter

nickm_GR — Wed, 28 Nov 2012 07:08:43 GMT

Tony Trus; Ah, thanks! I tried that and those two extra results disappeared. It makes sense that they are dumped into that variable. Thanks for providing the example!Nick

RE: Top 10 Wait State Issues Reporter

nickm_GR — Wed, 28 Nov 2012 06:52:48 GMT

Thanks, that was what I was wondering. I didn't know if it meant something important, like "these are the two wait states that are causing the biggest problem" or something. I'll just refer to the table.ThanksNick

RE: Top 10 Wait State Issues Reporter

Tony Trus — Tue, 27 Nov 2012 13:21:32 GMT

I know that there is newer code but I wanted to help the person who asked about why there are the two straggler records. It comes from the fetch statement not being directed to a declared variable.To see what affect this has try doing the following as a learning experience:Add this line just below where it says "set nocount on"DECLARE @blah varchar(50)Next, add this to the end of the two fetch statements:into @blahNow @blah holds the unneeded value instead of the screen That was a great question to ask!Tony Trus

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Tue, 27 Nov 2012 13:05:37 GMT

I agree, the newer code is displaying the data much better than my code did. My apologies. Hats off to you all!!! :-)Thanks,Rudy

RE: Top 10 Wait State Issues Reporter

ahperez — Tue, 27 Nov 2012 12:51:03 GMT

[quote][b]nickm_GR (11/27/2012)[/b][hr]I'm trying to understand the first two sets of results and where they come from in the code. You'll notice I get three result sets back, where do the first two "wait_type" come from and what is their significance?[img]https://dl.dropbox.com/u/5792250/results.png[/img]Thanks!Nick[/quote]Hi Nick,I got those too with the original version of the script. I am not familiar with cursors and perhaps it is some artifact of that or a typo. When I ran the code as re-written by either Greg Drake or Cadavre I don't get those extra results, just the nicely formatted table.

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Tue, 27 Nov 2012 08:56:06 GMT

Excellent Tony. Thanks for the information.Rudy

RE: Top 10 Wait State Issues Reporter

Tony Trus — Tue, 27 Nov 2012 08:48:02 GMT

Many of us already know how to reset counters on the wait states however I saw a few people posting who are newer at this and want to know how they can determine wait states without it being cumulative since sql start. The command to reset the wait statistics is as follows:DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);As with every other DBCC command, consider the ramifications before executing it and clear only if the benefits outweigh the drawbacks. If for example I had a customer that had always observed sluggishness between 1:30-2:30pm and they wanted to know what the system was waiting on only for that particular hour then clearing the wait stats might make sense. Unfortunately, clearing any of the dm counters means that you could potentially lose a long period of aggregated metrics that might show a more well rounded picture of system since it was built over a long period of time. While most folks here already know this I am hoping this has helped at least one personTony

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Tue, 27 Nov 2012 08:47:51 GMT

[quote][b]nickm_GR (11/27/2012)[/b][hr]I'm trying to understand the first two sets of results and where they come from in the code. You'll notice I get three result sets back, where do the first two "wait_type" come from and what is their significance?[img]https://dl.dropbox.com/u/5792250/results.png[/img]Thanks!Nick[/quote]Hi Nick,You didn't include the results on your post so I can't help there. As for what this all means? The code was written to provide some direction not as a complete diagnostic tool. You still need to do your internet searches and/or contact MS support. But at least you will have the top 10 issues that you can show to others and/or MS support so that you can get some help.i'm sure that's not what your looking for here but I can't help diagnose your system without a whole lot more information and time.Thanks,Rudy

RE: Top 10 Wait State Issues Reporter

nickm_GR — Tue, 27 Nov 2012 08:28:27 GMT

I'm trying to understand the first two sets of results and where they come from in the code. You'll notice I get three result sets back, where do the first two "wait_type" come from and what is their significance?[img]https://dl.dropbox.com/u/5792250/results.png[/img]Thanks!Nick

RE: Top 10 Wait State Issues Reporter

Greg Drake — Mon, 26 Nov 2012 12:43:09 GMT

[quote][b]Cadavre (11/26/2012)[/b][hr]Why do you need the temporary tables?[/quote]Good one Cadavre! Your derived tables got me thinking about it some more. I took out that big constant scan with all the UNIONs I used before and turned it into a CASE statement. That removed the need for a JOIN.[code="sql"];WITH sdows AS ( SELECT wait_type ,waiting_tasks_count ,wait_time_ms ,max_wait_time_ms ,signal_wait_time_ms ,'comment' = CASE WHEN wait_type = 'SOS_SCHEDULER_YIELD' THEN 'CPU - Execute this script: SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255 ;If runnable tasks count > zero, CPU issues if double digits for any length of time, extreme CPU concern' WHEN wait_type = 'CXPACKET' THEN 'SETTINGS OR CODE - Wait stats shows more than 5% of your waits are on CXPackets, you may want to test lower (or non-zero) values of “max degree of parallelism”. Never set value great than # of CPUs' WHEN wait_type = 'ASYNC_NETWORK_IO' THEN 'NETWORK - Occurs on network writes when the task is blocked behind the network' WHEN wait_type = 'LCK_M_BU' THEN 'LOCK - Waiting to acquire a Bulk Update (BU) lock' WHEN wait_type = 'LCK_M_IS' THEN 'LOCK - Waiting to acquire an Intent Shared (IS) lock' WHEN wait_type = 'LCK_M_IU' THEN 'LOCK - Waiting to acquire an Intent Update (IU) lock ' WHEN wait_type = 'LCK_M_IX' THEN 'LOCK - Waiting to acquire an Intent Exclusive (IX) lock' WHEN wait_type = 'LCK_M_RIn_NL' THEN 'LOCK - Waiting to acquire a NULL lock on the current key value and an Insert Range lock between the current and previous key' WHEN wait_type = 'LCK_M_RIn_S' THEN 'LOCK - Waiting to acquire a shared lock on the current key value and an Insert Range lock between the current and previous key' WHEN wait_type = 'LCK_M_RIn_U' THEN 'LOCK - Waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key' WHEN wait_type = 'LCK_M_RIn_X' THEN 'LOCK - Waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key' WHEN wait_type = 'LCK_M_RS_S' THEN 'LOCK - Waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous' WHEN wait_type = 'LCK_M_RS_U' THEN 'LOCK - Waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key' WHEN wait_type = 'LCK_M_RX_S' THEN 'LOCK - Waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key' WHEN wait_type = 'LCK_M_RX_U' THEN 'LOCK - Waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key' WHEN wait_type = 'LCK_M_RX_X' THEN 'LOCK - Waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key' WHEN wait_type = 'LCK_M_S' THEN 'LOCK - Waiting to acquire a Shared lock' WHEN wait_type = 'LCK_M_SCH_M' THEN 'LOCK - Waiting to acquire a Schema Modify lock' WHEN wait_type = 'LCK_M_SCH_S' THEN 'LOCK - Waiting to acquire a Schema Modify lock' WHEN wait_type = 'LCK_M_SIU' THEN 'LOCK - Waiting to acquire a Shared With Intent Update lock' WHEN wait_type = 'LCK_M_SIX' THEN 'LOCK - Waiting to acquire a Shared With Intent Exclusive lock' WHEN wait_type = 'LCK_M_U' THEN 'LOCK - Waiting to acquire an Update lock' WHEN wait_type = 'LCK_M_UIX' THEN 'LOCK - Waiting to acquire an Update With Intent Exclusive lock' WHEN wait_type = 'LCK_M_X' THEN 'LOCK - Waiting to acquire an Exclusive lock' WHEN wait_type = 'LATCH_DT' THEN 'LOCK - Waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches' WHEN wait_type = 'LATCH_EX' THEN 'LOCK - Waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches' WHEN wait_type = 'LATCH_KP' THEN 'LOCK - Waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches' WHEN wait_type = 'LATCH_SH' THEN 'LOCK - Waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches' WHEN wait_type = 'LATCH_UP' THEN 'LOCK - Waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches' WHEN wait_type = 'RESOURCE_SEMAPHORE' THEN 'MEMORY - Query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts' WHEN wait_type = 'RESOURCE_SEMAPHORE_MUTEX' THEN 'MEMORY - Query waits for its request for a thread reservation to be fulfilled. It also occurs when synchronizing query compile and memory grant requests' WHEN wait_type = 'RESOURCE_SEMAPHORE_QUERY_COMPILE' THEN 'MEMORY - Number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans' WHEN wait_type = 'RESOURCE_SEMAPHORE_SMALL_QUERY' THEN 'MEMORY - Memory request by a small query cannot be granted immediately due to other concurrent queries. Wait time should not exceed more than a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries' WHEN wait_type = 'WRITELOG' THEN 'MEMORY - Waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits' WHEN wait_type = 'PAGEIOLATCH_DT' THEN 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits may indicate problems with the disk subsystem' WHEN wait_type = 'PAGEIOLATCH_EX' THEN 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem' WHEN wait_type = 'PAGEIOLATCH_KP' THEN 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits may indicate problems with the disk subsystem' WHEN wait_type = 'PAGEIOLATCH_SH' THEN 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Share mode. Long waits may indicate problems with the disk subsystem' WHEN wait_type = 'PAGEIOLATCH_UP' THEN 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits may indicate problems with the disk subsystem' WHEN wait_type = 'PAGELATCH_DT' THEN 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode' WHEN wait_type = 'PAGELATCH_EX' THEN 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode' WHEN wait_type = 'PAGELATCH_KP' THEN 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode' WHEN wait_type = 'PAGELATCH_SH' THEN 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode' WHEN wait_type = 'PAGELATCH_UP' THEN 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode' WHEN wait_type = 'LOGBUFFER' THEN 'DISK - Waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server' WHEN wait_type = 'ASYNC_IO_COMPLETION' THEN 'DISK - Waiting for I/Os to finish' WHEN wait_type = 'IO_COMPLETION' THEN 'DISK - Waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits' ELSE NULL END FROM sys.dm_os_wait_stats WHERE waiting_tasks_count <> 0)SELECT TOP 10 'OS WAIT STATS - WAIT TYPE' = wait_type ,'TASKS WAITING COUNT' = waiting_tasks_count ,'TIME WAITING (MS)' = wait_time_ms ,'MAX TIME WAITING (MS)' = max_wait_time_ms ,'SIGNAL TIME WAITING (MS)' = signal_wait_time_ms ,'POSSIBLE ISSUES' = commentFROM sdowsWHERE comment IS NOT NULLORDER BY wait_time_ms DESC[/code][quote][b]Rudy Panigas (11/26/2012)[/b][hr]As we all know there are more than one way to skin SQL server :-D[/quote]Ain't that the truth, Rudy.

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Mon, 26 Nov 2012 10:52:36 GMT

Thanks Greg for the updated code. As we all know there are more than one way to skin SQL server :-DRudy

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Mon, 26 Nov 2012 10:50:22 GMT

The temp tables are used to add the possible issues to the results. :-)

RE: Top 10 Wait State Issues Reporter

ahperez — Mon, 26 Nov 2012 10:20:38 GMT

Elegant, reduced the logical reads needed to complete the query.

RE: Top 10 Wait State Issues Reporter

Cadavre — Mon, 26 Nov 2012 09:47:15 GMT

Why do you need the temporary tables?[code="sql"]SELECT TOP 10[OS WAIT STATS - WAIT TYPE] = a.wait_type,[TASKS WAITING COUNT] = a.waiting_tasks_count, [TIME WAITING (MS)] = a.wait_time_ms,[MAX TIME WAITING (MS)] = a.max_wait_time_ms, [SIGNAL TIME WAITING (MS)] = a.signal_wait_time_ms,[POSSIBLE ISSUES] = b.commentFROM (SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms FROM sys.dm_os_wait_stats WHERE waiting_tasks_count <> 0 ) aINNER JOIN (SELECT wait_type, comment FROM (SELECT 'SOS_SCHEDULER_YIELD', 'CPU - Execute this script: SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255 ;If runnable tasks count > zero, CPU issues if double digits for any length of time, extreme CPU concern' UNION ALL SELECT 'CXPACKET', 'SETTINGS OR CODE - Wait stats shows more than 5% of your waits are on CXPackets, you may want to test lower (or non-zero) values of “max degree of parallelism”. Never set value great than # of CPUs' UNION ALL SELECT 'ASYNC_NETWORK_IO', 'NETWORK - Occurs on network writes when the task is blocked behind the network' UNION ALL SELECT 'LCK_M_BU', 'LOCK - Waiting to acquire a Bulk Update (BU) lock' UNION ALL SELECT 'LCK_M_IS', 'LOCK - Waiting to acquire an Intent Shared (IS) lock' UNION ALL SELECT 'LCK_M_IU', 'LOCK - Waiting to acquire an Intent Update (IU) lock ' UNION ALL SELECT 'LCK_M_IX', 'LOCK - Waiting to acquire an Intent Exclusive (IX) lock' UNION ALL SELECT 'LCK_M_RIn_NL', 'LOCK - Waiting to acquire a NULL lock on the current key value and an Insert Range lock between the current and previous key' UNION ALL SELECT 'LCK_M_RIn_S', 'LOCK - Waiting to acquire a shared lock on the current key value and an Insert Range lock between the current and previous key' UNION ALL SELECT 'LCK_M_RIn_U', 'LOCK - Waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key' UNION ALL SELECT 'LCK_M_RIn_X', 'LOCK - Waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key' UNION ALL SELECT 'LCK_M_RS_S', 'LOCK - Waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous' UNION ALL SELECT 'LCK_M_RS_U', 'LOCK - Waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key' UNION ALL SELECT 'LCK_M_RX_S', 'LOCK - Waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key' UNION ALL SELECT 'LCK_M_RX_U', 'LOCK - Waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key' UNION ALL SELECT 'LCK_M_RX_X', 'LOCK - Waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key' UNION ALL SELECT 'LCK_M_S', 'LOCK - Waiting to acquire a Shared lock' UNION ALL SELECT 'LCK_M_SCH_M', 'LOCK - Waiting to acquire a Schema Modify lock' UNION ALL SELECT 'LCK_M_SCH_S', 'LOCK - Waiting to acquire a Schema Modify lock' UNION ALL SELECT 'LCK_M_SIU', 'LOCK - Waiting to acquire a Shared With Intent Update lock' UNION ALL SELECT 'LCK_M_SIX', 'LOCK - Waiting to acquire a Shared With Intent Exclusive lock' UNION ALL SELECT 'LCK_M_U', 'LOCK - Waiting to acquire an Update lock' UNION ALL SELECT 'LCK_M_UIX', 'LOCK - Waiting to acquire an Update With Intent Exclusive lock' UNION ALL SELECT 'LCK_M_X', 'LOCK - Waiting to acquire an Exclusive lock' UNION ALL SELECT 'LATCH_DT', 'LOCK - Waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches' UNION ALL SELECT 'LATCH_EX', 'LOCK - Waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches' UNION ALL SELECT 'LATCH_KP', 'LOCK - Waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches' UNION ALL SELECT 'LATCH_SH', 'LOCK - Waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches' UNION ALL SELECT 'LATCH_UP', 'LOCK - Waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches' UNION ALL SELECT 'RESOURCE_SEMAPHORE', 'MEMORY - Query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts' UNION ALL SELECT 'RESOURCE_SEMAPHORE_MUTEX', 'MEMORY - Query waits for its request for a thread reservation to be fulfilled. It also occurs when synchronizing query compile and memory grant requests' UNION ALL SELECT 'RESOURCE_SEMAPHORE_QUERY_COMPILE', 'MEMORY - Number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans' UNION ALL SELECT 'RESOURCE_SEMAPHORE_SMALL_QUERY', 'MEMORY - Memory request by a small query cannot be granted immediately due to other concurrent queries. Wait time should not exceed more than a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries' UNION ALL SELECT 'WRITELOG', 'MEMORY - Waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits' UNION ALL SELECT 'PAGEIOLATCH_DT', 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits may indicate problems with the disk subsystem' UNION ALL SELECT 'PAGEIOLATCH_EX', 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem' UNION ALL SELECT 'PAGEIOLATCH_KP', 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits may indicate problems with the disk subsystem' UNION ALL SELECT 'PAGEIOLATCH_SH', 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Share mode. Long waits may indicate problems with the disk subsystem' UNION ALL SELECT 'PAGEIOLATCH_UP', 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits may indicate problems with the disk subsystem' UNION ALL SELECT 'PAGELATCH_DT', 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode' UNION ALL SELECT 'PAGELATCH_EX', 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode' UNION ALL SELECT 'PAGELATCH_KP', 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode' UNION ALL SELECT 'PAGELATCH_SH', 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode' UNION ALL SELECT 'PAGELATCH_UP', 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode' UNION ALL SELECT 'LOGBUFFER', 'DISK - Waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server' UNION ALL SELECT 'ASYNC_IO_COMPLETION', 'DISK - Waiting for I/Os to finish' UNION ALL SELECT 'IO_COMPLETION', 'DISK - Waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits' ) b(wait_type, comment) ) b ON a.wait_type = b.wait_typeORDER BY a.wait_time_ms DESC;[/code]

RE: Top 10 Wait State Issues Reporter

Greg Drake — Mon, 26 Nov 2012 09:27:36 GMT

I'm not sure why I got carried away with this, but I modified your script as shown below. It should provide the same results. The main changes were to take out the cursor and all those UPDATE statements. Instead of that I created another temp table to join against.[code]USE MASTERSET NOCOUNT ONCREATE TABLE #wait_stats ( wait_type nvarchar(60) NOT NULL ,waiting_tasks_count bigint NOT NULL ,wait_time_ms bigint NOT NULL ,max_wait_time_ms bigint NOT NULL ,signal_wait_time_ms bigint NOT NULL)CREATE TABLE #wait_type ( wait_type nvarchar(60) NOT NULL ,comment varchar(max) NOT NULL)INSERT INTO #wait_stats ( wait_type ,waiting_tasks_count ,wait_time_ms ,max_wait_time_ms ,signal_wait_time_ms)SELECT wait_type ,waiting_tasks_count ,wait_time_ms ,max_wait_time_ms ,signal_wait_time_msFROM sys.dm_os_wait_statsWHERE waiting_tasks_count <> 0INSERT INTO #wait_type ( wait_type ,comment)SELECT 'SOS_SCHEDULER_YIELD' , 'CPU - Execute this script: SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255 ;If runnable tasks count > zero, CPU issues if double digits for any length of time, extreme CPU concern'UNION ALL SELECT 'CXPACKET' , 'SETTINGS OR CODE - Wait stats shows more than 5% of your waits are on CXPackets, you may want to test lower (or non-zero) values of “max degree of parallelism”. Never set value great than # of CPUs'UNION ALL SELECT 'ASYNC_NETWORK_IO' , 'NETWORK - Occurs on network writes when the task is blocked behind the network'UNION ALL SELECT 'LCK_M_BU' , 'LOCK - Waiting to acquire a Bulk Update (BU) lock'UNION ALL SELECT 'LCK_M_IS' , 'LOCK - Waiting to acquire an Intent Shared (IS) lock'UNION ALL SELECT 'LCK_M_IU' , 'LOCK - Waiting to acquire an Intent Update (IU) lock 'UNION ALL SELECT 'LCK_M_IX' , 'LOCK - Waiting to acquire an Intent Exclusive (IX) lock'UNION ALL SELECT 'LCK_M_RIn_NL' , 'LOCK - Waiting to acquire a NULL lock on the current key value and an Insert Range lock between the current and previous key'UNION ALL SELECT 'LCK_M_RIn_S' , 'LOCK - Waiting to acquire a shared lock on the current key value and an Insert Range lock between the current and previous key'UNION ALL SELECT 'LCK_M_RIn_U' , 'LOCK - Waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key'UNION ALL SELECT 'LCK_M_RIn_X' , 'LOCK - Waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key'UNION ALL SELECT 'LCK_M_RS_S' , 'LOCK - Waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous'UNION ALL SELECT 'LCK_M_RS_U' , 'LOCK - Waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key'UNION ALL SELECT 'LCK_M_RX_S' , 'LOCK - Waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key'UNION ALL SELECT 'LCK_M_RX_U' , 'LOCK - Waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key'UNION ALL SELECT 'LCK_M_RX_X' , 'LOCK - Waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key'UNION ALL SELECT 'LCK_M_S' , 'LOCK - Waiting to acquire a Shared lock'UNION ALL SELECT 'LCK_M_SCH_M' , 'LOCK - Waiting to acquire a Schema Modify lock'UNION ALL SELECT 'LCK_M_SCH_S' , 'LOCK - Waiting to acquire a Schema Modify lock'UNION ALL SELECT 'LCK_M_SIU' , 'LOCK - Waiting to acquire a Shared With Intent Update lock'UNION ALL SELECT 'LCK_M_SIX' , 'LOCK - Waiting to acquire a Shared With Intent Exclusive lock'UNION ALL SELECT 'LCK_M_U' , 'LOCK - Waiting to acquire an Update lock'UNION ALL SELECT 'LCK_M_UIX' , 'LOCK - Waiting to acquire an Update With Intent Exclusive lock'UNION ALL SELECT 'LCK_M_X' , 'LOCK - Waiting to acquire an Exclusive lock'UNION ALL SELECT 'LATCH_DT' , 'LOCK - Waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches'UNION ALL SELECT 'LATCH_EX' , 'LOCK - Waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches'UNION ALL SELECT 'LATCH_KP' , 'LOCK - Waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches'UNION ALL SELECT 'LATCH_SH' , 'LOCK - Waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches'UNION ALL SELECT 'LATCH_UP' , 'LOCK - Waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches'UNION ALL SELECT 'RESOURCE_SEMAPHORE' , 'MEMORY - Query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts'UNION ALL SELECT 'RESOURCE_SEMAPHORE_MUTEX' , 'MEMORY - Query waits for its request for a thread reservation to be fulfilled. It also occurs when synchronizing query compile and memory grant requests'UNION ALL SELECT 'RESOURCE_SEMAPHORE_QUERY_COMPILE' , 'MEMORY - Number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans'UNION ALL SELECT 'RESOURCE_SEMAPHORE_SMALL_QUERY' , 'MEMORY - Memory request by a small query cannot be granted immediately due to other concurrent queries. Wait time should not exceed more than a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries'UNION ALL SELECT 'WRITELOG' , 'MEMORY - Waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits'UNION ALL SELECT 'PAGEIOLATCH_DT' , 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits may indicate problems with the disk subsystem'UNION ALL SELECT 'PAGEIOLATCH_EX' , 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem'UNION ALL SELECT 'PAGEIOLATCH_KP' , 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits may indicate problems with the disk subsystem'UNION ALL SELECT 'PAGEIOLATCH_SH' , 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Share mode. Long waits may indicate problems with the disk subsystem'UNION ALL SELECT 'PAGEIOLATCH_UP' , 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits may indicate problems with the disk subsystem'UNION ALL SELECT 'PAGELATCH_DT' , 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode'UNION ALL SELECT 'PAGELATCH_EX' , 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode'UNION ALL SELECT 'PAGELATCH_KP' , 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode'UNION ALL SELECT 'PAGELATCH_SH' , 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode'UNION ALL SELECT 'PAGELATCH_UP' , 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode'UNION ALL SELECT 'LOGBUFFER' , 'DISK - Waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server'UNION ALL SELECT 'ASYNC_IO_COMPLETION' , 'DISK - Waiting for I/Os to finish'UNION ALL SELECT 'IO_COMPLETION' , 'DISK - Waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits'SELECT TOP 10 'OS WAIT STATS - WAIT TYPE' = ws.wait_type ,'TASKS WAITING COUNT' = ws.waiting_tasks_count ,'TIME WAITING (MS)' = ws.wait_time_ms ,'MAX TIME WAITING (MS)' = ws.max_wait_time_ms ,'SIGNAL TIME WAITING (MS)' = ws.signal_wait_time_ms ,'POSSIBLE ISSUES' = wt.commentFROM #wait_stats ws INNER JOIN #wait_type wt ON (ws.wait_type = wt.wait_type)ORDER BY ws.wait_time_ms DESCDROP TABLE #wait_statsDROP TABLE #wait_type[/code]

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Mon, 26 Nov 2012 09:14:35 GMT

[quote][b]ahperez (11/26/2012)[/b][hr]Thanks for taking the time to post this script. I find the "Possible Issues" column very useful. Spurred me to take the time to find updated firmware for my disk array.[/quote]Great! I'm glad the code is helping out. That's what I was hoping for.Rudy

RE: Top 10 Wait State Issues Reporter

ahperez — Mon, 26 Nov 2012 09:02:53 GMT

Thanks for taking the time to post this script. I find the "Possible Issues" column very useful. Spurred me to take the time to find updated firmware for my disk array.

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Fri, 23 Nov 2012 12:24:20 GMT

Hi Tony,I haven't seen Glenn's code so I can't comment on it. My code reads from sys.dm_os_wait_stats and then produces a report from the top 10 in descending order.As you can see, it basic and is data collected by Microsoft SQL server.Not sure what else I can say.Thanks again!Rudy

RE: Top 10 Wait State Issues Reporter

Tony Trus — Thu, 22 Nov 2012 14:03:29 GMT

I look forward to it :)Oddly it wasnt that the numbers were different. It was that the waits were entirely different. There wasn't any overlap between the two scripts. Your script looked like this:WRITELOG 71683311 41587429CXPACKET 17272294 12899940ASYNC_NETWORK_IO 2031366 3974649PAGEIOLATCH_SH 3710965 3626942PAGEIOLATCH_EX 688979 436225ASYNC_IO_COMPLETION 299 297143IO_COMPLETION 415738 212294PAGELATCH_EX 23683964 209939SOS_SCHEDULER_YIELD 5274174 136505LATCH_EX 236420 71269Glenn's looked like this:wait_type wait_time_s pct running_pctSQLTRACE_INCREMENTAL_FLUSH_SLEEP 1490310.39 38.28 38.28BROKER_EVENTHANDLER 1434896.13 36.86 75.14ONDEMAND_TASK_QUEUE 887224.04 22.79 97.94Notice no overlap in wait types...Both scripts are awesome in their own way and I look forward to all of the inovation and inginuity that goes into each person's version.Best,Tony

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Thu, 22 Nov 2012 13:25:31 GMT

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,

RE: Top 10 Wait State Issues Reporter

Tony Trus — Thu, 22 Nov 2012 12:44:47 GMT

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 rnFROM sys.dm_os_wait_statsWHERE 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_pctFROM Waits AS W1INNER JOIN Waits AS W2ON W2.rn <= W1.rnGROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pctHAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Thu, 22 Nov 2012 12:01:25 GMT

[quote][b]Tony Trus (11/22/2012)[/b][hr]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[/quote]Thanks for the comments. As for the copy /paste issues, try using a different web browser.

RE: Top 10 Wait State Issues Reporter

Tony Trus — Thu, 22 Nov 2012 11:55:38 GMT

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

RE: Top 10 Wait State Issues Reporter

Caruncles — Thu, 22 Nov 2012 10:39:33 GMT

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.

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Thu, 22 Nov 2012 10:33:55 GMT

[quote][b]Caruncles (11/22/2012)[/b][hr]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?[/quote]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,RudyPS. Please continue to ask question regardless as we all learn from them :)

RE: Top 10 Wait State Issues Reporter

Caruncles — Thu, 22 Nov 2012 10:24:37 GMT

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?

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Thu, 22 Nov 2012 07:52:55 GMT

[quote][b]Arthur Kirchner (11/22/2012)[/b][hr]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 stabilitySCH_M <=> Schema modification[/quote]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

RE: Top 10 Wait State Issues Reporter

Arthur Kirchner — Thu, 22 Nov 2012 07:34:03 GMT

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 stabilitySCH_M <=> Schema modification

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Thu, 22 Nov 2012 07:15:32 GMT

[quote][b]Arthur Kirchner (11/22/2012)[/b][hr]I am also delighted, thanks a lot!here a little correction'LOCK - Waiting to acquire a Schema [b]Modify[/b] lock' WHERE dbo.[#results].[wait_type] = 'LCK_M_SCH_S' ... should be Share.[/quote]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

RE: Top 10 Wait State Issues Reporter

Arthur Kirchner — Thu, 22 Nov 2012 06:59:45 GMT

I am also delighted, thanks a lot!here a little correction'LOCK - Waiting to acquire a Schema [b]Modify[/b] lock' WHERE dbo.[#results].[wait_type] = 'LCK_M_SCH_S' ... should be Share.

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Thu, 22 Nov 2012 06:05:38 GMT

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

RE: Top 10 Wait State Issues Reporter

Rudy Panigas — Thu, 22 Nov 2012 05:56:02 GMT

Thanks for the comments! :)Could please tell me where the problem is? i don't see the problem.Thanks,Rudy

RE: Top 10 Wait State Issues Reporter

Seed Vicious — Thu, 22 Nov 2012 05:16:55 GMT

Hi Rudy, excelent script!but, you have a little bug at the en of the second comment... /* instead */ ;P

Top 10 Wait State Issues Reporter

Rudy Panigas — Wed, 21 Nov 2012 22:45:05 GMT

Comments posted to this topic are about the item [B]Top 10 Wait State Issues Reporter[/B]