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

Question

Post reply

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

kbhanu15

SSCarpal Tunnel

Points: 4498
More actions
January 22, 2014 at 1:55 am

#281446

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

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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

kbhanu15 SSCarpal Tunnel Points: 4498 More actions · Answer 2

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.

kbhanu15 SSCarpal Tunnel Points: 4498 More actions · Answer 3

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

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

phil.doensen Mr or Mrs. 500 Points: 501 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

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)

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 7

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