What should you set from the index options?

You have an index for a table in a SQL Azure database. The database is used for Online
Transaction Processing (OLTP).
You discover that the index consumes more physical disk space than necessary.
You need to minimize the amount of disk space that the index consumes.
What should you set from the index options?

A.
STATISTICS_NORECOMPUTE = OFF

B.
FILLFACTOR = 80

C.
FILLFACTOR = 0

D.
STATISTICS_NORECOMPUTE = ON

Explanation:

http://msdn.microsoft.com/en-us/library/ms177459.aspx
http://msdn.microsoft.com/en-us/library/ms188783.aspx

Show Hint

← Previous question

Next question →

Leave a Reply 5

Jai

Fillfactor=0 is equal to FillFactor=100, means more space. I think it should by FILLFactor=80

zzz

Jai, you are wrong. FillFactor less than 100 will lead to more pages created in the index cause those are not filled up to 100%. Which will lead to more space consumption.

Answer “C” is correct.

Jai

Agree with you ZZZ.. Aim is to reduce hard disk so by opting C, more index leaves will be fitted in page. Eventually would save HDD

malakosa

C is correct – FILLFACTOR = 0 means that pages will be filled in 100% and no empty space will be left.

The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.

ryahan

see the issue i have here is with the context it says you are in a OLTP database meaning a lot of insert updates etc… it s never the best solution to use a 0 or 100 % fill factor in those conditions. because of the fragmentation . it would have been an Olap then fine.