Multi Indexes - One Column

I ran accross something I had not seen before on a MSSQL 2000 db today. All
of the tables had a PK and an index for the PK. In addition there was also
created another Clustered Index on the same PK column. As I have never seen
this before I dropped all existing indexes and put a single Clustered Index
in place on the PK columns. Currently I am wondering if this double index is
something that may have caused some performance issues? All is working great
with the single clustered index.
Thanks, KimHi Kim
The Northwind database has some similar duplicate indexes. Usually, it is
not a good thing. Sometimes it doesn't hurt anything, but if you do a lot of
updates, all those indexes need to be updated along with the data, so it can
actually hurt performance. (Inserts and deletes could have similar
performance issues.)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kman" <kman@.toocoolwebs.com> wrote in message
news:OPtQroA1DHA.2156@.TK2MSFTNGP12.phx.gbl...
> I ran accross something I had not seen before on a MSSQL 2000 db today.
All
> of the tables had a PK and an index for the PK. In addition there was also
> created another Clustered Index on the same PK column. As I have never
seen
> this before I dropped all existing indexes and put a single Clustered
Index
> in place on the PK columns. Currently I am wondering if this double index
is
> something that may have caused some performance issues? All is working
great
> with the single clustered index.
> Thanks, Kim
>||||
| I ran accross something I had not seen before on a MSSQL 2000 db today.
All
| of the tables had a PK and an index for the PK. In addition there was also
| created another Clustered Index on the same PK column. As I have never
seen
| this before I dropped all existing indexes and put a single Clustered
Index
| in place on the PK columns. Currently I am wondering if this double index
is
| something that may have caused some performance issues? All is working
great
| with the single clustered index.
--
Generally you should avoid duplication of indexes to avoid performance
degradation during updates.
--
Eric Cárdenas
SQL Server support|||Thank you Kalen and Eric
Regards,
Kim (Kman)|||If you join by the PK a lot in your queries it would be faster for the
queries to use a non-clustered index for the joining instead of the
clustered index as simply more entries could be stored in the pages of the
NCI. As the others have pointed out this would slow down your
updates/inserts but if you do join to this table a lot and on that column,
it might behoove you to keep a seemingly-redundant index.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Kman" <kman@.toocoolwebs.com> wrote in message
news:OPtQroA1DHA.2156@.TK2MSFTNGP12.phx.gbl...
> I ran accross something I had not seen before on a MSSQL 2000 db today.
All
> of the tables had a PK and an index for the PK. In addition there was also
> created another Clustered Index on the same PK column. As I have never
seen
> this before I dropped all existing indexes and put a single Clustered
Index
> in place on the PK columns. Currently I am wondering if this double index
is
> something that may have caused some performance issues? All is working
great
> with the single clustered index.
> Thanks, Kim
>|||"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:eg%23KeXE1DHA.2180@.TK2MSFTNGP12.phx.gbl...
> If you join by the PK a lot in your queries it would be faster for the
> queries to use a non-clustered index for the joining instead of the
> clustered index as simply more entries could be stored in the pages of the
> NCI. As the others have pointed out this would slow down your
> updates/inserts but if you do join to this table a lot and on that column,
> it might behoove you to keep a seemingly-redundant index.
>
Interesting - so for a table that does a lot of joins, you're saying that
having a NCI on the PK would actually produce faster results?
How much faster (or is that a how long is a piece of string type question) ?|||Best way to test is use the command "set statistics IO on"
Run the query with only the clustered index, look at the logical IO (which
is not the number of pages hit but the number of times a hit occurred to a
page)
Place a non-clustered index on the PK column, re-run the query and look at
the new logical IO. If you are only needing the PK in your query it would be
faster to hit the NCI data pages as they are more compact. If your query
looks for multiple columns from the PK table, it may be more sensible for
SQL to only hit the clustered index and ignore your NCI...so, it all depends
on how your queries are.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Dan Boylett" <parc_erom@.crossdata.co.uk> wrote in message
news:ufeOGGF1DHA.484@.TK2MSFTNGP10.phx.gbl...
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:eg%23KeXE1DHA.2180@.TK2MSFTNGP12.phx.gbl...
> > If you join by the PK a lot in your queries it would be faster for the
> > queries to use a non-clustered index for the joining instead of the
> > clustered index as simply more entries could be stored in the pages of
the
> > NCI. As the others have pointed out this would slow down your
> > updates/inserts but if you do join to this table a lot and on that
column,
> > it might behoove you to keep a seemingly-redundant index.
> >
> Interesting - so for a table that does a lot of joins, you're saying that
> having a NCI on the PK would actually produce faster results?
> How much faster (or is that a how long is a piece of string type question)
?
>
>|||Hi Ray
This would only be true if the join was a SEMI-join, checking for existence
in of matching rows in the inner table. If you were doing a true inner join,
and you needed data from the matching rows, a clustered index would be a
better choice.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:eg#KeXE1DHA.2180@.TK2MSFTNGP12.phx.gbl...
> If you join by the PK a lot in your queries it would be faster for the
> queries to use a non-clustered index for the joining instead of the
> clustered index as simply more entries could be stored in the pages of the
> NCI. As the others have pointed out this would slow down your
> updates/inserts but if you do join to this table a lot and on that column,
> it might behoove you to keep a seemingly-redundant index.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Kman" <kman@.toocoolwebs.com> wrote in message
> news:OPtQroA1DHA.2156@.TK2MSFTNGP12.phx.gbl...
> > I ran accross something I had not seen before on a MSSQL 2000 db today.
> All
> > of the tables had a PK and an index for the PK. In addition there was
also
> > created another Clustered Index on the same PK column. As I have never
> seen
> > this before I dropped all existing indexes and put a single Clustered
> Index
> > in place on the PK columns. Currently I am wondering if this double
index
> is
> > something that may have caused some performance issues? All is working
> great
> > with the single clustered index.
> >
> > Thanks, Kim
> >
> >
>|||Kalen, I agree with you, not too many cases where you would really use that
scenario of duplicate indexes, with a clustered index already on the PK, it
would make more sense to have a NCI on a column that you might pull from the
table, which of course would include the clustered key already that you
could join on.
Thanks!
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:evf2SfJ1DHA.1916@.TK2MSFTNGP10.phx.gbl...
> Hi Ray
> This would only be true if the join was a SEMI-join, checking for
existence
> in of matching rows in the inner table. If you were doing a true inner
join,
> and you needed data from the matching rows, a clustered index would be a
> better choice.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:eg#KeXE1DHA.2180@.TK2MSFTNGP12.phx.gbl...
> > If you join by the PK a lot in your queries it would be faster for the
> > queries to use a non-clustered index for the joining instead of the
> > clustered index as simply more entries could be stored in the pages of
the
> > NCI. As the others have pointed out this would slow down your
> > updates/inserts but if you do join to this table a lot and on that
column,
> > it might behoove you to keep a seemingly-redundant index.
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Kman" <kman@.toocoolwebs.com> wrote in message
> > news:OPtQroA1DHA.2156@.TK2MSFTNGP12.phx.gbl...
> > > I ran accross something I had not seen before on a MSSQL 2000 db
today.
> > All
> > > of the tables had a PK and an index for the PK. In addition there was
> also
> > > created another Clustered Index on the same PK column. As I have never
> > seen
> > > this before I dropped all existing indexes and put a single Clustered
> > Index
> > > in place on the PK columns. Currently I am wondering if this double
> index
> > is
> > > something that may have caused some performance issues? All is working
> > great
> > > with the single clustered index.
> > >
> > > Thanks, Kim
> > >
> > >
> >
> >
>sql