Query Plan read using T-SQL

How to Analyze SQL Server Execution Plans from a Saved XML File

If you have an execution plan saved as an XML file, here's how you can read and analyze it in SQL Server.

📄 Step 1: Load the Execution Plan File

Use the OPENROWSET function to load your .xml file into a variable.

DECLARE @plancontents VARCHAR(MAX), @xml XML;
SET @plancontents = (
    SELECT * 
    FROM OPENROWSET(BULK 'C:\TEMP\MyPlan.XML', SINGLE_CLOB) AS FileData
);

🔍 Step 2: Retrieve Recent Queries Matching a Specific Pattern

You can extract the top 100 recent SQL queries that match a specific text pattern, along with their execution times and plan handles.

SELECT TOP 100 
    execquery.last_execution_time AS [Date Time],
    execsql.TEXT AS [Script],
    execquery.plan_handle
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
WHERE execsql.TEXT LIKE '%WITH Temp_Accounts (%'
ORDER BY execquery.last_execution_time DESC;

🧠 Step 3: Analyze Execution Plan Nodes

Once you have the execution plan XML, you can use XQuery to dig into specific attributes of each node, such as cost estimates and operation types.

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    c.value('.[1]/@PhysicalOp', 'nvarchar(max)') AS PhysicalOp,
    c.value('.[1]/@LogicalOp', 'nvarchar(max)') AS LogicalOp,
    c.value('.[1]/@AvgRowSize', 'nvarchar(max)') AS AvgRowSize,
    c.value('.[1]/@TableCardinality', 'nvarchar(max)') AS TableCardinality,
    c.value('.[1]/@Parallel', 'nvarchar(max)') AS Parallel,
    c.value('.[1]/@EstimateRebinds', 'nvarchar(max)') AS EstimateRebinds,
    c.value('.[1]/@EstimateRewinds', 'nvarchar(max)') AS EstimateRewinds,
    c.value('.[1]/@NodeId', 'nvarchar(max)') AS NodeId,
    c.value('.[1]/@EstimatedTotalSubtreeCost', 'nvarchar(max)') AS EstimatedTotalSubtreeCost,
    c.value('.[1]/@EstimateRows', 'nvarchar(max)') AS EstimateRows,
    c.value('.[1]/@EstimateIO', 'nvarchar(max)') AS EstimateIO,
    c.value('.[1]/@EstimateCPU', 'nvarchar(max)') AS EstimateCPU,
    c.query('.') AS ExecPlanNode
FROM @xml.nodes('//child::RelOp') AS T(c)
ORDER BY EstimatedTotalSubtreeCost DESC;

🔗 Useful References

• Escape XML Special Characters in SQL Server
• Working with XML and NULL Columns in SQL Server
• Querying XML Data Types in SQL Server
• Tracking CPU Utilization Trends in SQL Server

💡 Final Thoughts

By leveraging SQL Server's XQuery capabilities and DMVs, you can gain deep insights into query performance and execution behavior directly from saved plan files. This method is particularly useful when troubleshooting or optimizing complex queries.

CPU utilization trends

Track SQL Server CPU Usage for the Last 256 Minutes

Ever wonder how your SQL Server has been utilizing its CPU over time? This handy T-SQL script allows you to view the CPU utilization history for the last 256 minutes, broken down into one-minute intervals. It's a fantastic way to quickly pinpoint any performance bottlenecks or trends related to CPU usage.

The SQL Script

DECLARE @ts_now BIGINT = (
  SELECT cpu_ticks / (cpu_ticks / ms_ticks)
  FROM sys.dm_os_sys_info WITH (NOLOCK)
  );

SELECT TOP (256) SQLProcessUtilization AS [SQL Server Process CPU Utilization]
 ,SystemIdle AS [System Idle Process]
 ,100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
 ,DATEADD(ms, - 1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
 SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
  ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle]
  ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization]
  ,[timestamp]
 FROM (
  SELECT [timestamp]
    ,CONVERT(XML, record) AS [record]
  FROM sys.dm_os_ring_buffers WITH (NOLOCK)
  WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
    AND record LIKE N'%<SystemHealth>%'
  ) AS x
 ) AS y
ORDER BY record_id DESC
OPTION (RECOMPILE);

How It Works

This script queries `sys.dm_os_ring_buffers`, specifically looking at the `RING_BUFFER_SCHEDULER_MONITOR` type. This ring buffer captures system health events, including CPU utilization. By parsing the XML output from the `record` column, we can extract details like `SystemIdle` (how much CPU is idle), `SQLProcessUtilization` (how much CPU SQL Server is using), and then calculate the CPU usage by other processes.

For more in-depth information about `sys.dm_os_ring_buffers` and how it's used, check out this great resource: Inside sys.dm_os_ring_buffers.

Give this script a try and let us know in the comments if you found it useful for your SQL Server monitoring!

Determine which scalar UDFs are in-lineable

When you're working with SQL Server, understanding the properties of your functions can be really helpful for performance optimization. The following SQL query helps you identify your scalar user-defined functions (UDFs) and check if they are "inlineable."

Check if Your SQL Scalar Functions are Inlineable

This query provides insight into whether a scalar UDF can be inlined, which can significantly improve query performance by essentially expanding the function's logic directly into the calling query, avoiding the overhead of a function call.

SELECT OBJECT_NAME(m.object_id) AS [Function Name]
 ,is_inlineable
 ,inline_type
FROM sys.sql_modules AS m WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_exec_function_stats AS efs WITH (NOLOCK) ON m.object_id = efs.object_id
WHERE efs.type_desc = N'SQL_SCALAR_FUNCTION'
OPTION (RECOMPILE);

For more detailed information on scalar UDF inlining and related system views, you can refer to the official Microsoft documentation:

• Scalar UDF Inlining
• sys.sql_modules