Solucionando problemas do Administrador de Recursos

Se não houver nenhuma UDF de classificação disponível, todas as sessões irão automaticamente para o grupo de cargas de trabalho padrão. Depois de criar uma UDF de classificação, é necessário verificar se ela está registrada no Administrador de Recursos e se a configuração está atualizada na memória.

A criação, registro e habilitação de uma UDF de classificação é um processo de três etapas:

• Primeiro, é necessário criar a função.

  CREATE FUNCTION function_name() RETURNS <something> 
  WITH SCHEMABINDING
  

• Em segundo lugar, é necessário registrar a função no Administrador de Recursos.

  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=schema_name.function_name)
  

• E, em terceiro lugar, é necessário atualizar a configuração do Administrador de Recursos na memória.

  ALTER RESOURCE GOVERNOR RECONFIGURE
  

A primeira coisa a fazer ao solucionar problemas de classificação é verificar se a função criada está registrada no Administrador de Recursos e se a configuração foi atualizada. Use a consulta a seguir para obter o nome do esquema (schema_name) e o nome (function_name) da UDF de classificação que o Administrador de Recursos está usando no momento.

USE master
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

É possível usar a abordagem anterior para solucionar problemas em um cenário em que você alterou a UDF de classificação, mas o Administrador de Recursos ainda está usando a lógica da função anterior para classificar sessões. Esse comportamento indica que as alterações feitas não foram aplicadas à configuração na memória.

É possível criar uma função de classificação bastante complexa que não gere os resultados esperados ou que use muitos recursos. Se a solução de problemas básica foi executada, poderá ser necessário verificar se a lógica da função está correta. O pior caso é um cenário em que uma falha na codificação resulta em um loop infinito ou em uma consulta sem-controle.

Você pode usar uma conexão de administrador dedicada (DAC) para solucionar problemas de uma função de classificação escrita inadequadamente por que uma DAC não está sujeita à classificação e pode ser usada enquanto o Administrador de Recursos está executando e classificando sessões de entrada. Para obter mais informações, consulte Usando uma conexão de administrador dedicada [SQL Server].

Observação
Se uma DAC não estiver disponível para solução de problemas, você poderá reiniciar o sistema em modo de usuário único. Embora o modo de usuário único não esteja sujeito à classificação, você não pode diagnosticar a classificação do Administrador de Recursos enquanto ele está em execução.

As informações sobre a função de classificação podem ser obtidas consultando:

• sys.dm_exec_query_stats. (contém informações sobre a instrução mas não a função real)

• sys.dm_exec_sql_text (use em conjunto com o sql_handle obtido de sys.dm_exec_query_stats)

• Classe de evento PreConnect:Starting (fornece a ID e o nome da função de classificação)

Neste cenário um usuário relata uma degradação de desempenho e você suspeita que a contagem de solicitação está limitada.

A primeira coisa a ser feita é verificar se a limitação da contagem de solicitação está explicitamente configurada para o grupo em que o usuário está. Para fazer isso, verifique a associação do grupo do usuário para ver se a configuração de GROUP_MAX_REQUESTS está habilitada. Se GROUP_MAX_REQUESTS não estiver habilitado, não haverá nenhuma limitação na solicitação contagem de solicitações explícitas. Siga estas etapas para investigar mais.

• Consulte sys.dm_os_waiting_tasks para verificar se há alguma solicitação em espera no tipo de espera RESMGR_THROTTLED. A presença desse tipo de espera indica limitação da contagem de solicitação.

• Inicie o Monitor de Desempenho e colete dados usando os contadores Solicitações na fila e Solicitações ativas. Uma contagem de Solicitação na fila diferente de zero indica limitação de solicitação.

• Verifique se o valor de Solicitações ativas corresponde à configuração de GROUP_MAX_REQUESTS. Se o valor de Solicitações ativas for superior à configuração de GROUP_MAX_REQUESTS, talvez o grupo tenha solicitações que não possam ser limitadas (por exemplo, transações abertas).

