Anton1151's questions

No meu banco de dados, ninguém está adicionando, removendo ou desabilitando restrições fora de patches e lançamentos. Todas as restrições são habilitadas e confiáveis. Há uma verificação regular de restrições não confiáveis ​​ou desabilitadas, caso uma seja introduzida acidentalmente. DBCC CHECKDBtambém é executado regularmente para verificar se há corrupção.

Existe algum benefício em correr com DBCC CHECKCONSTRAINTSa mesma regularidade DBCC CHECKDB? Ou estou causando trabalho desnecessário? Quando DBCC CHECKCONSTRAINTSgeralmente deve ser executado?

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?