Dear friends,
If I want to create index that for sure the order of Inserts is not according to the Index order, should I create it with fillfactor (80 ?) so that there is free spaces in the index pages?
Is it better practice to prevent high fragmentation ?
Thank you so much!
Generally speaking, there's no reason to use any fill factor except 100%.
1 Like
Thank you very much. Yossi
Like I said on the other post by the OP...
I'll have to take extreme exception to that bit of advice. If you rebuild and index, it's most likely because it was fragmenting. With some exceptions, if you rebuild it to 100% you are guaranteeing that it will fragment almost immediately and massively. That's caused by some serious page splits, which are typically 43 or or more worse for the log file, cpu, duration, etc.
I'll also state that it's better to do no index maintenance than it is doing it wrong and rebuilding fragmented indexes to 100% in a willy-nilly fashion is doing it wrong.k
And, yes... I'm speaking from experience where my production system had massive performance and blocking issues on the proverbial morning after such things were done because I was ignorant about them back then.
Index fill factor depends on how much the index is updated. You can get the index use/update statis from the view dm_db_index_usage_stats. For highly updated indices you can set the fill factor to 95% or 90%, but I would not go lower than 85
Example to get index usage:
select DB_NAME(), OBJECT_NAME(I.object_id, db_id()) as TableName, I.index_id, I.name as Indexname
, ( IU.system_lookups + IU.system_scans + IU.system_seeks) as SystemUses
,(IU.user_lookups + IU.user_scans + IU.user_seeks) as UserUses
, IU.user_updates
, I.is_primary_key, I.fill_factor, I.is_unique, I.is_unique_constraint , P.rows as NrOfRows
from sys.indexes I
left outer join sys.dm_db_index_usage_stats IU on I.index_id = IU.index_id and I.object_id = IU.object_id
INNER JOIN sys.partitions AS p ON I.[object_id] = p.object_id AND I.index_id = p.index_id
where IU.database_id = db_id()
order by Tablename, Indexname
Thank you very much, Yossi
Please site where you're getting those numbers from especially for "highly updated indices".
The real key here is that no one on this thread can actually answer that question because they know nothing about the keys of the index nor any of your INSERT, UPDATE, or DELETE patterns.
And "High fragmentation" isn't necessarily a bad thing. Page splits are a bad thing but you can have indexes that are super high in fragmentation that will never suffer from a bad page split nor any kind of low page density.
But, again, we know nothing about your index nor the INSERT, UPDATE, or DELETE patterns and anyone that makes a suggestion about a Fill Factor for your question is only shooting in the dark because the "Best Practice" is to actually know what the index is doing over time.
Indeed my Database / Tables work only with Inserts, almost never Updates.
What about the rest of the story on this index? What are the keys of the index? Since all NCI's also contain the keys for the CI, what is the definition of the CI? If any of the columns of the CI are populated from another table, what's their story? And you say "almost never Updates", does that include the keys for this index and the underlying CI?
It's not just the fact that you're only going to have just INSERTs on a table... We need to know more about the columns of the intended NCI and the columns that make up the CI. Some indexes will benefit (reduced page splits) from a reduced Fill Factor. Some will not. You need a whole lot more information to determine that.
I'm getting those numbers from 30 years of experience with database servers, 25 with sql server, it's a kind of guiding line.
What is NCI and CI ? (sorry, my english...)
NCI= Non Clustered Index
CI= Clustered Index
1 Like
Heh... what if I told you those 30 years of experience were actually incorrect? And, no... that's not meant as a slam. I was also a believer in all that until I found out the hard way that most of it is all wrong. For example... do you do regularly scheduled index maintenance? Does it use REORGANIZE for indexes that have a range of logical fragmentation? Yeah... that's all wrong because REORGANIZE doesn't actually do what most people think it does. For example, most people think that it "follows the Fill Factor". It actually doesn't because it cannot and will not create new pages to clear the area above the Fill Factor lie a REBUILD will.
Even the author of the supposed "Best Practices" says to take all of that with a grain of salt.
The point I'm trying to make here is that there is no panacea "guiding line". Every index has it's own "personality" and a general "guiding line" may be the worst thing that could be done to an index. The OP needs to know a whole lot more about the index he's talking about.
My apologies... it has nothing to do with your English. These are fairly common abbreviations in the world of indexes and I make the mistake of thinking that everyone knows them. Such abbreviations aren't used in the MS Documentation and I shouldn't assume.
ahmeds08 is correct....
NCI = Non Clustered Index
CI = Clustered Index
You are so kind, my langauge is hebrew, of course I have to know english, everyone should know english.
Getting back to the subject, tell us more about your index. What you don't want to do is assign a Fill Factor to an index where a reduced Fill Factor won't help. You also don't want to skip assigning a Fill Factor if it will. Of course, the use of REORGANIZE can actually cause and perpetuate Page Splits and fragmentation unless it's just exactly the right kind of index but, even then, it can (and frequently does) use more resources (contrary to what is stated in the documentation) than a REBUILD usually does.
A perfect example of this is Random GUIDs. They're actually the epitome of how a fragmenting index should work like but the use of REORGANIZE prevents them from going literally months with less than 1% fragmentation. And, yeah... I have proof of all that, if you're interested.