SQL Server: Explain the between SQL_Handle and Plan_Handle

What is the difference between SQL_Handle and Plan_Handle? SQL Server keeps track of different types of information using "handles." In this article, we'll explain the difference between SQL_Handle and Plan_Handle, and how each is used by SQL Server. Read on to learn more!

The dynamic management view sys.dm_exec_query_stats return the SQL_Handle and Plan_Handle as shown below.

SELECTsql_handle, plan_handle FROM sys.dm_exec_query_stats

Do you know the difference between these sql_handle and plan_handle? If not, let’s understand these terms.

• Plan_handle: SQL Server stores the compiled plan in the SQLCP or OBJCP cache stores. The compiled plan hash is unique and serves as an identifier for the cache compiled plan. You can retrieve the cached compiled plan using the plan_handle. If you remove the compiled plan from the cache, its associated queries and statements are also deleted.

• Sql_handle: The SQL text hash (MD5) contains all your formatting, such as spaces and casing. The SQL_handle is unique for the entire batch text. SQL Server stores the actual SQL text in the SQLMGR.

Relationship between sql_handle and plan_handle

The relationship between the plan sql_handle and plan_handle is 1:N. It means that you can have multiple plans handles for the associated sql_handle.

Let’s look at the below example. The query executes the code with a different set of conditions (quote_identifier on and off).

create table test (
col1 int,
col2 int
)
set quoted_identifier off
go
if exists(select col1 from test where col2 > 10) 
       declare @x int;
go
set quoted_identifier on
go
if exists(select col1 from test where col2 > 10) 
       declare @x int;
go

select st.text, qs. sql_handle, qs.plan_handle
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle) st
where st.text like '%declare @x int%'
go

As shown below, we have two different plan_handle for a sql_handle. It shows that you can have multiple plan_handle for a sql_handle.

Execute the following query to check the number of plan_handle for a sql_handle.

SELECT sql_handle, COUNT(plan_handle) TotalNumberofPlans
FROM sys.dm_exec_query_stats
GROUP BY sql_handle
ORDER BY TotalNumberofPlans DESC
GO