Banco de dados SQL Server TempDB e contenção de trava

Banco de dados SQL Server TempDB e contenção de trava

Eliminating the PAGELATCH_UP latch problem fort he TempDB database


Neste artigo, aprenderemos os problemas de contenção de trava que podemos enfrentar no banco de dados SQL Server TempDB. Também discutiremos os motivos e o método de solução desses problemas de contenção de trava. Especialmente, mencionaremos o recurso de metadados TempDB com otimização de memória que foi introduzido no SQL Server 2019.

Primeiramente, aprenderemos brevemente as características essenciais do banco de dados TempDB e também falaremos sobre o conceito de trava do SQL Server para que possamos entender todos os aspectos dos problemas de contenção de trava do banco de dados TempDB com mais clareza.

Para que é usado o banco de dados TempDB no SQL Server?

O banco de dados TempDB é um dos bancos de dados do sistema do SQL Server, mas possui várias funcionalidades exclusivas, distintas de outros bancos de dados do sistema. Tabelas temporárias globais e locais são criadas nesse banco de dados SQL Server TempDB e os dados dessas tabelas são armazenados por esse banco de dados. Ao mesmo tempo, variáveis ​​de tabela, procedimentos armazenados temporários e cursores são usados ​​neste recurso de banco de dados. Além disso, os recursos TempdDB também são usados ​​pelos seguintes recursos.

  • Isolamento de instantâneo e isolamento de instantâneo confirmado por leitura

  • Operações de índice online

  • MARS – (vários conjuntos de resultados ativos)

Quando reiniciamos, o banco de dados TempdDB do mecanismo SQL é descartado e recriado. Não podemos recuperar esse banco de dados e não podemos alterar o modelo de recuperação de simples para outros. Quando levamos em conta tudo isso, podemos dizer que as configurações do banco de dados TempDB afetam diretamente o desempenho da consulta.

Qual é a trava no SQL Server?

Um buffer pool SQL é o local de memória reservado pelo sistema operacional para o SQL Server e também é chamado como cache de buffer SQL. O SQL Server transfere as páginas de dados para a memória do disco para lê-las ou manipulá-las e as envia de volta ao disco de acordo com uma lógica especial. O principal objetivo desse mecanismo é o desejo de oferecer um desempenho mais rápido aos clientes, porque a memória é sempre mais rápida que os sistemas de armazenamento. Nesse contexto, precisamos de um mecanismo para garantir a consistência das páginas de dados no buffer pool. Uma trava é um objeto de sincronização usado para proteger as estruturas de dados mantidas na memória contra inconsistência e corrupção, para que o SQL Server garanta a consistência das páginas de dados na memória. Essa operação de sincronização é gerenciada internamente pelo SQL Server.

Metadados Contenção de banco de dados TempDB

A contenção de metadados do TempDB ocorre quando muitas sessões tentam acessar as tabelas de sistema do SQL Server TempDB ao mesmo tempo durante a criação das tabelas temporárias. Essa carga de trabalho pesada causa latência nessas tabelas do sistema devido a esse motivo e o desempenho da consulta será reduzido.

Agora, criaremos uma carga de trabalho falsa no TempDB para simular esse problema. Usaremos uma ferramenta antiga, mas válida, chamada SQLQueryStress para gerar uma carga de trabalho falsa no banco de dados TempDB.

Inicialmente, criaremos o seguinte procedimento. Este procedimento armazenado criará uma tabela temporária e inserirá 20 linhas aleatórias da tabela sys.all_columns.

Iniciaremos o SQLQueryStress e colaremos a seguinte consulta no painel de consulta. Esta consulta executa o procedimento armazenado ProcTest 100 vezes em um loop WHILE.

Vamos definir o Número de iterações como 100 e definirá Número de processos como 25 para que o procedimento armazenado seja executado 2500 vezes.

Usando o SQLQueryStress para contenção de banco de dados TempDB do SQL Server

Vamos clicar no Base de dados e defina as configurações de conexão e credenciais do banco de dados.

Configurações do banco de dados SQLQueryStress

Vamos clicar no VAI para iniciar a execução da consulta.

Iniciando o SQLQueryStress

Enquanto o SQLQueryStress está executando a consulta, estamos executando sp_WhoisActive e analise os resultados.

Analisando a contenção de metadados do banco de dados TempDB do SQL Server com sp_whoisactive

Como podemos ver, o tipo de espera PAGELATCH_EX pode ser visto na coluna wait_info do banco de dados TempDB. Específico ao banco de dados TempDB, podemos superar essa espera usando um novo recurso do SQL Server 2019. Na próxima seção, aprenderemos esse recurso.

Metadados TempDB com otimização de memória

