[SQL]Index–Fill Factor- simple practice

The is the difinition
http://technet.microsoft.com/en-us/library/ms177459.aspx
I use the sample DB and Table is AdventureWorks2012::person.address

It’s very important if you want to do a maintenance on your INDEX in SQL DataBase
I used two scripts to look at the Index
A. Check your Tables exists how many “Fill Factor”

--INDEX-1-Check Tables' index "Fill Factor"

Select DB_NAME() DBNames,  

OBJECT_NAME([object_id]) AS TableName,

name as IndexName,

type_desc as IndexType,

is_unique,

is_primary_key,

fill_factor,

is_padded

FROM sys.indexes

You can add two lines to exclude non-CONSTRAINT table

WHERE OBJECT_NAME([object_id]) IN

(SELECT table_name from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE)

B.Check Table INDEX total size

--INDEX-2-Check Table INDEX total size

sp_spaceused 'person.address'

<TEST>

Set up Fill Factor = 0,30,50

--0
name         rows    reserved        data            index_size    unused
Address    19614   5960 KB        2784 KB    2688 KB        488 KB
Table 'sysschobjs'. Scan count 0, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-30

name         rows        reserved       data           index_size    unused
Address    19614      6600 KB        2784 KB    3320 KB        496 KB
Table 'Address'. Scan count 1, logical reads 38, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.



Why??

Higher Fill Factor, Higher Disk Space. Higher Fragmentation, More Pages produces, More Reads

Lower Fill factor, Lower Disk Space, Lower Fragmentation, Lower Pages produces, Lower Reads