Chapter 2: Tracing and Profiling
—Adam Machanic
Query tuning, optimization, and general troubleshooting are all made possible through visibility into what’s going on within SQL Server; it would be impossible to fix problems without being able to identify what caused them. SQL Trace and SQL Server Profiler, some of the most powerful tools provided by SQL Server, can give you a real-time or near real-time peek into exactly what the database engine is up to, at a very granular level.
Included in the tracing toolset are over 170 events that you can monitor and filter to get a look at anything from a broad overview of user logins down to such fine-grained information as the lock activity done by a specific server process id (spid). This data is all made available via a specialized user interface tool, SQL Server Profiler, in addition to a series of server-side stored procedures and .NET classes, giving you the flexibility to roll a custom solution when a problem calls for one.
This chapter outlines the overall architecture used by the system under the covers, then goes on to show you how to actually use SQL Trace to solve some common problems. SQL Trace is fairly easy to use once you get familiar with it, but the combination of possible options is extensive, so we’ll focus on those that you’ll use most often in the real world. We’ll also spend some time discussing how best to design your traces so as to keep things as efficient as possible. Tracing server activity bears a slight performance penalty, but by paying attention to best practices you can help minimize any impact.
© Microsoft. All Rights Reserved.