4

I could not find valuable answers or explanations to the following question:

How should a procedure to test query improvements effects look like? Using SSMS I am not absolutely sure about the steps.

Currently I do the following:

  • restore the production DB to an isolated test server, to cut off external influence / usage that falsifies results by additional server usage.
  • rebuild all indexes and statistics.

Then I repetitively execute the following steps:

  • SET STATISTICS IO ON - to get information about IO usage
  • DBCC FREEPROCCACHE; CHECKPOINT; - to free plan cache and consider e.g. index changes when repeating / testing the changes
  • DBCC DROPCLEANBUFFERS; - to avoid usage of cached data to make sure I have a fair comparison on each query execution
  • run the query

After each execution I quickly import all the statistics outputs into an excel sheet, to compare the changes to originals (see picture) as well as the runtime, checking if the number of returned rows still match the original and inspect the new execution plan to see the effects.

Unfortunately this process takes a lot of time because each execution with cleared caches takes about 15 minutes to complete.

  • Is this procedure fine or should I consider changes?
  • Is clearing out the data cache really necessary or is it still a fair comparison to execute the query with the data cache since there is no other users active that could make changes to that cache?

IO STATISTICS in excel

  • 1
    You should also use set statistics time on to measure the execution time as well. You can use statisticsparser.com to parse the output as well. – Kin Shah May 25 '16 at 13:19
  • 1
    If you're just looking into logical I/O (or scans), you don't really need to clear the cache. Optimizing physical I/O is usually impossible, since it depends what else you're running. If you haven't seen statisticsparser.com it might also help you. – James Z May 25 '16 at 13:46
  • You can also recover the query plan (or estimated one) to find better approaches on your query or create new indexes if needed. – Hybris95 Jun 6 '17 at 14:26
1

At the risk of stating the obvious, first decide what you are optimizing for. Almost always it is elapsed time, though you may be trying to reduce CPU use (to reduce license fees) or IO (to avoid a disk upgrade, say). Then find a way to measure that thing, holding other variables constant. Be sure your chosen measure actually measures what you target and not a bunch of other stuff too.

For example, running a query in SSMS and looking at the status bar execution time will show round-trip network time plus plan compilation time plus IO time plus query execution time plus client caching time plus SSMS rendering time. For large result sets and slow networks the overhead and variability may swamp any actual improvement.

Having a completely clean bufferpool and plan cache for each itteration will give great reproducability. It is unlikely to be the actual production scenario, however. Since production is what you're trying to make fast then I'd suggest you test in a production-like environment, with similar memory & disk, plans compiled and the working set in the bufferpool.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.