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 uate 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.
