Welcome to our blog post on understanding temporary statistics in SQL Server. In this post, we will address some common questions related to temporary statistics and provide insights into their usage and behavior.
What are Temporary Statistics?
Temporary statistics are a type of statistics that are created and maintained by SQL Server for temporary tables or table variables. These statistics help the query optimizer make better decisions when generating query plans for queries involving temporary objects.
Can Temporary Statistics be Updated?
No, temporary statistics cannot be updated by users. Only SQL Server has the ability to create or update temporary statistics. However, users can drop temporary statistics using the DROP STATISTICS command.
How to Retain Temporary Statistics Permanently?
If you want to retain temporary statistics permanently in your database, you can script them out and create them using the steps described in our previous blog post titled “Scripting Statistics for Read-Only Database and Making it Permanent”. This allows you to recreate the temporary statistics as permanent statistics in your database.
Where are Temporary Statistics Stored?
Temporary statistics are stored in the TempDB database. It’s important to note that when you restart your SQL Server services, all temporary statistics are cleared and need to be recreated.
Differentiating Temporary and Permanent Statistics
To determine whether a statistic is temporary or permanent, you can query the sys.stats catalog view. If the “is_temporary” column value is 1, it indicates that the statistic is temporary. Conversely, if the value is 0, it means the statistic is permanent. For more information, you can refer to our blog post titled “Statistics for Read-Only Database are in TempDB”.
We hope this blog post has provided you with a better understanding of temporary statistics in SQL Server. By knowing how temporary statistics work and how to manage them, you can optimize the performance of your queries involving temporary objects.