According to the docs:
Prevents SQL Server from automatically starting and recovering any database except the master database. If activities that require TempDB are initiated, then model is recovered and TempDB is created. Other databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work. Use for Move System Databases and Move User Databases.
Note: Do not use during normal operation.
Scope: global only
But it turns out it can do quite a bit of harm. It seems that many things stop working when it’s in use, though, including statistics getting automatically created.
Here’s a repro script:
DROP TABLE IF EXISTS dbo.stats_test, dbo.stats_test_3608; CREATE TABLE dbo.stats_test ( id int NOT NULL ); GO INSERT dbo.stats_test ( id ) VALUES ( 0 ); GO SELECT st.* FROM dbo.stats_test AS st WHERE st.id = 1; GO DBCC TRACEON(3608, -1); GO CREATE TABLE dbo.stats_test_3608 ( id int NOT NULL ); GO INSERT dbo.stats_test_3608 ( id ) VALUES ( 0 ); GO SELECT st.* FROM dbo.stats_test AS st WHERE st.id = 1; GO DBCC TRACEOFF(3608, -1); GO
And here are the results:
This post comes from finding the trace flag enabled at a client site, and nearly every query plan having warnings about columns with missing statistics.
Sometimes this happens when statistics exist for a single column, but multi-column statistics don’t.
In this case, disabling the trace flag fixed things.
Even forcing ghost record cleanup won’t work with this trace flag on.
DBCC TRACEOFF(3608, -1); GO INSERT dbo.stats_test WITH(TABLOCK) ( id ) SELECT m.message_id FROM sys.messages AS m; GO DELETE dbo.stats_test; GO DBCC FORCEGHOSTCLEANUP; GO SELECT record_count, ghost_record_count, version_ghost_record_count FROM sys.dm_db_index_physical_stats ( DB_ID(), OBJECT_ID('stats_test'), 0, 0, 'DETAILED' ); GO DBCC TRACEON(3608, -1); GO INSERT dbo.stats_test_3608 WITH(TABLOCK) ( id ) SELECT m.message_id FROM sys.messages AS m; GO DELETE dbo.stats_test_3608; GO DBCC FORCEGHOSTCLEANUP; GO SELECT record_count, ghost_record_count, version_ghost_record_count FROM sys.dm_db_index_physical_stats ( DB_ID(), OBJECT_ID('stats_test_3608'), 0, 0, 'DETAILED' ); GO DBCC TRACEOFF(3608, -1); GO
So, uh, if you see this enabled anywhere, you should disable it.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- SQL Server 2022 Parameter Sensitive Plan Optimization: A Missed Opportunity For PSP To Make A Query Go Faster
- What’s The Best Statistics Sample Rate For SQL Server?
- Creating An Agent Job To Update Statistics Using Ola Hallengren’s Scripts
- One Thing The “New” Cardinality Estimator Does Better In SQL Server
One thought on “Trace Flag 3608 Disables Automatic Statistics Creation And Ghost Record Cleanup In SQL Server”
Comments are closed.