Professional SQL Server™ 2005 Programming

23.10. Troubleshooting

SQL Server offers a number of options to help with the prevention, detection, and measurement of long-running queries. The options range from a passive approach of measuring actual performance, so you know what's doing what, to a more active approach of employing a query "governor" to automatically kill queries that run over a length of time you choose. These tools are very often ignored or used only sparingly — which is something of a tragedy — they can save hours of troubleshooting by often leading you right to the problem query and even the specific portion of your query that is creating the performance issues.

Tools to take a look at include:

• SHOWPLAN TEXT|ALL and Graphical showplan — Looked at in this chapter

• STATISTICS IO — Also in this chapter

• Database Consistency Checker (DBCC) — Also in this chapter

• The Query Governor — Also in this chapter

• The sys.processes table function

• The SQL Server Profiler — Covered in this chapter

Many people are caught up in just using one of these, but the reality is that there is little to no (depending on which two you're comparing) overlap between them. This means that developers and DBAs who try to rely on just one of them are actually missing out on a lot of potentially important information.

Also, keep in mind that many of these are still useful in some form even if you are writing in a client-side language and sending the queries to the server (no sprocs). You can either watch the query come through to your server using ...