Solucionar problemas de desempenho lento ou memória baixa causados por concessões de memória em 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 do Workspace e Reservas de Memória, descrevem o uso da memória na hora da execução da consulta. 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 no runtime para criação de índice (não um problema comum)
Para fornecer algum contexto, durante o tempo de vida, uma consulta pode solicitar memória de diferentes alocadores de memória ou funcionários, dependendo do que ele 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 de 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á 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 usa toda ou parte da memória para classificar resultados 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 de consulta for concluída, a concessão de memória será liberada de volta para SQL Server a ser usada para 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, o tipo poderá levar muitos segundos ou minutos e a memória concedida será usada para 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 a 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 classifica-a. 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ê poderá 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 Plano de Execução XML... e localizar um elemento XML que mostra 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). No runtime, 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 inicialmente solicitada (MaxUsedMemory). Se o otimizador de consulta superestimou 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 derramadas no disco para fazer o trabalho na hora da execução. Em vez de alocar mais memória do que o tamanho solicitado inicialmente, SQL Server envia as linhas extras para o disco e a usa como um workspace temporário. Para obter mais informações, consulte Workfiles e Worktables in Memory Grant Considerations.
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): Este termo é usado para realçar o fato de que a memória de hash ou classificação é usada durante a execução de uma consulta. Normalmente, a memória QE é o maior consumidor de memória durante a vida de uma consulta.
Reservas de QE (execução de consulta) 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 para memória. Essa solicitação de reserva é calculada em tempo de compilação com base na cardinalidade estimada. Posteriormente, quando a consulta for executada, 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 contador de memória (contador de memória) chamado 'MEMORYCLERK_SQLQERESERVATIONS' que mantém o controle dessas alocações de memória (marcar DBCC MEMORYSTATUS ou sys.dm_os_memory_clerks).
Concessões de Memória: Quando SQL Server concede a memória solicitada a uma consulta em execução, diz-se que ocorreu uma concessão de memória. Há 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 atendidas ou aguardando. Eles não dão conta do tamanho da concessão de memória. Uma consulta por si só poderia ter consumido, por exemplo, 4 GB de memória para executar uma classificação, mas isso não se reflete em nenhum desses contadores.Memória do workspace é outro termo que descreve a mesma memória. Geralmente, 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, expressas em KB. OMaximum Workspace Memory (KB), outro contador, contabiliza a quantidade máxima de memória do workspace disponível para quaisquer solicitações que possam precisar fazer essas operações de hash, classificação, cópia em massa e criação de índice. O termo Memória do Workspace é encontrado com pouca frequência fora desses dois contadores.
Impacto de desempenho da utilização de memória QE grande
Na maioria dos casos, quando um thread solicita memória dentro de SQL Server para fazer algo e a memória não está disponível, a solicitação falha com um erro de memória fora. 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 concessões de memória e o outro é a memória de compilação de consulta. SQL Server usa um objeto de sincronização de thread chamado semáforo para acompanhar a quantidade de memória concedida para execução de consulta. Se SQL Server ficar sem o workspace de QE predefinido, em vez de falhar na consulta com um erro de memória, isso fará com que a consulta aguarde. Dado que a memória do workspace tem permissão para obter uma porcentagem significativa da memória geral SQL Server, esperar na memória neste espaço tem sérias implicações de desempenho. Um grande número de consultas simultâneas solicitaram memória de execução e, juntas, esgotaram o pool de memória QE ou algumas consultas simultâneas solicitaram cada uma concessões muito grandes. De qualquer forma, os problemas de desempenho resultantes podem ter os seguintes sintomas:
- Páginas de dados e índice de um cache de buffer provavelmente foram liberadas para abrir espaço para as solicitações de concessão de memória grandes. Isso significa que as leituras de página provenientes de solicitações de consulta precisam ser atendidas do disco (uma operação significativamente mais lenta).
- As solicitações de outras alocações de memória podem falhar com erros de memória fora porque o recurso está vinculado com 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 que o recurso fique disponível 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, nas DMVs (modos de gerenciamento dinâmico) ou DBCC MEMORYSTATUS, deverá agir para resolve esse problema, especialmente se o problema ocorrer com frequência. Para obter mais informações, confira 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 a espera por reservas de QE. Escolha os que melhor servem para ver a imagem maior 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 detalhar no nível de consulta individual para ver quais consultas precisam ser afinadas ou reescritas.
No nível do servidor, use os seguintes métodos:
- Sys.dm_exec_query_resource_semaphores de DMV de semáforo de recursos Para obter mais informações, consulte sys.dm_exec_query_resource_semaphores.
- contadores Monitor de Desempenho Para obter mais informações, consulte SQL Server objeto gerenciador de memória.
- DBCC MEMORYSTATUS Para obter mais informações, consulte DBCC MEMORYSTATUS.
- DMV de funcionários de memória 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:
- Identificar consultas específicas com sys.dm_exec_query_memory_grants: atualmente executando consultas. Para obter mais informações, consulte sys.dm_exec_query_memory_grants.
- Identificar consultas específicas com sys.dm_exec_requests: atualmente executando consultas. Para obter mais informações, consulte sys.dm_exec_requests.
- Identificar 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 Repositório de Consultas (QDS) 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.
Estatísticas de uso de memória agregada
Sys.dm_exec_query_resource_semaphores de DMV de semáforo de recursos
Esse 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 três 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 Monitor de Desempenho
Informações semelhantes estão disponíveis por meio de contadores Monitor de Desempenho, em que você pode observar as solicitações concedidas no momento (Memory Grants Outstanding), as solicitações de concessão de 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 de workspace concedida é de 828.288 KB. O Memory Grants Pending contador Perfmon com um valor não zero indica que a memória foi esgotada.
Para obter mais informações, consulte SQL Server objeto gerenciador de memória.
- SQLServer, Gerenciador de Memória: Memória máxima do workspace (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: KB (memória de workspace concedida)
DBCC MEMORYSTATUS
Outro local em que 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 Query Memory Objects (default) saída para consultas de usuário. Se você tiver habilitado Resource Governor com um pool de recursos chamado PoolAdmin, por exemplo, você poderá examinar tanto quanto Query Memory Objects (default)Query Memory Objects (PoolAdmin).
Aqui está uma saída de exemplo de um sistema em que 18 solicitações receberam memória de execução de consulta e duas solicitações estão aguardando memória. O contador disponível é zero, o que indica que não há mais memória de workspace disponível. Esse fato explica as duas solicitações 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 Consultar objetos de memória.
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 funcionário de memória que mantém o controle da memória de execução de 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
DMV de funcionários de memória sys.dm_os_memory_clerks
Se você precisar de mais de um conjunto de resultados tabulares, diferente do baseado em DBCC MEMORYSTATUSseção , poderá usar sys.dm_os_memory_clerks para obter informações semelhantes. Procure o funcionário de MEMORYCLERK_SQLQERESERVATIONS memória. No entanto, os Objetos de Memória de Consulta não estão disponíveis neste DMV.
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
Aqui está uma saída de exemplo:
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 a desaceleração da 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 fornecendo informações de concessão de memória (ela pode ser usada, por exemplo, para telemetria).
- sqlserver.query_memory_grant_resource_semaphores: ocorre em intervalos de cinco minutos para cada pool de recursos do governador de recursos.
- sqlserver.query_memory_grant_usage: ocorre no final do processamento de consultas para consultas com concessões de memória acima de 5 MB para informar os usuários sobre as 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 recursos de comentários de concessão de memória de processamento de consulta, confira Comentários sobre concessão de memória.
- sqlserver.memory_grant_feedback_loop_disabled: ocorre quando o loop de comentários de concessão de memória é 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 para 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 ouquery_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 build é particionada ou um resgate de hash quando a partição da entrada de build excede o nível máximo de recursão. Use esse evento junto com qualquer um dos
query_pre_execution_showplaneventos ouquery_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 de execução não se encaixa 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 umaSelectinstrução.) Use esse evento para identificar consultas que são executadas lentamente devido à operação de classificação, especialmente quando owarning_type= 2, indicando que várias passagens sobre os dados foram necessárias para classificar.
Planejar a geração de eventos que contenham informações de concessão de memória
O seguinte plano de consulta 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 do repositório de colunas
Uma das áreas cobertas por XEvents é a memória de execução usada durante o edifício 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 grupo de linhas foi reduzido. Há várias colunas de interesse aqui.
- sqlserver.column_store_index_build_memory_trace: rastrear o uso de memória durante o build de índice.
- sqlserver.column_store_index_build_memory_usage_scale_down: Mecanismo de Armazenamento reduzido.
- sqlserver.column_store_index_memory_estimation: mostra o resultado da estimativa de memória durante o build 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. Este último grupo pode consistir em solicitações com necessidades modestas para concessões de memória e, se for o caso, você poderá concentrar sua atenção em outro lugar. Mas eles também podem ser os culpados se eles estão solicitando enormes tamanhos de memória. Concentre-se neles se você achar que esse é o caso. Pode ser comum descobrir que uma consulta específica é o infrator, mas muitas instâncias dela são geradas. As instâncias que recebem 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.
Identificar consultas específicas com sys.dm_exec_query_memory_grants
Para exibir solicitações individuais e o tamanho da memória que eles solicitaram e foram concedidas, você pode consultar a exibição de sys.dm_exec_query_memory_grants gerenciamento dinâmico. Este DMV mostra informações sobre a execução de consultas no momento, não informações históricas.
A instrução a seguir obtém dados do 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á uma saída de exemplo abreviada da consulta durante o consumo ativo de memória QE. A maioria das consultas tem sua memória concedida, conforme mostrado por granted_memory_kb e used_memory_kb sendo valores numéricos não NULL. As consultas que não receberam a 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. O deles wait_time_ms indica 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 dela 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
Identificar 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 RESOURCE_SEMAPHOREde memó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 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 de espera e longos tempos de espera indicam um número excessivo de consultas simultâneas usando memória de execução ou grandes tamanhos de solicitação de memória.
Observação
O tempo de espera para concessões de memória é finito. Após uma espera excessiva (por exemplo, mais de 20 minutos), SQL Server vezes a consulta e gera o erro 8645: "Ocorreu um tempo limite enquanto esperava que os recursos de memória executassem a consulta. Execute novamente a consulta." Você pode ver o valor de tempo limite definido no nível do servidor examinando timeout_sec em sys.dm_exec_query_memory_grants. O valor de tempo limite pode variar ligeiramente entre SQL Server versões.
Com o uso de 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 atualmente aguardando uma concessão de memória procurando o RESOURCE_SEMAPHORE tipo de espera. Aqui está uma consulta que mostra as solicitações concedidas e as solicitações de 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 outras duas dúzias 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)
Identificar consultas específicas com sys.dm_exec_query_stats
Se o problema de concessão de memória não estiver acontecendo neste momento, mas você quiser identificar as consultas ofensivas, você poderá examinar dados históricos de 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 do plano, as linhas correspondentes são eliminadas dessa exibição. Em outras palavras, o DMV mantém estatísticas na memória que não são preservadas após uma reinicialização de SQL Server ou após a pressão de memória causar uma versão do cache do plano. Dito isso, você pode encontrar as informações aqui valiosas, especialmente 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, você pode descobrir que o problema se foi. Nessa situação, sys.dm_exec_query_stats pode fornecer os insights que outros DVMs não podem. Aqui está uma consulta de exemplo 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 a 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 SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 (somente o valor do predicado de 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 olhando para as consultas agregadas por query_hash. Este exemplo ilustra como encontrar os tamanhos de concessão médios, máximos e mínimos para uma instrução de consulta em todas as 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 da consulta, mas deve ser lida sem considerar valores específicos na instrução. Por exemplo, se uma instrução contiver WHERE Id = 5, você poderá lê-la em sua forma mais genérica: WHERE Id = @any_value.
Aqui está uma saída de exemplo abreviada da consulta com apenas 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 Repositório de Consultas (QDS) com sys.query_store_runtime_stats
Se você tiver Repositório de Consultas habilitado, poderá aproveitar suas estatísticas históricas persistentes. Ao contrário dos dados de sys.dm_exec_query_stats, essas estatísticas sobrevivem a uma SQL Server reinicialização 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 Definir o modo de captura de Repositório de Consultas ideal e Manter os dados mais relevantes nas seções Repositório de Consultas em Melhores práticas para gerenciar o Repositório de Consultas.
Identifique se seus bancos de dados Repositório de Consultas habilitados usando esta consulta:
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1Execute a consulta de diagnóstico a seguir 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
sys.dm_exec_query_statsque ; você vê estatísticas agregadas para as instruções. No entanto, uma diferença é que, com o QDS, você está olhando apenas para consultas no escopo desse banco de dados, não toda a SQL Server. Portanto, talvez seja necessário saber 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á uma saída de exemplo abreviada:
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 por meio sys.dm_exec_requests e sys.dm_exec_query_memory_grants, além das estatísticas de nível de servidor fornecidas por 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 nesta 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á derrotada. A LOOP JOIN dica pode potencialmente fazer com que a consulta de diagnóstico seja mais lenta, mas, nesse caso, é mais importante obter os resultados de diagnóstico.
Aqui está uma saída de exemplo abreviada desta 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 | NULL | 9 | 9 | 7 | 1 | 1 |
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 2 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 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_SEMAPHOREwait_type. O tempo de espera deles foi superior a 1.300 segundos (21 minutos) e o deles granted_memory_mb é NULL.
Essa consulta de diagnóstico é uma amostra, portanto, sinta-se à vontade para modificá-la de qualquer maneira que atenda às suas necessidades. Uma versão dessa consulta também é usada em ferramentas de diagnóstico que a Microsoft SQL Server suporte usa.
Ferramentas de diagnóstico
Há ferramentas de diagnóstico que a Microsoft SQL Server suporte técnico 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 dos contadores de DMVs e Monitor de Desempenho descritos anteriormente que podem ajudá-lo a diagnosticar problemas de concessão de memória.
Se você executar o LOGScout do SQL 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 examinar manualmente, você pode usar o SQL Nexus para importar a saída proveniente do SQL LogScout ou PSSDIAG em um banco de dados 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 perfmon na forma de . Arquivos BLG, que podem ser usados para revisar os contadores de desempenho descritos na seção contadores 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, 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 operações de classificação ou hash enormes. Essas consultas não só consomem muitas SQL Server memória, mas também fazem com que outras consultas aguardem a disponibilidade da 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 grande concessão. Em seguida, você pode ajustar ou reescrever essas consultas para resolve ou reduzir o uso da 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 redesenhando essas consultas.
O que causa operações de classificação e hash em consultas
A primeira etapa é tomar conhecimento 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) leva à classificação de linhas 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 ordena as colunas agrupadas.
DISTINCT (T-SQL) se comporta de forma semelhante a
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 ordenado.O operador Merge Join , quando selecionado pelo otimizador de consulta, requer que ambas as entradas ingressadas sejam classificadas. SQL Server pode disparar uma classificação se um índice clusterizado não estiver disponível na coluna de junção em uma das tabelas.
Razões pelas quais uma consulta usaria um operador de plano de consulta HASH:
Essa 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 ingressar em tabelas, SQL Server tem uma opção entre três operadores físicos,
Nested Loop,Merge JoineHash Join. Se SQL Server acabar escolhendo um Hash Join, 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,Hash Join. 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.UNION (T-SQL): isso é semelhante a
DISTINCT. UmHash Aggregatepode ser usado para remover as duplicatas para esse operador.SUM/AVG/MAX/MIN (T-SQL): qualquer operação de agregação poderia potencialmente 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 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 muitos níveis, garante que o otimizador de consulta tenha as informações mais precisas ao selecionar planos de consulta. SQL Server determina qual tamanho solicitar sua concessão de memória com base em 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 a vazamento de resultados em disco, respectivamente. Verifique se as estatísticas de atualização automática estão habilitadas em seus bancos de dados e/ou mantenha as estáticas atualizadas com ESTATÍSTICAS DE ATUALIZAÇÃO 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 poderá solicitar 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 salvar a 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 recursos já estiverem em vigor, essa diretriz será 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 nenhum í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 precisarã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 de consulta básico começa com a verificação se suas consultas têm índices apropriados para ajudá-las a reduzir leituras e minimizar ou eliminar grandes tipos ou operações de hash sempre que possível. As junções de hash geralmente são selecionadas para processar entradas grandes, não variadas e nãoexidas. A criação de índices pode alterar essa estratégia de otimizador e acelerar a recuperação de dados. Para obter assistência na criação de índices, consulte Orientador de Otimização do Mecanismo de Banco de Dados e Ajustar índices nãoclusos com sugestões de índice ausentes.
- Use índices COLUMNSTORE quando apropriado para consultas de agregação que usam
GROUP BY. Consultas de análise que lidam com conjuntos de linhas muito grandes e normalmente executam agregações "group by" podem precisar de grandes partes de memória para fazer o trabalho. Se um índice não estiver disponível que forneça resultados ordenados, uma classificação será introduzida automaticamente no plano de consulta. Uma espécie de resultado muito grande pode levar a uma concessão de memória cara. - Remova o
ORDER BYse você não precisar. Nos casos em que os resultados são transmitidos para um aplicativo que classifica os resultados à sua maneira ou permite que o usuário modifique a ordem dos dados exibidos, você não precisa executar uma classificação no lado SQL Server. Basta transmitir os dados para o aplicativo na ordem em que o servidor os produz e permitir que o usuário final os classifique por conta própria. Aplicativos de relatório como Power BI ou 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 DE JUNÇÃO LOOP quando as junções existem 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 só é sugerida como último recurso porque forçar uma junção pode levar a uma consulta significativamente mais lenta. Teste a carga de trabalho de teste de estresse para garantir que essa seja uma opção. Em alguns casos, uma junção de loop aninhado pode nem ser uma opção. Nesse caso, SQL Server pode falhar com MSSQLSERVER_8622 de erro: "O processador de consulta 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 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 esta 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 espera que muitas instâncias de sua consulta sejam executadas simultaneamente. Certifique-se de testar sua carga de trabalho para corresponder ao ambiente de produção e determinar quais valores usar.
Para obter mais informações, consulte MAX_GRANT_PERCENT e MIN_GRANT_PERCENT.
Resource Governor
Memória QE é a memória que Resource Governor realmente limita quando as configurações 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 mencionar que o default grupo de carga de trabalho permite que uma consulta leve até 25% da memória que pode ser concedida em uma instância de SQL Server. Para obter mais informações, consulte Resource Governor Pools de Recursos e CREATE WORKLOAD GROUP.
Processamento de consulta adaptável e comentários de concessão de memória
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 mais memória for necessária. Esse recurso foi lançado em três ondas:
- Comentários sobre a concessão de memória do modo lote no SQL Server 2017
- Comentários sobre a concessão de memória do modo de linha no SQL Server 2019
- Memória concede comentários sobre persistência no disco usando a concessão de Repositório de Consultas e percentil em SQL Server 2022
Para obter mais informações, confira Comentários sobre 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 em versões SQL Server 2019 e posteriores, em que o processamento adaptável do modo de linha está disponível, você pode até não notar nenhum problema de memória proveniente da execução da consulta. No entanto, se você tiver esse recurso em vigor (por padrão) e ainda vir um grande consumo de memória QE, aplique as etapas discutidas anteriormente para reescrever consultas.
Aumentar SQL Server ou memória do sistema operacional
Depois de tomar as medidas para reduzir as concessões de memória desnecessárias para suas consultas, se você ainda tiver problemas de memória baixos relacionados, a carga de trabalho provavelmente exigirá mais memória. Portanto, considere aumentar a memória para SQL Server usando a max server memory configuração se houver memória física suficiente no sistema para fazê-lo. Siga as recomendações sobre como 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 nenhuma memória suficiente estiver disponível no sistema, considere adicionar RAM física ou, se for uma máquina virtual, aumente a RAM dedicada para sua VM.
Interno de concessão de memória
Para saber mais sobre alguns internos na memória de execução de consulta, confira a postagem no blog De concessão de memória do SQL Server .
Como criar um cenário de desempenho com uso de concessão de memória pesada
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 de diagnóstico e as técnicas descritas neste artigo.
Aviso
Não use isso em um sistema de produção. Essa simulação é fornecida para ajudá-lo a entender o conceito e ajudá-lo a aprender melhor.
Em um servidor de teste, instale Utilitários RML e SQL Server.
Use um aplicativo cliente como SQL Server Management Studio para reduzir a configuração de memória máxima do servidor do seu 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 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 de 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 Resource Governor.
- 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 no runtime. Esse recurso pode impedir problemas de concessão de memória em primeiro lugar.
- Aumente SQL Server ou memória do sistema operacional.
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de