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:

The statistics that exist now:

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

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

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.

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

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.

Ads by Google, Paying the Rent: