Building Indexes

I have a couple of questions on indexes if someone can explain or point me t
o
a good resource that explains these type of questions in detail. We have a
database that has no more than 200,000 records, but some of our lookups take
up to 5 minutes or more. I believe the problem to be with the indexes (or
lack of).
1. Should every table have an index?
2. If a table is used as a join table for two other tables and only has
about 100 records, should it have an index, or do the other tables having
indexes the best solution.
3. If I have a SQL statement that has a where clause on three (or any other
number) different fields, should I build an index on each item separately, o
r
all three items together in one index?
If someone can please help out these questions, that would be great. Also,
if you can recommend a book (or website) that explains basic questions like
these in detail, that would be even better.
Thank you in advance for your help.> 1. Should every table have an index?
Yes, at least a clustered one.

> 2. If a table is used as a join table for two other tables and only has
> about 100 records, should it have an index, or do the other tables having
> indexes the best solution.
Create an index for every foreign key constraint. SQL Server create a unique
clustered (if you do not specify other) for primary key constraint, but no
index is created for foreign key constraints. In other words, you should hav
e
an index, in each table, by the columns participating in the join.

> 3. If I have a SQL statement that has a where clause on three (or any othe
r
> number) different fields, should I build an index on each item separately,
or
> all three items together in one index?
You can start creating an index by each column (single column), sql server
is smart enough to use then when optimizing a query. Sometime you can improv
e
the performance using a covering index (an index where the columns in the
key, cover all columns being referenced by the statement).
Here is a good place to read about indexing and I also strongly recommend
the book "Inside SQL Server 2000" written by Kalen Delaney.
http://www.sql-server-performance.c...performance.asp
Read all topics about indexes.
AMB
"Mike Collins" wrote:

> I have a couple of questions on indexes if someone can explain or point me
to
> a good resource that explains these type of questions in detail. We have a
> database that has no more than 200,000 records, but some of our lookups ta
ke
> up to 5 minutes or more. I believe the problem to be with the indexes (or
> lack of).
> 1. Should every table have an index?
> 2. If a table is used as a join table for two other tables and only has
> about 100 records, should it have an index, or do the other tables having
> indexes the best solution.
> 3. If I have a SQL statement that has a where clause on three (or any othe
r
> number) different fields, should I build an index on each item separately,
or
> all three items together in one index?
> If someone can please help out these questions, that would be great. Also,
> if you can recommend a book (or website) that explains basic questions lik
e
> these in detail, that would be even better.
> Thank you in advance for your help.|||1. It is not really necessary, but I make it a point to have a clustered
index on every table. This makes for easier maintenance of the table
structure, especially when the table is huge.
2. Generally every table has a primary key, and the primary key needs to be
supported by a clustered or nonclustered index. Having an index is never a
bad thing from a query performance point of view. But you have to see how
the index is affecting the writes to the table.
3. You don't necessarily have to build indexes on all 3 columns, as long as
the column you are indexing on is very selective. Also make sure the first
column in a composite index is the selective column. Some times you do have
situations where composite indexes are useful, where all the information
required by a query can be obtained directly from the index - these are
called covered indexes.
For general advice, check out sql-server-performance.com. Have a look at SQL
Server performance related books at:
http://vyaskn.tripod.com/sqlbooks.htm#perf
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
> I have a couple of questions on indexes if someone can explain or point me
to
> a good resource that explains these type of questions in detail. We have a
> database that has no more than 200,000 records, but some of our lookups
take
> up to 5 minutes or more. I believe the problem to be with the indexes (or
> lack of).
> 1. Should every table have an index?
> 2. If a table is used as a join table for two other tables and only has
> about 100 records, should it have an index, or do the other tables having
> indexes the best solution.
> 3. If I have a SQL statement that has a where clause on three (or any
other
> number) different fields, should I build an index on each item separately,
or
> all three items together in one index?
> If someone can please help out these questions, that would be great. Also,
> if you can recommend a book (or website) that explains basic questions
like
> these in detail, that would be even better.
> Thank you in advance for your help.|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23vD12TFnFHA.3656@.TK2MSFTNGP09.phx.gbl...
> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to b
e
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long a
s
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do hav
e
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at S
QL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
I have a quick question on indexes.
If a table has multiple indexes, will SQL Server server select only 1 index
to perform
a query or it can select more than 1 index also. Informix selects only 1 ind
ex by
analyzing statistics.|||Thanks for the reply. I have a question on number 3 though.
What is a composite index and what does it mean to make the first column the
selective column. An example would be great. Thanks again for answering.
"Narayana Vyas Kondreddi" wrote:

> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to b
e
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long a
s
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do hav
e
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at S
QL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
> to
> take
> other
> or
> like
>
>|||Mike,

> What is a composite index and what does it mean to make the first column t
he
> selective column. An example would be great. Thanks again for answering.
A composite index is one with multiple columns participating in the index
key. SQL Server stores statistics about the distribution of the values in th
e
key and in a multicolumn key, the statistics is different that the one from
a
single column key index. This paragragh is from the article I am attaching.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn.microsoft.com/library/d...asp?frame=true
*****
The multicolumn statistics for one set of columns consists of one histogram
for the first column in the statistics definition, one density value for the
first column, and an all density value for each prefix combination of column
s
(including the first column alone). Each set of statistics (a histogram and
two or more density values) is stored in one row of SYSINDEXES together with
the timestamp of the last statistics update, the number of rows in the sampl
e
used to produce the statistical information, the number of steps in the
histogram, and the average length of the key. The number of rows value
(rowcnt column) is maintained only for index number 0 or 1 (heap or clustere
d
index) and it is replicated across all indexes on the table. Similarly, the
dpages is maintained for each table and index. The statistical information i
s
empty for tables without any rows at the time of statistics collection.
*****
If the selectivity oh the most left column in the key is low, then sql
server will not perform an "index seek" operation in the index in question.
AMB
"Mike Collins" wrote:
[vbcol=seagreen]
> Thanks for the reply. I have a question on number 3 though.
> What is a composite index and what does it mean to make the first column t
he
> selective column. An example would be great. Thanks again for answering.
> "Narayana Vyas Kondreddi" wrote:
>|||A composite index is an index that consists of more than one column. For
example, if you have an index on EmpID and Surname, it would make more sense
to have the index as "EmpID, Surname", than "Surname, EmpID" - because EmpID
is more selective and has unique values.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:A568ACC8-D7FC-4A87-A31A-B7C2CAB37FC2@.microsoft.com...
Thanks for the reply. I have a question on number 3 though.
What is a composite index and what does it mean to make the first column the
selective column. An example would be great. Thanks again for answering.
"Narayana Vyas Kondreddi" wrote:

> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to
be
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long
as
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do
have
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at
SQL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:3B3579C4-4708-4A61-8AC3-985AE862B3E7@.microsoft.com...
me[vbcol=seagreen]
> to
a[vbcol=seagreen]
> take
(or[vbcol=seagreen]
having[vbcol=seagreen]
> other
separately,[vbcol=seagreen]
> or
Also,[vbcol=seagreen]
> like
>
>|||SQL Server 2000 can use multiple indexes for a single table, in the same
query. See "Designing an Index" in SQL Server Books Online for more
information.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rkusenet" <rkusenet@.yahoo.com> wrote in message
news:3lpsruF13v248U1@.individual.net...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23vD12TFnFHA.3656@.TK2MSFTNGP09.phx.gbl...
> 1. It is not really necessary, but I make it a point to have a clustered
> index on every table. This makes for easier maintenance of the table
> structure, especially when the table is huge.
> 2. Generally every table has a primary key, and the primary key needs to
be
> supported by a clustered or nonclustered index. Having an index is never a
> bad thing from a query performance point of view. But you have to see how
> the index is affecting the writes to the table.
> 3. You don't necessarily have to build indexes on all 3 columns, as long
as
> the column you are indexing on is very selective. Also make sure the first
> column in a composite index is the selective column. Some times you do
have
> situations where composite indexes are useful, where all the information
> required by a query can be obtained directly from the index - these are
> called covered indexes.
> For general advice, check out sql-server-performance.com. Have a look at
SQL
> Server performance related books at:
> http://vyaskn.tripod.com/sqlbooks.htm#perf
I have a quick question on indexes.
If a table has multiple indexes, will SQL Server server select only 1 index
to perform
a query or it can select more than 1 index also. Informix selects only 1
index by
analyzing statistics.