When to Create an Index and When not to create and Index on a table or view?

Problem: When to Create an Index and When not to create and Index on a table or view?

Solution: In my last post I had a chance to explain what is an index and what they are used for and how many types of indexes are present and what are the differences between each of them.

If you want to go through that article, please go to this link. what is an Index.?

When to create an Index:

In a daily basis if you are using the table more often and your selects on the table are slower. A query may have benefit from an index if you are performing aggregation queries, such as grouping and searching with having clause, calculating counting the number of times a particular value appears. You may also benefit if you frequently order by on a particular column.

Usually in a reporting environment indexes would make huge impact where most of the aggregations happen.

Sometimes SQL server query optimizer suggests indexes when you keep an eye on the execution plans for execution costs and such other things like this one. But it’s not always right to use this recommendation as this can be a query specific which might not be always used for a daily work.

Also SQL Server has an index related dynamic management view which tells where there is a likely chance of indexes missing. Below is the view which gives information about missing indexes in a database.

sys.dm_db_missing_index_details (Transact-SQL)

Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. Missing index information is kept only until SQL Server is restarted. Database administrators should periodically make backup copies of the missing index information if they want to keep it after server recycling.

When not to create an Index:

If you have more number of indexes on a OLTP environment where you have more concurrent transactions happening for each second. If you have indexes on those tables where the query tries to insert or update the rows, the query might wait to sort the input and then insert based on the index. So there is a good chance of impact on the performance of inserts and updates.

There are few index related dynamic management views in SQL Server which has information related index usage and how often it uses.

sys.dm_db_index_usage_stats (Transact-SQL)

This view has a row for each time the query uses the index on the table whether it’s seek or scan.

Example:

Example 1: In this example I would like to show how we can retrieve the stats of an index and how this has been used in the recent times.

select t.name as table_name, i.name as index_name,iu.last_user_seek,iu.last_user_scan,iu.last_user_lookup,iu.last_user_update

 from sys.dm_db_index_usage_stats iu

 INNER JOIN sys.indexes i

 ON i.index_id=iu.index_id

  INNER JOIN sys.tables t

 ON i.object_id=t.object_id

 where database_id=DB_ID('DB')

 and t.is_ms_shipped=0

table_name	index_name	last_user_seek	last_user_scan	last_user_lookup
Subject	PK__Subject__AC1BA3A82F7E5518	NULL	36:53.2	NULL
Narrative	pk_NarrativeId	NULL	36:53.2	NULL
TableZ	PK__TableZ__3214EC2796F02B07	NULL	36:53.2	NULL
T_Call	PK__T_Call__5180CFAAC943B956	21:51.7	NULL	NULL
History	ClusteredIndex-20170110-211852	21:51.7	NULL	NULL
Employees	PK_Employees	21:51.7	NULL	NULL
Zone	NULL	NULL	NULL	21:51.7
TUsers	NULL	NULL	NULL	21:51.7
Address	ui_uSubject	21:51.7	NULL	NULL
nondurablememtemp	ix_1	21:51.7	NULL	NULL
durablememtemp	PK__durablem__A259EE45EC352EE1	21:51.7	NULL	NULL

This query will provide information about all the scans happen on a table and counts the number of scans. So this can elevate a chance of how often this gets into use.

 Example 2: This query will give information about missing indexes on a table when there is a required.

select * from sys.dm_db_missing_index_details