Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reason behind, if same query run several times the results are comming in less time Expand / Collapse
Author
Message
Posted Wednesday, January 22, 2014 1:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:25 AM
Points: 988, Visits: 292
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
Post #1533498
Posted Wednesday, January 22, 2014 2:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:57 AM
Points: 13,292, Visits: 11,079
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1533502
Posted Wednesday, January 22, 2014 2:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:25 AM
Points: 988, Visits: 292
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.

Post #1533505
Posted Wednesday, January 22, 2014 5:31 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:25 AM
Points: 988, Visits: 292
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
Post #1533574
Posted Wednesday, January 22, 2014 5:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:57 AM
Points: 13,292, Visits: 11,079
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1533577
Posted Thursday, January 23, 2014 8:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:34 PM
Points: 9, Visits: 65
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.

Post #1534321
Posted Friday, January 24, 2014 3:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534670
Posted Friday, January 24, 2014 3:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 1,061, Visits: 2,577
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
Post #1534676
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse