SQL Server 2005 Index Information

SQL Server 2005 Index Information


Here are some queries using the new Dynamic Management views and functions see which indexes are being used and how they are being updated.

show index usage statistics
This query shows which indexes are used, whether it was a seek, scan or lookup and the date it was last performed. When using the object_name function, you want to USE the database you are interested in and then filter the view so it only shows objects from that database. Filtering on object_id > 100 eliminates system tables from the output.
USE Adventureworks
SELECT object_name(object_id), *FROM sys.dm_db_index_usage_stats WHERE object_id > 100 AND database_id = DB_ID('Adventureworks')

show index operational statistics (I/O,locking, latching)
This query provides more information about current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database. Since this query uses a table-valued function, you must pass parameters for the database id, table id, index id, and partition number. If you want to select all tables in a database you just need to pass the database id and specify DEFAULT for the other three parameters. Filtering on object_id > 100 eliminates system tables from the output.
SELECT object_name(object_id), * FROM sys.dm_db_index_operational_stats (db_id('Adventureworks'), default, default, default)WHERE object_id > 100

show file I/O statistics
The last query provides read and write I/O counts by file. This can help you determine which files have the highest I/O activity. SELECT db_name(database_id), * FROM sys.dm_io_virtual_file_stats(default, default)

--