Statistics play a crucial role in optimizing query performance in SQL Server. They provide the query optimizer with information about the distribution of data in tables, allowing it to make informed decisions about the most efficient way to execute queries. While statistics are typically stored in the same database as the original object, there is an interesting scenario to consider when dealing with Read-Only Databases.

Read-Only Databases, as the name suggests, are databases that are set to read-only mode. This means that no modifications can be made to the data within these databases. However, even in a Read-Only Database, statistics can still exist and contribute to query optimization.

In the case of Read-Only Databases, statistics are stored in TempDB. TempDB is a system database in SQL Server that is used to store temporary objects and data. When a query is executed on a Read-Only Database, the necessary statistics are created in TempDB to assist in query optimization.

It’s important to note that whenever the SQL Server is restarted, TempDB is reset to its original state. This means that every time the server is restarted, the statistics for Read-Only Databases are built again from scratch in TempDB.

To list all the statistics in any database, you can use the following script:

SELECT OBJECT_ID, name, auto_created, user_created, is_temporary
FROM sys.stats;

If you specifically want to list the statistics for a Read-Only Database, you can modify the script as follows:

SELECT OBJECT_ID, name, auto_created, user_created, is_temporary
FROM sys.stats
WHERE is_temporary = 1;

The column “is_temporary” is set to 1 when statistics are created on a Read-Only Database, indicating that they are stored in TempDB.

Let’s consider an example to further illustrate this concept. We will create a Read-Only Database, populate it with data, and then check if any statistics exist for the data table:

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

-- 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 is_temporary = 1
GO

-- Execute a Query
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 is_temporary = 1
GO

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

In this example, we create a Read-Only Database called “Read-Only” and insert data into the “Shift” table. Initially, there are no statistics for the table. However, when we execute a query on the table, the necessary statistics are created in TempDB. The column “is_temporary” is set to 1, indicating that the statistics are temporary and stored in TempDB.

Understanding how statistics work in Read-Only Databases can help you optimize query performance and make informed decisions when working with such databases. It’s important to keep in mind that statistics in Read-Only Databases are rebuilt from scratch every time the SQL Server is restarted.

I hope this article has provided you with a clear understanding of statistics in SQL Server, particularly in the context of Read-Only Databases. If you have any questions or thoughts on this topic, feel free to share them in the comments below.