SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
kbhanu15
kbhanu15
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 336
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
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27873 Visits: 13268
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?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
kbhanu15
kbhanu15
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 336
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
kbhanu15
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 336
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
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27873 Visits: 13268
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


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
phil.doensen
phil.doensen
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 195
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
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88634 Visits: 41130
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. :-D

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1480 Visits: 2582
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
Blog: SQLSouth
Twitter: @SQLSouth
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search