What is query_hash from sys.dm_exec_query_stats?

According to Books Online, it is a:

Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.

My first question after reading the above was how similar is similar? There is only one way to find out and what started out as a simple experiment turned into a game of its own. The aim of the game is to change a proc in as many steps as possible and still generate the same query_hash. My record was 10 steps and stopped at that.  Here is an example of what I did:

StepChangequery_hashProc
1Create a new proc0x47F48A4C2B564BB8CREATE proc louie as select * from sys.objects
2Add a parameter0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as select * from sys.objects
3Set nocount on0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on select * from sys.objects
4Return 10x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on select * from sys.objects return 1
5Print0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on print 'Before' select * from sys.objects print 'After' return 1
6Raiserror0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on print 'Before' select * from sys.objects print 'After' raiserror('Error', 16, 1) return 1
7Try...Catch0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on print 'Before' begin try select * from sys.objects print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1
8Return all columns0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on print 'Before' begin try select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published from sys.objects print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1
9Set transaction isolation level read uncommitted0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on set transaction isolation level read uncommitted print 'Before' begin try select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
from sys.objects print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1
10Manipulate variable0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on set transaction isolation level read uncommitted print 'Before' begin try if @name is null set @name = 'louie' select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
from sys.objects print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1

I think by now you get the picture. My observation was that as long as the query was not touched, the query_hash stayed the same. Now let’s see what changes would cause a different query_hash to be generated. By the way, below is the query I used to retrieve the query_hash:

select a.sql_handle, a.query_hash, c.text
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.sql_handle) c
join sys.objects b on c.objectid = b.object_id
where b.name = 'louie'
Changequery_hashProc
With (nolock)0xC32DED2DE8B7FB38CREATE proc louie @name sysname = null as set nocount on set transaction isolation level read uncommitted print 'Before' begin try if @name is null set @name = 'louie' select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
from sys.objects with (nolock) print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1
Add a where clause to return the same resultset0xE659977264A05561CREATE proc louie @name sysname = null as set nocount on set transaction isolation level read uncommitted print 'Before' begin try if @name is null set @name = 'louie' select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
from sys.objects where name = isnull(nullif(@name, 'louie'), name) print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1

The list is of course not exhaustive and it is pretty safe to assume that if a query is modified (with the potential to return a different resultset), its query_hash would change as well.