Microsoft® SQL Server® 2008 For Dummies®

Chapter 14. Troubleshooting SQL Server 2008 Problems

From time to time, you may encounter problems with your SQL Server databases. These issues may come as the result of poorly written database queries, hardware/software performance issues, or the physical failure of hardware components.

When performance issues arise, troubleshooting often requires a joint effort between database administrators and server administrators. In this chapter, I describe some of the tools available to SQL Server 2008 DBAs to assist in the detection and troubleshooting of database problems.

Understanding the Inner Workings of SQL Server Queries

SQL Server allows you to capture quite a bit of information about queries in progress. Some commonly used data elements include:

• Transact-SQL statements executed

• Stored procedures invoked

• Query execution time (in CPU time or clock time)

• Physical disk activity

• Login name responsible for each query

SQL Server provides this information through the use of the SQL Trace facility. This complex programming environment is accessible through an Application Programmer Interface (API). Alternatively, you can access this advanced functionality using the graphical user interface offered by SQL Server Profiler.

Creating a trace with SQL Server Profiler

If you'd like to capture detailed information about SQL Server performance using SQL Server Profiler, you must first create a trace. Each trace defines the events you would like to gather data about and the specific data elements you would ...