Diagnóstico de solução de problemas de desempenho de hiperescala do SQL

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure

Para solucionar problemas de desempenho em um banco de dados de hiperescala, as metodologias gerais de ajuste de desempenho no nó de computação do Banco de Dados SQL do Azure é o ponto inicial de uma investigação de desempenho. No entanto, considerando a arquitetura distribuída de hiperescala, diagnósticos adicionais foram adicionados para auxiliar. Este artigo descreve dados de diagnóstico específicos de hiperescala.

Esperas causadas por limitação de taxa de log

Cada objetivo de serviço do Banco de Dados SQL do Azure tem limites de taxa de geração de log impostos por meio da governança de taxa de log. Em hiperescala, o limite de controle de log é definido como 105 MB/s, independentemente do nível de serviço. Esse valor é exposto na coluna primary_max_log_rate em sys.dm_user_db_resource_governance.

No entanto, há ocasiões em que a taxa de geração de log na réplica de computação primária deve ser limitada para manter os SLAs (Contrato de nível de serviço) de recuperação. Essa limitação ocorre quando um servidor de página ou outra réplica de computação está significativamente atrasado na aplicação de novos registros de log do serviço de log. Se nenhum servidor de página ou réplica estiver com atraso, o mecanismo de limitação permitirá que a taxa de geração de log atinja 100 MB/s. Essa é a taxa máxima efetiva de geração de log em todos os objetivos de serviço de Hiperescala.

Os seguintes tipos de espera (em sys.dm_os_wait_stats) descrevem os motivos pelos quais a taxa de log pode ser limitada na réplica de computação primária:

Tipo de Espera Descrição
RBIO_RG_STORAGE Ocorre quando uma taxa de geração de log do nó de computação primária do banco de dados de hiperescala está sendo limitada devido ao consumo de log atrasado no(s) servidor(es) de página.
RBIO_RG_DESTAGE Ocorre quando uma taxa de geração de log do nó de computação do banco de dados de hiperescala está sendo limitada devido ao consumo de log atrasado pelo armazenamento de log de longo prazo.
RBIO_RG_REPLICA Ocorre quando uma taxa de geração de log do nó de computação do banco de dados de hiperescala está sendo limitada devido ao consumo de log atrasado pela(s) réplica(s) secundária(s) para leitura.
RBIO_RG_GEOREPLICA Ocorre quando uma taxa de geração de log do nó de computação do banco de dados de hiperescala está sendo limitada devido ao consumo de log atrasado pela réplica geográfica secundária.
RBIO_RG_LOCALDESTAGE Ocorre quando uma taxa de geração de log do nó de computação do banco de dados de hiperescala está sendo limitada devido ao consumo de log atrasado pelo serviço de log.

Leituras de servidor de página

As réplicas de computação não armazenam em cache uma cópia completa do banco de dados localmente. Os dados locais para a réplica de computação são armazenados no pool de buffers (na memória) e no cache RBPEX (extensão de pool de buffers resilientes locais) que é um cache parcial (não abrangendo) de páginas de dados. Esse cache RBPEX local é dimensionado proporcionalmente ao tamanho da computação e é três vezes a memória da camada de computação. O RBPEX é semelhante ao pool de buffers, pois ele tem os dados acessados com mais frequência. Por outro lado, cada servidor de página tem um cache RBPEX de cobertura para a parte do banco de dados que ele mantém.

Quando uma leitura é emitida em uma réplica de computação, se os dados não existirem no pool de buffers ou no cache RBPEX local, uma chamada de função getPage (pageId, LSN) será emitida e a página será buscada no servidor de páginas correspondente. As leituras de servidores de páginas são leituras remotas e, portanto, são mais lentas do que as leituras do RBPEX local. Ao solucionar problemas de desempenho relacionados a E/S, precisamos saber quantas E/S foram feitas por meio de leituras de servidor de página remota relativamente mais lentas.