• Se Solicitações na fila for igual a zero, verifique a opção Solicitações ativas de todos os grupos de cargas de trabalho que compartilham o mesmo pool de recursos porque o pool pode estar sobrecarregado com muitas solicitações.

Se houver uma política direcionada por geração de eventos do Administrador de Recursos, você poderá usar o evento gerado quando o limite máximo de CPU for atingido.

Nesse cenário você deve determinar se o limite máximo de CPU (REQUEST_MAX_CPU_TIME_SEC) configurado para detectar uma consulta que usa muita CPU está muito baixo.

As seguintes ações ajudam a validar a configuração do limite de CPU.

• Inicie uma sessão de Rastreamento SQL e colete o evento CPU Threshold Exceeded. Quando uma solicitação de usuário atinge o limite máximo de CPU o servidor gera automaticamente um evento de Rastreamento SQL. Se sua configuração for muito baixa, um alto número desses eventos será gerado.

Observação
Esse evento também é exposto como uma notificação de eventos de servidor para que você possa escrever um script que reaja ao evento.

• Inicie o Monitor de Desempenho e colete dados usando o contador Tempo máximo de cpu da solicitação (ms). É possível usar o valor desse contador como um guia para configurar o limite apropriado para o grupo de cargas de trabalho.

Neste cenário, há suspeita de que a largura de banda da CPU esteja limitada porque o contador de desempenho % de uso de CPU está próximo ou no nível da configuração MAX_CPU_PERCENT do Administrador de Recursos. A consulta a seguir retorna o valor da % de uso de CPU de todos os grupos de cargas de trabalho e pools de recursos de uma instância do SQL Server.

select * from sys.dm_os_performance_counters where counter_name = 'cpu usage %'

Para obter mais informações, consulte sys.dm_os_performance_counters (Transact-SQL).

É possível determinar se a largura de banda da CPU está limitada executando as seguintes verificações no sistema.

• Verifique a utilização total de CPU do servidor. Se uma carga diferente do SQL Server estiver ativa no momento, poderá afetar a consulta cujos problemas estão sendo solucionados.

• Verifique a distribuição de uso de CPU entre os pools de recursos. Um pool de recursos pode estar limitado porque outro pool tem um valor mínimo alto configurado para uso de CPU. Compare os contadores de utilização de CPU esperada (utilização de CPU calculada) em relação à utilização real.

• Verifique os grupos de cargas de trabalho atribuídos ao pool de recursos em questão. Carga de outros grupos de cargas de trabalho podem afetar os usuários que estão compartilhando o mesmo pool.

• Verifique a distribuição do uso de CPU entre agendadores. A consulta que está sendo investigada pode estar colocada em um agendador que contém consultas demoradas. Nesse caso, a consulta pode parecer estar limitada, mas o problema real é uma distribuição irregular de carga entre os agendadores.

• Verifique se há casos possíveis de carga de trabalho estar sendo bloqueada por outras sessões em vez de estarem limitadas pelas configurações do Administrador de Recursos.

• Verifique o número de sessões que estão executando consultas no sistema no momento. À medida que o número de solicitações de execução concorrentes cresce, o SQL Server tenta garantir que todas recebam pelo menos algum tempo de CPU para evitar privação de CPU.

Neste cenário você suspeita que o tamanho de concessão de memória esteja fazendo com que uma consulta demore.

O Administrador de Recursos impõe o limite máximo de memória de consulta reduzindo a concessão de memória para que consultas grandes se encaixem dentro do limite. Se uma consulta obtiver menos de 100% de concessão de memória, poderá ser necessário derramar dados temporários e gravá-los em disco, o que pode ter um efeito notável no desempenho.

É necessário determinar a porcentagem de consultas grandes para definir o limite máximo de tamanho de consulta. As seguintes ações ajudam a determinar as melhores configurações:

