SQL Server 2008 R2 'wake up' issue

I have an Excel application that get data from SQL Server via ADO. All works fine. When I run a query (with a few joins) it takes some 2 seconds. This is great. However, the first time I run the same query typically it takes 10-15 seconds (after which the speed gets as good as 2 sec).

Is there something I can do to make the first time as fast? Is there somewhere I can check? OR is it an unavoidable characteristic when working with database servers - eg. it takes time for the hard drive to start spinning, or similar?
hindersalivaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BrandonGalderisiConnect With a Mentor Commented:
That has to do with SQL plan caching.  If the query isn't run frequently the query plan falls out of the cache and it takes longer for SQL Server to determine how to run the query.  
0
 
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
The delay can be the result of any number of things, including an expired execution plan. If a query hasn't been run in a while, SQL Server has to figure out the best way to do it the first time (in a while) it's asked for - it has to check the statistics, examine the indexes, consider all the options, all before it even begins to work on fetching the results.

One option you have for speeding it up is to schedule it on the server as a SQL Agent Job that runs every few minutes - that way, the statistics stays fresh, and the execution plan stays cached, making every other request relatively quick.
0
 
BrandonGalderisiCommented:
Unnecessarily running a query in order keep the query plan cached is not a good idea.  The plan will stay cached as long as feasible.  Many things affect the size of the plan cache including the number of plans and the amount of memory on the server.  If the plan is falling out of the cache, there's a reason.
0
 
dwkorConnect With a Mentor Commented:
Most likely this is the matter of hot and cold cache. Typically amount of memory available for SQL Server is less than total size of the database. SQL Server tries to keep the data pages that accessed more often in the cache. So if your query is using the data that rarely accessed, those pages need to be read from the disk that takes a lot of time. Subsequent runs use data from the memory that is much much faster.

Plan cache and recompilation is another issue but there is no way it introduce such big impact (from 2 to 15 seconds)

Statistics is another one (synchronous stats update can introduce the issue), but I doubt it's the case.

What you can do is to run this query in management studio with "display execution plan enabled" and "set statistics io on" before the query. Hot/cold cache could be monitored by # of physical reads query produces (physical read - is the read from the disk). If plans are the same and 1 run introduces a lot of physical reads it's definitely the hot/cold cache issue. In such case you can either optimize your query (preferable way) or add more RAM to the server to increase the chance that data would be kept in the memory
0
All Courses

From novice to tech pro — start learning today.