Início / dba / Perguntas / 235102
Accepted
mustaccio
Asked: 2019-04-18 14:51:37 +0800 CST

Se uma coluna VARCHAR(MAX) for incluída em um índice, o valor inteiro será sempre armazenado na(s) página(s) de índice?

  • 772

Estou perguntando isso por curiosidade, sendo inspirado por essa pergunta .

Sabemos que VARCHAR(MAX)valores maiores que 8000 bytes não são armazenados em linhas, mas em páginas LOB separadas. A recuperação subsequente de uma linha com esse valor requer duas ou mais operações lógicas de E/S (essencialmente, uma a mais do que seria teoricamente necessária).

Podemos adicionar uma VARCHAR(MAX)coluna, como INCLUDEd, a um índice único, conforme demonstrado na pergunta vinculada. Se essa coluna tiver valores que excedam 8.000 bytes de comprimento, esses valores ainda serão armazenados "inline" nas páginas de folha de índice ou também serão movidos para páginas LOB?

sql-server varchar

1 respostas

  1. Best Answer

    Valores que excedem 8000 bytes não podem ser armazenados "inline". Eles são armazenados em páginas LOB. Você pode ver isso com sys.dm_db_index_physical_stats . Comece com uma tabela simples:

    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);

    Agora insira algumas linhas com valores que levam 8000 bytes para a VARCHAR(MAX)coluna e confira o 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');

    Não há páginas LOB no índice:

    ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
║ 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 ║
╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝

    Mas se eu adicionar linhas com valores que levam 8001 bytes:

    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');

    Agora eu tenho 1 página LOB no índice para cada linha que acabei de inserir:

    ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
║ 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 ║
╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝

    Você também pode ver isso com SET STATISTICS IO ON;e a consulta correta. Considere a seguinte consulta que analisa apenas linhas com 8.000 bytes:

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

    Resultados ao executar:

    Contagem de varredura 1, leituras lógicas 2560, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Se eu consultar as linhas com 8001 bytes:

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

    Agora eu vejo lob lê:

    Contagem de varredura 1, leituras lógicas 20, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 5080, leituras físicas lob 0, leituras antecipadas lob 0.

    • 21

relate perguntas