As a common practice, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases, such as an identity column or some other column where the value is unique. In many cases, the primary key is the ideal column for a clustered index.
Indexes should be measured on all columns that are frequently used in
Do not automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.
For historical (static) tables, create the indexes with a
FILLFACTOR and a
PAD_INDEX of 100 to ensure there is no wasted space. This reduces disk I/O, helping to boost overall performance.
Queries that return a single row are just as fast using a non-clustered index as a clustered index.
Queries that return a range of rows are just as fast using a clustered index as a non-clustered index.
Do not add more indexes on your OLTP tables to minimize the overhead that occurs with indexes during data modifications.
Do not add the same index more than once on a table with different names.
Drop all those indexes that are not used by the Query Optimizer, generally. You probably won't want to add an index to a table under the following conditions:
- If the index is not used by the query optimizer. Use the Query Analyzer's "Show Execution Plan" option to see if your queries against a particular table use an index or not.
- If the table is small, most likely indexes will not be used.
- If the column(s) to be indexed are very wide.
- If the column(s) are defined as
IMAGE data types.
- If the table is rarely queried but insertion, updating is frequent.
� To provide up-to-date statistics, the query optimizer needs to make smart query optimization decisions. You will generally want to leave the "Auto Update Statistics" database option on. This helps to ensure that the optimizer statistics are valid, ensuring that queries are properly optimized when they are run.
Keep the "width" of your indexes as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index.
If possible, try to create indexes on columns that have integer values instead of characters. Integer values use less overhead than character values.
If you have two or more tables that are frequently joined together, then the columns used for the joins should have an appropriate index. If the columns used for the joins are not naturally compact, then consider adding surrogate keys to the tables that are compact in order to reduce the size of the keys. This will decrease I/O during the join process, which increases overall performance.
When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index. This is because, in a unique index, each row is unique and once the needed record is found, SQL Server doesn't have to look any further.
If a particular query against a table is run infrequently and the addition of an index greatly speeds the performance of the query, but the performance of
DELETES is negatively affected by the addition of the index, consider creating the index for the table for the duration of when the query is run and then dropping the index. An example of this is when monthly reports are run at the end of the month on an OLTP application.
REAL data types as primary keys, as they add unnecessary overhead that can hurt performance.
If you want to boost the performance of a query that includes an
AND operator in the
WHERE clause, consider the following:
- Of the search criteria in the
WHERE clause, at least one of them should be based on a highly selective column that has an index.
- If at least one of the search criteria in the
WHERE clause is not highly selective, consider adding indexes to all of the columns referenced in the
- If none of the columns in the
WHERE clause are selective enough to use an index on their own, consider creating a covering index for this query.
The Query Optimizer will always perform a table scan or a clustered index scan on a table 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 (or do not have a useful index). Because of this, if you use many queries with
OR clauses, you will want to ensure that each referenced column in the
WHERE clause has an index.
If you have a query that uses
ORs and it is not making the best use of indexes, consider rewriting it as a
UNION and then testing performance. Only through testing can you be sure that one version of your query will be faster than another.
If you use the
SOUNDEX function against a table column in a
WHERE clause, the Query Optimizer will ignore any available indexes and perform a table scan.
Queries that include either the
DISTINCT or the
GROUP BY clauses can be optimized by including appropriate indexes. Any of the following indexing strategies can be used:
- Include a covering, non-clustered index (covering the appropriate columns) of the
DISTINCT or the
GROUP BY clauses.
- Include a clustered index on the columns in the
GROUP BY clause.
- Include a clustered index on the columns found in the
- Adding appropriate indexes to queries that include
GROUP BY is most important for those queries that run often.
Avoid clustered indexes on columns that are already "covered" by non-clustered indexes. A clustered index on a column that is already "covered" is redundant. Use the clustered index for columns that can better make use of it.
Ideally a clustered index should be based on a single column (not multiple columns) that are as narrow as possible. This not only reduces the clustered index's physical size, it also reduces the physical size of non-clustered indexes and boosts SQL Server's overall performance.
When you create a clustered index, try to create it as a unique clustered index, not a non-unique clustered index.
SET NOCOUNT ON at the beginning of each stored procedure you write. This statement should be included in every stored procedure, trigger, etc. that you write.
Keep Transact-SQL transactions as short as possible within a stored procedure. This helps to reduce the number of locks, helping to speed up the overall performance of your SQL Server application.
If you are creating a stored procedure to run in a database other than the Master database, don't use the prefix
sp_ in its name. This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly.
Before you are done with your stored procedure code, review it for any unused code, parameters or variables that you may have forgotten to remove while you were making changes and remove them. Unused code just adds unnecessary bloat to your stored procedures, although it will not necessarily negatively affect performance of the stored procedure.
For best performance, all objects that are called within the same stored procedure should be owned by the same object owner or schema, preferably
dbo, and should also be referred to in the format of
When you need to execute a string of Transact-SQL, you should use the
sp_executesql stored procedure instead of the
If you use input parameters in your stored procedures, you should validate all of them at the beginning of your stored procedure. This way, if there is a validation problem and the client application needs to be notified of the problem, it happens before any stored procedure processing takes place, preventing wasted effort and boosting performance.
When calling a stored procedure from your application, it is important that you call it using its qualified name, for example: