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

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; */

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.

Rate Your Experience

: 89 : 0