Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2008 R2 'wake up' issue

Posted on 2011-03-08
4
Medium Priority
?
628 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

721 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