Earlier this week, we discussed the topic of statistics for read-only databases being stored in TempDB. Today, we will address a question raised by one of our readers, Ayman El-Ghazali, regarding the fate of statistics when a database is switched to read-only mode.

Ayman asked, “What happens to the statistics if you create a database, create some statistics in it, and then later decide to switch it to read-only? How are those statistics updated if they exist inside the read-only database?”

The answer is quite straightforward. Statistics that were created before the database was marked as read-only will remain in the same database and will be considered permanent statistics. However, any new statistics created after the database is marked as read-only will be stored in TempDB.

Let’s validate this with a demonstration:

-- Create Database
CREATE DATABASE [Read-Only]
GO

-- Use Database
USE [Read-Only]
GO

-- Create Table
CREATE TABLE [Shift](
[ShiftID] [tinyint] NOT NULL,
[Name] VARCHAR(100) NOT NULL,
[StartTime] [time](7) NOT NULL,
[EndTime] [time](7) NOT NULL,
[ModifiedDate] [datetime] NOT NULL
)
GO

-- Insert Table
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate])
VALUES (1, N'Day', GETDATE(), GETDATE(), GETDATE())
GO
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate])
VALUES (2, N'Evening', GETDATE(), GETDATE(), GETDATE())
GO
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate])
VALUES (3, N'Night', GETDATE(), GETDATE(), GETDATE())
GO

-- Simple Select Statement to build statistics
-- BEFORE Database is Read Only
SELECT *
FROM [Shift]
WHERE Name = 'Day'
GO

-- Check if there are temporary statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, name, auto_created,
user_created, is_temporary
FROM sys.stats
WHERE OBJECT_NAME(OBJECT_ID) = 'Shift'
GO

-- Make Database Read Only
ALTER DATABASE [Read-Only] SET READ_ONLY
GO

-- Check if there are temporary statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, name, auto_created,
user_created, is_temporary
FROM sys.stats
WHERE OBJECT_NAME(OBJECT_ID) = 'Shift'
GO

-- Simple Select Statement to build statistics
-- AFTER Database is Read Only
SELECT *
FROM [Shift]
WHERE ShiftID = 1
GO

-- Check if there are temporary statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, name, auto_created,
user_created, is_temporary
FROM sys.stats
WHERE OBJECT_NAME(OBJECT_ID) = 'Shift'
GO

-- Clean up
USE MASTER
GO
DROP DATABASE [Read-Only]
GO

Let’s explain the result set with comments:

• Before marking the database as read-only, we create statistics on the “Shift” table.
• We check the status of the statistics location and find that they are not temporary.
• We then mark the database as read-only.
• Upon checking the statistics location again, we find that the statistics created before the database was marked as read-only are still in the same database and are considered permanent.
• We perform a select statement on the “Shift” table after marking the database as read-only.
• Finally, we check the statistics location once more and find that the new statistics created after the database became read-only are stored in TempDB.

Please note that this behavior applies specifically to SQL Server 2012.

We hope you found this information interesting and helpful. If you have any further questions or topics you would like us to cover, please let us know!