You manage a large database named Sales. The Sales database contains a table named OrderDetails,
which is a heavily transacted table with frequent inserts. Indexes in the table often become
fragmented due to excessive page splitting. You want to minimize the amount of fragmentation due
to page splits. What should you do?

A.
Update the statistics on the indexes.

B.
Change the fillfactor for the indexes to 100.

C.
Change the fillfactor for the indexes to 60.

D.
Change the fillfactor for the indexes to 0.

Explanation:
FILLFACTOR specifies a percentage that indicates how much free space will be in the leaf level of
each index page. When a clustered index is
created on a table, SQL Server does not stuff data wherever it finds space, but it physically
rearranges data in order. SQL Server cannot
rearrange data without page split if it does not find free space at an index page. To help SQL Server
accomplish this, there is a need to leave
a little space at the leaf level of each page on a clustered index. This free space is called FILLFACTOR.
The fillfactor determines how much empty space is left in the indexes. Once a page is full, it must
split to accommodate additional data.
However, excessive splitting causes the indexes to be fragmented and can significantly impact
performance. The fillfactor can be modified with
a statement like this:
ALTER INDEX ALL ON dbo.OrderDetails REBUILD WITH (FILLFACTOR = 60);
FILLFACTOR =60 tells SQL Server to fill the page to 60 percent full with 30 percent free space.
Answer D and B are incorrect. Fillfactor values of 0 and 100 are both the same. This indicates that
the indexes fill the pages with only
one empty row. As soon as a second insert is performed, the page will split.
Answer A is incorrect. Statistics are used by the database engine to determine how useful an index
may be for a query, but updating
the index would not reduce the fragmentation.