SQL SERVER – Scripting Statistics for Read-Only Database and Making it Permanent

This is a follow up blog post to my earlier blog posts about statistics for Read-Only Database. Please read the following blog post in this series to get further idea about this subject:

One question which keeps on coming to my Inbox which was based on Read-Only Database and its best practices. Everybody wanted to know that they do understand that they have a read-only database and its statistics is in the TempDB. When TempDB is restarted it resets the statistics. If the user knows that next time they are going to execute the same kind of queries on the Read-Only database and it will once again create the similar statistics in TempDB, is there any way to create those statistics in the Read-Only database so next time when the server restarts (or service restarts) the statistics remains in the database instead of disappearing with TempDB?

In simple words the question is – How to move statistics from temporary status  to permanent status in the Read-Only database?

This is very interesting question and indeed a very easy one. First of all users should only move the statistics from the TempDB database to Read-Only database if he is confident that the same statistics are created multiple times and there is absolutely no change in the queries which are running against the Read-Only database. In my career I have seen Read-Only database which are refreshed daily, weekly or at intervals. If you have such database in that case, you should not think of moving the statistics from the TempDB till the database is in a consistent stage where data is not moving much.

Now if you have decided to move your database from TempDB to Read-Only database, here is a big problem – we can’t create statistics in Read-Only Database. A database must be in ReadWrite mode to create statistics. Hence, users will have to change the status of the database from Read-Only to ReadWrite and move the statistics from TempDB. Once the statistics are moved from TempDB, you can once again change the status of the database from ReadWrite to Read-Only. When statistics are generated based from your Read-Only database, it will additionally append suffix _scripted at the end of the statistic’s name. For example if your original statistic’s name is  MyStatisticsName_readonly_database_statistic, it will be automatically converted to MyStatisticsName_readonly_database_statistic_scripted.

Here is a summary of how we can do the same.

Step 1: Change Database status to Read-Write

USE [master]
GO
ALTER DATABASE [dbname] SET  READ_WRITE WITH NO_WAIT
GO

Step 2: Follow the blog post to create statistics from Read-Only Database How to Script Statistics from SSMS? – Scripting Statistics is Disabled in SSMS

Step 3: Execute the script generated from Step 2

Step 4: Change Database status to Read-Only

USE [master]
GO
ALTER DATABASE [dbname] SET  READ_ONLY WITH NO_WAIT
GO

Let me know what you think of this blog post series.

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

One thought on “SQL SERVER – Scripting Statistics for Read-Only Database and Making it Permanent

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