Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Top 10 Wait State Issues Reporter Expand / Collapse
Author
Message
Posted Thursday, November 22, 2012 10:39 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:21 PM
Points: 78, Visits: 193
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."
Post #1387952
Posted Thursday, November 22, 2012 11:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:16 AM
Points: 27, Visits: 77
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
Post #1387965
Posted Thursday, November 22, 2012 12:01 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 312, Visits: 1,106
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.



Post #1387967
Posted Thursday, November 22, 2012 12:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:16 AM
Points: 27, Visits: 77
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
Post #1387973
Posted Thursday, November 22, 2012 1:25 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 312, Visits: 1,106
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,



Post #1387979
Posted Thursday, November 22, 2012 2:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:16 AM
Points: 27, Visits: 77
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 41587429
CXPACKET 17272294 12899940
ASYNC_NETWORK_IO 2031366 3974649
PAGEIOLATCH_SH 3710965 3626942
PAGEIOLATCH_EX 688979 436225
ASYNC_IO_COMPLETION 299 297143
IO_COMPLETION 415738 212294
PAGELATCH_EX 23683964 209939
SOS_SCHEDULER_YIELD 5274174 136505
LATCH_EX 236420 71269

Glenn's looked like this:
wait_type wait_time_s pct running_pct
SQLTRACE_INCREMENTAL_FLUSH_SLEEP 1490310.39 38.28 38.28
BROKER_EVENTHANDLER 1434896.13 36.86 75.14
ONDEMAND_TASK_QUEUE 887224.04 22.79 97.94

Notice 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
Post #1387982
Posted Friday, November 23, 2012 12:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 312, Visits: 1,106
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



Post #1388231
Posted Monday, November 26, 2012 9:02 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, August 15, 2014 8:01 AM
Points: 440, Visits: 593
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.
Post #1388671
Posted Monday, November 26, 2012 9:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 312, Visits: 1,106
ahperez (11/26/2012)
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.


Great! I'm glad the code is helping out. That's what I was hoping for.

Rudy



Post #1388677
Posted Monday, November 26, 2012 9:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:05 AM
Points: 426, Visits: 450
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.

USE MASTER
SET NOCOUNT ON


CREATE 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_ms
FROM
sys.dm_os_wait_stats
WHERE
waiting_tasks_count <> 0


INSERT 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.comment
FROM
#wait_stats ws
INNER JOIN
#wait_type wt ON (ws.wait_type = wt.wait_type)
ORDER BY
ws.wait_time_ms DESC


DROP TABLE #wait_stats
DROP TABLE #wait_type

Post #1388681
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse