我正在排除性能故障，我需要一个 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似乎还会重置触发重新编译的跟踪内容。
有什么解决方法吗？还有其他人遇到过这个问题吗？
