Using SQL Server Query Analyzer

by Kiran 18. February 2011 06:11

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. 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.

Tags:

General

Category

Recent Posts

Tag cloud