Por que e como às vezes recebo o "erro de tabela ausente" enquanto há definitivamente um bloqueio Sch-M?

Pergunta realmente interessante e difícil. Não consegui encontrar nenhuma documentação oficial desse comportamento e suspeito que não haja nenhuma (embora eu adoraria se alguém me corrigisse sobre isso!).

Minha pesquisa me leva a acreditar que é a etapa de compilação do plano que é vulnerável a essa condição de corrida. Observe que consegui executar suas consultas de teste por uma hora sem erros, mas se eu iniciar seu processo repetidamente, recebo um erro imediato em uma parte do tempo. Quando ocorre um erro, sempre o faz imediatamente na compilação do plano. Como alternativa, você pode adicionar "OPTION RECOMPILE" ao COUNT(*) no loop, forçando a compilação de um novo plano a cada tentativa. Usando essa abordagem, vejo o erro quase imediatamente toda vez que executo seus scripts.

Consegui reproduzir o erro com uma série controlada de etapas que parece atingir o erro a cada tentativa, eliminando a necessidade de configurar um loop e confiar na aleatoriedade.

Eu também lancei uma correção potencial (para usar ALTER TABLE...SWITCH) que pode ser possível experimentar em seu ambiente de produção. Agora, vamos aos detalhes!

Aqui estão os passos para reproduzir:

-- Instructions: Process each step one at a time, following any instructions in that step

-- (1) Initial setup: Clean any relics and create the test table
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;
SET NOCOUNT ON;
IF @@TRANCOUNT > 0 ROLLBACK
IF object_id('test') IS NOT NULL 
    DROP TABLE test
IF object_id('TMP_test') IS NOT NULL 
    DROP TABLE TMP_test
IF object_id('test1') IS NOT NULL 
    DROP TABLE test1
CREATE TABLE test(Id INT IDENTITY CONSTRAINT PK_test PRIMARY KEY)
GO
INSERT test DEFAULT VALUES
GO 2000

-- (2) Run the COUNT(*)
-- This will acquire the Sch-S lock, and we use HOLDLOCK to retain that lock.
-- This simulates a race condition where this query is running at the time the first rename occurs.
BEGIN TRANSACTION
SELECT COUNT(*) FROM Test WITH (HOLDLOCK)
GO

-- (3) In another window, run the block of code that performs the renames
-- This will be blocked, waiting for a Sch-M lock on "test" until we complete step 4 below
CREATE TABLE TMP_test(Id INT PRIMARY KEY)
INSERT TMP_test SELECT * FROM test
EXEC sp_rename 'test', 'test1'
EXEC sp_rename 'TMP_test', 'test'
EXEC sp_rename 'test1', 'TMP_test'
DROP TABLE TMP_test
GO

-- (4) Now, commit the original COUNT(*) and immediately fire off the query again
-- We use OPTION (RECOMPILE) to make sure we need to compile a new query plan
-- The COMMIT releases the Sch-S lock, allowing (3) to acquire the Sch-M lock
-- This batch will now be waiting for the Sch-S lock again, on the same object_id,
-- but that object_id will no longer point to the correct object by the time the lock
-- is acquired.
COMMIT
SELECT COUNT(*) FROM Test OPTION (RECOMPILE)
GO

Usando essas etapas, podemos nos aprofundar um pouco mais no que está causando o erro. Para isso, executei um rastreamento com os seguintes eventos em: SP:StmtStarting, SP:StmtCompleted, Lock:Acquired, Lock:Released.

O que descobri é que ocorre o seguinte, em ordem (mas omitindo alguns detalhes intermediários):

1. O primeiro COUNT(*) adquire um bloqueio Sch-S
2. O primeiro COUNT(*) adquire alguns bloqueios nas estatísticas (presumivelmente para criar um plano de consulta)
3. O primeiro COUNT(*) libera um bloqueio Sch-S (fim da compilação do plano)
4. O primeiro COUNT(*) adquire um bloqueio IS (início da execução) e é executado com sucesso
5. O sp_rename é executado até o ponto em que precisa receber um bloqueio Sch-M e, em seguida, é bloqueado pelo primeiro COUNT(*), que ainda não foi confirmado
6. O primeiro COUNT(*) é confirmado
7. O sp_renames adquire o bloqueio Sch-M
8. O segundo COUNT(*) solicita um bloqueio Sch-S e é adicionado à cadeia de bloqueio por trás do sp_rename
9. O sp_renames foi concluído (neste ponto, o segundo COUNT(*) está aguardando um bloqueio Sch-S para um object_id que não é mais o object_id correto)
10. O segundo COUNT(*) adquire o bloqueio Sch-S
11. O segundo COUNT(*) adquire um bloqueio Sch-S em sys.sysschobjs, presumivelmente como uma verificação de sanidade porque detectou (ou precisa confirmar) que algo está errado com o bloqueio Sch-S que foi concedido no object_id usado para ser "teste"
12. O erro 'Test' de nome de objeto inválido é lançado

No entanto, se uma sequência semelhante de eventos ocorrer quando uma compilação de plano não for necessária (como ao executar seu loop), o SQL Server será realmente inteligente o suficiente para detectar que o object_id foi alterado. Também rastreei esse caso e encontrei uma situação em que o segundo COUNT(*) executou a seguinte sequência

1. Adquire o bloqueio Sch-S (no object_id que costumava ser "teste")
2. Adquire um bloqueio Sch-S em sys.sysschobjs
3. Libera esses dois bloqueios
4. Adquire um bloqueio IS em um object_id diferente; o novo object_id de "teste"!
5. Executa com sucesso até a conclusão

Portanto, parece que essa lógica adaptativa está em vigor, como você hipotetizou. Mas parece que pode estar em vigor apenas para execução de consulta e não para compilação de consulta.

Conforme prometido, aqui está um trecho alternativo para a seção (3) que parece fornecer uma maneira de renomear tabelas que resolve o problema de simultaneidade (pelo menos na minha máquina!):

-- (3) In another window, run the block of code that performs the "renames"
-- Instead of sp_rename, we use ALTER TABLE...SWITCH
-- This appears to be a more robust way of performing the same logic
CREATE TABLE test1(Id INT PRIMARY KEY)
CREATE TABLE TMP_test(Id INT PRIMARY KEY)
INSERT TMP_test SELECT * FROM test
ALTER TABLE test SWITCH TO test1
ALTER TABLE TMP_test SWITCH TO test
ALTER TABLE test1 SWITCH TO TMP_test
DROP TABLE TMP_test
DROP TABLE test1
GO

Por fim, aqui está outro link que achei útil para entender como pensar sobre o bloqueio no contexto de tabelas cujos nomes estão mudando:

• http://www.sqlnotes.info/2011/12/02/sp_rename-causes-lock-leaking/