SQL SERVER – Location of Statistics Before and After Database is Marked as a Read-Only

December 2, 2013

Earlier last week I wrote a blog post about Statistics for Read-Only Database are in TempDB, I received a very interesting comment from Ayman El-Ghazali on the same blog post. Let me reproduce the same comment over here.

“Nice post. What if you create a database, create some statistics in there, then later decide to switch it to Read-Only? How are those statistics updated if they exist inside the Read-Only DB? From your post I understood that all new statistics created will be in TempDB but what about the older stuff that resides in the DB that was switched to Read-Only?” (Original Comment)

In simple words what will happen to the statistics which were created before the database changed status to Read-Only.

Here is the answer – the statistics which were created before the database was marked as a read-only will still stay in the same database and it will be permanent statistics. However, all the new statistics which we will be creating after the database is marked as a read-only are created in the TempDB.

Let us validate the same with the help of demonstration:

In following script below we are creating the statistics before and after the database is marked as a read-only. We will check the status of the statistics location before and after the database is marked as a Read-Only.

-- 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

Here is the result set explained with comments.

SQL SERVER - Location of Statistics Before and After Database is Marked as a Read-Only statistics-readonly