
-- clear caches
CHECKPOINT				--writes dirty pages to disk
DBCC FREEPROCCACHE		--clears entire plan cache
DBCC DROPCLEANBUFFERS	--clears all clean data cache pages

DECLARE @DBID	smallint
SELECT @DBID = db_id('pubs')
DBCC FLUSHPROCINDB(@DBID)	--clears all clean plan cache for specified database


-- You can see number of executions:
SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.* 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE text NOT LIKE '%sql.text%'
ORDER BY qs.EXECUTION_COUNT DESC
