Friday, 21 August 2009

TSQL : Unused Indexes and Index Sizes

A quick exercise in looking at saving a little data space by dropping some unused indexes.
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: