Olika storlekar på Heap vs Clustered tabeller

Om man laddar två tabeller med exakt samma data den ena tabellen är en Heap och den andra är en clustrad tabell så ser man att de har olika storlekar (olika antal Pages).

Frågan är varför.

I en clustrad tabell så är det ju clusternyckeln som bestämmer var raden ska sparas. Men när vi kommer till en heap så ska ju SQL Server försöka hitta en plats bland de sidorna som tabellen består av. För att bestämma om raden får plats så scannas sidornas PFS (Page Free Space) som är en 2 bits parameter som finns på varje sida.

Parametern visar hur mycket plats som finns på sidan:

0x00 = Empty
0x01 <= 50%                       (50_PCT_FULL)
0x02 >= 51% och <= 80% (80_PCT_FULL)
0x03 >= 81% och <= 95% (95_PCT_FULL)
0x04 >= 96% och 100%   (100_PCT_FULL)

Med dbcc page (Database, 1, Page, 3) får man bland annat se detta:

Med hjälp av den beräknas hur mycket plats som finns kvar genom att nyttja:

Som exempel 95_PCT_FULL och vi vill spara en rad som är 1 119 byte.
8 060 Bytes (som max kan sparas på en sida) * 95% (från PFS) => 7 657 bytes (som används)
8 060 Bytes – 7 657 Bytes => 403 byte (ledigt)
Enligt beräkningen får inte 1 119 Bytes plats på sidan då det endast är 403 Bytes ledigt. Vilket leder till att raden sparas på en annan/ny sida.

I bifogat exempel vet vi att vi har sparat 6 rader med exakt samma längd (6*1 119) => 6 714 bytes. 8 060 Bytes – 6 714 Bytes => 1 346 Bytes borde vara ledigt.

Här kan vi se att även SQL Server vet att det finns så mycket ledigt, men nyttjar inte den informationen när den ska bestämma vart den ska lagra raden i en heap.

Om man gör en rebuild av tabellen så kommer den att nyttja det lediga utrymmet så att den har 7 rader /sida. Men så fort man lägger till fler rader så gäller beräkningen igen och sidorna blir inte fullt nyttjade.

Exempelkod:

-- Create test tables
Create Table Tab_NonClustered (
ID int Identity(1,1),
RowNr int,
ColChar char (100),
ColInt int,
ColVarChar varchar(1000));
go
Create nonclustered index IX_Tab_NonClustered_RowNr on Tab_NonClustered (RowNr);
Go
Create Table Tab_Clustered (
ID int Identity(1,1),
RowNr int,
ColChar char (100),
ColInt int,
ColVarChar varchar(1000),
CONSTRAINT PK_Tab_Clustered_ID PRIMARY KEY CLUSTERED (ID));
go
Create nonclustered index IX_Tab_Clustered_RowNr on Tab_Clustered (RowNr);
Go
 
-- Load data
Set Nocount On;
Declare @i int;
Select @i = 0;
While (@i < 1000)
Begin
Insert Into Tab_NonClustered Values (@i, 'hello', @i+10000, replicate ('a', 1000));
Set @i = @i + 1;
End
Select @i = 0;
While (@i < 1000)
Begin
Insert Into Tab_Clustered Values (@i, 'hello', @i+10000, replicate ('a', 1000));
Set @i = @i + 1;
End 
 
-- Check the size of the tables
Select index_type_desc, alloc_unit_type_desc, page_Count
From sys.dm_db_index_physical_stats(DB_Id(),
Object_Id('Tab_NonClustered'), NULL, NULL, NULL); --HEAP
 
Select index_type_desc, alloc_unit_type_desc, page_count
From sys.dm_db_index_physical_stats(DB_Id(),
Object_Id('Tab_Clustered'), NULL, NULL, NULL); --Clustered Index
 
-- Tab_NonClustered = 174 Pages => 1,42 MB
-- Tab_Clustered = 143 Pages => 1,17 MB
 
-- Check number of rows/page
Select 'Tab_NonClustered' Tabell,
       SubString([Physical RID], 4, 5) PageNumber,
       Count(*) [Rader/Page]
From (Select %%physloc%% rowid,
      sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID]
      From Tab_NonClustered ) x
Group by SubString([Physical RID], 4, 5)
order by 2;
 
Select 'Tab_Clustered' Tabell,
       SubString([Physical RID], 4, 5) PageNumber,
       Count(*) [Rader/Page]
From (Select %%physloc%% rowid,
      sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID]
      From Tab_Clustered ) x
Group by SubString([Physical RID], 4, 5)
order by 2;
 
-- Tab_NonClustered = 6 Rows/Page
-- Tab_Clustered = 7 Rows/Page

-- Check Datapage info
-- Man måste slå på traceflaggan 3604
DBCC TRACEON(3604)
GO
 
-- Nyttja DBCC Page (Database, File Number, Page Number, Print Option)
-- Byt [SQLService_Div] till ditt databasnamn
-- Ta ett Page nummer från resultatet ovan och sätt in istället för 13405

DBCC PAGE([SQLService_Div],1,13405,3)
-- Slå av traceflaggan
DBCC TRACEOFF(3604)
GO
Drop Table Tab_NonClustered;
Drop table Tab_Clustered;