Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Query performance decreases when sys.dm_db_index_usage_stats has large number of rows
Article ID: 2003031 - View products that this article applies to.
Consider the following scenario:
In this scenario you may notice the following:
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:
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.
Article ID: 2003031 - Last Review: December 27, 2010 - Revision: 2.0