Use o comando DBCC MEMORYSTATUS para monitorar o uso de memória no SQL Server

  • Artigo

Este artigo descreve como usar o DBCC MEMORYSTATUS comando para monitorar o uso da memória.

Versão original do produto: SQL Server
Número de KB original: 907877

Introdução

O DBCC MEMORYSTATUS comando fornece uma instantâneo da memória atual status Microsoft SQL Server e do sistema operacional. Ele fornece uma das saídas mais detalhadas de distribuição e uso de memória em SQL Server. Você pode usar a saída para solucionar problemas de consumo de memória no SQL Server ou para solucionar problemas específicos de erros fora da memória. Muitos erros fora de memória geram automaticamente essa saída no log de erros. Se você tiver um erro relacionado a uma condição de baixa memória, poderá executar o DBCC MEMORYSTATUS comando e fornecer a saída quando entrar em contato com Suporte da Microsoft.

A saída do DBCC MEMORYSTATUS comando inclui seções para gerenciamento de memória, uso de memória, informações de memória agregada, informações de pool de buffer e informações de cache de procedimento. Ele também descreve a saída de objetos de memória globais, objetos de memória de consulta, otimização e corretores de memória.

Observação

Monitor de Desempenho (PerfMon) e Gerenciador de Tarefas não responderão pelo uso completo da memória se a opção Páginas Bloqueadas na Memória estiver habilitada. Não há contadores de desempenho que mostrem o uso de memória da API de Extensões de Janela de Endereço (AWE).

Importante

O DBCC MEMORYSTATUS comando destina-se a ser uma ferramenta de diagnóstico para Suporte da Microsoft. O formato da saída e o nível de detalhes fornecidos estão sujeitos a alterações entre pacotes de serviço e versões de produto. A funcionalidade que o DBCC MEMORYSTATUS comando fornece pode ser substituída por um mecanismo diferente em versões posteriores do produto. Portanto, em versões posteriores do produto, esse comando pode não funcionar mais. Nenhum aviso adicional será fornecido antes que esse comando seja alterado ou removido. Portanto, aplicativos que usam esse comando podem ser interrompidos sem aviso.

A saída do DBCC MEMORYSTATUS comando foi alterada em relação às versões anteriores do SQL Server. Atualmente, ele contém várias tabelas que não estavam disponíveis nas versões anteriores do produto.

Como usar o DBCC MEMORYSTATUS

DBCC MEMORYSTATUSnormalmente é usado para investigar problemas de baixa memória relatados por SQL Server. A baixa memória pode ocorrer se houver pressão de memória externa de fora do processo de SQL Server ou pressão interna que se origina no processo. A pressão interna pode ser causada pelo mecanismo de banco de dados SQL Server ou por outros componentes executados dentro do processo (como servidores vinculados, XPs, SQLCLR, proteção contra intrusão ou software antivírus). Para obter mais informações sobre como solucionar problemas de pressão de memória, consulte Solucionar problemas de memória ou memória baixa em SQL Server.

