Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

SQL Server 2000 Indexing Expand / Collapse
Posted Thursday, September 16, 2004 4:58 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 3,413, Visits: 3,043

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

Newbie on
Post #137321
Posted Thursday, September 16, 2004 5:25 AM


Group: General Forum Members
Last Login: Friday, June 19, 2015 7:41 AM
Points: 142, Visits: 65

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.

Post #137328
Posted Thursday, September 16, 2004 10:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 14, 2015 11:00 AM
Points: 9, Visits: 28

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.


Leonel E. Umaña Araya
Post #137424
Posted Thursday, September 16, 2004 10:16 PM



Group: Administrators
Last Login: Today @ 1:04 PM
Points: 34,375, Visits: 18,596
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
Post #137510
Posted Thursday, September 15, 2005 4:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 5:39 PM
Points: 36, Visits: 137


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.


Post #220418
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse