Top 20 worst performing queries
---------------------------------
and thus you would see "similar" query text in the proc cache each with its separate compiled plans
*/
DECLARE @SQLString nvarchar(500)
DECLARE @cnt INT
SET @cnt = 1
/* Build the SQL string several times by appending the @SQLString*/
While @cnt <= 100
begin
      SET @SQLString =
            N'SELECT EmployeeID, NationalIDNumber, Title, ManagerID
               FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = ' + CAST(@cnt AS nvarchar(500))
      EXECUTE sp_executesql @SQLString
      SET @cnt = @cnt + 1
end
You could run the below query which helps in identifying queries with same query_hash value:
select q.query_hash,
      q.number_of_entries,
      t.text as sample_query,
      p.query_plan as sample_plan
from (select top 20 query_hash,
                  count(*) as number_of_entries,
                  min(sql_handle) as sample_sql_handle,
                  min(plan_handle) as sample_plan_handle
            from sys.dm_exec_query_stats
            group by query_hash
            having count(*) > 1
            order by count(*) desc) as q
      cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t
      cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p
go

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.

 Twitter Linkedin