Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Query Speed - First Run Slow - Next Run Very Fast

Posted on 2006-05-25
Medium Priority
Last Modified: 2008-01-16
Hi Experts,

I have a number of tables. I have just created and popluated and created indexes on the NumberX tables.

When I first run the query below it took 5116 mseconds. Any further executions of the query took 16 mseconds.
select top 10 a.[id], a.num4,b.number from numbers4 a, numbers b where a.num4 = '2011' and
a.[id] = b.[id]
When I changed the numbers4 table in the query to test again all the other tables I got the same results.

I need the query to run at 16 ms the first time it is requested.
Is there some kind of Cache going on?
If i reboot the server when my application runs for the first time will I get this performace on the first query?

Question by:Stephent
LVL 44

Expert Comment

ID: 16759808
Hi Stephent,

you are correct it maintains a cache SQL Server in memory with recently accessed data pages, as well as query plan for recently submitted queries and invoked stored procedure

this is what you see when you run a query or stored procedure once it will be faster on the next run it retrieves the result of a query without accessing the disk for frequently accessed tables

so yes after a server reboot the chache will be cleared and you'll get the performance hit on the first query

share what you know, learn what you don't
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 16760148
> If i reboot the server when my application runs for the first time will I get this performace on the first query?
Once you restart the machine the sql server has to again create an execution Plan and your cache will be cleared.

Without restarting also you can test this

DBCC FREEPROCCACHE  --Removes all elements from the procedure cache.
DBCC DROPCLEANBUFFERS  --Removes all clean buffers from the buffer pool.


Author Comment

ID: 16760263
Thanks guys

So is it the execution plan that is stored in cache or the results? Once the application is running then these queries will search for many different numbers.

What i don't want is every time the query is run with a new search parameter to take 5 seconds.

My thinking is I can run a script after a reboot to force the execution plan into cache thus when the application starts users will get the speedy response.

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

LVL 44

Assisted Solution

bruintje earned 200 total points
ID: 16760282
it will not store the results but the plan and the accessed pages on disk especially for quick retrieval on subsequent likewise queries

not that good on this stuff yet but from what i remember is that sql server keeps tabs on usage statistics of queries and has them ranked by most used in the cache as per above
LVL 50

Expert Comment

ID: 16760449
Yes there is a cache

have you updated / created statistics for the indexes after you've created them?

LVL 21

Accepted Solution

Kevin3NF earned 200 total points
ID: 16760753
>>What i don't want is every time the query is run with a new search parameter to take 5 seconds.<<

Generally speaking, the parameter won't matter, as the stored plan will have been based on the statistics generated for the indexes on the table, which accounts for all the values that the parameter could be.

It should only be slow(ish) for the first execution as the new plan is generated after a reboot.  After that, slowness is likely due to SQL having to pull the actual data pages into the buffer pool that are not already there from previous executions.   If the query is run frequently and the memory allocated to SQL Server is sifficient, the likelihood is greater that the relevant pages will already be there in mem.

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

581 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