Create missing indexes to improve your Database performance

How you create missing indexes to improve your Database performance

 

Step-1:

Query to identify the indexes which are missing. SqlServer provides you with DMVs(DynamicManagementViews) which help you get performance statistics of your DB.

Execute following query:

SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )

 AS [index_advantage] ,

 migs.last_user_seek ,

 mid.[statement] AS [Database.Schema.Table] ,

 mid.equality_columns ,

 mid.inequality_columns ,

 mid.included_columns ,

 migs.unique_compiles ,

 migs.user_seeks ,

 migs.avg_total_user_cost ,

 migs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )

 INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )

 ON migs.group_handle = mig.index_group_handle

 INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )

 ON mig.index_handle = mid.index_handle

WHERE mid.database_id = DB_ID()

ORDER BY index_advantage DESC ;

 

Step-2:

Analyse which indexes you need to create

Identify which storeprocs and/or applications are querying from the tables that these indexes should be created on. You need to carefully judge and prioritize which indexes returned from the previous query would actually help you. One thing you need to keep in mind is the read/write ratio. If you have multiple applications one just reading from a table and other that writes much more as compared to the number of times the first one reads, it might be a better decision to not create the index or create the index dynamically before  the first application  is using the table and drop it after it completes the required queries from the table

[If you are working with a database where you are not sure what/how many applications are using the tables which the indexes are referring to, you might want to try this trick. Create all indexes in the desc order of priority from the results of the above query suggests. You may just create  indexes until the index advantage that you think will help improve DB performance. Now execute all the unit tests from all the applications. Now you can safely remove the ones which do not have a good read/write ratio. To ensure that you have retained the right indexes you can capture a trace from prod and execute it on test or execute it from DatabaseTuningAdvisor for better analysis]

Remember too many indexes might decrease than improve the performance of DB

 

Step-3:

Create the indexes from the missing indexes results as per the below format picking the ones that you want and ignoring the ones that you do not require.

 CREATE NONCLUSTERED INDEX  <IX_<TableName>_<columnnames>>

     ON <TableName> (<EqualityColumns>,<InequalityColumns>)

     INCLUDE (<IncludedColumns>)

 

Step-4:

Observe the performance of created indexes. Drop any redundant/unuseful indexes that you created.

After a period of time that you think all the regular load from applications had hit the database, get the statistics for the indexes you created.

Kindly note that to trust the results of the below queries your DB server  should have been restarted long back and you server should have received appropriate load, as these DMVs get you only the data from last restart of the server

 Execute following queries with the indexes you created

 

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

and i.name in (<Index names you created>)

order by reads desc, leaf_writes, nonleaf_writes

order by indexname

 

 

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

                        ,reads=user_seeks + user_scans + user_lookups

                        ,writes =  user_updates

from sys.dm_db_index_usage_stats s,

            sys.indexes i

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

and s.object_id = i.object_id

and i.index_id = s.index_id

and s.database_id = @dbid

and i.name in (<Index names you created>)

order by reads desc

order by indexname

go

 

Step-5:

Compare the results of the two queries  above to verify that for all the create indexes reads are  more as  compared to the writes/updates. For most of the indexes the above two queries return a similar ratio of read/write. For very few that you find conflicting results relook into the indexes for which you have conflicting results. Make changes to those indexes as per your knowledge of the tables  and its columns  and try reanalyse these indexes.

 

Step-6:

Drop the indexes that you find the statistics DMVs show higher updates as compared to reads.

 

Step-7:

When you add indexes that are required it would be a better idea to look into the indexes which are no more being used and remove them. Following query gives statistics of indexes which are poorly performing. Now delete the indexes but make sure you have done the required analysis before deleting them. For instance, there might be a service/job/report scheduled to run once everymonth and your server might have restarting just two weeks back, in case which the following query would not show the indexes that the service/job/report uses.

SELECT TableName = OBJECT_NAME(s.[object_id]),

       SchemaName=SCHEMA_NAME(o.[schema_id])

        ,IndexName = i.name

        ,user_updates   

        ,i.is_primary_key

        ,OBJECTPROPERTY(s.[object_id], 'IsMsShipped')

        ,user_seeks

        ,user_scans

        ,user_lookups

FROM   sys.dm_db_index_usage_stats s

JOIN sys.objects O ON  s.[object_id] = O.[object_id]

JOIN sys.indexes i ON  s.[object_id] = i.[object_id]

    AND s.index_id = i.index_id

WHERE   OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

    -- Only ISV defined.

    AND user_seeks = 0

    AND user_scans = 0

    AND user_lookups = 0

    AND i.name IS NOT NULL -- Ignore HEAP indexes.

ORDER BY user_updates DESC

 

 

Lastly:

Remember you need to keep repeating this process as an ongoing activity as your applications emerge and you DB structure changes.