Aqui estão as etapas gerais para usar o comando e interpretar seus resultados. Cenários específicos podem exigir que você aborde a saída um pouco diferente, mas a abordagem geral é descrita aqui.

  1. Execute o comando DBCC MEMORYSTATUS.
  2. Use as seções Contagens de Processo/Sistema e Gerenciador de Memória para estabelecer se há pressão de memória externa (por exemplo, o computador está com pouca memória física ou virtual ou o conjunto de trabalho SQL Server é excluído). Além disso, use essas seções para determinar a quantidade de memória que o mecanismo de banco de dados SQL Server alocou em comparação com a memória geral no sistema.
  3. Se você estabelecer que há pressão de memória externa, tente reduzir o uso de memória por outros aplicativos e pelo sistema operacional ou adicione mais RAM.
  4. Se você estabelecer que o mecanismo SQL Server está usando a maior parte da memória (pressão de memória interna), você poderá usar as seções restantes de DBCC MEMORYSTATUS para identificar quais componentes (assistente de memória, Cachestore, UserStore ou Objectstore) são os maiores contribuidor para esse uso de memória.
  5. Examine cada componente: MEMORYCLEARK, CACHESTORE, USERSTOREe OBJECTSTORE. Examine o valor alocado de páginas para determinar a quantidade de memória que esse componente está consumindo dentro de SQL Server. Para obter uma breve descrição da maioria dos componentes de memória do mecanismo de banco de dados, consulte a tabela Tipos do Gerenciador de Memória .
    1. Em casos raros, a alocação é uma alocação virtual direta em vez de passar pelo gerenciador de memória SQL Server. Nesses casos, examine o valor confirmado da VM no componente específico em vez de Páginas Alocadas.
    2. Se o computador usar NUMA, alguns componentes de memória serão divididos por nó. Por exemplo, você pode observar OBJECTSTORE_LOCK_MANAGER (node 0), OBJECTSTORE_LOCK_MANAGER (node 1), OBJECTSTORE_LOCK_MANAGER (node 2)e assim por diante, e finalmente observar um valor resumido de cada nó em OBJECTSTORE_LOCK_MANAGER (Total). O melhor lugar para começar é na seção que relata o valor total e, em seguida, examine a divisão, conforme necessário. Para obter mais informações, consulte Uso de memória com nós NUMA.
  6. Algumas seções de DBCC MEMORYSTATUS fornecem informações detalhadas e especializadas sobre alocadores de memória específicos. Você pode usar essas seções para entender detalhes adicionais e ver um detalhamento adicional das alocações em um funcionário de memória. Exemplos dessas seções incluem Pool de Buffer (cache de dados e índice), cache/plano de procedimento, Objetos de Memória de Consulta (concessões de memória), Fila de Otimização e gateways pequenos e médios e grandes (memória otimizadora). Se você já sabe que um componente específico da memória em SQL Server é a fonte de pressão de memória, talvez você prefira ir diretamente para essa seção específica. Por exemplo, se você estabeleceu de alguma outra forma que há um alto uso de concessões de memória que causam erros de memória, você pode revisar a seção Objetos de memória de consulta.

O restante deste artigo descreve alguns dos contadores úteis na DBCC MEMORYSTATUS saída que podem permitir diagnosticar problemas de memória de forma mais eficaz.

Contagens de processo/sistema

Esta seção fornece uma saída de exemplo em um formato tabular e descreve seus valores.

Process/System Counts                Value
------------------------------------ ------------
Available Physical Memory            5060247552
Available Virtual Memory             140710048014336
Available Paging File                7066804224
Working Set                          430026752
Percent of Committed Memory in WS    100
Page Faults                          151138
System physical memory high          1
System physical memory low           0
Process physical memory low          0
Process virtual memory low           0

A lista a seguir discute valores e suas descrições:

  • Memória Física Disponível: esse valor mostra a quantidade geral de memória gratuita no computador. No exemplo, a memória gratuita é de 5.060.247.552 bytes.
  • Memória Virtual Disponível: esse valor mostra que a quantidade total de memória virtual gratuita para SQL Server processo é de 140.710.048.014.336 bytes (128 TB). Para obter mais informações, consulte Limites de espaço de memória e endereço.
  • Arquivo de paginação disponível: esse valor mostra o espaço de arquivo de paginação gratuito. No exemplo, o valor é 7.066.804.224 bytes.
  • Conjunto de trabalho: esse valor mostra que a quantidade geral de memória virtual que o processo de SQL Server tem na RAM (não é paged out) é de 430.026.752 bytes.
  • Percentual da memória confirmada no WS: esse valor mostra qual percentual de SQL Server memória virtual alocada reside na RAM (ou é Conjunto de Trabalho). O valor de 100% mostra que toda a memória comprometida é armazenada em RAM e 0% dela é excluída.
  • Falhas de página: esse valor mostra a quantidade geral de falhas de página duras e suaves para o SQL Server. No exemplo, o valor é 151.138.

Os quatro valores restantes são binários ou boolianos.

  • O alto valor de memória física do sistema de 1 indica que SQL Server considera que a memória física disponível no computador é alta. É por isso que o valor da memória física do sistema baixo é 0, o que significa que não há memória baixa. Lógica semelhante é aplicada ao Processo de memória física baixa e Processa memória virtual baixa, onde 0 significa que é falso e 1 significa que é verdade. Neste exemplo, ambos os valores são 0, o que significa que há muita memória física e virtual para o processo de SQL Server.

