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?