Várias DMVs (exibições de gerenciamento dinâmico) e eventos estendidos têm colunas e campos que especificam o número de leituras remotas de um servidor de páginas, que podem ser comparadas com o total de leituras. O repositório de consultas também captura as leituras remotas como parte das estatísticas de tempo de execução de consulta.

  • Colunas para relatar as leituras do servidor da página estão disponíveis em DMVs de execução e exibições de catálogo, como:

  • As leituras de servidor de página são adicionadas aos seguintes eventos estendidos:

    • sql_statement_completed
    • sp_statement_completed
    • sql_batch_completed
    • rpc_completed
    • scan_stopped
    • query_store_begin_persist_runtime_stat
    • query-store_execution_runtime_info

  • ActualPageServerReads/ActualPageServerReadAheads são adicionados ao XML do plano de consulta para planos reais. Por exemplo:

<RunTimeCountersPerThread Thread="8" ActualRows="90466461" ActualRowsRead="90466461" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="133645" ActualCPUms="85105" ActualScans="1" ActualLogicalReads="6032256" ActualPhysicalReads="0" ActualPageServerReads="0" ActualReadAheads="6027814" ActualPageServerReadAheads="5687297" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobPageServerReads="0" ActualLobReadAheads="0" ActualLobPageServerReadAheads="0" />

Observação

Para exibir esses atributos na janela de propriedades do plano de consulta, é necessário o SSMS (SQL Server Management Studio) 18.3 ou posterior.

Estatísticas de arquivo virtual e contabilização de E/S

No Banco de Dados SQL do Azure, a DMF (função de gerenciamento dinâmico) sys.dm_io_virtual_file_stats () é a principal maneira de monitorar a E/S do Banco de Dados SQL. As características de E/S em Hiperescala são diferentes devido à sua arquitetura distribuída. Nesta seção, nos concentramos na E/S (leituras e gravações) nos arquivos de dados, como visto nessa DMF. Em hiperescala, cada arquivo de dados visível nessa DMF corresponde a um servidor de página remoto. O cache RBPEX mencionado aqui é um cache local baseado em SSD (unidade de estado sólido), que é um cache sem cobertura na réplica de computação.

Uso do cache RBPEX local

O cache RBPEX local existe na réplica de computação, no armazenamento SSD local. Portanto, a E/S nesse cache é mais rápida do que a E/S em servidores de página remota. Atualmente, sys.dm_io_virtual_file_stats () em um banco de dados de hiperescala tem uma linha especial relatando a E/S em relação ao cache RBPEX local na réplica de computação. Essa linha tem o valor de 0 para as colunas database_id e file_id. Por exemplo, a consulta abaixo retorna estatísticas de uso de RBPEX desde a inicialização do banco de dados.

select * from sys.dm_io_virtual_file_stats(0,NULL);

Uma taxa de leituras feitas em RBPEX para leituras agregadas feitas em todos os outros arquivos de dados fornece a taxa de acertos do cache RBPEX. O contador RBPEX cache hit ratio também é exposto na DMV sys.dm_os_performance_counters dos contadores de desempenho.

Leituras de dados

  • Quando as leituras são emitidas pelo mecanismo de banco de dados do SQL Server em uma réplica de computação, elas podem ser servidas pelo cache RBPEX local ou por servidores de página remota, ou por uma combinação dos dois se estiver lendo várias páginas.
  • Quando a réplica de computação lê algumas páginas de um arquivo específico, por exemplo file_id 1, se esses dados residem exclusivamente no cache RBPEX local, toda E/S para essa leitura é contabilizada em file_id 0 (RBPEX). Se alguma parte desses dados estiver no cache RBPEX local e alguma parte estiver em um servidor de página remoto, a E/S será contabilizada em relação a file_id 0 para a parte armazenada em RBPEX, e a parte armazenada em servidor de página remoto será contabilizada em relação a file_id 1.
  • Quando uma réplica de computação solicitar uma página em um determinado LSN (número de sequência de log) de um servidor de página, se ele não tiver sido detectado até o LSN solicitado, a leitura na réplica de computação aguardará até que o servidor de página seja exibido antes que a página seja retornada para a réplica de computação. Para qualquer leitura de um servidor de página na réplica de computação, você verá o tipo de espera PAGEIOLATCH_* se ele estiver aguardando nessa E/S. Em hiperescala, esse tempo de espera inclui o tempo para acompanhar a página solicitada no servidor de página para o LSN necessário e o tempo necessário para transferir a página do servidor de página para a réplica de computação.
  • Leituras grandes, como read-ahead, geralmente são feitas usando leituras de scatter-gather. Isso permite leituras de até 4 MB de páginas por vez, consideradas uma única leitura no mecanismo de Banco de Dados SQL Server. No entanto, quando os dados que estão sendo lidos estão em RBPEX, essas leituras são contadas como várias leituras individuais de 8 KB, pois o pool de buffers e o RBPEX sempre usam páginas de 8 KB. Como resultado, o número de E/S de leitura visto em relação a RBPEX pode ser maior do que o número real de E/S executado pelo mecanismo.

