Forum Discussion
Query on sys.dm_db_index_usage_stats slow
The slowness you’re seeing is expected behavior and not a bug in your query. sys.dm_db_index_usage_stats is a DMV that keeps accumulating index usage data from the last SQL Server restart, and on systems with long uptime and many databases/objects, its internal structures can grow very large. As the DMV grows, SQL Server may need to scan a significant amount of internal memory to return results, even when you filter on database_id, because DMVs are not indexed or statistics-optimized like user tables. That’s why the query becomes slower over time and suddenly becomes fast again after a SQL Server restart, which clears the DMV. Updating statistics does not help here, since statistics are not maintained for DMVs. The practical solution is to avoid querying this DMV frequently on a busy, long-running system; instead, periodically snapshot the data into your own table via a scheduled job and query that table (which you can index and control), or at least apply additional filters (for example, excluding rows with NULL last_user_update or limiting by date) to reduce the result set. This approach is commonly used in production systems to avoid the performance overhead of repeatedly scanning large DMVs.