Questão:
Eu tenho algumas consultas que estão solicitando grandes concessões de memória (~ 7 GB). Essas consultas são executadas com frequência e isso está fazendo com que outras consultas aguardem a memória. Então, estou vendo o tipo de espera RESOURCE_SEMAPHORE. Informações do servidor:
- Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) no Windows Server 2016 Standard 10.0 (Build 14393: )
- 128 GB de RAM
- 96 GB como memória máxima do servidor
- O tamanho do banco de dados é de 1,6 TB
- Habilitar a replicação de transações
- Repositório de consultas desativado
Eu sei que postei perguntas semelhantes sobre o desempenho deste servidor. Estou tentando ver as coisas de uma perspectiva diferente ou tentando resolver de maneiras diferentes. Eu tenho algum tipo de pressão de memória como mencionado neste post. Agora estou tentando consertar as coisas na consulta ou me livrar dessa enorme concessão de memória.
Aqui está o plano para a consulta mencionada no post.
Aprendi que SORT,HASH JOIN,EXCHANGE(parallel Distribute Stream and Parallel Re partition stream and Parallel Gather Stream)são iteradores que consomem memória e os vejo no meu plano de execução.
Como posso reduzir essa enorme concessão de memória dessa consulta.? Mesmo eu estou confuso se a pressão da memória é devido à enorme concessão de memória por monte de consultas.?
Aqui está a consulta real enquanto eu pego a consulta usando o SQLServer Profiler.
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
Do plano de execução que você postou:
A consulta levou apenas 51,3 s para ser executada quando obteve sua concessão de memória. Isso significa que, em geral, 45% do tempo para essa consulta foi gasto aguardando uma concessão de memória. Se me deparar com
RESOURCE_SEMAPHOREuma espera tão extrema, eu restringiria imediatamente a concessão de memória da consulta usando o Resource Governor ou aMAX_GRANT_PERCENTdica de consulta e classificaria os detalhes posteriormente. O SQL Server é bastante ganancioso com concessões de memória e, em alguns casos, você pode diminuir a concessão de memória de uma consulta sem prejudicar o desempenho da consulta.Você só pode implementar a solução Resource Governor se estiver em um equivalente da Enterprise Edition. Você pode limitar a concessão máxima de memória da consulta com a
REQUEST_MAX_MEMORY_GRANT_PERCENTopção de um grupo de carga de trabalho. O Resource Governor é uma solução ideal se você quiser restringir as concessões de memória de muitas consultas ou se não puder alterar o texto da consulta para as consultas que estão causando problemas. Uma restrição infeliz é que você só pode especificar números inteiros para essa opção antes do SQL Server 2019.Você pode implementar a
MAX_GRANT_PERCENTopção em qualquer edição do SQL Server se puder editar o texto da consulta. Também pode ser possível aplicar essa dica por meio de um Guia de plano, mas nunca tentei. A abordagem de dica de consulta permite especificar decimais para a dica para que ela seja mais flexível do que o Administrador de Recursos.Em termos de quanto você deve restringir a concessão de memória, eu cortaria pela metade a cada vez até que as
RESOURCE_SEMAPHOREesperas ficassem sob controle. No momento, sua concessão é de cerca de 10,5% (0,25*7664448/18189472) do total de memória concedível disponível para o servidor. Se você quisesse cortá-lo pela metade, limitaria a concessão de memória a 5% usando o Resource Governor ou a 21% usando oMAX_GRANT_PERCENT hint. As porcentagens são diferentes porque aMAX_GRANT_PERCENTdica influencia na concessão máxima de memória disponível para a consulta, enquanto a configuração do Administrador de Recursos determina a concessão máxima de memória disponível para a consulta.É verdade que restringir demais a concessão de memória resultará em um vazamento para tempdb, o que pode ser ruim para o desempenho. Dependendo do desempenho de E/S, esse derramamento pode levar muito menos tempo do que você está gastando atualmente aguardando a concessão de memória. O operador que usa a maior porcentagem de sua memória disponível é o hash join no nó 41:
Com base nisso, meu melhor palpite conservador é que você pode diminuir a concessão de memória de consulta de 7664448 KB para 4350000 KB e ainda evitar um vazamento de tempdb. Números precisos só podem ser determinados por meio de testes.
Se você precisar de outra opção, considere alterar os índices ou fazer outros ajustes para obter um plano de consulta com menos junções ou classificações de hash. Suas estimativas de cardinalidade são muito boas para esses operadores e esses operadores processam um número relativamente grande de linhas em comparação com o restante do plano, então essa não seria minha primeira abordagem.