March 28, 2012 at 1:42 pm
If compelled, please explain your reasoning.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 1:50 pm
Not 'No, why should I', but 'No way'
That affects the entire server, every database, any new index created. I prefer to leave that at default and pick fill factors for the indexes based on my knowledge of the tables and index usage patterns.
Oh, and watch out for some index 'maintenance' practices I've seen that do CREATE INDEX ... WITH DROP EXISTING instead of alter index rebuild. The create index gets the default fill factor.
I suppose it's another case of the quick and easy approach vs fine-grained control.
Edited for clarity.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2012 at 1:58 pm
Thanks Gail. Funny you should be the first to respond, you inspired me to post this thread out of our bulk-log-friend's post 😀
I did not know anything about the Maintenance Plan options as I do not use MPs when there is a choise, but it's good to know they're there as I have to work on them sometimes.
To clarify I am talking about the option set using sp_configure. To my knowledge this does not affect existing indexes, only new indexes where WITH FILL_FACTOR = # is not specified, and of course ones rebuilt using the MP options on Kim's blog (Change free space per page percentage to).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 2:12 pm
opc.three (3/28/2012)
To clarify I am talking about the option set using sp_configure.
So am I
To my knowledge this does not affect existing indexes, only new indexes where WITH FILL_FACTOR = # is not specified, and of course ones rebuilt using the MP options on Kim's blog (Change free space per page percentage to).
Correct, but I still don't want it set at a server level
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2012 at 2:14 pm
I would rather set the fillfactor on a per index basis and not have one setting apply to them all.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 28, 2012 at 2:21 pm
GilaMonster (3/28/2012)
opc.three (3/28/2012)
To clarify I am talking about the option set using sp_configure.So am I
Sorry if I implied you misunderstood, I trust you know Default Fill Factor backwards and forwards. My comment was for future readers since the post you linked to referenced fill factor settings in Maintenance Plans and did not mention sp_configure.
To my knowledge this does not affect existing indexes, only new indexes where WITH FILL_FACTOR = # is not specified, and of course ones rebuilt using the MP options on Kim's blog (Change free space per page percentage to).
Correct, but I still don't want it set at a server level
Fair enough, but is it fair to view it as if it is already set at the server level, to 100%?
SQLRNNR (3/28/2012)
I would rather set the fillfactor on a per index basis and not have one setting apply to them all.
I do as well, but unfortunately I do not have the luxury of creating every index ever added to databases in the instance. Maybe that is affecting my perspective. WITH FILL_FACTOR is not a required parameter for CREATE INDEX.
For the record I change the server default to 90, but may change to 95 based on Gail's comment.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 2:33 pm
opc.three (3/28/2012)
I do not have the luxury of creating every index ever added to databases in the instance.
Neither do I. What I do is log index fragmentation and rebuild dates and durations and go over that information every now and again and evaluate which indexes get their fill factors reduced
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2012 at 2:38 pm
GilaMonster (3/28/2012)
opc.three (3/28/2012)
I do not have the luxury of creating every index ever added to databases in the instance.Neither do I. What I do is log index fragmentation and rebuild dates and durations and go over that information every now and again and evaluate which indexes get their fill factors reduced
I do that as well. You recommended Michelle's index maintenance system on another thread. Do you use that one yourself, or MPs, or? I do not know Michelle's system but I use Ola's and his provides the option to log all activity to a table. I review that output regularly and adjust fill factors as needed.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 3:13 pm
I recommend them both, use whichever the client perfers. If they have no opinion then it depends how much I need to do. The one place I picked the tool I use Michelle's because all I need is the index rebuilds. rest of the maintenance is handled in other ways.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2012 at 3:27 pm
There is logging with Michelle's too. I like to use hers because it works very well for the Index Maintenance.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply