SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

Hannah Vernon

Asked: 2018-09-15 11:21:05 +0800 CST2018-09-15 11:21:05 +0800 CST 2018-09-15 11:21:05 +0800 CST

Listar páginas ROW_OVERFLOW_DATA para uma tabela específica

772

Estou tentando obter uma lista de páginas para uma tabela que possui linhas com ROW_OVERFLOW_DATA. Eu posso obter a lista de páginas alocadas do DMV não documentado sys.db_db_database_page_allocations, no entanto, parece não haver páginas ROW_OVERFLOW_DATA listadas na saída desse DMV. Existe algum outro DMV que eu simplesmente não consigo localizar?

Exemplo mínimo, completo e (espero!) verificável:

USE tempdb;

IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t;
GO

CREATE TABLE dbo.t
(
    rownum int NOT NULL IDENTITY(1,1)
        PRIMARY KEY CLUSTERED
    , on_row_data varchar(30) NOT NULL
        DEFAULT ('on_row_data')
    , off_row_data varchar(MAX) NOT NULL
        DEFAULT REPLICATE('A', 20000) --PLENTY BIG ENOUGH!
) WITH (DATA_COMPRESSION = NONE); --not compressing those pages!

INSERT INTO dbo.t DEFAULT VALUES;

DECLARE @ObjectID int = (SELECT o.object_id FROM sys.objects o WHERE o.name = 't');
DECLARE @PageID int;
DECLARE @PageTypeDesc varchar(100);

SELECT FileID = dpa.allocated_page_file_id
    , PageID = dpa.allocated_page_page_id
    , PageTypeDesc = dpa.page_type_desc
FROM sys.dm_db_database_page_allocations(DB_ID(), @ObjectID, NULL, NULL, 'DETAILED') dpa

A saída se parece com:

╔════════╦════════╦══════════════╗
║ FileID ║ PageID ║ PageTypeDesc ║
╠════════╬════════╬══════════════╣
║ 1 ║ 1598 ║ IAM_PAGE ║
║ 3 ║ 105368 ║ DATA_PAGE ║
║ 3 ║ 105369 ║ NULL ║
║ 3 ║ 105370 ║ NULL ║
║ 3 ║ 105371 ║ NULL ║
║ 3 ║ 105372 ║ NULL ║
║ 3 ║ 105373 ║ NULL ║
║ 3 ║ 105374 ║ NULL ║
║ 3 ║ 105375 ║ NULL ║
╚════════╩════════╩══════════════╝

O que faz sentido, além da página ROW_OVERFLOW_DATA ausente. Temos uma única página de mapa de alocação de índice e uma extensão total de páginas de dados de 8 KB, com apenas uma dessas páginas realmente alocada.

Da mesma forma, se eu usar a sys.fn_PhysLocCrackerfunção não documentada para mostrar a página onde cada linha existe, como em:

SELECT *
FROM dbo.t
CROSS APPLY sys.fn_PhysLocCracker(%%PHYSLOC%%)

Só vejo os DATA_PAGElistados:

╔════════╦═════════════╦═════════════════════╦════ ═════╦═════════╦═════════╗
║ rownum ║ on_row_data ║ off_row_data ║ file_id ║ page_id ║ slot_id ║
╠════════╬═════════════╬═════════════════════╬════ ═════╬═════════╬═════════╣
║ 1 ║ on_row_data ║ AAAAAAAAAAAAAAAAAAA ║ 3 ║ 105368 ║ 0 ║
╚════════╩═════════════╩═════════════════════╩════ ═════╩═════════╩═════════╝

Da mesma forma, se eu usar DBCC IND(database, table, index)só vejo as duas páginas listadas:

DBCC IND (tempdb, t, 1);

Resultado:

╔═════════╦═════════╦════════╦════════╦═══════════ ═╦═════════╦═════════════════╦════════════════════ ═╦════════════════╦══════════╦════════════╦═══════ ══════╦═════════════╦═════════════╦═════════════╦═ ═╗
║ PageFID ║ PagePID ║ IAMFID ║ IAMPID ║ ObjectID ║ IndexID ║ PartitionNumber ║ PartitionID ║ iam_chain_type ║ PageType ║ IndexLevel ║ NextPageFID ║ NextPagePID ║ PrevPageFID ║ PrevPagePID ║ ║
╠═════════╬═════════╬════════╬════════╬═══════════ ═╬═════════╬═════════════════╬════════════════════ ═╬════════════════╬══════════╬════════════╬═══════ ══════╬═════════════╬═════════════╬═════════════╬═ ═╣
║ 1 ║ 1598 ║ NULL ║ NULL ║ 2069582411 ║ 1 ║ 1 ║ 6989586877272752128 ║ Dados em linha ║ 10 ║ NULL ║ 0 ║ 0 ║ 0 ║ 0 ║ ║ 0 ║ 0 ║ 0 ║
║ 3 ║ 105368 ║ 1 ║ 1598 ║ 2069582411 ║ 1 ║ 1 ║ 6989586877272752128 ║ Dados em linha ║ 1 ║ 0 ║ 0 ║ 0 ║ 0 ║ ║ 0 ║ 0 ║ 0 ║ 0 ║
╚═════════╩═════════╩════════╩════════╩═══════════ ═╩═════════╩═════════════════╩════════════════════ ═╩════════════════╩══════════╩════════════╩═══════ ══════╩═════════════╩═════════════╩═════════════╩═ ═╝

Se eu olhar para o conteúdo real da página, usando DBCC PAGE, parece que ainda não vejo nada sobre qual página contém o ROW_OVERFLOW_DATA - tenho certeza que deve estar lá, provavelmente não sei o que olhar:

DBCC PAGE (tempdb, 3, 105368 , 3) WITH TABLERESULTS;