Gravações de dados

  • A réplica de computação primária não grava diretamente em servidores de página. Em vez disso, os registros de log do serviço de log são reproduzidos nos servidores de página correspondentes.
  • As gravações que acontecem na réplica de computação são, predominantemente, gravações no RBPEX local (file_id 0). Para gravações em arquivos lógicos maiores que 8 KB, em outras palavras aquelas feitas usando o Gather-write, cada operação de gravação é convertida em várias gravações individuais de 8 KB em RBPEX, já que o pool de buffers e RBPEX sempre usam páginas de 8 KB. Como resultado, o número de E/S de gravação visto em relação a RBPEX pode ser maior do que o número real de E/S executado pelo mecanismo.
  • Os arquivos que não forem de RBPEX ou arquivos de dados diferentes de file_id 0 que correspondem a servidores de página também mostram gravações. Na camada de serviço de hiperescala, essas gravações são simuladas, pois as réplicas de computação nunca gravam diretamente em servidores de página. O IOPS de gravação e a taxa de transferência são contabilizados à medida que ocorrem na réplica de computação, mas a latência de arquivos de dados diferentes de file_id 0 não reflete a latência real das gravações do servidor de página.

Gravações de log

  • Na computação primária, uma gravação de log é contabilizada no file_id 2 de sys.dm_io_virtual_file_stats. Uma gravação de log na computação primária é uma gravação na zona de destino do log.
  • Os registros de log não são protegidos na réplica secundária em uma confirmação. Em hiperescala, o log é aplicado pelo serviço de log para as réplicas secundárias de forma assíncrona. Como as gravações de log não ocorrem na verdade em réplicas secundárias, todas as estatísticas de E/S de logs nas réplicas secundárias são apenas para fins de rastreamento.

E/S de dados nas estatísticas de utilização de recursos

Em um banco de dados que não é de hiperescala, os IOPS de leitura e gravação combinados em relação aos arquivos de dado, referentes ao limite de IOPS de dados de governança de recursos, são relatados nas exibições sys.dm_db_resource_stats e sys.resource_stats na coluna avg_data_io_percent. O mesmo valor é relatado no portal do Azure como Porcentagem de E/S de Dados.

Em um banco de dados de hiperescala, essa coluna relata a utilização de IOPS de dados em relação ao limite do armazenamento local somente na réplica de computação, especificamente a E/S em relação a RBPEX e tempdb. Um valor de 100% nessa coluna indica que a governança de recursos está limitando o IOPS de armazenamento local. Se isso estiver correlacionado a um problema de desempenho, ajuste a carga de trabalho para gerar menos E/S ou aumente o objetivo do serviço de banco de dados para aumentar o limitemáximo de IOPS de dados de governança de recursos. Para a governança de recursos de leituras e gravações de RBPEX, o sistema conta E/S de 8 KB individual, em vez de E/S maior que pode ser emitida pelo mecanismo de Banco de Dados SQL Server.

A E/S de dados em servidores de página remota não é relatada nas exibições de utilização de recursos ou no portal, mas é relatada na DMF sys.dm_io_virtual_file_stats (), conforme observado anteriormente.

Recursos adicionais