Pluralsight blog Where devs, IT admins & creative pros go for news, tips, videos and more.
Pluralsight + Digital-Tutors - 3,000 tech & creative courses - starting at $29/month Get it now →
February 26, 2014

Analyze your SQL Server wait stats and get free SQL Server training!

By

One of my favorite topics in SQL Server is wait statistics analysis. Why? Because there is so much misinformation out there about what the various wait types mean and what you should do about them.

They’re an incredibly useful way to diagnose SQL Server performance problems, giving you insight into both the current state of resource bottlenecks on your SQL Server and the historical resource bottleneck trends over time. They’re always tracked by SQL Server and to make use of them you just need to query the relevant DMVs correctly and know how to interpret the results.

And that’s the catch: knowing how to correctly gather the data and interpret it.

Right now I’m running an offer in conjunction with the very nice folks at Pluralsight where if you run a wait-statistics collection script from my blog and send me the results, I’ll give you insight into what your results mean and send you a code for 30 days of free access to all 100+ hours of SQLskills Pluralsight courses. That’s going to be a lot of real-life data for me to analyze, and a lot of real-life training for you!

You can get the script to use and more details from my blog post here.

Here’s some (simplified) example data:

WaitType WaitCount Percentage AvgWait_S
CXPACKET 38403526 62.84 0.0039
PAGEIOLATCH_SH 3614930 18.01 0.0120
ASYNC_NETWORK_IO 33680 17.94 1.287

There are a couple of thing I notice immediately from these statistics:

  1. There are lots of CXPACKET waits (indicating parallelism) with accompanying PAGEIOLATCH_SH waits (indicating data pages being read from disk into memory).
  2. There are long waits for ASYNC_NETWORK_IO.

Number one is a common pattern caused by parallel table scans, which could indicate a missing nonclustered index is causing a query to require a costly table scan instead of an index seek.

Number two is a common pattern caused by applications doing row-by-agonizing-row (RBAR) processing of data returned by SQL Server, instead of caching the data client-side, and immediately telling SQL Server that it can send more data.

Of course, there are other causes of these patterns, but the common ones are what I would suspect first. In my wait statistics course, I go into great detail about common wait types, what can cause them to become prevalent, and what the ‘knee-jerk’ reaction commonly is that you should avoid.

I encourage you to take me up on this offer and send me your data. You’ll get to watch my course (and a bunch more) for free, and get a taste of the SQL Server training available on Pluralsight.

About the Author

is the CEO of SQLskills.com, a Microsoft Regional Director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. Among other things, he wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development. Since leaving Microsoft, Paul has been a full-time consultant and trainer on SQL Server around the world, including authoring and teaching the prestigious Microsoft Certified Master certifications on SQL Server and SharePoint for Microsoft. He regularly presents at conferences such as SQL PASS, SQLIntersection, and TechEd, and has written or contributed to numerous white papers and books on SQL Server. He also writes regular columns and articles for SQL Server Pro magazine and TechNet Magazine, and is a Contributing Editor of both. He blogs at SQLskills.com/blogs/paul, and you can find him on Twitter at twitter.com/PaulRandal. When he's away from work, his main passion is scuba diving with his wife, fellow SQL expert Kimberly Tripp, and their two daughters.

Author's Website: http://sqlskills.com


Discussion