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.

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

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 (https://blog.sqlauthority.com)

SQL Statistics
Previous Post
Weekend Project – Connect your Database and Automatically Generate a Fully Customized Back-office Interface
Next Post
SQL SERVER – Finding Frequently Running Query and Elapsed Time – Notes from the Field #005

Related Posts

Leave a Reply