Duplicate Index Detected-Help!

Hi,
I have a table with more than 2 lakh records. Recently we
started SQL Server DB Auditing for performance and found
that one of the table has duplicate index. How do i remove
the duplicate?. This is a production DB and we dont want
to loose any data.
TableID IndexID Type Clustured Table.Index ColumnID's
37575172 4 STD n dbo.Inbox.MessageID 8
37575172 2 PKI n dbo.Inbox.PK_Inbox 8
Regards
ChipHi,
Since it is a non clustered index , we can very well drop one of the
indexes.
Use the below statement from Query Analyzer to drop the index, ( Ensure
that no users are accessing the index, otherwise it will create a lock)
drop index Inbox.MessageID
Thanks
Hari
MCDBA
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:3d4801c3e324$b7f943c0$a401280a@.phx.gbl...
> Hi,
> I have a table with more than 2 lakh records. Recently we
> started SQL Server DB Auditing for performance and found
> that one of the table has duplicate index. How do i remove
> the duplicate?. This is a production DB and we dont want
> to loose any data.
> TableID IndexID Type Clustured Table.Index ColumnID's
> 37575172 4 STD n dbo.Inbox.MessageID 8
> 37575172 2 PKI n dbo.Inbox.PK_Inbox 8
> Regards
> Chip|||To add to Hari's response, you will never lose data by dropping an index.
The underlying data is stored in the table data pages.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:3d4801c3e324$b7f943c0$a401280a@.phx.gbl...
> Hi,
> I have a table with more than 2 lakh records. Recently we
> started SQL Server DB Auditing for performance and found
> that one of the table has duplicate index. How do i remove
> the duplicate?. This is a production DB and we dont want
> to loose any data.
> TableID IndexID Type Clustured Table.Index ColumnID's
> 37575172 4 STD n dbo.Inbox.MessageID 8
> 37575172 2 PKI n dbo.Inbox.PK_Inbox 8
> Regards
> Chip|||Hi Hari & Dan,
Thanks for the Help. Aslo Can you please let me know how
do duplicate indexes kill performance of a database.
Thanks for all the help
Regards
Chip
>--Original Message--
>Hi,
>Since it is a non clustered index , we can very well drop
one of the
>indexes.
>Use the below statement from Query Analyzer to drop the
index, ( Ensure
>that no users are accessing the index, otherwise it will
create a lock)
>drop index Inbox.MessageID
>Thanks
>Hari
>MCDBA|||It may be too harsh to say that redundant indexes will kill performance
because it all depends on your environment. However, there is no doubt that
these will add unnecessary overhead for inserts, updates and deletes so you
should remove indexes you don't need.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:3af501c3e3a4$671f44b0$a301280a@.phx.gbl...
> Hi Hari & Dan,
> Thanks for the Help. Aslo Can you please let me know how
> do duplicate indexes kill performance of a database.
> Thanks for all the help
> Regards
> Chip
> >--Original Message--
> >Hi,
> >
> >Since it is a non clustered index , we can very well drop
> one of the
> >indexes.
> >
> >Use the below statement from Query Analyzer to drop the
> index, ( Ensure
> >that no users are accessing the index, otherwise it will
> create a lock)
> >
> >drop index Inbox.MessageID
> >
> >Thanks
> >Hari
> >MCDBA
>|||Chip,
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:3af501c3e3a4$671f44b0$a301280a@.phx.gbl...
> Thanks for the Help. Aslo Can you please let me know how
> do duplicate indexes kill performance of a database.
Mostly on INSERTs, UPDATEs, and DELETEs, due to maintenance of the index.
James Hokes