Query performance decreases when sys.dm_db_index_usage_stats has large number of rows

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article has been archived. It is offered "as is" and will no longer be updated.

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.
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Article ID: 2003031 - Last Review: 12/12/2015 05:01:39 - Revision: 2.0

Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Enterprise X64 Edition, Microsoft SQL Server 2005 Evaluation Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Service Pack 2, Microsoft SQL Server 2005 Service Pack 3, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Standard X64 Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Express Edition with Advanced Services, Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems

  • kbnosurvey kbarchive KB2003031