SQLServerWiki

“The Only Thing That Is Constant Is Change”

Using the SQL Server fill factor effectively!

Posted by database-wiki on August 23, 2013

=> The idea behind this utility is to use the fill factor on indexes efficiently.

=> How to monitor fill factor with respect to the growth of data on an indexes and

recommend fill factor value depending on the data size.

=> How effective does the fill factor improves performance with INSERT/UPDATES/DELETE and SELECT.

The Approach followed:

===================

=> Capture actual fill factor values on every indexes daily.

=> Derive the recommended fill factor using the data capture for a week.

Tables Used:

===========

=> Table FillFactorTable contains the table names and the indexes for which the information will be capture on a daily basis.

Why this table? We don’t want to capture information on all the tables exists in the database.

We can go ahead and add or remove the tables which we would like to capture the information.

=> Table FillFactorInformation will capture the fill factor and related information to derive the recommended Fill factor.

Few of the Key Factors to be consider for fill factor

=========================================

Key entry – this is the value of the key, it is also called a filled index row.

Empty slot – an empty index row.

Page – this is the smallest physical container for a group of keys, there are 8 pages in an extent.

Extents can be mixed, for example, one extent may contain eight different small indexes.

Defragmentation- this may occur because of either a reorganization or rebuild of indexes.

Max_record_size_in_Bytes- the maximum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the maximum record size in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the maximum record size in the complete allocation unit.

Avg_record_size_in_bytes- Average record size in bytes

Record_count- total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the total number of records in the IN_ROW_DATA allocation unit.

Avg_fragmentation_in_percent- Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

The value is measured as a percentage and takes into account multiple files

Room for improvement.

=====================

The fillfactor required information are captured using the DMV

sys.dm_db_index_physical_stats(DB_ID(@DatabaseName) ,  OBJECT_ID(@TableName) , NULL , NULL , ‘DETAILED’ )

Which is time consuming process to capture the information, this fetches the required information. The time taken to fetch the information also depends on the size of the database, bigger the db size the more the time to fetch the information.

For more details

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

Script 1:

USE May10

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FillFactorInformation]’) AND type in (N’U’))

BEGIN

— drop table [FillFactorInformation]

CREATE TABLE [dbo].[FillFactorInformation](

ID                                    BIGINT IDENTITY(1,1),

[DatabaseName]                  NVARCHAR(128)   NOT NULL,

[TableName]                     NVARCHAR(100)   NOT NULL,

[SchemaName]                    NVARCHAR(10)    NOT NULL,

[IndexName]                     NVARCHAR(100)     NOT NULL,

[IndexKey]                      NVARCHAR(50)    NULL,

[KeySize]                       INT               NOT NULL,

[IndexBytes]                    FLOAT                   NOT NULL,

[OrginalFillFactor]             INT               NOT NULL,

[GrowthPercentage]        FLOAT                   NULL,

[FragmentationPercentage] FLOAT                   NOT NULL,

[AlterStatement]          NVARCHAR(4000)  NULL,

[RecommendedFillFactor]  INT               NULL,

[InsertUTC]                     DATETIME          NOT NULL,

CONSTRAINT [PK_FillFactorInformation] PRIMARY KEY CLUSTERED

(

[DatabaseName] ASC,

[TableName] ASC,

[SchemaName] ASC,

[IndexName] ASC,

[InsertUTC] ASC

))

END

GO

Script 2:

USE May10

GO

IF NOT EXISTS ( Select 1 From sys.sysobjects WHERE Name = ‘FillFactorTable’)

BEGIN

CREATE TABLE

FillFactorTable

(

[ID]              BIGINT IDENTITY(1,1),

[DatabaseId]      INT,

[DatabaseName]    VARCHAR(400),

[TableId]         INT,

[TableName]       VARCHAR(400),

[IndexKey]        VARCHAR(100)  NOT NULL,

[IndexName]       VARCHAR(1000) NOT NULL,

OrderID               INT

)

END

GO

Script 3:

Output:

USE may10

GO

DECLARE @Cnt INT = 0

Select @Cnt = Count(1) FROM [may10].dbo.[FillFactorTable]

IF @Cnt = 0

BEGIN

INSERT INTO [may10].dbo.[FillFactorTable]  (OrderID , DatabaseID , DatabaseName , TableId , TableName , IndexKey , IndexName )

Select DISTINCT

DENSE_RANK() OVER(  ORDER BY s.id) as OrderID,

23                                                 as DatabaseID,

‘may10’                                      as DatabaseName,

s.Id                                         as TableID,

s.name                                             as TableName,

cast(i.object_id AS VARCHAR(11))+’_’+cast(i.index_id AS VARCHAR(11)) as IndexKey,

i.name                                                                                                as IndexName