• Consulte sys.dm_exec_query_memory_grants para verificar o estado atual das concessões de memória. A coluna ideal_memory_kb mostra a quantidade ideal com base na estimativa de cardinalidade. A coluna requested_memory_kb mostra a quantidade pedida que poderia haver sido reduzida depois de atingir o limite máximo de consulta. Se requested_memory_kb for significativamente inferior a ideal_memory_kb, a consulta poderá acabar sendo derramada (supondo-se que a estimativa da cardinalidade esteja correta).

• Inicie o Monitor de Desempenho e colete dados usando o contador Concessões de memória reduzidas/s. O valor desse contador representa a taxa da contagem de concessões de memória que receberam menos do que a quantidade ideal após atingir o limite máximo de tamanho de solicitação. Consultas grandes podem demorar muito mais do que consultas com a quantidade ideal porque elas precisam ser derramadas em disco para permanecer dentro dos limites de memória.

Para aliviar o problema de concessão de memória, pode ser necessário aumentar o limite de tamanho de pool e/ou o limite máximo de tamanho de memória.

Observação
Se você aumentar apenas o tamanho máximo de memória, a simultaneidade de consultas poderá ser reduzida.

Neste cenário, há uma falha em uma consulta com um erro de tempo limite de concessão de memória.

O número total de solicitações de concessão de memória ativas e os limites de memória especificados nas definições do grupo de cargas de trabalho e do pool de recursos podem desempenhar uma função nos tempos limites de concessão de memória. Se um único pool de recursos for compartilhado por vários grupos de recursos, o número de consultas simultâneas em outros grupos também poderá afetar os tempos limites de concessão de memória.

As seguintes ações ajudam a determinar as melhores configurações do pool de recursos.

• Consulte sys.dm_exec_query_memory_grants para verificar o número de concessões de memória e das consultas em espera neste grupo e pool.

• Consulte sys.dm_exec_query_resource_semaphores para verificar a memória total concedida e os destinos.

Se o uso de memória concedida for maior do que o espaço de memória disponível, você poderá considerar aumentar o limite de tamanho do pool de recursos.

Há falha em uma consulta com um erro de memória insuficiente.

Solução de problemas básica

Solução de problemas avançada

O erro de Memória insuficiente (701) é um erro genérico retornado quando as tarefas tentam alocar um bloco de memória do gerenciador de memória e há falha na tentativa. Para obter mais informações, consulte MSSQLSERVER_701.

As seguintes condições podem provocar esse erro:

• O pool de memória atinge seu limite total.

Observação
O Administrador de Recursos talvez não seja a única causa dessa condição. Pode haver outros aplicativos em execução no servidor que têm exigências de memória que contribuem para essa condição.

• Há falha na alocação de espaço para múltiplas páginas ou para endereço virtual porque o espaço de endereço virtual não tem um bloco livre grande o suficiente para a reserva necessária. É mais provável que isso ocorra em uma arquitetura de 32 bits, e é improvável que ocorra em uma arquitetura de 64 bits.

• Há falha na alocação de espaço para múltiplas páginas ou para endereço virtual porque a confirmação total atinge o limite de confirmação. Isso se aplica a arquiteturas de 32 e 64 bits.

Quando você vir o erro de memória insuficiente, o melhor ponto de partida para a investigação do erro será o log de erros. O log contém saída semelhante ao exemplo a seguir:

2006-01-28 04:27:15.43 spid51 Páginas de alocação com falha: FAIL_PAGE_ALLOCATION 1

As possíveis falhas registradas no log de erros são:

• FAIL_PAGE_ALLOCATION seguido pelo número de páginas que se tentou alocar

• FAIL_VIRTUAL_RESERVE seguido pelo número de bytes que se tentou reservar

• FAIL_VIRTUAL_COMMIT seguido pelo número de bytes que se tentou confirmar

É importante entender que a tarefa que dispara o erro de memória insuficiente normalmente não é a tarefa que provoca o erro. A menos que exista uma tarefa sem-controle, a condição de memória insuficiente é normalmente a culminação de várias tarefas em execução. Como resultado, usando o caso bastante comum de um erro FAIL_PAGE_ALLOCATION, sua investigação pode utilizar uma visão mais ampla das atividades do sistema.

