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...
quote:|||To add to Hari's response, you will never lose data by dropping an index.
> 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
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...
quote:|||Hi Hari & Dan,
> 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
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
quote:
>--Original Message--
>Hi,
>Since it is a non clustered index , we can very well drop
one of the
quote:
>indexes.
>Use the below statement from Query Analyzer to drop the
index, ( Ensure
quote:
>that no users are accessing the index, otherwise it will
create a lock)
quote:|||It may be too harsh to say that redundant indexes will kill performance
>drop index Inbox.MessageID
>Thanks
>Hari
>MCDBA
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...
quote:|||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
> one of the
> index, ( Ensure
> create a lock)
>
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:3af501c3e3a4$671f44b0$a301280a@.phx.gbl...
quote:
> 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 Hokessql
No comments:
Post a Comment