Degradação de desempenho no SQL Server com conformidade com critérios comuns habilitada

Sintomas

Quando você tem a opção de configuração de conformidade de critérios comuns habilitada no SQL Server, pode notar uma degradação no desempenho do SQL Server.

Conformidade com critérios comuns

Depois de habilitar a opção de conformidade de critérios comuns no SQL Server, você pode enfrentar a espera de bloqueio e a sobrecarga de auditoria de conformidade de critérios comuns quando várias autenticações simultâneas usam o mesmo logon.

Depois de habilitar essa opção, você pode experimentar uma degradação lenta no desempenho. Além disso, você pode ver altas LCK_M_SCH_M esperas nas estatísticas de desempenho. LCK_M_SCH_M é um bloqueio de modificação de esquema que impede o acesso a uma tabela enquanto ocorre uma operação DDL (Data Definition Language). Você também encontrará registros de processo bloqueados em que uma LOGIN_STATS tabela no banco de dados mestre está aguardando por um longo tempo. Esta tabela é usada para manter as estatísticas de login. Quando há muitos logins e logouts, pode haver contenção nesta tabela.

Para entender por que ocorre um evento de LCK_M_SCH_M espera, execute os seguintes comandos:

CREATE EVENT SESSION [CreateDumpOnLCKMSCHM] ON SERVER
ADD EVENT sqlos.wait_info(
    ACTION(package0.callstack,sqlserver.create_dump_all_threads)
    WHERE (([opcode]=('Begin')) AND ([wait_type]=('LCK_M_SCH_M'))))
ADD TARGET package0.event_file(SET filename=N'CreateDumpOnLCKMSCHM',max_file_size=(128))
GO

ALTER EVENT SESSION [TestCreateDump] ON SERVER
STATE = START
GO

WHILE ((SELECT COUNT(1) FROM sys.dm_server_memory_dumps) < 5)
BEGIN
       WAITFOR DELAY '00:00:00.100'

END

ALTER EVENT SESSION [CreateDumpOnLCKMSCHM] ON SERVER
STATE = STOP

Quando você habilita a conformidade com critérios comuns, uma medida de segurança chamada RIP (Proteção de Informações Residual) também é habilitada. O RIP requer alocação de memória para ser substituído por um padrão conhecido de bits antes que a memória seja realocada para um novo recurso. Portanto, com muitos logins e logouts, há uma degradação do desempenho na memória porque a substituição da alocação de memória precisa ser feita.

Solução alternativa

Se a conformidade com critérios comuns não for um requisito estrito, considere desabilitá-la para reduzir a sobrecarga de auditoria e melhorar o desempenho. Para desativar a opção, execute os seguintes comandos:

sp_configure 'common criteria compliance enabled', 0
GO
RECONFIGURE
GO

Depois de modificar a configuração, reinicie os serviços do SQL Server para que as alterações entrem em vigor.

Mais informações

Configuração de servidor habilitada para conformidade com critérios comuns