SQL Server – Kullanılmayan index’leri bulun

By | 15 August 2013

Zaman içerisinde hangi index’lerin ne sıklıkla kullanıldığı ve hiç kullanılmadığının takibini yapmamız gerekli. kullanılmayan size’ı büyüten index’leri saptayıp silmek isteriz. Bunun için aşağıda paylaştığım sorguyu kullanabilirsiniz.

<span style="font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 13px; line-height: 19px;">SELECT ind.Index_id,</span></pre>
obj.Name AS TableName,
 ind.Name AS IndexName,
 ind.Type_Desc,
 indUsage.user_seeks,
 indUsage.user_scans,
 indUsage.user_lookups,
 indUsage.user_updates,
 indUsage.last_user_seek,
 indUsage.last_user_scan,
 'DROP INDEX [' + ind.name + '] ON [' + obj.name + ']' AS DropCommand
FROM sys.Indexes AS ind
 JOIN sys.Objects AS obj
 ON ind.object_id = obj.Object_ID
 LEFT JOIN sys.dm_db_index_usage_stats indUsage
 ON ind.object_id = indUsage.object_id
 AND ind.Index_id = indUsage.Index_id
WHERE ind.type_desc <> 'HEAP'
 AND obj.type <> 'S'
 AND OBJECTPROPERTY(obj.object_id, 'isusertable') = 1
 AND (
 ISNULL(indUsage.user_seeks, 0) = 0
 AND ISNULL(indUsage.user_scans, 0) = 0
 AND ISNULL(indUsage.user_lookups, 0) = 0
 )
 AND ind.Type_Desc = 'NONCLUSTERED'
ORDER BY
 obj.name,
 ind.Name
<pre>
Share

Leave a Reply

Your email address will not be published. Required fields are marked *