Index Optimization Tips--SQL Server

[ragav...@gmail.com]

****************************
Index Optimization Tips
****************************

·Consider creating index on column(s) frequently used in the WHERE,
ORDER BY, and GROUP BY clauses.

These column(s) are best candidates for index creating. You should
analyze your queries very attentively to avoid
creating not useful indexes.

·Keep your indexes as narrow as possible.

Because each index take up disk space try to minimize the index key's
size to avoid using superfluous disk space.
This reduces the number of reads required to read the index and boost
overall index performance.

·Drop indexes that are not used.

Because each index take up disk space and slow the adding, deleting,
and updating of rows, you should drop indexes
that are not used. You can use Index Wizard to identify indexes that
are not used in your queries.

·Try to create indexes on columns that have integer values rather
than character values.

Because the integer values usually have less size then the characters
values size (the size of the int data type
is 4 bytes, the size of the bigint data type is 8 bytes), you can
reduce the number of index pages which are used
to store the index keys. This reduces the number of reads required to
read the index and boost overall index
performance.

·Limit the number of indexes, if your application updates data very
frequently.

Because each index take up disk space and slow the adding, deleting,
and updating of rows, you should create new
indexes only after analyze the uses of the data, the types and
frequencies of queries performed, and how your
queries will use the new indexes. In many cases, the speed advantages
of creating the new indexes outweigh the
disadvantages of additional space used and slowly rows modification.
However, avoid using redundant indexes,
create them only when it is necessary. For read-only table, the number
of indexes can be increased.

·Check that index you tried to create does not already exist.

Keep in mind that when you create primary key constraint or unique key
constraints SQL Server automatically
creates index on the column(s) participate in these constraints. If
you specify another index name, you can create
the indexes on the same column(s) again and again.

·Create clustered index instead of nonclustered to increase
performance of the queries that return a range of
values and for the queries that contain the GROUP BY or ORDER BY
clauses and return the sort results.

Because every table can have only one clustered index, you should
choose the column(s) for this index very
carefully. Try to analyze all your queries, choose most frequently
used queries and include into the clustered
index only those column(s), which provide the most performance
benefits from the clustered index creation.

·Create nonclustered indexes to increase performance of the queries
that return few rows and where the index has good
selectivity.

In comparison with a clustered index, which can be only one for each
table, each table can have as many as
249 nonclustered indexes. However, you should consider nonclustered
index creation as carefully as the clustered
index, because each index take up disk space and drag on data
modification.

·Create clustered index on column(s) that is not updated very
frequently.

Because the leaf node of a nonclustered index contains a clustered
index key if the table has clustered index,
then every time that a column used for a clustered index is modified,
all of the nonclustered indexes must also
be modified.

·Create clustered index based on a single column that is as narrow as
possibly.

Because nonclustered indexes contain a clustered index key within
their leaf nodes and nonclustered indexes use
the clustered index to locate data rows, creating clustered index
based on a single column that is as narrow as
possibly will reduce not only the size of the clustered index, but all
nonclustered indexes on the table also.

·Avoid creating a clustered index based on an incrementing key.

For example, if a table has surrogate integer primary key declared as
IDENTITY and the clustered index was created
on this column, then every time data is inserted into this table, the
rows will be added to the end of the table.
When many rows will be added a "hot spot" can occur. A "hot spot"
occurs when many queries try to read or write
data in the same area at the same time. A "hot spot" results in I/O
bottleneck.

Note. By default, SQL Server creates clustered index for the primary
key constraint. So, in this case, you
should explicitly specify NONCLUSTERED keyword to indicate that a
nonclustered index is created for the primary key constraint.

·Create a clustered index for each table.

If you create a table without clustered index, the data rows will not
be stored in any particular order. This
structure is called a heap. Every time data is inserted into this
table, the row will be added to the end of the
table. When many rows will be added a "hot spot" can occur. To avoid
"hot spot" and improve concurrency, you
should create a clustered index for each table.

