July 12, 2005 at 3:08 am
Background
I'm working on a suite of reports that have been lifted from Access and also need optimising. Typically each report uses 10-12 sql statements to prepare the data in temp tables and process the results. To identify the slow bits I have added datediff calcs to report the speed of each statement so i can concentrate my efforts in the right place. When complete the suite of reports will be run once a day.
The Problem
When I run the sproc first time I get a good indication of which statement is slowing the proc down. But, any subsequent runs are affected by the server cache and it improves performance, significantly, in some cases. This skews any results when testing statements I have adjusted ( with the aid of the query plan ) and can provide misleading results.
The Solution...
...would be, how do I switch off cacheing? OR
am I looking in the wrong place for an answer?
July 13, 2005 at 4:21 am
You cannot switch off the cache.
But what you can do is you can clean up the cache between eauch consequent SP run by using:
DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE
Bye
Gabor
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply