Trace Flag 3608 Disables Automatic Statistics Creation And Ghost Record Cleanup In SQL Server

Short Bad

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:

End Times

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.

Ghost Busters

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

Turn Off

So, uh, if you see this enabled anywhere, you should disable it.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount 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.