Jaki jest narzut wiersza podczas korzystania z kompresji strony?

Utworzyłem tabelę z 650 kolumnami numerycznymi (19,4). Po włączeniu kompresji strony, uruchamiam

ALTER TABLE fct.MyTable REBUILD  WITH (DATA_COMPRESSION = PAGE);

dostaję

Msg 1975, poziom 16,
indeks stanu 1 Długość wiersza „PK_Mytable” przekracza maksymalną dopuszczalną długość „8060” bajtów.

ale 650 razy 9 bajtów to tylko 5850 bajtów, co jest dość dalekie od podanego limitu 8060 bajtów.

Na serwerze działa system Windows 2012 R2 z SQL Server 2016 SP1 CU2

Jaki jest narzut wiersza podczas korzystania z kompresji strony?

Oto kod pokazujący, co mam na myśli:

/* test script to demo MSG 1975 */
DECLARE @sql NVARCHAR(max)='', @i INT =0
drop table if exists dbo.mytable;

SET @sql = 'Create table dbo.Mytable (MyTableID bigint not null 
  identity(1,1) primary key clustered, '

WHILE @i < 593 BEGIN
    SET @sql += ' Column' + LTRIM(@i) + ' numeric(19,4) null, '
    SET @i +=1
END

SET @sql += ' LastColumn int) '
--SET @sql += ' with (DATA_COMPRESSION = ROW) '
SET @sql += ' with (DATA_COMPRESSION = PAGE) '

SELECT @sql
EXEC sys.sp_executesql @sql

SELECT top 10000 * FROM dbo.MyTable MT

Kompresja wierszy również się nie udaje, ale przy innej liczbie wierszy.

Jeśli spróbujesz utworzyć tabelę bez klastrowego ograniczenia PK, otrzymasz nieco inny błąd:

Msg 1701, poziom 16, stan 1, wiersz 1 Tworzenie lub modyfikowanie tabeli „Mytable” nie powiodło się, ponieważ minimalny rozmiar wiersza wynosiłby 8067, w tym 1530 bajtów narzutu wewnętrznego. Przekracza to maksymalny dopuszczalny rozmiar wiersza tabeli wynoszący 8060 bajtów.

W tym komunikacie o błędzie widać, że na kompresję strony przypada 1530 bajtów narzutu wewnętrznego.

Teraz możesz wykonać matematykę:

• 8 bajtów dla bigintMyTableID
• 4 bajty dla intLastColumn
• 9 bajtów dla każdej z 593 numeric(19,4)kolumn (łącznie 5337 bajtów)
• 1530 bajtów narzutu kompresji

Więc 8 + 4 + (593 * 9) + 1530 = 6879. Poczekaj sekundę ... To wciąż poniżej 8060. O co chodzi ?!

Algorytm kompresji strony faktycznie łączy kilka algorytmów kompresji. Pierwszym krokiem jest zastosowanie kompresji ROW. Narzut związany z kompresją wierszy nie jest uwzględniony w 1530 bajtach narzutu wymienionego w tym komunikacie o błędzie.

Możesz przeczytać więcej o tym, jak działa kompresja wierszy tutaj na moim blogu i tutaj w BOL . W artykule BOL zauważysz, że opisuje on numericpamięć jako „Ta pamięć jest dokładnie taka sama jak format pamięci vardecimal”, ale nie wyjaśnia vardecimal. Ten post obejmuje vardecimalnieco więcej - w zasadzie dodaje 2 bajty narzutu na kolumnę do przechowywania rzeczywistej długości (podobnie do tego, co varcharrobi).

Kompresja wierszy będzie wymagała dodatkowych 2 bajtów dla każdej z 593 numerickolumn oraz plus biginti intbędzie wymagać 1 bajta narzutu dla każdej.

Do wierszy sprężone wymagania przechowywania, to:

• 8 bajtów + 1 bajt narzut dla bigintMyTableID
• 4 bajty + 1 bajt narzut dla intLastColumn
• 9 bajtów + 2 bajty narzut dla każdej z 593 numeric(19,4)kolumn
• 1188 bajtów narzutu kompresji ROW

8 + 4 + (593 * 9) = 5349 bajtów danych

1 + 1 + (593 * 2) = narzut kompresji rzędu 1188 bajtów

6537 bajtów łącznie dla schematu skompresowanego wierszem

Teraz, gdy mamy rozmiar wiersza dla schematu skompresowanego w wierszu, możemy wrócić do naszej matematyki. Rozmiar wiersza skompresowanego przez stronę to rozmiar danych + narzut kompresji wiersza + narzut kompresji strony:

• 8 bajtów dla bigintMyTableID
• 4 bajty dla intLastColumn
• 9 bajtów dla każdej z 593 numeric(19,4)kolumn
• 1188 bajtów narzutu kompresji ROW
• 1530 bajtów narzutu kompresji PAGE

  5349 bajtów danych 
+ 1188 bajtów narzutu na kompresję wiersza 
+ 1530 bajtów narzutu na kompresję strony 

Łącznie 8067 bajtów