Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Use the sys.dm db index usage stats dmv to check if indexes are being used

From Wiki

Jump to: navigation, search

Use the sys.dm_db_index_usage_stats dmv to check if indexes are being used

The sys.dm_db_index_usage_stats dynamic management view is extremely helpful in a couple of ways, I will list some of them It can help you identify if an index is used or not You can also find out the scan to seek ratio Another helpful thing is the fact that the last seek and scan dates are in the view, this can help you determine if the index is still used

So let's get started

  1. CREATE TABLE TestIndex(id INT IDENTITY,
  2. SomeID INT not null,
  3. SomeDate DATETIME not null)
  4. GO
  5.  
  6. CREATE CLUSTERED INDEX IX_TestIndexID ON TestIndex(SomeID)
  7. GO
  8.  
  9. CREATE NONCLUSTERED INDEX IX_TestIndexDate ON TestIndex(SomeDate)
  10. GO
  11.  
  12. INSERT TestIndex VALUES(1,GETDATE())
  13. GO
  14. INSERT TestIndex VALUES(2,GETDATE()-1)
  15. GO


Run the sys.dm_db_index_usage_stats query

  1. SELECT
  2. TableName = OBJECT_NAME(s.[OBJECT_ID]),
  3. IndexName = i.name,
  4. s.last_user_seek,
  5. s.user_seeks,
  6. CASE s.user_seeks WHEN 0 THEN 0
  7. ELSE s.user_seeks*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS SeekPercentage,
  8. s.last_user_scan,
  9. s.user_scans,
  10. CASE s.user_scans WHEN 0 THEN 0
  11. ELSE s.user_scans*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS ScanPercentage,
  12. s.last_user_lookup,
  13. s.user_lookups,
  14. s.last_user_update,
  15. s.user_updates,
  16. s.last_system_seek,
  17. s.last_system_scan,
  18. s.last_system_lookup,
  19. s.last_system_update,*
  20. FROM
  21. sys.dm_db_index_usage_stats s
  22. INNER JOIN
  23. sys.indexes i
  24. ON
  25. s.[OBJECT_ID] = i.[OBJECT_ID]
  26. AND s.index_id = i.index_id
  27. WHERE
  28. s.database_id = DB_ID()
  29. AND OBJECTPROPERTY(s.[OBJECT_ID], 'IsMsShipped') = 0
  30. AND OBJECT_NAME(s.[OBJECT_ID]) = 'TestIndex';


After each of the select queries below run the sys.dm_db_index_usage_stats query above

user_updates should be 2 but user_seeks,user_scans, user_lookups should be 0

  1. SELECT *
  2. FROM TestIndex
  3. WHERE ID =1
  4. --IX_TestIndexID user_scans = 1


  1. SELECT *
  2. FROM TestIndex
  3. WHERE SomeID =1
  4. --IX_TestIndexID user_seeks = 1
  1. SELECT *
  2. FROM TestIndex
  3. WHERE SomeDate > GETDATE() -1
  4. AND SomeID =1
  5. --IX_TestIndexID user_seeks = 2


let's force the optimizer to use the IX_TestIndexDate index

  1. SELECT *
  2. FROM TestIndex WITH (INDEX = IX_TestIndexDate)
  3. WHERE SomeDAte > GETDATE() -1
  4. --IX_TestIndexDate user_seeks = 1


IX_TestIndexID SeekPercentage = 66.66% and ScanPercentage = 33.33

As you can see I have added the following code CASE s.user_seeks WHEN 0 THEN 0 ELSE s.user_seeks*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS SeekPercentage CASE s.user_scans WHEN 0 THEN 0 ELSE s.user_scans*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS ScanPercentage

This is helpful to determine the seek/scan ratio if you have mostly scans then maybe you have to look at your queries to optimize them


If you run the sys.dm_db_index_usage_stats query again you will see that the user_updates column is 2, that's because we inserted 2 rows (2 batches)


Let's do this

  1. UPDATE TestIndex
  2. SET SomeID = SomeID + 1

--(2 row(s) affected)

Now user_updates is 3 since we used 1 batch that modified 2 rows

Now restart your server and run the same query again. as you can see the resultset is empty this is because the counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed. When an index is used, a row is added to sys.dm_db_index_usage_stats if a row does not already exist for the index. When the row is added, its counters are initially set to zero.


When you run this query

  1. SELECT *
  2. FROM TestIndex

You will see a row again after you run the sys.dm_db_index_usage_stats query Also note that every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.

The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly all by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.


sys.dm_db_index_usage_stats

database_id smallint

ID of the database on which the table or view is defined.


object_id int

ID of the table or view on which the index is defined


index_id int

ID of the index.


user_seeks bigint

Number of seeks by user queries.


user_scans bigint

Number of scans by user queries.


user_lookups bigint

Number of lookups by user queries.


user_updates bigint

Number of updates by user queries.


last_user_seek datetime

Time of last user seek


last_user_scan datetime

Time of last user scan.


last_user_lookup datetime

Time of last user lookup.


last_user_update datetime

Time of last user update.


system_seeks bigint

Number of seeks by system queries.


system_scans bigint

Number of scans by system queries.


system_lookups bigint

Number of lookups by system queries.


system_updates bigint

Number of updates by system queries.


last_system_seek datetime

Time of last system seek.


last_system_scan datetime

Time of last system scan.


last_system_lookup datetime

Time of last system lookup.


last_system_update datetime

Time of last system update.




Contributed by: --SQLDenis 20:49, 29 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Usefull Admin stuff For The Developer

294 Rating: 3.2/5 (30 votes cast)