Gerenciador de Memória

Esta seção fornece uma saída de exemplo do Gerenciador de Memória que mostra o consumo geral de memória por SQL Server.

Memory Manager             KB
-------------------------- --------------------
VM Reserved                36228032
VM Committed               326188
Locked Pages Allocated     0
Large Pages Allocated      0
Emergency Memory           1024
Emergency Memory In Use    16
Target Committed           14210416
Current Committed          326192
Pages Allocated            161904
Pages Reserved             0
Pages Free                 5056
Pages In Use               286928
Page Alloc Potential       15650992
NUMA Growth Phase          0
Last OOM Factor            0
Last OS Error              0

A lista a seguir discute valores na saída e suas descrições:

  • VM Reservada: esse valor mostra a quantidade geral de VAS (espaço de endereço virtual) ou VM (memória virtual) que SQL Server reservada. A reserva de memória virtual não usa memória física; isso significa simplesmente que os endereços virtuais são separados de dentro do VAS grande. Para obter mais informações, consulte VirtualAlloc(), MEM_RESERVE.

  • VM Confirmada: esse valor mostra a quantidade geral de VM (memória virtual) que SQL Server cometeu (em KB). Isso significa que a memória usada pelo processo é apoiada pela memória física ou com menos frequência pelo arquivo de página. Os endereços de memória reservados anteriormente agora são apoiados por um armazenamento físico; ou seja, eles são alocados. Se Páginas Bloqueadas na Memória estiver habilitada, SQL Server usará um método alternativo para alocar memória, API do AWE e a maioria da memória não será refletida neste contador. Consulte [Páginas Bloqueadas Alocadas](#Locked Páginas Alocadas) para essas alocações. Para obter mais informações, consulte VirtualAlloc(), MEM_COMMIT.

  • Páginas alocadas: esse valor mostra o número total de páginas de memória alocadas por SQL Server mecanismo de banco de dados.

  • Páginas bloqueadas Alocadas: esse valor representa a quantidade de memória, em quilobytes (KB), que SQL Server alocado e bloqueado na RAM física usando a API da AWE. Ele indica a quantidade de memória que SQL Server está usando ativamente e pediu para ser mantida na memória para otimizar o desempenho. Ao bloquear páginas na memória, SQL Server garante que páginas de banco de dados críticas estejam prontamente disponíveis e não trocadas em disco. Para obter mais informações, consulte Memória do AWE (Endereçamento de Extensões do Windows). Um valor zero indica que o recurso "páginas bloqueadas na memória" está desabilitado no momento e SQL Server usa memória virtual. Nesse caso, o valor da VM Committed representaria a memória alocada para SQL Server.

  • Páginas grandes alocadas: esse valor representa a quantidade de memória alocada por SQL Server usando Páginas Grandes. Páginas Grandes é um recurso de gerenciamento de memória fornecido pelo sistema operacional. Em vez de usar o tamanho padrão da página (normalmente 4 KB), esse recurso usa um tamanho de página maior, como 2 MB ou 4 MB. Um valor de zero indica que o recurso não está habilitado. Para obter mais informações, consulte Virtual Alloc(), MEM_LARGE_PAGES.

  • Target Committed: esse valor indica a quantidade de memória de destino que SQL Server pretende ter confirmado, uma quantidade ideal de memória SQL Server poderia consumir, com base na carga de trabalho recente.

  • Current Committed: esse valor indica a quantidade da memória do sistema operacional (em KB) que o gerenciador de memória SQL Server cometeu no momento (alocado no repositório físico). Esse valor inclui "páginas bloqueadas na memória" (API do AWE) ou memória virtual. Portanto, esse valor é próximo ou o mesmo que páginas comprometidas ou bloqueadas da VM alocadas. Observe que quando SQL Server usa a API AWE, alguma memória ainda é alocada pelo Gerenciador de Memória Virtual do sistema operacional e será refletida como VM Committed.

  • Fase de Crescimento numa: esse valor indica se SQL Server está atualmente em uma fase de crescimento numa. Para obter mais informações sobre esse aumento inicial de memória quando houver nós NUMA no computador, confira Como funciona: SQL Server (Blocos de Memória NUMA Local, Estrangeiro e Afastado).

  • Último erro do sistema operacional: esse valor mostra o último erro do sistema operacional que ocorreu quando havia uma pressão de memória no sistema. SQL Server registra esse erro do sistema operacional e mostra-o na saída. Para obter uma lista completa de erros do sistema operacional, consulte Códigos de Erro do Sistema.

Uso de memória com nós NUMA

A seção Gerenciador de Memória é seguida por um resumo do uso de memória para cada nó de memória. Em um sistema habilitado para NUMA (acesso à memória) não uniforme, há uma entrada de nó de memória correspondente para cada nó NUMA de hardware. Em um sistema SMP, há uma única entrada de nó de memória. O mesmo padrão é aplicado a outras seções de memória.

Memory node Id = 0      KB
----------------------- -----------
VM Reserved             21289792
VM Committed            272808
Locked Pages Allocated  0
Pages Allocated         168904
Pages Free              3040
Target Committed        6664712
Current Committed       272808
Foreign Committed       0
Away Committed          0
Taken Away Committed    0

Observação

  • O Memory node Id valor pode não corresponder à ID do nó de hardware.
  • Esses valores mostram a memória alocada por threads que estão em execução neste nó NUMA. Esses valores não são a memória local do nó NUMA.
  • As somas dos valores reservados da VM e dos valores confirmados da VM em todos os nós de memória serão ligeiramente menores do que os valores correspondentes relatados na tabela Gerenciador de Memória.
  • O nó NUMA 64 (nó 64) é reservado para DAC e raramente é de interesse na investigação de memória porque essa conexão usa recursos de memória limitados. Para obter mais informações sobre a DAC (conexão de administrador dedicado), consulte Conexão de diagnóstico para administradores de banco de dados.

A lista a seguir discute valores na tabela de saída e suas descrições:

  • VM Reservada: mostra o VAS (espaço de endereço virtual) reservado por threads que estão em execução neste nó.
  • VM Committed: mostra o VAS que é confirmado por threads que estão em execução neste nó.

Memória agregada

A tabela a seguir contém informações de memória agregadas para cada tipo de funcionário e nó NUMA. Para um sistema habilitado para NUMA, você pode ver uma saída que se assemelha ao seguinte:

MEMORYCLERK_SQLGENERAL (node 0) KB
------------------------------  --------------------
VM Reserved                     0
VM Committed                    0
Locked Pages Allocated          0
SM Reserved                     0
SM Commited                     0
Pages Allocated                 5416

MEMORYCLERK_SQLGENERAL (node 1) KB
------------------------------- --------------------
VM Reserved                     0
VM Committed                    0
Locked Pages Allocated          0
SM Reserved                     0
SM Commited                     0
Pages Allocated                 136

MEMORYCLERK_SQLGENERAL (Total)  KB
------------------------------- --------------------
VM Reserved                     0
VM Committed                    0
Locked Pages Allocated          0
SM Reserved                     0
SM Commited                     0
Pages Allocated                 5552

O valor de Pages Allocated mostra o número geral de páginas de memória alocadas por um componente específico (repositório de memória, armazenamento de usuários, repositório de objetos ou repositório de cache).

Observação

Essas IDs de nó correspondem à configuração do nó NUMA do computador que está executando SQL Server. As IDs de nó incluem possíveis nós NUMA de software definidos em cima de nós NUMA de hardware ou em cima de um sistema SMP. Para localizar o mapeamento entre IDs de nó e CPUs para cada nó, consulte ID do evento de informações 17152. Esse evento é registrado no log do aplicativo no Visualizador de Eventos quando você inicia SQL Server.

Para um sistema SMP, você verá apenas uma tabela para cada tipo de funcionário, sem contar nó = 64 usado pelo DAC. Esta tabela se assemelha ao exemplo a seguir.

MEMORYCLERK_SQLGENERAL (Total)     KB
--------------------------------- --------------------
VM Reserved                        0
VM Committed                       0
AWE Allocated                      0
SM Reserved                        0
SM Commited                        0
Pages Allocated                    2928

Outras informações nestas tabelas são sobre memória compartilhada:

  • SM Reservado: mostra o VAS reservado por todos os funcionários desse tipo que estão usando a API de arquivos mapeados pela memória. Essa API também é conhecida como memória compartilhada.
  • SM Committed: mostra o VAS que é confirmado por todos os funcionários desse tipo que estão usando a API de arquivos mapeados pela memória.

Como um método alternativo, você pode obter informações de resumo para cada tipo de funcionário para todos os nós de memória usando a exibição de gerenciamento dinâmico sys.dm_os_memory_clerks (DMV). Para fazer isso, execute a seguinte consulta:

SELECT
  TYPE,
  SUM(virtual_memory_reserved_kb) AS [VM Reserved],
  SUM(virtual_memory_committed_kb) AS [VM Committed],
  SUM(awe_allocated_kb) AS [AWE Allocated],
  SUM(shared_memory_reserved_kb) AS [SM Reserved],
  SUM(shared_memory_committed_kb) AS [SM Committed],
  -- SUM(multi_pages_kb) AS [MultiPage Allocator],          /*Applies to: SQL Server 2008   (10.0.x) through SQL Server 2008 R2 (10.50.x).*/
  -- SUM(single_pages_kb) AS [SinlgePage Allocator],        /*Applies to: SQL Server 2008   (10.0.x) through SQL Server 2008 R2 (10.50.x).*/
  SUM(pages_kb) AS [Page Allocated]                      /*Applies to: SQL Server 2012 (11.  x) and later.*/
FROM sys.dm_os_memory_clerks
GROUP BY TYPE

Detalhes do pool de buffers

Esta é uma seção importante que fornece uma divisão de diferentes estados de dados e páginas de índice no pool de buffers, também conhecido como cache de dados. A tabela de saída a seguir lista detalhes sobre o pool de buffers e outras informações.

Buffer Pool                                       Pages
------------------------------------------------- ---------
Database                                          5404
Simulated                                         0
Target                                            16384000
Dirty                                             298
In IO                                             0
Latched                                           0
IO error                                          125
In Internal Pool                                  0
Page Life Expectancy                              3965

A lista a seguir discute valores na saída e suas descrições:

  • Banco de dados: mostra o número de buffers (páginas) que têm conteúdo de banco de dados (dados e páginas de índice).
  • Destino: mostra o tamanho do destino do pool de buffers (contagem de buffers). Consulte Memória confirmada de destino nas seções anteriores deste artigo.
  • Sujo: mostra as páginas que têm conteúdo do banco de dados e foram modificadas. Esses buffers contêm alterações que devem ser liberadas no disco normalmente pelo processo de ponto de verificação.
  • Em E/S: mostra os buffers que estão aguardando uma operação de E/S pendente. Isso significa que o conteúdo dessas páginas está sendo gravado ou lido do armazenamento.
  • Travado: mostra os buffers travados. Um buffer é travado quando um thread está lendo ou modificando o conteúdo de uma página. Um buffer também é travado quando a página está sendo lida do disco ou gravada em disco. Uma trava é usada para manter a consistência física dos dados na página enquanto ele está sendo lido ou modificado. Por outro lado, um bloqueio é usado para manter a consistência lógica e transacional.
  • Erro de E/S: mostra a contagem de buffers que podem ter encontrado erros de sistema operacional relacionados a E/S (isso não indica necessariamente um problema).
  • Expectativa de vida da página: este contador mede a quantidade de tempo em segundos que a página mais antiga permaneceu no pool de buffers.

Você pode obter informações detalhadas sobre o pool de buffers para páginas de banco de dados usando o sys.dm_os_buffer_descriptors DMV. Mas use esse DMV com cuidado porque ele pode executar muito tempo e produzir uma saída enorme se o servidor baseado em SQL Server tiver permissão para ter muita RAM à sua disposição.

Planejar cache

Esta seção discute o cache de plano que anteriormente era chamado de cache de procedimento.

Procedure Cache         Value
----------------------- -----------
TotalProcs              4
TotalPages              25
InUsePages              0

A lista a seguir discute valores na saída e suas descrições:

  • TotalProcs: esse valor mostra o total de objetos armazenados em cache atualmente no cache do procedimento. Esse valor corresponde ao número de entradas no sys.dm_exec_cached_plans DMV.

    Observação

    Devido à natureza dinâmica dessas informações, a correspondência pode não ser exata. Você pode usar o PerfMon para monitorar o objeto SQL Server: Planejar Cache e o sys.dm_exec_cached_plans DMV para obter informações detalhadas sobre o tipo de objetos armazenados em cache, como gatilhos, procedimentos e objetos ad hoc.

  • TotalPages: mostra as páginas cumulativas usadas para armazenar todos os objetos armazenados em cache no cache de plano ou procedimento. Você pode multiplicar esse número por 8 KB, para obter o valor expresso em KBs.

  • InUsePages: mostra as páginas no cache de procedimento que pertencem a procedimentos que estão ativos no momento. Essas páginas não podem ser descartadas.

Objetos de memória globais

Esta seção contém informações sobre vários objetos de memória globais e a quantidade de memória que eles usam.

Global Memory Objects               Buffers
----------------------------------  ----------------
Resource                            576
Locks                               96
XDES                                61
DirtyPageTracking                   52
SETLS                               8
SubpDesc Allocators                 8
SE SchemaManager                    139
SE Column Metadata Cache            159
SE Column Metadata Cache Store      2
SE Column Store Metadata Cache      8
SQLCache                            224
Replication                         2
ServerGlobal                        1509
XP Global                           2
SortTables                          3

A lista a seguir discute valores na saída e suas descrições:

  • Recurso: mostra a memória que o objeto Resource usa. Ele é usado pelo mecanismo de armazenamento para várias estruturas em todo o servidor.
  • Bloqueios: mostra a memória usada pelo Gerenciador de Bloqueios.
  • XDES: mostra a memória usada pelo Gerenciador de Transações.
  • SETLS: mostra a memória usada para alocar a estrutura por thread específica do Mecanismo de Armazenamento que usa o TLS (armazenamento local de thread). Para obter mais informações, consulte Thread Local Storage.
  • Alocadores subpDesc: mostra a memória usada para gerenciar subprocessos para consultas paralelas, operações de backup, operações de restauração, operações de banco de dados, operações de arquivo, espelhamento e cursores assíncronos. Esses subprocessos também são conhecidos como "processos paralelos".
  • SE SchemaManager: mostra a memória que o Schema Manager usa para armazenar metadados específicos do Mecanismo de Armazenamento.
  • SQLCache: mostra a memória usada para salvar o texto de instruções ad hoc e preparadas.
  • Replicação: mostra a memória que o servidor usa para subsistemas internos de replicação.
  • ServerGlobal: mostra o objeto de memória do servidor global que é usado genericamente por vários subsistemas.
  • XP Global: mostra a memória usada pelos procedimentos armazenados estendidos.
  • Classificações: mostra a memória usada pelas tabelas de classificação.

Objetos de memória de consulta

Esta seção descreve informações de concessão de memória de consulta. Ele também inclui um instantâneo do uso da memória de consulta. A memória de consulta também é conhecida como "memória do workspace".

Query Memory Objects (default)           Value
---------------------------------------- -------
Grants                                    0
Waiting                                   0
Available                                 436307
Current Max                               436307
Future Max                                436307
Physical Max                              436307
Next Request                              0
Waiting For                               0
Cost                                      0
Timeout                                   0
Wait Time                                 0

Se o tamanho e o custo de uma consulta atenderem aos limites de memória de consulta "pequenos", a consulta será colocada em uma pequena fila de consulta. Esse comportamento impede que consultas menores sejam atrasadas atrás de consultas maiores que já estão na fila.

A lista a seguir discute valores na saída e suas descrições:

  • Concessões: mostra o número de consultas em execução que têm concessões de memória.
  • Espera: mostra o número de consultas que estão aguardando para obter concessões de memória.
  • Disponível: mostra os buffers disponíveis para consultas para uso como workspace de hash e workspace de classificação. O Available valor é atualizado periodicamente.
  • Próxima Solicitação: mostra o tamanho da solicitação de memória, em buffers, para a próxima consulta de espera.
  • Aguardando: mostra a quantidade de memória que deve estar disponível para executar a consulta à qual o valor da Próxima Solicitação se refere. O valor Waiting For é o Next Request valor multiplicado por um fator de headroom. Esse valor garante efetivamente que uma quantidade específica de memória estará disponível quando a próxima consulta de espera for executada.
  • Custo: mostra o custo da próxima consulta de espera.
  • Tempo limite: mostra o tempo limite, em segundos, para a próxima consulta de espera.
  • Tempo de Espera: mostra o tempo decorrido, em milissegundos, já que a próxima consulta de espera foi colocada na fila.
  • Max atual: mostra o limite geral de memória para execução de consulta. Esse valor é o limite combinado para a fila de consulta grande e a pequena fila de consultas.

Para obter mais informações sobre quais concessões de memória são, o que esses valores significam e como solucionar problemas de concessões de memória, consulte Solucionar problemas de desempenho lento ou de baixa memória causados por concessões de memória em SQL Server.

Memória de otimização

As consultas são enviadas ao servidor para compilação. O processo de compilação inclui análise, álgebração e otimização. As consultas são classificadas com base na memória que cada consulta consome durante o processo de compilação.

Observação

Essa quantidade não inclui a memória necessária para executar a consulta.

Quando uma consulta é iniciada, não há limite de quantas consultas podem ser compiladas. À medida que o consumo de memória aumenta e atinge um limite, a consulta deve passar por um gateway para continuar. Há um limite decrescente progressivamente de consultas compiladas simultaneamente após cada gateway. O tamanho de cada gateway depende da plataforma e da carga. Os tamanhos do gateway são escolhidos para maximizar a escalabilidade e a taxa de transferência.

Se a consulta não puder passar por um gateway, ela aguarda até que a memória esteja disponível ou retorne um erro de tempo limite (Erro 8628). Além disso, a consulta pode não adquirir um gateway se você cancelar a consulta ou se um impasse for detectado. Se a consulta passar por vários gateways, ela não liberará os gateways menores até que o processo de compilação seja concluído.

Esse comportamento permite que apenas algumas compilações com uso intensivo de memória ocorram ao mesmo tempo. Além disso, esse comportamento maximiza a taxa de transferência para consultas menores.

A próxima tabela fornece detalhes das esperas de memória que ocorrem devido à memória insuficiente para otimização de consulta. A memória interna conta para memória otimizadora usada por consultas do sistema, enquanto o padrão relata a memória de otimização para consultas de usuário ou aplicativo.

Optimization Queue (internal)      Value
---------------------------------- ----------------
Overall Memory                     4013162496
Target Memory                      3673882624
Last Notification                  1
Timeout                            6
Early Termination Factor           5

Small Gateway (internal)           Value
---------------------------------- ----------------
Configured Units                   32
Available Units                    32
Acquires                           0
Waiters                            0
Threshold Factor                   380000
Threshold                          380000

Medium Gateway (internal)          Value
---------------------------------- ----------------
Configured Units                   8
Available Units                    8
Acquires                           0
Waiters                            0
Threshold Factor                   12
Threshold                          -1

Big Gateway (internal)             Value
---------------------------------- ----------------
Configured Units                   1
Available Units                    1
Acquires                           0
Waiters                            0
Threshold Factor                   8
Threshold                          -1

Optimization Queue (default)       Value
---------------------------------- ----------------
Overall Memory                     4013162496
Target Memory                      3542319104
Last Notification                  1
Timeout                            6
Early Termination Factor           5

Small Gateway (default)            Value
---------------------------------- ----------------
Configured Units                   32
Available Units                    32
Acquires                           0
Waiters                            0
Threshold Factor                   380000
Threshold                          380000

Medium Gateway (default)           Value
---------------------------------- ----------------
Configured Units                   8
Available Units                    8
Acquires                           0
Waiters                            2
Threshold Factor                   12
Threshold                          -1

Big Gateway (default)              Value
---------------------------------- ----------------
Configured Units                   1
Available Units                    1
Acquires                           0
Waiters                            0
Threshold Factor                   8
Threshold                          -1

Aqui está uma descrição de alguns desses valores:

  • Unidades configuradas – indica o número de consultas simultâneas que podem usar a memória de compilação do gateway. No exemplo, 32 consultas simultâneas podem estar usando memória do gateway pequeno (padrão), oito consultas simultâneas do gateway médio e uma consulta do gateway Big. Conforme mencionado anteriormente, se uma consulta precisar de mais memória do que o Gateway Pequeno pode alocar, ela será acessada no gateway médio e essa consulta será contada para ter tomado uma unidade em ambos os gateways. Quanto maior a quantidade de memória de compilação que uma consulta precisa, menos unidades configuradas em um gateway.
  • Unidades disponíveis – indica o número de slots ou unidades disponíveis para consultas simultâneas a serem compiladas na lista de unidades configuradas. Por exemplo, se 32 unidades estiverem disponíveis, mas três consultas estiverem atualmente usando memória de compilação, será Available Units 32 menos 3 ou 29 unidades.
  • Adquire - Indica o número de unidades ou slots adquiridos por consultas a serem compiladas. Se três consultas estiverem usando memória de um gateway, adquire = 3.
  • Garçons – Indica quantas consultas estão aguardando memória de compilação em um gateway. Se todas as unidades em um gateway estiverem esgotadas, o valor garçons não será zero que mostra a contagem de consultas de espera.
  • Limite – indica um limite de memória de gateway que determina de onde uma consulta obtém sua memória ou em qual gateway ele permanece. Se uma consulta não precisar mais do que o valor limite, ela permanecerá no gateway pequeno (uma consulta sempre começa com o gateway pequeno). Se precisar de mais memória para compilação, ele irá para o médio e, se esse limite ainda for insuficiente, ele irá para o gateway grande. Para o gateway pequeno, o fator limite é de 380.000 bytes (pode estar sujeito a alterações em versões futuras) para a plataforma x64.
  • Fator Limite: determina o valor limite de cada gateway. Para o gateway pequeno, como o limite é predefinido, o fator também é definido como o mesmo valor. Os fatores de limite para o gateway médio e grande são frações da memória total do otimizador (Memória Geral na fila de otimização) e são definidos como 12 e 8, respectivamente. Portanto, se a memória geral for ajustada porque outros consumidores de memória SQL Server exigem memória, os fatores de limite também fariam com que os limites fossem ajustados dinamicamente.
  • Tempo limite: indica o valor em minutos que define quanto tempo uma consulta espera pela memória do otimizador. Se esse valor de tempo limite for atingido, a sessão interromperá a espera e aumentará o erro 8628 - A time out occurred while waiting to optimize the query. Rerun the query.

Corretores de memória

Esta seção fornece informações sobre agentes de memória que controlam memória armazenada em cache, memória roubada e memória reservada. Você pode usar as informações nessas tabelas apenas para diagnóstico internas. Portanto, essas informações não são detalhadas.

MEMORYBROKER_FOR_CACHE (internal)       Value
--------------------------------------- -------------
Allocations                             20040
Rate                                    0
Target Allocations                      3477904
Future Allocations                      0
Overall                                 3919104
Last Notification                       1

MEMORYBROKER_FOR_STEAL (internal)       Value
--------------------------------------- -------------
Allocations                             129872
Rate                                    40
Target Allocations                      3587776
Future Allocations                      0
Overall                                 3919104
Last Notification                       1

MEMORYBROKER_FOR_RESERVE (internal)     Value
--------------------------------------- -------------
Allocations                             0
Rate                                    0
Target Allocations                      3457864
Future Allocations                      0
Overall                                 3919104
Last Notification                       1

MEMORYBROKER_FOR_CACHE (default)        Value
--------------------------------------- -------------
Allocations                             44592
Rate                                    8552
Target Allocations                      3511008
Future Allocations                      0
Overall                                 3919104
Last Notification                       1

MEMORYBROKER_FOR_STEAL (default)        Value
--------------------------------------- -------------
Allocations                             1432
Rate                                    -520
Target Allocations                      3459296
Future Allocations                      0
Overall                                 3919104
Last Notification                       1

MEMORYBROKER_FOR_RESERVE (default)      Value
--------------------------------------- -------------
Allocations                             0
Rate                                    0
Target Allocations                      3919104
Future Allocations                      872608
Overall                                 3919104
Last Notification                       1