Configuring Fill Factor [SQL SERVER 2012]

Configuring Fill Factor

        Fill factor determines the percentage of space on each leaf-level page that is filled with data when an index is created or rebuilt. The remaining space is used for future growth.

      Fill factor is configured as a percentage value between 1 and 100. The server-wide default is 0. Fill factor can be configured at the individual index level and at the server level. 

       You can set the instance-wide fill factor value by using the sp_configure stored procedure.
You can change the fill factor only on an instance-wide basis when the advanced options
value is set to 1. For example, to set the instance-wide fill factor configuration option to 90,
use the following code:

sp_configure 'show advanced options', 1;

GO                          

RECONFIGURE;

GO

sp_configure 'fill factor', 90;

GO

RECONFIGURE;

GO

Designing and Managing Filegroups

SQL Server databases have three types of files.

PRIMARY FILEGROUP

Each database has a primary filegroup. This filegroup hosts the primary data file and any secondary files that you have not allocated to other filegroups. The system tables are hosted in the primary filegroup. You can create filegroups to host data files together for reasons including data allocation, administrative, and placement.

Primary data files use the mdf extension.

SECONDARY FILEGROUP

Secondary data files use the .ndf extension.

You can assign these secondary files to different filegroups. Secondary data files that are hosted on different volumes can be assigned to the same filegroup. When you create a table or an index, you can configure it to use a specific filegroup. When a filegroup contains more than one file, the Database Engine will write data across the files proportionally, depending on how much free space is available in each file.

Adding New FILEGROUP

To add a new filegroup to a database by using SQL Server Management Studio, perform the following steps:

1. In SQL Server Management Studio, right-click the database to which you want to add the filegroup and then choose Properties.
   
2. On the Filegroups page, click Add and then enter the name of the new filegroup,. You can add new files to a filegroup on the Files page in the Database Properties dialog box.
   
3. You can use the ALTER DATABASE statement with the ADD FILEGROUP option to add filegroups to a database.
   

To add a new filegroup named Tertiary to the AdventureWorks2012 database, use the following query:

USE [master]
GO

ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [Tertiary]
GO

Moving an Index from One Filegroup to Another

To move an index to a different filegroup or partition scheme in SQL Server Management Studio, perform the following steps:

1. In SQL Server Management Studio, right-click the index that you want to move to a new filegroup and then choose Properties.
   
2. On the Storage page, use the Filegroup drop-down list to select the filegroup to which you want to move the index.
   

You can move indexes under the following conditions:

• You cannot move indexes created using a unique or primary key constraint through SQL Server Management Studio. You can move these indexes by using the CREATE INDEX statement with the (DROP_EXISTING=ON) option.
• If the table or index is partitioned, you must select the partition scheme in which to move the index.
• You can move clustered indexes by using online processing, allowing user access to data during the move.

To move an index by using Transact-SQL, use the CREATE INDEX statement with the DROP_EXISTING = ON option and specify the target filegroup.