主页 / dba / 问题 / 340124
Accepted
DMDM
Asked: 2024-06-10 18:07:29 +0800 CST

即使禁用了所有相关表的 auto_stats，SP 仍会以“统计信息已更改”原因重新编译

  • 772

我正在排除性能故障，我需要一个 SP 来停止因为自动更新统计数据而重新编译。

我不想禁用整个数据库的自动统计信息更新，所以我认为可以使用sp_autostatsproc 禁用所有相关表的自动统计信息更新。但即使在我禁用所有相关表的自动统计信息更新后，它仍然会重新编译，原因是“统计信息已更改”。

我检查了上次更新统计数据的时间，并使用了 auto_stats 扩展事件来跟踪统计数据是否已更新，但事实并非如此。

瑞普罗

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

我怀疑这可能与为排序创建的工作表上的统计信息有关？如果我们注释掉 SP 中的 order by 子句，问题就会消失。定期调用sp_recompile似乎还会重置触发重新编译的跟踪内容。

有什么解决方法吗？还有其他人遇到过这个问题吗？

sql-server

1 个回答

  1. Best Answer

    重新编译不是由统计数据的变化引起的。原因代码具有误导性*。

    SQL Server 不维护排序工作表的统计信息。

    引用SQL Server 2005 中的批处理编译、重新编译和计划缓存问题：

    如果表或索引视图 T 没有统计信息，或者在查询编译期间没有将 T 上的现有统计信息视为“有趣的”，则仍会执行以下纯粹基于 T 的基数的阈值交叉测试：

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

    card(current)表示当前T中的行数，card(snapshot)表示上次编译查询计划时的行数。

    换句话说，过程中的语句正在重新编译，因为引用的表已超过与表中总行数相关的重新编译阈值。没有任何单个统计数据促成了这一决定。

    您可以通过KEEPFIXED PLAN向过程语句添加查询提示来避免重新编译。

    强制查询优化器不要因为统计信息发生变化而重新编译查询。指定此选项KEEPFIXED PLAN可确保仅当基础表的架构发生变化或sp_recompile针对这些表运行时才重新编译查询。

    使用这种方法，您可以直接定位您不想重新编译的语句。不再需要通过STATISTICS_NORECOMPUTE或禁用自动统计信息更新sp_autostats。

    复制

    该脚本仅需一个查询窗口即可重现 SQL Server 2022 上的问题。

    设置

    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';

    测试

    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;

    输出

    无提示的示例输出KEEPFIXED PLAN：

    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)

    使用查询提示时，没有输出，因为没有发生重新编译。

    * 查询优化器可用的统计信息发生了变化，但统计对象没有变化。这是最接近的选择原因。

    • 4

相关问题