Partilhar via

Usar 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 original do KB: 907877

Introdução

O DBCC MEMORYSTATUS comando fornece um instantâneo do status atual da memória Microsoft SQL Server e do sistema operacional. Ele fornece uma das saídas mais detalhadas de distribuição e uso de memória no SQL Server. Você pode usar a saída para solucionar problemas de consumo de memória no SQL Server ou para solucionar erros específicos de falta de memória. Muitos erros de falta de memória geram automaticamente essa saída no log de erros. Se você tiver um erro relacionado a uma condição de pouca memória, poderá executar o DBCC MEMORYSTATUS comando e fornecer a saída ao entrar em contato com o 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 do pool de buffers e informações de cache de procedimento. Ele também descreve a saída de objetos de memória global, objetos de memória de consultas, otimização e agentes de memória.

Observação

O Monitor de Desempenho (PerfMon) e o Gerenciador de Tarefas não consideram o uso total 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 AWE (Address Windowing Extensions).

Importante

O DBCC MEMORYSTATUS comando destina-se a ser uma ferramenta de diagnóstico para o Suporte da Microsoft. O formato da saída e o nível de detalhes fornecidos estão sujeitos a alterações entre service packs e versões de produtos. A funcionalidade que o comando fornece pode ser substituída por um mecanismo diferente em versões posteriores do DBCC MEMORYSTATUS 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, os aplicativos que usam esse comando podem ser interrompidos sem aviso.

A saída do comando foi alterada em relação às DBCC MEMORYSTATUS 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 DBCC MEMORYSTATUS

