INF: Assessing Query Performance Degradation

ID: Q167610

The information in this article applies to:

SUMMARY

Microsoft SQL Server's cost-based optimizer evaluates the cost of each query based on various parameters available to it at compile and execution time. The goal of the optimizer is to minimize logical and physical page reads and formulate the most efficient plan. Often times it becomes necessary to compare query performance between different service packs or versions of Microsoft SQL Server.

The steps below outline the basic steps to follow in such comparisons. It is assumed that these manipulations are done in a test environment, and the databases are assumed to be structurally consistent (that is, DBCC CHECKDB, DBCC NEWALLOC and DBCC TEXTALL show no errors).


MORE INFORMATION

Step 1

Isolate the query in question from your application. Apart from the SHOWPLAN, you must enable STATISTICS IO and STATISTICS TIME before running the query (refer to the Books Online for more information about setting these three set statements on). You must run UPDATE STATISTICS on all objects involved in the query. If the query is in the form of a stored procedure or view, it must be re-created and rerun.

Statistical significance is important in this instance, because metrics are at the heart of the problem being analyzed. Consequently, you need to run the query in question several times, as the required data in the cache may affect query performance.

For a valid comparison, the queries must be the same, and must be running against the same data set.

There must be no change in the type and number of indexes or the number of data pages. If you need to move the database to another server to run additional tests, you can retain the indexes and number of data pages by backing up and restoring the database.

Step 2

Compare the query on the same hardware, firmware, operating system version, and identical SQL Server configuration. The amount of memory allocated for SQL Server, the number of processors, and other hardware configurations may affect the running time of the query.

If you are comparing performance between SQL Server service packs, it may be necessary to install one service pack, run the query (as in step 1 above), collect the output, and then repeat the tests with the original service pack.

If you are comparing performance between versions, ideally, both versions should be loaded on the same computer and run one at a time. To do this, you can load the different versions into different directories. You can use the RegistryRebuild option of setup to install registry entries for the particular version being loaded.

For example, if you are comparing SQL Server 6.0 performance against SQL Server 6.5 for a particular query, do the following:
  1. Install SQL Server 6.0 on the computer, and assuming the original database is from SQL Server 6.0, run the query (as in step 1 above).


  2. Collect the output.


  3. Stop the server and remove registry entries for SQL Server 6.0.


  4. Install SQL Server 6.5 in a different directory and run the query again, as in step 1 above.


Step 3

Review the output collected and compare the showplans. If the showplans are identical and the logical and physical reads seem reasonably the same but the query takes longer, look at and compare the parse and compile times (the SQL Server parse and compile time is: cpu time = xx ms).

Also determine the execution time (the SQL Server execution time is: cpu time = xx ms; elapsed time = xx ms).

Save all your findings. If there is a considerable amount of degradation in the compile time or execution time of a query when using identical plans, the optimizer may have incorrect estimates. This may warrant further study, and a technician may be able to further assist you.

You can use trace flags 302 and 310 in analyzing the query. You can set these flags by doing the following before running query: 

   DBCC TRACEON(3604,302,310)

Oftentimes such comparisons reveal that the showplans are different. This in itself does not mean the optimizer is not doing its job properly. All this means is that the optimizer chose a plan whose cost was higher. If the plans are different, you can still attempt to tune the query so that the optimizer attempts to use (or is forced to use) the plan you want. Sometimes you will have to accept the plan chosen by the optimizer. You can attempt to tune the query by reading the good query plan, identifying the changes between the plans, and making the necessary changes to the query generating the bad plan. You can achieve this by forcing the indexes that were used in the good plan or by forcing query plans. Please review the section Analyzing queries in Books Online for further information. 


Keywords          : kbusage SSrvGen 
Version           : 4.2 6.0 6.5
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 9, 1999