Forum Discussion

DominikS's avatar
DominikS
Copper Contributor
Oct 31, 2025

Query on sys.dm_db_index_usage_stats slow

Hi,

we discovered that queries on sys.dm_db_index_usage_stats are getting very slowly when the sql server is running for a longer time without restart.

The execution time is up to 30 seconds for the following query:

SELECT object_name(object_id) objectName, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id=db_id()

We get the following query plan:

The Actual Rows in LOGINDEXSTATS are about 2 million.

We found 2 similiar cases by searching the internet:

  1. https://stackoverflow.com/questions/52165370/query-against-sys-tables-sys-dm-db-index-usage-stats-slow
  2. https://www.linkedin.com/pulse/sql-server-2014-object-dependencies-dmvdmf-slow-andrea-bruschetta

We tested the workaround (UPDATE STATISTICS sys.*** WITH FULLSCAN;) without success.

 

How can we increase performance without restarting the database?


Regards

Dominik

sql

1 Reply

  • carlwalk's avatar
    carlwalk
    Copper Contributor
    Jan 07, 2026

    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.

Resources