A próxima parte das valiosas informações do log de erros é a saída do status da memória. Dependendo da falha, você deve procurar por uma única página, múltiplas páginas, números virtuais reservados ou confirmados por administradores de memória individuais. A identificação dos maiores consumidores de memória é uma etapa chave para continuar a investigar o erro. Normalmente, os maiores consumidores de memória são dos seguintes tipos:

• MEMORYCLERK_ * indica que a configuração ou a carga de trabalho do servidor precisa de uma alocação de memória específica. Existem administradores de memória correspondentes para componentes do SQL Server e componentes individuais podem ter vários administradores de memória. Para obter mais informações, consulte sys.dm_os_memory_clerks (Transact-SQL). Às vezes, é possível identificar a carga de trabalho que está provocando o problema a partir dos administradores de memória, mas é mais provável que seja necessário examinar os objetos de memória associados aos administradores para descobrir o que provocou a grande quantidade de consumo de memória.

• CACHESTORE_ *, USERSTORE_ *, OBJECTSTORE_ * são os tipos de cache. O alto consumo de memória por um cache pode indicar que:

  • A memória esteja alocada fora do cache, mas ainda não esteja inserida como uma entrada que pode ser removida. Isso é muito semelhante ao caso MEMORYCLERK acima.

  • Todas as entradas do cache estejam em uso e não possam ser removidas. Isso pode ser confirmado consultando os sys.dm_os_memory_cache_counters e comparando os valores da coluna entries_count e da coluna entries_in_use_count.

• MEMORYCLERK_SQLQERESERVATIONS mostra a quantidade de memória reservada pela execução de consulta (QE) para executar consultas com classificações/junções. Um erro de memória insuficiente que ocorre quando as reservas são altas, normalmente indica um bug no servidor.

A saída de status da memória no log de erros também mostrará qual pool de memória está esgotado. Os agentes de memória de cada pool mostram a distribuição entre memória roubada (compilação), armazenada em cache e reservada (concedida). Os números dos três agentes correspondem aos objetos de memória anteriores associados aos agentes de memória. É possível descobrir a quantidade de memória alocada para um pool a partir de um administrador específico ou objeto de memória extraindo as informações de um despejo completo, usando um script personalizado, e a exibição de gerenciamento dinâmico de sys.dm_os_memory_cache_entries mostra a pool_id a qual cada entrada está associada.

Se for necessário contatar os Serviços de Atendimento ao Cliente, colete as seguintes informações para nossa equipe de suporte:

• O log de erros que mostra o erro de memória insuficiente e a saída de status da memória na hora do erro.

• A saída das seguintes instruções:

  dbcc memorystatus
  dbcc sqlperf(spinlockstats)
  select * from sys.dm_os_memory_clerks
  select * from sys.dm_os_wait_stats order by wait_type
  select * from sys.dm_os_waiting_tasks
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_OOM'
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_RESOURCE_MONITOR'
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_MEMORY_BROKER'
  select * from sys.dm_os_memory_cache_clock_hands
  

• Opcionalmente, um despejo da memória insuficiente coletado com T8004. Esse minidespejo terá informações valiosas, como os buffers de anel e o status de spinlock/espera. O contador de despejo para T8004 pode ser redefinido sem reinicializar o servidor desativando e ativando T8004.

Neste cenário, você suspeita que uma consulta está demorando por causa de um plano de consulta de qualidade inferior. O otimizador de consultas pode gerar um plano de consulta com qualidade inferior se não tiver memória suficiente por causa de uma configuração baixa do limite de memória para um pool de recursos.

As seguintes ações ajudam a determinar as melhores configurações do pool de recursos.

• Obtenha os dados do contador Otimizações de consultas/s para verificar se o grupo de cargas de trabalho tem um número alto de compilações de consultas.

• Obtenha os dados do contador Planos de qualidade inferior/s para verificar se o otimizador de consulta está gerando planos de qualidade inferior com frequência.

Se qualquer uma das condições anteriores existir, considere a possibilidade de aumentar o limite de memória do pool de recursos.