Struggling with Wait Stats - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Struggling with Wait Stats

Author

Topic

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-06 : 16:37:31

Hi all it's me again. So sorry to bug you all again. I ran this following script and would like help on interpreting it. I'm thinking about importing the output into access so I can query for the bigger values and focus on those rows for my investigation. Can you folks guide me on what wait types to focus on and tell me what is an acceptal wait time or give the help in which ever style or dose you would like? This is the script:

select * into #BufferWaitStats from sys.dm_os_wait_stats

-- wait ten seconds or so
--wait for X amount of time
WAITFOR DELAY '00:00:10';

select ws.wait_type,
ws.waiting_tasks_count - bws.waiting_tasks_count as waiting_tasks_count,
ws.wait_time_ms - bws.wait_time_ms as wait_time_ms,
ws.max_wait_time_ms,
ws.signal_wait_time_ms - bws.signal_wait_time_ms as signal_wait_time_ms
from sys.dm_os_wait_stats ws
join #BufferWaitStats bws
on ws.wait_type = bws.wait_type
order by wait_time_ms desc

--clean up
drop table #BufferWaitStats

I got 490 rows returned. So this is much for me. Here's a small sample of the output in case you see something that screams to you red flag. I know you are all very busy and probably can comb through this but if you can tell me what to look for I'll be happy with that. this is some of the output:


wait_type	waiting_tasks_count	wait_time_ms	max_wait_time_ms	signal_wait_time_ms
WAITFOR	5	30000	10000	0
SQLTRACE_INCREMENTAL_FLUSH_SLEEP	3	12000	4129	0
LAZYWRITER_SLEEP	10	10095	1196	5
LOGMGR_QUEUE	5	10013	5683	0
BROKER_TASK_STOP	2	10005	10000	0
REQUEST_FOR_DEADLOCK_SEARCH	2	10000	5118	10000
ASYNC_NETWORK_IO	282	6541	2038	13
SLEEP_TASK	38	5187	1133	67
BROKER_TO_FLUSH	5	5120	1080	0
CXPACKET	7	145	3991	6
SOS_SCHEDULER_YIELD	329	143	110	142
PREEMPTIVE_OS_REPORTEVENT	37	25	57	0
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT	37	12	82	0
PREEMPTIVE_OS_AUTHORIZATIONOPS	54	12	236	0
PREEMPTIVE_OS_DELETESECURITYCONTEXT	39	11	45	0
PREEMPTIVE_OS_AUTHZINITIALIZECONTEXTFROMSID	2	7	6760	0
PREEMPTIVE_OS_AUTHENTICATIONOPS	58	5	72	0
PREEMPTIVE_OS_CRYPTIMPORTKEY	37	5	26	0
WRITELOG	5	2	75	0
PREEMPTIVE_OS_LOOKUPACCOUNTSID	5	2	33	0
LATCH_EX	7	1	37	0
LATCH_KP	0	0	0	0
LATCH_NL	0	0	0	0
LATCH_SH	0	0	4	0
LATCH_UP	0	0	0	0
ABR	0	0	0	0
ASSEMBLY_LOAD	0	0	0	0
ASYNC_DISKPOOL_LOCK	0	0	0	0
ASYNC_IO_COMPLETION	0	0	6264	0
PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER	2	0	6	0

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-06 : 16:38:36

btw I got the code from Michael J. Swart's site.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-12-06 : 16:54:43

None of these look to be excessive. You can reduce the row count by adding WHERE waiting_tasks_count>0 since those wait types aren't being generated.

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-06 : 17:38:15

ASYNC_NETWORK_IO at 282 is ok?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-12-06 : 18:20:21

That wait type is an indication that SQL Server is waiting for a client to consume the data it's returning. 282 counts with a total of 6541 milliseconds, max of 2038 milliseconds doesn't strike me as being the problem.

If people are complaining that the web app is slower than 2 seconds, then the queries must be large, and/or the network slow, and/or the application code is taking a long time to process the data, but from what you've posted it doesn't appear that SQL Server is overloaded.

You should run the queries Glenn Berry has posted here: http://sqlserverperformance.wordpress.com/2010/08/23/dmv-emergency-queries-from-sql-saturday-51-in-nashville/

He has DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) commented out, I'd leave it like that for at least your first day of analysis. Don't clear the wait stats until you're comfortable that you have good baseline data from those queries (you can go up to a week if you need to). Pay particular attention to the results returned by the "expensive" DMV queries, these will point out the worst performing queries/procedures. You can then start looking at some of them by duration and examine their execution plans for possible tuning.

Don't rush this, take a full day at least to get comfortable with this data and be sure to understand it before you make any system changes.

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-07 : 16:09:31

Thanks for the Glenn Berry script link. I have ran the first 20 or so scripts and see that I have a lot to learn. Any suggestions on where to look for information that I can learn from on how to interpret this output? Specifically on what are good or bad values?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-12-07 : 16:38:43

Keep reading Glenn Berry. He explains a lot of this stuff and gives more examples. Also go to SQLSkills.com and read Paul Randal's blog, he has a link for wait stats. I'm pretty sure I linked it in one of your other postings.

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-07 : 17:56:17

Super thanks. I'm gonna bug out of here in a couple. I'll cram long and hard this weekend with the Glenn Berry, Paul Randal and SqlSkills material. Have a nice weekend everybody.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources