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.
Go to Top of Page

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.
Go to Top of Page

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-06 : 17:38:15
ASYNC_NETWORK_IO at 282 is ok?
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -