你的索引被有效的使用了吗？（Are Your Indexes Being Used Effectively?）

 

Are Your Indexes Being Used Effectively?

By : Ashish Kumar Mehta http://www.sql-server-performance.com/articles/per/Are_Your_Indexes_Being_Used_Effectively_p1.aspx Nov 03, 2008

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.

USE AdventureWorks
GO

 

DECLARE @TABLENAME SYSNAME
SET @TABLENAME= 'HumanResources.Employee'

SELECT DB_NAME(DATABASE_ID) AS [DATABASE NAME]
 , OBJECT_NAME(SS.OBJECT_ID) AS [OBJECT NAME]
 , I.NAME AS [INDEX NAME]
 , I.INDEX_ID AS [INDEX ID]
 , USER_SEEKS AS [NUMBER OF SEEKS]
 , USER_SCANS AS [NUMBER OF SCANS]
 , USER_LOOKUPS AS [NUMBER OF BOOKMARK LOOKUPS]
 , USER_UPDATES AS [NUMBER OF UPDATES]
FROM    
   SYS.DM_DB_INDEX_USAGE_STATS SS
   INNER JOIN SYS.INDEXES I
       ON I.OBJECT_ID = SS.OBJECT_ID
            AND I.INDEX_ID = SS.INDEX_ID
WHERE DATABASE_ID = DB_ID()
  AND OBJECTPROPERTY (SS.OBJECT_ID,'IsUserTable') = 1
 AND  SS.OBJECT_ID = OBJECT_ID(@TABLENAME) 
ORDER BY USER_SEEKS
    , USER_SCANS
    , USER_LOOKUPS
      , USER_UPDATES ASC
GO



From the above image you can see that all the indexes are used 20 times. This information is from the time when the SQL Server Service was last restarted. Once the SQL Server Service comes up after the restart all the values in sys.dm_db_index_usage_stats Dynamic Management Views (DMV) are reset to zero.

In the next step let us execute the below mentioned select statements which utilizes the indexes defined on the HumanResources.Employee table of the AdventureWorks database and see whether the changes are getting reflected in the sys.dm_db_index_usage_stats Dynamic Management Views (DMV):

Use AdventureWorks
GO

SELECT * FROM HumanResources.Employee WITH (INDEX = 1)
WHERE EmployeeID = '200'
GO
SELECT * FROM HumanResources.Employee WITH (INDEX = 2)
WHERE LoginID = 'adventure-works/hazem0'
GO
SELECT * FROM HumanResources.Employee WITH (INDEX = 3)
WHERE NationalIDNumber='398223854'
GO
SELECT * FROM HumanResources.Employee WITH (INDEX = 4)
WHERE rowguid='05C84608-F445-4F9D-BB5C-0828C309C29D'
GO



You could see that the above mentioned queries where executed with a specific index hint. This is done in order to make use of specific indexes when executing queries against the HumanResources.Employee table. Now let us execute the below query to see whether the usage of the index is updated in the sys.dm_db_index_usage_stats Dynamic Management Views (DMV) or not.



You could see that the values for INDEX ID from 1...4 has incremented by 1 to 21 from the previous value of 20 and for INDEX ID 5 the value has remained unchanged. In this way you can identify which indexes are really helpful and which are very rarely used. If you are interested to know when each of the above indexes were last used then you can use the query below:

 

USE AdventureWorks
GO

DECLARE @TABLENAME sysname
SET @TABLENAME= 'HumanResources.Employee'

SELECT DB_NAME(DATABASE_ID) AS [DATABASE NAME]
 , OBJECT_NAME(SS.OBJECT_ID) AS [OBJECT NAME]
 , I.NAME AS [INDEX NAME]
 , I.INDEX_ID AS [INDEX ID]
 , USER_SEEKS AS [NUMBER OF SEEKS]
 , LAST_USER_SEEK AS  [LAST USER SEEK]
 , USER_SCANS AS [NUMBER OF SCANS]
 , LAST_USER_SCAN AS [LAST USER SCAN]
 , USER_LOOKUPS AS [NUMBER OF BOOKMARK LOOKUPS]
 , LAST_USER_LOOKUP AS [LAST USER LOOKUP]
 , USER_UPDATES AS [NUMBER OF UPDATES]
 , LAST_USER_UPDATE AS [LAST USER UPDATE]
FROM    
   SYS.DM_DB_INDEX_USAGE_STATS SS
   INNER JOIN SYS.INDEXES I
       ON I.OBJECT_ID = SS.OBJECT_ID
            AND I.INDEX_ID = SS.INDEX_ID
WHERE DATABASE_ID = DB_ID()
  AND OBJECTPROPERTY(SS.OBJECT_ID,'IsUserTable') = 1
 AND  SS.OBJECT_ID = OBJECT_ID(@TABLENAME)
ORDER BY USER_SEEKS
    , USER_SCANS
    , USER_LOOKUPS
      , USER_UPDATES ASC
GO

Before dropping an index it would be ideal to disable the index on the table and see is there is any performance hit when the index is disabled. Disabling a non clustered index prevents users from accessing the particular non clustered index defined on the underlying table. However, the index definition remains in metadata and index statistics are also kept on nonclustered indexes. You need to keep in mind that if you disable the clustered index by any chance then you will not be able to access the data in the underlying table until the index is dropped or rebuilt.

Disable IX_Employee_ManagerID index on HumanResources.Employee table
USE AdventureWorks
GO
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee
DISABLE
GO

Enable IX_Employee_ManagerID index on HumanResources.Employee table
USE AdventureWorks
GO
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee REBUILD
GO

Finally after the analysis when it is discovered that the queries are not using the IX_Employee_ManagerID index then you can execute the below TSQL code to drop the index on the HumanResources.Employee table.

Drop IX_Employee_ManagerID index on HumanResources.Employee table
USE AdventureWorks
GO
DROP INDEX IX_Employee_ManagerID ON HumanResources.Employee
GO

Conclusion
Using the sys.dm_db_index_usage_stats Dynamic Management View, you can easily identify indexes which are used often by queries and also ndexes which are rarely used. Once you have identified the indexes, the best approach is to disable the indexes for some time and see is there is any performance degradation once the indexes are disabled. If there is no performance degradation then you can go ahead and drop the index there by saving disk space and improving performace during Insert, Update and Delete operations.