FROM

sys.indexes i

INNER JOIN

sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id

INNER JOIN

sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id

INNER JOIN

sys.sysobjects s ON c.object_id = s.ID

INNER JOIN sys.dm_db_partition_stats st ON  st.OBJECT_ID  = c.object_id

WHERE st.index_id = 1

and st.row_count > 8000000

END

GO

OUTPUT:

select * from may10.dbo.FillFactorTable

 

ID    DatabaseId  DatabaseName      TableId     TableName         IndexKey            IndexName                           OrderID

===============================================================================================================================

1     23                may10       373576369   tblStatements      373576369_1       tblStatements_StartTime             1

2     23                may10       373576369   tblStatements      373576369_10      tblStatements_BatchEndSeq           1

3     23                may10       373576369   tblStatements      373576369_11      tblStatements_BatchStmtStart        1

4     23                may10       373576369   tblStatements      373576369_2       tblStatements_EndTime               1

5     23                may10       373576369   tblStatements      373576369_3       tblStatements_HashId                1

6     23                may10       373576369   tblStatements      373576369_4       tblStatements_SessionRequestBatch   1

7     23                may10       373576369   tblStatements      373576369_5       tblStatements_StmtSeq               1

8     23                may10       373576369   tblStatements      373576369_6       tblStatements_StartEndSeq           1

9     23                may10       373576369   tblStatements      373576369_7       tblStatements_EndSeq                1

10    23                may10       373576369   tblStatements      373576369_8       tblStatements_ConnId                1

11    23                may10       373576369   tblStatements      373576369_9       tblStatements_BatchStartSeq         1

12    23                may10       405576483   tblPlanRows       405576483_1       PK_tblPlanRows                      2

Script 4:

USE may10

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tvf_GetFillFactor]’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’))

BEGIN

DROP FUNCTION [dbo].[tvf_GetFillFactor]

END

GO

CREATE FUNCTION [dbo].[tvf_GetFillFactor]

(

@KeyBytes           FLOAT,

@GrowthPercentage FLOAT

)

RETURNS INT

AS

BEGIN

DECLARE @FillFactor FLOAT

DECLARE @Rate           FLOAT

DECLARE @Offset         FLOAT

IF @KeyBytes < 2

SET @KeyBytes=2

IF @GrowthPercentage > 0.06

SET @GrowthPercentage = 0.06

IF @GrowthPercentage < 0.001

SET @GrowthPercentage = 0.001

SET @Rate=- 5.2312 * Power(@keybytes,-0.244) — R=0.95

SET @Offset=1 – 0.2193 * Power(@keybytes, – 0.462) — R = 0.99

SET @FillFactor= CEILING(100 * (@Rate * @GrowthPercentage + @Offset))

IF @FillFactor < 50

SET @FillFactor = 50

IF @FillFactor > 99

SET @FillFactor = 99

RETURN @FillFactor

END

GO

Script 5:

USE may10

GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspPub_GenerateFillFactor]’) AND type in (N’P’, N’PC’))

BEGIN

DROP PROCEDURE [dbo].[uspPub_GenerateFillFactor]

END

GO

CREATE PROCEDURE [dbo].[uspPub_GenerateFillFactor]

(@CurrentDate DateTime = null)

AS

DECLARE @RecordedAt       DATETIME

IF @CurrentDate  IS NULL

SET @RecordedAt = CONVERT(DATE , GETUTCDATE());

ELSE

SET @RecordedAt = CONVERT(DATE , @CurrentDate);

DECLARE @DatabaseId   BIGINT

DECLARE @DatabaseName NVARCHAR(100)

DECLARE @TableId      BIGINT

DECLARE @TableName    NVARCHAR(100)

DECLARE @IndexName        VARCHAR(1000)

DECLARE @FillFactorValue VARCHAR(3)

DECLARE @MySql            NVARCHAR(MAX)

DECLARE @intFlag INT

DECLARE @iRowCnt INT

DECLARE @ID INT

Select @iRowCnt = MAX(OrderID)  , @intFlag = MIN(OrderID) from [Utility].[dbo].[FillFactorTable]

WHILE (@intFlag <=@iRowCnt)

BEGIN

Select DISTINCT @DatabaseId = DatabaseId , @DatabaseName = DatabaseName , @TableId = TableId , @TableName = TableName   From [Utility].[dbo].[FillFactorTable] WHERE OrderID = @intFlag

IF @TableId  <> 0

BEGIN

INSERT INTO [Utility].[dbo].[FillFactorInformation]

([DatabaseName]

,[TableName]

,[IndexName]

,[IndexKey]

,[KeySize]

,[IndexBytes]

,[OrginalFillFactor]

,[FragmentationPercentage]

,SchemaName

,InsertUTC)

SELECT

db_name([database_id])                                                                       AS [DatabaseName],

CAST(OBJECT_NAME(S.[Object_Id]) AS VARCHAR(100))                                 AS [TableName],

i.[Name]                                                                                           AS [IndexName],

CAST(S.object_id AS VARCHAR(11))+’_’ + CAST(S.index_id AS VARCHAR(11)) AS [IndexKey],

S.[KeySize]                                                                                        AS KeySize,

S.[IndexBytes]                                                                                     AS IndexBytes,

i.fill_factor                                                                                    AS FillFactorValue,

S.[FragmentationPercentage]                                                                  AS FragmentationPercentage,

‘dbo’                                                                                              AS SchemaName,

CurrentDate                                                                                        AS InsertUTC

FROM (Select

[database_id],

[object_id],

[index_id],

max(max_record_size_in_Bytes)                                              as KeySize,

sum(avg_record_size_in_bytes * record_count)                         as IndexBytes,

Sum(avg_fragmentation_in_percent * page_count)/Sum(page_count) as FragmentationPercentage,

@RecordedAt                                                                            as CurrentDate

FROM sys.dm_db_index_physical_stats(DB_ID(@DatabaseName) ,  OBJECT_ID(@TableName) , NULL , NULL , ‘DETAILED’ ) as dd

WHERE index_type_desc IN (‘CLUSTERED INDEX’,’NONCLUSTERED INDEX’)

AND alloc_unit_type_desc=’IN_ROW_DATA’

GROUP BY [database_id],[Object_Id] ,[index_id]

HAVING SUM(page_count) > 0

) S

JOIN Sys.Indexes i

ON  i.[Object_id] = S.[Object_Id]

AND i.[Index_id]  = S.[Index_id]

END

SET @intFlag = @intFlag + 1

END

UPDATE [Utility].[dbo].[FillFactorInformation]

SET GrowthPercentage = CASE WHEN [EstStDev]*2 + [EstAvg] < 0.001 THEN

0.001

ELSE

[EstStDev]*2+[EstAvg]

END

From [Utility].[dbo].[FillFactorInformation] TT

JOIN (

SELECT

ITT.[IndexKey]                                                                                              AS IndexKey,

Stdev([IndexBytes])/Max([IndexBytes])                                                           AS [EstStDev],

(Max([IndexBytes])- Min([IndexBytes]))/Min([IndexBytes])/Count([IndexBytes]) AS [EstAvg]

FROM [Utility].[dbo].[FillFactorInformation] ITT INNER JOIN [Utility].[dbo].[FillFactorTable] AFF

ON ITT.[IndexKey]=AFF.[IndexKey]

WHERE [InsertUTC] > GetUTCDate()-7

and [IndexBytes] IS NOT NULL

and [IndexBytes] > 0

GROUP BY ITT.[IndexKey]  ) Estimates

ON [Estimates].[IndexKey]= TT.[IndexKey]

And InsertUTC = @RecordedAt

And Convert(varchar(10), [InsertUTC] ,103)  = Convert(varchar(10), @RecordedAt,103)

— ———————

CREATE TABLE #temp (TableName VARCHAR(255), IndexName VARCHAR(255), FillFactorValue VARCHAR(3) , ID BIGINT)

INSERT INTO  #temp (TableName , IndexName , FillFactorValue , ID )

SELECT

‘[dbo].[‘+TableName+’]’,

[IndexName],

ISNULL(Cast([dbo].[tvf_GetFillFactor](KeySize,GrowthPercentage)as varchar(3)) , 0 ) as FF ,

ID as ID

FROM  [Utility].[dbo].[FillFactorInformation]

WHERE [InsertUTC]= @RecordedAt

DECLARE Index_cursor CURSOR FOR

SELECT TableName ,IndexName ,FillFactorValue, ID

FROM #temp

OPEN Index_cursor

FETCH NEXT FROM Index_cursor INTO @TableName, @IndexName, @FillFactorValue , @ID

WHILE @@FETCH_STATUS = 0

BEGIN

SET @MySql = ‘ALTER INDEX [‘ + @IndexName + ‘] ON ‘ + @TableName +’ REBUILD

WITH (PAD_INDEX = ON, FILLFACTOR = ‘ + @FillFactorValue +  ‘)’

Update [Utility].[dbo].[FillFactorInformation] SET AlterStatement = @MySql , RecommendedFillFactor = @FillFactorValue    WHERE ID = @ID

FETCH NEXT FROM Index_cursor INTO @TableName, @IndexName, @FillFactorValue , @ID

END

CLOSE Index_cursor

DEALLOCATE Index_cursor

Drop table #temp

GO

Output:

Use may10

go

Exec uspPub_GenerateFillFactor

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: