I got a hunch about SQL Server Fragmentation: Part VI

***Image quality in this post is horrible, I apologize.  I'll replace later.


Its been a while... but I'm feeling a hunch come on again...

I'm working in a database with a single mdf data file.  (That's important context when looking at results of sys.dm_db_database_page_allocations.)

Let's create my favorite kind of two-column demo table - clustered index key on an integer and an uncompressed varchar(4096).  That forces one row per CI page.

Then insert into the table - in reverse numeric order, key values from 4084 to 1 and a varchar just to take up space.



 How does that look in terms of fragmentation, by the most commonly used measure?  99.9755%, huh?  (See part I of my musings "I got a hunch about SQL Server Fragmentation Part I" at http://sql-sasquatch.blogspot.com/2015/08/i-got-hunch-about-sql-server.html )



OK, now lets delete every row with an odd-numbered clustered index value.
 

How does fragmentation look now? Pretty much the same at 99.9511% (are you surprised that the fragmentation measure improved slightly when all that happened was delete of every-other row??).



Now, *almost everyone in the world* will tell you that after a certain level of fragmentation you should go with an index rebuild rather than an index reorganization.  Right?  But... I'm the guy that *sometimes* rebuilds indexes at 0.44% fragmentation (see http://sql-sasquatch.blogspot.com/2016/02/sql-server-who-on-earth-would-rebuild.html).
 
What do you think the outcome was?
Let's check.


Cool!  From 99.9511% fragmentation to 0.6853% fragmentation!  Not bad for a reorg rather than a rebuild! :-)

As I built the table, rows were inserted into the clustered index with keys decreasing in numeric order.  Traveling the clustered index in increasing key order, the "next data page" after each data page almost certainly has a lower page ID.  That's the cause of the high reported fragmentation percent in the original table, and in the resulting table after delete of all odd-numbered rows.

The index reorg moves many rows into the empty pages (each odd-numbered row delete resulted in an empty page).  But if all the reorg did was smush together the reverse-ordered rows/pages, the resulting reported fragmentation at the end would still be high.

The index reorg ALSO reordered pages as it was able.

Before the index reorg, every data page had a page ID that was HIGHER than its "next page ID", due to reverse-ordered inserts.  After the rebuild, with 2043 data pages in the clustered index...


  After the index reorg there are only 3 data pages left that have page IDs higher than their next page page ID (unnatural order, if you will).

So, two takeaways:

• reorg can re-order data pages into a better logical order in addition to making the index more compact
• the general guidelines about "reorg in this range of reported fragmentation, rebuild above this level of reported fragmentation, ignore otherwise" don't tell the whole story

 I'm going somewhere with this, y'all, I promise.  It's just gonna take me a while to get there...