Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server 2008 R2 'wake up' issue

Posted on 2011-03-08
4
Medium Priority
?
637 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 668 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 668 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 664 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Integration Management Part 2
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

926 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