Here’s our favorite links for performance tuning SQL Server with wait stats:

Introductions to Wait Stats

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.

ASYNC_NETWORK_IO

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.

CXPACKET

What is the CXPACKET Wait Type, and How Do You Reduce It? – Brent explains the single most popular – and most cryptic – wait type.

PAGEIOLATCH_*

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.

Explanations of Each Wait Type

css.php