
use pubs
go

DBCC FREEPROCCACHE

SELECT a.au_id, au_fname, au_lname, royaltyper
FROM authors a
JOIN titleauthor ta ON a.au_id = ta.au_id
WHERE au_fname = 'Marjorie' AND  au_lname = 'Green'

SELECT a.au_id, au_fname, au_lname, royaltyper
FROM authors a
JOIN titleauthor ta ON a.au_id = ta.au_id
WHERE au_fname = 'Ann' AND  au_lname = 'Dull'
go

--Similar queries yield similar query_hash
SELECT st.text AS "Query Text", qs.query_hash AS "Query Hash"
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st

/* The queries below are all similar enough to have the same query fingerprint: 
SELECT * FROM foo WHERE column1 = 'A' AND column2 = 'B'
SELECT * FROM foo WHERE column1 = 'X' AND column2 = 'Y'
SELECT * FROM foo WHERE column1 = 100 AND column2 = 200
SELECT * FROM foo WHERE foo.column1 = 'X' AND foo.column2 = 'Y'

-- All of the queries below are have differences that are significant enough to produce different query fingerprints: 
SELECT * FROM foo WHERE column1 = 'X' 
SELECT * FROM foo AS foo2 WHERE column1 = 'X' AND column2 = 'Y'
SELECT * FROM foo WHERE column1 = 'X' AND column2 IS NULL
SELECT * FROM foo WHERE column1 = 'X' OR column2 = 'Y'
SELECT column1 FROM foo WHERE column1 = 'X' AND column2 > 'Y'
*/

SELECT a.au_id, au_fname, au_lname, royaltyper
FROM authors a
JOIN titleauthor ta ON a.au_id = ta.au_id
WHERE au_fname = 'Marjorie' AND  au_lname = 'Green'

SELECT a.au_id, au_fname, au_lname, royaltyper
FROM authors a
JOIN titleauthor ta ON a.au_id = ta.au_id
WHERE au_fname = 'Marjorie' OR  au_lname = 'Green'

--AND vs OR is not similar enough
SELECT st.text AS "Query Text", qs.query_hash AS "Query Hash"
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st

-- Cumulative query cost
SELECT query_stats.query_hash AS "Query Hash", 
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    COUNT(query_stats.statement_text) AS "Total Executions",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM 
    (SELECT QS.*, 
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY [Avg CPU Time] DESC;
GO

USE AdventureWorks;
GO
DBCC FREEPROCCACHE
GO
SET STATISTICS XML ON;
GO
SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
    JOIN Production.Product P
    ON T.ProductID = P.ProductID
WHERE P.ProductID = 1;
GO
SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
    JOIN Production.Product P
    ON T.ProductID = P.ProductID
WHERE P.ProductID = 3;
GO
SET STATISTICS XML OFF;
GO

--Show the query_hash and query plan hash
--Query_hash is same, query_plan_hash is different
SELECT ST.text AS "Query Text", QS.query_hash AS "Query Hash", 
    QS.query_plan_hash AS "Query Plan Hash"
FROM sys.dm_exec_query_stats QS
    CROSS APPLY sys.dm_exec_sql_text (QS.sql_handle) ST
