Searching the SQL Server query plan cache
Introduction
Database and SQL Information
Execution Details and Performance Metrics
Cache Removal Strategies
Practical Application and Optimization
SQL Query
T-SQL description
Conclusion
Introduction
In the realm of database management, understanding the performance of cached queries is paramount for optimizing operations and ensuring efficient resource utilization.
In this blog post, we will explore the cached queries within Microsoft SQL Server, exploring their execution details, performance metrics, and cache
removal strategies.
Database and SQL Information
The SQL query provides a comprehensive snapshot of cached queries, including database details (
database ID and
name)
and SQL text (
query text and
plan).
By analyzing this information, database administrators can gain valuable insights into the queries executed within their databases.
Execution Details and Performance Metrics
The SQL query also presents execution details and performance metrics for cached queries, such as
execution count, total elapsed time, average elapsed time, CPU time, physical reads, logical reads, and logical writes.
These metrics offer a holistic view of query performance, facilitating the identification of bottlenecks and areas for optimization.
Cache Removal Strategies
Furthermore, the SQL query outlines cache removal strategies for both SQL and plan handles. By utilizing the
dbcc freeproccache command, administrators can selectively remove cached SQL and
plan handles from the cache,
thereby optimizing cache utilization and enhancing query performance.
Practical Application and Optimization
Armed with this insightful analysis of cached queries, database administrators can proactively identify and address performance issues, optimize query execution, and fine-tune cache utilization to improve overall
database performance and user experience.
SQL Query
You can use different conditions in the ORDER BY clause to achieve the desired result. Individual query plan statistics from the query cache against a given
database. Most times are in microseconds but are only accurate to milliseconds.
select top 100
/* Database */
deco.dbid,
d.name,
/* SQL */
de.sql_handle,
substring(
deco.text,
(de.statement_start_offset/2)+1,
((
case de.statement_end_offset
when -1 then
datalength(deco.text)
else
de.statement_end_offset
end - de.statement_start_offset
)/2) + 1
) as query_text,
/* Plan */
de.plan_handle,
deplan.query_plan,
/* Execution Details */
de.execution_count,
de.creation_time,
de.last_execution_time,
/* Execution Times */
de.total_elapsed_time as total_elapsed_time_us,
de.total_elapsed_time / 1000 as total_elapsed_time_ms,
de.total_elapsed_time / 1000000 as total_elapsed_time_sec,
de.total_elapsed_time / 1000000 / 60 as total_elapsed_time_min,
de.total_elapsed_time / de.execution_count as average_elapsed_time_us,
de.total_elapsed_time / de.execution_count / 1000 as average_elapsed_time_ms,
de.total_elapsed_time / de.execution_count / 1000000 as average_elapsed_time_sec,
de.total_elapsed_time / de.execution_count / 1000000 / 60 as average_elapsed_time_min,
de.last_elapsed_time as last_elapsed_time_us,
de.last_elapsed_time / 1000 as last_elapsed_time_ms,
de.last_elapsed_time / 1000000 as last_elapsed_time_sec,
de.last_elapsed_time / 1000000 / 60 as last_elapsed_time_min,
de.min_elapsed_time as min_elapsed_time_us,
de.min_elapsed_time / 1000 as min_elapsed_time_ms,
de.min_elapsed_time / 1000000 as min_elapsed_time_sec,
de.min_elapsed_time / 1000000 / 60 as min_elapsed_time_min,
de.max_elapsed_time as max_elapsed_time_us,
de.max_elapsed_time / 1000 as max_elapsed_time_ms,
de.max_elapsed_time / 1000000 as max_elapsed_time_sec,
de.max_elapsed_time / 1000000 / 60 as max_elapsed_time_min,
/* Rows */
de.total_rows,
de.total_rows / de.execution_count as average_rows,
de.last_rows,
de.min_rows,
de.max_rows,
/* CPU Time */
de.total_worker_time,
de.total_worker_time / de.execution_count as average_worker_time,
de.last_worker_time,
de.min_worker_time,
de.max_worker_time,
/* Physical Reads */
de.total_physical_reads,
de.total_physical_reads / de.execution_count as average_physical_reads,
de.last_physical_reads,
de.min_physical_reads,
de.max_physical_reads,
/* Logical Reads */
de.total_logical_reads,
de.total_logical_reads / de.execution_count as average_logical_reads,
de.last_logical_reads,
de.min_logical_reads,
de.max_logical_reads,
/* Logical Writes */
de.total_logical_writes,
de.total_logical_writes / de.execution_count as average_logical_writes,
de.last_logical_writes,
de.min_logical_writes,
de.max_logical_writes,
/* Cache Removal */
case
when de.sql_handle is not null then
'dbcc freeproccache(' + convert(varchar(128), de.sql_handle, 1) + ');'
else
'N/A'
end as remove_sql_handle_from_cache,
case
when de.plan_handle is not null then
'dbcc freeproccache(' + convert(varchar(128), de.plan_handle, 1) + ');'
else
'N/A'
end as remove_plan_handle_from_cache
from
sys.dm_exec_query_stats de
cross apply
sys.dm_exec_sql_text(de.plan_handle) as deco
cross apply
sys.dm_exec_query_plan(de.plan_handle) as deplan
inner join
sys.databases d
on deco.dbid = d.database_id
where
d.name = 'dbdocs' -- Your database Name
order by
/* By Total Elapsed Time */
/* total_elapsed_time_us desc; */
/* By Average Elapsed Time */
average_elapsed_time_us desc;
/* By Max Elapsed Time */
/* max_elapsed_time_us desc; */
/* By CPU Time */
/* de.total_worker_time desc; */
/* By Physical Reads */
/* de.total_physical_reads desc; */
T-SQL description
Now, let's break down each component and understand its significance
Column | Description
------------------------------| ------------------------------------------------------
dbid | Application database ID.
name | Application database name.
sql_handle | SQL handle for this statement.
text | SQL text.
execution_count | Number of executions since the plan was last compiled.
creation_time | Time at which the plan was compiled.
last_execution_time | Last time at which the plan started executing.
total_elapsed_time | Total elapsed time for completed executions in microseconds.
average_elapsed_time | Average elapsed time for completed executions in microseconds.
last_elapsed_time | Elapsed time for the most recently completed execution in microseconds.
min_elapsed_time | Minimum elapsed time for completed executions in microseconds.
max_elapsed_time | Maximum elapsed time for completed executions in microseconds.
total_rows | Total number of rows returned by the query.
average_rows | Average number of rows returned by the query.
last_rows | Number of rows returned by the last execution of the query.
min_rows | Minimum number of rows returned for completed executions.
max_rows | Maximum number of rows returned for completed executions.
total_worker_time | Total CPU time consumed by executions of this plan in microseconds.
average_worker_time | Average CPU time consumed by executions of this plan in microseconds.
last_worker_time | CPU time consumed by the most recent execution of this plan in microseconds.
min_worker_time | Minimum CPU time consumed by executions of this plan in microseconds.
max_worker_time | Maximum CPU time consumed by executions of this plan in microseconds.
total_physical_reads | Total physical disk reads by executions of this plan.
average_physical_reads | Average physical disk reads by executions of this plan.
last_physical_reads | Physical disk reads for the most recent execution of this plan.
min_physical_reads | Minimum physical disk reads for all executions of this plan.
max_physical_reads | Maximum physical disk reads for all executions of this plan.
total_logical_reads | Total logical reads (memory) for all executions of this plan.
average_logical_reads | Average logical reads for all executions of this plan.
last_logical_reads | Logical reads for the most recent execution of this plan.
min_logical_reads | Minimum logical reads for all executions of this plan.
max_logical_reads | Maximum logical reads for all executions of this plan.
total_logical_writes | Total logical writes (memory) for all executions of this plan.
average_logical_writes | Average logical writes for all executions of this plan.
last_logical_writes | Logical writes for the most recent execution of this plan.
min_logical_writes | Minimum logical writes for all executions of this plan.
max_logical_writes | Maximum logical writes for all executions of this plan.
plan_handle | Plan handle for this requests's SQL execution plan.
query_plan | Query plan in XML format.
remove_sql_handle_from_cache | Statement to remove the SQL statement from the cache.
remove_plan_handle_from_cache | Statement to remove the plan from the cache.
Conclusion
In conclusion, by leveraging the insights provided by this comprehensive analysis of cached queries, database administrators
can take proactive measures to optimize query performance, enhance resource utilization, and ensure the efficient operation of their Microsoft SQL Server databases.
Related content