How to select correct FILL FACTOR 3

[icemel]

Hi,

Can someone please tell me the best way to determine an appropriate fill factor? Is there a formula to do this?

Thanks!

 

[SQLDenis]

Depends on your design
for read only tables and data warehousing go for 100 percent
for heavily used tables (insert and updates) I would keep it at 70-80% and then monitor these tables, check fragmentation levels periodically and defragment if necessary
I think by default SQL server will always keep enough space to insert 1 row if you select 0

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[amrita418]

There is no set formula for calculating the fill factor.

check this link.

Regards,
AA

 

[icemel]

Thanks, AA

In the article, the author mentions that he run the DBCC SHOWCONTIG on his tables, then loads the data into a table for easy reference... is there an easy way to do this?

 

[druer]

DBCC SHOWCONTIG normally returns data as messages and so you can't capture that data. However if you use the WITH TABLERESULTS option it will instead return the data as a results set that you can capture:

dbcc showcontig (YourTableName) with tableresults

All you need to do is create a table with the matching columns and then do the following:

declare @command varchar(255)
set @command = 'dbcc showcontig (YourTableName) with tableresults'
insert into YourPreDefinedContigResultsTable
exec (@command)

And voila, the command will be run and its values are inserted into your table instead of being displayed. You can still use all of your other WITH options like ALL_INDEXES or FAST etc.

 

[SQLDenis]

icemel,
if you lookup dbcc showcontig in BOL you will see the following code that you can use under section E


E. Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
This example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[druer]

Denis,

Is 30 the value that you use for the Logical Frag check?
Do you actually run this as is on a nightly basis?

 

[SQLDenis]

druer, I just copied this form BOL
I periodically check fragmentation levels manually and defragment if needed

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

Thanks everyone - very helpful!!