Saturday, March 23, 2013

Multiple options to Clear query cache and buffer pool


While tuning queries it becomes important to run the existing query and obtain initial execution time.
Changes are then made to the query or underlying indexes or statistics. The modified query is executed and the execution time is compared with the initial value.
The query gets compiled the very first time it is executed and the query plan is cached. Similarly the results of the first execution are stored in the buffer pool.

As a result the second time the test query is executed, unless the query is recompiled the execution time would be faster and undependable.
With every change the query should be executed with the exact same set of conditions as the initial execution. How can this be done?

This can be achieved by clearing the plan cache and buffer pool using these commands

CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Note: Do not run these commands on a production server. These commands will cause  queries to be recompiled and it may adversely impact performance. This changes clear plan cache and buffer pool for the entire instance.

DBCC FREEPROCCACHE

Removes all cached query plans and execution contexts from the plan cache,
Removes a specific plan from the plan cache by specifying a plan handle or SQL handle or
Removes all cache entries associated with a specified resource pool.
Example:
SELECT * FROM Person.Address;
GO
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';
GO
dbcc freeproccache(0x060005123RGCA270E306E5EF00200000001000000000000000000000000000000000000000000000000000000)

DBCC FREESYSTEMCACHE
Removes all plans from all caches or from a specified Resource Governor pool.
Example: DBCC FREESYSTEMCACHE('SQL Plans')

DBCC FREESESSIONCACHE
Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

DBCC FLUSHPROCINDB( db_id )
Removes all cached plans from the plan cache for a particular database.
Example:
       Declare @dbid  int = DB_ID()
       DBCC FLUSHPROCINDB(@dbid)

DBCC DROPCLEANBUFFERS
Removes all clean buffers from the buffer pool. Issue a checkpoint to write all dirty pages to disk and then execute the command.

No comments:

Post a Comment