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 statistics on how the query performed. For example, some of the statistics include:
-
Rows affected by INSERT, UPDATE, and DELETE statements
-
Rows affected by SELECT statements
-
Number of User Transactions
-
Number of server roundtrips
-
Number of bytes sent
-
Cumulative client processing time
And many others. These statistics can be valuable roadmaps to how a particular query is running, helping you to diagnose performance-related problems.
Manage Indexes
The SQL Analyzer Manage Index tool is not designed to help you diagnosis performance, but it allows you to easily experiment with your table’s indexes as you are fine-tuning your queries using the other tools we have already described. The Manage Indexes option is available from the Tools menu, and allows you to add, edit, or delete indexes on any table. This way, you can experiment with indexing, and test your indexes, all in one central location.