Are Your Indexes Being Used Effectively?

In this fast moving world, data is the heart and soul of any enterprise. As the data is growing very rapidly day by day, the biggest challenge which enterprises face today is to store the data in such a way that it can be retrieved quickly whenever required. The most common thought which comes in the mind of database administrators who basically works on performance improvement is to add indexes for tables to improve the data retrieval. However adding too many indexes on a table can sometimes reduce the performance of the table considerably. So it is very important for the database administrator to know whether the indexes created on the tables are used effectively or not. If there are indexes created on a table and they are not used, then they should be drop, as having unwanted index will slow down Insert, Update or Delete operations on the underlying tables.

It has been always a challenge for database administrators to figure out which indexes on a table are helpful and which aren't. In SQL Server 2005, Microsoft introduced Dynamic Management Views (DMV) which return server state information that can be used by developers or database administrators to monitor the health of a SQL Server Instance and identify potential performance issues. Dynamic Management Views basically reflect all the activities on the instance of SQL Server since the last restart of SQL Server. All the Dynamic Management Views exist in the SYS schema and they can be easily identify as they follow the naming convention of dm_*. The list of all the Dynamic Management Views that are available on SQL Server 2005 and higher versions can be obtained by running the below TSQL code.

USE master
GO
SELECT * FROM sys.sysobjects WHERE NAME LIKE 'dm_%'
GO

Unfortunately in the SQL Server editions prior to SQL Server 2005 there is no easy way to identify indexes which are helpful and which aren't. In SQL Server 2000 the only way to identify if an index is being used or not was to capture a workload in profiler and then run it against the Index Tuning Wizard.

Some of the disadvantage of having too many indexes on a database table
a) Insert, Update and Delete operations will become very slow if there are many indexes created on a table. This happens because when the Insert, Update or a Delete operation occurs against a table all the indexes will be updated, there by reducing query performance
b) Indexes are basically stored on disk; the amount of disk space required by the index depends on the size of database table, and the number and type of columns used in the index definition.
c) The more the indexes, the more disk space that is required to store the indexes.

Example to verify whether indexes on a SQL Server table are used effectively or not
It is not advisable to simple go ahead and disable or drop any index on any SQL Server table without doing the proper investigation. As a database administrator or a database developer you need to make sure that you drop only those indexes which are not or rarely used by queries.

In this example we will be using the HumanResources.Employee table of AdventureWorks database. The first step will be to find out how many indexes are created on the HumanResources.Employee table. This can be done by using the sp_helpindex stored procedure which accepts ObjectOwner.TableName as a parameter.

Use AdventureWorks
GO
sp_helpindex 'HumanResources.Employee'
GO



You can see from the above image that there are five indexes defined on the HumanResources.Employee table in the AdventureWorks database. In SQL Server 2005 and above, the information related to index usage is stored in the sys.dm_db_index_usage_stats Dynamic Management Views (DMV). By executing the below mentioned query you can identify the current index usage information for the HumanResources.Employee table in the AdventureWorks database. However, in order to access the information stored in the sys.dm_db_index_usage_stats Dynamic Management Views (DMV) you need to have VIEW SERVER STATE permissions. Only members of the sysadmin fixed server role can grant VIEW SERVER STATE permissions to other users.