Best indexes to use

Hello,
I am currently working on some database tables that are suffering from
performance issues and would like some advice on which indexes are best
to use.
There exists a composite key on three fields out of 7 in the table and
these three fields are the ones normally used when searching the table
as well.
1) As most queries return only single answers, would it be better to
add three seperate indexes or a composite index?
2) Also, from my research, most people indicate using composite index
would be better - is this the case?
3) If I use a composite index, would it be used when searching using
only one or two of the composite fields?
Thanks in advance,
jjumblesale wrote:
> Hello,
> I am currently working on some database tables that are suffering from
> performance issues and would like some advice on which indexes are best
> to use.
> There exists a composite key on three fields out of 7 in the table and
> these three fields are the ones normally used when searching the table
> as well.
> 1) As most queries return only single answers, would it be better to
> add three seperate indexes or a composite index?
> 2) Also, from my research, most people indicate using composite index
> would be better - is this the case?
> 3) If I use a composite index, would it be used when searching using
> only one or two of the composite fields?
> Thanks in advance,
> j
>
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Cheers for your reply - that's great in regards to the composite index
issue i'm having, but in these kinds of scenarios, would I be right in
thinking that a non-clustered index covering the three columns would be
of more use than a clustered index because I am using queries that
bring back small numbers (usually one) result?
j
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||jumblesale wrote:
> Cheers for your reply - that's great in regards to the composite index
> issue i'm having, but in these kinds of scenarios, would I be right in
> thinking that a non-clustered index covering the three columns would be
> of more use than a clustered index because I am using queries that
> bring back small numbers (usually one) result?
>
I'm going to have to used the canned response of "it depends" on this
one. *GENERALLY* clustered indexes perform best on range scans, BUT,
they can also be used by non-clustered indexes to avoid doing bookmark
lookups. Your best bet is to experiment a little, compare the execution
plan of your query when different indexes are available, see what
performs best given your data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy's given some nice samples here. I'll just chime in with a little extra
which might help you understand.
Ideally, you want the columns that are being filtered in the query + those
being returned in the select list in the index. If you have many different
queries that run against the table, you might need a few different indexes
to accommodate their various requirements. Don't be afraid of adding more
than one column to an index & also don't be afraid of adding multiple
indexes to a table.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4506C241.8090806@.realsqlguy.com...
> jumblesale wrote:
>> Hello,
>> I am currently working on some database tables that are suffering from
>> performance issues and would like some advice on which indexes are best
>> to use.
>> There exists a composite key on three fields out of 7 in the table and
>> these three fields are the ones normally used when searching the table
>> as well.
>> 1) As most queries return only single answers, would it be better to
>> add three seperate indexes or a composite index?
>> 2) Also, from my research, most people indicate using composite index
>> would be better - is this the case?
>> 3) If I use a composite index, would it be used when searching using
>> only one or two of the composite fields?
>> Thanks in advance,
>> j
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to get
> Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to get
> Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the table,
> using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to get
> the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action, depending
> on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||ha! sorry for backing you into a corner there - good advice though,
i'll have to get working on the indexes and check the execution plans -
just wanted a bit of advise before I start playing about on the server.
Thanks for your ideas,
j
Tracy McKibben wrote:
> I'm going to have to used the canned response of "it depends" on this
> one. *GENERALLY* clustered indexes perform best on range scans, BUT,
> they can also be used by non-clustered indexes to avoid doing bookmark
> lookups. Your best bet is to experiment a little, compare the execution
> plan of your query when different indexes are available, see what
> performs best given your data.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hi Tracy
Re >*GENERALLY* clustered indexes perform best on range scans<
I'd love to hear your thoughts on this blog I did yesterday.
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/11/365.aspx
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4506C596.30004@.realsqlguy.com...
> jumblesale wrote:
>> Cheers for your reply - that's great in regards to the composite index
>> issue i'm having, but in these kinds of scenarios, would I be right in
>> thinking that a non-clustered index covering the three columns would be
>> of more use than a clustered index because I am using queries that
>> bring back small numbers (usually one) result?
> I'm going to have to used the canned response of "it depends" on this one.
> *GENERALLY* clustered indexes perform best on range scans, BUT, they can
> also be used by non-clustered indexes to avoid doing bookmark lookups.
> Your best bet is to experiment a little, compare the execution plan of
> your query when different indexes are available, see what performs best
> given your data.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Greg Linwood wrote:
> Hi Tracy
> Re >*GENERALLY* clustered indexes perform best on range scans<
> I'd love to hear your thoughts on this blog I did yesterday.
> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/11/365.aspx
>
I have to admit, I've never actually compared clustered vs.
non-clustered indexes in the detail that you have here. I'm guilty of
simply repeating what I've picked up from other sources. Your analysis
makes perfect sense to me, but I'm curious... Your example assumes
we're returning a subset of columns from every row in the table.
Suppose our table contains a million rows, clustered on a "SessionID"
column. Session ID's increment throughout the day. A typical session
contains 100-150 rows, and I want to return every column from the table
for the five sessions that occurred yesterday. Without having hard
numbers to back me up, it seems like it the clustered index wins in this
case. Or would you not consider this to be a "range" scan?
I think it's going to depend on how wide your query is and what sort of
data you're pulling back - can it be covered with a non-clustered index?
Either way, it's a well written article and it certainly has me thinking...
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Greg,
Just a quick question - because I don't have a unique primary key and
require the use of a composite key on the table, would it potentially
cause a performance hit if I use composite NCIX only and have no CIX on
the table?
Cheers for your posts btw - very interesting blog article too - it
confirmed some tests I thought i'd done incorrectly in a sql refresher
course I attended last year!
j
Greg Linwood wrote:
> Hi Tracy
> Re >*GENERALLY* clustered indexes perform best on range scans<
> I'd love to hear your thoughts on this blog I did yesterday.
> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/11/365.aspx
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
>|||jumblesale wrote:
> ha! sorry for backing you into a corner there - good advice though,
> i'll have to get working on the indexes and check the execution plans -
> just wanted a bit of advise before I start playing about on the server.
>
I spend most of my time sitting in the corner... :-)
There's just no way to give a definitive answer without having intimate
knowledge of the data involved, and being able to work with it
first-hand. Good luck!
Tracy McKibben
MCDBA
http://www.realsqlguy.com