Quando ativamos o recurso de metadados TempDB com otimização de memória, ele converte algumas das tabelas de sistema do SQL Server TempDB em tabelas otimizadas de memória não duráveis, para minimizar a latência nas tabelas de sistema do TempDB. As tabelas com otimização de memória oferecem baixa latência, alta taxa de transferência e tempo de resposta acelerado; portanto, esse recurso tira proveito desses aprimoramentos de desempenho.

Podemos ativar esse recurso através da seguinte consulta:

Ou podemos usar a seguinte consulta para ativar esta opção.

A consulta a seguir nos ajuda a detectar o status desse recurso.

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br

Habilitando o recurso de metadados TempDB com otimização de memória para o SQL Server 2019

Após ativar o recurso de metadados TempDB otimizado para memória, o mecanismo do SQL Server deve ser reiniciado. Após reiniciar o SQL Server, podemos ver a lista das tabelas que são convertidas nas tabelas com otimização de memória através da seguinte consulta:

Lista da tabela TempDB do SQL Server que foi convertida para a memória otimizada

Quando executamos novamente o SQLQueryStress para a mesma consulta com os mesmos parâmetros. A saída sp_WhoIsActive será alterada e não vemos nenhuma PAGELATCH_EX tipo de espera.

Eliminando o PAGELATCH_EX para o banco de dados TempDB

O recurso de metadados TempDB com otimização de memória tem algumas limitações, e devemos considerar essas limitações antes de decidir usá-lo:

Inicialmente, criamos uma tabela temporária local cujo nome é # temp1, e quando tentamos criar um índice columnstore para ele, não tivemos êxito porque o recurso de metadados com otimização de memória está ativado.

Procedimento armazenado, sp_estimate_data_compression_savings calcula os ganhos de compactação estimados para as tabelas anteriores para compactar a operação. No entanto, quando ativamos a opção de metadados TempDB com otimização de memória, esse procedimento não funciona para a tabela t1 porque inclui um índice columnstore.

Contenção de página de alocação de banco de dados TempDB

Páginas de dados são a unidade fundamental do SQL Server que armazena dados e o tamanho das páginas de dados é 8 KB. As oito páginas de dados fisicamente contíguas são nomeadas extensão. As informações sobre quais extensões são alocadas são registradas pelo Mapa de alocação global (GAM). As informações sobre quais extensões são usadas como misturadas são registradas pelo Mapa de alocação global compartilhada (SGAM). Espaço livre na página (PFS) registra quanto espaço livre está disponível em qual página nas extensões.

Uma sessão deve atualizar as páginas de alocação TempDB do SQL Server ao criar e eliminar tabelas temporárias. À medida que esse número de conexões simultâneas começa a aumentar, o acesso à alocação de páginas se torna mais difícil, pois, por vez, apenas um thread pode alterar essas páginas; portanto, outros threads precisam aguardar o lançamento do recurso alocado. Agora vamos simular esse cenário.

  • Iniciaremos o SQLQueryStress e colaremos a seguinte consulta no painel de consulta:

  • Vamos definir o Número de iterações como 100 e definir Número de processos como 200:

    Usando a contenção do banco de dados SQLQueryStress for TempDB

  • Vamos clicar no Base de dados e defina as credenciais do banco de dados e outras configurações:

    Configurações de banco de dados do SQLQueryStress

  • Vamos clicar no VAI para iniciar a execução da consulta:

    Iniciando o SQLQueryStress para executar as consultas

Enquanto o SQLQueryStress está executando as consultas, estamos executando sp_WhoisActive e analisamos o resultado da coluna wait_info.

Eliminando o problema de trava PAGELATCH_UP no banco de dados TempDB

Como podemos ver, o tipo de espera PAGELATCH_UP pode ser visto na coluna wait_info. Se adicionarmos mais arquivos de dados ao banco de dados TempDb, esse problema será minimizado, e a Microsoft recomenda uma fórmula para quantos arquivos precisamos.

“Se o número de processadores lógicos for menor ou igual a oito (8), use o mesmo número de arquivos de dados que os processadores lógicos. Se o número de processadores lógicos for maior que oito (8), use oito arquivos de dados. Se a contenção continuar, aumente o número de arquivos de dados em múltiplos de quatro (4) até o número de processadores lógicos até que a contenção seja reduzida para níveis aceitáveis. ”

De acordo com esta fórmula, podemos aumentar o número do arquivo do banco de dados TempDB para minimizar esse problema.

Conclusão

As configurações do banco de dados TempDB do SQL Server afetam o desempenho das consultas, portanto, precisamos configurá-lo com atenção. Neste artigo, discutimos os problemas de contenção de trava que podemos enfrentar no banco de dados TempDB e também examinamos os métodos de solução correspondentes.

Esat Erkec
Últimas mensagens de Esat Erkec (ver todos)

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br
Leia Também  Importar dados de vários arquivos do Excel para tabelas do SQL Server usando um pacote SSIS