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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
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:
|
1 2 3 4 5 6 |
+------------+--------------------+-----------------------+-----------+------+-------------+-------------+ | SCHEMANAME | OBJECTNAME | STATSNAME | STATSDATE | ROWS | ROWSSAMPLED | MOD COUNTER | +------------+--------------------+-----------------------+-----------+------+-------------+-------------+ | dbo | ReadOnlyTestTable | PK_ReadOnlyTestTable | NULL | NULL | NULL | NULL | | dbo | ReadOnlyTestTable2 | PK_ReadOnlyTestTable2 | NULL | NULL | NULL | NULL | +------------+--------------------+-----------------------+-----------+------+-------------+-------------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:
|
1 2 3 4 5 6 |
+------------+--------------------+-----------------------+-----------+------+-------------+-------------+ | 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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.
|
1 2 3 |
SELECT * FROM dbo.StatsDetails; |
The above query shows that statistics objects have just been created:
|
1 2 3 4 5 6 7 8 |
+------------+--------------------+---------------------------+-----------------------------+------+-------------+-------------+ | 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.