Pages

If you have a fill factor with 70 , what will be the effect , good or bad.

For this I will have :
– table with fill factor 70 – Table70
– table with fill factor 100 = Table100

Each table will have a cluster index on an identity column.
Then Inserting records so that we have like 1000 pages:
4 records per page * 1000 pages = 4000 records needed to be inserted.
Check pages for both tables.
Do a rebuild with the right fill factor.
Check the level of fragmentation.

 

 

 

use db_workspace
go

DROP TABLE dbo.Table70;
CREATE TABLE dbo.Table70
(
idTable INT IDENTITY(1,1) NOT NULL
,colText CHAR(2000) NOT NULL DEFAULT ‘ 70 table some text 2000 char’
,CONSTRAINT PK_Table70_idTable PRIMARY KEY CLUSTERED(idTable)
);

DROP TABLE dbo.Table100;
CREATE TABLE dbo.Table100
(
idTable INT IDENTITY(1,1) NOT NULL
,colText CHAR(2000) NOT NULL DEFAULT ‘ 100 table some text 2000 char’
,CONSTRAINT PK_Table100_idTable PRIMARY KEY CLUSTERED(idTable)
);

INSERT INTO dbo.Table70(colText)
DEFAULT VALUES
GO 4000

INSERT INTO dbo.Table100(colText)
DEFAULT VALUES
GO 4000

Check the number of pages:

 

SELECT COUNT(DISTINCT CA.page_id) as distinct_count
FROM dbo.Table70
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)AS CA
–1000 distinct pages

SELECT COUNT(DISTINCT CA.page_id) as distinct_count
FROM dbo.Table100
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)AS CA
–1000 distinct pages

Do rebuild with the fill factor :

ALTER INDEX PK_Table70_idTable ON dbo.Table70
REBUILD WITH (FILLFACTOR = 70);

ALTER INDEX PK_Table100_idTable ON dbo.Table100
REBUILD WITH (FILLFACTOR = 100);

Check pages:

SELECT COUNT(DISTINCT CA.page_id) as distinct_count
FROM dbo.Table70
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)AS CA
–1334 distinct pages

SELECT COUNT(DISTINCT CA.page_id) as distinct_count
FROM dbo.Table100
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)AS CA
–1000 distinct pages

So , we have more pages, in Table70, because of the fill factor = 70. This will trigger more logical reads, more memory …

A plus , in a lower fill factor then 100, and depending of your situation/table/activity . is that it will not generate an expensive page split, in case of a update/insert.

In this case, new records are always added to the end of table , like an append. For update , also it’s not a problem because, you have CHAR data type, and it’s fully allocated.

So , in such a case,  Table100 it’s a better option.

 

In other cases, analyze it, check the cluster index, what kind of DML activity , monitor page splits and finally hire a good DBA.

 

 

S