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?