September 21, 2013 at 4:30 am
The important thing is not whether you do DBCC or not, the important thing is that you understand the ramifications of what you are doing.
For instance, if you want to measure the performance of a query that you expect to run regularly and to have the one and same plan, you should not empty the cache and measure performance from a cold cache. It may be a good thing to empty the cache, if you are testing on your workstation, and set a low limit for Max Server Memory (to prevent SQL Server from taking over the machine). But in this case you should discard the first execution.
On the other hand, if you have a query which you expect execute only once per day, you may prefer to measure from a cold cache, because that is likely to be the actual production scenario.
In this case, the issue was about compilation. Since you are trying different triggers, the first execution will always include compilation, so whether you clear the plan cache does not really matter. What is important is that you should discard the first execution. If you have also cleared the buffer cache, SQL Server will need to read the statistics table into memory which may add insult to injury. You should still discard that first execution.
I should add that compilation times should not always be discarded. An obvious case is if a query has OPTION (RECOMPILE), in which case it will be recompiled every time. But there are also other situations where recompilations are likley, for instance queries with temp tables (because the statistics for the temp table gets updated).
Measuring performance is by no means trivial. There are plenty of traps to walk into. I know - I fallen into more than one myself.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 23, 2013 at 4:01 am
Thanks for helping to understand the times... Just "forgot" the trigger, SPs, functions,... are compiled the first time they're executed...
I'll do a CHECKPOINT and DBCC DROPCLEANBUFFERS to free the data but keep the execution plans and compiled SP and triggers, since that's what will happen.
Thanks again,
Pedro
September 24, 2013 at 6:01 am
PiMané (9/23/2013)
I'll do a CHECKPOINT and DBCC DROPCLEANBUFFERS to free the data but keep the execution plans and compiled SP and triggers, since that's what will happen.
Again, since you are replacing the trigger between the tests, you will see compilation first time anyway. Just discard the first execution.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 24, 2013 at 7:05 am
Erland Sommarskog (9/24/2013)
PiMané (9/23/2013)
I'll do a CHECKPOINT and DBCC DROPCLEANBUFFERS to free the data but keep the execution plans and compiled SP and triggers, since that's what will happen.Again, since you are replacing the trigger between the tests, you will see compilation first time anyway. Just discard the first execution.
Yep. that's what I did. I'm making 10 runs each time and ignoring the 1st execution.
Thanks for all the help,
Pedro
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply