October 7, 2011 at 2:43 am
Morning All
I have the following script so far to check for any indexes which have not been used in a lookup, scan or seek and to create a drop command for index maintenance.
I then want to create a create command just incase we ever need to recreate the index for whatever reason, I have got it working to a degree, but cant get it to generate the create statement if the index contains more than one column.
select
'DROP INDEX [' + b.name + '] ON ['+OBJECT_SCHEMA_NAME(a.object_id) + '].['+OBJECT_NAME(a.object_id) + ']',
'CREATE INDEX [' + b.name + '] ON ['+OBJECT_SCHEMA_NAME(a.object_id) + '].['+OBJECT_NAME(a.object_id) + '] (' + d.name +')'
from
sys.dm_db_index_usage_stats a
inner join
sys.indexes b
on
a.object_id = b.object_id
and
a.index_id = b.index_id
inner join
sys.index_columns c
on
a.object_id = c.object_id
and
a.index_id = c.index_id
inner join
sys.columns d
on
a.object_id = d.object_id
and
c.index_column_id = d.column_id
where
database_id = (DB_ID('netcars'))
and
user_lookups = 0
and
user_scans = 0
and
user_seeks = 0
order by
last_user_seek
/Initial results/
DROP INDEX [IX_Vehicle_Colour] ON [dbo].[Vehicle]
CREATE INDEX [IX_Vehicle_Colour] ON [dbo].[Vehicle] (VehicleId)
DROP INDEX [IX_FeedProviderMapping] ON [dbo].[FeedProviderMapping]
CREATE INDEX [IX_FeedProviderMapping] ON [dbo].[FeedProviderMapping] (FeedProviderMappingId)
DROP INDEX [IX_FeedProviderMapping] ON [dbo].[FeedProviderMapping]
CREATE INDEX [IX_FeedProviderMapping] ON [dbo].[FeedProviderMapping] (FeedProviderMappingGroupId)
/Expected Results/
DROP INDEX [IX_Vehicle_Colour] ON [dbo].[Vehicle]
CREATE INDEX [IX_Vehicle_Colour] ON [dbo].[Vehicle] (VehicleId)
DROP INDEX [IX_FeedProviderMapping] ON [dbo].[FeedProviderMapping]
CREATE INDEX [IX_FeedProviderMapping] ON [dbo].[FeedProviderMapping] (FeedProviderMappingId,FeedProviderMappingGroupId)
Any help would be appreciated.
Thanks
Anthony
October 7, 2011 at 3:14 am
anthony.green (10/7/2011)
Morning AllI have the following script so far to check for any indexes which have not been used in a lookup, scan or seek and to create a drop command for index maintenance.
Be very, very, very careful with that. The DMV only stores info since the last time the DB was started. Easy to mistake a seldom-used index for an unused index.
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply