SQL Server 2012: sys.dm_db_database_page_allocations

Article
1/17/2024

Introdução

Como todos sabemos as Dynamic Management Functions e Dynamic Managemente Views foram introduzidas no SQL Server 2005 afim de que se possa visualizar o estado do servidor e até mesmo a saúde, diagnosticar problemas e realizar performance tuning. O que alguns não sabem é que o SQL Server possui os famosos comandos não documentados que por padrão não são visíveis muitas vezes ao Administrador/ Desenvolvedor de bancos de dados e que podem ajudar no nosso dia a dia. Vale lembrar que os comandos não documentados não são suportados pela Microsoft em caso de suporte, então tudo que você ler aqui é de minha própria opinião. Com isso o SQL Server 2012 inseriu uma nova DMF não documentada que vamos verificar abaixo.

Conhecendo a nova DMF

A DMF em questão é a sys.dm_db_database_page_allocations essa nova DMF traz para nós algumas informações novas como: Quais as paginas de dados estão utilizando algum tipo de compressão, quais paginas de dados participam de um processo do Ghost Cleanup e é claro ser muito mais fácil de manipular do que o comando anterior DBCC IND. Sem mais delongas vou demonstrar a vocês quais as informações podemos capturar.

Abaixo vamos utilizar alguns scripts

Listagem 01. Criação da base de dados e tabelas

use master
go
if exists (select name from sys.databases where name  = 'PageAllocations')
begin
drop database PageAllocations
end
go
create database PageAllocations
go
use PageAllocations
go
create table tblAlloc1(id int identity, nome char(8000))
go
create table tblAlloc2(id int identity not null primary key, nome char(8000))
go

insert into tblAlloc1
values ('Marcos')
go 2
insert into tblAlloc2
values ('Marcos Freccia')
go 7
Feito a criação dos objetos conforme a listagem 01, vamos demonstrar na pratica como funciona a nova DMF: sys.dm_db_database_page_allocations.

Listagem 02. Consultando a DMF




select allocation_unit_type_desc,extent_file_id,extent_page_id,is_iam_page,
is_allocated,allocated_page_page_id,is_mixed_page_allocation,page_type_desc,
next_page_page_id,previous_page_page_id,is_page_compressed,has_ghost_records
from sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('tblAlloc1'),null,null,'DETAILED')

E temos os resultado abaixo na imagem 01.

(Imagem 01. Consultando a nova DMF)

Informações úteis que podemos retirar

A qual extent nossas paginas de dados estão atreladas
Qual é a nossa pagina de dados IAM.
Em qual arquivo está meu extent
Se a pagina esta alocado com dados ou apenas disponível.
É extent misto ou extent uniforme?
Qual o tipo de pagina estamos vendo.
Essa pagina de dados está passando ou não por um delete, através do campo has_ghost_records.

Como podem ver temos inúmeras informações valiosas através dessa consulta, mas acredito eu uma coisa vocês devem ter notado. Porque os campos next_page_page_id e previous_page_page_id estão nulos?
A resposta para isso é bastante simples. HEAP TABLE !!!

Como podemos visualizar com uma heap table não temos os links de referencia entre uma pagina e outra por isso os campos nulos, a única coisa que faz o mapeamento dessa tabela é o id do extent.

Vamos ao próximo exemplo

Listagem 02. Extraindo informações da DMF

select allocation_unit_type_desc,extent_file_id,extent_page_id,is_iam_page,
is_allocated,allocated_page_page_id,is_mixed_page_allocation,page_type_desc,
next_page_page_id,previous_page_page_id,is_page_compressed,has_ghost_records
from sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('tblAlloc2'′),null,null,'DETAILED')

(Imagem 02. Consultando os dados da DMF)

Como vocês podem ver passamos a ter mais informações, agora de quais são as paginas predecessoras e sucessoras a cada uma. Outro ponto legal que podemos ver é que com a adição de um índice na nossa tabela conseguimos ver também qual é a pagina de dados correspondente a uma pagina de índice.

Apenas por curiosidade vamos adicionar mais dois registros a nossa tabela tblAlloc2.

Listagem 03. Inserção de registros

insert into tblAlloc2
values ('Marcos Freccia')
go 2


(Imagem 03. Consultando dados da DMF)

Mas como assim 17 registros se eu inseri apenas dois na minha tabela??

O SQL Server não está mostrando nada errado, esse comportamento acontece cada vez que prenchemos nossas 8 paginas de dados de um extent misto. O SQL Server automaticamente já aloca mais 8 paginas (1 extent = 8 paginas de 8KB) e agora já passamos a ter não mais extents mistos, mas sim uniformes.

Uma lição que podemos tirar daqui então é que o SQL Server sempre irá alocar de primeiro ponto um extent misto e não uniforme.

Conclusão

Como podemos perceber com a nova DMF podemos realizar verificações de uma maneira mais fácil e robusta, sem a necessidade de habilitação de trace flags para a utilização do DBCC IND, além é claro dos resultados serem retornados em modo de tabela e não mais em modo texto.

Share via