Sql-Server

導致 RESOURCE SEMAPHORE 的巨大記憶體授予等待其他查詢

  • September 18, 2019

問題：

我很少有查詢請求大量記憶體授予（~7GB）。這些查詢經常執行，這導致其他查詢等待記憶體。所以我看到 RESOURCE_SEMAPHORE 等待類型。伺服器資訊：

  1. Microsoft SQL Server 2016 (SP2) (KB4052908) - Windows Server 2016 Standard 10.0（內部版本 14393：）上的 13.0.5026.0 (X64)
  2. 128 GB 記憶體
  3. 96 GB 作為最大伺服器記憶體
  4. 數據庫大小為 1.6 TB
  5. 啟用事務複製
  6. 查詢儲存已禁用

我知道我已經發布了有關此伺服器性能的類似問題。我試圖從不同的角度看待事物或嘗試以不同的方式解決問題。如本文所述，我有某種記憶壓力。現在我正在嘗試修復查詢或擺脫這個巨大的記憶體授予。

這是文章中提到的查詢計劃。

執行計劃

我了解到這SORT,HASH JOIN,EXCHANGE(parallel Distribute Stream and Parallel Re partition stream and Parallel Gather Stream)是消耗記憶體的迭代器，我在我的執行計劃中看到了這些。

我怎樣才能從這個查詢中減少這個巨大的記憶體授予。？即使我很困惑記憶體壓力是否是由於大量查詢授予的大量記憶體造成的。？

這是我使用 SQLServer 探查器擷取查詢時的實際查詢。

