ceturtdiena, 2013. gada 24. janvāris

Tabulas parametrs "large value types out of row"

Šajā rakstā par LOB (Large Objects) un mainīga garuma datu glabāšanu SQL Server tabulās un tabulas uzstādījumu "large value types out of row". Minētais parametrs var būt noderīgs ātrdarbības uzlabošanai un var ietekmēt datu kompresēšanu.

Tabulas datu glabāšana
Rakstot par Tabulām un indeksiem iedziļinājos tajā, kā tiek glabāti dati SQL Server, bet apzināti ignorēju situācijas, kad vienas datu rindas dati netiek glabātas vienā datu lapā. Ja tiek izmantoti mainīga garuma dati (piem., varchar, varbinary, xml un arī jau novecojušie datu tipi- text, ntext, image), tad rinda var glabāties vairākās datu lapās.

Vēsturiski SQL Server viena rinda jebkurā tabulā nevarēja pārsniegt 8 KB robežu izņemot gadījumus, kad tika izmantots ntext, text vai image datu tips. Jau no SQL 2005 šis ierobežojums ir maznozīmīgs, jo tagad tikai fiksēta garuma kolonu kopējais garums nevar pārsniegt 8 KB (no 8 KB jāatņem datu lapas apraksta apjoms).

Tātad, realitātē tabulas dati var izmantot 3 dažādus datu lapu tipus (Table and Index Organization, msdn neapskatot FileStream, kas glabā tabulas datus failu sistēmā):
  • IN_ROW_DATA - Visi tabulas dati, kas nav mainīga garuma. Kā arī mainīga garuma datu tipi, kas satilpst vienā 8 KB datu lapā.
  • ROW_OVERFLOW_DATA- Ja rindā ir mainīga garuma dati, kas nesatilpst vienā 8 KB datu lapā
    (piemēram, ja tabulai ir divas kolonas- x varchar(6000) un y varchar(4000). Ja abu kopējais garums nepārsniegs 8000 baitus, tad dati tiks glabāti "IN_ROW_DATA", bet pārsniedzot šo limitu- tās kolonnas, kas neietilps "IN_ROW_DATA" tiks glabātas "ROW_OVERFLOW_DATA").
  • LOB_DATA- Tiek izmantotas LOB datu glabāšanai- varchar(max), nvarchar(max), varbinary(max), xml datu tipiem, kas nesatilpst vienā datu lapā.
    (piemēram, ja tabulā ir kolona MansObjekts nvarchar(max), tad MansObjekts kurš nepārsniedz 8000 baitus, tiks glabāts "IN_ROW_DATA", bet ja pārsniedz, tad "LOB_DATA")
Parametra pielietojums
Par parametru "large value types out of row" ir vērts atcerēties gadījumos, kad tiek izmantoti datu tipi- varchar(max), nvarchar(max), varbinary(max), xml, kā arī atsevišķos gadījumos- lietotāja definētie datu tipi.

Noklusētā parametra vērtība ir "OFF" (jeb 0, nozīme ir tā pati). Tas nozīmē to, ka ja datu rinda kopumā ir īsāka par atļauto datu lapas robežu (minētie 8 KB neskatot datu lapas aprakstošos datus), tad visi dati tiks glabāti vienā datu lapā. Tikai pārsniedzot atļauto apjoma robežu, attiecīgo kolonnas(u) dati tiks pārvietoti uz "LOB_DATA".

Un tomēr, vislabāk šo ilustrēt var izmantojot piemēru.
Skriptā tabula, kurā tiks glabāti xml dokumenti, no kuriem vairums būs mazāki par 8 KB. Tabulā līdzās dokumentiem glabāsies arī svarīgākā tos aprakstošā informācija, pēc kuras tie tiks meklēti.

Create Table dbo.Dokumenti
(
    DokumentsID int primary key identity,
    Pievienots datetime default GetDate(),
    Numurs int,
    Statuss varchar(20),
    Autors varchar(50),
    Atslegvardi varchar(250),
    /* .. daudzas citas kolonnas .. */
    Dokuments XML
);
Go
-- datu ģenerēšana, ne tā efektīvākā, bet šādi skripts sanāk īsāks
Set NoCount On;
Declare @MaksligsDokuments XML =
    (
        Select Top 10 * From sys.objects
        For XML Raw, Type
    );
-- Select DataLength(@MaksligsDokuments); -- manā gadījumā: 2357 baiti

Insert Into dbo.Dokumenti(Pievienots, Numurs, Statuss, Autors, Atslegvardi, Dokuments)
    Values(default, Cast(Rand() * 1000 as int), 'Jauns', 'Jānis D.',
        'sysobjects, xml, LOB, piemers', @MaksligsDokuments);
Go 1000

-- MEKLĒŠANAS ĀTRUMS- jāpievērš uzmanība "Logical Reads"
Set Statistics IO ON;
Select  * From dbo.Dokumenti Where Numurs = 56;
Set Statistics IO OFF;

