SP getting recompiled with "Statistics changed" reason even after disabling auto_stats for all tables involved

1 vote

1 answer

190 views

sql-server sql-server-2014 statistics recompile

                          I was troubleshooting a performance issue and I need an SP to stop getting recompiled because of auto update stats.

I don't want to disable Auto stats update on the entire database so I thought I could disable it on all the tables involved using sp_autostats proc.  But even after I disabled auto update stats on all the tables involved, it is still getting recompiled with the reason "Statistics changed".

I checked the last time stats was updated and also used auto_stats extended event to track if the stats were getting updated, but it is not. 

REPRO

    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

I suspect it might have something to do with stats on worktable created for the sort? if we comment out the order by clause in the SP the problem goes away.  Periodically calling sp_recompile also seems to reset whatever it is tracking that triggers recompile.

Any workaround for this and anyone else faced this issue?

                        

Asked by DMDM (1750 rep)

Jun 10, 2024, 10:07 AM
Last activity: Jun 13, 2024, 07:00 PM

SP getting recompiled with "Statistics changed" reason even after disabling auto_stats for all tables involved

Related Questions