- General concepts
- Optimization tips
- Literature
General concepts
In this article I want to show how you can improve the speed of your
queries by choosing the proper indexes, what kinds of indexes MS SQL
supports and what is the advantage and disadvantage of using indexes
in particular situation.
There are clustered and nonclustered indexes. A clustered index is a
special type of index that reorders the way records in the table are
physically stored. Therefore table can have only one clustered index.
The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical
order of the index does not match the physical stored order of the
rows on disk. The leaf nodes of a nonclustered index does not consist
of the data pages. Instead, the leaf nodes contain index rows.
When you create clustered or nonclustered index you can set the fill
factor option to specify how full SQL Server should make each index
page. When there is no free space to insert new row on the index page,
SQL Server will create new index page and transfer some rows from the
previous page to the new one. This operation is called page splits.
You can reduce the number of page splits by setting the appropriate
fill factor option to reserve free space on each page of the index.
The fill factor is a value from 1 through 100 that specifies the
percentage of the index page to be left empty.
The default value for fill factor is 0. It is treated similarly to a
fill factor value of 100, the difference in that SQL Server leaves
some space within the upper level of the index tree for FILLFACTOR = 0.
The fill factor percentage is used only at the time the index is created.
A covering index is a index, which includes all of the columns referenced
in the query. So the index contains the data you are looking for and SQL
Server does not have to look up the actual data in the table.
When you perform many data modification operations (INSERT, UPDATE or
DELETE statements), then the table fragmentation occurs. If you want to
determine the level of fragmentation, you can run the DBCC SHOWCONTIG
command.
Optimization tips
Every index increases the time in takes to perform INSERTS, UPDATES
and DELETES, so the number of indexes should not be very much.
Try to use maximum 4-5 indexes on one table, not more. If you
have
read-only table, then the number of indexes may be
increased.
Keep your indexes as narrow as possible. This reduces the size of the
index and reduces the number of reads required to read the
index.
Try to create indexes on columns that have integer values rather than
character values.
If you create a composite (multi-column) index, the order of the columns
in the key are very important. Try to order the columns in the
key as to
enhance selectivity, with the most selective columns to the
leftmost of
the key.
If you want to join several tables, try to create surrogate integer keys
for this purpose and create indexes on their columns.
Create surrogate integer primary key (identity for example) if your
table will not have many insert operations.
Clustered indexes are more preferable than nonclustered, if you need to
select by a range of values or you need to sort results set with
GROUP BY
or ORDER BY.
If your application will be performing the same query over and over on
the same table, consider creating a covering index on the
table.
You can 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.
You can use sp_MSforeachtable undocumented stored procedure to rebuild
all indexes in your database. Try to schedule it to execute
during CPU
idle time and slow production periods.
sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
Literature
1. SQL Server Books Online.
2. Microsoft SQL Server 7.0 Performance Tuning Guide
http://msdn.microsoft.com/library/techart/msdn_sql7perftune.htm
3. SAP R/3 Performance Tuning Guide for Microsoft SQL Server 7.0
http://msdn.microsoft.com/library/techart/sql7sapr3.htm
»
See All Articles by Columnist Alexander Chigrik