Here’s our favorite links for performance tuning SQL Server with wait stats:
Introductions to Wait Stats
- The Ozar Family Tradition of Performance Monitoring – When my grandfather needed to tune his SQL Servers, he used Perfmon, and he passed his knowledge on down through my father to me. Today, though, we’ve got a better way – wait stats – and I explain how they’re different.
- What is SQL Server Waiting On Now? - a query from our First Responder Kit that checks SQL Server’s cumulative waits since the last restart, plus waits over the next 30 seconds.
- Waits and Queues Whitepaper – by the Microsoft SQL Server Customer Advisory Team, aka SQLCAT.
- Our Book – Professional SQL Server Internals and Troubleshooting – in chapter 3, we discuss how the SQL Server engine tracks what it’s waiting for with wait statistics.
Tools to Analyze Waits and Queues
- sp_WhoIsActive from Adam Machanic – this awesome free replacement for sp_who shows you what’s REALLY happening inside your database server including the queries, wait statistics, and locking/blocking issues. Call it with @get_plans = 1 to see execution plans, too!
- SQLNexus – when you want to analyze performance over time, use this free tool from Microsoft to generate pretty graphs of your SQL Server’s activity. Not for the faint of heart, but we discuss it in detail in chapter 13 of our book, Professional SQL Server 2008 Internals and Troubleshooting.
SQL Server built the query results, and it’s just sitting around waiting for the application on the other end of the pipe to consume the results faster. There’s nothing you can do to performance tune the SQL Server here – you have to figure out why the app can’t get the data down faster. It could be a slow network pipe between the app and the SQL Server (like a long distance wide area network), an underpowered client machine, or row-by-row processing happening the application server.
What is the CXPACKET Wait Type, and How Do You Reduce It? – Brent explains the single most popular – and most cryptic – wait type.
SQL Server is waiting to read data from storage. It’s not necessarily a case of under-provisioned storage – it could also be insufficient memory in SQL Server available to cache data, or a lack of indexes on the tables involved, or queries that aren’t sargable.