Interface Blogs

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

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

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

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)