patternsqlMinor
Index usage stats DMV indicates no index activity
Viewed 0 times
indicatesstatsdmvusageactivityindex
Problem
I'm trying to obtain information on indexes. According to several websites, the below TSQL query should join information on indexes to the indexes table, returning indexes and related information. The below query is simplified to the bare join, as I'm currently trying to return data and then from there, I will return what I need:
This query returns nothing. Yet, in several databases there are hundreds of tables that have at least one index, and according to various articles online, this query should return those indexes (because it's joining on the object ids). Also, if I try to change the query to the below query, it returns nothing as well:
Why isn't this query returning data?
Added
If I run the below queries, I receive data from both of these
The problem (on the original query) is the
Final: Martin had the right answer originally - even though data was being returned from the
SELECT *
FROM sys.indexes i
INNER JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_idThis query returns nothing. Yet, in several databases there are hundreds of tables that have at least one index, and according to various articles online, this query should return those indexes (because it's joining on the object ids). Also, if I try to change the query to the below query, it returns nothing as well:
SELECT *
FROM DATABASENAME.sys.indexes i
INNER JOIN DATABASENAME.sys.dm_db_index_usage_stats s ON i.object_id = s.object_idWhy isn't this query returning data?
Added
If I run the below queries, I receive data from both of these
SELECT queries:SELECT *
FROM sys.indexes
SELECT *
FROM sys.dm_db_index_usage_statsThe problem (on the original query) is the
object_id join. However, I don't know why I have objects in one table that don't exist in another.Final: Martin had the right answer originally - even though data was being returned from the
sys.dm_db_index_usage_stats table, it was on the msdb database, not the specific database I was seeking. I ran queries on tables again, and found the database id in the table. Unsure of why the sys.dm_db_index_usage_stats table didn't retain data from previous queries on that database.Solution
One possibility would be that you don't have sufficient permissions to view the metadata.
Another is that
whenever a database is detached or is shut down (for example, because
AUTO_CLOSE is set to ON), all rows associated with the database are
removed.
You need to filter
Another is that
sys.dm_db_index_usage_stats only reports on indexes that have been used at least once since the server was last restarted. Also whenever a database is detached or is shut down (for example, because
AUTO_CLOSE is set to ON), all rows associated with the database are
removed.
AUTO_CLOSE is the default for SQL Server Express.You need to filter
sys.dm_db_index_usage_stats by database_id to determine if it has metadata loaded for a particular database also the join should include index_idSELECT *
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
ON i.object_id = s.object_id
AND i.index_id = s.index_id
AND s.database_id = DB_ID()Code Snippets
SELECT *
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
ON i.object_id = s.object_id
AND i.index_id = s.index_id
AND s.database_id = DB_ID()Context
StackExchange Database Administrators Q#44691, answer score: 8
Revisions (0)
No revisions yet.