(Optimizer) - Number of Indexes Per Table

Hi all,
We were all told that SQL 7.0 and up could use more than one index per table
during query optimization. In practice, how often does this actually occur?
If and when it does occur, how does this appear in the output of the 'Show
Estimated Execution Plan?". It seems to us that this almost never occurs.
We're seeing the first index, and then after that all HASH joins and such.
Thanks!
James HokesHi James
It happens when you have two where clauses referencing two different columns
with nonclustered indexes, and for each, there are only a few rows that
satisfy the condition.
In the showplan output, it looks like a join, but both tables are the same
table, and it's usually a hash join. SQL Server has build an internal
worktable from the results of using each of the indexes, and then those two
worktables have to be joined. SInce there are no good indexes on the
worktables, a hash join is used to find the rows in common between the
worktables.
Here is an example
USE Northwind
select * into od from [order details]
create index qnt_index on od(quantity)
create index price_index on od(unitprice)
go
select * from od
where quantity = 4 and unitprice = 6
Look at the plan for the select, after building the table and the two
indexes.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We were all told that SQL 7.0 and up could use more than one index per
table
> during query optimization. In practice, how often does this actually
occur?
> If and when it does occur, how does this appear in the output of the 'Show
> Estimated Execution Plan?". It seems to us that this almost never occurs.
> We're seeing the first index, and then after that all HASH joins and such.
> Thanks!
> James Hokes
>|||Sounds like you have a complicated multi-table join. After the first couple
joins all you have are work tables (intermediate data results) which have no
indexes. In this case it may be necessary to do hash joins due to the lack
of indexes. The key is to whittle down the result sets to a manageable set
in the first joins using the appropriate indexes so the joining of the
intermediate tables are relatively painless. Maybe if you post a query
example and the associated query plan we can cnfirm or deny that theory.
--
Andrew J. Kelly SQL MVP
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We were all told that SQL 7.0 and up could use more than one index per
table
> during query optimization. In practice, how often does this actually
occur?
> If and when it does occur, how does this appear in the output of the 'Show
> Estimated Execution Plan?". It seems to us that this almost never occurs.
> We're seeing the first index, and then after that all HASH joins and such.
> Thanks!
> James Hokes
>|||Kalen,
Interesting that it would only happen for 'a few rows'.
That being the case, I suspect that this is due to 'total page reads', i.e.
query cost.
Is that a safe assumption?
Thanks,
James Hokes
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e2zkDXLxDHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi James
> It happens when you have two where clauses referencing two different
columns
> with nonclustered indexes, and for each, there are only a few rows that
> satisfy the condition.
> In the showplan output, it looks like a join, but both tables are the same
> table, and it's usually a hash join. SQL Server has build an internal
> worktable from the results of using each of the indexes, and then those
two
> worktables have to be joined. SInce there are no good indexes on the
> worktables, a hash join is used to find the rows in common between the
> worktables.
> Here is an example
> USE Northwind
> select * into od from [order details]
> create index qnt_index on od(quantity)
> create index price_index on od(unitprice)
> go
> select * from od
> where quantity = 4 and unitprice = 6
> Look at the plan for the select, after building the table and the two
> indexes.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > We were all told that SQL 7.0 and up could use more than one index per
> table
> > during query optimization. In practice, how often does this actually
> occur?
> >
> > If and when it does occur, how does this appear in the output of the
'Show
> > Estimated Execution Plan?". It seems to us that this almost never
occurs.
> > We're seeing the first index, and then after that all HASH joins and
such.
> >
> > Thanks!
> > James Hokes
> >
> >
>|||This is a general principle for any use of nonclustered indexes. They will
only be cost effective if there are only a few rows that meet the condition,
so only a few pages need to be read. If you need to use a nc index to access
100's of rows, which means 100s of pages, it is often considered by the
optimizer to be more cost effective to just scan the table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:eoTyWLMxDHA.3208@.tk2msftngp13.phx.gbl...
> Kalen,
> Interesting that it would only happen for 'a few rows'.
> That being the case, I suspect that this is due to 'total page reads',
i.e.
> query cost.
> Is that a safe assumption?
> Thanks,
> James Hokes
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e2zkDXLxDHA.1932@.TK2MSFTNGP09.phx.gbl...
> > Hi James
> >
> > It happens when you have two where clauses referencing two different
> columns
> > with nonclustered indexes, and for each, there are only a few rows that
> > satisfy the condition.
> >
> > In the showplan output, it looks like a join, but both tables are the
same
> > table, and it's usually a hash join. SQL Server has build an internal
> > worktable from the results of using each of the indexes, and then those
> two
> > worktables have to be joined. SInce there are no good indexes on the
> > worktables, a hash join is used to find the rows in common between the
> > worktables.
> >
> > Here is an example
> >
> > USE Northwind
> >
> > select * into od from [order details]
> > create index qnt_index on od(quantity)
> > create index price_index on od(unitprice)
> > go
> > select * from od
> > where quantity = 4 and unitprice = 6
> >
> > Look at the plan for the select, after building the table and the two
> > indexes.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Hi all,
> > >
> > > We were all told that SQL 7.0 and up could use more than one index per
> > table
> > > during query optimization. In practice, how often does this actually
> > occur?
> > >
> > > If and when it does occur, how does this appear in the output of the
> 'Show
> > > Estimated Execution Plan?". It seems to us that this almost never
> occurs.
> > > We're seeing the first index, and then after that all HASH joins and
> such.
> > >
> > > Thanks!
> > > James Hokes
> > >
> > >
> >
> >
>|||Andrew,
Right on. We're talking multi-table, multi-column JOIN, multi-column WHERE
clause, multi-column output, multi-million rows. My main curiosity revolved
around the 'multiple tables per query'. I had pretty much put that out of my
mind for the last few years, because it never happens in this environment.
One of our team asked about it this morning during a debugging session, and
I remarked that I never see it. As for whittling the result sets down,
there's just no way.
Oh, I should mention that there's also no possibility of building covering
indices here, because of disk space constraints, as well as the constant
influx of new data, and the need for high UPDATE performance.
Hence, we got curious as to when it actually _would_ happen, and by the
sounds of it, pretty much never. :-)
Thanks,
James Hokes.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Sounds like you have a complicated multi-table join. After the first
couple
> joins all you have are work tables (intermediate data results) which have
no
> indexes. In this case it may be necessary to do hash joins due to the
lack
> of indexes. The key is to whittle down the result sets to a manageable
set
> in the first joins using the appropriate indexes so the joining of the
> intermediate tables are relatively painless. Maybe if you post a query
> example and the associated query plan we can cnfirm or deny that theory.
> --
> Andrew J. Kelly SQL MVP
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > We were all told that SQL 7.0 and up could use more than one index per
> table
> > during query optimization. In practice, how often does this actually
> occur?
> >
> > If and when it does occur, how does this appear in the output of the
'Show
> > Estimated Execution Plan?". It seems to us that this almost never
occurs.
> > We're seeing the first index, and then after that all HASH joins and
such.
> >
> > Thanks!
> > James Hokes
> >
> >
>|||However, it is not true that you won't have indexes to use even when you
have a multi-table join. If your join density (the average number of
duplicates on your join column) is not too high, and you have an index on
the join column, I have seen cases where an index is used for every table in
a 5 or 6 table join.
Have you tried index tuning wizard?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> Right on. We're talking multi-table, multi-column JOIN, multi-column WHERE
> clause, multi-column output, multi-million rows. My main curiosity
revolved
> around the 'multiple tables per query'. I had pretty much put that out of
my
> mind for the last few years, because it never happens in this environment.
> One of our team asked about it this morning during a debugging session,
and
> I remarked that I never see it. As for whittling the result sets down,
> there's just no way.
> Oh, I should mention that there's also no possibility of building covering
> indices here, because of disk space constraints, as well as the constant
> influx of new data, and the need for high UPDATE performance.
> Hence, we got curious as to when it actually _would_ happen, and by the
> sounds of it, pretty much never. :-)
> Thanks,
> James Hokes.
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > Sounds like you have a complicated multi-table join. After the first
> couple
> > joins all you have are work tables (intermediate data results) which
have
> no
> > indexes. In this case it may be necessary to do hash joins due to the
> lack
> > of indexes. The key is to whittle down the result sets to a manageable
> set
> > in the first joins using the appropriate indexes so the joining of the
> > intermediate tables are relatively painless. Maybe if you post a query
> > example and the associated query plan we can cnfirm or deny that theory.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Hi all,
> > >
> > > We were all told that SQL 7.0 and up could use more than one index per
> > table
> > > during query optimization. In practice, how often does this actually
> > occur?
> > >
> > > If and when it does occur, how does this appear in the output of the
> 'Show
> > > Estimated Execution Plan?". It seems to us that this almost never
> occurs.
> > > We're seeing the first index, and then after that all HASH joins and
> such.
> > >
> > > Thanks!
> > > James Hokes
> > >
> > >
> >
> >
>|||Right.
I never said it wasn't using indexes; all of our queries do. I've tuned them
all.
I was just wondering about the 'multiple indexes per table' in the optimizer
step,
since we never see it happen. My original post referenced the fact that we
_are_ seeing the one index used per table,
but never multiple.
Thanks,
James Hokes
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> However, it is not true that you won't have indexes to use even when you
> have a multi-table join. If your join density (the average number of
> duplicates on your join column) is not too high, and you have an index on
> the join column, I have seen cases where an index is used for every table
in
> a 5 or 6 table join.
> Have you tried index tuning wizard?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Andrew,
> >
> > Right on. We're talking multi-table, multi-column JOIN, multi-column
WHERE
> > clause, multi-column output, multi-million rows. My main curiosity
> revolved
> > around the 'multiple tables per query'. I had pretty much put that out
of
> my
> > mind for the last few years, because it never happens in this
environment.
> > One of our team asked about it this morning during a debugging session,
> and
> > I remarked that I never see it. As for whittling the result sets down,
> > there's just no way.
> >
> > Oh, I should mention that there's also no possibility of building
covering
> > indices here, because of disk space constraints, as well as the constant
> > influx of new data, and the need for high UPDATE performance.
> >
> > Hence, we got curious as to when it actually _would_ happen, and by the
> > sounds of it, pretty much never. :-)
> >
> > Thanks,
> > James Hokes.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Sounds like you have a complicated multi-table join. After the first
> > couple
> > > joins all you have are work tables (intermediate data results) which
> have
> > no
> > > indexes. In this case it may be necessary to do hash joins due to the
> > lack
> > > of indexes. The key is to whittle down the result sets to a
manageable
> > set
> > > in the first joins using the appropriate indexes so the joining of the
> > > intermediate tables are relatively painless. Maybe if you post a
query
> > > example and the associated query plan we can cnfirm or deny that
theory.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > Hi all,
> > > >
> > > > We were all told that SQL 7.0 and up could use more than one index
per
> > > table
> > > > during query optimization. In practice, how often does this actually
> > > occur?
> > > >
> > > > If and when it does occur, how does this appear in the output of the
> > 'Show
> > > > Estimated Execution Plan?". It seems to us that this almost never
> > occurs.
> > > > We're seeing the first index, and then after that all HASH joins and
> > such.
> > > >
> > > > Thanks!
> > > > James Hokes
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry, didn't mean to make it sound like it couldn't use indexes. Just
that it can and does happen<g>.
--
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> However, it is not true that you won't have indexes to use even when you
> have a multi-table join. If your join density (the average number of
> duplicates on your join column) is not too high, and you have an index on
> the join column, I have seen cases where an index is used for every table
in
> a 5 or 6 table join.
> Have you tried index tuning wizard?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Andrew,
> >
> > Right on. We're talking multi-table, multi-column JOIN, multi-column
WHERE
> > clause, multi-column output, multi-million rows. My main curiosity
> revolved
> > around the 'multiple tables per query'. I had pretty much put that out
of
> my
> > mind for the last few years, because it never happens in this
environment.
> > One of our team asked about it this morning during a debugging session,
> and
> > I remarked that I never see it. As for whittling the result sets down,
> > there's just no way.
> >
> > Oh, I should mention that there's also no possibility of building
covering
> > indices here, because of disk space constraints, as well as the constant
> > influx of new data, and the need for high UPDATE performance.
> >
> > Hence, we got curious as to when it actually _would_ happen, and by the
> > sounds of it, pretty much never. :-)
> >
> > Thanks,
> > James Hokes.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Sounds like you have a complicated multi-table join. After the first
> > couple
> > > joins all you have are work tables (intermediate data results) which
> have
> > no
> > > indexes. In this case it may be necessary to do hash joins due to the
> > lack
> > > of indexes. The key is to whittle down the result sets to a
manageable
> > set
> > > in the first joins using the appropriate indexes so the joining of the
> > > intermediate tables are relatively painless. Maybe if you post a
query
> > > example and the associated query plan we can cnfirm or deny that
theory.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > Hi all,
> > > >
> > > > We were all told that SQL 7.0 and up could use more than one index
per
> > > table
> > > > during query optimization. In practice, how often does this actually
> > > occur?
> > > >
> > > > If and when it does occur, how does this appear in the output of the
> > 'Show
> > > > Estimated Execution Plan?". It seems to us that this almost never
> > occurs.
> > > > We're seeing the first index, and then after that all HASH joins and
> > such.
> > > >
> > > > Thanks!
> > > > James Hokes
> > > >
> > > >
> > >
> > >
> >
> >
>|||No harm done!
I just didn't want people to spend any of their newsgroup time on that part
of the thread.
I know I only get to go on here a little bit, so I know your time and the
time of others is also valuable.
Thanks for yours!
James Hokes
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:uev3vyRxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> Sorry, didn't mean to make it sound like it couldn't use indexes. Just
> that it can and does happen<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> > However, it is not true that you won't have indexes to use even when you
> > have a multi-table join. If your join density (the average number of
> > duplicates on your join column) is not too high, and you have an index
on
> > the join column, I have seen cases where an index is used for every
table
> in
> > a 5 or 6 table join.
> >
> > Have you tried index tuning wizard?
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Andrew,
> > >
> > > Right on. We're talking multi-table, multi-column JOIN, multi-column
> WHERE
> > > clause, multi-column output, multi-million rows. My main curiosity
> > revolved
> > > around the 'multiple tables per query'. I had pretty much put that out
> of
> > my
> > > mind for the last few years, because it never happens in this
> environment.
> > > One of our team asked about it this morning during a debugging
session,
> > and
> > > I remarked that I never see it. As for whittling the result sets down,
> > > there's just no way.
> > >
> > > Oh, I should mention that there's also no possibility of building
> covering
> > > indices here, because of disk space constraints, as well as the
constant
> > > influx of new data, and the need for high UPDATE performance.
> > >
> > > Hence, we got curious as to when it actually _would_ happen, and by
the
> > > sounds of it, pretty much never. :-)
> > >
> > > Thanks,
> > > James Hokes.
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > Sounds like you have a complicated multi-table join. After the
first
> > > couple
> > > > joins all you have are work tables (intermediate data results) which
> > have
> > > no
> > > > indexes. In this case it may be necessary to do hash joins due to
the
> > > lack
> > > > of indexes. The key is to whittle down the result sets to a
> manageable
> > > set
> > > > in the first joins using the appropriate indexes so the joining of
the
> > > > intermediate tables are relatively painless. Maybe if you post a
> query
> >
> > > > example and the associated query plan we can cnfirm or deny that
> theory.
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > > Hi all,
> > > > >
> > > > > We were all told that SQL 7.0 and up could use more than one index
> per
> > > > table
> > > > > during query optimization. In practice, how often does this
actually
> > > > occur?
> > > > >
> > > > > If and when it does occur, how does this appear in the output of
the
> > > 'Show
> > > > > Estimated Execution Plan?". It seems to us that this almost never
> > > occurs.
> > > > > We're seeing the first index, and then after that all HASH joins
and
> > > such.
> > > > >
> > > > > Thanks!
> > > > > James Hokes
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>