Are Your Indexes Being Used Effectively?


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-workshazem0′
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.


Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |