Getting rid of unused indexes - sql server 2000

Hi,
I'm analyzing index configuration in one large database (800GB). I am not
familiar that much with the application. Since I suspected that bunch of
indexes are not being used at all (for most of "suspicious" composite
indexes, less selective column is listed first - one of the things that makes
me think what I think), I ran the trace, and included only "execution plan"
event in it. I imported all these trace files into table, and I ran couple of
reports against it. I believe that I got confirmation for my initial thought
but first I want to double check with you guys:
For most of "suspicious" indexes (I observed only large tables) I found only
"index delete" operations. For example, for one of these indexes, in all
execution plans for that day, there was only this (as part of one of many
execution plans):
|--Index
Delete(OBJECT:([products].[dbo].[ProdTrans].[IX_ProdTrans_Days_Type]))
So no "Index Scan", no "Index Seek" operations for the the given index
(IX_ProdTrans_Days_Type) at all - whole day! These delete operations are (I
suspect) part of every day purging process. So when they run purging script
for the table, all indexes are listed within execution plan with "index
delete" operation.
I was just wondering if I missed something? Does this idea sound reasonable?
Thanks,
Pedja
P.S. I do take into account weekly&monthly tasks which were not included
into execution plans that day...Pedja
So what is the question?
http://www.sql-server-performance.com/ma_finding_duplicate_indexes.asp
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
> Hi,
> I'm analyzing index configuration in one large database (800GB). I am not
> familiar that much with the application. Since I suspected that bunch of
> indexes are not being used at all (for most of "suspicious" composite
> indexes, less selective column is listed first - one of the things that
> makes
> me think what I think), I ran the trace, and included only "execution
> plan"
> event in it. I imported all these trace files into table, and I ran couple
> of
> reports against it. I believe that I got confirmation for my initial
> thought
> but first I want to double check with you guys:
> For most of "suspicious" indexes (I observed only large tables) I found
> only
> "index delete" operations. For example, for one of these indexes, in all
> execution plans for that day, there was only this (as part of one of many
> execution plans):
> |--Index
> Delete(OBJECT:([products].[dbo].[ProdTrans].[IX_ProdTrans_Days_Type]))
> So no "Index Scan", no "Index Seek" operations for the the given index
> (IX_ProdTrans_Days_Type) at all - whole day! These delete operations are
> (I
> suspect) part of every day purging process. So when they run purging
> script
> for the table, all indexes are listed within execution plan with "index
> delete" operation.
> I was just wondering if I missed something? Does this idea sound
> reasonable?
> Thanks,
> Pedja
> P.S. I do take into account weekly&monthly tasks which were not included
> into execution plans that day...|||In SQL Server 2005 you can find unused or used indexes and type of
index use in details.
take a look at
http://shahamishm.tripod.com/id1.html
Regards
Amish Shah
http://shahamishm.tripod.com
Uri Dimant wrote:
> Pedja
> So what is the question?
> http://www.sql-server-performance.com/ma_finding_duplicate_indexes.asp
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
> > Hi,
> > I'm analyzing index configuration in one large database (800GB). I am not
> > familiar that much with the application. Since I suspected that bunch of
> > indexes are not being used at all (for most of "suspicious" composite
> > indexes, less selective column is listed first - one of the things that
> > makes
> > me think what I think), I ran the trace, and included only "execution
> > plan"
> > event in it. I imported all these trace files into table, and I ran couple
> > of
> > reports against it. I believe that I got confirmation for my initial
> > thought
> > but first I want to double check with you guys:
> > For most of "suspicious" indexes (I observed only large tables) I found
> > only
> > "index delete" operations. For example, for one of these indexes, in all
> > execution plans for that day, there was only this (as part of one of many
> > execution plans):
> >
> > |--Index
> > Delete(OBJECT:([products].[dbo].[ProdTrans].[IX_ProdTrans_Days_Type]))
> >
> > So no "Index Scan", no "Index Seek" operations for the the given index
> > (IX_ProdTrans_Days_Type) at all - whole day! These delete operations are
> > (I
> > suspect) part of every day purging process. So when they run purging
> > script
> > for the table, all indexes are listed within execution plan with "index
> > delete" operation.
> >
> > I was just wondering if I missed something? Does this idea sound
> > reasonable?
> >
> > Thanks,
> > Pedja
> >
> > P.S. I do take into account weekly&monthly tasks which were not included
> > into execution plans that day...|||amish
SS2005? He are asking about SS2000
"amish" <shahamishm@.gmail.com> wrote in message
news:1166513986.026176.182400@.j72g2000cwa.googlegroups.com...
> In SQL Server 2005 you can find unused or used indexes and type of
> index use in details.
> take a look at
> http://shahamishm.tripod.com/id1.html
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>
> Uri Dimant wrote:
>> Pedja
>> So what is the question?
>> http://www.sql-server-performance.com/ma_finding_duplicate_indexes.asp
>>
>> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
>> news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
>> > Hi,
>> > I'm analyzing index configuration in one large database (800GB). I am
>> > not
>> > familiar that much with the application. Since I suspected that bunch
>> > of
>> > indexes are not being used at all (for most of "suspicious" composite
>> > indexes, less selective column is listed first - one of the things that
>> > makes
>> > me think what I think), I ran the trace, and included only "execution
>> > plan"
>> > event in it. I imported all these trace files into table, and I ran
>> > couple
>> > of
>> > reports against it. I believe that I got confirmation for my initial
>> > thought
>> > but first I want to double check with you guys:
>> > For most of "suspicious" indexes (I observed only large tables) I found
>> > only
>> > "index delete" operations. For example, for one of these indexes, in
>> > all
>> > execution plans for that day, there was only this (as part of one of
>> > many
>> > execution plans):
>> >
>> > |--Index
>> > Delete(OBJECT:([products].[dbo].[ProdTrans].[IX_ProdTrans_Days_Type]))
>> >
>> > So no "Index Scan", no "Index Seek" operations for the the given index
>> > (IX_ProdTrans_Days_Type) at all - whole day! These delete operations
>> > are
>> > (I
>> > suspect) part of every day purging process. So when they run purging
>> > script
>> > for the table, all indexes are listed within execution plan with "index
>> > delete" operation.
>> >
>> > I was just wondering if I missed something? Does this idea sound
>> > reasonable?
>> >
>> > Thanks,
>> > Pedja
>> >
>> > P.S. I do take into account weekly&monthly tasks which were not
>> > included
>> > into execution plans that day...
>|||> I was just wondering if I missed something? Does this idea sound reasonable?
Yes, your reasoning seems reasonable to me.
Just to be sure, do some analysis on some index that *is* used and make sure you *do* find scan
and/or seek operations against that index. Just so you don't miss out all index usage in your
analysis (you never know...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:1D5EC8FA-E448-4DEE-BC5F-C565A83583B3@.microsoft.com...
> Hi,
> I'm analyzing index configuration in one large database (800GB). I am not
> familiar that much with the application. Since I suspected that bunch of
> indexes are not being used at all (for most of "suspicious" composite
> indexes, less selective column is listed first - one of the things that makes
> me think what I think), I ran the trace, and included only "execution plan"
> event in it. I imported all these trace files into table, and I ran couple of
> reports against it. I believe that I got confirmation for my initial thought
> but first I want to double check with you guys:
> For most of "suspicious" indexes (I observed only large tables) I found only
> "index delete" operations. For example, for one of these indexes, in all
> execution plans for that day, there was only this (as part of one of many
> execution plans):
> |--Index
> Delete(OBJECT:([products].[dbo].[ProdTrans].[IX_ProdTrans_Days_Type]))
> So no "Index Scan", no "Index Seek" operations for the the given index
> (IX_ProdTrans_Days_Type) at all - whole day! These delete operations are (I
> suspect) part of every day purging process. So when they run purging script
> for the table, all indexes are listed within execution plan with "index
> delete" operation.
> I was just wondering if I missed something? Does this idea sound reasonable?
> Thanks,
> Pedja
> P.S. I do take into account weekly&monthly tasks which were not included
> into execution plans that day...