DBCC MEMORYSTATUS normalmente é usado para investigar problemas de pouca memória relatados pelo SQL Server. Pouca memória pode ocorrer se houver pressão de memória externa de fora do processo do SQL Server ou pressão interna originada dentro do processo. A pressão interna pode ser causada pelo mecanismo de banco de dados do SQL Server ou por outros componentes executados no processo (como servidores vinculados, XPs, SQLCLR, proteção contra intrusões 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 insuficiente ou de memória insuficiente no 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 de maneira 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 do SQL Server está paginado). Além disso, use essas seções para determinar quanta memória o mecanismo de banco de dados do SQL Server alocou em comparação com a memória geral do 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 do SQL Server está usando a maior parte da memória (pressão de memória interna), poderá usar as seções restantes para DBCC MEMORYSTATUS identificar quais componentes (Memory clerk, Cachestore, UserStore ou Objectstore) são os maiores contribuintes para esse uso de memória.
  5. Examine cada componente: MEMORYCLEARK, CACHESTORE, USERSTOREe OBJECTSTORE. Examine seu valor de Páginas Alocadas para determinar quanta memória esse componente está consumindo dentro do SQL Server. Para obter uma breve descrição da maioria dos componentes de memória do mecanismo de banco de dados, consulte a tabela de tipos de Memory Clerk.
    1. Em casos raros, a alocação é uma alocação virtual direta em vez de passar pelo gerenciador de memória do SQL Server. Nesses casos, examine o valor da VM Confirmada no componente específico em vez de Páginas Alocadas.
    2. Se o seu computador usa NUMA, alguns componentes de memória sã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 somado 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, examinar o detalhamento, conforme necessário. Para obter mais informações, consulte Uso de memória com nós NUMA.
  6. Algumas seções fornecem informações detalhadas e especializadas sobre alocadores de DBCC MEMORYSTATUS memória específicos. Você pode usar essas seções para entender detalhes adicionais e ver um detalhamento adicional das alocações em um administrador de memória. Exemplos dessas seções incluem Buffer Pool (cache de dados e índice), cache de procedimento/cache de plano, objetos de memória de consulta (concessões de memória), fila de otimização e gateways pequenos, médios e grandes (memória do otimizador). Se você já sabe que um determinado componente da memória no SQL Server é a fonte de pressão de memória, talvez 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 causa erros de memória, poderá examinar 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 que você diagnostique problemas de memória com mais eficiência.

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 total de memória livre no computador. No exemplo, a memória livre é de 5.060.247.552 bytes.
  • Memória Virtual Disponível: esse valor mostra que a quantidade total de memória virtual livre para o processo do SQL Server é de 140.710.048.014.336 bytes (128 TB). Para obter mais informações, consulte Limites de memória e espaço de endereço.
  • Arquivo de paginação disponível: esse valor mostra o espaço livre do arquivo de paginação. 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 do SQL Server tem na RAM (não é paginada) é de 430.026.752 bytes.
  • Porcentagem de memória confirmada no WS: esse valor mostra em qual porcentagem da memória virtual alocada do SQL Server reside na RAM (ou está funcionando O valor de 100 por cento mostra que toda a memória confirmada é armazenada na RAM e 0 por cento dela é paginada.
  • Falhas de página: esse valor mostra a quantidade geral de falhas de página rígidas e flexíveis para o SQL Server. No exemplo, o valor é 151.138.

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

  • O valor alto da memória física do sistema de 1 indica que o SQL Server considera que a memória física disponível no computador é alta. É por isso que o valor de Memória física do sistema baixa é 0, o que significa que não há memória baixa. Lógica semelhante é aplicada a Processar memória física baixa e Processar memória virtual baixa, em que 0 significa que é falso e 1 significa que é verdadeiro. Neste exemplo, ambos os valores são 0, o que significa que há muita memória física e virtual para o processo do 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 pelo 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 os 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 o SQL Server reservou. A reserva de memória virtual não usa memória física; significa simplesmente que os endereços virtuais são separados de dentro do grande VAS. Para obter mais informações, consulte VirtualAlloc() MEM_RESERVE.

  • VM confirmada: esse valor mostra a quantidade geral de VM (memória virtual) que o SQL Server confirmou (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 paginação. Os endereços de memória anteriormente reservados agora são apoiados por um armazenamento físico; ou seja, eles são alocados. Se as Páginas Bloqueadas na Memória estiverem habilitadas, o SQL Server usará um método alternativo para alocar memória, a API AWE e a maior parte da memória não será refletida nesse 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 pelo mecanismo de banco de dados do SQL Server.

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

  • Páginas Grandes Alocadas: esse valor representa a quantidade de memória alocada pelo 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 de página padrão (normalmente 4 KB), esse recurso usa um tamanho de página maior, como 2 MB ou 4 MB. Um valor zero indica que o recurso não está habilitado. Para obter mais informações, consulte Virtual Alloc() MEM_LARGE_PAGES.

  • Destino Confirmado: esse valor indica a quantidade de memória de destino que o SQL Server pretende confirmar, uma quantidade ideal de memória que o SQL Server poderia consumir, com base na carga de trabalho recente.

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

  • Fase de crescimento do NUMA: esse valor indica se o SQL Server está atualmente em uma fase de crescimento do NUMA. Para obter mais informações sobre esse aumento inicial de memória quando existem nós NUMA no computador, consulte Como funciona: SQL Server (blocos de memória local, externa e ausente do NUMA).

  • Último erro do sistema operacional: esse valor mostra o último erro do sistema operacional que ocorreu quando houve uma pressão de memória no sistema. O SQL Server registra esse erro do sistema operacional e o mostra 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 não uniforme à memória), 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 ao ID do nó de hardware.
  • Esses valores mostram a memória alocada por threads em execução nesse nó NUMA. Esses valores não são a memória local para o 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 um pouco menores do que os valores correspondentes relatados na tabela do 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 dedicada), consulte Conexão de diagnóstico para administradores de banco de dados.

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

  • VM reservada: mostra o espaço de endereço virtual (VAS) reservado por threads em execução neste nó.
  • VM confirmada: mostra o VAS confirmado por threads em execução nesse nó.

Memória agregada

A tabela a seguir contém informações de memória agregada para cada tipo de funcionário e nó NUMA. Para um sistema habilitado para NUMA, você pode ver uma saída semelhante à 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 total de páginas de memória alocadas por um componente específico (memory clerk, userstore, objectstore ou cache store).

Observação

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

Para um sistema SMP, você vê apenas uma tabela para cada tipo de funcionário, sem contar o nó = 64 usado pelo DAC. Esta tabela é semelhante 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 na memória. Essa API também é conhecida como memória compartilhada.
  • SM Committed: mostra o VAS confirmado por todos os funcionários desse tipo que estão usando a API de arquivos mapeados na memória.

Como método alternativo, você pode obter informações resumidas para cada tipo de funcionário para todos os nós de memória usando a sys.dm_os_memory_clerks DMV (exibição de gerenciamento dinâmico). 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 buffer pool

