February 8, 2010 at 8:03 am
Hello World,
I was curious if there is an article or explanation of how much free space I should use or how can I determine that.
Thanks!
February 8, 2010 at 8:13 am
Look for articles / blogs explaining Fill Factor..
To suggest how much space to be used in an Index, you need to try working with different fill factor settings and test it before making it final.
DBCC SHOWCONTIG would give you the current fragmentation, page fullness and more information..
You can also use the sys.dm_db_index_physical_stats DMV
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 10, 2010 at 4:19 pm
Depends on data updates you perform on the table, Best practices is b\w 70 - 80% fill factor.
EnjoY!
February 11, 2010 at 3:15 am
Hi
It really does depend on the data types of the columns involved and the type of data going into the columns.
But here are some basic rules of thumb:
Don't have a fill factor less than 70 unless you have a really good reason.
Try not to use uniqueidentifier columns in indexes, the data is random, so it is impossible to calculate a fill factor that will work. So that means no GUID's as Primary Keys. Rather use BigInt identities instead.
You don't need a fill factor on any identity column. The data is always perfectly sequential and inserted in order.
Limit the number of columns in your index to as few as possible, rather have more indexes. You want as many rows per page as possible.
Make sure your queries match your indexes.
In general, a higher fill factor is better. When possible, don't pad indexes. Rather have a high fill factor and use Index REBUILDS and REORGANIZE that run regularly. Don't just rebuild every single index, write a custom script that checks the sys.dm_db_index_physical_stats view to find out which indexes need it, which need a rebuild and which can use reorganize and schedule that script to run. If you are running Enterprise Edition, and your app functions 24/7 like mine do, use ONLINE=ON, otherwise you'll need to schedule down time once a week, or however frequently is required based on monitoring sys.dm_db_index_physical_stats. We've also got 41 tables with over 250 million rows each, so we've scheduled specific table's indexes on specific days only to make sure that the maintenance does not take more than 1 hour per night during the quiet time which doesn't really exist, but between 3 and 4 am, we can do maintenance. Make sure the index maintenance is done before any scheduled reports run. Also, don't reorg indexes that don't need it, it can make performance worse.
As for the rest, you really need to look at the data going in to the table, the data that already exists in the table, work out how many index rows/page you get (based on column size and headers, etc) and from that try to see if there is a patern to the data or not. If not, choose a fill factor size that will just cope (or just not cope) with the amount of data going in, in the time between index rebuild/reorg.
February 11, 2010 at 3:46 am
I guess it's purely depend on the application you are hosting on the database, database structure, table structure etc....
I prefer you do more performance R&D by setting various fill factors for best performance.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply