January 9, 2009 at 10:06 am
In determining an optimal FILLFACTOR I have read that SPLIT IO/Sec and Page Splits/Sec should be examined to see if the values are high. What is considered a high value for these counters?
Thanks, Dave
January 9, 2009 at 10:14 am
Hi Dave,
Like many other counters, the ones you listed don't have absolute good/bad values. There are many variables when it comes to determining fillfactors. Hopefully the following article will prove to be helpful to you:
http://qa.sqlservercentral.com/articles/Performance+Tuning/amethodologyfordeterminingfillfactors/1195/[/url]
The article uses the somewhat antequated DBCC SHOWCONTIG command, but it is still useful knowledge.
January 9, 2009 at 10:48 am
I read through it yesterday and it is very good, but it does not explain what values indicate a problem. I agree there are many counters where a specific value does not exist, but with all counters there should be some rule of thumb to follow. The problem with most articles I come across is they typically say when Page Splits are "high" or Split IO/Sec are "high" or some other counter is "high", but do not provide a way to determine what is considered "high". Sometimes the author says to take a baseline, but that doesn't always work. It's possible your system has misconfigured hardware, outdated firmware, bad drives, poor sql configurations at the time of your baseline so your baseline results are actual indicating a bottleneck, but you cannot tell that because you don't know what values to look for in the counters. Are there any rules of thumb for Page Splits and Split IO/sec?
Thanks, Dave
January 9, 2009 at 12:17 pm
What is a high Page Splits/sec? There is no simple answer, as it somewhat depends on your system's I/O subsystem. But if you are having disk I/O performance problems on a regular basis, and this counter is over 100 on a regular basis, then you might want to experiment with increasing the fill factor to see if it helps or not. [6.5, 7.0, 2000] From-->http://www.sql-server-tips/sql_server_performance_monitor_coutners_p1.aspx
HTH!
MJ
January 9, 2009 at 12:57 pm
High sometimes means above your baseline.
sometimes it is a concrete number... sometimes (like this one) it's calculus.
for Fill factor, the level of index fragmentation and how quickly it gets fragmented is an easier to understand indicator, at least for me.
as an example, your index is over 40% fragmented and 99% full at 6pm and you reindex every day at 5pm
you might consider setting your fill factor to a lower value. Adjust and monitor.
If an index is 80% full and 3% fragmented after a week without reindexing, you could raise your fill factor for that index.
sys.dm_db_index_physical_stats and a week's worth of investigating might help you out if you have specific issues.
~BOT
Craig Outcalt
January 9, 2009 at 12:59 pm
Also, the fragmentation percent depends on the order with which the data is inserted related to the index order...
http://qa.sqlservercentral.com/articles/Index+fragmentation/64424/[/url]
so fill factor isn't always the answer!
~BOT
Craig Outcalt
January 9, 2009 at 1:06 pm
That makes sense. One article/blog I read yesterday said to look at Scandensity and Page Splits. The Scandensity part makes sense, but it was the page splits that got me wondering what's is considered acceptable. I can start by looking at how quickly fragmentation is occuring with the largest indexes and adjust their FILLFACTOR. I should then be able to see a drop in page splits, which over time will get me to a baseline.
Thanks, Dave
January 9, 2009 at 2:01 pm
The SQLCAT team published a series of perfmon counter guidelines including a value for page splits - they're on my website if you can't find them. http://grumpyolddba.co.uk/monitoring/Performance%20Counter%20Guidance%20-%20SQL%20Server.htm
The problem with page splits is that the counter is server wide - no granularity!
I have yet to ever fully find a variable fill factor any use whatsoever; however that doesn't mean I won't.
Without being too blunt most people bandy 90% and 85% fill factors about without any analysis of the impact of that fill factor. The use of a fill factor must be based upon how many rows you can fit per page, For any row 4k or above a fill factor is pointless, and at 3k you'll only allow for one and instantly double the size of your table, not good if it's a 300 million row table and by adding a fill factor of 90% you've grown it from 30Gb to 60GB !!
And don't imagine a single int index will fit 2,000 odd rows per page; it doesn't work that way.
On my website and blog are some articles about sizing indexes, in fact I'm giving a presentation later this month which covers this a passing aspect of indexes.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 12, 2009 at 9:42 am
Thanks Grumpy. I already told my manager it will take several weeks to get some proper analysis of the fillfactors currently set by the vendor. The problem is we are slamming in the application to meet a deadline, which is only going to lead to spotty performance and frustrated users. But that's out of my control. I'm trying to setup some sysmon traces for disk, memory, etc. to help identify problems. Between profiler, sysmon, Spotlight on SQL Server, SQL Diagnostic Manager and sysinternals running at all different times I feel like an air traffic controller. I have yet to add EMC tools to the mix. That's happening later this week.
Dave
January 12, 2009 at 1:44 pm
Ah don't you just love vendors!! I can tell you of a BI vendor who set a DW database to binary collation ( case sensitive for our readers ) because it gave more performance. This was despite the fact there wasn't one case sensitive application/database in the company. The vendor also couldn't tell me which aspects of binary were quicker - it's certain types of sorts/searches ( check out BOL ).
I've only met one person who had an intelligent view of applying fill factors. Best of luck
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply