Using DMVs to find out the index usage history- SQLServer Index Usage

SQLserver 2005 ships with a set of DMVs that can help you identify the missing indexes for your workload, Analyze the effectiveness of the existing ones and help find out index fragmentation.

Using DMVs to find out the index usage history

Over a period of time, you could create a lot of indexes on your tables and modify existing ones. However, in SQL 2000, you couldn’t estimate how effective were each of these indexes. Poorly design indexes could lead to performance overhead instead of enhancing performance.

In SQL 2005, you can query the sys.dm_db_index_usage_stats DMV to find out the indexes that have NEVER been used since the last start of SQL Server. You can use the following query to find that out:

select object_name(i.object_id) as ObjectName,

i.name as IndexName, s.user_updates, s.user_seeks, s.user_scans, s.user_lookups

from sys.indexes i

left join sys.dm_db_index_usage_stats s

on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = <dbid>

where objectproperty(i.object_id, 'IsIndexable') = 1 and

-- index_usage_stats has no reference to this index (not being used)

s.index_id is null or

-- index is being updated, but not used by seeks/scans/lookups

(s.user_updates > 0 and s.user_seeks = 0

and s.user_scans = 0 and s.user_lookups = 0)

order by object_name(i.object_id) asc

In the output, you will ALL the indexes that have never been used by any sort of workload on your server, since the last start of SQL Server.

For indexes that have NEVER been used (either for a SELECT or a DML statement), all columns will be NULL

For indexes that have NEVER been used (for a SELECT), but had to be updated due to a DML statement, the user_updates column will be >0, while other columns will be 0. It is these indexes that could cause severe performance overhead for your DML statements and might be worth dropping.

Give ample time for SQL Server to get exposed to all the workload after a restart, before running this query.

Using DMVs to find out missing indexes

When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes. The missing indexes feature enables you to access information about these indexes so you can decide whether they should be implemented.

For more information on how to use this feature, please visit the following link

https://msdn2.microsoft.com/en-us/library/ms345417.aspx

Using DMVs to find out index fragmentation

The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement.

You can learn more about using this DMV to identify fragmentation, correcting it and possibly automating this activity for your server by visiting the following link:

https://msdn2.microsoft.com/en-us/library/ms188917.aspx

Regards

Karthick P.K