
-- 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 '%dm_exec%'
ORDER BY qs.EXECUTION_COUNT DESC

SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text(plan_handle)
   WHERE [text] NOT LIKE '%dm_exec%'
   AND cacheobjtype = 'Compiled Plan'
ORDER BY p.usecounts DESC


-- execute query on DMVs to find 
-- operations in query plans

CREATE PROCEDURE LookForPhysicalOps (@op VARCHAR(30))
AS
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 query_plan.exist('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
') = 1
GO

EXEC LookForPhysicalOps 'Clustered Index Scan'
EXEC LookForPhysicalOps 'Nested Loops'
EXEC LookForPhysicalOps 'Table Scan'



