What is the Difference Between sql_handle and plan_handle?- Interview Question of the Week #269

Question: What is the difference between sql_handle and plan_handle?

Answer: I was asked this during a Comprehensive Database Performance Health Check. A sql_handle identifies the batch or stored procedure text. A plan_handle identifies a cached execution plan. Neither value is the text or plan itself; each is a token you pass to the appropriate function to retrieve more information.

See Both Handles in the Cache

sys.dm_exec_query_stats is the place to start. The text function below makes the batch behind a sql_handle visible. Results depend on what is cached on your instance, so your grid will look different from anyone else’s.

SELECT TOP (10)
       qs.sql_handle,
       qs.plan_handle,
       st.text AS BatchText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st;

One batch can have more than one cached plan under different compilation contexts. But sys.dm_exec_query_stats has a row for each statement within a cached plan, so a plain COUNT(plan_handle) counts statement rows, not distinct plans. To count the distinct plans per SQL handle, first remove duplicate pairs:

WITH HandlePairs AS
(
    SELECT DISTINCT sql_handle, plan_handle
    FROM sys.dm_exec_query_stats
)
SELECT sql_handle,
       COUNT(*) AS DistinctCachedPlans
FROM HandlePairs
GROUP BY sql_handle
HAVING COUNT(*) > 1
ORDER BY DistinctCachedPlans DESC;

This is a snapshot of the plan cache, not a permanent history of a query. Cached plans can age out, and a query currently running or not cached may not appear here. During a health check, the pair can point me toward a question about repeated compilation, but I investigate the plan attributes and workload before calling it a problem.

Reading this DMV requires VIEW SERVER STATE on SQL Server 2019 and earlier, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

A sql_handle is not a plan, it is the text’s address, and one text can have several cached plans.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.