Professional SQL Server® 2008 Internals and Troubleshooting

Chapter 3. SQL Server Waits and Extended Events

WHAT'S IN THIS CHAPTER

• What are wait statistics?

• How to look at wait statistics in previous editions of SQL Server

• The most common wait types of importance and why they occur

• The Extended Events Architecture

• How to CREATE and ALTER Extended Event Sessions

• How to use Extended Events to capture detailed information on SQL Waits

Being able to troubleshoot problems when they happen in SQL Server is paramount to the success of any database administrator. Performance problems in particular can be a sink or swim type of test of the abilities and value a database administrator brings to the table. In an ideal world, anytime a query or batch is submitted for execution inside of SQL Server it will run immediately without delay. Most people do not, however, live in an ideal world. In fact most often, queries executed against SQL Server have to wait at some point during their execution while the resources required during their execution are gathered, latched/locked, and or other processes execute on the server. When troubleshooting performance problems and tuning SQL Server, understanding common and troublesome waits and how to eliminate them is often one of the ways to get the best bang for your buck. This chapter dives into what wait types are and how to troubleshoot them in SQL Server 2008.

In SQL Server 2000 wait statistics were aggregated and maintained at the server level, and were able to be reviewed through the use of DBCC SQLPERF. Additionally, ...