• 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]