December 27, 2011 at 9:49 am
The page split on one table is as follows
dbo.Patient_Table<IX_Patient_Table_SSN> - 316 (no of splits)
The avg fragmnetation on this index is 34%
I am thinking of reindexing this table to minimize the page splits but cannot decide upon what fill factor can I use.
This is a replicated ( transactional) table from another server
The Publisher of this table has a heavy inserts once the data is replicated to this table this table has a heavy select from all the web applications.
Any pointers would be greatly appreciated.
Thanks
December 27, 2011 at 10:11 am
Trial and error.
The lower the fill factor, the slower the index will fragment but larger the index will be.
what percentage of new data is added on a weekly basis? If you have a 10 million row table and you add 100k rows a week (1%), a high fill factor makes sense (e.g. 95%). Start there and reduce up or down depending on how fast the index fragments.
December 28, 2011 at 7:17 am
A good place to start would be to look at the average row size in the leaf level (level 0) of the row size. This can be determined from sys.dm_db_index_physical_stats. If, as I suspect, it is inserts causing page splits, you can estimate the fill factor easily with some somple math.
For instance, the leaf level may be 50,000 pages, leaf level of index is 200 bytes, and we have 5,000 inserts a week: On average, you'll have <1 insert per page, so make room for 1 extra row. 200/8096 = 2,4 percent, so the fill factor should be 97 percent.
A different example, the leaf level may be 5,000 pages, leaf level of index is 30 bytes, and we have 50,000 inserts a week: On average, you''ll have 10 inserts per page, of 30 bytes each, so you have to have at least 300 bytes free in each page. 300/8096 = 3,7 percent, sou the fill factor should be at the most 96 percent, I would most likely run with 95 or 94 percent since the distribution of inserted records most likely is somewhat uneven.
I hope this gives you an idea of how you can estimate the fill factor (given relatively even distribution).
January 5, 2012 at 5:15 am
be careful with the fillfactor as you can affect read performance if you don't get it right as there will be more pages required to be read into memory for certain queries.
Have a look at this article for more info: http://www.sqlservercentral.com/articles/Performance+Tuning/whocaresaboutfillfactor/1196/[/url]
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply