Klustrad tabell med varchar-kolumner

Klustrade tabeller är det som rekommenderas i alla typer av applikationer. Detta är vad som händer i en klustrad tabell vid de vanliga operationerna:

INSERT- Den nya raden placeras på en sida som bestäms av det klustrade indexet så att raden hamnar rätt i ordning. Om raden inte får plats så delas sidan och de sista raderna på sidan flyttas till den nya sidan.
UPDATE Om man sen uppdaterar en kolumn som är av typen varchar och har samma längd på den uppdaterade strängen som den lagrade så sker endast uppdatering av innehållet.
Om man däremot uppdaterar en varchar-kolumn med en sträng som är längre än den tidigare och det inte finns plats på sidan att spara den nya raden som har blivit längre så delas sidan och vissa rader flyttas til den nya sidan, allt för att ordningen enligt klustrade indexet skall vara intakt.
DELETE – Raden markeras bara som borttagen
SELECT – Har man bra index så får man en Key-lookup eller så måste hela tabellen läsas.

Exempel

-- Create test tables
Create Table Tab_Clustered (
RowNr int,
ColChar char (100),
ColVarChar varchar(4000));
go
Create clustered index PK_Tab_Clustered_RowNr on Tab_Clustered (RowNr);
Go
-- Load data
Set Nocount On;
Declare @i int;
Select @i = 1;
While (@i < 11)
Begin
Insert Into Tab_Clustered
Values (@i, 'hello', replicate ('a', 500));
Set @i = @i + 1;
End

-- Check the size of the tables
Select index_type_desc, alloc_unit_type_desc, page_count, Fragment_Count, forwarded_record_count
From sys.dm_db_index_physical_stats(DB_Id(),
Object_Id('Tab_Clustered'), NULL, NULL, 'DETAILED');

Vi har data bara på en sida och när det är en klustrad tabell, så finns inga forwarded_record_count.

-- Check number of rows/page
Select 'Tab_Clustered' Tabell, 
       SubString([Physical RID], 4, 5) Page, 
	   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;

Vi har 10 rader på sida 12746 (du får annat sidnummer):

-- Check Datapage info
-- Man måste slå på traceflaggan 3604 
DBCC TRACEON(3604)
GO
-- Här måste du ange ditt database namn och sidnr istället för SQLService_Div 
dbcc page([SQLService_Div],1,12746,3)

Här ser vi att Slot2 (RowNr=3) har en PRIMARY_RECORD  (Slot 2 startar på rad 159):

Set statistics io on
Set statistics time on
Select * from Tab_Clustered

Vi får 3 Logical reads:

--Add 1500 char to a column
Update Tab_Clustered set ColVarChar = ColVarChar + replicate ('b', 1500) 

Om vi nu kontrollerar hur många sidor tabellen är nu så ser vi att den ökat till totalt 6. Anledningen till att vi får två rader är att man får en rad per nivå i b-tree trädet. Med 5 sidor ( kallas leaf level) som består av själva raderna och 1 sida som kallas root level).

Men tittar vi på hur många rader vi har på sidorna får vi bara upp 5 sida och tillsammans innehåller dessa 10 rader (samma som tidigare). Här ser man direkt att rader har fått flytta.

Tittar vi nu hur sidan ser ut så hittar vi att slot 2 så finns bara slot 0 & 1 i sidan 12746. Men i Page Header på sidan kan vi hitta nästa sidas nummer, i mitt fall 12768.

Och tittar vi på sida 12768 så hittar vi vår rad. (RowNr = 3) som slot 0

Select * from Tab_Clustered

Vi får bara 11 Logical reads:

Vad händer om man förlänger kolumnen ännu mer, så att den flyttade raden inte får plats på sin nya sida?

--Add 1000 char to a column
Update Tab_Clustered set ColVarChar = ColVarChar + replicate ('c', 1000)

Nu har tabellen ökat till 6+1 sida:

Och selecten ger ytterligare några logical reads:

Och efter ny flytt av raderna:

Om vi nu gör en reorganize av tabellen

-- Reorganize table
ALTER INDEX PK_Tab_Clustered_RowNr ON Tab_Clustered REORGANIZE;

Som packar ihop sidorna lite och en sida försvinner (12771)

Här ser man vilken page respektive rad  som ligger på i de olika stegen.

Här ser vi en av anledningarna till att man ska hantera indexen. Kör man regelbundet en rebuild eller reorganize av indexen, så packas tabellerna ihop på ett effektivt sätt och databasen får färre sidor att leta bland och därmed snabbare svar.