exec sp_executesql N'SELECT TOP (@p__linq__6) 
   [PJ2].[FormId] AS [FormId], 
   [PJ2].[TNUMId] AS [TNUMId], 
   [PJ2].[TNUMDateTime] AS [TNUMDateTime], 
   [PJ2].[TNUMEventNumber] AS [TNUMEventNumber], 
   [PJ2].[EventNumber] AS [EventNumber], 
   [PJ2].[SubUnit] AS [SubUnit], 
   [PJ2].[EventClass] AS [EventClass], 
   [PJ2].[NatureOfEvent] AS [NatureOfEvent], 
   [PJ2].[EventType] AS [EventType], 
   [PJ2].[FileNumber] AS [FileNumber], 
   [PJ2].[EventStatus] AS [EventStatus], 
   [PJ2].[TNUMDate] AS [TNUMDate], 
   [PJ2].[FileDate] AS [FileDate], 
   [PJ2].[ComplainantFirstName] AS [ComplainantFirstName], 
   [PJ2].[ComplainantLastName] AS [ComplainantLastName], 
   [PJ2].[InvestBy] AS [InvestBy], 
   [PJ2].[SecondaryManager] AS [SecondaryManager], 
   [PJ2].[RejectionReason] AS [RejectionReason], 
   [PJ2].[InactiveReason] AS [InactiveReason], 
   [PJ2].[InactiveDate] AS [InactiveDate], 
   [PJ2].[Ag_Id] AS [Ag_Id], 
   [PJ2].[Queue] AS [Queue], 
   [PJ2].[SL] AS [SL], 
   [PJ2].[PrimaryManagerId] AS [PrimaryManagerId], 
   [PJ2].[WarehouseDistrictId] AS [WarehouseDistrictId], 
   [PJ2].[WarehouseIsSQ] AS [WarehouseIsSQ], 
   [PJ2].[WarehousePhone] AS [WarehousePhone], 
   [PJ2].[CityTwp] AS [CityTwp], 
   [PJ2].[County] AS [County], 
   [PJ2].[Institution] AS [Institution], 
   [PJ2].[TNUMTime] AS [TNUMTime], 
   [PJ2].[Prefix] AS [Prefix], 
   [PJ2].[StreetNumber] AS [StreetNumber], 
   [PJ2].[Street] AS [Street], 
   [PJ2].[RoadType] AS [RoadType], 
   [PJ2].[Suffix] AS [Suffix], 
   [PJ2].[Apartment] AS [Apartment], 
   [PJ2].[AtOrNear] AS [AtOrNear], 
   [PJ2].[State] AS [State], 
   [PJ2].[Zip] AS [Zip], 
   [PJ2].[Beat] AS [Beat], 
   [PJ2].[Reviewed] AS [Reviewed], 
   [PJ2].[WarehouseCounty] AS [WarehouseCounty], 
   [PJ2].[IsTrue] AS [IsTrue], 
   [PJ2].[EnquiredByUserId] AS [EnquiredByUserId], 
   [PJ2].[SecondaryManagerUserId] AS [SecondaryManagerUserId], 
   [PJ2].[DiaryNumber] AS [DiaryNumber], 
   [PJ2].[CI_Value] AS [CI_Value], 
   [PJ2].[CurrentEventStatus_Description] AS [CurrentEventStatus_Description]
   FROM ( SELECT [PJ2].[FormId] AS [FormId], [PJ2].[TNUMId] AS [TNUMId], [PJ2].[TNUMDateTime] AS [TNUMDateTime], [PJ2].[TNUMEventNumber] AS 
[TNUMEventNumber], [PJ2].[EventNumber] AS [EventNumber], [PJ2].[IsTrue] AS [IsTrue], [PJ2].[SubUnit] AS [SubUnit], [PJ2].[EventClass] AS [EventClass], 
[PJ2].[NatureOfEvent] AS [NatureOfEvent], [PJ2].[EventType] AS [EventType], [PJ2].[FileNumber] AS [FileNumber], [PJ2].[EventStatus] AS [EventStatus], [PJ2].[TNUMDate] AS [TNUMDate], [PJ2].[FileDate] AS [FileDate], [PJ2].[ComplainantFirstName] AS [ComplainantFirstName], [PJ2].[ComplainantLastName] AS [ComplainantLastName], [PJ2].[InvestBy] AS [InvestBy], [PJ2].[SecondaryManager] AS [SecondaryManager], [PJ2].[EnquiredByUserId] AS [EnquiredByUserId], [PJ2].[SecondaryManagerUserId] AS [SecondaryManagerUserId], [PJ2].[RejectionReason] AS [RejectionReason], [PJ2].[InactiveReason] AS [InactiveReason], [PJ2].[InactiveDate] AS [InactiveDate], [PJ2].[Ag_Id] AS [Ag_Id], [PJ2].[Queue] AS [Queue], [PJ2].[SL] AS [SL], [PJ2].[PrimaryManagerId] AS [PrimaryManagerId], [PJ2].[WarehouseDistrictId] AS [WarehouseDistrictId], [PJ2].[WarehouseIsSQ] AS [WarehouseIsSQ], [PJ2].[WarehousePhone] AS [WarehousePhone], [PJ2].[CityTwp] AS [CityTwp], [PJ2].[County] AS [County], [PJ2].[Institution] AS [Institution], [PJ2].[TNUMTime] AS [TNUMTime], [PJ2].[Prefix] AS [Prefix], [PJ2].[StreetNumber] AS [StreetNumber], [PJ2].[Street] AS [Street], [PJ2].[RoadType] AS [RoadType], [PJ2].[Suffix] AS [Suffix], [PJ2].[Apartment] AS [Apartment], [PJ2].[AtOrNear] AS [AtOrNear], [PJ2].[State] AS [State], [PJ2].[Zip] AS [Zip], [PJ2].[Beat] AS [Beat], [PJ2].[Reviewed] AS [Reviewed], [PJ2].[CI_Value] AS [CI_Value], [PJ2].[CurrentEventStatus_Description] AS [CurrentEventStatus_Description], [PJ2].[DiaryNumber] AS [DiaryNumber], [PJ2].[WarehouseCounty] AS [WarehouseCounty], row_number() OVER (ORDER BY [PJ2].[Ag_Id] ASC, [PJ2].[TNUMEventNumber] ASC, [PJ2].[FileNumber] ASC) AS [row_number]
       FROM ( SELECT 
           [Ex1].[FormId] AS [FormId], 
           [Ex1].[TNUMId] AS [TNUMId], 
           [Ex1].[TNUMDateTime] AS [TNUMDateTime], 
           [Ex1].[TNUMEventNumber] AS [TNUMEventNumber], 
           [Ex1].[EventNumber] AS [EventNumber], 
           [Ex1].[IsTrue] AS [IsTrue], 
           [Ex1].[SubUnit] AS [SubUnit], 
           [Ex1].[EventClass] AS [EventClass], 
           [Ex1].[NatureOfEvent] AS [NatureOfEvent], 
           [Ex1].[EventType] AS [EventType], 
           [Ex1].[FileNumber] AS [FileNumber], 
           [Ex1].[EventStatus] AS [EventStatus], 
           [Ex1].[TNUMDate] AS [TNUMDate], 
           [Ex1].[FileDate] AS [FileDate], 
           [Ex1].[ComplainantFirstName] AS [ComplainantFirstName], 
           [Ex1].[ComplainantLastName] AS [ComplainantLastName], 
           [Ex1].[InvestBy] AS [InvestBy], 
           [Ex1].[SecondaryManager] AS [SecondaryManager], 
           [Ex1].[EnquiredByUserId] AS [EnquiredByUserId], 
           [Ex1].[SecondaryManagerUserId] AS [SecondaryManagerUserId], 
           [Ex1].[RejectionReason] AS [RejectionReason], 
           [Ex1].[InactiveReason] AS [InactiveReason], 
           [Ex1].[InactiveDate] AS [InactiveDate], 
           [Ex1].[Ag_Id] AS [Ag_Id], 
           [Ex1].[Queue] AS [Queue], 
           [Ex1].[SL] AS [SL], 
           [Ex1].[PrimaryManagerId] AS [PrimaryManagerId], 
           [Ex1].[WarehouseDistrictId] AS [WarehouseDistrictId], 
           [Ex1].[WarehouseIsSQ] AS [WarehouseIsSQ], 
           [Ex1].[WarehousePhone] AS [WarehousePhone], 
           [Ex1].[CityTwp] AS [CityTwp], 
           [Ex1].[County] AS [County], 
           [Ex1].[Institution] AS [Institution], 
           [Ex1].[TNUMTime] AS [TNUMTime], 
           [Ex1].[Prefix] AS [Prefix], 
           [Ex1].[StreetNumber] AS [StreetNumber], 
           [Ex1].[Street] AS [Street], 
           [Ex1].[RoadType] AS [RoadType], 
           [Ex1].[Suffix] AS [Suffix], 
           [Ex1].[Apartment] AS [Apartment], 
           [Ex1].[AtOrNear] AS [AtOrNear], 
           [Ex1].[State] AS [State], 
           [Ex1].[Zip] AS [Zip], 
           [Ex1].[Beat] AS [Beat], 
           [Ex1].[Reviewed] AS [Reviewed], 
           [Ex1].[CI_Value] AS [CI_Value], 
           [Ex1].[CurrentEventStatus_Description] AS [CurrentEventStatus_Description], 
           [Ex1].[DiaryNumber] AS [DiaryNumber], 
           [Ex1].[WarehouseCounty] AS [WarehouseCounty]
           FROM (SELECT 
   [SAQE].[FormId] AS [FormId], 
   [SAQE].[TNUMId] AS [TNUMId], 
   [SAQE].[TNUMDateTime] AS [TNUMDateTime], 
   [SAQE].[TNUMEventNumber] AS [TNUMEventNumber], 
   [SAQE].[EventNumber] AS [EventNumber], 
   [SAQE].[IsTrue] AS [IsTrue], 
   [SAQE].[SubUnit] AS [SubUnit], 
   [SAQE].[EventClass] AS [EventClass], 
   [SAQE].[NatureOfEvent] AS [NatureOfEvent], 
   [SAQE].[EventType] AS [EventType], 
   [SAQE].[FileNumber] AS [FileNumber], 
   [SAQE].[EventStatus] AS [EventStatus], 
   [SAQE].[TNUMDate] AS [TNUMDate], 
   [SAQE].[FileDate] AS [FileDate], 
   [SAQE].[ComplainantFirstName] AS [ComplainantFirstName], 
   [SAQE].[ComplainantLastName] AS [ComplainantLastName], 
   [SAQE].[InvestBy] AS [InvestBy], 
   [SAQE].[SecondaryManager] AS [SecondaryManager], 
   [SAQE].[EnquiredByUserId] AS [EnquiredByUserId], 
   [SAQE].[SecondaryManagerUserId] AS [SecondaryManagerUserId], 
   [SAQE].[RejectionReason] AS [RejectionReason], 
   [SAQE].[InactiveReason] AS [InactiveReason], 
   [SAQE].[InactiveDate] AS [InactiveDate], 
   [SAQE].[Ag_Id] AS [Ag_Id], 
   [SAQE].[Queue] AS [Queue], 
   [SAQE].[SL] AS [SL], 
   [SAQE].[PrimaryManagerId] AS [PrimaryManagerId], 
   [SAQE].[WarehouseDistrictId] AS [WarehouseDistrictId], 
   [SAQE].[WarehouseIsSQ] AS [WarehouseIsSQ], 
   [SAQE].[WarehousePhone] AS [WarehousePhone], 
   [SAQE].[CityTwp] AS [CityTwp], 
   [SAQE].[County] AS [County], 
   [SAQE].[Institution] AS [Institution], 
   [SAQE].[TNUMTime] AS [TNUMTime], 
   [SAQE].[Prefix] AS [Prefix], 
   [SAQE].[StreetNumber] AS [StreetNumber], 
   [SAQE].[Street] AS [Street], 
   [SAQE].[RoadType] AS [RoadType], 
   [SAQE].[Suffix] AS [Suffix], 
   [SAQE].[Apartment] AS [Apartment], 
   [SAQE].[AtOrNear] AS [AtOrNear], 
   [SAQE].[State] AS [State], 
   [SAQE].[Zip] AS [Zip], 
   [SAQE].[Beat] AS [Beat], 
   [SAQE].[Reviewed] AS [Reviewed], 
   [SAQE].[CI_Value] AS [CI_Value], 
   [SAQE].[CurrentEventStatus_Description] AS [CurrentEventStatus_Description], 
   [SAQE].[DiaryNumber] AS [DiaryNumber], 
   [SAQE].[WarehouseCounty] AS [WarehouseCounty]
   FROM [dbo].[SAQE] AS [SAQE]) AS [Ex1]
           WHERE ((N''Public'' = [Ex1].[SL]) OR (N''Private'' = [Ex1].[SL]) OR ([Ex1].[PrimaryManagerId] = @p__linq__0) OR ( EXISTS (SELECT 
               1 AS [C1]
               FROM (SELECT 
   [fp].[Id] AS [Id], 
   [fp].[Ag_Id] AS [Ag_Id], 
   [fp].[UserId] AS [UserId]
   FROM [dbo].[fp] AS [fp]) AS [Extent2]
               WHERE ([Ex1].[FormId] = [Extent2].[Id]) AND ([Extent2].[UserId] = @p__linq__1) AND ([Extent2].[Ag_Id] = @p__linq__2)
           ))) AND (([Ex1].[Ag_Id] = @p__linq__3) OR (([Ex1].[Ag_Id] IS NULL) AND (@p__linq__3 IS NULL))) AND (([Ex1].[Queue] = @p__linq__4) OR (([Ex1].[Queue] IS NULL) AND (@p__linq__4 IS NULL)))
       )  AS [PJ2]
   )  AS [PJ2]
   WHERE [PJ2].[row_number] > @p__linq__5
   ORDER BY [PJ2].[Ag_Id] ASC, [PJ2].[TNUMEventNumber] ASC, [PJ2].[FileNumber] ASC',N'@p__linq__0 int,@p__linq__1 int,@p__linq__2 varchar(8000),@p__linq__3 varchar(8000),@p__linq__4 varchar(8000),@p__linq__5 int,@p__linq__6 int',@p__linq__0=9495,@p__linq__1=9495,@p__linq__2='3568',@p__linq__3='3568',@p__linq__4='1',@p__linq__5=0,@p__linq__6=100

