Solucionar problemas de desempenho lento ou pouca memória causados por concessões de memória no SQL Server
O que são concessões de memória?
As concessões de memória, também conhecidas como Reservas de Execução de Consulta (QE), Memória de Execução de Consulta, Memória de Espaço de Trabalho e Reservas de Memória, descrevem o uso de memória no momento da execução da consulta. O SQL Server aloca essa memória durante a execução da consulta para uma ou mais das seguintes finalidades:
- Classificar operações
- Operações de hash
- Operações de cópia em massa (não é um problema comum)
- Criação de índice, incluindo a inserção em índices COLUMNSTORE porque dicionários/tabelas de hash são usados em tempo de execução para a criação de índices (não é um problema comum)
Para fornecer algum contexto, durante seu tempo de vida, uma consulta pode solicitar memória de diferentes alocadores ou administradores de memória, dependendo do que ela precisa fazer. Por exemplo, quando uma consulta é analisada e compilada inicialmente, ela consome memória de compilação. Depois que a consulta é compilada, essa memória é liberada e o plano de consulta resultante é armazenado na memória cache do plano. Depois que um plano é armazenado em cache, a consulta está pronta para execução. Se a consulta fizer operações de classificação, operações de correspondência de hash (JOIN ou agregações) ou inserções em índices COLUMNSTORE, ela usará a memória do alocador de execução de consulta. Inicialmente, a consulta solicita essa memória de execução e, posteriormente, se essa memória for concedida, a consulta usará toda ou parte da memória para resultados de classificação ou buckets de hash. Essa memória alocada durante a execução da consulta é o que é chamado de concessões de memória. Como você pode imaginar, depois que a operação de execução da consulta é concluída, a concessão de memória é liberada de volta para o SQL Server para uso em outro trabalho. Portanto, as alocações de concessão de memória são temporárias por natureza, mas ainda podem durar muito tempo. Por exemplo, se uma execução de consulta executar uma operação de classificação em um conjunto de linhas muito grande na memória, a classificação poderá levar muitos segundos ou minutos e a memória concedida será usada durante o tempo de vida da consulta.
Exemplo de uma consulta com uma concessão de memória
Aqui está um exemplo de uma consulta que usa memória de execução e seu plano de consulta mostrando a concessão:
SELECT *
FROM sys.messages
ORDER BY message_id
Essa consulta seleciona um conjunto de linhas de mais de 300.000 linhas e o classifica. A operação de classificação induz uma solicitação de concessão de memória. Se você executar essa consulta no SSMS, poderá exibir seu plano de consulta. Ao selecionar o operador mais SELECT à esquerda do plano de consulta, você pode exibir as informações de concessão de memória para a consulta (pressione F4 para mostrar Propriedades):
Além disso, se você clicar com o botão direito do mouse no espaço em branco no plano de consulta, poderá escolher Mostrar XML do Plano de Execução... e localizar um elemento XML que mostre as mesmas informações de concessão de memória.
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="41232" RequiredMemory="5248" DesiredMemory="46016" RequestedMemory="46016" GrantWaitTime="0" GrantedMemory="46016" MaxUsedMemory="45816" MaxQueryMemory="277688" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution" />
Vários termos precisam de explicação aqui. Uma consulta pode desejar uma determinada quantidade de memória de execução (DesiredMemory) e normalmente solicitaria essa quantidade (RequestedMemory). Em tempo de execução, o SQL Server concede toda ou parte da memória solicitada, dependendo da disponibilidade (GrantedMemory). No final, a consulta pode usar mais ou menos da memória solicitada inicialmente (MaxUsedMemory). Se o otimizador de consulta tiver superestimado a quantidade de memória necessária, ele usará menos do que o tamanho solicitado. Mas essa memória é desperdiçada, pois poderia ter sido usada por outra solicitação. Por outro lado, se o otimizador tiver subestimado o tamanho da memória necessária, as linhas em excesso poderão ser despejadas no disco para que o trabalho seja feito no tempo de execução. Em vez de alocar mais memória do que o tamanho solicitado inicialmente, o SQL Server envia as linhas extras para o disco e a usa como um espaço de trabalho temporário. Para obter mais informações, consulte Arquivos de trabalho e tabelas de trabalho em Considerações sobre concessão de memória.
Terminologia
Vamos examinar os diferentes termos que você pode encontrar em relação a esse consumidor de memória. Novamente, todos eles descrevem conceitos relacionados às mesmas alocações de memória.
Memória de execução de consulta (memória QE): esse termo é usado para destacar o fato de que a memória de classificação ou hash é usada durante a execução de uma consulta. Normalmente, a memória QE é o maior consumidor de memória durante a vida útil de uma consulta.
Reservas de Execução de Consulta (QE) ou Reservas de Memória: quando uma consulta precisa de memória para operações de classificação ou hash, ela faz uma solicitação de reserva de memória. Essa solicitação de reserva é calculada em tempo de compilação com base na cardinalidade estimada. Posteriormente, quando a consulta for executada, o SQL Server concederá essa solicitação parcial ou totalmente, dependendo da disponibilidade de memória. No final, a consulta pode usar uma porcentagem da memória concedida. Há um funcionário de memória (contador de memória) chamado 'MEMORYCLERK_SQLQERESERVATIONS' que controla essas alocações de memória (confira DBCC MEMORYSTATUS ou sys.dm_os_memory_clerks).
Concessões de memória: quando o SQL Server concede a memória solicitada a uma consulta em execução, é dito que ocorreu uma concessão de memória. Existem alguns contadores de desempenho que usam o termo "concessão". Esses contadores
Memory Grants OutstandingeMemory Grants Pending, exibem a contagem de concessões de memória satisfeitas ou aguardando. Eles não levam em conta o tamanho da concessão de memória. Uma consulta sozinha poderia ter consumido, por exemplo, 4 GB de memória para executar uma classificação, mas isso não é refletido em nenhum desses contadores.Memória do espaço de trabalho é outro termo que descreve a mesma memória. Muitas vezes, você pode ver esse termo no contador
Granted Workspace Memory (KB)Perfmon , que reflete a quantidade geral de memória usada atualmente para operações de classificação, hash, cópia em massa e criação de índice, expressa em KB. OMaximum Workspace Memory (KB)contador , outro é responsável pela quantidade máxima de memória do workspace disponível para qualquer solicitação que possa precisar fazer essas operações de hash, classificação, cópia em massa e criação de índice. O termo Memória do Espaço de Trabalho é encontrado com pouca frequência fora desses dois contadores.
Impacto no desempenho da utilização de memória QE grande
Na maioria dos casos, quando um thread solicita memória dentro do SQL Server para fazer algo e a memória não está disponível, a solicitação falha com um erro de memória insuficiente. No entanto, há alguns cenários de exceção em que o thread não falha, mas aguarda até que a memória fique disponível. Um desses cenários são as concessões de memória e o outro é a memória de compilação de consulta. O SQL Server usa um objeto de sincronização de thread chamado semáforo para controlar quanta memória foi concedida para a execução da consulta. Se o SQL Server ficar sem o espaço de trabalho QE predefinido, em vez de falhar na consulta com um erro de memória insuficiente, ele fará com que a consulta aguarde. Considerando que a memória do workspace pode ocupar uma porcentagem significativa da memória geral do SQL Server, aguardar a memória nesse espaço tem sérias implicações de desempenho. Um grande número de consultas simultâneas solicitou memória de execução e, juntas, esgotaram o pool de memória QE ou algumas consultas simultâneas solicitaram concessões muito grandes. De qualquer forma, os problemas de desempenho resultantes podem ter os seguintes sintomas:
- As páginas de dados e índice de um cache de buffer provavelmente foram liberadas para liberar espaço para as grandes solicitações de concessão de memória. Isso significa que as leituras de página provenientes de solicitações de consulta devem ser atendidas a partir do disco (uma operação significativamente mais lenta).
- As solicitações de outras alocações de memória podem falhar com erros de falta de memória porque o recurso está vinculado a operações de classificação, hash ou criação de índice.
- As solicitações que precisam de memória de execução estão aguardando a disponibilização do recurso e estão demorando muito para serem concluídas. Em outras palavras, para o usuário final, essas consultas são lentas.
Portanto, se você observar esperas na memória de execução de consulta no Perfmon, DMVs (exibições de gerenciamento dinâmico) ou DBCC MEMORYSTATUS, deverá agir para resolver esse problema, especialmente se o problema ocorrer com frequência. Para obter mais informações, consulte O que um desenvolvedor pode fazer sobre operações de classificação e hash.
Como identificar esperas pela memória de execução de consulta
Há várias maneiras de determinar as esperas para reservas de QE. Escolha aqueles que melhor lhe servem para ver o quadro geral no nível do servidor. Algumas dessas ferramentas podem não estar disponíveis para você (por exemplo, o Perfmon não está disponível no Banco de Dados SQL do Azure). Depois de identificar o problema, você deve fazer uma busca detalhada no nível de consulta individual para ver quais consultas precisam de ajustes ou reescritas.
No nível do servidor, use os seguintes métodos:
- DMV de semáforo de recurso sys.dm_exec_query_resource_semaphores Para obter mais informações, consulte sys.dm_exec_query_resource_semaphores.
- Contadores do Monitor de Desempenho Para obter mais informações, consulte Objeto do Gerenciador de Memória do SQL Server.
- DBCC MEMORYSTATUS Para obter mais informações, consulte DBCC MEMORYSTATUS.
- Administradores de memória DMV sys.dm_os_memory_clerks Para obter mais informações, consulte sys.dm_os_memory_clerks.
- Identificar concessões de memória usando Eventos Estendidos (XEvents) Para obter mais informações, consulte Eventos Estendidos (XEvents).
No nível de consulta individual, use os seguintes métodos:
- Identifique consultas específicas com sys.dm_exec_query_memory_grants: Consultas em execução no momento. Para obter mais informações, consulte sys.dm_exec_query_memory_grants.
- Identifique consultas específicas com sys.dm_exec_requests: Consultas em execução no momento. Para obter mais informações, consulte sys.dm_exec_requests.
- Identifique consultas específicas com sys.dm_exec_query_stats: Estatísticas históricas sobre consultas. Para obter mais informações, consulte sys.dm_exec_query_stats.
- Identifique consultas específicas usando o QDS (Repositório de Consultas) com sys.query_store_runtime_stats: estatísticas históricas sobre consultas com QDS. Para obter mais informações, consulte sys.query_store_runtime_stats.
Agregar estatísticas de uso de memória
Recurso semáforo DMV sys.dm_exec_query_resource_semaphores
Essa DMV divide a memória de reserva de consulta por pool de recursos (interno, padrão e criado pelo usuário) e resource_semaphore (solicitações de consulta regulares e pequenas). Uma consulta útil pode ser:
SELECT
pool_id
,total_memory_kb
,available_memory_kb
,granted_memory_kb
,used_memory_kb
,grantee_count, waiter_count
,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs
A saída de exemplo a seguir mostra que cerca de 900 MB de memória de execução de consulta são usados por 22 solicitações e mais 3 estão aguardando. Isso ocorre no pool padrão (pool_id = 2) e no semáforo de consulta regular (resource_semaphore_id = 0).
pool_id total_memory_kb available_memory_kb granted_memory_kb used_memory_kb grantee_count waiter_count resource_semaphore_id
------- --------------- ------------------- ----------------- -------------- ------------- ------------ ---------------------
1 30880 30880 0 0 0 0 0
1 5120 5120 0 0 0 0 1
2 907104 0 907104 898656 22 3 0
2 40960 40960 0 0 0 0 1
(4 rows affected)
Contadores do Monitor de Desempenho
Informações semelhantes estão disponíveis por meio dos contadores do Monitor de Desempenho, onde você pode observar as solicitações concedidas no momento (Memory Grants Outstanding), as solicitações de concessão em espera (Memory Grants Pending) e a quantidade de memória usada pelas concessões de memória (Granted Workspace Memory (KB)). Na imagem a seguir, as concessões pendentes são 18, as concessões pendentes são 2 e a memória do workspace concedida é de 828.288 KB. O Memory Grants Pending contador Perfmon com um valor diferente de zero indica que a memória foi esgotada.
Para obter mais informações, consulte Objeto do Gerenciador de Memória do SQL Server.
- SQLServer, Gerenciador de Memória: Memória Máxima do Espaço de Trabalho (KB)
- SQLServer, Gerenciador de Memória: Concessões de Memória Pendentes
- SQLServer, Gerenciador de Memória: Concessões de Memória Pendentes
- SQLServer, Gerenciador de Memória: Memória de Espaço de Trabalho Concedida (KB)
STATUS DE MEMÓRIA DO DBCC
Outro local onde você pode ver detalhes sobre a memória de reserva de consulta é DBCC MEMORYSTATUS (seção Objetos de Memória de Consulta). Você pode examinar a saída para consultas de Query Memory Objects (default) usuário. Se você tiver habilitado o Administrador de Recursos com um pool de recursos chamado PoolAdmin, por exemplo, poderá examinar o Query Memory Objects (default) .Query Memory Objects (PoolAdmin)
Aqui está um exemplo de saída de um sistema em que 18 solicitações receberam memória de execução de consulta e 2 solicitações estão aguardando memória. O contador disponível é zero, o que indica que não há mais memória do workspace disponível. Esse fato explica os dois pedidos de espera. O Wait Time mostra o tempo decorrido em milissegundos desde que uma solicitação foi colocada na fila de espera. Para obter mais informações sobre esses contadores, consulte Objetos de memória de consulta.
Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 18
Waiting 2
Available 0
Current Max 103536
Future Max 97527
Physical Max 139137
Next Request 5752
Waiting For 8628
Cost 16
Timeout 401
Wait Time 2750
(11 rows affected)
Small Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 0
Waiting 0
Available 5133
Current Max 5133
Future Max 5133
DBCC MEMORYSTATUS Também exibe informações sobre o administrador de memória que controla a memória de execução da consulta. A saída a seguir mostra que as páginas alocadas para reservas de QE (execução de consulta) excedem 800 MB.
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
------------------------------------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 824640
Funcionários de memória DMV sys.dm_os_memory_clerks
Se você precisar de mais de um conjunto de resultados tabular, diferente do baseado em DBCC MEMORYSTATUSseção , poderá usar sys.dm_os_memory_clerks para obter informações semelhantes. Procure o funcionário da MEMORYCLERK_SQLQERESERVATIONS memória. No entanto, os Objetos de Memória de Consulta não estão disponíveis nesta DMV.
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
Aqui está um exemplo de saída:
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
Identificar concessões de memória usando Eventos Estendidos (XEvents)
Há vários eventos estendidos que fornecem informações de concessão de memória e permitem que você capture essas informações por meio de um rastreamento:
- sqlserver.additional_memory_grant: ocorre quando uma consulta tenta obter mais concessão de memória durante a execução. A falha em obter essa concessão de memória adicional pode causar lentidão na consulta.
- sqlserver.query_memory_grant_blocking: ocorre quando uma consulta está bloqueando outras consultas enquanto aguarda uma concessão de memória.
- sqlserver.query_memory_grant_info_sampling: ocorre no final das consultas amostradas aleatoriamente que fornecem informações de concessão de memória (pode ser usado, por exemplo, para telemetria).
- sqlserver.query_memory_grant_resource_semaphores: Ocorre em intervalos de cinco minutos para cada pool de recursos do administrador de recursos.
- sqlserver.query_memory_grant_usage: ocorre no final do processamento de consultas com concessões de memória acima de 5 MB para informar os usuários sobre imprecisões de concessão de memória.
- sqlserver.query_memory_grants: Ocorre em intervalos de cinco minutos para cada consulta com uma concessão de memória.
Eventos estendidos de comentários de concessão de memória
Para obter informações sobre os recursos de comentários de concessão de memória de processamento de consulta, consulte Comentários de concessão de memória.
- sqlserver.memory_grant_feedback_loop_disabled: Ocorre quando o loop de feedback de concessão de memória está desabilitado.
- sqlserver.memory_grant_updated_by_feedback: ocorre quando a concessão de memória é atualizada por comentários.
Avisos de execução de consulta relacionados a concessões de memória
- sqlserver.execution_warning: ocorre quando uma instrução T-SQL ou procedimento armazenado aguarda mais de um segundo por uma concessão de memória ou quando a tentativa inicial de obter memória falha. Use esse evento em combinação com eventos que identificam esperas para solucionar problemas de contenção que afetam o desempenho.
- sqlserver.hash_spill_details: Ocorre no final do processamento de hash se não houver memória suficiente para processar a entrada de build de uma junção de hash. Use esse evento junto com qualquer um dos
query_pre_execution_showplaneventos orquery_post_execution_showplanpara determinar qual operação no plano gerado está causando o vazamento de hash. - sqlserver.hash_warning: ocorre quando não há memória suficiente para processar a entrada de build de uma junção de hash. Isso resulta em uma recursão de hash quando a entrada de compilação é particionada ou um resgate de hash quando o particionamento da entrada de compilação excede o nível máximo de recursão. Use esse evento junto com qualquer um dos
query_pre_execution_showplaneventos orquery_post_execution_showplanpara determinar qual operação no plano gerado está causando o aviso de hash. - sqlserver.sort_warning: ocorre quando a operação de classificação em uma consulta em execução não cabe na memória. Esse evento não é gerado para operações de classificação causadas pela criação de índice, apenas para operações de classificação em uma consulta. (Por exemplo, um
Order Byem umaSelectdeclaração.) Use esse evento para identificar consultas que são executadas lentamente devido à operação de classificação, especialmente quando owarning_type= 2, indicando várias passagens sobre os dados, foi necessário para classificar.
Planejar a geração de eventos que contêm informações de concessão de memória
O plano de consulta a seguir que gera eventos estendidos contém campos granted_memory_kb e ideal_memory_kb por padrão:
- sqlserver.query_plan_profile
- sqlserver.query_post_execution_plan_profile
- sqlserver.query_post_execution_showplan
- sqlserver.query_pre_execution_showplan
Criação de índice de repositório de colunas
Uma das áreas cobertas por XEvents é a memória de execução usada durante a criação do repositório de colunas. Esta é uma lista de eventos disponíveis:
- sqlserver.column_store_index_build_low_memory: O Mecanismo de Armazenamento detectou uma condição de memória baixa e o tamanho do rowgroup foi reduzido. Existem várias colunas de interesse aqui.
- sqlserver.column_store_index_build_memory_trace: rastreie o uso de memória durante a compilação do índice.
- sqlserver.column_store_index_build_memory_usage_scale_down: O mecanismo de armazenamento foi reduzido.
- sqlserver.column_store_index_memory_estimation: mostra o resultado da estimativa de memória durante a compilação do grupo de linhas COLUMNSTORE.
Identificar consultas específicas
Há dois tipos de consultas que você pode encontrar ao examinar o nível de solicitação individual. As consultas que estão consumindo uma grande quantidade de memória de execução de consulta e aquelas que estão aguardando a mesma memória. O último grupo pode consistir em solicitações com necessidades modestas de concessões de memória e, em caso afirmativo, você pode concentrar sua atenção em outro lugar. Mas eles também podem ser os culpados se estiverem solicitando tamanhos de memória enormes. Concentre-se neles se achar que é esse o caso. Pode ser comum descobrir que uma consulta específica é o infrator, mas muitas instâncias dela são geradas. Essas instâncias que obtêm as concessões de memória estão fazendo com que outras instâncias da mesma consulta aguardem a concessão. Independentemente de circunstâncias específicas, em última análise, você deve identificar as consultas e o tamanho da memória de execução solicitada.
Identifique consultas específicas com sys.dm_exec_query_memory_grants
Para exibir solicitações individuais e o tamanho da memória que elas solicitaram e foram concedidas, você pode consultar a exibição de sys.dm_exec_query_memory_grants gerenciamento dinâmico. Essa DMV mostra informações sobre consultas em execução no momento, não informações históricas.
A instrução a seguir obtém dados da DMV e também busca o texto da consulta e o plano de consulta como resultado:
SELECT
session_id
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
,queue_id
,wait_order
,wait_time_ms
,is_next_candidate
,pool_id
,text
,query_plan
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Aqui está um exemplo abreviado de saída da consulta durante o consumo de memória QE ativo. A maioria das consultas tem sua memória concedida, como mostrado por granted_memory_kb e used_memory_kb sendo valores numéricos não NULL. As consultas que não tiveram sua solicitação concedida estão aguardando a memória de execução, e o granted_memory_kb = NULL. Além disso, eles são colocados em uma fila de espera com um queue_id = 6. Indica wait_time_ms cerca de 37 segundos de espera. A sessão 72 é a próxima na fila para obter uma concessão, conforme indicado por wait_order = 1, enquanto a sessão 74 vem depois com wait_order = 2.
session_id requested_memory_kb granted_memory_kb used_memory_kb queue_id wait_order wait_time_ms is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80 41232 41232 40848 NULL NULL NULL NULL 2
83 41232 41232 40848 NULL NULL NULL NULL 2
84 41232 41232 40848 NULL NULL NULL NULL 2
74 41232 NULL NULL 6 2 37438 0 2
78 41232 41232 40848 NULL NULL NULL NULL 2
81 41232 41232 40848 NULL NULL NULL NULL 2
71 41232 41232 40848 NULL NULL NULL NULL 2
75 41232 NULL NULL 6 0 37438 1 2
82 41232 41232 40848 NULL NULL NULL NULL 2
76 41232 41232 40848 NULL NULL NULL NULL 2
79 41232 41232 40848 NULL NULL NULL NULL 2
85 41232 41232 40848 NULL NULL NULL NULL 2
70 41232 41232 40848 NULL NULL NULL NULL 2
55 41232 41232 40848 NULL NULL NULL NULL 2
59 41232 NULL NULL 6 3 37438 0 2
62 41232 41232 40848 NULL NULL NULL NULL 2
54 41232 41232 40848 NULL NULL NULL NULL 2
77 41232 41232 40848 NULL NULL NULL NULL 2
52 41232 41232 40848 NULL NULL NULL NULL 2
72 41232 NULL NULL 6 1 37438 0 2
69 41232 41232 40848 NULL NULL NULL NULL 2
73 41232 41232 40848 NULL NULL NULL NULL 2
66 41232 NULL NULL 6 4 37438 0 2
68 41232 41232 40848 NULL NULL NULL NULL 2
63 41232 41232 40848 NULL NULL NULL NULL 2
Identifique consultas específicas com sys.dm_exec_requests
Há um tipo de espera no SQL Server que indica que uma consulta está aguardando a concessão de RESOURCE_SEMAPHOREmemória. Você pode observar esse tipo de espera para sys.dm_exec_requests solicitações individuais. Este último DMV é o melhor ponto de partida para identificar quais consultas são vítimas de memória de concessão insuficiente. Você também pode observar a RESOURCE_SEMAPHORE espera em sys.dm_os_wait_stats como pontos de dados agregados no nível do SQL Server. Esse tipo de espera aparece quando uma solicitação de memória de consulta não pode ser concedida devido a outras consultas simultâneas terem usado a memória. Uma alta contagem de solicitações em espera e longos tempos de espera indicam um número excessivo de consultas simultâneas usando memória de execução ou tamanhos de solicitação de memória grandes.
Observação
O tempo de espera para concessões de memória é finito. Após uma espera excessiva (por exemplo, mais de 20 minutos), o SQL Server atinge o tempo limite da consulta e gera o erro 8645: "Ocorreu um tempo limite enquanto aguardava a execução da consulta pelos recursos de memória. Execute novamente a consulta." Você pode ver o valor de tempo limite definido no nível do servidor observando timeout_sec em sys.dm_exec_query_memory_grants. O valor de tempo limite pode variar um pouco entre as versões do SQL Server.
Com o uso do sys.dm_exec_requests, você pode ver quais consultas receberam memória e o tamanho dessa concessão. Além disso, você pode identificar quais consultas estão aguardando uma concessão de memória no momento procurando o tipo de RESOURCE_SEMAPHORE espera. Aqui está uma consulta que mostra as solicitações concedidas e em espera:
SELECT session_id, wait_type, wait_time, granted_query_memory, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE granted_query_memory > 0
OR wait_type = 'RESOURCE_SEMAPHORE'
Uma saída de exemplo mostra que duas solicitações receberam memória e duas dúzias de outras estão aguardando concessões. A granted_query_memory coluna relata o tamanho em páginas de 8 KB. Por exemplo, um valor de 34.709 significa 34.709 * 8 KB = 277.672 KB de memória concedida.
session_id wait_type wait_time granted_query_memory text
---------- ----------------------------------- -------------------- -------------------------------------------------------------------
65 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
66 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
67 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
68 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
69 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
70 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
71 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
72 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
73 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
74 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
75 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
76 ASYNC_NETWORK_IO 11 34709 select * from sys.messages order by message_id option (maxdop 1)
77 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
78 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
79 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
80 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
81 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
82 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
83 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
84 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
85 ASYNC_NETWORK_IO 14 34709 select * from sys.messages order by message_id option (maxdop 1)
86 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
87 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
88 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
89 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
Identifique consultas específicas com sys.dm_exec_query_stats
Se o problema de concessão de memória não estiver acontecendo no momento, mas você quiser identificar as consultas ofensivas, poderá examinar os dados históricos da consulta por meio de sys.dm_exec_query_stats. O tempo de vida dos dados está vinculado ao plano de consulta de cada consulta. Quando um plano é removido do cache de planos, as linhas correspondentes são eliminadas dessa exibição. Em outras palavras, a DMV mantém estatísticas na memória que não são preservadas após uma reinicialização do SQL Server ou depois que a pressão de memória causa uma liberação de cache de plano. Dito isso, você pode achar as informações aqui valiosas, principalmente para estatísticas de consulta agregadas. Alguém pode ter relatado recentemente ter visto grandes concessões de memória de consultas, mas quando você olha para a carga de trabalho do servidor, pode descobrir que o problema desapareceu. Nessa situação, sys.dm_exec_query_stats pode fornecer os insights que outros DVMs não podem. Aqui está um exemplo de consulta que pode ajudá-lo a encontrar as 20 principais instruções que consumiram as maiores quantidades de memória de execução. Essa saída exibe instruções individuais mesmo que sua estrutura de consulta seja a mesma. Por exemplo, SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 é uma linha separada de (somente o valor do predicado de SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 filtro varia). A consulta obtém as 20 principais instruções com um tamanho máximo de concessão maior que 5 MB.
SELECT TOP 20
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
,CONVERT(DECIMAL (10,2), max_grant_kb /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), min_grant_kb /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (total_grant_kb / execution_count) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), max_used_grant_kb /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), min_used_grant_kb /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_used_grant_kb/ execution_count) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ execution_count) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ 1024.0)) AS total_grant_for_all_executions_mb
,execution_count
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE max_grant_kb > 5120 -- greater than 5 MB
ORDER BY max_grant_kb DESC
Uma visão ainda mais poderosa pode ser obtida observando as consultas agregadas pelo query_hash. Este exemplo ilustra como encontrar os tamanhos médios, máximos e mínimos de concessão para uma instrução de consulta em todas as suas instâncias desde que o plano de consulta foi armazenado em cache pela primeira vez.
SELECT TOP 20
MAX(SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1)) AS sample_statement_text
,CONVERT(DECIMAL (10,2), SUM(max_grant_kb) /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), SUM(min_grant_kb) /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (SUM(total_grant_kb) / SUM(execution_count)) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), SUM(max_used_grant_kb) /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), SUM(min_used_grant_kb) /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_used_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_ideal_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), SUM(total_grant_kb) /1024.0) AS total_grant_all_executions_mb
,SUM(execution_count) AS execution_count
,query_hash
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
GROUP BY query_hash
HAVING SUM(max_grant_kb) > 5120 -- greater than 5 MB
ORDER BY SUM(max_grant_kb) DESC
OPTION (MAX_GRANT_PERCENT = 5)
A Sample_Statement_Text coluna mostra um exemplo da estrutura de consulta que corresponde ao hash de consulta, mas deve ser lida sem considerar valores específicos na instrução. Por exemplo, se uma instrução contém WHERE Id = 5, você pode lê-la em sua forma mais genérica: WHERE Id = @any_value.
Aqui está um exemplo de saída abreviado da consulta com apenas as colunas selecionadas mostradas:
sample_statement_text max_grant_mb avg_grant_mb max_grant_used_mb avg_grant_used_mb avg_ideal_grant_mb total_grant_all_executions_mb execution_count
----------------------------------------- ------------ ------------- ----------------- ----------------- ------------------ ----------------------------- ----------------
select de.ObjectName,de.CounterName,d 282.45 282.45 6.50 6.50 282.45 282.45 1
SELECT SCHEMA_NAME(udf.schema_id) AS [Sch 33.86 8.55 7.80 1.97 8.55 42.74 5
insert into #tmpCounterDateTime (CounterD 32.45 32.45 3.11 3.11 32.45 32.45 1
select db_id() dbid, db_name() dbname, * 20.80 1.30 5.75 0.36 1.30 20.80 16
SELECT SCHEMA_NAME(obj.schema_id) AS [Sch 20.55 5.19 5.13 1.28 5.19 25.93 5
SELECT xmlplan FROM (SELECT ROW_NUMBER() 19.69 1.97 1.09 0.11 1.97 19.69 10
if ( select max(cast(countervalue as floa 16.39 8.20 0.77 0.38 8.20 16.39 2
SELECT udf.name AS [Name], udf.object_id 11.36 5.08 1.66 0.83 5.08 20.33 4
select --* Database_I 10.94 5.47 1.98 0.99 5.47 10.94 2
IF (select max(cast(dat.countervalue as f 8.00 1.00 0.00 0.00 0.53 8.00 8
insert into #tmpCounterDateTime (CounterD 5.72 2.86 1.98 0.99 2.86 5.72 2
INSERT INTO #tmp (CounterDateTime, Counte 5.39 1.08 1.64 0.33 1.08 6.47 6
Identificar consultas específicas usando o QDS (Repositório de Consultas) com sys.query_store_runtime_stats
Se você tiver o Repositório de Consultas habilitado, poderá aproveitar suas estatísticas históricas persistentes. Ao contrário dos dados do sys.dm_exec_query_stats, essas estatísticas sobrevivem a uma reinicialização do SQL Server ou à pressão de memória porque são armazenadas em um banco de dados. O QDS também tem limites de tamanho e uma política de retenção. Para obter mais informações, consulte as seções Definir o modo de captura do Repositório de Consultas ideal e Manter os dados mais relevantes no Repositório de Consultas em Práticas recomendadas para gerenciar o Repositório de Consultas.
Identifique se seus bancos de dados têm o Repositório de Consultas habilitado usando esta consulta:
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1Execute a seguinte consulta de diagnóstico no contexto de um banco de dados específico que você deseja investigar:
SELECT MAX(qtxt.query_sql_text) AS sample_sql_text ,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb ,SUM(count_executions) AS count_query_executions FROM sys.query_store_runtime_stats rts JOIN sys.query_store_plan p ON p.plan_id = rts.plan_id JOIN sys.query_store_query q ON p.query_id = q.query_id LEFT OUTER JOIN sys.query_store_query_text qtxt ON q.query_text_id = qtxt.query_text_id GROUP BY q.query_hash HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB ORDER BY SUM(avg_query_max_used_memory) DESC OPTION (MAX_GRANT_PERCENT = 5)Os princípios aqui são os mesmos que
sys.dm_exec_query_stats; você vê estatísticas agregadas para as declarações. No entanto, uma diferença é que, com o QDS, você está olhando apenas para consultas no escopo desse banco de dados, não para todo o SQL Server. Portanto, talvez seja necessário conhecer o banco de dados no qual uma solicitação de concessão de memória específica foi executada. Caso contrário, execute essa consulta de diagnóstico em vários bancos de dados até encontrar as concessões de memória consideráveis.Aqui está um exemplo de saída abreviado:
sample_sql_text avg_mem_grant_used_mb min_mem_grant_used_mb max_mem_grant_used_mb stdev_mem_grant_used_mb last_mem_grant_used_mb count_query_executions ----------------------------------------- ---------------------- ---------------------- ---------------------- ------------------------ ----------------------- ---------------------- SELECT qtxt.query_sql_text ,CONVERT(D 550.16 550.00 550.00 0.00 550.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 61.00 36.00 65.00 10.87 51.00 14 SELECT qtxt.query_sql_text ,q.* ,rts 25.46 25.00 25.00 0.00 25.00 2 insert into #tmpStats select 5 'Database 13.69 13.00 13.00 0.03 13.00 16 SELECT q.* ,rts 11.93 11.00 12.00 0.23 12.00 2 SELECT * ,rts.avg_query_max_used_memory 9.70 9.00 9.00 0.00 9.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 9.32 9.00 9.00 0.00 9.00 1 select db_id() dbid, db_name() dbname, * 7.33 7.00 7.00 0.00 7.00 9 SELECT q.* ,rts.avg_query_max_used_memo 6.65 6.00 6.00 0.00 6.00 1 (@_msparam_0 nvarchar(4000),@_msparam_1 5.17 4.00 5.00 0.68 4.00 2
Uma consulta de diagnóstico personalizada
Aqui está uma consulta que combina dados de várias exibições, incluindo as três listadas anteriormente. Ele fornece uma visão mais completa das sessões e suas concessões via sys.dm_exec_requests e sys.dm_exec_query_memory_grants, além das estatísticas no nível do servidor fornecidas pelo sys.dm_exec_query_resource_semaphores.
Observação
Essa consulta retornaria duas linhas por sessão devido ao uso de sys.dm_exec_query_resource_semaphores (uma linha para o semáforo de recurso regular e outra para o semáforo de recurso de consulta pequena).
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime
, r.session_id
, r.wait_time
, r.wait_type
, mg.request_time
, mg.grant_time
, mg.requested_memory_kb
/ 1024 requested_memory_mb
, mg.granted_memory_kb
/ 1024 AS granted_memory_mb
, mg.required_memory_kb
/ 1024 AS required_memory_mb
, max_used_memory_kb
/ 1024 AS max_used_memory_mb
, rs.pool_id as resource_pool_id
, mg.query_cost
, mg.timeout_sec
, mg.resource_semaphore_id
, mg.wait_time_ms AS memory_grant_wait_time_ms
, CASE mg.is_next_candidate
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Memory has been granted'
END AS 'Next Candidate for Memory Grant'
, r.command
, ltrim(rtrim(replace(replace (substring (q.text, 1, 1000), char(10), ' '), char(13), ' '))) [text]
, rs.target_memory_kb
/ 1024 AS server_target_grant_memory_mb
, rs.max_target_memory_kb
/ 1024 AS server_max_target_grant_memory_mb
, rs.total_memory_kb
/ 1024 AS server_total_resource_semaphore_memory_mb
, rs.available_memory_kb
/ 1024 AS server_available_memory_for_grants_mb
, rs.granted_memory_kb
/ 1024 AS server_total_granted_memory_mb
, rs.used_memory_kb
/ 1024 AS server_used_granted_memory_mb
, rs.grantee_count AS successful_grantee_count
, rs.waiter_count AS grant_waiters_count
, rs.timeout_error_count
, rs.forced_grant_count
, mg.dop
, r.blocking_session_id
, r.cpu_time
, r.total_elapsed_time
, r.reads
, r.writes
, r.logical_reads
, r.row_count
, s.login_time
, d.name
, s.login_name
, s.host_name
, s.nt_domain
, s.nt_user_name
, s.status
, c.client_net_address
, s.program_name
, s.client_interface_name
, s.last_request_start_time
, s.last_request_end_time
, c.connect_time
, c.last_read
, c.last_write
, qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_connections c
ON r.connection_id = c.connection_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
INNER JOIN sys.databases d
ON r.database_id = d.database_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle ) AS q
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
OPTION (MAXDOP 1, LOOP JOIN )
Observação
A LOOP JOIN dica é usada nessa consulta de diagnóstico para evitar uma concessão de memória pela própria consulta e nenhuma ORDER BY cláusula é usada. Se a consulta de diagnóstico acabar aguardando uma concessão em si, sua finalidade de diagnosticar concessões de memória será anulada. A LOOP JOIN dica pode fazer com que a consulta de diagnóstico seja mais lenta, mas, nesse caso, é mais importante obter os resultados do diagnóstico.
Aqui está uma saída de exemplo abreviada dessa consulta de diagnóstico com apenas colunas selecionadas.
| session_id | wait_time | wait_type | requested_memory_mb | granted_memory_mb | required_memory_mb | max_used_memory_mb | resource_pool_id |
|---|---|---|---|---|---|---|---|
| 60 | 0 | NULO | 9 | 9 | 7 | 1 | 1 |
| 60 | 0 | NULO | 9 | 9 | 7 | 1 | 2 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULO | 0 | NULO | 1 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULO | 0 | NULO | 2 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULO | 0 | NULO | 1 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULO | 0 | NULO | 2 |
A saída de exemplo ilustra claramente como uma consulta enviada por session_id = 60 obteve com êxito a concessão de memória de 9 MB solicitada, mas apenas 7 MB foram necessários para iniciar a execução da consulta com êxito. No final, a consulta usou apenas 1 MB dos 9 MB recebidos do servidor. A saída também mostra que as sessões 75 e 86 estão aguardando concessões de memória, portanto, o RESOURCE_SEMAPHORE wait_type. Seu tempo de espera foi superior a 1.300 segundos (21 minutos) e seu granted_memory_mb é NULL.
Essa consulta de diagnóstico é um exemplo, portanto, sinta-se à vontade para modificá-la da maneira que atender às suas necessidades. Uma versão dessa consulta também é usada em ferramentas de diagnóstico que o suporte do Microsoft SQL Server usa.
Ferramentas de diagnóstico
Há ferramentas de diagnóstico que o suporte técnico do Microsoft SQL Server usa para coletar logs e solucionar problemas com mais eficiência. O SQL LogScout e o Pssdiag Configuration Manager (juntamente com o SQLDiag) coletam saídas das DMVs descritas anteriormente e dos contadores do Monitor de Desempenho que podem ajudá-lo a diagnosticar problemas de concessão de memória.
Se você executar o SQL LogScout com cenários LightPerf, GeneralPerf ou DetailedPerf , a ferramenta coletará os logs necessários. Em seguida, você pode examinar manualmente o YourServer_PerfStats.out e procurar -- dm_exec_query_resource_semaphores -- e -- dm_exec_query_memory_grants -- saídas. Ou, em vez de exame manual, você pode usar o SQL Nexus para importar a saída proveniente do SQL LogScout ou PSSDIAG para um banco de dados do SQL Server. O SQL Nexus cria duas tabelas tbl_dm_exec_query_resource_semaphores e tbl_dm_exec_query_memory_grants, que contêm as informações necessárias para diagnosticar concessões de memória. O SQL LogScout e o PSSDIAG também coletam logs do Perfmon na forma de . BLG , que podem ser usados para revisar os contadores de desempenho descritos na seção Contadores do Monitor de Desempenho.
Por que as concessões de memória são importantes para um desenvolvedor ou DBA
Com base na experiência de suporte da Microsoft, os problemas de concessão de memória tendem a ser alguns dos problemas mais comuns relacionados à memória. Os aplicativos geralmente executam consultas aparentemente simples que podem acabar causando problemas de desempenho no SQL Server devido a grandes operações de classificação ou hash. Essas consultas não apenas consomem muita memória do SQL Server, mas também fazem com que outras consultas aguardem a disponibilidade de memória, portanto, o gargalo de desempenho.
Usando as ferramentas descritas aqui (DMVs, contadores Perfmon e planos de consulta reais), você pode identificar quais consultas são consumidores de concessão grande. Em seguida, você pode ajustar ou reescrever essas consultas para resolver ou reduzir o uso de memória do workspace.
O que um desenvolvedor pode fazer sobre operações de classificação e hash
Depois de identificar consultas específicas que consomem uma grande quantidade de memória de reserva de consulta, você pode tomar medidas para reduzir as concessões de memória reprojetando essas consultas.
O que causa operações de classificação e hash em consultas
A primeira etapa é ficar ciente de quais operações em uma consulta podem levar a concessões de memória.
Razões pelas quais uma consulta usaria um operador SORT:
ORDER BY (T-SQL) faz com que as linhas sejam classificadas antes de serem transmitidas como resultado final.
GROUP BY (T-SQL) pode introduzir um operador de classificação em um plano de consulta antes do agrupamento se um índice subjacente não estiver presente que ordene as colunas agrupadas.
DISTINCT (T-SQL) se comporta de forma semelhante ao
GROUP BY. Para identificar linhas distintas, os resultados intermediários são ordenados e, em seguida, as duplicatas são removidas. O otimizador usa umSortoperador anterior a esse operador se os dados ainda não estiverem classificados devido a uma busca ou verificação de índice ordenada.O operador Merge Join , quando selecionado pelo otimizador de consulta, requer que ambas as entradas unidas sejam classificadas. O SQL Server poderá disparar uma classificação se um índice clusterizado não estiver disponível na coluna de junção em uma das tabelas.
Motivos pelos quais uma consulta usaria um operador de plano de consulta HASH:
Esta lista não é exaustiva, mas inclui os motivos mais comumente encontrados para operações de Hash. Analise o plano de consulta para identificar as operações de correspondência de hash.
JOIN (T-SQL): ao unir tabelas, o SQL Server pode escolher entre três operadores físicos,
Nested Loop,Merge JoineHash Join. Se o SQL Server acabar escolhendo uma junção de hash, ele precisará de memória QE para que os resultados intermediários sejam armazenados e processados. Normalmente, a falta de bons índices pode levar a esse operador de junção mais caro em recursos,Hash Joino . Para examinar o plano de consulta para identificarHash Match, consulte Referência de operadores lógicos e físicos.DISTINCT (T-SQL): Um
Hash Aggregateoperador pode ser usado para eliminar duplicatas em um conjunto de linhas. Para procurar umHash Match(Aggregate) no plano de consulta, consulte Referência de operadores lógicos e físicos.UNIÃO (T-SQL): é semelhante ao
DISTINCT. AHash Aggregatepode ser usado para remover as duplicatas desse operador.SUM/AVG/MAX/MIN (T-SQL): qualquer operação agregada pode ser executada como um
Hash Aggregate. Para procurar umHash Match(Aggregate) no plano de consulta, consulte Referência de operadores lógicos e físicos.
Conhecer esses motivos comuns pode ajudá-lo a eliminar, tanto quanto possível, as grandes solicitações de concessão de memória que chegam ao SQL Server.
Maneiras de reduzir as operações de classificação e hash ou o tamanho da concessão
- Mantenha as estatísticas atualizadas. Essa etapa fundamental, que melhora o desempenho de consultas em vários níveis, garante que o otimizador de consulta tenha as informações mais precisas ao selecionar planos de consulta. O SQL Server determina o tamanho a ser solicitado para sua concessão de memória com base nas estatísticas. Estatísticas desatualizadas podem causar superestimação ou subestimação da solicitação de concessão e, portanto, levar a uma solicitação de concessão desnecessariamente alta ou ao derramamento de resultados para o disco, respectivamente. Certifique-se de que as estatísticas de atualização automática estejam habilitadas em seus bancos de dados e/ou mantenha as estatísticas atualizadas com UPDATE STATISTICS ou sp_updatestats.
- Reduza o número de linhas provenientes de tabelas. Se você usar um filtro WHERE mais restritivo ou um JOIN e reduzir o número de linhas, uma classificação subsequente no plano de consulta ordenará ou agregará um conjunto de resultados menor. Um conjunto de resultados intermediário menor requer menos memória de conjunto de trabalho. Essa é uma regra geral que os desenvolvedores podem seguir não apenas para economizar memória do conjunto de trabalho, mas também para reduzir a CPU e a E/S (essa etapa nem sempre é possível). Se consultas bem escritas e eficientes em termos de recursos já estiverem em vigor, essa diretriz foi atendida.
- Crie índices em colunas de junção para ajudar a mesclar junções. As operações intermediárias em um plano de consulta são afetadas pelos índices na tabela subjacente. Por exemplo, se uma tabela não tiver índice em uma coluna de junção e uma junção de mesclagem for considerada o operador de junção mais econômico, todas as linhas dessa tabela deverão ser classificadas antes que a junção seja executada. Se, em vez disso, existir um índice na coluna, uma operação de classificação poderá ser eliminada.
- Crie índices para ajudar a evitar operações de hash. Normalmente, o ajuste básico de consulta começa com a verificação se suas consultas têm índices apropriados para ajudá-las a reduzir leituras e minimizar ou eliminar grandes classificações ou operações de hash sempre que possível. As junções de hash são comumente selecionadas para processar entradas grandes, não classificadas e não indexadas. A criação de índices pode alterar essa estratégia do otimizador e acelerar a recuperação de dados. Para obter ajuda na criação de índices, consulte Orientador de Otimização do Mecanismo de Banco de Dados e Ajustar índices não clusterizados com sugestões de índice ausentes.
- Use índices COLUMNSTORE quando apropriado para consultas de agregação que usam
GROUP BY. As consultas de análise que lidam com conjuntos de linhas muito grandes e normalmente executam agregações "agrupar por" podem precisar de grandes blocos de memória para realizar o trabalho. Se não estiver disponível um índice que forneça resultados ordenados, uma classificação será introduzida automaticamente no plano de consulta. Um tipo de resultado muito grande pode levar a uma concessão de memória cara. - Remova o
ORDER BYse você não precisar dele. Nos casos em que os resultados são transmitidos para um aplicativo que classifica os resultados à sua própria maneira ou permite que o usuário modifique a ordem dos dados exibidos, você não precisa executar uma classificação no lado do SQL Server. Basta transmitir os dados para o aplicativo na ordem em que o servidor os produz e deixar o usuário final classificá-los por conta própria. Aplicativos de relatório, como o Power BI ou o Reporting Services, são exemplos desses aplicativos que permitem que os usuários finais classifiquem seus dados. - Considere, embora com cautela, o uso de uma dica LOOP JOIN quando houver junções em uma consulta T-SQL. Essa técnica pode evitar junções de hash ou mesclagem que usam concessões de memória. No entanto, essa opção é sugerida apenas como último recurso, pois forçar uma junção pode levar a uma consulta significativamente mais lenta. Teste de estresse sua carga de trabalho para garantir que essa seja uma opção. Em alguns casos, uma junção de loop aninhada pode nem ser uma opção. Nesse caso, o SQL Server pode falhar com o erro MSSQLSERVER_8622 "O processador de consultas não pôde produzir um plano de consulta devido às dicas definidas nesta consulta".
Dica de consulta de concessão de memória
Desde o SQL Server 2012 SP3, existe uma dica de consulta que permite controlar o tamanho da concessão de memória por consulta. Aqui está um exemplo de como você pode usar essa dica:
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
Recomendamos que você use valores conservadores aqui, especialmente nos casos em que você espera que muitas instâncias de sua consulta sejam executadas simultaneamente. Certifique-se de testar sua carga de trabalho para corresponder ao seu ambiente de produção e determinar quais valores usar.
Para obter mais informações, consulte MAX_GRANT_PERCENT e MIN_GRANT_PERCENT.
Resource Governor
A memória QE é a memória que o Administrador de Recursos realmente limita quando as configurações de MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT são usadas. Depois de identificar consultas que causam grandes concessões de memória, você pode limitar a memória usada por sessões ou aplicativos. Vale a pena mencionar que o default grupo de carga de trabalho permite que uma consulta ocupe até 25% da memória que pode ser concedida em uma instância do SQL Server. Para obter mais informações, consulte Resource Governor Resource Pools e CREATE WORKLOAD GROUP.
Processamento de consulta adaptável e feedback de concessão de memória
O SQL Server 2017 introduziu o recurso de comentários de concessão de memória. Ele permite que o mecanismo de execução de consulta ajuste a concessão dada à consulta com base no histórico anterior. O objetivo é reduzir o tamanho da concessão quando possível ou aumentá-la quando for necessária mais memória. Esse recurso foi lançado em três ondas:
- Comentários sobre a concessão de memória no modo de lote no SQL Server 2017
- Comentários de concessão de memória de modo de linha no SQL Server 2019
- Comentários de concessão de memória persistência em disco usando o Repositório de Consultas e a concessão de percentil no SQL Server 2022
Para saber mais, confira Feedback de concessão de memória. O recurso de concessão de memória pode reduzir o tamanho das concessões de memória para consultas em tempo de execução e, assim, reduzir os problemas decorrentes de grandes solicitações de concessão. Com esse recurso em vigor, especialmente no SQL Server 2019 e versões posteriores, em que o processamento adaptável do modo de linha está disponível, talvez você nem perceba nenhum problema de memória proveniente da execução da consulta. No entanto, se você tiver esse recurso em vigor (ativado por padrão) e ainda observar um grande consumo de memória QE, aplique as etapas discutidas anteriormente para reescrever consultas.
Aumentar a memória do SQL Server ou do sistema operacional
Depois de executar as etapas para reduzir concessões de memória desnecessárias para suas consultas, se você ainda tiver problemas de memória insuficiente relacionados, a carga de trabalho provavelmente exigirá mais memória. Portanto, considere aumentar a memória do SQL Server usando a max server memory configuração se houver memória física suficiente no sistema para fazer isso. Siga as recomendações sobre deixar cerca de 25% da memória para o sistema operacional e outras necessidades. Para obter mais informações, consulte Opções de configuração de memória do servidor. Se não houver memória suficiente disponível no sistema, considere adicionar RAM física ou, se for uma máquina virtual, aumentar a RAM dedicada para sua VM.
Internos de concessão de memória
Para saber mais sobre alguns componentes internos da memória de execução de consulta, consulte a postagem no blog Noções básicas sobre concessão de memória do SQL Server.
Como criar um cenário de desempenho com uso intenso de concessão de memória
Por fim, o exemplo a seguir ilustra como simular um grande consumo de memória de execução de consulta e introduzir consultas aguardando RESOURCE_SEMAPHORE. Você pode fazer isso para aprender a usar as ferramentas e técnicas de diagnóstico descritas neste artigo.
Aviso
Não use isso em um sistema de produção. Esta simulação é fornecida para ajudá-lo a entender o conceito e ajudá-lo a aprendê-lo melhor.
Em um servidor de teste, instale os Utilitários RML e o SQL Server.
Use um aplicativo cliente como o SQL Server Management Studio para reduzir a configuração de memória máxima do servidor do SQL Server para 1.500 MB:
EXEC sp_configure 'max server memory', 1500 RECONFIGUREAbra um prompt de comando e altere o diretório para a pasta de utilitários RML:
cd C:\Program Files\Microsoft Corporation\RMLUtilsUse ostress.exe para gerar várias solicitações simultâneas no SQL Server de teste. Este exemplo usa 30 sessões simultâneas, mas você pode alterar esse valor:
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30Use as ferramentas de diagnóstico descritas anteriormente para identificar os problemas de concessão de memória.
Resumo das maneiras de lidar com grandes concessões de memória
- Reescreva consultas.
- Atualize as estatísticas e mantenha-as atualizadas regularmente.
- Crie índices apropriados para a consulta ou consultas identificadas. Os índices podem reduzir o grande número de linhas processadas, alterando assim os
JOINalgoritmos e reduzindo o tamanho das concessões ou eliminando-as completamente. - Use a
OPTIONdica (min_grant_percent = XX, max_grant_percent = XX). - Use o Administrador de Recursos.
- O SQL Server 2017 e 2019 usam o processamento de consulta adaptável, permitindo que o mecanismo de comentários de concessão de memória ajuste o tamanho da concessão de memória dinamicamente em runtime. Esse recurso pode evitar problemas de concessão de memória em primeiro lugar.
- Aumente a memória do SQL Server ou do sistema operacional.