Fill Factor

The never ending debate — what should your fill factors be set to?  There is no one set answer, but there are some things you can do to at least get yourself a little more performance depending on how the environment is set up for you.

I came into a position and the reporting setup had a *ton* of indexes already set to a fill factor of 90.  The person who decided to go the 90 route no longer was employed and the reporting team just continued the tradition because they didn’t know any better.

A quick query to run on the databases in question — this will restrict it to clustered indexes that are not set to be full:

SELECT
sys.tables.name as TableName
, sys.indexes.name as IndexName
, fill_factor
, i.rows
FROM sys.indexes
INNER JOIN sys.tables ON sys.tables.object_id = sys.indexes.object_id
INNER JOIN sys.partitions AS i ON sys.tables.object_id = i.object_id
AND i.index_id = 1
where fill_factor between 1 and 99 and sys.indexes.type_desc = ‘clustered’
ORDER BY
sys.tables.name,
sys.indexes.name

In my case, I found a couple 20 million row tables [on development] to experiment with — just wanted to confirm that changing it would improve performance.  First, you have to find out how the procedures that are hitting that table run to before you make a change.  Using this query, I found a couple stored procedures and tested their performance:

SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE [text] LIKE ‘%InsertNameOfTableHere%’
AND OBJECTPROPERTY(id, ‘IsProcedure’) = 1
GROUP BY OBJECT_NAME(id)

In my case — I like to turn stats on so I can get some real values:

SET STATISTICS IO ON
GO

SET STATISTICS TIME ON
GO

exec ProcedureNameHere

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO

Make a note of the cpu/elapsed times as well as the reads for the table in question.  In my case
SQL Server Execution Times:
CPU time = 2871 ms, elapsed time = 61580 ms.

SQL Server Execution Times:
CPU time = 3385 ms, elapsed time = 62395 ms.

Table ‘tblNamehere’. Scan count 1237, logical reads 144961, physical reads 538, read-ahead reads 63937, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

I altered the fill factor
ALTER INDEX [PK_tblNameHere_Schedule] ON [dbo].[tblNameHere] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)

After about thirty seconds [it depends upon the size of the table], it was finished.

I then run the stored procedure again and get:
SQL Server Execution Times:
CPU time = 2855 ms, elapsed time = 45136 ms.

SQL Server Execution Times:
CPU time = 3323 ms, elapsed time = 45718 ms.

Table ‘tblNameHere’. Scan count 1237, logical reads 126408, physical reads 459, read-ahead reads 63760, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

That is a pretty significant difference for a very minor change.  We are not done, though  — you do want to do some diligence with your nightly ReOrg process.  Hopefully you are using Ola Hallengren’s solution.  Make sure you turn on the LogToTable=’Y’ parameter, this way when reorg’s happen, you have a log of it.  You can turn this off after a while, but it is pretty nice to have so that you can prove out your work.

After you have changed some fill factors, wait for the nightly Reorg to happen a couple of times.  Then run this query [change date as necessary]:
select databasename, objectname, indexname, count(1)
from commandlog
where commandtype <> ‘update_statistics’
and starttime > ‘2014-05-09 20:00:01.240’
group by databasename, objectname, indexname
having count(1) > 1
order by count(1) desc

This will give you a listing of all of the indexes that were rebuilt more than once — if the ones you changed continue showing up, lower the fill factor by ~2 or so until you get to the point it isn’t having to reorganize it nightly.

You should also try to keep an eye on page splits although it can be difficult to track them down.  Jonathan Kehayias has a great posting on using Extended Events to track them here

Questions? Comments?  Post!

Advertisements

Tags: , , ,

2 responses to “Fill Factor”

  1. mmcdonald says :

    You are using REBUILD & REORGANIZE interchangeably but they are not the same.

    To get a count of the rebuilds use:

    select DatabaseName, ObjectName, IndexName, count(1)
    from CommandLog
    where Command like ‘%REBUILD%’
    and StartTime > ‘2014-09-09 20:00:01.240’
    group by DatabaseName, ObjectName, IndexName
    having count(1) > 1
    order by count(1) desc

    • mmcdonald says :

      No edit feature….

      Reorgs

      select DatabaseName, ObjectName, IndexName, count(1)
      from CommandLog
      where Command like ‘%REORGANIZE%’
      and StartTime > ‘2014-09-09 20:00:01.240’
      group by DatabaseName, ObjectName, IndexName
      having count(1) > 1
      order by count(1) desc

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: