Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

T-SQL Tuesday #49: Wait for it

T-SQL TuesdayIt’s T-SQL Tuesday again and this time it’s being hosted by the SQL Soldier. He’s picked the subject of Waits. As in, why am I having to wait for my query to finish. Or more specifically what is my query waiting for. Waits are a popular subject. You can find lots of great articles by some really smart people out on the interweb. Brent Ozar, Kevin Kline and Paul Randal to name but a few. That being the case I’m not going to discuss wait problems. I’m going to make a brief statement about the importance of knowing which waits aren’t a problem. And let’s be very clear, not all waits are a problem.

Waits are a natural part of life and you can’t avoid them. Almost every time you have parallelism you are going to have CXPACKET waits. It’s all but impossible to break out the parallel threads so exactly that they will all end at the same time. I mean you might get lucky, which is why I said “Almost”, but it’s going to be rather unusual. If you have a service broker or email system that isn’t constantly in use you are going to see some waiting. Your network is going to cause some waiting. So is your IO subsystem. There is a lot of waiting to be done even on a good healthy system.

So what is my point? Next time you are doing performance tuning (server, query, etc) you will be reviewing waits. Looking at sys.dm_os_wait_stats, sys.dm_exec_requests, etc searching for your “wait problem”. Just remember that not all of the wait’s you are seeing are a problem. Be aware of what’s normal for your system and what isn’t. One place you can start is Erin Stellato’s article Capturing Baselines on SQL Server: Wait Statistics and another is John Sterrett’s Benchmark SQL Server Wait Statistics. Once you have a good set of wait statistic baselines you will know what information you are seeing will help you and what won’t.


Filed under: Microsoft SQL Server, Performance, SQLServerPedia Syndication, T-SQL Tuesday Tagged: language sql, microsoft sql server, Performance, T-SQL, T-SQL Tuesday, Waits

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...