Esta é uma seção importante que fornece um detalhamento de dados de estados diferentes e páginas de índice dentro do pool de buffers, também conhecido como cache de dados. A tabela de saída a seguir lista detalhes sobre o buffer pool 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 os 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 (páginas de dados e índice).
  • Destino: mostra o tamanho de destino do buffer pool (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 de banco de dados e foram modificadas. Esses buffers contêm alterações que devem ser liberadas para o 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 no disco. Uma trava é usada para manter a consistência física dos dados na página enquanto ela está sendo lida ou modificada. 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 à E/S (isso não indica necessariamente um problema).
  • Expectativa de vida da página: esse contador mede a quantidade de tempo em segundos que a página mais antiga permaneceu no buffer pool.

Você pode obter informações detalhadas sobre o buffer pool para páginas de banco de dados usando a sys.dm_os_buffer_descriptors DMV. Mas use essa DMV com cuidado, pois ela pode ser executada por muito tempo e produzir uma saída enorme se o servidor baseado no SQL Server tiver muita RAM à sua disposição.

Cache de planos

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

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

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

  • TotalProcs: esse valor mostra o total de objetos armazenados em cache atualmente no cache de procedimento. Esse valor corresponde ao sys.dm_exec_cached_plans número de entradas no 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 SQL Server: Planejar o objeto Cache e a 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 procedimentos que pertencem aos procedimentos que estão ativos no momento. Essas páginas não podem ser descartadas.

Objetos de memória global

Esta seção contém informações sobre vários objetos de memória global 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 os valores na saída e suas descrições:

  • Resource: 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 Armazenamento local de thread.
  • 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 de replicação internos.
  • 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.
  • SortTables: mostra a memória usada pelas tabelas de classificação.

Objetos de memória de consulta

Esta seção descreve as 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 espaço de trabalho".

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 fila de consulta pequena. Esse comportamento impede que consultas menores sejam atrasadas por trás de consultas maiores que já estão na fila.

A lista a seguir discute os 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.
  • Aguardando: Mostra o número de consultas que estão aguardando para obter concessões de memória.
  • Disponível: mostra os buffers que estão disponíveis para consultas para uso como espaço de trabalho de hash e espaço de trabalho 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 em 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 Aguardando é o valor multiplicado Next Request 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 em espera for executada.
  • Custo: mostra o custo da próxima consulta em espera.
  • Tempo limite: mostra o tempo limite, em segundos, para a próxima consulta em espera.
  • Tempo de espera: mostra o tempo decorrido, em milissegundos, desde que a próxima consulta em espera foi colocada na fila.
  • Máximo atual: mostra o limite geral de memória para execução de consulta. Esse valor é o limite combinado para a fila de consultas grande e a fila de consultas pequenas.

Para obter mais informações sobre o que são concessões de memória, o que esses valores significam e como solucionar problemas de concessões de memória, consulte Solucionar problemas de desempenho lento ou de memória insuficiente causados por concessões de memória no 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, algebrizaçã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 progressivamente decrescente de consultas compiladas simultaneamente após cada gateway. O tamanho de cada gateway depende da plataforma e da carga. Os tamanhos de gateway são escolhidos para maximizar a escalabilidade e a taxa de transferência.

Se a consulta não puder passar um gateway, ela aguardará até que a memória esteja disponível ou retornará 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 deadlock 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 tabela a seguir fornece detalhes das esperas de memória que ocorrem devido à memória insuficiente para otimização de consulta. A memória interna é responsável pela memória do otimizador usada por consultas do sistema, enquanto a memória de otimização de relatórios padrã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 grande. Como mencionado anteriormente, se uma consulta precisar de mais memória do que o gateway pequeno pode alocar, ela irá para o gateway médio e essa consulta será contada como tendo uma unidade em ambos os gateways. Quanto maior a quantidade de memória de compilação necessária para uma consulta, 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 a partir da lista de unidades configuradas. Por exemplo, se 32 unidades estiverem disponíveis, mas três consultas estiverem usando memória de compilação no momento, 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, Acquires = 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 de Waiters será diferente de zero, o que mostra a contagem de consultas em 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 ela permanece. Se uma consulta não precisar de 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, irá para o médio e, se esse limite ainda for insuficiente, vai para o gateway grande. Para o gateway pequeno, o fator de limite é de 380.000 bytes (pode estar sujeito a alterações em versões futuras) para a plataforma x64.
  • Fator de limite: Determina o valor limite para cada gateway. Para o gateway pequeno, como o limite é predefinido, o fator também é definido com 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 do SQL Server exigem memória, os fatores de limite também farão com que os limites sejam ajustados dinamicamente.
  • Tempo limite: indica o valor em minutos que define quanto tempo uma consulta aguarda pela memória do otimizador. Se esse valor de tempo limite for atingido, a sessão será interrompida em espera e gerará o erro 8628 - A time out occurred while waiting to optimize the query. Rerun the query.

Agentes de memória

Esta seção fornece informações sobre agentes de memória que controlam a memória armazenada em cache, a memória roubada e a memória reservada. Você pode usar as informações nessas tabelas apenas para diagnósticos internos. 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