Query Speed - First Run Slow - Next Run Very Fast

Posted on 2006-05-25
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

    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
    > 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.

    LVL 1

    Author Comment

    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.

    LVL 44

    Assisted Solution

    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

    Yes there is a cache

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

    LVL 21

    Accepted Solution

    >>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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now