查找雜湊匹配聚合

閱讀以下部落格後，我了解hash match聚合導致blocking. 使用適當的索引，它可以做成stream aggregate.

1. 阻塞/非阻塞聚合運算符
2. 聚集體的阻塞性質 - Rob Farley
3. 雜湊聚合-Craig Freedman

我有一個數據庫，其中包含多年前創建的 200 多個表。我正在嘗試查找目前正在使用hash match聚合運算符的所有帶有 group by 的查詢。我發現的一種可能性是使用 dmv，如下所示。但我不知道如何過濾它以僅列出帶有hash match聚合運算符的查詢。如何做到這一點？此外，在大圖層面上，除了關注 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%'

無法將部分查詢文本（例如）與最終執行計劃中的特定操作直接連接起來。GROUP BY

您可以編寫查詢來查找滿足以下條件的計劃：

• 包含雜湊匹配聚合；和
• 查詢文本包含一個GROUP BY子句

…這並不完全相同，因為這將找到使用 Stream Aggregate 實現分組邏輯的計劃，用另一個操作替換，甚至完全刪除 - 但由於某些其他原因碰巧還包含一個 Hash Match Aggregate .

例如：

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

GROUP BY如果它們之間的空白數量（或類型）與查詢預期的不同，GROUP這BY可能會錯過一些查詢。也許可以在 or 之前使用空格替換所有空格並將連續的空格折疊成一個空格，LIKE或者改用 SQLCLR 和正則表達式。

您需要手動檢查結果以確定雜湊匹配聚合是否與GROUP BY子句直接相關。或許可以擴展上面的查詢，以根據分組列檢查計劃HashKeysBuild元素，但由於優化器操作，這很難正確處理。

引用自：https://dba.stackexchange.com/questions/136339