2 Tidied up the formatting slightly
source | link

The other answers are correct about reasons to not run DBCC FREEPROCCACHE. However, there are also a couple of reasons to do so:

  • Consistency. If you want to compare two different queries or procedures which are attempting to do the same thing in different ways, they're likely to hit the same pages. If you naively run query #1 then query #2, the second may be much faster simply because those pages were cached by the first query. If you clear the cache before each execution, they start on an even footing.
  1. Consistency

If you want to compare two different queries or procedures which are attempting to do the same thing in different ways, they're likely to hit the same pages. If you naively run query #1 then query #2, the second may be much faster simply because those pages were cached by the first query. If you clear the cache before each execution, they start on an even footing.

If you do want to test hot-cache performance, be sure to run the queries several times, alternating, and discard the first couple of runs. Average the results.

  • Worst-case performance. Say you have a query which takes one second against a hot cache but one minute against a cold cache. An optimization which makes the in-memory query 20% slower but the IO-bound query 20% faster could be a big win. During normal operations, no one will notice the extra 200 ms, but if something forces a query to run against disk, taking 48 seconds instead of 60 could save a sale.
  1. Worst-case performance

Say you have a query which takes one second against a hot cache but one minute against a cold cache. An optimization which makes the in-memory query 20% slower but the IO-bound query 20% faster could be a big win: during normal operations, no one will notice the extra 200 ms under normal circumstances, but if something forces a query to run against disk, taking 48 seconds instead of 60 might save a sale.

This is less of a concern on modern systems with tens of gigabytes of memory, and relatively fast SAN and SSD storage, but it still matters. If some analyst runs a massive table scan query against your OLTP database which wipes out half of your buffer cache, storage-efficient queries will get you back up to speed sooner.

The other answers are correct about reasons to not run DBCC FREEPROCCACHE. However, there are also a couple of reasons to do so:

  • Consistency. If you want to compare two different queries or procedures which are attempting to do the same thing in different ways, they're likely to hit the same pages. If you naively run query #1 then query #2, the second may be much faster simply because those pages were cached by the first query. If you clear the cache before each execution, they start on an even footing.

If you do want to test hot-cache performance, be sure to run the queries several times, alternating, and discard the first couple of runs. Average the results.

  • Worst-case performance. Say you have a query which takes one second against a hot cache but one minute against a cold cache. An optimization which makes the in-memory query 20% slower but the IO-bound query 20% faster could be a big win. During normal operations, no one will notice the extra 200 ms, but if something forces a query to run against disk, taking 48 seconds instead of 60 could save a sale.

This is less of a concern on modern systems with tens of gigabytes of memory, and relatively fast SAN and SSD storage, but it still matters. If some analyst runs a massive table scan query against your OLTP database which wipes out half of your buffer cache, storage-efficient queries will get you back up to speed sooner.

The other answers are correct about reasons to not run DBCC FREEPROCCACHE. However, there are also a couple of reasons to do so:

  1. Consistency

If you want to compare two different queries or procedures which are attempting to do the same thing in different ways, they're likely to hit the same pages. If you naively run query #1 then query #2, the second may be much faster simply because those pages were cached by the first query. If you clear the cache before each execution, they start on an even footing.

If you do want to test hot-cache performance, be sure to run the queries several times, alternating, and discard the first couple of runs. Average the results.

  1. Worst-case performance

Say you have a query which takes one second against a hot cache but one minute against a cold cache. An optimization which makes the in-memory query 20% slower but the IO-bound query 20% faster could be a big win: during normal operations, no one will notice the extra 200 ms under normal circumstances, but if something forces a query to run against disk, taking 48 seconds instead of 60 might save a sale.

This is less of a concern on modern systems with tens of gigabytes of memory, and relatively fast SAN and SSD storage, but it still matters. If some analyst runs a massive table scan query against your OLTP database which wipes out half of your buffer cache, storage-efficient queries will get you back up to speed sooner.

1
source | link

The other answers are correct about reasons to not run DBCC FREEPROCCACHE. However, there are also a couple of reasons to do so:

  • Consistency. If you want to compare two different queries or procedures which are attempting to do the same thing in different ways, they're likely to hit the same pages. If you naively run query #1 then query #2, the second may be much faster simply because those pages were cached by the first query. If you clear the cache before each execution, they start on an even footing.

If you do want to test hot-cache performance, be sure to run the queries several times, alternating, and discard the first couple of runs. Average the results.

  • Worst-case performance. Say you have a query which takes one second against a hot cache but one minute against a cold cache. An optimization which makes the in-memory query 20% slower but the IO-bound query 20% faster could be a big win. During normal operations, no one will notice the extra 200 ms, but if something forces a query to run against disk, taking 48 seconds instead of 60 could save a sale.

This is less of a concern on modern systems with tens of gigabytes of memory, and relatively fast SAN and SSD storage, but it still matters. If some analyst runs a massive table scan query against your OLTP database which wipes out half of your buffer cache, storage-efficient queries will get you back up to speed sooner.