Os resultados são grandes demais para caber aqui, se eu incluir as linhas de despejo de memória, mas esta é a saída do cabeçalho:

╔══════════════╦════════════════════════════════╦═ ══════════════════════════════╦═══════════════════ ════════════╗
║ ParentObject ║ Object ║ Field ║ VALUE ║
╠══════════════╬════════════════════════════════╬═ ══════════════════════════════╬═══════════════════ ════════════╣
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bpage ║ 0x000002431A8A2000 ║
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bhash ║ 0x0000000000000000 ║
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bpageno ║ (3:105368) ║
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bdbid ║ 2 ║
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ referências ║ 0 ║
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bcputicks ║ 0 ║
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bsampleCount ║ 0 ║
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bUse1 ║ 63172 ║
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bstat ║ 0x10b ║
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ blog ║ 0x212121cc ║
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║bnext ║ 0x0000000000000000 ║
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bDirtyContext ║ 0x000002435DA77160 ║
║ BUFFER: ║ BUF @0x000002437E86D5C0 ║ bstat2 ║ 0x0 ║
║ PAGE HEADER: ║ Página @0x000002431A8A2000 ║ m_pageId ║ (3:105368) ║
║ PAGE HEADER: ║ Página @0x000002431A8A2000 ║ m_headerVersion ║ 1 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_type ║ 1 ║
║ PAGE HEADER: ║ Página @0x000002431A8A2000 ║ m_typeFlagBits ║ 0x0 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_level ║ 0 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_flagBits ║ 0xc000 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_objId (AllocUnitId.idObj) ║ 3920762 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_indexId (AllocUnitId.idInd) ║ 512 ║
║ PAGE HEADER: ║ Página @0x000002431A8A2000 ║ Metadados: AllocUnitId ║ 144115445026914304 ║
║ PAGE HEADER: ║ Página @0x000002431A8A2000 ║ Metadados: PartitionId ║ 6989586877272752128 ║
║ PAGE HEADER: ║ Página @0x000002431A8A2000 ║ Metadados: IndexId ║ 1 ║
║ PAGE HEADER: ║ Página @0x000002431A8A2000 ║ Metadados: ObjectId ║ 2069582411 ║
║ PAGE HEADER: ║ Página @0x000002431A8A2000 ║ m_prevPage ║ (0:0) ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_nextPage ║ (0:0) ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ pminlen ║ 8 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_slotCnt ║ 1 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_freeCnt ║ 66 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_freeData ║ 8124 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_reservedCnt ║ 0 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_lsn ║ (36:47578:1) ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_xactReserved ║ 0 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_xdesId ║ (0:0) ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_ghostRecCnt ║ 0 ║
║ CABEÇALHO DA PÁGINA: ║ Página @0x000002431A8A2000 ║ m_tornBits ║ 0 ║
║ PAGE HEADER: ║ Página @0x000002431A8A2000 ║ DB Frag ID ║ 1 ║
║ CABEÇALHO DA PÁGINA: ║ Status de alocação ║ GAM (3:2) ║ ALOCADA ║
║ CABEÇALHO DA PÁGINA: ║ Status de alocação ║ SGAM (3:3) ║ NÃO ALOCADO ║
║ PAGE HEADER: ║ Status de alocação ║ PFS (3:105144) ║ 0x40 ALLOCATED 0_PCT_FULL ║
║ CABEÇALHO DA PÁGINA: ║ Status de alocação ║ DIFF (3:6) ║ NÃO ALTERADO ║
║ CABEÇALHO DA PÁGINA: ║ Status de Alocação ║ ML (3:7) ║ NÃO MIN_LOGGED ║
║ PAGE HEADER: ║ Slot 0 Offset 0x60 Comprimento 8028 ║ Tipo de Registro ║ PRIMARY_RECORD ║
║ PAGE HEADER: ║ Slot 0 Offset 0x60 Comprimento 8028 ║ Atributos de Gravação ║ NULL_BITMAP VARIABLE_COLUMNS ║
║ CABEÇALHO DA PÁGINA: ║ Slot 0 Offset 0x60 Comprimento 8028 ║ Tamanho do Registro ║ 8028 ║
╚══════════════╩════════════════════════════════╩═ ══════════════════════════════╩═══════════════════ ════════════╝

1 respostas

Voted

Josh Darnell · Answer 1 · 2018-09-15T12:10:45+08:00

Best Answer

Josh Darnell

2018-09-15T12:10:45+08:002018-09-15T12:10:45+08:00

Sua demonstração está sendo atingida por uma limitação de REPLICATE :

Se string_expression não for do tipo varchar(max) ou nvarchar(max), REPLICATE trunca o valor de retorno em 8.000 bytes. Para retornar valores maiores que 8.000 bytes, string_expression deve ser convertido explicitamente para o tipo de dados de valor grande apropriado.

Se eu fizer isso:

INSERT INTO dbo.t (off_row_data) VALUES (REPLICATE(CAST('A' as varchar(max)), 20000));

E, em seguida, execute sua consulta DMV de cima para dm_db_database_page_allocations, recebo páginas com um PageTypeDesc de TEXT_MIX_PAGE.

Posso então executar o DBCC PAGE com o sinalizador de rastreamento 3604 habilitado para ver os detalhes dessa página fora da linha:

DBCC TRACEON (3604);
GO
DBCC PAGE (TestDB, 1, 20696 , 3) -- your page will be different :)

A saída é grande, mas perto do início você verá:

Blob row at: Page (1:20696) Slot 0 Length: 3934 Type: 3 (DATA)

E então, você sabe, um monte de A's.

10

Listar páginas ROW_OVERFLOW_DATA para uma tabela específica

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Listar páginas ROW_OVERFLOW_DATA para uma tabela específica

1 respostas

relate perguntas