Reason behind, if same query run several times the results are comming in less time

  • HI All,

    I have a query if i run first time it is taking 2 minutes,

    if i run the same query in same window it is running in 70 seconds and third time 10 secs and fourth time 0 secs it is giving result.

    1. can any one help me why it is performing like this ?

    2. if i want to reproduce the issue what to do ( i mean if i run the query every time it should give result in same time(like first time 2 minutes) then i can add my performance tuning tips .

    Thanks

    Bhanu

  • SQL Server has caching, but it is really strange that it goes from 2 minutes to subsecond performance.

    You can expect some performance improvement, but that is fenomenal. Care to share the query?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • yes your right if i wait 2 or 3 minutes now i can reproduce the issue ( i mean same time 2 minutes).

    thanks for your answer.

  • HI All,

    TO avoid this issue any database setting to be change ?

    i mean ( if we run the query 1st or 2nd time or 3rd time it should show the same time)

    after that we apply our performance tips and improve .

    thanks

    Bhanu

  • kbhanu15 (1/22/2014)


    HI All,

    TO avoid this issue any database setting to be change ?

    i mean ( if we run the query 1st or 2nd time or 3rd time it should show the same time)

    after that we apply our performance tips and improve .

    thanks

    Bhanu

    This isn't an issue, it is desired behaviour.

    Typically you want queries to perform faster if they are used more, right?

    If you want to do some performance testing, you need to clear the cache yourself:

    Clearing Cache for SQL Server Performance Testing[/url]

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • It's query and data caching that will speed this up the second and third time round.

    I find

    DBCC DROPCLEANBUFFERS

    will probably get you back to the 70sec mark,

    but to get to the original 2 minutes you need to restart the SQL Server.

    Very necessary when you are actually trying to work out if your

    query optimisations are actually any better or you have just wasted

    the last two hours re-writing a script for no result.

  • phil.doensen (1/23/2014)


    It's query and data caching that will speed this up the second and third time round.

    I find

    DBCC DROPCLEANBUFFERS

    will probably get you back to the 70sec mark,

    but to get to the original 2 minutes you need to restart the SQL Server.

    Very necessary when you are actually trying to work out if your

    query optimisations are actually any better or you have just wasted

    the last two hours re-writing a script for no result.

    Just don't do that on a production server. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • phil.doensen (1/23/2014)


    It's query and data caching that will speed this up the second and third time round.

    I find

    DBCC DROPCLEANBUFFERS

    will probably get you back to the 70sec mark,

    but to get to the original 2 minutes you need to restart the SQL Server.

    Very necessary when you are actually trying to work out if your

    query optimisations are actually any better or you have just wasted

    the last two hours re-writing a script for no result.

    You don't need to restart the SQL Server to get a "clean" instance for testing.

    CHECKPOINT will flush all "dirty" pages (pages that have been modified but not yet written to disk) to disk, resulting in a "clean" buffer cache.

    Then, DBCC DROPCLEANBUFFERS will drop all the buffers. Now, you have no data pages in memory, so no queries can take advantage of physical reads performed by previous queries.

    Finally, DBCC FREEPROCCACHE will drop cached execution plans (either all of them or specified ones) so that every query has to compile a new plan and no query can re-use an already compiled plan.

    Other than that, the only other variable to manage is contention, which can really only be avoided by ensuring that no other processes are running on the server at the time.

    Jason Wolfkill

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply