May 19, 2015 at 6:09 am
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'exactduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;
Using above script I identified indexes that are having same index definition.Regarding this I have some questions
1.Why indexes and statistics are considered as duplicate .
means statistics on tbl1(col1) and index on tbl1(col1) are considered as duplicate
2. Should I go ahead and drop this duplicate indexes given by query?
Thanks
May 19, 2015 at 6:13 am
mandirkumar 18293 (5/19/2015)
1.Why indexes and statistics are considered as duplicate .means statistics on tbl1(col1) and index on tbl1(col1) are considered as duplicate
A stats set and an index aren't duplicates and you can't drop one, but stats shouldn't show up in sys.indexes and so you shouldn't be able to see them
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
May 19, 2015 at 9:24 am
i would look at ozar's index routine and use that. I would also advise not to become a trigger happy DBA with some of this stuff. Depending on server reboots etc, for unused indexes I would save the results and analyze. An index which may not be used but every quarted may throw up as an issue, but when they run the quarterly close it now takes hours instead of minutes because your dropped them (I also store all my drop scripts in Sharepoint in case I may need them)
May 19, 2015 at 9:36 am
Yes, and check that there are no index hints in any of your code for any of the indexes you propose to drop.
John
May 19, 2015 at 6:04 pm
tcronin 95651 (5/19/2015)
i would look at ozar's index routine and use that.
Link?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 19, 2015 at 6:14 pm
May 20, 2015 at 7:11 am
he has a whole group of great tools
May 20, 2015 at 11:21 am
tcronin 95651 (5/20/2015)
www.brentozar.comhe has a whole group of great tools
Heh... yep... I know that. It's just that when you mention something that's very specific, you should include a very specific link lie pietlinden did above. 😉 It's just good forum etiquette.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply