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

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

-- individual caches
DBCC FREESYSTEMCACHE('SQL Plans')
DBCC FREESYSTEMCACHE('TokenAndPermUserStore')

-- individual plans (SQL Server 2008)
-- DBCC FREEPROCCACHE (plan_handle)
DBCC FREEPROCCACHE (0x060007002888622FB8802005000000000000000000000000)

select * from sys.dm_exec_query_stats

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

-- plan attributes

SELECT * FROM 
sys.dm_exec_plan_attributes(0x060009007B126A21B8412005000000000000000000000000)

-- all cached plans
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_cached_plans%'
   AND cacheobjtype = 'Compiled Plan'
ORDER BY p.usecounts DESC

-- executable plans
SELECT cp.*, do.*, p.* FROM sys.dm_exec_cached_plans cp
OUTER APPLY sys.dm_exec_cached_plan_dependent_objects(plan_handle) do 
OUTER APPLY sys.dm_exec_query_plan(plan_handle) p
ORDER BY cp.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'

-- Performance
select total_worker_time/execution_count as avg_cpu_time,
substring(st.text, (qs.statement_start_offset/2) + 1,
((case statement_end_offset 
when -1 
      then datalength(st.text) 
else 
      qs.statement_end_offset 
end 
- qs.statement_start_offset)/2) + 1) as statement_text
, cast(query_plan as xml)
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) 
order by total_worker_time/execution_count desc;
go

-- cache usage per database
SELECT count(*)AS cached_pages_count
    ,CASE database_id 
        WHEN 32767 THEN 'ResourceDb' 
        ELSE db_name(database_id) 
        END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC




