| Author |
 Topic  |
|
|
viperbyte
Posting Yak Master
USA
101 Posts |
 Posted - 12/06/2012 : 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
USA
101 Posts |
Posted - 12/06/2012 : 16:38:36
|
| btw I got the code from Michael J. Swart's site. |
 |
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 12/06/2012 : 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
USA
101 Posts |
Posted - 12/06/2012 : 17:38:15
|
| ASYNC_NETWORK_IO at 282 is ok? |
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 12/06/2012 : 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
USA
101 Posts |
Posted - 12/07/2012 : 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
USA
15557 Posts |
Posted - 12/07/2012 : 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
USA
101 Posts |
Posted - 12/07/2012 : 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. |
|
|
| |
Topic |
|
Struggling with Wait Stats
