T-SQL Tuesday #49: Wait for it

3

December 10, 2013 by Kenneth Fisher

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.

3 thoughts on “T-SQL Tuesday #49: Wait for it

  1. sqlsoldier says:

    Thanks for participating in T-SQL Tuesday #49!

  2. […] Fisher (blog) talked about some of the resources he uses for understanding waits and offers up some advice on how to think about wait […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: