Can statistics be updated for objects in read-only filegroups?

DBA.StackExchange.com has an interesting question about the benefits of filegroups and setting filegroups to read-only which I recently answered. I made several assumptions about read-only filegroups that turned out to be incorrect, one of which I explore in this post.

I assumed statistics would not be automatically created or updated for tables residing on read-only filegroups. I was wrong.

The following test-bed code proves that statistics continue to be maintained since the statistics objects are actually created in the [PRIMARY] filegroup, as values in the sys.sysobjvalues system table. Aaron Bertrand shows the proof that stats are created in PRIMARY in this excellent answer.

To prove that statistics for objects stored on read-only filegroups can be updated, I wrote this simple test bed:

USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'ReadOnlyTest')
DROP DATABASE ReadOnlyTest;
GO
CREATE DATABASE ReadOnlyTest;
GO
ALTER DATABASE ReadOnlyTest SET RECOVERY SIMPLE;
/*
    Backup the database to NUL: to ensure we are in simple recovery.
*/
BACKUP DATABASE ReadOnlyTest TO DISK = 'NUL:';
GO
ALTER DATABASE ReadOnlyTest 
ADD FILEGROUP ReadOnlyFilegroup;

ALTER DATABASE ReadOnlyTest 
ADD FILE (
	NAME = 'ReadOnlyTest_File1'
	, FILENAME = 'C:\Data\ReadOnlyTest_File1.mdf'
	) TO FILEGROUP ReadOnlyFilegroup;
GO

/*
	Turn auto_update and auto_create statistics off to ensure we 
	don't inadvertantly create stats objects prior to making the 
	filegroup readonly
*/
ALTER DATABASE ReadOnlyTest SET AUTO_UPDATE_STATISTICS OFF;
ALTER DATABASE ReadOnlyTest SET AUTO_CREATE_STATISTICS OFF;
GO

USE ReadOnlyTest;
GO
/*
    Create a view to show details about statistics objects, such as 
    when they were created/last updated, the number of rows sampled,
    the number of rows in the underlying object that have been modified
    since statistics were last updated, etc.
*/
CREATE VIEW dbo.StatsDetails AS
WITH StatsDetails AS
(
	SELECT ObjectID = o.object_id
		, SchemaName = sch.name
		, ObjectName = o.name
		, StatsName = st.name
		, StatsID = st.stats_id
		, StatsDate = dsp.last_updated
		, Rows = dsp.rows
		, RowsSampled = dsp.rows_sampled
		, ModificationCounter = dsp.modification_counter 
		, UpdateStatement = 'UPDATE STATISTICS ' + quotename(sch.name) 
			+ '.' + quotename(o.name) 
			+ '(' + quotename(st.name) + ') WITH FULLSCAN' 
			+ CASE WHEN st.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';'
	FROM sys.schemas sch 
		INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
		INNER JOIN sys.stats st ON o.object_id = st.object_id
	OUTER APPLY sys.dm_db_stats_properties(o.object_id, st.stats_id) dsp
	WHERE o.is_ms_shipped = 0
) 
SELECT sd.ObjectID
	, sd.StatsID
	, sd.SchemaName
	, sd.ObjectName
	, sd.StatsName
	, sd.UpdateStatement
	, sd.StatsDate
	, sd.Rows
	, sd.RowsSampled
	, sd.ModificationCounter
FROM StatsDetails sd;
GO

/*
    Create a couple of test tables
*/
CREATE TABLE dbo.ReadOnlyTestTable
(
	ID INT NOT NULL
		CONSTRAINT PK_ReadOnlyTestTable
		PRIMARY KEY CLUSTERED
		IDENTITY(1,1)
	, SomeVar VARCHAR(100) NOT NULL
) ON ReadOnlyFileGroup;
CREATE TABLE dbo.ReadOnlyTestTable2
(
	ID INT NOT NULL
		CONSTRAINT PK_ReadOnlyTestTable2
		PRIMARY KEY CLUSTERED
		IDENTITY(1,1)
	, SomeVar VARCHAR(100) NOT NULL
) ON ReadOnlyFileGroup;
GO

/*
    Show the existing statistics objects in the database
    The only two that exist are for the primary key,
    later we'll have SQL Server automatically created
    statistics on the SomeVar column, as a result of 
    simply querying it once the filegroup is marked as
    read-only.
*/
SELECT * FROM dbo.StatsDetails;
GO

The statistics that exist now:

+------------+--------------------+-----------------------+-----------+------+-------------+-------------+
| SCHEMANAME |     OBJECTNAME     |       STATSNAME       | STATSDATE | ROWS | ROWSSAMPLED | MOD COUNTER |
+------------+--------------------+-----------------------+-----------+------+-------------+-------------+
| dbo        | ReadOnlyTestTable  | PK_ReadOnlyTestTable  | NULL      | NULL | NULL        | NULL        |
| dbo        | ReadOnlyTestTable2 | PK_ReadOnlyTestTable2 | NULL      | NULL | NULL        | NULL        |
+------------+--------------------+-----------------------+-----------+------+-------------+-------------+

INSERT INTO dbo.ReadOnlyTestTable (SomeVar)
SELECT s1.name + s2.name
FROM sys.objects s1
	, sys.objects s2;
INSERT INTO dbo.ReadOnlyTestTable2 (SomeVar)
SELECT TOP(100) s1.name + s2.name
FROM sys.objects s1
	, sys.objects s2;
GO
ALTER DATABASE ReadOnlyTest SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE ReadOnlyTest SET AUTO_CREATE_STATISTICS ON;
GO
SELECT * FROM dbo.StatsDetails;

Again, the select results showing no stats exist, other than the two primary keys:

+------------+--------------------+-----------------------+-----------+------+-------------+-------------+
| SCHEMANAME |     OBJECTNAME     |       STATSNAME       | STATSDATE | ROWS | ROWSSAMPLED | MOD COUNTER |
+------------+--------------------+-----------------------+-----------+------+-------------+-------------+
| dbo        | ReadOnlyTestTable  | PK_ReadOnlyTestTable  | NULL      | NULL | NULL        | NULL        |
| dbo        | ReadOnlyTestTable2 | PK_ReadOnlyTestTable2 | NULL      | NULL | NULL        | NULL        |
+------------+--------------------+-----------------------+-----------+------+-------------+-------------+

Here, we’ll set the filegroup to read-only.


USE master;
GO
ALTER DATABASE ReadOnlyTest 
MODIFY FILEGROUP ReadOnlyFilegroup READONLY;
GO
USE ReadOnlyTest;
GO
/*
	This will fail since the ReadOnlyFileGroup is READ ONLY
*/
CREATE TABLE dbo.ReadOnlyTestTable3
(
	ID INT NOT NULL
		CONSTRAINT PK_ReadOnlyTestTable3
		PRIMARY KEY CLUSTERED
		IDENTITY(1,1)
	, SomeVar VARCHAR(100) NOT NULL
) ON ReadOnlyFileGroup;
GO
/*
    Running this will cause SQL Server to automatically create
    statistics on the `SomeVar` columns to enable an efficient
    query plan.
*/
DECLARE @count INT;
SELECT @count = COUNT(1)
FROM dbo.ReadOnlyTestTable rott
	INNER JOIN dbo.ReadOnlyTestTable2 rott2
		ON rott.SomeVar = rott2.SomeVar;
GO

The attempt to create the dbo.ReadOnlyTestTable3 table above results in the following error, proving that the filegroup is in fact read-only:

Msg 1924, Level 16, State 2, Line 4
Filegroup ‘ReadOnlyFilegroup’ is read-only.
Msg 1750, Level 16, State 0, Line 4
Could not create constraint. See previous errors.


SELECT * FROM dbo.StatsDetails;

The above query shows that statistics objects have just been created:

+------------+--------------------+---------------------------+-----------------------------+------+-------------+-------------+
| SCHEMANAME |     OBJECTNAME     |         STATSNAME         |          STATSDATE          | ROWS | ROWSSAMPLED | MOD COUNTER |
+------------+--------------------+---------------------------+-----------------------------+------+-------------+-------------+
| dbo        | ReadOnlyTestTable  | PK_ReadOnlyTestTable      | NULL                        | NULL | NULL        | NULL        |
| dbo        | ReadOnlyTestTable  | _WA_Sys_00000002_0F975522 | 2016-04-14 10:27:22.1000000 | 6561 | 6561        | 0           |
| dbo        | ReadOnlyTestTable2 | PK_ReadOnlyTestTable2     | NULL                        | NULL | NULL        | NULL        |
| dbo        | ReadOnlyTestTable2 | _WA_Sys_00000002_117F9D94 | 2016-04-14 10:27:22.0830000 | 100  | 100         | 0           |
+------------+--------------------+---------------------------+-----------------------------+------+-------------+-------------+

In the contrived example above, having SQL Server automatically create the statistics is actually A Good Thing™ since it allows the query optimizer to pick the best plan it can. However, if you had a billion-row-table you may want to ensure no stats objects are ever created or updated, since that action may have terrible negative consequences for performance. In this case, you might consider permanently turning off AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS. Of course, if you do that, you will need to manage the statistics creation and update process yourself.