Query Analysis with SQL Profiler

With the additions to SQL Profiler in SQL Server 2000, it has become an even more powerful tool for query analysis. When you must monitor a broad range of queries and database activity and analyze the performance, it is difficult to analyze manually. For example, if you have a number of stored procedures to analyze, how would you know which ones to focus on as problem procedures? You would have to identify sample parameters for all of them and execute them to see which ones were running too slowly and then after they were identified, do some query analysis on them.

With SQL Server Profiler, you can simply define a trace to capture performance-related statistics on-the-fly while the system is being used normally. This way, you can capture a representative sample of the type of activity your database will receive and capture statistics for the stored procedures as they are being executed with real data values. Also, to avoid having to look at everything, you can set a filter on the Duration column so that it only displays items with a runtime longer than the specified threshold.

The events that you want to capture to analyze query performance include Performance:Execution Plan, Performance:Show Plan All, Performance:Show Plan Statistics, and Performance:Show Plan Text. The data columns that you want to be sure to include are the TextData, CPU, StartTime, Duration, and Reads and Writes.

Capturing this performance information with SQL Profiler will provide you with all the same information that you can capture with all the other individual tools discussed in this chapter. The trace information can be easily saved to a file or a table for replaying the sequence to test index or configuration changes, or simply for historical analysis.

For more information on using SQL Profiler, see Chapter 7, "Using the SQL Server Profiler."

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features