Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again

Question

I have a stored procedure that returns a lot of data for a business dashboard. On first load it takes 18 seconds, after that it takes 0 seconds, even if I change the input parameters at random it stays at 0 seconds and returns the correct results.

If I wait about three minutes it goes back to 18 seconds. This is not acceptable for the user and I have exhausted every option I know of.

Why would it be slow on the first load but instant after? I understand it may be recompiling the execution plan so on each query I added OPTION (KEEP PLAN); with no effect. My stored procedure does not have 'with recompile'. I downloaded 'datawizard sql profiler' and set up a trace to show recompiles but it does not show any, even when I have 'with recompile' in the query.

My only other thought is that mssql is doing some kind of result caching, but the query runs fast even when I change the parameters.

Why would it take 18 seconds, then 0, then 18 seconds again after I wait for a while?

Answer 1

The issue was the setting "Auto close" was set to true on a series of databases on the server. Even though the databases were unrelated their "auto close" setting was flushing all execution plans on the server.

Here are the types of log issues you will see with auto_close set to true:

SQL Server has encountered 24 occurrence(s) of     cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 24 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 24 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

As you can see in this article, NEVER SET AUTO CLOSE TO TRUE!!! http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases

Answer 2

It's almost certainly a matter of the data for the first run are not in the cache and have to be read from disk. After that first run then the data is in cache and the query can run much more quickly. After several minutes other data needs to be in the cache and so the data for this query get's pushed out.

There is a simple way to test this. Run the following

SET STATISTICS IO ON
GO
EXEC stored_procedure

Then check the output. It will look something like this:

Table 'syspalvalues'. Scan count 0, logical reads 0, physical reads 240, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 0, logical reads 0, physical reads 40, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysdbreg'. Scan count 1, logical reads 0, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Note that all of the reads are physical reads

Table 'syspalvalues'. Scan count 0, logical reads 240, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 0, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysdbreg'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Note that the reads are now logical reads.

So having resolved why the query is slowing down you will want to fix it. There may be other ways but adding memory to the server is probably the simplest. If there is more memory then the cache will be bigger and more data can stay in it longer.

There is probably a way to force the data to stay in cache but I don't know it off the top of my head. I'll do some looking around though.

EDIT: You might also consider looking at page life expectancy for the server. My guess is that it will be fairly low meaning that the pages of data are not being held very long in memory.