2

I'm running some tests to find out when query plans get invalidated after an update statistics. The machine I'm using for testing is a SQL Server Developer 2016 SP1 CU7.

I found an article on BrentOzar.com how I can trace the recompiles but I never get a recompile. Auto Update Statistics and Auto Create Statistics are both enabled

This is my test:

/* Create a table and put over 1k rows in it (to get past the 500 row stats threshold) */
CREATE TABLE dbo.MyTable (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, StringField VARCHAR(50));
GO
INSERT INTO dbo.MyTable(StringField) 
SELECT 'Stuff' FROM sys.all_objects;
GO

/* Start a trace monitoring recompiles */
exec sp_BlitzTrace @Action='start', @TargetPath='c:\temp\', @SessionId=@@SPID, @TraceRecompiles=1;
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere';
GO
/* Increment the row mod counters to encourage a stats update */
BEGIN TRAN
DELETE dbo.MyTable;
GO
ROLLBACK
GO
UPDATE STATISTICS dbo.MyTable WITH fullscan
GO

/* We don't strictly need to wait, but makes different executions easier to see: */
WAITFOR DELAY '00:00:10';
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere';
GO
EXEC sp_BlitzTrace @Action='stop'
GO
EXEC sp_BlitzTrace @Action='read'
GO

As I understand it, the plan should get invalidated when you call UPDATE STATISTICS WITH FULLSCAN and there are rows changed. Or am I missing something?

  • I rewrote your sample code to make it runnable by others - you hadn't included the definitions for MyTable, or how many rows that were in it. – Brent Ozar May 4 '18 at 13:02
6

Your original sample code wasn't re-runnable (the table creation wasn't in there, or the population of rows) so I rewrote it to flesh it out and make it re-runnable.

The problem is that your query is trivially simple: it doesn't matter whether stats are updated or not, it's going to produce the same plan. This tweak will make your SELECT query bypass trivial optimization:

SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere' AND 1 = (SELECT 1);

Try the test again with that:

/* Start a trace monitoring recompiles */
exec sp_BlitzTrace @Action='start', @TargetPath='c:\temp\', @SessionId=@@SPID, @TraceRecompiles=1;
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere' AND 1 = (SELECT 1);
GO
/* Increment the row mod counters to encourage a stats update */
BEGIN TRAN
DELETE dbo.MyTable;
GO
ROLLBACK
GO
UPDATE STATISTICS dbo.MyTable WITH fullscan
GO

/* We don't strictly need to wait, but makes different executions easier to see: */
WAITFOR DELAY '00:00:10';
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere' AND 1 = (SELECT 1);
GO
EXEC sp_BlitzTrace @Action='stop'
GO
EXEC sp_BlitzTrace @Action='read'
GO

And now you get recompiles - note the third line that shows recompile_cause = "Statistics changed"

Recompiles

This is one of the reasons that sp_BlitzCache (disclaimer: I'm a very small author on that open source script) shows warnings for trivial plans - you don't get all of the cool stuff you get on fully optimized plans, like missing index recommendations.

I'm certainly not saying that adding "WHERE 1 = (SELECT 1)" is a good idea in your queries - just saying that when building repro scenarios, you gotta watch out for trivial queries. To know if your query is trivially simple, view the execution plan, right-click on the root node (like the SELECT), click Properties, and look at the Optimization Level. If it says Trivial, you're not getting the full genius of those folks at Microsoft.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.