Are Your Indexes Being Used Effectively?

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.