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 eachindex
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.