Previous musing here.
I got a hunch about SQL Server fragmentation Part I
And if you are hungering for more after this, please proceed to
I got a hunch about SQL Server fragmentation Part III
I'm working in a newly created test database, which has a single database file.
I'll start by creating my favorite (for now) type of table - two columns with one an integer clustered index key, and the second a 4096 character VARCHAR. Because the clustered index is not compressed, each row in this table will be 1 page.
Important to disclose that in the SQL Server instance for my testing, trace flag 1118 is enabled globally.
You can read more about trace flag 1118 in Paul Randal's post below.
Misconceptions around TF 1118
Lets populate the table, and grab fragmentation information.
Huh. 14% fragmentation, 2 fragments. I thought it'd be better than that.
What happens if we checkpoint(to write out any dirty database pages), dropcleanbuffers and do a full tablescan?
1 Read. 65536 bytes. That's one extent. How can two fragments be read in one 64kb read?
Maybe sys.dm_db_database_page_allocations can help clear the matter up?
This information makes it clear that all of the DATA_PAGE pages are in the same 64kb extent.
In fact, the physical order of DATA_PAGE pages is in agreement with the logical order of DATA_PAGE pages:
So the ordering of the clustered index DATA_PAGE pages is good - in fact it can't be made any better. (Dare ya to try :-})
All of DATA_PAGE pages can be retrieved in a single disk read.
So why does sys.dm_db_index_physical_stats report over 14% fragmentation?
Why does sys.dm_db_index_physical_stats report 2 fragments, when all pages can be retrieved in one read?
I suspect an algorithm error. My guess is that the algorithm is looking at the next logical DATA_PAGE for each DATA_PAGE, and comparing to the next ALLOCATED_PAGE_PAGE_ID that IS_ALLOCATED for the Index Id. By that algorithm, the placement of INDEX_PAGE 78169 divides the 7 DATA_PAGE pages incorrectly into two "fragments".
Instead the algorithm should look at the next logical DATA_PAGE for each DATA_PAGE, and compare to the next ALLOCATED_PAGE_PAGE_ID that IS_ALLOCATED for the Index Id for DATA_PAGE page_type_desc.
That's a subtle distinction that would make ~14% "fragmentation" magically disappear in this case.
That's it for now I guess...