Partager via

How can SQL Server 2005 help me evaluate and manage indexes?

  • Article

Question: How can SQL Server 2005 help me evaluate and manage indexes?

(1) How can I find out whether my indexes are useful? How are they used?

(2) Do I have any tables or indexes that are not used (or rarely)

(3) What is the cost of index maintenance vs. its benefit?

(4) Do I have hot spots & index contention?

(5) Could I benefit from more (or less) indexes?

Answer:

SQL Server 2005 Dynamic Management Views (DMVs) are important insofar as they expose changing server state information that typically spans many sessions, many transactions, and many requests. DMVs provide a level of transparency that was not available in SQL Server 2000 and can be used for diagnostics, memory and process tuning, and monitoring. The SQL Server engine tracks detailed resource history in DMVs that can be queriable with SELECT statements but are not persisted to disk. Thus DMVs reflect activity since the last SQL Server recycle.

Since indexes provide an alternative to a table scan, and because DMVs expose index usage counters, we can compare the cost vs. the benefit of indexes. This comparison will include the maintenance cost of keeping the index up to date, versus the benefit of reads e.g. when an index can be used in lieu of a table scan. Keep in mind that an update or delete operation involves both a read which is first required to determine whether a row qualifies for the update operation, and a write if a row is found to qualify. In an insert operation, only writes will be performed on all indexes. Consequently, in an insert-intensive workload, writes will exceed reads. In an update- intensive (updates and deletes) workload, read and write counts are generally close assuming there are not a lot of ‘records not found’. In read-intensive workloads, read counts will exceed write counts. Referential constraints such as foreign keys require other read activity (for inserts, updates, and deletes) to ensure referential integrity is maintained.

(1) How can I find out whether my indexes are useful? How are they used?

First, we will determine whether indexes are ‘useful’. DDL is used to create objects (such as indexes) and update the catalog. Creating the index does not constitute ‘use’ of the index, and thus the index will not be reflected in the index DMVs until the index is actually used. When an index is used by a Select, Insert, Update, or Delete, its use is captured by sys.dm_db_index_usage_stats. If you have run a representative workload, all useful indexes will have been recorded in sys.dm_db_index_usage_stats. Thus, any index not found in sys.dm_db_index_usage_stats is unused by the workload (since the last re-cycle of SQL Server). Unused indexes can be found as follows:

(2) Do I have any tables or indexes that are not used (or rarely used)?

 

------ unused tables & indexes. Tables have index_id’s of either 0 = Heap table or 1 = Clustered Index

Declare @dbid int

Select @dbid = db_id('Northwind')

Select  objectname=object_name(i.object_id)

                        , indexname=i.name, i.index_id

from sys.indexes i, sys.objects o

where objectproperty(o.object_id,'IsUserTable') = 1

and i.index_id NOT IN (select s.index_id

       from sys.dm_db_index_usage_stats s

  where s.object_id=i.object_id and

            i.index_id=s.index_id and

                        database_id = @dbid )

and o.object_id = i.object_id

order by objectname,i.index_id,indexname asc

Rarely used indexes will appear in sys.dm_db_index_usage_stats just like heavily used indexes. To find rarely used indexes, you look at columns such as user_seeks, user_scans, user_lookups, and user_updates.

 

--- rarely used indexes appear first

declare @dbid int

select @dbid = db_id()

select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id

            , user_seeks, user_scans, user_lookups, user_updates

from sys.dm_db_index_usage_stats s,

            sys.indexes i

where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1

and i.object_id = s.object_id

and i.index_id = s.index_id

order by (user_seeks + user_scans + user_lookups + user_updates) asc

(3) What is the cost of index maintenance vs. its benefit?

If a table is heavily updated and also has indexes that are rarely used, the cost of maintaining the indexes could exceed the benefits. To compare the cost and benefit, you can use the table valued function sys.dm_db_index_operational_stats as follows:

--- sys.dm_db_index_operational_stats

declare @dbid int

select @dbid = db_id()

           

select objectname=object_name(s.object_id), indexname=i.name, i.index_id

            , reads=range_scan_count + singleton_lookup_count

            , 'leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count

            , 'leaf_page_splits' = leaf_allocation_count

            , 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count

            , 'nonleaf_page_splits' = nonleaf_allocation_count

from sys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) s,

                        sys.indexes i

where objectproperty(s.object_id,'IsUserTable') = 1

and i.object_id = s.object_id

and i.index_id = s.index_id

order by reads desc, leaf_writes, nonleaf_writes

--- sys.dm_db_index_usage_stats

select objectname=object_name(s.object_id), indexname=i.name, i.index_id

                        ,reads=user_seeks

Comments

  • Anonymous
    April 08, 2006
    Is there any system stored procedure to display text of an index?
    (like sp_helptext)

  • Anonymous
    October 12, 2006
    Now, I will admit up front that I haven't technically tried out all of their bits and pieces of the following...

  • Anonymous
    December 06, 2006
    PingBack from http://blog.allahbaksh.com/?p=36

  • Anonymous
    April 06, 2007
    Now, I will admit up front that I haven't technically tried out all of their bits and pieces of the following

  • Anonymous
    April 15, 2007
    Last week, I presented a query to view I/O pressure , and (to me) more usefully, look at it in a slice

  • Anonymous
    June 07, 2007
    Introduction There are three items that any SQL Server maintenance plan should cover at a minimum: Backup,

  • Anonymous
    June 08, 2007
    PingBack from http://msdnrss.thecoderblogs.com/2007/06/08/

  • Anonymous
    June 08, 2007
    PingBack from http://msdnrss.thecoderblogs.com/2007/06/08/smart-indexing-part-i-analyzing-indexes/

  • Anonymous
    August 17, 2007
    PingBack from http://furrukhbaig.wordpress.com/2007/08/17/is-this-index-useful/

  • Anonymous
    October 30, 2008
    SQL Server includes two DMVs - sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats - that