This article explores how to identify significant changes in SQL Server query execution plans by leveraging dynamic management views (DMVs) and implementing a monitoring solution using C#.NET.
Utilizing DMVs for Execution Plan Change Detection
Several DMVs provide valuable insights into query execution statistics and plan details. Key views and example queries include:
-
sys.dm_exec_query_stats: This DMV offers performance metrics for executed queries, such as CPU time, elapsed time, and execution counts.
SELECT sql_handle, statement_start_offset, statement_end_offset, creation_time, last_execution_time, execution_count, total_worker_time, total_elapsed_time FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC; -
sys.dm_exec_sql_text: Used in conjunction with
sql_handlefromsys.dm_exec_query_stats, this allows retrieval of the actual SQL statement text.SELECT qs.sql_handle, st.text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st; -
sys.dm_exec_cached_plans: This view contains statistics about cached execution plans, including their size and usage counts.
SELECT cacheobjtype, usecounts, objtype, size_in_bytes, creation_time, last_use_time FROM sys.dm_exec_cached_plans ORDER BY usecounts DESC; -
sys.dm_exec_query_plan: Combined with
sys.dm_exec_sql_text, this DMV retrieves the XML execution plan for a given query.SELECT qp.query_plan, st.text FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;
To detect execution plan regressions, these queries should be executed periodically. Storing the results in a persistent table allows for historical comparison. Monitoring changes in total_worker_time or total_elapsed_time can indicate a plan alteration.
Since sys.dm_exec_query_stats data can be reset by SQL Server, it's best practice to log this information to a durable table for historical analysis. Comprehensive detection may require correlating data from multiple DMVs.
C# Implementation for Monitoring
1. Persistent Storage Table
Create a SQL Server table to store query execution statistics fetched from the DMVs.
CREATE TABLE QueryExecutionMetrics (
RecordID INT IDENTITY(1,1) PRIMARY KEY,
SqlHandle VARBINARY(64),
StatementStartOffset INT,
StatementEndOffset INT,
PlanCreationTime DATETIME,
LastExecution DATETIME,
ExecutionFrequency BIGINT,
CumulativeCPUTime BIGINT,
CumulativeDuration BIGINT,
FullSqlText NVARCHAR(MAX),
ExecutionPlan XML,
SnapshotTimestamp DATETIME DEFAULT GETDATE()
);
2. C# Monitoring Code
The following C# code demonstrates connecting to SQL Server, executing DMV queries, and persisting the data.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
public class QueryPlanMonitor
{
private readonly string _dbConnectionString;
public QueryPlanMonitor(string connectionString)
{
_dbConnectionString = connectionString;
}
public void CaptureQueryStatistics()
{
using (var connection = new SqlConnection(_dbConnectionString))
{
connection.Open();
string dmvQuery = @"
SELECT
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.total_worker_time,
qs.total_elapsed_time,
st.text AS FullSqlText,
qp.query_plan AS ExecutionPlan
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
qs.total_worker_time DESC;";
using (var command = new SqlCommand(dmvQuery, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var sqlHandleBytes = (byte[])reader["sql_handle"];
var startOffset = (int)reader["statement_start_offset"];
var endOffset = (int)reader["statement_end_offset"];
var createTime = (DateTime)reader["creation_time"];
var lastExecTime = (DateTime)reader["last_execution_time"];
var execCount = (long)reader["execution_count"];
var workerTime = (long)reader["total_worker_time"];
var elapsedTime = (long)reader["total_elapsed_time"];
var sqlText = reader["FullSqlText"] as string ?? string.Empty;
var planXml = reader["ExecutionPlan"] as string ?? string.Empty;
PersistQueryData(sqlHandleBytes, startOffset, endOffset, createTime, lastExecTime, execCount, workerTime, elapsedTime, sqlText, planXml);
}
}
}
}
}
private void PersistQueryData(byte[] sqlHandle, int statementStartOffset, int statementEndOffset, DateTime creationTime, DateTime lastExecutionTime, long executionCount, long totalWorkerTime, long totalElapsedTime, string sqlText, string queryPlan)
{
using (var connection = new SqlConnection(_dbConnectionString))
{
connection.Open();
string insertSql = @"
INSERT INTO QueryExecutionMetrics (
SqlHandle,
StatementStartOffset,
StatementEndOffset,
PlanCreationTime,
LastExecution,
ExecutionFrequency,
CumulativeCPUTime,
CumulativeDuration,
FullSqlText,
ExecutionPlan
) VALUES (
@SqlHandle,
@StatementStartOffset,
@StatementEndOffset,
@CreationTime,
@LastExecutionTime,
@ExecutionCount,
@TotalWorkerTime,
@TotalElapsedTime,
@SqlText,
@QueryPlan
);";
using (var command = new SqlCommand(insertSql, connection))
{
command.Parameters.AddWithValue("@SqlHandle", sqlHandle);
command.Parameters.AddWithValue("@StatementStartOffset", statementStartOffset);
command.Parameters.AddWithValue("@StatementEndOffset", statementEndOffset);
command.Parameters.AddWithValue("@CreationTime", creationTime);
command.Parameters.AddWithValue("@LastExecutionTime", lastExecutionTime);
command.Parameters.AddWithValue("@ExecutionCount", executionCount);
command.Parameters.AddWithValue("@TotalWorkerTime", totalWorkerTime);
command.Parameters.AddWithValue("@TotalElapsedTime", totalElapsedTime);
command.Parameters.AddWithValue("@SqlText", sqlText);
command.Parameters.AddWithValue("@QueryPlan", queryPlan);
command.ExecuteNonQuery();
}
}
}
public static void Main(string[] args)
{
// Replace with your actual connection string
string connectionString = "Server=your_server;Database=your_database;Integrated Security=True;";
var monitor = new QueryPlanMonitor(connectionString);
try
{
monitor.CaptureQueryStatistics();
Console.WriteLine("Query statistics captured successfully.");
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
3. Scheduling and Comparison
Schedule the C# application to run at regular intervals (e.g., using Windows Task Scheduler or as a background service). The collected data in QueryExecutionMetrics can then be analyzed to detect plan regressions.
4. Regression Detection Queries
SQL queries can be used to compare historical data and identify significant shifts in query performance.
SELECT
a.FullSqlText,
a.CumulativeCPUTime AS PreviousCPUTime,
b.CumulativeCPUTime AS CurrentCPUTime,
a.CumulativeDuration AS PreviousDuration,
b.CumulativeDuration AS CurrentDuration
FROM
QueryExecutionMetrics a
JOIN
QueryExecutionMetrics b ON a.SqlHandle = b.SqlHandle
AND a.StatementStartOffset = b.StatementStartOffset
AND a.StatementEndOffset = b.StatementEndOffset
WHERE
a.SnapshotTimestamp = '2023-10-26 00:00:00' -- Replace with your earlier snapshot time
AND b.SnapshotTimestamp = '2023-10-27 00:00:00' -- Replace with your later snapshot time
AND (
ABS(a.CumulativeCPUTime - b.CumulativeCPUTime) > 100000 -- Example threshold in microseconds
OR ABS(a.CumulativeDuration - b.CumulativeDuration) > 100000 -- Example threshold in microseconds
);
This query highlights queries where CPU time or elapsed time has changed substantially between two recorded snapshots, indicating a potential query plan regression.