After reading following blogs, I understand that
hash match aggregate causes
blocking. Using appropriate indexes it can be made as
- Blocking/non-blocking aggregate operators
- The blocking nature of aggregates - Rob Farley
- Hash Aggregate-Craig Freedman
I have a database that has over 200 tables which has been created years back. I am trying to find all queries with group by that is currently working with
hash match aggregate operator. One possibility I found is using dmv like below. But I don't know how to filter it to list only queries with
hash match aggregate operators. How to achieve this? Also, on a big picture level, what are the other options to get this information other than following dmv?
SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS QueryText, qp.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st WHERE st.TEXT LIKE '%GROUP%'