A Little About Fill Factor And Fragmentation In SQL Server
Thanks for watching!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Tangentially related… Jeff Moden had a cool presentation a couple of years ago about how clustered index page density is affected by [n]varchar(max) and varbinary(max) columns. The moral of the story was to run EXEC sp_tableoption ‘.’,’large value types out of row’,1 ; after creating the table but before inserting any data.
That was supposed to be EXEC sp_tableoption ‘schema.table’,’large value types out of row’,1 ; but I used angle brackets the first time and that didn’t render properly.
Well, who would just copy and paste something from the internet, anyway?
What is your advice when you have a situation with a piece of software that has designed unique identifiers as clustered indexes and have identified that there are page splits and average page space issues causing the true fragmentation that you speak of? Is there a balance or sacrifice that would need to be made to do index maintenance more often or lower fill factor down from 100%?
I think my goal would be to minimize queries using the clustered indexes for data access that requires scans, by adding nonclustered indexes that can do the job instead.
Like I said in the video, that won’t really even mess up queries that seek into indexes.
Other rule when it comes to maintenance that I kind of blindly follow and see being followed is updating statistics. eg. when some percentage of changes inside the table is made. Would you provide us with some wisdom concerning statistics update, please? Maybe at some future post?
It sounds like you have some thoughts on the matter. Perhaps that blog post is yours to write.