從您發布的執行計劃中：

查詢在執行期間必須等待 43 秒的 MemoryGrant

一旦獲得記憶體授權，查詢只需要 51.3 秒即可執行。這意味著該查詢總共有 45% 的時間用於等待記憶體授予。如果遇到RESOURCE_SEMAPHORE這種極端情況，我會立即使用資源調控器或MAX_GRANT_PERCENT 查詢提示限制查詢的記憶體授予，並在稍後整理出細節。SQL Server 對記憶體授予相當貪婪，在某些情況下，您可以降低查詢的記憶體授予而不會降低查詢性能。

如果您使用的是企業版等效版本，則只能實施資源調控器解決方案。REQUEST_MAX_MEMORY_GRANT_PERCENT您可以使用工作負載組的選項來限制查詢的最大記憶體授予。如果您想限制許多查詢的記憶體授予，或者如果您無法更改導致問題的查詢的查詢文本，則資源調控器是一個理想的解決方案。一個不幸的限制是您只能在 SQL Server 2019 之前為該選項指定整數。

MAX_GRANT_PERCENT如果可以編輯查詢文本，則可以在任何版本的 SQL Server 上實現該選項。也可以通過計劃指南強制執行此提示，但我從未嘗試過。查詢提示方法允許您為提示指定小數，因此它可以比資源調控器更靈活。

