Database Administration
- sql-server optimization performance-tuning
- Updated Sun, 25 Sep 2022 17:09:37 GMT
-
Poorly performing stored procedure using cached data while testing
I have a stored procedure that takes about 15 seconds to run the first time, and the subsequent runs take 1 to 2 seconds. If I wait for an hour and run it again then it takes 15 seconds again.
I'm guessing that it's using the cached data in the buffer pool on the subsequent runs, whereas the first time it has to load data from the disk to the buffer pool. I'm trying to tune this stored procedure, but after the first run I'm not able to test my changes since it takes only 1 to 2 seconds.
I know that I can use the DBCC DROPCLEANBUFFERS command to free the cache and run my stored procedure, but I'm not allowed to clear the cache at my work. I tried WITH RECOMPILE as well, but that only creates a new plan, but still uses the cached data. Is there another way of forcing the stored procedure to not use the cached data?
Solution
No. Save the CPU and wait stats from the actual execution plan where you're seeing the 15sec duration. Then work to minimize CPU and Reads. If you see that X reads = 15sec of PAGEIOLATCH waits, then you can get a reasonable estimate of the impact of reducing reads.
The important thing is to discover and remediate the queries that are causing the buffer pool to churn. Your query is probably at least partially to blame, but you need to discover why this query's data is not sticking in the cache. May be other queries, may need more memory, or better compression, or to avoid table scans, etc.