Tips for using indexes in SQL Server 2014 (Part 2)
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.
Consider disabling the indexes.
In SQL Server 2014 you can disable index. For example, it can be useful if you want to rebuild
a non-clustered index. In this case, disabling the index before rebuilding significantly reduces
the amount of disk space required. To disable index, you can use the ALTER INDEX DISABLE statement.
Note. Keep in mind that disabling an index prevents user access to the index. So, if you disable
a clustered index, the user cannot access the underlying table data.
Use a clustered columnstore index.
You can use the clustered columnstore indexes to improve data compression and query performance
for data warehousing workloads that primarily perform bulk loads and read-only queries.
Since the clustered columnstore index is updateable, the workload can perform many
insert, update, and delete operations.
Add nonkey columns in nonclustered indexes very carefully.
SQL Server 2014 supports adding nonkey columns to the leaf level of the nonclustered index.
When all columns in the query are included in the index either as key or nonkey columns, the
query performance will be significantly improved, but adding nonkey columns in nonclustered
indexes can degrade performance also. The performance degradation will occur if you frequently
use queries that not include nonkey columns, because fewer index rows will fit on an index page
and more disk space will be required to store the index.
Consider specifying the index locking granularity during creating or altering index.
SQL Server 2014 supports the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options in
CREATE INDEX and ALTER INDEX statements. These options can be used to control the level
at which locking occurs for the index.
Use the ONLINE option of the DROP INDEX statement to drop index online.
SQL Server 2014 supports the ONLINE option of the DROP INDEX statement. If you specify the
ONLINE option when dropping index, users can still access the table data and use other indexes
on the table while this index is being dropped.
Note. Keep in mind that you can drop index online only in SQL Server 2014 Enterprise, Developer,
and Evaluation editions.
Consider using a new COLUMNSTORE_ARCHIVE data compression option with the
ALTER INDEX … REBUILD statement.
You can use this option for archival, or for other situations that require a smaller data
storage size and can afford more time for storage and retrieval.
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.
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.
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.
Avoid creating index on column(s) which values has low selectivity.
For example, do not 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.
Try to create indexes on columns that have integer values rather than character values.
Because the integer values usually have less size than 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.
Consider including nonkey columns in nonclustered indexes.
SQL Server 2014 supports adding nonkey columns to the leaf level of the nonclustered index.
Nonkey columns added in nonclustered indexes allow indexes to cover more queries. When all
columns in the query are included in the index either as key or nonkey columns, the query
performance will be significantly improved.
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.
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.
Limit the number of indexes, if your application updates data very frequently.
Because each index take up disk space and slow down 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.
Consider using filtered indexes.
SQL Server 2014 supports filtered Indexes and statistics. Now you can create index on a subset
of rows in the table. For example, you can create filtered indexes for queries that select from
well-defined subsets of data, such as columns with mostly NULL values, columns with heterogeneous
categories of values, and columns with distinct ranges of values. By using filtered Indexes, you
can improve query performance, reduce index maintenance costs, and reduce index storage costs
compared with full-table indexes.
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.
When you create an index with the SORT_IN_TEMPDB option, SQL Server 2014 uses the tempdb database,
instead of the current database, to sort data during the index creation. Using this option can
reduce the time Database Engine takes to create an index, but increases the amount of disk space
used to store an index data.
Use SQL Server 2014 Database Engine Tuning Advisor to create an optimal set of indexes.
SQL Server 2014 Database Engine Tuning Advisor helps to select and create an optimal set of
indexes, indexed views, and partitions. So, you can find tables that are scanned by queries
instead of using an index.