Finding hash match aggregate

After reading following blogs, I understand that hash match aggregate causes blocking. Using appropriate indexes it can be made as stream aggregate.

• 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%'

It is not possible to directly connect part of the query text (e.g. GROUP BY) with a specific operation in the final execution plan.

You can write a query to find plans that:

• Contain a Hash Match Aggregate; and

• The query text contains a GROUP BY clause

...which is not quite the same thing, since this will find plans where the grouping logic was implemented using a Stream Aggregate, replaced with another operation, or even removed entirely - but happens to also contain a Hash Match Aggregate for some other reason.

For example:

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DEQP.objectid,
    DEQP.query_plan,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE
    1 = DEQP.query_plan.exist(
        '//RelOp[
            @PhysicalOp = "Hash Match"
            and @LogicalOp = ("Aggregate","Partial Aggregate","Flow Distinct")]')
    AND DEST.[text] LIKE N'%GROUP BY%';

This might miss some GROUP BY queries if they have a different amount (or type) of white space between GROUP and BY than expected by the query. Maybe replacing all white space with spaces and collapsing contiguous ones into a single space could be done before the LIKE or use SQLCLR and regex instead.

You would need to inspect the results by hand to determine if the Hash Match Aggregate correlates directly to the GROUP BY clause or not. The query above could be extended, perhaps, to check the plan HashKeysBuild element against the grouping columns, but this would be hard to get right, due to optimizer operations.

StackExchange Database Administrators Q#136339, answer score: 8