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