-- DATU KOMPRESĒŠANA- jāpievērš uzmanība "PAGE" kompersijai
Exec sp_estimate_data_compression_savings 'dbo', 'Dokumenti', NULL, NULL, 'ROW';
Exec sp_estimate_data_compression_savings 'dbo', 'Dokumenti', NULL, NULL, 'PAGE';
Go

Truncate Table dbo.Dokumenti;
EXEC sp_tableoption 'dbo.Dokumenti', 'large value types out of row', 'ON';
Go
-- datu ģenerēšana, ne tā efektīvākā, bet šādi skripts sanāk īsāks
Set NoCount On;
Declare @MaksligsDokuments XML =
    (
        Select Top 10 * From sys.objects
        For XML Raw, Type
    );
-- Select DataLength(@MaksligsDokuments); -- manā gadījumā: 2357 baiti

Insert Into dbo.Dokumenti(Pievienots, Numurs, Statuss, Autors, Atslegvardi, Dokuments)
    Values(default, Cast(Rand() * 1000 as int), 'Jauns', 'Jānis D.',
        'sysobjects, xml, LOB, piemers', @MaksligsDokuments);
Go 1000

-- MEKLĒŠANAS ĀTRUMS- jāpievērš uzmanība "Logical Reads"
Set Statistics IO ON;
Select  * From dbo.Dokumenti Where Numurs = 56;
Set Statistics IO OFF;

-- DATU KOMPRESĒŠANA- jāpievērš uzmanība "PAGE" kompersijai
Exec sp_estimate_data_compression_savings 'dbo', 'Dokumenti', NULL, NULL, 'ROW';
Exec sp_estimate_data_compression_savings 'dbo', 'Dokumenti', NULL, NULL, 'PAGE';
Go

-- Pēc sevis satīram:
Drop Table dbo.Dokumenti 
Secinājumi (izpildot skriptu):
  • Datu meklēšana konkrētajā gadījumā ir daudz efektīvāka, ja tiek nomainīts noklusētā "large value types out of row" vērtība uz "ON"- salīdzinoši 336 pret 14 loģiskajām lasīšanas operācijām.
  • "PAGE" datu kompresija konkrētajā gadījumā ir daudz efektīvāka, ja tiek atstāta noklusētā "large value types out of row" vērtība "OFF". Ar noklusēto vērtību dati kompresija 2688 KB sākotnēji uz 40 KB pēc kompresijas, bet ar "ON" vērtību datu kompresija neatstāj praktiski nekādu iespaidu.
Svarīgas piebildes
Protams, šī situācija ir mākslīgi radīta. Reālajā situācijā ir jāņem vērā arī citi būtiski aspekti. Galvenais- ja nav līdz galam skaidrs vai to vajag, tad labāk neko neaiztikt :)
  • Indeksi- ja piemērā dotā tabula saturētu indeksu uz "Numurs" kolonu, tad parametra vērtība nesniegtu aprakstīto uzlabojumu. Katram risinājumam savi plusi un mīnusi;
  • Datu lasīšanas veids- ja xml kolonas dati tiek bieži lasīti/rakstīti (salīdzinoši ar pārējām kolonām) tad datu glabāšana ārpus rindas var radīt papildus slodzi (jo jālasa/jāmaina vairākas datu lapas);
  • Kompresēšana- jāņem vērā datu saturs, citos gadījumos ieguvums glabājot datus rindā var būt daudz mazāks (piemēram, ja dati ir šifrēti). Un jāatceras, ka SQL Server datu kompresēšana attiecas tikai uz "IN_ROW_DATA";
  • Datu apjoms- ja LOB kolonas datu apjoms ir lielāks par 8 KB, tie vienmēr tiks glabāti ārpus "IN_ROW_DATA". Parametrs neatstās nekādu iespaidu;
  • Novecojuši datu tipi- izmantojot text, ntext, image datu tipus, datu glabāšanas princips ir savādāks- pēc noklusējuma tādā gadījumā LOB dati vienmēr tiek glabāti ārpus rindas, neatkarīgi no to apjoma. Lai to mainītu ir jāizmanto cits parametrs- "text in row". Par to plašāk msdn.
Var noderēt
- parametra mainīšana notiek izmantojot procedūru sp_tableoptions, noklusētā vērtība ir OFF:
EXEC sp_tableoption 'dbo.Dokumenti', 'large value types out of row', 'ON';
- Pārliecināties par uzstādījumiem var izmantojot skatu sys.tables:
SELECT name, large_value_types_out_of_row, text_in_row_limit
FROM sys.tables
--WHERE name = 'dbo.Dokumenti'
- Datu apjomu dažāda tipa datu lapās var uzzināt izmantojot sys.dm_db_partition_stats:
Select in_row_used_page_count, lob_used_page_count, row_overflow_used_page_count
From  sys.dm_db_partition_stats
Where object_id = object_id('dbo.Documenti')

Nav komentāru:

Ierakstīt komentāru