Posted By

wilson0825 on 10/01/13


Tagged

index tuning


Versions (?)

Unused Index


 / Published in: SQL
 

URL: http://www.foliotek.com/devblog/identifying-unused-indexes-in-a-sql-server-database/

IDENTIFYING UNUSED INDEXES IN A SQL SERVER DATABASE

  1. -- GET UNUSED INDEXES THAT APPEAR IN THE INDEX USAGE STATS TABLE
  2. DECLARE @MinimumPageCount int
  3. SET @MinimumPageCount = 500
  4.  
  5. SELECT DATABASES.name AS [DATABASE],
  6. object_name(Indexes.object_id) AS [TABLE],
  7. Indexes.name AS [INDEX],
  8. PhysicalStats.page_count AS [Page_Count],
  9. CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Size (MB)],
  10. CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Frag %],
  11. ParititionStats.row_count AS [Row Count],
  12. CONVERT(decimal(18,2), (PhysicalStats.page_count * 8.0 * 1024)
  13. / ParititionStats.row_count) AS [INDEX Size/Row (Bytes)]
  14. FROM sys.dm_db_index_usage_stats UsageStats
  15. INNER JOIN sys.indexes Indexes
  16. ON Indexes.index_id = UsageStats.index_id
  17. AND Indexes.object_id = UsageStats.object_id
  18. INNER JOIN SYS.DATABASES DATABASES
  19. ON DATABASES.database_id = UsageStats.database_id
  20. INNER JOIN sys.dm_db_index_physical_stats (DB_ID(),NULL,NULL,NULL,NULL)
  21. AS PhysicalStats
  22. ON PhysicalStats.index_id = UsageStats.Index_id
  23. AND PhysicalStats.object_id = UsageStats.object_id
  24. INNER JOIN SYS.dm_db_partition_stats ParititionStats
  25. ON ParititionStats.index_id = UsageStats.index_id
  26. AND ParititionStats.object_id = UsageStats.object_id
  27. WHERE UsageStats.user_scans = 0
  28. AND UsageStats.user_seeks = 0
  29. -- ignore indexes with less than a certain number of pages of memory
  30. AND PhysicalStats.page_count > @MinimumPageCount
  31. -- Exclude primary keys, which should not be removed
  32. AND Indexes.type_desc != 'CLUSTERED'
  33. ORDER BY [Page_Count] DESC

Report this snippet  

You need to login to post a comment.