September 26, 2008 at 12:32 pm
Is there a way to find unused indexes in SQL Server 2000. In SQL Server 2005 they have a DMV to help you out.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 26, 2008 at 12:45 pm
Index tuning wizard?
September 26, 2008 at 12:57 pm
Nothing I am aware of in SQL Server 2000. Use this for 2005.
declare @dbid int
--To get Datbase ID
set @dbid = db_id()
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')
SQL DBA.
September 26, 2008 at 1:35 pm
SanjayAttray (9/26/2008)
Nothing I am aware of in SQL Server 2000. Use this for 2005.
How is that going to find indexes that the queries never use?
In SQL 2000, there's no easy way to find indexes that aren't used. The only real way is to do it one table at a time. Find all queries that affect the table (easy if you're using all stored procedures, difficult if the system uses ad-hoc SQL), run all the queries against a copy of the prod system and check what indexes are used by the queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply