|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 2,749,
Visits: 1,407
|
|
So, if you rebuild and index with a low fill factor you get lots of pages with lots of room in them and therefore more space to insert records before the split occurs? The downside being that you use a lot of disk space (which is a cheap as dirt)!
LinkedIn Profile
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:34 AM
Points: 141,
Visits: 61
|
|
Yes, that's correct, and hopefully you can schedule a re-build of the indexes before the page splitting starts to occur. The other downside is that less-full indexes also increase disk I/O. If you have an index that's 20% full, you still have to read in the entire index page, even though 80% of it is empty. So compared with an optimal index, where no page splits are occuring but every index page is 100% full (unlikely scenario unless you're not inserting any data into your database, but...), you'll end up having to perform 5 times as much disk I/O in order to read the index, as the index pointers / data will be spread over 5 times as many pages. I think this is one of the reasons Microsoft recommend that you leave FILLFACTOR settings to SQL Server - you can cripple a system's performance by creating lots of half empty indexes which increase disk I/O. Also, your backups are going to be bigger too, as there's lots of *almost* empty pages in the index which have to be backed up. For the most part, I'd look at leaving FILLFACTOR alone, with one exception - "read-only" MIS/reporting databases which are only going to be restored from an OLTP backup, and are never going to have data added to them. In that case, fill your indexes 100%, as it minimises the space they use on disk, which in turn minimises disk I/O when queries read the index, and you never need to worry about page splits because no data is added, so they never happen.
Jon
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Thursday, May 02, 2013 3:03 PM
Points: 8,
Visits: 21
|
|
All of your articles has been excellent. This one is not the exception. I agree with you that indexing and all about performance is un art. But in this art I am a beginer, and your articles are a good source of knowledge. Thanks.
Leonel E. Umaña Araya leo_umana@hotmail.com
|
|
|
|
|
SSC-Dedicated
           
Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421,
Visits: 13,734
|
|
Thanks for the comments. This was an update of something I'd written a few years back, but it still applies.
Fillfactor and your indexing is something that needs regular maintenance. If this is a very active table, lots of updatees, inserts, and deletes, you will need to rebuild often to maintain performance. As pointed out by Antares and Jon Reade above. If you are dealing with slowly or non changing data, you can probably set your fillfactor, defragment and leave things alone.
Follow me on Twitter: @way0utwest
 Forum Etiquette: How to post data/code on a forum to get the best help
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, September 06, 2010 3:09 AM
Points: 36,
Visits: 136
|
|
Hi Great article - thanks. Is there anything similar for a table? When administering Oracle I would check both tables and indexes for fragmentation and want to do the same on SQL Server but so far have not been able to find anything that reports table fragmentation. Also, how do I check each index? Unless I'm missing something here it appears that dbcc showcontig reports on a single index, certainly if I run it on a table with 5 indexes I get one set of results. Regards Karl
|
|
|
|