就你應該限制多少記憶體授予而言，我每次都會減少一半，直到RESOURCE_SEMAPHORE等待得到控制。現在，您的授權大約是伺服器可用的總授權記憶體的 10.5% (0.25*7664448/18189472)。如果您想將其減半，那麼您可以使用 Resource Governor 將記憶體授予限制為 5% 或使用MAX_GRANT_PERCENT hint. 百分比不同，因為MAX_GRANT_PERCENT提示因素考慮了查詢可用的最大記憶體授權，而資源調控器設置確定了查詢可用的最大記憶體授權。

確實，過多地限制記憶體授予會導致 tempdb 溢出，這可能對性能不利。根據 IO 性能，該溢出所花費的時間可能比您目前等待記憶體授予所花費的時間要少得多。使用最大可用記憶體百分比的運算元是節點 41 處的雜湊連接：

雜湊連接

基於此，我最好的保守猜測是，您可以將查詢記憶體授予從 7664448 KB 一直降低到 4350000 KB，並且仍然避免 tempdb 溢出。準確的數字只能通過測試來確定。

如果您需要其他選項，您可以考慮更改索引或進行其他調整以獲得具有更少雜湊連接或排序的查詢計劃。您的基數估計對這些運算符非常有用，並且與計劃的其餘部分相比，這些運算符處理的行數相對較多，因此這不是我的第一種方法。

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

相關問答

Sql-Server

SQL 連接的性能

  • March 23, 2022
檢視問答
Sql-Server

如果 CTE 在查詢中定義並且從未使用過，它會發出聲音嗎？

  • December 3, 2021
檢視問答
Sql-Server

TOP 如何（以及為什麼）影響執行計劃？

  • November 27, 2021
檢視問答
Sql-Server

非聚集索引更新未顯示在執行計劃中

  • August 12, 2021
檢視問答
Sql-Server

為什麼我的查詢在環境 A 中執行得很快，而在環境 B 中卻很慢？

  • June 30, 2021
檢視問答
Sql-Server

參數嗅探即使在禁用後也會發生嗎？

  • June 15, 2021
檢視問答