Solved

SQL Server 2008 R2 'wake up' issue

Posted on 2011-03-08
4
553 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:hindersaliva
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 167 total points
ID: 35069614
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
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 167 total points
ID: 35072037
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35072091
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
 
LVL 13

Assisted Solution

by:dwkor
dwkor earned 166 total points
ID: 35072128
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now