FILLFACTOR vs Free Space on Rebuild Index Task

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

FILLFACTOR vs Free Space on Rebuild Index Task

Author

Topic

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2008-10-27 : 10:24:35

I did a search and read a few posts on this topic, but my question is a little different. I was put under the impression that Free Space was the oposite of Fill Factor. Meaning if Iwanted to have an index rebuild with 80% fill factor I would fill out the field "Change free space per page percentage to" to be set to 20. Yet the T-SQL command this option generates set FILLFACTOR to 80. So I am a bit confused. Any help?

USE [MyCustomers]
GO
ALTER INDEX [PK__Fr_Customers__7C8480AE] ON [dbo].[Fr_Customers] REBUILD WITH ( FILLFACTOR = 80, PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON )
GO
USE [MyCustomers]
GO
ALTER INDEX [PK__UK_Customers__7E6CC920] ON [dbo].[UK_Customers] REBUILD WITH ( FILLFACTOR = 80, PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON )

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-10-27 : 10:48:09

I can't see the issue, Management Studio asks the question a different way to how the T-SQL command works. FILLFACTOR = 80 just means that 80% of each page will be used, "Change free space per page percentage to" set to 20 will do exactly the same.

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2008-10-27 : 11:09:04

My apologies. I forgot to mention I had set the “Change free space per page to:” 80 when I was testing. If I set it to 20 the T-SQL statement says 20 as well for FILLFACTOR.

“Change free space per page to:” 80 -> T-SQL Command sets FILLFACTOR to 80

“Change free space per page to:” 20 -> T-SQL Command sets FILLFACTOR to 20.

I expected what you stated in your post, though.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-27 : 12:57:51

Robert,

You are correct. I tested with maintenance plan got the same result.It should have been opposite.anyone??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources