Click here to monitor SSC
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1298 Visits: 332
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

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

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
kbhanu15
kbhanu15
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1298 Visits: 332
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1298 Visits: 332
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

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

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
phil.doensen
phil.doensen
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 173
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45123 Visits: 39922
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 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