Jeśli kolumna VARCHAR (MAX) jest zawarta w indeksie, czy cała wartość jest zawsze przechowywana na stronach indeksowych?

Pytam o to z ciekawości, inspirując się tym pytaniem .

Wiemy, że VARCHAR(MAX)wartości dłuższe niż 8000 bajtów nie są przechowywane w wierszach, ale na osobnych stronach LOB. Późniejsze pobranie wiersza o takiej wartości wymaga dwóch lub więcej logicznych operacji IO (zasadniczo jedna więcej niż byłoby teoretycznie konieczne).

Możemy dodać VARCHAR(MAX)kolumnę, jako INCLUDEd, do unikalnego indeksu, jak pokazano w połączonym pytaniu. Jeśli ta kolumna ma wartości przekraczające 8000 bajtów, czy takie wartości byłyby nadal przechowywane „w linii” na stronach liści indeksu, czy też zostałyby przeniesione na strony LOB?

Wartości przekraczające 8000 bajtów nie mogą być przechowywane „w linii”. Są one przechowywane na stronach LOB. Możesz to zobaczyć za pomocą sys.dm_db_index_physical_stats . Zacznij od prostego stołu:

USE tempdb;

DROP TABLE IF EXISTS #LOB_FOR_ME;

CREATE TABLE #LOB_FOR_ME (
ID BIGINT,
MAX_VERNON_WAS_HERE VARCHAR(MAX) 
);

CREATE INDEX IX ON #LOB_FOR_ME (ID) INCLUDE (MAX_VERNON_WAS_HERE);

Teraz wstaw kilka wierszy z wartościami, które zajmują 8000 bajtów dla VARCHAR(MAX)kolumny i sprawdź DMF:

USE tempdb;

INSERT INTO #LOB_FOR_ME
SELECT 1, REPLICATE('Z', 8000)
FROM master..spt_values;

SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED'); 

W indeksie nie ma stron LOB:

╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
║ index_level ║  index_type_desc   ║ alloc_unit_type_desc ║ page_count ║ record_count ║
╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
║           0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║       2540 ║         2540 ║
║           1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║         18 ║         2540 ║
║           2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║          1 ║           18 ║
╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝

Ale jeśli dodam wiersze z wartościami, które zajmują 8001 bajtów:

USE tempdb;

INSERT INTO #LOB_FOR_ME
SELECT 2, REPLICATE(CAST('Z' AS VARCHAR(MAX)), 8001)
FROM master..spt_values;

SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED'); 

Teraz mam 1 stronę LOB w indeksie dla każdego właśnie wstawionego wiersza:

╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
║ index_level ║  index_type_desc   ║ alloc_unit_type_desc ║ page_count ║ record_count ║
╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
║           0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║       2556 ║         5080 ║
║           1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║         18 ║         2556 ║
║           2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║          1 ║           18 ║
║           0 ║ NONCLUSTERED INDEX ║ LOB_DATA             ║       2540 ║         2540 ║
╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝

Możesz to również zobaczyć za SET STATISTICS IO ON;pomocą odpowiedniego zapytania. Rozważ następujące zapytanie, które dotyczy tylko wierszy o 8000 bajtach:

SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
FROM #LOB_FOR_ME
WHERE ID = 1;

Wyniki po wykonaniu:

Liczba skanów 1, logiczne odczyty 2560, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.

Jeśli zamiast tego sprawdzę wiersze z 8001 bajtami:

SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
FROM #LOB_FOR_ME
WHERE ID = 2;

Teraz widzę, że LOB czyta:

Liczba skanów 1, logiczne odczyty 20, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 5080, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.