Metadados TempDB com otimização de memória no SQL Server 2019

Metadados TempDB com otimização de memória no SQL Server 2019

Metadados TempDB com otimização de memória no SQL Server 2019
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Por: Aaron Bertrand | Atualizado: 2019-11-07 | Comentários (7) | Relacionados: Mais> SQL Server 2019

Problema

Desde que trabalhei com o SQL Server, e em quase todos os sistemas que
já gerenciado, o tempdb tem sido um gargalo de uma maneira ou de outra. Microsoft tem
fez muitas melhorias ao longo dos anos para ajudar a resolver gargalos com o acesso
para PFS, GAM, SGAM e outros recursos no tempdb. Em nenhuma ordem específica, alguns
Entre essas melhorias estão:

Embora eles tenham removido alguns desses gargalos, e geralmente isso faz com que o tempdb
menos perda de desempenho, certas cargas de trabalho podem introduzir gargalos em outros lugares.
Uma área em que a contenção ainda é comum, mesmo após todas essas melhorias,
é com metadados da tabela de sistema tempdb, em que muitas sessões estão tentando gravar
para tabelas do sistema (como uma carga de trabalho pesada que cria, altera e elimina muitas
#temp tables).

Solução

Metadados TempDB com otimização de memória é um novo recurso do SQL Server 2019, como
parte de "Em memória
Base de dados
"guarda-chuva de recursos. Muito parecido com" Always On "não é
um recurso específico, banco de dados na memória é um termo que descreve o conjunto de recursos
que permitem aproveitar a memória para ajudar a acelerar os bancos de dados.

Pam Lahoud descreve o principal sintoma dos metadados da tabela de sistema tempdb
problema de contenção da seguinte forma (e você pode se aprofundar nos detalhes de baixo nível

Aqui):

… Sessões no estado Suspenso com um tipo de espera PAGELATCH_xx
e
um recurso de espera de uma página que hospeda uma tabela do sistema TempDB, como 2: 1: 118.

Você pode verificar esta contenção usando a seguinte consulta, que limita a
resultados para PAGELATCH_xx espera envolvendo um específico
conjunto de tabelas do sistema tempdb:

SELECT r.session_id, r.wait_type, r.wait_resource, r.command, 
    (object) = OBJECT_NAME(p.(object_id),p.database_id)
  FROM sys.dm_exec_requests AS r
  CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS pc 
  CROSS APPLY sys.dm_db_page_info(pc.(db_id), pc.(file_id), pc.page_id, 'DETAILED') AS p
  WHERE UPPER(r.wait_type) like '%PAGELATCH%'
    AND p.database_id = 2 -- tempdb
    AND p.(object_id) IN(3,9,34,40,41,54,55,60,74,75);-- what are these IDs? you'll see shortly

(Nas versões mais antigas do SQL Server, você não poderá
para usar funções como

sys.dm_db_page_info. Você precisará manualmente
analisar o wait_resource
valor
e, em seguida, use

sys.dm_db_database_page_allocations ou, se
mais velho que isso,

DBCC PAGE e

DBCC IND, para determinar se sua contenção
envolve esse mesmo conjunto de tabelas.)

O recurso fornece uma nova configuração no nível da instância que move dez dos mais
tabelas de sistema comumente usadas no tempdb (correspondentes aos IDs de objetos listados
acima) para tabelas com otimização de memória. Isso elimina a trava, melhorando substancialmente
simultaneidade e resolução da maioria ou de todas as esperas observadas na consulta acima.
Observe que você ainda pode ver tipos de espera semelhantes associados a outro sistema
tabelas ou mesmo tabelas permanentes de usuário no tempdb (que, sendo um banco de dados do sistema,
atualmente não pode ter tabelas de usuário otimizadas para memória). Observe também que isso
não otimize a memória de nenhuma das suas próprias tabelas #temp ou variáveis ​​de tabela; esse recurso
afeta apenas metadados.

Você ativa o recurso com um ALTER SERVER CONFIGURATION
comando, que requer uma reinicialização:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA= ON;			

Você também pode usar sp_configure:

EXEC sys.sp_configureN'show advanced options', 1;
RECONFIGURE WITH OVERRIDE; EXEC sys.sp_configureN'tempdb metadata memory-optimized', 1;
RECONFIGURE WITH OVERRIDE;

E você pode verificar o valor atual em sys.configurations:

SELECT CASE WHENvalue <> value_in_use THEN 'restart required' END, *
  FROMsys.configurations
  WHERE name = N'tempdb metadata memory-optimized';

Ou com PROPRIEDADE DE SERVIDOR ():

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');			

Você não perceberá nada óbvio depois de reiniciar, mas poderá ver quais
tabelas de sistema foram convertidas em otimizadas para memória com a seguinte consulta:

SELECTt.(object_id), t.name
  FROMtempdb.sys.all_objects AS t
  INNER JOIN tempdb.sys.memory_optimized_tables_internal_attributes ASi
  ONt.(object_id) = i.(object_id);

Resultados (descrições adicionadas por mim; algumas

retirado dos documentos
, mas nem todos estão documentados):

object_id

nome

descrição

3

sysrscols

Armazena informações da coluna
como compensações, frequência de alteração, tipos e valores máximos em linha.

9

sysseobjvalues

Coluna de lojas
em formação

como valores padrão.

34

sysschobjs

Armazena uma linha para cada objeto.

40.

sysmultiobjvalues

Armazena informações sobre
entidades como chaves de criptografia de coluna.

41

syscolpars

Armazena uma linha para cada tabela / exibição
coluna e procedimento / parâmetro de função.

54

sysidxstats

Armazena uma linha para cada índice
ou estatística.

55

sysiscols

Armazena uma linha para cada persistente
coluna de índice e estatística.

60

sysobjvalues

Armazena uma ampla variedade de propriedades
para entidades.

74

syssingleobjrefs

Armazena uma linha para dependências N: 1
(pense em vincular esquemas de partição a tabelas).

75

sysmultiobjrefs

Armazena uma linha para dependências N: N
(pensar sys.sql_dependencies)

Não há muitas outras evidências de que o recurso tenha sido ativado, outros
do que a eliminação da contenção identificada acima e o desempenho geral do snappier
(Niko Neugebauer

mostra algumas observações aqui) Inicialmente, verifiquei as propriedades de arquivos e grupos de arquivos,
meio que espera que o tempdb seja iniciado com um grupo de arquivos adicional com otimização de memória.
Isso não aconteceu porque, como parte do tempdb, que é recriado em todos os
início do serviço, essas tabelas de sistema agora não são, por definição, duráveis
mesas. Portanto, a durabilidade fornecida pelo grupo de arquivos otimizado para memória que você precisa
adicionar aos bancos de dados do usuário não é necessário aqui.

Qual é o problema?

Parece uma grande vitória e um acéfalo para acender quando estiver ligado
SQL Server 2019. Então, por que não está ativado por padrão? Bem, existem alguns
limitações na primeira implementação das quais você deve estar ciente:

  • Você precisa ter cuidado quando tiver transações que envolvam memória
    tabelas nos bancos de dados do usuário e também certos padrões podem gerar os seguintes
    erro:

Msg 41317,
Nível 16, Estado 1
Uma transação do usuário que acessa tabelas com otimização de memória
ou módulos compilados nativamente não podem acessar mais de um banco de dados ou bancos de dados do usuário
model e msdb e não pode gravar no master.

  • Os índices columnstore não são suportados nas tabelas #temp quando esse recurso
    está ativado. Se você tentar, você receberá o seguinte erro:

Msg 11442,
Nível 16, Estado 1
A criação do índice columnstore não é suportada no tempdb
quando o modo de metadados com otimização de memória estiver ativado.

  • Uma questão relacionada é que sys.sp_estimate_data_compression_savings
    não pode ser usado para estimar COLUMNSTORE ou
    COLUMNSTORE_ARCHIVE (mesmo erro: Msg 11442). Eu
    achamos que podemos escrever nossa própria versão deste procedimento
    que não usa #temp tables, mas guardarei isso para outra hora.
  • Isso não deve afetar muitas pessoas, mas bloquear dicas de qualquer sistema
    visualizações que fazem referência a essas tabelas do sistema serão ignoradas; efetivamente, eles
    usará READ COMMITTED SNAPSHOT.
  • Finalmente, esse recurso requer uma reinicialização do serviço para entrar em vigor; você não pode
    basta decidir no meio de um ciclo de negócios que você vai virar
    ligado (ou desligado). Para evitar interrupções, a ativação ou desativação precisa ser planejada
    coincidir com uma janela de failover ou manutenção. E não há garantia
    que esse recurso melhorará o desempenho da sua carga de trabalho, justificando
    essa interrupção pode exigir testes para provar que vai ajudar.

Sumário

Se você tiver uma carga de trabalho pesada em tempdb, vale a pena experimentar esse recurso, pois
isso deve ajudar. Embora existam algumas limitações, lembre-se de que este é o
primeira iteração e certamente mais acontecerá nos lançamentos futuros (bem como
a primeira iteração do Columnstore não foi perfeita). Eu estou supondo que eles
pode adicionar mais tabelas de metadados que não foram cortadas (pense em sysallocunits
e sysrowsets), tenha certeza de que eles resolverão as incompatibilidades mencionadas
acima, e espero que eles possam expandir essa funcionalidade para criar tabelas temporárias e
variáveis ​​da tabela em algum momento no futuro.

Próximos passos

Leia para obter dicas relacionadas e outros recursos:

Última atualização: 2019-11-07

Sobre o autor

Aaron Bertrand (@AaronBertrand) é um tecnólogo apaixonado com experiência no setor que remonta ao ASP clássico e SQL Server 6.5. Ele é editor-chefe do blog relacionado ao desempenho, SQLPerformance.com, e atua como moderador da comunidade do Exchange Administrators Stack Exchange.

Ver todas as minhas dicas



Leia Também  Como recuperar exclusões acidentais no Armazenamento de Blob do Azure