Início / dba / Perguntas / 340124
Accepted
DMDM
Asked: 2024-06-10 18:07:29 +0800 CST

SP sendo recompilado com o motivo "Estatísticas alteradas" mesmo após desabilitar auto_stats para todas as tabelas envolvidas

  • 772

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?

sql-server

1 respostas

  1. Best Answer

    A recompilação não está sendo causada por uma alteração nas estatísticas. O código de razão é enganoso*.

    O SQL Server não mantém estatísticas sobre tabelas de trabalho de classificação.

    Citando problemas de compilação em lote, recompilação e planejamento de cache no SQL Server 2005 :

    Se uma tabela ou visão indexada T não tiver nenhuma estatística sobre ela, ou nenhuma das estatísticas existentes sobre T for considerada "interessante" durante uma compilação de consulta, o seguinte teste de cruzamento de limite, baseado puramente na cardinalidade de T, ainda será executado:

    ABS(card(current) – card(snapshot))) >= RT

    card(current)denota o número de linhas em T no momento e card(snapshot)denota a contagem de linhas quando o plano de consulta foi compilado pela última vez.

    Em outras palavras, a instrução no procedimento está sendo recompilada porque uma tabela referenciada ultrapassou o Limite de Recompilação associado ao número total de linhas na tabela. Nenhuma estatística individual contribuiu para a decisão.

    Você pode evitar a recompilação adicionando uma KEEPFIXED PLANdica de consulta às instruções do procedimento.

    Força o Query Optimizer a não recompilar uma consulta devido a alterações nas estatísticas. A especificação KEEPFIXED PLANgarante que uma consulta seja recompilada somente se o esquema das tabelas subjacentes for alterado ou se sp_recompilefor executada nessas tabelas.

    Com essa abordagem, você pode direcionar diretamente as instruções que não deseja recompilar. A desativação das atualizações automáticas de estatísticas não é mais necessária, via STATISTICS_NORECOMPUTEou sp_autostats.

    Reprodução

    Este script reproduz o problema no SQL Server 2022 com apenas uma única janela de consulta necessária.

    Configurar

    DROP PROCEDURE IF EXISTS dbo.sp1;
DROP TABLE IF EXISTS dbo.test;
GO
CREATE TABLE dbo.Test
(
    a integer IDENTITY NOT NULL,
    b integer NULL,
    c integer NULL,

    PRIMARY KEY CLUSTERED (a)
        WITH (STATISTICS_NORECOMPUTE = ON)
);
GO
CREATE NONCLUSTERED INDEX idx
ON dbo.test (c)
WITH (STATISTICS_NORECOMPUTE = ON);
GO
CREATE PROCEDURE dbo.sp1
    @c integer
AS
SELECT 
    T.* 
FROM dbo.test AS T
WHERE 
    T.c = @c
--OPTION (KEEPFIXED PLAN)
;
GO
EXECUTE sys.sp_autostats
    @tblname = N'dbo.test',
    @flagc = 'off';

    Teste

    SET NOCOUNT ON;
GO
DBCC TRACEON (205, 3604) 
    WITH NO_INFOMSGS;

EXECUTE dbo.sp1 @c = 1;

INSERT dbo.Test (b, c) VALUES (1, 1);

-- Recompile due to going from zero rows to one
EXECUTE dbo.sp1 @c = 1;

INSERT dbo.Test (b, c)
SELECT
    SV.number,
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 500;

-- Recompile due to cardinality change >= 500
EXECUTE dbo.sp1 @c = 1;

INSERT dbo.Test (b, c)
SELECT
    SV.number,
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 600;

-- Recompile due to cardinality change >= 500 + 20%
EXECUTE dbo.sp1 @c = 1;

DBCC TRACEOFF (205, 3604) 
    WITH NO_INFOMSGS;
GO
DROP PROCEDURE dbo.sp1;
DROP TABLE dbo.test;

    Saída

    Exemplo de saída sem a KEEPFIXED PLANdica:

    Data related recompile(card change): Tbl Dbid: 8, Objid: 1652200936, 
current rowcount: 1, compile time rowcount: 0, threshold: 500 (small table)

Data related recompile(card change): Tbl Dbid: 8, Objid: 1652200936, 
current rowcount: 501, compile time rowcount: 1, threshold: 500 (small table)

Data related recompile(card change): Tbl Dbid: 8, Objid: 1652200936, 
current rowcount: 1101, compile time rowcount: 501, threshold: 600 (sublinear heuristic)

    Com a dica de consulta, não há saída, pois não ocorrem recompilações.

    * Houve uma alteração nas informações estatísticas disponíveis para o otimizador de consulta, mas não uma alteração em um objeto estatístico. É o mais próximo dos motivos disponíveis para escolher.

    • 4

relate perguntas