FillFactor of an Index – Slow down fragmentation

What is FillFactor?

The FILLFACTOR option for an index determines the percentage of free space that is reserved on each leaf level page of an index when it is created or rebuilt. This free space leaves room on the page for values to be added – and so is a must on volatile tables. Creating an appropriate fillfactor reduces the rate at which a page splits and therefore the rate at which a table fragments.

The server wide default FILLFACTOR is 0, which means that the leaf level pages are filled to capacity.

Index Structure

image

  • The Leaf level of a clustered index is the data itself, each leaf level entry being a row on the table.
  • For a non clustered index, it is the leaf level which contains one entry per row. The entry consists of the index key columns, optional included columns, and the bookmark/pointer.

Changing Fillfactor

To change fillfactor at the server level, so that all future indexes are created with the same fillfactor:

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'fill factor', 90
GO
RECONFIGURE
GO

This can also be done in the SSMS GUI:

image

To Change fillfactor at index level:

USE MyDB;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80);
GO

PAD_INDEX option

This option specifies index padding. When turned ON, it uses the percentage specified by FILLFACTOR is applied to the intermediate-level and root level pages of an index.

Obviously this should only be considered if there are a lot of root and intermediate level pages:

SELECT OBJECT_NAME(P.OBJECT_ID) AS 'Table'
     , I.name AS 'Index'
     , P.index_id AS 'IndexID'
     , P.index_type_desc 
     , P.index_level  
     , P.page_count 
  FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('order_record'), 2, NULL, 'DETAILED') P
  JOIN sys.indexes I ON I.OBJECT_ID = P.OBJECT_ID 
                    AND I.index_id = P.index_id; 

image

Things to be aware of

Creating or altering a clustered index with a FILLFACTOR value affects the amount of storage space the data occupies, because the Database Engine redistributes the data when it creates the clustered index.

Identifying Page Splits

Ok – so you find that your database has no fillfactor and the default is set to 0 (or 100%). The next logical step is to find out if your database is suffering from page splits. you can either run Performance Monitor and check for “SQL Server:Access Methods\Page Splits/sec” but this wont return granular data as to which indexes are most affected or do some clever reading of the transaction log and look for ‘LOP_DELETE_SPLIT’ events and aggregate.

select Operation, AllocUnitName, COUNT(*) as NumberofIncidents
from   ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by Operation, AllocUnitName

image


This is a good starting point to consider fillfactor.

Advertisements

Extended Database Properties

When there are hundreds of databases in a SQL Server estate, its good to make use of extended properties and give each database a description. When some of these databases rarely get looked at, and others are automatically named through an application install, it can become difficult to manage and to handover.

Just as an example, when installing Microsoft Lync 2010, it creates 11 databases (depending on the install) and some are inconspicuously named rtc & xds.

To create an extended Property with description information:

USE rtc;
GO
EXEC sys.sp_addextendedproperty 
@name = N'Database Description', 
@value = N'Microsoft Lync 2010 - Persistent user data (for example, ACLs, contacts, home server or pool, scheduled conferences)';
GO

The following example displays all extended properties set on the database itself.

SELECT objtype, objname, name, value
FROM fn_listextendedproperty
(default, default, default, default, default, default, default);

image

Unused Indexes – Identifying and Removing

Ive blogged about this previously in the general context of Index Management but ive created a more specific query for unused indexes which lets me quickly identify which indexes are not being used or have never been used in a database. This query goes a step further to find how much space could be saved and creates the dynamic sql to drop the indexes.

SELECT
    DB_NAME(us.[database_id]) as [DB Name], 
    OBJECT_NAME(us.[object_id]) as [Name],         
    i.name as [Index Name], 
    i.index_id, p.rows as [#Records],
    (a.total_pages * 8) / 1024 as [Reserved(mb)],
    (a.used_pages * 8)/ 1024 as [Used(mb)],
    us.user_seeks, us.user_scans, us.user_lookups, 
    us.user_updates,us.last_user_seek, 
    us.last_user_scan, us.last_user_lookup, us.last_user_update,
    'DROP INDEX ' + i.name + ' ON ' + OBJECT_NAME(us.[object_id]) AS [Script]
FROM    
    sys.dm_db_index_usage_stats us
    inner join sys.indexes AS i 
        ON (us.index_id = i.index_id and us.object_id = i.object_id)
    inner join sys.partitions AS p 
        ON i.object_id = p.object_id and i.index_id = p.index_id 
    inner join sys.allocation_units AS a 
        ON p.partition_id = a.container_id
WHERE    
    us.database_id = dB_id('MyDB')
    and (us.last_user_seek is NULL 
        and us.last_user_scan is NULL 
        and us.last_user_lookup is NULL)
    and us.index_id >  1        -- no primary keys
ORDER BY 
    OBJECT_NAME(us.[object_id]), i.index_id

Enable/Disable FK Constraints for reloading data

Scenario: Several tables in a database become corrupt (i.e. updates have happened on the tables which cant be backed out). You have a backup copy of the table and want to overwrite the existing tables with the backup copy.

If its a normalised database to some form, then there may be some foreign key constraints in the tables you want to overwrite. There are a couple of easy steps below which can be used to disable and enable constraints.

Script to Disable\Enable FK Constraints

SELECT 
'ALTER TABLE ' + OBJECT_NAME(FKEYID) + ' NOCHECK CONSTRAINT ' 
+ OBJECT_NAME(CONSTID) as 'DISABLE',
'ALTER TABLE ' + OBJECT_NAME(FKEYID) + ' CHECK CONSTRAINT ' 
+ OBJECT_NAME(CONSTID) as 'ENABLE',
(CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
WHERE OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0
--Uncomment if you want to narrow down to constraints for a specific table
--AND (OBJECT_NAME(FKEYID) = 'SU_HELP_CENTRE' 
--    OR OBJECT_NAME(RKEYID) = 'SU_HELP_CENTRE')
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

The first column gives the disable dynamic SQL for all enabled constraints in a database.

The Second column gives the enable dynamic SQL so that you can revert the change back and only enable the constraints that were originally enabled.

Ensure you copy out both dynamic SQL scripts before continuing.

1. Run the Disable constraints script

2. Delete data from the affected tables that you want to overwrite

3. Import the backup data into the destination tables

4. Run the Enable constraints script

Why disable constraints at all?

To prevent getting integrity check errors against the constraints on the tables you are re-importing

Things to be aware of…

  • If re-importing data, make sure you re-import a consistent snapshot of the data, and you import all the tables that may be involved in a particular relationship – this is to prevent data integrity issues.
  • It might be said that its better to restore from a backup – but for the scenario we faces, we knew the relationship was contained to 3 tables, so we could easily manage integrity. Restoring the whole database wasn’t an option as only part of the data set was compromised while the rest was functioning ok.