August 16, 2011 at 12:11 pm
Ok, what I really mean is, that I want to try some different code to test performance.
When I ran query ver #1, it took 4 minutes.
Then I make a small change (Query ver #2) and it takes 4 seconds.
I re-run #1, and it now takes 4 seconds.
I suspect the plans are cached.
What's the best way to re-run queries to test performance ?
August 16, 2011 at 12:12 pm
Run each multiple times, with I/O and time stats turned on each time. You'll see compile time vs run time when you do that. You need to know both.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 16, 2011 at 12:30 pm
Thanks
What I'm testing is an insert inside a stored procedure, that joins to a table on another server (linked server) if that makes any difference.
Don't try clearing any cache or anything else ?
August 16, 2011 at 12:33 pm
Clearing the cache tests how well it runs if it hasn't been cached. Most of the time, on most servers, the more usual user experience will be with it cached, if it's something that's called with any real frequency. That's why it's important to test both with and without.
You can end up with unchached execution being faster one version, but cached faster on the other, and if it will be cached 99% of the time, which one would you pick?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 16, 2011 at 12:54 pm
Got it.
p.s. I don't know what most of your certifications mean, but I would think you've earned ROFL and LMAO by now.
August 16, 2011 at 1:10 pm
Good idea!
PS: Most of them aren't certifications, and the ones that are, have nothing to do with databases or even computers. They're all applicable abbreviations, but it's meant to be a parody.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 16, 2011 at 5:43 pm
homebrew01 (8/16/2011)
Ok, what I really mean is, that I want to try some different code to test performance.When I ran query ver #1, it took 4 minutes.
Then I make a small change (Query ver #2) and it takes 4 seconds.
I re-run #1, and it now takes 4 seconds.
I suspect the plans are cached.
What's the best way to re-run queries to test performance ?
Despite what most folks think, I've found that the lifetime of cached plans for large, infrequently executed, batch process stored procedures on busy production systems is less than 5 minutes. I've also found that the llifetime for cached data of any magnitude is even less than that. With that in mind, I always try to determine how long something will take on the very first, uncached run.
With that thought in mind, here's a quote from Books Online...
Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.
To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.
Obviously, you don't want to do that to a production system.
--Jeff Moden
August 17, 2011 at 2:55 am
When I ran query ver #1, it took 4 minutes.
Then I make a small change (Query ver #2) and it takes 4 seconds.
I re-run #1, and it now takes 4 seconds.
I suspect the plans are cached.
What's the best way to re-run queries to test performance ?
I tend to use ostress and at times SQLQueryStress
When testing for performance the approach I follow is:
a) First measure the performance taken after cleaning the cache (as Jeff mentioned)
b) After that - run the existing code using one of the utilities above (with varying parameters across mutliple connections)
c) Start a generic load script to simulate normal load on the system
d) Use DMVs/any other tools to capture performance stats for the existing code
Repeat the steps above with the modified version of the code and compare the results.
Most of the time this works out well enough to test out the cached version, the non-cached version and to check what happens under normal environment load.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply