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

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?

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

StackExchange Database Administrators Q#80676, answer score: 5