What is the location of statistics created before a database is marked as read-only? 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.
Let me know if you find this information interesting. Please note that this only applies to SQL Server 2012.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
