Index Management using T-SQL

This post outlines some useful scripts to help manage indexes. Online indexes are not covered in this post as yet

Basic Information about an indexes for an object:

SELECT,, i.index_id, i.type_desc from sys.indexes i
join sys.tables t on i.object_id = t.object_id

Drop an Index:

DROP INDEX ix_TMG_Order_Insert_Order_Row_ID_Insert_Date

Space used by an Individual Index:

i.[object_id],,i.index_id,p.partition_number,p.rows AS [#Records],
(a.total_pages * 8) / 1024 AS [Reserved(mb)],(a.used_pages * 8)/ 1024 AS [Used(mb)]
FROM sys.indexes AS i
inner join sys.partitions AS p 
    ON i.object_id = p.object_id and i.index_id = p.index_id 
inner join sys.allocation_units AS a ON p.partition_id = a.container_id
WHERE i.[object_id] = object_id('Order_Item')
--AND i.index_id = 1 -- clustered index
ORDER BY p.partition_number

Most Used indexes on a database:

SELECT object_name(s.object_id) as TableName, isnull(,'HEAP') as IndexName, 
CASE i.index_id 
  ELSE 'NC' 
END AS IndexType 
,reads=user_seeks + user_scans + user_lookups, writes =  user_updates 
FROM sys.dm_db_index_usage_stats s join sys.indexes i 
ON s.object_id = i.object_id and i.index_id = s.index_id 
WHERE objectproperty(s.object_id,'IsUserTable') = 1 
and s.database_id = db_id() 
ORDER BY reads desc

I want to find out which indexes are not used…or have never been used

SELECT, i.* 
FROM sys.dm_db_index_usage_stats i, sysobjects o--, sysindexes ind
WHERE database_id = DB_ID('AdventureWorks')
and i.object_id =
/*uncomment if you only want never used indexes*/
--AND (last_user_seek is NULL 
--AND last_user_scan is NULL 
--AND last_user_lookup is NULL)
ORDER BY last_user_seek, last_user_scan, last_user_lookup

More detail about a particular object:

SELECT,, st.* 
FROM sys.indexes i
join sys.dm_db_index_usage_stats st on st.index_id = i.index_id 
join sys.objects o on i.object_id = o.object_id and o.object_id = st.object_id
--and last_user_seek is null
ORDER BY i.index_id

Generate dynamic drop statements for unused indexes:

SELECT 'DROP INDEX ' + + ' ON ' +,,, st.* 
FROM sys.indexes i
join sys.dm_db_index_usage_stats st on st.index_id = i.index_id 
join sys.objects o on i.object_id = o.object_id and o.object_id = st.object_id
and user_seeks = 0 and user_scans = 0 and user_lookups = 0
and database_id = DB_ID('CMS3000Dev')
ORDER BY, st.index_id

I want to find out which indexes are missing on my sql server…

/* ------------------------------------------------------------------
-- Title:    FindMissingIndexes
-- Author:    Brent Ozar
-- Date:    2009-04-01 
-- Modified By: Clayton Kramer <ckramer.kramer @="""">
-- Description: This query returns indexes that SQL Server 2005 
-- (and higher) thinks are missing since the last restart. The 
-- "Impact" column is relative to the time of last restart and how 
-- bad SQL Server needs the index. 10 million+ is high.
-- Changes: Updated to expose full table name. This makes it easier
-- to identify which database needs an index. Modified the 
-- CreateIndexStatement to use the full table path and include the
-- equality/inequality columns for easier identifcation.
------------------------------------------------------------------ */
    [Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),  
    [Table] = [statement],
    [CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_' 
        + '_' 
        + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')
        +ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_')
        + ' ON ' 
        + [statement] 
        + ' ( ' + IsNull(mid.equality_columns, '') 
        + CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE 
            CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END 
        + mid.inequality_columns END + ' ) ' 
        + CASE WHEN mid.included_columns IS NULL THEN '' 
        ELSE 'INCLUDE (' + mid.included_columns + ')' END 
        + ';', 
FROM sys.dm_db_missing_index_group_stats AS migs 
    INNER JOIN sys.dm_db_missing_index_groups AS mig 
        ON migs.group_handle = mig.index_group_handle 
    INNER JOIN sys.dm_db_missing_index_details AS mid 
        ON mig.index_handle = mid.index_handle 
    INNER JOIN sys.objects WITH (nolock) 
        ON mid.OBJECT_ID = sys.objects.OBJECT_ID 
WHERE (migs.group_handle IN 
        (SELECT TOP (500) group_handle 
        FROM sys.dm_db_missing_index_group_stats WITH (nolock) 
        ORDER BY 
    (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
    AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1 
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

3 Responses to Index Management using T-SQL

  1. Luis Cardenas says:

    excellently condensed, informative and useful thanks Mos…

    Su amigo!

  2. Pingback: Unused Indexes – Identifying and Removing « Mohsin's DBA Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: