SQL SERVER – Wrap on Series of Temporary Statistics

This is the final post in the series of the Temporary Statistics. I have previously written about this subject in a few posts. I have received quite a few questions about this series so I decided to include all the questions in this one blog post. Before you read this blog post, here are a few blog posts related to this subject.

Here are a few questions which I have received.

Question: Can I update the temporary statistics?
Answer: No, the user cannot create or update temporary statistics. SQL Server only can create or update temporary statistics. However, the user can drop the statistics with the help of DROP STATISTICS command.

Question: Is there any way the user can retain created temporary statistics permanently on SQL Server.
Answer: You can script out the temporary statistics and create them in the database with the steps described in the blog Scripting Statistics for Read-Only Database and Making it Permanent

Question: If database is read-only where are the temporary statistics are stored?
Answer: In the TempDB. Hence when you restart your SQL Server services, all the temporary statistics disappear.

Question: How can I know that if the statistics created is temporary or permanent?
Answer: You can run the SELECT query on sys.stats catalogue view and checkout the status of the index. If the is_temporary column is 1 for statistics it is temporary and if it is 0 it is permanent. Read more about that over here:  Statistics for Read-Only Database are in TempDB.  

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

About these ads

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