Sql-server – Index usage stats DMV indicates no index activity

indexsql server

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:

SELECT *
FROM sys.indexes i
     INNER JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id

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:

SELECT *
FROM DATABASENAME.sys.indexes i
    INNER JOIN DATABASENAME.sys.dm_db_index_usage_stats s ON i.object_id = s.object_id

Why 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_stats

The 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.

Best Answer

One possibility would be that you don't have sufficient permissions to view the metadata.

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_id

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()

Related Solutions

Sql-server – Index physical stats DMV – Object ID/table not found

You are in the wrong database context.

Try:

select avg_fragmentation_in_percent 
from databasename.sys.dm_db_index_physical_stats(DB_ID('databasename'), OBJECT_ID('tablename'), 1, NULL, 'limited')

Alternatively you can just throw a

USE Databasename

on the first line.

I'm guessing that it's executing in the context of master or something similar, which won't have the OBJECTID you are looking for.

Sql-server – Referencing database programmatically via T-SQL

Dynamic SQL comes in handy for these types of administrative tasks. Here is a snippet from a stored procedure I wrote that not only gets the defragmentation levels, but also generates the code to do the defragmentation:

select @SQL = 
'
select getdate(),
       ''' + @@ServerName + ''',
       ''' + @DatabaseName + ''',
       so.Name,
       si.Name,
       db_id(''' + @DatabaseName + '''),
       ips.object_id,
       ips.index_id,
       ips.index_type_desc,
       ips.alloc_unit_type_desc,
       ips.index_depth,
       ips.avg_fragmentation_in_percent,
       ips.fragment_count,
       avg_fragment_size_in_pages,
       ips.page_count,
       ips.record_count,
       case
         when ips.index_id = 0 then ''alter table [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
         else ''alter index '' + si.name + '' on [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
       end
  from sys.dm_db_index_physical_stats(db_id(''' + @DatabaseName + '''),null,null,null, ''' + @SampleMode + ''') ips
  join [' + @DatabaseName + '].sys.objects so  on so.object_id = ips.object_id
  join [' + @DatabaseName + '].sys.schemas ss  on ss.schema_id = so.schema_id
  join [' + @DatabaseName + '].sys.indexes si  on si.object_id = ips.object_id
                      and si.index_id  = ips.index_id
order by so.Name, ips.index_id
'

exec (@SQL)

Best Answer

Related Solutions

Sql-server – Index physical stats DMV – Object ID/table not found

Sql-server – Referencing database programmatically via T-SQL

Related Question