Eu estava solucionando um problema de desempenho e preciso de um SP para parar de ser recompilado devido às estatísticas de atualização automática.
Não quero desabilitar a atualização automática de estatísticas em todo o banco de dados, então pensei em desativá-la em todas as tabelas envolvidas usando sp_autostatsproc. Mas mesmo depois de desabilitar as estatísticas de atualização automática em todas as tabelas envolvidas, ela ainda está sendo recompilada com o motivo "Estatísticas alteradas".
Verifiquei a última vez que as estatísticas foram atualizadas e também usei o evento estendido auto_stats para rastrear se as estatísticas estavam sendo atualizadas, mas não está.
REPRODUÇÃO
USE [test]
GO
CREATE TABLE [dbo].[test](
[a] [int] IDENTITY(1,1) NOT NULL, [b] [int] NULL, [c] [int] NULL,
PRIMARY KEY CLUSTERED
(
[a] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx] ON [dbo].[test]
(
[c] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE proc [dbo].[sp1] @a int
as
select * from test where c=@a order by b
GO
sp_autostats 'test','off'
go
--creating lots of changes.
--Session 1
set nocount on
while 1=1
begin
delete top(1) from test
end
--Session 2
set nocount on
while 1=1
insert into test select 1, FLOOR(RAND() * (10 - 1 + 1)) + 1;
--powershell 3. Calling the SP in a loop
while ($true) {
$sqlQuery = "EXEC test.dbo.sp1 @a=100;"
Invoke-Sqlcmd -ServerInstance 'sql1\s14' -Database 'test' -Query $sqlQuery -Encrypt Optional
Start-Sleep -Milliseconds 500
}
--Monitor sql_statement_recompile extended event. Should see a recompile event in a couple minutes with "Statistics changed" as recompile_cause
CREATE EVENT SESSION [test] ON SERVER
ADD EVENT sqlserver.sql_statement_recompile(SET collect_object_name=(1),collect_statement=(1)
ACTION(sqlserver.server_principal_name,sqlserver.sql_text))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
Suspeito que possa ter algo a ver com estatísticas na mesa de trabalho criada para esse tipo. se comentarmos a cláusula order by no SP o problema desaparece. Chamar periodicamente sp_recompiletambém parece redefinir o que quer que esteja rastreando e acionando a recompilação.
Alguma solução alternativa para isso e alguém mais enfrentou esse problema?
