I'm using a CTE to draw from 2 pieces of code and recommend indexes to lose.
The first is Jason Massie's Unused Index Query and the second is my Index Size script.
WITH UnusedIndexQuery (Object_ID, ObjectName, IndexName, Index_ID, Reads, Writes, Rows) AS ( SELECT s.object_id, objectname=OBJECT_NAME(s.OBJECT_ID) , indexname=i.name , i.index_id , reads=user_seeks + user_scans + user_lookups , writes = user_updates , p.rows FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID JOIN sys.partitions p ON p.index_id = s.index_id AND s.OBJECT_ID = p.OBJECT_ID WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1 AND s.database_id = DB_ID() AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND p.rows > 10000 ) , IndexSizes (schemaname,tablename,object_id,indexname,index_id,indextype,indexsizekb,indexsizemb,indexsizegb) AS ( SELECT sys_schemas.name AS SchemaName ,sys_objects.name AS TableName ,sys_objects.[object_id] AS object_id ,sys_indexes.name AS IndexName ,sys_indexes.index_id as index_id ,sys_indexes.type_desc AS IndexType ,partition_stats.used_page_count * 8 AS IndexSizeKB ,CAST(partition_stats.used_page_count * 8 / 1024.00 AS Decimal(10,3))AS IndexSizeMB ,CAST(partition_stats.used_page_count * 8 / 1048576.00 AS Decimal(10,3)) AS IndexSizeGB FROM sys.dm_db_partition_stats partition_stats INNER JOIN sys.indexes sys_indexes ON partition_stats.[object_id] = sys_indexes.[object_id] AND partition_stats.index_id = sys_indexes.index_id AND sys_indexes.type_desc <> 'HEAP' INNER JOIN sys.objects sys_objects ON sys_objects.[object_id] = partition_stats.[object_id] INNER JOIN sys.schemas sys_schemas ON sys_objects.[schema_id] = sys_schemas.[schema_id] AND sys_schemas.name <> 'SYS' ) select IndexSizes.* , UnusedIndexQuery.Reads , UnusedIndexQuery.Writes , UnusedIndexQuery.Rows from UnusedIndexQuery inner join IndexSizes on UnusedIndexQuery.object_id = IndexSizes.object_id and UnusedIndexQuery.index_id = IndexSizes.index_id order by reads
No comments:
Post a Comment