SQL SERVER – Statistics for Read-Only Database are in TempDB

Here is the question I received when I was explaining about Statistics in SQLLive360 event in Orlando.

“As we use database tables and run various queries on SQL Server, it automatically creates statistics and the statistics helps eventually queries to execute efficiently. I also understand that statistics are stored in the same database where original object (table, view) exists. In the case of the Read-Only Database, what actually happens? Does Read-Only Database have statistics? If they can have statistics, how do they store them as database is already a Read-Only database?”

Well, this is a fantastic question as I have never blogged about it before. Here is how I would like to sum up Read-Only Database and statistics.

Read-Only Database and Statistics

Just like any other database Read-Only Database can also have statistics. In all of the other cases the statistics are stored in the database where the original object is stored but in the case of the Read-Only Databases, statistics are stored in TempDB. The behavior of the statistics created on Read-Only Database is absolutely same as they are created on any other database. However, there is a one thing to remember – whenever we restart the server the TempDB is reset to original state hence, every single time the server is restarted the statistics are built again from scratch for your Read-Only Database in TempDB.

Script to list Statistics

Here is a script which we can use to list all the statistics from any database.

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

Script to list Statistics for Read-Only Database

Here is a script which we can use to list all the statistics from any database.

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

Column is_temporary  is set to 1 when statistics are created on the Read-Only Database and they are stored in the TempDB. If you restart the server statistics will go away and will be created when needed again.

Example

Let us see an example, where we will create a database and later on populate that with the data. Once we have data in the table, we will make it read only and check if there are any statistics on that data table or not. We will not find any statistics on that table as we have so far not executed any query. Now let us execute a query on the table and once again check the status of statistics. You will find necessary stats created on the table. When you pay attention to the last column it will have is_temporary = 1, this means that the statistics has been created in TempDB.

-- 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
-- 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
-- Simple Select Statement
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

I have executed all of my scripts in SQL Server 2012. Let me know what you think about this subject.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

13 thoughts on “SQL SERVER – Statistics for Read-Only Database are in TempDB

  1. Make sense for auto create. I guess that’s not true for auto update, since there are no updates to data in read only database.

    Very interesting question. Never thought about it before, until I read this blog.

    Same is true for Database Snapshot?

  2. Pingback: SQL SERVER – How to Script Statistics from SSMS? – Scripting Statistics is Disabled in SSMS | Journey to SQL Authority with Pinal Dave

  3. 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?

    Thanks

  4. Pingback: SQL SERVER – Location of Statistics Before and After Database is Marked as a Read-Only | Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – Scripting Statistics for Read-Only Database and Making it Permanent | Journey to SQL Authority with Pinal Dave

  6. Pingback: SQL SERVER – Wrap on Series of Temporary Statistics | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s