Monitoring the Performance of SQL Server

Solution Preview

---

How to Take Advantage of SQL Server 2000 Performance Tuning Tools
--------------------------------------------------------------------------------

SQL Server 2000 includes several tools you may find useful when performance tuning your SQL Server applications. The include:

Query Analyzer
Profiler
Index Wizard
System (Performance) Monitor

In the next couple of sections, we will take a look at how your can take advantage of these tools to help optimize your SQL Server-based applications.

SQL Server 2000 Query Analyzer

The SQL Server 2000 Query Analyzer is not only a great tool for developing and debugging Transact-SQL code, it is also a great tool for performance tuning Transact-SQL code. In this section we will take a look at what the Query Analyzer can do, and also learn a little about how it can be used to help identify and resolve performance problems. And like many of the more advanced SQL Server 2000 tools, you need to have a fairly good understanding of Transact-SQL to get the most out of this tool. Let's take a look at some of the key features included with Query Analyzer that can be used to help performance tune Transact-SQL code.

Show Execution Plan

Whenever you enter a query into the Query Analyzer, you can run it and see the results immediately. While this lets you know if the results are what you expected, it doesn't tell you much about performance. Sure, the bottom of the screen tells you how long the query ran, but that's about all.

One of the most powerful features of the Query Analyzer is that you can turn on a feature called Show Execution Plan. This option allows you to view the execution plan used by SQL Server's Query Optimizer to actually execute the query. This option is available from the Query menu on the main menu of Query Analyzer, and must be turned on before the query is executed. Once the query is executed, the results of the execution plan are displayed in graphical format in a separate window, available from a tab that appears below the query results window on the screen.

The execution plan displayed in the Execution Plan window may be very simple, if the query is simple, or it may be very complex, if the query is complex. It will show you, step-by-step, how the Query Optimizer executed the query. The execution plan should be read right to left, as the right part of the plan indicates the first step taken by the Query Analyzer, and you continue reading the plan, from right to left, until you get to the left side of the screen, which indicates the very last step taken by the Query Analyzer.

While the graphical representation of the execution plan is interesting, the most useful and powerful part of this tool is somewhat hidden. If you move the cursor on top of each of the steps in the query plan, a pop-up box appears with detailed information exactly what the Query Optimizer did in each step as the query was executed.

The information displayed in the pop-up box is sometimes obvious, such as telling you that a "Clustered Index Scan" was performed, or it may very obscure, such as the "Subtree Cost" was .0376. The details provided not only often need further explaining, they need interpreting. The explanations can be found in the Books Online, but the interpretation takes experience using and performance tuning SQL Server, a topic beyond the scope of this article.

If the query you are working with takes a long time to run, and you want to experiment with your query, you don't have to actually run the query each time. Query Analyzer also has an option to create and display an Execution Plan without actually running the query. This option is also available under the Query menu, and it is called "Display Estimated Execution Plan".

When this option is selected, the Query Optimizer creates and displays the execution plan, but does not actually run it. Notice that this is referred to as an "estimated" execution plan. This means the resulting query plan may not be the exact one the Query Optimizer will use if the query is really run. It will be close though, and it is a good tool if you are doing a lot of experimenting with long running queries. Once you have the query fine-tuned using this feature, you can turn it off and then run it for real, and see how it actually performs.

Show Server Trace

The Show Server Trace can be used to help performance tune queries, stored procedures, or Transact-SQL scripts. What it does is display the communications sent from the Query Analyzer (acting as a SQL Server client) to SQL Server. This is the same type of information that is captured by the SQL Server 2000 Profiler, described later in this article. The main difference is that you only see the communications to SQL Server that are instigated by the query or script you are running in Query Analyzer.

The Show Server Trace feature can be turned on from the Query menu on the main menu of Query Analyzer, and must be turned on before the query is executed. Once the query is executed, the results of the trace are displayed in a new window available from a tab that appears below the query results window on the screen.

The results of the trace are in the form of rows, with each row representing a distinct communication from Query Analyzer to SQL Server. Each row includes the text of the communication, such as Transact-SQL code; the Event Class, which describes the type of communication being sent; the duration of the communication; the amount of CPU time used, and how many reads or writes that were performed for the event. This information can be very valuable when analyzing query performance, and when comparing the performance of one variation of a query against another.

Show Client Statistics

Like the Show Server Trace feature, the Show Client Statistics can be very helpful when performance tuning queries, stored procedures, and scripts. What this option does is provide you with application profile, network, and time statistics of whatever Transact-SQL you are running in Query Analyzer. This statistics provide additional information you can use to see how efficiently a query is running, and also allows you to easily compare one query against another.

The Show Client Statistics feature can be turned on from the Query menu on the main menu of Query Analyzer, and must be turned on before the query is executed. Once the query is executed, the results of the trace are displayed in a new window that is available from a tab that appears below the query results window on the screen.

The results of the Show Client Statistics feature are a series of three tables, each containing a set of ...