What Kind Of Statistics Updates Invalidate Plans?

Last Updated 3 years ago

Basics

If you update statistics when the underlying objects haven’t been modified, plans won’t be invalidated.

That makes total sense if your statistics update doesn’t also change the statistics. But what about when they do?

That seemed like a no-brainer to me. What if you used a higher sampling percentage and got a more accurate (or just different) histogram?

It turns out that doesn’t always trigger a recompile. At least not according to Extended Events.

Setup

This is the XE session I’m using. The settings aren’t very good for tracking recompiles generally in production.

You wouldn’t wanna use no event loss and a 1 second dispatch latency.

Just an FYI, copy and paste cowboys and girls.

CREATE EVENT SESSION recompile
    ON SERVER
    ADD EVENT sqlserver.sql_statement_recompile
    ( SET collect_object_name = ( 1 ), collect_statement = ( 1 )
     WHERE ( sqlserver.session_id = ( 59 )))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\recompile' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = NO_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 1 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = OFF,
           STARTUP_STATE = OFF );
GO

CREATE EVENT SESSION recompile

ON SERVER

ADD EVENT sqlserver.sql_statement_recompile

( SET collect_object_name = ( 1 ), collect_statement = ( 1 )

WHERE ( sqlserver.session_id = ( 59 )))

ADD TARGET package0.event_file

( SET filename = N'c:\temp\recompile' )

WITH ( MAX_MEMORY = 4096KB,

EVENT_RETENTION_MODE = NO_EVENT_LOSS,

MAX_DISPATCH_LATENCY = 1 SECONDS,

MAX_EVENT_SIZE = 0KB,

MEMORY_PARTITION_MODE = NONE,

TRACK_CAUSALITY = OFF,

STARTUP_STATE = OFF );

Here’s the table I’m using, which is simple enough.

CREATE TABLE dbo.stats_test (id INT, filler_bunny VARCHAR(100));

INSERT dbo.stats_test ( id, filler_bunny )
SELECT x.n, REPLICATE(ASCII(x.n), 50)
FROM (
	SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID)
	FROM sys.messages AS m
	CROSS JOIN sys.messages AS m2
) AS x (n);

CREATE TABLE dbo.stats_test (id INT, filler_bunny VARCHAR(100));

INSERT dbo.stats_test ( id, filler_bunny )

SELECT x.n, REPLICATE(ASCII(x.n), 50)

FROM (

SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID)

FROM sys.messages AS m

CROSS JOIN sys.messages AS m2

) AS x (n);

First, I’m going to create some statistics with a really low sampling rate.

CREATE STATISTICS sst ON dbo.stats_test(id) WITH SAMPLE 1 PERCENT;

1	CREATE STATISTICS sst ON dbo.stats_test(id) WITH SAMPLE 1 PERCENT;

It’s going to be the only stats object on the table.

EXEC sp_helpstats @objname = N'dbo.stats_test', @results = 'ALL';

1	EXEC sp_helpstats @objname = N'dbo.stats_test', @results = 'ALL';

If I run this query, the plan will compile.

SELECT COUNT(*)
FROM dbo.stats_test AS st
WHERE st.id > 10000;

SELECT COUNT(*)

FROM dbo.stats_test AS st

WHERE st.id > 10000;

Then update stats with FULLSCAN and re-run the query above…

UPDATE STATISTICS dbo.stats_test WITH FULLSCAN;

1	UPDATE STATISTICS dbo.stats_test WITH FULLSCAN;

And my Extended Event session is empty. Unless I create stats on a column my query isn’t touching.

CREATE STATISTICS sst2 ON dbo.stats_test(filler_bunny) WITH SAMPLE 1 PERCENT;

1	CREATE STATISTICS sst2 ON dbo.stats_test(filler_bunny) WITH SAMPLE 1 PERCENT;

Because I know you’re going to ask — yes, the histogram is different.

Apparently this doesn’t change SQL Server’s view of things.

When Does It Change?

I’m starting to really hate trivial plans (more). If I change my query to this:

SELECT COUNT(*)
FROM dbo.stats_test AS st
WHERE st.id > 10000
AND 1 = (SELECT 1);

SELECT COUNT(*)

FROM dbo.stats_test AS st

WHERE st.id > 10000

AND 1 = (SELECT 1);

Updating the statistics with FULLSCAN, after creating the statistics and running the query, a recompile is triggered.

Stored Procedures, Too

It’s not just the Trivial Plan, it’s also the Simple Parameterization, which means…

CREATE PROCEDURE dbo.test (@i INT)
AS
BEGIN
SELECT COUNT(*)
FROM dbo.stats_test AS st
WHERE st.id > @i
AND 1 = (SELECT 1);
END

CREATE PROCEDURE dbo.test (@i INT)

BEGIN

SELECT COUNT(*)

FROM dbo.stats_test AS st

WHERE st.id > @i

AND 1 = (SELECT 1);

END

Even with a stats update using FULLSCAN, this won’t recompile.

Unlike the ad hoc query, this won’t recompile if I create an unrelated statistics object.

Using a more complicated example in Stack Overflow results in the same thing.

CREATE PROCEDURE dbo.stack_test (@i INT)
AS
BEGIN

SELECT COUNT(*)
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
AND u.Reputation = @i

END

EXEC dbo.stack_test @i = 1

UPDATE STATISTICS dbo.Users WITH FULLSCAN;
UPDATE STATISTICS dbo.Posts WITH FULLSCAN;

CREATE PROCEDURE dbo.stack_test (@i INT)

BEGIN

SELECT COUNT(*)

FROM dbo.Users AS u

JOIN dbo.Posts AS p

ON p.OwnerUserId = u.Id

AND u.Reputation = @i

END

EXEC dbo.stack_test @i = 1

UPDATE STATISTICS dbo.Users WITH FULLSCAN;

UPDATE STATISTICS dbo.Posts WITH FULLSCAN;

What Does This Mean For You?

When you update statistics and data hasn’t changed, your plans won’t recompile. This is sensible.

When you update statistics and change your histograms, your plans may not recompile if they’re trivial and simple parameterized, or parameterized in a stored procedure.

This is perhaps less sensible, if you were counting on stats updates to trigger a recompilation because you’re trying to fix parameter sniffing, or another plan quality issue.

Thanks for reading!

What’s Different About SQL Server in Cloud VMs?

Free SQL Server Training Next Week at GroupBy

2 Comments. Leave new

Rob Mark
August 30, 2018 1:00 pm

Sounds like they need to add a RECOMPILE option to the UPDATE STATISTICS to cause plans to recompile.

Reply
Henrik Staun Poulsen
August 31, 2018 1:54 am

I think this explains some of my problems on a big database. I ended up using OPTION (RECOMPILE) which is less than optimal on most systems. I also used EXEC SP_Recompile ‘object’ to ensure that I would get a refresh.
Thank you very much for blogging about this.

Reply