SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Struggling with Wait Stats
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 12/06/2012 :  16:37:31  Show Profile  Reply with Quote
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
132 Posts

Posted - 12/06/2012 :  16:38:36  Show Profile  Reply with Quote
btw I got the code from Michael J. Swart's site.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 12/06/2012 :  16:54:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
132 Posts

Posted - 12/06/2012 :  17:38:15  Show Profile  Reply with Quote
ASYNC_NETWORK_IO at 282 is ok?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 12/06/2012 :  18:20:21  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
132 Posts

Posted - 12/07/2012 :  16:09:31  Show Profile  Reply with Quote
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

USA
15655 Posts

Posted - 12/07/2012 :  16:38:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
132 Posts

Posted - 12/07/2012 :  17:56:17  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000