Query plan do not get invalidated after update statistics

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?

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"

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.

StackExchange Database Administrators Q#205833, answer score: 7