Database Interview: What is fillfactor? What is the use of it ? What happens when we ignore it? When you should use low fill factor?
When you create a clustered index, the data in the table is stored in
the data pages of the database according to the order of the values in
the indexed columns. When new rows of data are inserted into the table
or the values in the indexed columns are changed, Microsoft® SQL
Server™ 2000 may have to reorganize the storage of the data in the
table to make room for the new row and maintain the ordered storage of
the data. This also applies to nonclustered indexes. When data is
added or changed, SQL Server may have to reorganize the storage of the
data in the nonclustered index pages. When a new row is added to a
full index page, SQL Server moves approximately half the rows to a new
page to make room for the new row. This reorganization is known as a
page split. Page splitting can impair performance and fragment the
storage of the data in a table.
When creating an index, you can specify a fill factor to leave extra
Database Interview: Difference between Index defrag and Index rebuild?
When you create an index in the database, the index information used
by queries is stored in index pages. The sequential index pages are
chained together by pointers from one page to the next. When changes
are made to the data that affect the index, the information in the
index can become scattered in the database. Rebuilding an index
reorganizes the storage of the index data (and table data in the case
of a clustered index) to remove fragmentation. This can improve disk
performance by reducing the number of page reads required to obtain
the requested data
DBCC INDEXDEFRAG - Defragments clustered and secondary indexes of the
specified table or view.
