September 10, 2012 at 11:56 pm
Hi All
I'm using the following script to assess my highest wait_types ona fairly new SQL Server (+- 2 weeks)
SELECT TOP 15
wait_type ,
wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC
The highest wait type is for PAGEIOLATCH_SH and it shows that this wait_type contributes 35% of the total waits.
Where can I start troubleshooting this wait?
Is this a sign of a slow disk subsystem or should I start looking at my CPU pressure or is it query related?
Any help would be great
Thanks
September 11, 2012 at 2:49 am
I think you mean its 35% of the wait time after excluding all those other waits.
it *may* be negligible in proportion to all waits.
Before looking at hardware you should really investigate the querys that are causing the waits - you can also look at the missing index stats, which may provide some clues.
If users are experiencing performance problems, what are they? is it only certain queries?, only on one database?, or just generally slow?
September 11, 2012 at 2:52 am
Start with chapter 1.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2012 at 3:10 am
Thanks for the free book, Obi-Wan.
Looks pretty comprehensive from a first skim through, and small enough to read it all (*not* a 1200 page monster - which is good)
September 11, 2012 at 3:59 am
We'd have easily written 1200 pages, but the deadline was against us. 🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2012 at 4:19 am
Hi Gail,
Just noticed your name in the sig line - and that you're technical reviewer on the book!
and apologies - yours isn't a picture of Obi-Wan either is it?
September 11, 2012 at 4:22 am
t.brown 89142 (9/11/2012)
Just noticed your name in the sig line - and that you're technical reviewer on the book!
Yup
and apologies - yours isn't a picture of Obi-Wan either is it?
It's Qui-gon Jinn from Phantom Menace.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply