Index Maintenance
An
index is a B-tree structure that consists of all the entries from the
table corresponding to the index key. Values within an index are stored
on index pages according to the sort order specified for the index.When a new row is added to the table, before the operation can complete, SQL Server must add the value from this new row to the correct location within the index. Each time SQL Server writes to the table it must also perform a write operation to any affected index.
If the leaf-level index page does not have room for the new
value, SQL Server has to perform a page split and write half the rows
from the full page to a newly allocated page. If this also causes an
intermediate-level index page to overflow, a page split occurs at that
level as well. If the new row also causes the root page to overflow, the
root page is split into a new intermediate level, creating a new root
page. Indexes can improve query performance, but each index created also
causes performance degradation on all INSERT, UPDATE, DELETE, BULK INSERT, and BCP operations. Therefore, you need to balance the number of indexes carefully for optimal operations.
Distribution Statistics
QUERY OPTIMIZER
The
component that is responsible for determining whether an index should
even be used to satisfy a query is called the query optimizer. The query
optimizer decides whether or not to use an index based on the
distribution statistics that are stored for the index.
HISTOGRAM
When an index is created, SQL Server generates a structure called a histogram that stores information about the relative distribution of data values within a column.
Index Options
FILLFACTOR specifies the percentage of free space that should be
left on the leaf level of an index during creation or rebuild. By
leaving space on the leaf level, you can write a small number of rows to
a leaf-level page before a page split is required, thereby slowing the
rate of fragmentation for an index.
FILLFACTOR applies only to the leaf level of the index.
Intermediate-level pages (if applicable) and the root page are filled
to near capacity. During the creation of an index, all the data values
for the index key are read. After these values are read, SQL Server
creates a series of internal work tables to sort the values prior to
building the B-tree structure.
By default, the work tables are created in the same database as the index. You can specify the SORT_IN_TEMPDB option, which causes the work tables for sort operations to be generated in the tempdb database,
By default, the work tables are created in the same database as the index. You can specify the SORT_IN_TEMPDB option, which causes the work tables for sort operations to be generated in the tempdb database,
Online Index Creation
Indexes can be created either online or off-line.
Offline Index Creation
When an index is created using WITH ONLINE = OFF option, SQL Server locks the entire table, preventing any changes until the index is created.
To build a clustered index off-line, the table is locked and does not allow select statements or data modifications.
Offline Index Creation
When an index is created using WITH ONLINE = OFF option, SQL Server locks the entire table, preventing any changes until the index is created.
To build a clustered index off-line, the table is locked and does not allow select statements or data modifications.
If you build a non
clustered index off-line,a shared table lock is acquired, which allows select statements but not data modification.
Default option of index is ONLINE = OFF.
Default option of index is ONLINE = OFF.
Online Index Creation
When an index is created using the ONLINE = ON option, SQL Server allows changes to the table during the creation of the index by using the version store within the tempdb database.
When an index is created using the ONLINE = ON option, SQL Server allows changes to the table during the creation of the index by using the version store within the tempdb database.
