RiCo技術農場

In a few days ago, my friend(he is a native english speaker) asked me why our clustered index got more fragmentation when he updated or inserted a clustered index key in sql2014.

Is this as expect?

 

My short answer as below

Yep and I will make a simple test to prove it.

create table testCIordering
(
c1 int not null primary key clustered 
)

insert into testCIordering
values(1),(2),(3),(4),(6),(7)

--Consultando a DMF
select allocation_unit_type_desc,extent_file_id,extent_page_id,is_iam_page,
is_allocated,allocated_page_page_id,is_mixed_page_allocation,page_type_desc,
next_page_page_id,previous_page_page_id,is_page_compressed,has_ghost_records
from sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('testCIordering'),null,null,'DETAILED')

okay, I got file and page ID of testCIordering in Table

So, I will enable traceflag 3604 first then call DBCC PAGE command to show more information for me.

DBCC TRACEON(3604);
DBCC PAGE (DemoQueryStore, 1, 47776, 1);

No surprise, physically ordering of rows point to our clustered index key.

Now, I will insert a new row into our exists clustered index as below

--I did not keep ordering of rows
insert into testCIordering values(5)
select * from testCIordering

DBCC PAGE (DemoQueryStore, 1, 47776, 1);

As you can see very clearly in the above output that the new row which is not in between and greater than the address of the last row.

The memory address of the last 2 rows is unchanged(140 and 151),

but new row is not accommodated in between as per the sort order but at the end.

dbcc showcontig('testCIordering') with tableresults

Nice count(BestCount = ActualCount) and no LogicalFragmentation.

Let’s added more records of random into the table and see what happened

declare @max int=3000
declare @curval int=0
while (@max>0)
begin
    set @curval=ROUND(((1000600 - 7 -1) * RAND() + 7), 0)
	if not exists(select 1 from testCIordering where c1= @curval)
	begin
	  insert into testCIordering values(@curval)	 
      set @max=@max-1;
	end	
end

As you can see two points in the above output,

First is remember to keep ordering of rows to avoid more fragmentation when you run insert or update.

Second is clustered index do not guarantee physically ordering or sorting of rows.

 

Reference

[SQL SERVER][Memo]Clustered VS NonClustered Indexes

[SQL SERVER][Memo]再談 Clustered Index

SQL Server 2012: sys.dm_db_database_page_allocations

SQL SERVER 2012 – sys.dm_db_database_page_allocations – dynamic management function

Inside the Storage Engine: What’s in the buffer pool?

Using DBCC PAGE

More undocumented fun: DBCC IND, DBCC PAGE, and off-row columns

SQL SERVER – Random Number Generator Script – SQL Query

Create Your Own RANDBETWEEN Function in T-SQL

DBCC SHOWCONTIG (Transact-SQL)