Using SQL Server Query Analyzer

The Query Analyzer can be used to recommend indexes for specific tables. By entering a query into the Query Analyzer, and running the "Perform Index Analysis" option in 7.0, or the "Index Tuning Wizard" option in 2000, the query will be reviewed, and if appropriate, one or more indexes will be recommended by the Index Wizard. If an index is recommended, Query Analyzer can automatically create the index for you, if you like. This tool will not point out indexes that are not used, nor will it affect any existing indexes.

One way I take advantage of this tool when tuning a query is to run this option as a first step, before I really begin any analysis and work on the query. This way, if there are any obvious indexes needed, they will be found quickly, saving me a little time. Once this step is out of the way, then I run an execution plan of the query, and then look for other ways to tune the query. [7.0, 2000] Updated 1-10-2006

*****

Besides the "Show Execution Plan" option of the Query Analyzer, you can run one or more of the following commands in the Query Analyzer to turn on various performance-related information when tuning queries. These commands are only turned on for the current connection, and are turned off when the connection is broken. They can also be turned off manually by specifying "OFF" instead of "ON".

The results of these commands are displayed after a query is executed from Query Analyzer, generally after the results of the query are displayed. They include:

• SET SHOWPLAN_TEXT ON: Returns estimated (not actual, as the query is not run) detailed information on how the query will run.
• SET SHOWPLAN_ALL ON: Returns estimated (not actual, as the query is not run) detailed information on how the query will run, plus additional information, such as the estimated number of rows, I/O, CPU, and the average size of a the query.
• SET STATISTICS IO ON: Shows the number of scans, logical reads, and physical reads performed during the query. Returns actual data based on a query that has run.
• SET STATISTICS TIME ON: Shows the amount of time (in milliseconds) needed to parse, compile, and execute a query. Returns actual data based on a query that has run.
• SET STATISTICS PROFILE ON: Shows a recordset that represents a profile of the query execution. Returns actual data based on a query that has run.

You will not want to run the first two commands listed above at the same time as the others because the first two commands are based on estimated data, while the last three are based on real data.

If you are using SQL Server 2000, using these commands are less needed as you can get all of the same type of data other ways from within Query Analyzer.

[6.5, 7.0, 2000] Updated 1-10-2006

*****

The Show Server Trace option of the SQL Server 2000 Query Analyzer can be used to help performance tune queries, stored procedures, or Transact-SQL scripts. This option is used to 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.

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 available from a new screen that is 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. [2000] Updated 1-10-2006

*****

The Client Statistics option of the SQL Server 2000 Query Analyzer can be very helpful when performance tuning queries, stored procedures, and scripts. This option provides you with application profile, network, and time statistics of whatever Transact-SQL you are running in Query Analyzer. These 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 the SQL Server 2000 Query Analyzer, and must be turned on before the query is executed. Once the query is executed, the results of the trace are available from 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, 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 road maps to how a particular query is running, helping you to diagnose performance-related problems. [2000] Updated 1-10-2006

*****

The SQL Server 2000 Query Analyzer has a tool called "Manage Indexes". It allows you to add, edit, or delete indexes from any table using a GUI interface directly from Query Analyzer. This makes it convenient to experiment with how various indexes affect the performance of queries you are testing inside Query Analyzer. [2000] Updated 1-10-2006