Heap med varchar-kolumner

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

 

När man har en tabell utan klustrat index kallas det för Heap. Heapar anses inte bra ur prestandasynvinkel, men de är ofta förekommande även i köpta applikationer.

Detta händer i en Heap vid de vanliga operationerna:
INSERT – Den nya raden placeras på den första sidan i tabellen där den får plats. Finns ingen sida där det finns plats så skapas en ny sida sist i tabellen.

UPDATE – Om man 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å ersätts utrymmet i aktuell sida med en pekare (Forwarding) till en ny sida dit raden flyttas.

DELETE  – Raden markeras bara som borttagen

SELECT – För de flesta frågor måste hela tabellen läsas.

Varför Forwarding?
Varför pekar man om den befintliga posten till en ny sida istället för att ta bort allt från den gamla sidan och flytta allt ihop?
Om man flyttade alltihop så skulle man behöva uppdatera alla index (icke klustrade) som finns på tabellen. Genom detta förfarande minskar man kostnaden för en uppdatering.

Om vi tittar på ett exempel:

[code lang=”sql”]

— Create test tables
Create Table Tab_NonClustered (
RowNr int,
ColChar char (100),
ColVarChar varchar(1000));
go
Create nonclustered index IX_Tab_NonClustered_RowNr on Tab_NonClustered (RowNr);
Go
— Load data
Set Nocount On;
Declare @i int;
Select @i = 1;
While (@i < 11)
Begin
Insert Into Tab_NonClustered
Values (@i, ‘hello’, replicate (‘a’, 500));
Set @i = @i + 1;
End

— Check the Number of pageg och forwarded records
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_NonClustered’), NULL, NULL, ‘DETAILED’);

<pre>[/code]

Vi har data bara på en sida och inga rader flyttade:

Heap_bild1

[code lang=”sql”]</pre>

— Check number of rows/page

Select ‘Tab_NonClustered’ Tabell,

SubString([Physical RID], 4, 5) Page,

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;
<pre></pre>

[/code]

 

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

Heap_bild2

[code lang=”sql”]

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

[/code]

 

Här ser vi att Slot2 har en PRIMARY_RECORD (Slot 2 startar på rad 145):

Heap_3

[code lang=”sql”]</pre>
Set statistics io on
Set statistics time on
Select * from Tab_NonClustered
<pre>[/code]

Vi får bara 1 Logical reads:

Heap_bild4

[code lang=”sql”]
–Add 1500 char to a column
Update Tab_NonClustered set ColVarChar = ColVarChar + replicate (‘b’, 1500)
[/code]

 

Om vi nu kontrollerar hur många sidor tabellen är nu så ser vi att den ökat till 4. Dessutom har vi fått 7 Forwarded_record_count.

Heap_bild5

Men tittar vi på hur många rader vi har på sidorna får vi bara upp 1 sida och den ska innehålla 10 rader (samma som tidigare). Problemet är att nu är ju raderna 2115 Bytes långa vilket inte får plats på en sida som bara kan rymma 8060 Bytes data. Den tittar dock bara på en sida för att hitta antalet rader, att raderna ligger på andra sidor tas inte hänsyn till här.

Heap_bild6
Tittar vi nu hur sidan ser ut så hittar vi att slot 2 har flyttats till sida 12813 slot 1. (slot 2 på rad 325):

Heap_bild7
På sida 12813 hittar vi:

Heap_bild8

Det innebär att för att hitta denna rad måste man leta i på två sidor vilket inte är så lyckat ur prestanda synvinkel.

[code lang=”sql”]

Select * from Tab_NonClustered

[/code]

Vi får bara 11 Logical reads:Heap_bild9

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

[code lang=”sql”]

–Add 1000 char to a column
Update Tab_NonClustered set ColVarChar = ColVarChar + replicate (‘c’, 1000)

[/code]

Heap_bild10

Och selecten ger ytterligare några logical reads:

Heap_bild11

Man kan justera ’hoppandet’ mellan sidor genom att köra en rebuild.

[code lang=”sql”]</pre>
— Rebuild av tabellen

Alter Table Tab_NonClustered ReBuild
<pre>[/code]

Det ger inte färre sidor men nu har man inga ’forwarding_stub’ utan alla index är fixade så att de pekar direkt på rätt sida. Hela tabellen har ju byggts om så all information har flyttat till nya sidor.

Heap_bild12

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

Heap_bild15

Nästa gång tittar vi på vad som händer i motsvarande för clustrade tabeller.