Forum FAQ: How do I monitor index usage?

• Friday, March 19, 2010 9:45 AM

   

   
  

  0

  Symptom

  I would like to monitor the indexes usage so as to determine when to perform a reindex job.

   

  Solution

  SQL Server provides the sys.dm_db_index_usage_stats DMV, which shows indexes are used and whether they are in use by the user query or only by a system operation. It tracks the following detail information for each index (for user queries and system queries):

  ·         Seek operations against an index (either looking up a single row, or doing a range scan).

  ·         Lookup operations against an index (this means a bookmark lookup – where a non-clustered index does not fully cover a query and additional columns must be retrieved from the base table row).

  ·         Scan operations against an index (e.g. a select * operation).

  ·         Update operations against an index (this counts inserts, updates, and deletes).

  Note that the data in this DMV is kept in memory only and is not persisted. When the SQL Server instance is shut down, the data is lost.

   

  The following example of the DMV query returns the index usage for all objects in all databases:

  Select db_name(database_id) as database_name,

  object_name(object_id) as object_name,

  index_id, user_seeks, user_scans, user_lookups

  from sys.dm_db_index_usage_stats

  order by object_id, index_id

   

  Also you can use the DMV sys.dm_db_index_physical_stats to check the fragmentation of your index. This is a good reference for you to determine if you need a reindex or reorganization.

   

  Please refer to these two articles for detail information:

  Reorganizing and Rebuilding Indexes

  http://technet.microsoft.com/en-us/library/ms189858.aspx

  sys.dm_db_file_space_usage (Transact-SQL)

  http://technet.microsoft.com/en-us/library/ms174412.aspx

  • Reply
  • Quote