I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index.

Inserts are working perfectly fine. After inserting 2000 rows the fragmentation is at around 4%.

However, each records will be updated at least 3 times afterwards. This generates a fragmentation of this clustered index of more than 99% (with default Fill factor)..

Or with other fill factors I tested: - Setting a Fill factor of 80 on the clustered index (Fragmentation > 98%) - Setting a Fill factor of 50 on the clustered index (Fragmentation > 94%)

So this doesn't look like it would help a lot...

I tested each fill factor setting on a newly created table with 2000 inserts (and the 3+ updates)

there is no select or update which does not include the primary key in the query.

Has anybody an idea why this index has such a high fragmentation?

Updates are made through ADO.NET this is the generated command (traced by the SQL Profiler):

exec sp_executesql N'UPDATE MyTable SET MyValue = @MyValue WHERE MyId = @MyId',
N'@MyValue varchar(50),@MyId int',@MyValue='This is some random value',@MyId=1234

Other updates follow the same pattern (update other values, but alway providing the @MyId parameter)

Thank you for your help :)

  • "Has anybody an idea why this index has such a high fragmentation?" -- that would be explained by how exactly you update the records, but you have omitted that information. – mustaccio May 15 '13 at 15:46
  • @mustaccio Edited with an example update. – Nicholas May 15 '13 at 15:50
  • 2
    Why are you worried about this for 2000 rows? Fragmentation isn't going to produce a demonstrable performance issue until you get into the thousands of pages territory, and even then only for certain query patterns. – Aaron Bertrand May 15 '13 at 15:55
  • 2
    If the values you're updating MyValue with are indeed random, they are bound to cause fragmentation. If the new value is longer than the old one, the server has to find space for the record elsewhere, because it can't write it back into the existing space for this record. This leaves behind a "hole" as big as the old version of the record. If the new value is shorter, this also leaves a hole as big as the difference between the old and the new value. – mustaccio May 15 '13 at 16:01
  • @AaronBertrand it's not about those 2000 rows. I just tested with those to watch the fragmentation. There will be more rows added (and later also removed). I'm currently trying to optimize the queries and could see that after those are getting noticeable slower when adding more rows. the index fragmentation was something which caught my eye – Nicholas May 16 '13 at 7:05

FILLFACTOR only applies when you build or rebuild an index, not during normal operation. Normal operations always try to fill the pages to 100%.

If you insert a row that has a variable width, then update the row to be longer, that row will no longer fit on the page if there isn't enough extra space to store the after-image on the same page. If there isn't enough space, this will cause a page split, which is the process that creates the necessary space.

What's a bit misleading about page splits is that there are "good splits" and "bad splits," even though the performance counter counts all of them.

  • A good split is when new rows are added to the end of the index, like what happens when you do your initial batch of INSERTs. The new row doesn't fit on the last page, so the storage engine must allocate a new page and logically hook it up to the last index page. The new page will probably exist physically after the old-last index page.

  • A bad split is when a page must be inserted in the middle of an index: the new page is attached to the index structure logically on both sides, but may (likely) not exist in contiguous physical order to those pages.

Fragmentation is the discrepancy between the logical and physical order, and for the most part, only the bad type of page splits cause fragmentation.

Because you're inflating the size of already-existing rows, this causes the bad type of page split, which is why you're seeing high fragmentation numbers.

It's unclear what your exact process is, and how many rows are ultimately going to end up in this table. If it's something like a one-time population, do the full population process, then turn around and rebuild the clustered index with 100% FILLFACTOR.

If this process happens continuously, you could "pre-allocate" the space by doing the following: add a dummy variable-width column to the table, populate it to the max length on INSERT, and then on the first UPDATE, set the dummy value to NULL while updating the real value. This method will probably add overhead to the logging mechanism because of all the data values flying around.

In general, though, you only need to concern yourself with fragmentation when all of these factors are true:

  • The index is large enough that randomly reading the pages (extents) is too slow (choose your own definition of what "too slow" means)
  • The index will be scanned
  • The index pages (extents) will be physically read from disk (i.e., they are not already in the buffer pool)
  • The storage subsystem is poor at handling random reads (i.e., spindle-based, and the bytes won't come out of cache)

I would recommend, however, putting in place a solid index maintenance solution to keep things more or less in check. You can search this site for recommendations with regards to that.

| improve this answer | |
  • 2
  • Ok. This sounds reasonable. But I don't really like the solution with the dummy data. I'll probably rework the table and move the values which will get updated (yes mostly empty columns which will be filled afterwards with data). a maintenance plan for reorganizing/rebuilding the index is set. Isn't really usefull however if after a few minutes/hours the whole index is fragmented again. How can I check if the extents are physically read from disk? – Nicholas May 16 '13 at 7:11
  • @Nicholas: As I said, you haven't presented what your process is. I can only provide you the theory of what's happening, and you'll have to apply it to your specific scenario. It's possible there's no solution. To check if data is being read from disk you can use the disk performance counters; really it's a matter of knowing your environment, too, because there are many factors that could contribute to whether or not data is read from physical disk. Sorry I can't give a better answer than that. – Jon Seigel May 16 '13 at 14:19
  • @JonSeigel Well, I change it to insert the data in a new table instead of updating it on the record. however now the fragmentation happens on the new records. Is it possible that larger varchar columns could be the cause of that? (even when there are only inserts?) – Nicholas May 24 '13 at 14:28
  • @Nicholas: The var types only take up the space required to store the specified value, and no more (except metadata overhead). I'm still unclear on your process. You're now inserting new rows into a separate table, then doing your updates in that table, then transferring the rows to the original table? Is that correct? – Jon Seigel May 24 '13 at 14:40

It may also be worth checking that the new records are created with an adequate default size.

You do not want new pages allocating 10 bytes per record and then the application populating them with 100bytes worth of stuff. This would be forcing page splits right/centre/left. It could be avoided by finding the average record size and having default values of roughly that size.

| improve this answer | |

Your Answer

