Consider the following scenario:
- In Microsoft SQL Server 2005, you frequently perform DDL operation that involve dropping and recreation of lot of tables (especially temporary tables in tempdb database).
- You have a large number of entries (100,000 or more) in the sys.dm_db_index_usage_stats dynamic management view (DMV).
In this scenario you may notice the following:
- Queries in general take a long time to complete.
- Overall CPU utilization [user mode time] is higher than normal for the Sqlservr.exe process.
- The output of DBCC SQLPERF ('SPINLOCKSTATS') shows large number of spins and collisions for OPT_IDX_STATS or SPL_OPT_IDX_STATS.
The statistics information for user tables in all databases and temporary tables from tempdb is collected and stored in sys.dm_db_index_usage_stats DMV. When you perform DDL operations that involve dropping and creating tables (for example temporary tables), this DMV needs to be updated with the relevant information. This DMV has concurrency protection in the form of a light weight spinlock. When several threads attempt to update this DMV at the same time, it can cause contention and lead to other problems discussed in the Symptoms section of this article.
You might also notice similar problems either when large number of entries are updated or frequent updates are performed for the information maintained in sys.dm_db_missing_index_group_stats DMV.
You can use one or more of the following methods to workaround the problem:
- Reduce the rate at which you create or drop temporary tables. Refer to the following white paper for an explanation on why temporary tables are not getting cached.
- Use table variables instead of temporary tables for tables that need to be frequently dropped and recreated.
- Configure your SQL Server to use trace flag 2330 (T2330) as a startup parameter. This trace flag disables collection of statistics reported in the sys.dm_db_index_usage_stats and sys.dm_db_missing_index_group_stats DMVs. For more information about how to enable trace flag as a startup option, visit the following MSDN Web site:
- Upgrade to SQL Server 2008. This issue is specific to SQL server 2005 environments only and the issue has already been addressed in all editions of SQL Server 2008.
for other considerations.