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:
- SQL SERVER – Statistics for Read-Only Database are in TempDB
- SQL SERVER – How to Script Statistics from SSMS? – Scripting Statistics is Disabled in SSMS
- SQL SERVER – Location of Statistics Before and After Database is Marked as a Read-Only
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 (https://blog.sqlauthority.com)