·Don't create index on column(s) which values has low selectivity.

For example, don't create an index for columns with many duplicate
values, such as "Sex" column (which has only
"Male" and "Female" values), because in this case the disadvantages of
additional space used and slowly rows
modification outweigh the speed advantages of creating a new index.

·If you create a composite (multi-column) index, try to order the
columns in the key as to enhance selectivity, with the
most selective columns to the leftmost of the key.

The order of the columns in a composite (multi-column) index is very
important. This can increase the chance the
index will be used.

·If you create a composite (multi-column) index, try to order the
columns in the key so that the WHERE clauses of the
frequently used queries match the column(s) that are leftmost in the
index.

The order of the columns in a composite (multi-column) index is very
important. The index will be used to evaluate
a query only if the leftmost index key's column are specified in the
WHERE clause of the query. For example, if
you create composite index such as "Name, Age", then the query with
the WHERE clause such as "WHERE Name = 'Alex'"
will use the index, but the query with the WHERE clause such as "WHERE
Age = 28" will not use the index.

·If you need to join several tables very frequently, consider
creating index on the joined columns.

This can significantly improve performance of the queries against the
joined tables.

·Consider creating a surrogate integer primary key (identity, for
example).

Every table must have a primary key (a unique identifier for a row
within a database table). A surrogate primary
key is a field that has a unique value but has no actual meaning to
the record itself, so users should never see
or change a surrogate primary key. Some developers use surrogate
primary keys, others use data fields themselves
as the primary key. If a primary key consists of many data fields and
has a big size, consider creating a
surrogate integer primary key. This can improve performance of your
queries.

·Consider creating the indexes on all the columns, which referenced
in most frequently used queries in the WHERE clause
which contains the OR operator.

If the WHERE clause in the query contains an OR operator and if any of
the referenced columns in the OR clause are
not indexed, then the table or clustered index scan will be made. In
this case, creating the indexes on all such
columns can significantly improve your queries performance.

·If your application will perform the same query over and over on the
same table, consider creating a covering index
including columns from this query.

A covering index is an index, which includes all of the columns
referenced in the query. So the creating covering
index can improve performance because all the data for the query is
contained within the index itself and only the
index pages, not the data pages, will be used to retrieve the data.
Covering indexes can bring a lot of
performance to a query, because it can save a huge amount of I/O
operations.

·Use the DBCC DBREINDEX statement to rebuild all the indexes on all
the tables in your database periodically (for example,
one time per week at Sunday) to reduce fragmentation.

Because fragmented data can cause SQL Server to perform unnecessary
data reads and the queries performance against
the heavy fragmented table can be very bad, you should periodically
rebuild all indexes to reduce fragmentation.
Try to schedule the DBCC DBREINDEX statement during CPU idle time and
slow production periods.

·Use the DBCC INDEXDEFRAG statement to defragment clustered and
secondary indexes of the specified table or view.

The DBCC INDEXDEFRAG statement is a new SQL Server 2000 command, which
was not supported in the previous versions.
Unlike DBCC DBREINDEX, DBCC INDEXDEFRAG does not hold locks long term
and thus will not block running queries or
updates. So, try to use the DBCC INDEXDEFRAG command instead of DBCC
DBREINDEX, whenever possible.

·Consider using the SORT_IN_TEMPDB option when you create an index
and when tempdb is on a different set of disks than
the user database.

The SORT_IN_TEMPDB option is a new SQL Server 2000 feature, which was
not supported in the previous versions. When
you create an index with the SORT_IN_TEMPDB option, SQL Server uses
the tempdb database, instead of the current
database, to sort data during the index creation. Using this option
can reduce the time it takes to create an
index, but increases the amount of disk space used to create an index.

·Use the SQL Server Profiler Create Trace Wizard with "Identify Scans
of Large Tables" trace to determine which tables in
your database may need indexes.

This trace will show which tables are being scanned by queries instead
of using an index.