Solved

SQL Server 2008 R2 'wake up' issue

Posted on 2011-03-08
4
564 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
select over clause 1 40
Syntax using Declare 4 38
SQL Exceptions 3 39
SQL log file keeps growing despite getting successful log backups 4 34
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

867 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

21 Experts available now in Live!

Get 1:1 Help Now