Por que uma reconstrução de índice requer um bloqueio Sch-M?

A documentação de Como funcionam as operações de índice online diz:

SCH-M(Modificação de esquema) se qualquer estrutura de origem (índice ou tabela) for descartada.*

_{* A operação de índice aguarda a conclusão de qualquer transação de atualização não confirmada antes de adquirir o bloqueio S ou o bloqueio SCH-M na tabela.}

Isso é especificamente para operações online. Se você está perguntando sobre operações offline, a lógica é a mesma: nada pode estar acessando uma estrutura que está prestes a ser descartada , nem mesmo sob Sch-Sapenas, ou para uso interno dentro de uma transação do sistema. Observe também o "(índice ou tabela)" lá, portanto, isso se aplica a todos os índices (incluindo clusters) e tabelas de heap.

Há algum tempo, a Sch-Mrestrição era aplicada ao criar uma visualização indexada. Isso foi apontado como desnecessário (link de conexão não está mais disponível) porque nenhuma estrutura estava sendo descartada, apenas criada, então o comportamento foi alterado (para apenas pegar Sch-Se Tab-S).

Também relacionado da documentação :

ALTER TABLEadquire um bloqueio de modificação de esquema ( SCH-M) na tabela para garantir que nenhuma outra conexão faça referência nem mesmo aos metadados da tabela durante a alteração...

A partir do mesmo link, o SQL Server 2012 introduziu a capacidade de adicionar uma NOT NULLcoluna sem demorar Sch-M, desde que o valor padrão seja uma constante de tempo de execução . Menciono isso porque mostra como o SQL Server evoluiu para reduzir o bloqueio desnecessariamente restritivo sempre que possível. Se você encontrar um caso específico em que considere Sch-Mrealmente desnecessário, poderá fazer a sugestão no site de comentários da Microsoft .

Além disso, em Unicórnios, arco-íris e operações de índice online , Paul Randal diz:

Quando a operação de índice for concluída, os índices novo e antigo estarão em passo de bloqueio no que diz respeito às atualizações. Um bloqueio de modificação de esquema (modo de bloqueio SCH_M) é necessário para concluir a operação. Você pode pensar nisso como um bloqueio super-table-X – é necessário aumentar o número da versão principal da tabela – nenhuma operação pode ser executada na tabela e nenhum plano pode ser compilado enquanto o bloqueio é mantido.

Links Relacionados:

• O bloqueio Sch-M é mau por Michael J Swart

Os bloqueios de modificação de esquema( Sch-M) são adquiridos quando ocorrem alterações de metadados .

No caso de index rebuild( onlineou offline) uma nova cópia de um indexé construída e a antiga indexé descartada. Antes de descartar um índice antigo, os metadados da tabela precisam ser atualizados.

Portanto, no estágio final da reconstrução do índice, o Sch-Mbloqueio é obtido para substituir uma referência de índice no arquivo metadata.

ATUALIZAR

Responda ao comentário

Adicionar (não reconstruir!) um novo índice não clusterizado online não requer Sch-M. – Paulo Branco

OK. Aqui está minha nova reprodução:

use tempdb;
go

create table dbo.t (id int);
insert into dbo.t values (1);
go

begin tran;
create index IX_t on dbo.t (id) with (online = on);

select l.request_mode, 
       l.resource_type, 
       l.resource_subtype, 
       l.resource_associated_entity_id, 
       object_name(p.object_id), 
       p.index_id
from sys.dm_tran_locks l 
     left join sys.partitions p 
        on p.hobt_id = l.resource_associated_entity_id
where l.request_session_id = @@spid and l.request_mode = N'Sch-M';

commit;
go