3 4
In addition to using Query Analyzer to find inefficient T-SQL statements, you can use the SQL Profiler utility. Profiler can view all T-SQL statements that are running on the system and graphically display information about them. It also provides sorting and filtering options you can use to locate the T-SQL statements that are using the most CPU and I/O resources. With this information, you can determine which T-SQL statements to concentrate on when you tune the statements. T-SQL statements that are invoked through an application can be viewed in Profiler; you don't have to access the application source code.
The Profiler utility in SQL Server 2000 is similar to the Profiler utility included in SQL Server 7, but it contains a few enhancements. One useful enhancement is the introduction of the trace template, which can be used to create trace files. (A trace must still be created before you can use it to trace SQL Server activities.) With SQL Server 7, traces had to be created by hand.
To invoke the Profiler utility and run a trace, follow these steps:
Figure 35-16. The Open dialog box showing available trace templates.
The trace templates that come with SQL Server are described here:
SQLServerProfilerSP_Counts.tdf Counts the number of stored procedures that have been run. The results are grouped by the stored-procedure name and include the number of times the procedure was executed.
SQLServerProfilerStandard.tdf Collects general information about connections, stored procedures executed, and SQL batches in the order that they were executed.
SQLServerProfilerTSQL.tdf Collects all the T-SQL statements in the order in which they were submitted to SQL Server by the user community. The trace contains simply the T-SQL statements and the times they were issued.
SQLServerProfilerTSQL_Duration.tdf Displays the T-SQL statements that have been issued as well as the time (in milliseconds) those T-SQL statements required to execute.
SQLServerProfilerTSQL_Grouped.tdf Collects data similar to that collected by the SQLServerProfilerTSQL trace, but groups the statements by the users that submitted them.
SQLServerProfilerTSQL_Replay.tdf Provides detailed information about the T-SQL statements that have been issued. This trace provides data that can be used to replay T-SQL statements in Query Analyzer.
SQLServerProfilerTSQL_SPs.tdf Displays the specified stored procedure as well as the T-SQL commands within that stored procedure. The results are displayed in the order that they were executed.
SQLServerProfilerProfilerTuning.tdf Collects data about stored-procedure and SQL batch execution.
These trace templates can be quite useful. For example, the trace template SQLServerProfilerTSQL_Duration can help you determine which T-SQL statements are taking the most time to execute. This information gives you a place to start when you want to optimize a query. The statement might be running slowly because it has a lot of work to do, or it might be running slowly because it is inefficient. As you will see in the next step, you must use a predefined template for every trace.
Figure 35-17. The Connect To SQL Server dialog box.
Figure 35-18. The General tab of the Trace Properties window.
Figure 35-19. The Events tab of the Trace Properties window.
Figure 35-20. The Data Columns tab of the Trace Properties window.
Figure 35-21. The Filters tab of the Trace Properties window.
CAUTION
Profiler can use significant system resources in a busy environment. The more events you trace, the more overhead will be used.
Figure 35-22. A running trace.