INF: Assessing Query Performance Degradation

This article was previously published under Q167610
This article has been archived. It is offered "as is" and will no longer be updated.
Microsoft SQL Server's cost-based optimizer evaluates the cost of eachquery based on various parameters available to it at compile and executiontime. The goal of the optimizer is to minimize logical and physical pagereads and formulate the most efficient plan. Often times it becomesnecessary to compare query performance between different service packs orversions 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, andthe databases are assumed to be structurally consistent (that is, DBCCCHECKDB, DBCC NEWALLOC and DBCC TEXTALL show no errors).

Step 1

Isolate the query in question from your application. Apart from theSHOWPLAN, you must enable STATISTICS IO and STATISTICS TIME beforerunning the query (refer to the Books Online for more information aboutsetting these three set statements on). You must run UPDATE STATISTICS onall objects involved in the query. If the query is in the form of a storedprocedure or view, it must be re-created and rerun.

Statistical significance is important in this instance, because metrics areat the heart of the problem being analyzed. Consequently, you need to runthe query in question several times, as the required data in the cachemay affect query performance.

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

There must be no change in the type and number of indexes or the number ofdata pages. If you need to move the database to another server to runadditional tests, you can retain the indexes and number of data pages bybacking 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 forSQL Server, the number of processors, and other hardware configurations mayaffect the running time of the query.

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

If you are comparing performance between versions, ideally, both versionsshould 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 usethe RegistryRebuild option of setup to install registry entries for theparticular version being loaded.

For example, if you are comparing SQL Server 6.0 performance against SQLServer 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 areidentical and the logical and physical reads seem reasonably the same butthe 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: cputime = xx ms; elapsed time = xx ms).

Save all your findings. If there is a considerable amount of degradation inthe 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 setthese flags by doing the following before running query:
   DBCC TRACEON(3604,302,310)				

Oftentimes such comparisons reveal that the showplans are different. Thisin itself does not mean the optimizer is not doing its job properly. Allthis means is that the optimizer chose a plan whose cost was higher. If theplans are different, you can still attempt to tune the query so that theoptimizer 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 canattempt to tune the query by reading the good query plan, identifying thechanges between the plans, and making the necessary changes to the querygenerating the bad plan. You can achieve this by forcing the indexes thatwere used in the good plan or by forcing query plans. Please review thesection Analyzing queries in Books Online for further information.

Article ID: 167610 - Last Review: 01/16/2015 18:49:58 - Revision: 3.0

  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • kbnosurvey kbarchive kbhowto kbusage KB167610