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.

SQL SERVER - Wrap on Series of Temporary Statistics temporarylogo

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

Previous Post
SQL SERVER – Finding Frequently Running Query and Elapsed Time – Notes from the Field #005
Next Post
Personal Technology – How to do Exercise and Stay Relaxed while in Office – Tips for Developers

Related Posts

Leave a Reply

Menu