June 17, 2014 at 10:51 am
Is there any issues with the Primary Key / Clustered Index being set to 100%, while all Non-Clustered Indexes are set for 80%?
If not - does padding the indexes (both clustered and non-clustered) have any issues with mixed Fill Factor settings?
Thank you...
June 17, 2014 at 12:18 pm
No special issues associated with it. Each index can have it's own fill factor and it doesn't directly affect other indexes around it. It just affects storage and retrieval of that index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 17, 2014 at 12:34 pm
Grant, thank you for that confirmation. I know it probably sounds very neophyte, but I wasn't able to find anything to fully confirm this (I am working on a Microsoft Dynamics CRM install and am playing with the Fill Factors and Index Padding to see if I cannot squeeze more performance out the the indexes before we move to data compression).
Thanks man, and good to see you out here!
June 18, 2014 at 4:09 am
SQL_ME_RICH (6/17/2014)
Grant, thank you for that confirmation. I know it probably sounds very neophyte, but I wasn't able to find anything to fully confirm this (I am working on a Microsoft Dynamics CRM install and am playing with the Fill Factors and Index Padding to see if I cannot squeeze more performance out the the indexes before we move to data compression).Thanks man, and good to see you out here!
Not a problem. Happy to help. Fill factor is only ever going to give you pretty small performance enhancements, reducing splits, possibly maximizing rows on a page. Compression is likely to do a lot more. You just have to sweat if you're I/O or CPU bound. If the latter, compression could hurt instead of help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 19, 2014 at 11:12 am
That's actually good to know. I have been evaluating our performance gains using Data Compression in a test environment, for a while now, and was under the impression that the only expense to concern one's self with was CPU. We currently do not have Enterprise Edition on the system in question, but are preparing to go to it, so I am doing a lot of work in advance of that upgrade.
Along those lines, and kind of back to fill factoring - I am also looking at padding these indexes, but only the ones that have a minimum of 1000 pages and above, in them (of which I have roughly 500 or so candidates). I'm guessing that there really is no reason to pad a Clustered Index though (am I right on this?)
Thanks again, Grant! (et al!)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply