SQL Server 2016 - 對性能不佳的查詢發出過多的記憶體授予警告

我在 SQL Server 2016 EE 實例上有一個相對較大的 550GB 數據庫，它的最大記憶體限制為作業系統可用的總 128GB RAM 的 112GB。該數據庫的最新兼容性級別為 130。開發人員抱怨以下查詢在單獨執行時會在 30 秒的可接受時間內執行，但是當他們大規模執行程序時，相同的查詢會同時執行多次跨多個執行緒，這是他們觀察到執行時間受到影響並且性能/吞吐量下降的時候。有問題的 T-SQL 是：

select distinct dg.entityId, et.EntityName, dg.Version
                    from DataGathering dg with(nolock) 
                    inner join entity e with(nolock) 
                          on e.EntityId = dg.EntityId
                    inner join entitytype et with(nolock)   
                        on et.EntityTypeID = e.EntityTypeID  
                        and et.EntityName = 'Account_Third_Party_Details' 
                    inner join entitymapping em with(nolock)   
                        on em.ChildEntityId = dg.EntityId  
                        and em.ParentEntityId = -1  
                    where dg.EntityId = dg.RootId  

   union all

select distinct dg1.EntityId, et.EntityName, dg1.version
                    from datagathering dg1 with(nolock)  
                    inner join entity e with(nolock)   
                        on e.EntityId = dg1.EntityId 
                    inner join entitytype et with(nolock)   
                        on et.EntityTypeID = e.EntityTypeID 
                        and et.EntityName = 'TIN_Details' 
                    where dg1.EntityId = dg1.RootId  
                    and dg1.EntityId not in (  
                        select distinct ChildEntityId   
                        from entitymapping  
                        where ChildEntityId = dg1.EntityId 
                        and ParentEntityId = -1)

實際執行計劃顯示以下記憶體授予警告：

圖形執行計劃可以在這裡找到：

https://www.brentozar.com/pastetheplan/?id=r18ZtCidN

以下是此查詢涉及的表的行數和大小。最昂貴的運算符是對 DataGathering 表上的非聚集索引的索引掃描，考慮到與其他表相比的大小，這是有意義的。我理解為什麼/如何需要記憶體授予，我認為這是由於查詢的編寫方式需要多個排序和雜湊運算符。我需要的建議/指導是如何避免記憶體授予、T-SQL 和重構程式碼不是我的強項，有沒有辦法重寫這個查詢以提高性能？如果我可以將查詢調整為單獨執行得更快，那麼希望這些好處會轉移到大規模執行時，也就是性能開始受到影響的時候。很高興提供更多資訊，並希望從中學到一些東西！

更新 3 個表的統計資訊後：

UPDATE STATISTICS Entity WITH FULLSCAN; 
UPDATE STATISTICS EntityMapping WITH FULLSCAN; 
UPDATE STATISTICS EntityType WITH FULLSCAN;

…執行計劃改進了一些：

https://www.brentozar.com/pastetheplan/?id=rkVmdkh_4

不幸的是，“過度授權”警告仍然存在。

Josh Darnell 友好地建議將查詢重新分解為以下內容，以避免他在某個運算符上發現的並行性受到抑制。重構查詢引發錯誤“消息 4104，級別 16，狀態 1，第 7 行無法綁定多部分標識符“et.EntityName”。” 我該如何解決這個問題？

DECLARE @tinDetailsId int;

SELECT @tinDetailsId = et.EntityTypeID 
FROM entitytype et 
WHERE et.EntityName = 'TIN_Details';

select distinct dg1.EntityId, et.EntityName, dg1.version
                    from datagathering dg1 with(nolock)  
                    inner join entity e with(nolock)   
                        on e.EntityId = dg1.EntityId
                    where dg1.EntityId = dg1.RootId  
                    and e.EntityTypeID = @tinDetailsId
                    and dg1.EntityId not in (  
                        select distinct ChildEntityId   
                        from entitymapping  
                        where ChildEntityId = dg1.EntityId 
                        and ParentEntityId = -1)

           UNION ALL

select distinct dg.entityId, et.EntityName, dg.Version
                    from DataGathering dg with(nolock) 
                    inner join entity e with(nolock) 
                          on e.EntityId = dg.EntityId
                    inner join entitytype et with(nolock)   
                        on et.EntityTypeID = e.EntityTypeID  
                        and et.EntityName = 'Account_Third_Party_Details' 
                    inner join entitymapping em with(nolock)   
                        on em.ChildEntityId = dg.EntityId  
                        and em.ParentEntityId = -1  
                    where dg.EntityId = dg.RootId  

這可能對記憶體授予情況沒有幫助（希望額外的統計資訊更新會對此有所幫助），但我注意到此查詢中禁止了並行性。查看計劃的這一部分：

由於嵌套循環連接的外側只有一行，因此所有 900k 行都集中到一個執行緒上。所以儘管這個查詢在 DOP 8 執行，這部分計劃是完全串列的。這包括排序。這是該類型的 XML：

如果可能的話，請考慮避免連接到 EntityType，而只是抓住該 Id 並用它過濾 Entity 表。這將允許它只是對實體表的索引掃描的謂詞，希望允許並行性並加快執行速度。

像這樣的東西：

DECLARE @tinDetailsId int;

SELECT @tinDetailsId = et.EntityTypeID 
FROM entitytype et 
WHERE et.EntityName = 'TIN_Details';

然後您可以在查詢的下半部分引用它，從而消除連接：

select distinct dg1.EntityId, 'TIN_Details', dg1.version
                    from datagathering dg1 with(nolock)  
                    inner join entity e with(nolock)   
                        on e.EntityId = dg1.EntityId
                    where dg1.EntityId = dg1.RootId  
                    and e.EntityTypeID = @tinDetailsId
                    and dg1.EntityId not in (  
                        select distinct ChildEntityId   
                        from entitymapping  
                        where ChildEntityId = dg1.EntityId 
                        and ParentEntityId = -1)

您可能希望對EntityName查詢頂部的“Account_Third_Party_Details”做同樣的事情，因為它有同樣的問題 - 行數是原來的兩倍。

PS：與手頭的主題完全無關，我注意到你nolock對這個查詢中的所有表都有提示。確保您了解這一點的含義。查看有關該主題的漂亮部落格文章：

壞習慣： Aaron Bertrand

將NOLOCK 無處不在

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