Change index FILLFACTOR - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

Change index FILLFACTOR

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-12-04 : 02:51:18

I've just scripted all the PKs and indexes on a production database and for some reason most of them are set to "WITH FILLFACTOR = 90".

I can't see any point in this, what's my best way to change them to the default (given that its a production system)?

I've got a range of Clustered and Non-clustered PKs and Indexes to do

I was thinking of something along the lines of:


BEGIN TRANSACTION
ALTER TABLE [dbo].[MyTable] DROP [PK_MyTable]
ALTER TABLE [dbo].[MyTable] WITH NOCHECK ADD 
	CONSTRAINT [PK_MyTable] PRIMARY KEY  CLUSTERED 
	(
		[MyID]
	) /* WITH  FILLFACTOR = 90 */  ON [PRIMARY] 
COMMIT


CREATE  CLUSTERED  INDEX [IX_MyIndex] ON [dbo].[MyTable2]
(
	[MyColumn]
)
	/* WITH  FILLFACTOR = 90 */ 
	WITH DROP_EXISTING
	ON [PRIMARY]

Kristen

Kristen
Test

22859 Posts

Posted - 2004-12-05 : 02:26:30

By the by, I read an article somewhere that suggested that the Wizard Maintenance Plans adjust the Fill Factor if you use the [Optimizations] "Reogrganize data and index pages" and "Change free space per page percentage to 10%" - maybe that's how my FILLFACTORs have become 90%

Kristen