November 20, 2007 at 4:33 pm
1.Does anyone have the scrip to find the unused indexes.:hehe:
November 20, 2007 at 7:54 pm
Hi,
Declare @dbid as int
set @dbid=db_id('northwind')
SELECT
object_name(i.object_id) object_name
,i.name index_name
,i.index_id index_id
,i.type_desc type_desc
,c.index_columns
FROM
sys.indexes i LEFT OUTER JOIN
sys.dm_db_index_usage_stats d ON d .object_id = i.object_id AND i.index_id = d .index_id AND d .database_id = @dbid
LEFT OUTER JOIN
(SELECT DISTINCT object_id, index_id, stuff
((SELECT ',' + col_name(object_id, column_id) AS 'data()'
FROM sys.index_columns t2
WHERE t1.object_id = t2.object_id AND t1.index_id = t2.index_id FOR XML PATH('')), 1, 1, '') AS 'index_columns'
FROM
sys.index_columns t1) c
ON c.index_id = i.index_id AND c.object_id = i.object_id
WHERE
objectproperty(i.object_id, 'IsIndexable') = 1
AND d .index_id IS NULL
AND i.type_desc NOT IN ('heap', 'clustered')
Also check the following links
http://blogs.msdn.com/sqlcat/archive/2006/02/13/531339.aspx
http://www.developersdex.com/gurus/articles/888.asp?Page=2
Regards,
Ahmed
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply