Início / dba / Perguntas / 267570
Accepted
Anton1151
Asked: 2020-05-21 18:11:58 +0800 CST

Como você minimiza a chance de colisão entre restrições nomeadas pelo sistema em tabelas temporárias?

  • 772

Ocasionalmente, encontramos o erro “Já existe um objeto chamado 'PK__#TempTab__0796211ACE71813B' no banco de dados” quando alguns de nossos procedimentos tentam criar tabelas temporárias com uma restrição PK nomeada pelo sistema assim:

CREATE OR ALTER PROCEDURE dbo.StoredProc AS
BEGIN
    CREATE TABLE #TempTable (id INT NOT NULL, PRIMARY KEY CLUSTERED (id))
    ...
END

A consulta de tempdb.sys.objects revela algumas centenas de restrições no formato PK__#TempTab__XYZ. Muitos foram criados horas atrás e não foram modificados desde então. Havia muito poucas sessões ativas quando olhei para tempdb.sys.objects, então é difícil acreditar que existem muitas tabelas temporárias em uso atualmente. Temos muitas tabelas temporárias com nomes semelhantes criadas em muitos procedimentos armazenados.

Acho que o cache de tabela temporária é responsável aqui. Parece que essas restrições de PK permanecem no tempdb até que seus planos em cache associados sejam removidos. Os testes mostraram que desabilitar o cache da tabela temporária (criar estatísticas, adicionar restrições nomeadas etc.) faz com que a restrição PK seja removida de tempdb.sys.objects quando a tabela temporária sai do escopo. Acionar recompilações e liberar o cache do proc também limpa essas restrições de PK de tempdb.sys.objects.

Estou ciente de que as restrições nomeadas pelo sistema não são garantidas como exclusivas, conforme descrito nesta postagem O SQL Server pode criar colisões em nomes de restrição gerados pelo sistema? .

Minhas perguntas são:

  • Estou errado em supor que o cache de tabela temporária aumenta a chance de um conflito de nomes para restrições nomeadas pelo sistema em tabelas temporárias?
  • O excesso de cache do plano poderia ser responsável pelo grande número de restrições PK__#TempTab__XYZ que estou vendo em tempdb.sys.objects, aumentando assim a chance de duas restrições terem o mesmo nome? Estou tentando entender por que vimos esse erro raro com tanta frequência.
  • Se não pudermos garantir que as restrições nomeadas pelo sistema nas tabelas temporárias nunca encontrarão colisões de nomenclatura, o que podemos fazer para reduzir a chance dessas colisões em geral?
sql-server-2016 tempdb

1 respostas

  1. Best Answer

    A solução mais fácil é usar um índice clusterizado exclusivo que não precisa ser exclusivo no sistema (ao contrário de uma restrição de chave primária). Caso contrário, eles funcionam da mesma forma - um índice exclusivo ainda é uma restrição que evita duplicatas e também pode ser usado como destino de chaves estrangeiras.

    Eu esqueço se essa sintaxe de índice inline específica era possível em 2016 (eu só tenho 2019 localmente), mas isso funciona bem em versões mais recentes:

    CREATE TABLE #fooA(id int, INDEX PK1 UNIQUE clustered(id));
CREATE TABLE #fooB(id int, INDEX PK1 UNIQUE clustered(id));

    Se essa sintaxe ainda não fosse válida em 2016, você poderia criar um índice clusterizado exclusivo em uma segunda etapa, que você teria que nomear (mas não precisaria encontrar uma maneira complicada de tornar esse nome exclusivo ).

    Além disso, em vez de usar nomes genéricos como #TempTable, use nomes mais específicos para o conteúdo da tabela/funcionalidade do procedimento (como #Orders, #Customers, etc). Nos casos em que você precisa de um PK explícito, isso pelo menos reduziria a chance de colisões se todos os seus PKs não fossem mais nomeados pelo sistema PK__#TempTab_....

    Como um aparte, acho que nunca vi tantas tabelas temporárias em um sistema que haja colisões nesses nomes longos e que tentaram ser exclusivos. Você está tentando sobrecarregar os metadados?

    • 1

relate perguntas