sql_handle, plan_handle,query_hash,plan_hash

statement:

SELECT database_id, name FROM sys.databases WHERE name=@P1 AND state_desc = @P2
SELECT database_id, name FROM sys.databases    WHERE name=@P1 AND state_desc = @P2
SELECT database_id, name FROM sys.databases    WHERE      name=@P1 AND state_desc = @P2 
SELECT database_id, name FROM sys.databases    WHERE name=@P1 AND state_desc = @P2 --1

 

Run below sql with above four sql statements, which only have a little changes.

Declare @P1 int;
Exec sp_cursorprepare @P1 output,
N'@P1 nvarchar(128), @P2 nvarchar(100)',
N'SELECT database_id, name FROM sys.databases WHERE name=@P1 AND state_desc = @P2 ',1;
PRINT @P1;

 

check the prepared plan in the plan cache.

select cp.plan_handle, t.text,qp.query_plan from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan( cp.plan_handle) qp
inner join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle
cross apply sys.dm_exec_sql_text(qs.sql_handle) t
where objtype='Prepared' order by text;

Below is the result, the plan_handle changed, that means the sql text changed, then the plan need to regenerate.

 

check the sql_handle ,plan_handle,query_hash,plan_hash.

select sql_handle,plan_handle,query_hash ,query_plan_hash from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle) where plan_handle in (
0x06000600A99CA626B820DE08000000000000000000000000,
0x0600060027069B2AB860AB08000000000000000000000000,
0x06000600631A112DB8E0BA08000000000000000000000000,
0x0600060060FB5502B8401F09000000000000000000000000
)

 below results showing the sql_handle also changed, but the query_hash and plan_hash are the same.

 

 

I compared two generated XML plan files, only the statement and plan compile time information are different. the other plan operations are the same.

That means the query_hash and plan_hash identified the logic equiat SQL statement and  execution plan.