Per Database:
SELECT TOP 25
databases.[Name],
Object_Name(dm_exec_query_plan.objectid) as 'Object Name',
dm_exec_sql_text.[text] AS 'TSQL Text',
dm_exec_query_stats.execution_count,
dm_exec_query_stats.last_execution_time,
dm_exec_cached_plans.cacheobjtype,
dm_exec_cached_plans.objtype,
CAST(CAST(dm_exec_query_stats.total_worker_time AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as cpu_per_execution_µs,
CAST(CAST(dm_exec_query_stats.total_logical_reads AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as logical_reads_per_execution_µs,
CAST(CAST(dm_exec_query_stats.total_elapsed_time AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as elapsed_time_per_execution_µs,
dm_exec_query_stats.creation_time,
dm_exec_query_stats.total_elapsed_time,
dm_exec_query_stats.max_elapsed_time,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.max_worker_time AS max_cpu_time,
dm_exec_query_stats.total_logical_reads,
dm_exec_query_stats.max_logical_reads,
dm_exec_query_stats.total_physical_reads,
dm_exec_query_stats.max_physical_reads,
dm_exec_query_plan.query_plan,
dm_exec_cached_plans.size_in_bytes
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases ON dm_exec_sql_text.dbid = databases.database_id
INNER JOIN sys.dm_exec_cached_plans ON dm_exec_cached_plans.plan_handle = dm_exec_query_stats.plan_handle
WHERE databases.name = ''
ORDER BY 'total_elapsed_time' DESC;
Get the SQL_Handle, Plan_Handle, Query_Hash, Query_Plan_Hash
SELECT sql_handle, plan_handle, execution_count, query_hash, query_plan_hash
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.plan_handle) AS sql
WHERE sql.text LIKE N'%%';
Per SQL Text or Per Stored Procedure
SELECT TOP 25
databases.Name,
Object_Name(dm_exec_query_plan.objectid) as 'Object Name',
dm_exec_sql_text.text AS 'TSQL Text',
dm_exec_query_stats.execution_count,
dm_exec_query_stats.last_execution_time,
CAST(CAST(dm_exec_query_stats.total_elapsed_time AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as elapsed_time_per_execution_µs,
CAST(CAST(dm_exec_query_stats.total_worker_time AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as cpu_per_execution_µs,
CAST(CAST(dm_exec_query_stats.total_logical_reads AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as logical_reads_per_execution,
dm_exec_query_stats.last_dop,
dm_exec_query_stats.last_used_threads,
--dm_exec_query_stats.last_spills, --Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3
dm_exec_query_stats.sql_handle,
dm_exec_query_stats.plan_handle,
dm_exec_query_stats.query_hash,
dm_exec_query_stats.query_plan_hash,
dm_exec_query_stats.creation_time,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.total_elapsed_time,
dm_exec_query_stats.total_logical_reads,
dm_exec_query_stats.total_physical_reads,
dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases ON dm_exec_sql_text.dbid = databases.database_id
WHERE dm_exec_sql_text.text LIKE '%%'
--AND Object_Name(dm_exec_query_plan.objectid) IS NOT NULL;
To Remove the specific plan from the cache
DBCC FREEPROCCACHE (Plan_Handle);
Get Plan Attributes: Set options value that the plan was compiled with. (Causes multiple execution plans to be generated by different calls to the same stored procedure)
SELECT p.plan_handle, p.usecounts, p.size_in_bytes,
set_options = MAX(a.value)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a
WHERE t.objectid = OBJECT_ID(N'dbo.')
AND a.attribute = N'set_options'
GROUP BY p.plan_handle, p.usecounts, p.size_in_bytes
Get Plan Attributes: Schema (Casues multiple execution plans to be generated by different calls to the same stored procedure)
SELECT t.[text], p.size_in_bytes, p.usecounts,
[schema_id] = pa.value,
[schema] = s.name
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa
INNER JOIN sys.schemas AS s ON s.[schema_id] = pa.value
WHERE t.[text] LIKE N'%%'
AND pa.attribute = N'user_id';
Get SET options for current sessions
SELECT * FROM sys.dm_exec_sessions
Other things to look out or are case and whitespace since SQL Server hashes the query text - thuss every character in the query text is important to avoid getting multiple plans for the same query/stored procedure.
|
|
|
|
|
|