May 30, 2002 at 7:24 am
Hi all
From what I can gather, all of the standard dbcc routines for online re-indexing (defrag) do not allow the DBA to set PADINDEX. This is a real problem for me as ive inherited a large app where fillfactor is 75% but the padindex option has not been set, therefore, we are still experiencing some dramatic page split figures at times.
I can do something like:
CREATE UNIQUE CLUSTERED
INDEX [PK_AddressType] ON [dbo].[AddressType] ([addrtype])
WITH
PAD_INDEX
,FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
and write a fancy script to re-create all indexes via this method, but to be thats crazy talk (and somewhat risky).
Ideas?
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 30, 2002 at 7:49 am
To the best of my knowlegde and based on what I have read, this is the only way to change it. EM does the same sort of thing when you change there.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 30, 2002 at 7:57 am
What ill do is eval the most frequently altered tables and use EM for these to fix pad_index. In other cases I may re-evel the fill factor anyhow and go from there.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply