Usando DMV´s para consultar metadados de uma tabela

Olá Pessoal

Hoje vou falar de um recurso legal que é utilizar as DMV´s para consultar metadados de uma tabela. As vezes criamos uma tabela ou mesmo quando esta já estar criada e queremos saber algumas informações vamos dizer assim: “internas” sobre ela como:

  1. Quantas páginas de dados nossa tabela ocupa?
  2. Qual Data File e filegroup nossa tabela reside?
  3. Quantas partições nossa tabela utiliza?
  4. Quais Unidades de Alocação são utilizadas por nossa tabela?
  5. Quais colunas e seus tipos de dados, se a coluna é identity ou não, qual collation para cada coluna?

Para podermos responder essas perguntas precisamos entender ao menos o básico da estrutura de armazenamento de uma tabela no SQL Server. Para começar vamos analisar a imagem abaixo de qual inlustra este processo.

estrutura table

Considerando a imagem acima podemos observar que uma tabela ou índex no SQL Server pode conter uma ou várias partições com um limite de 1000 partições, com isso quando criamos uma tabela que não seja particionada esta irá ser amazenada em uma única partição. Caso criamos uma tabela particionada para cada partição esta será criada a mesma estrutura de armazenamento como mostrado na imagem acima. O Termo utilizado pelo SQL Server 2005 para representar uma tabela ou índex em uma única partição é o Hobt que significa Heap ou B-Tree.

Cada partição pode conter até tres tipos de linhas de quais são armazenadas em seus próprios conjuntos de página, esses conjuntos de páginas para uma determinada partição são chamados unidades de alocação. As tres unidades de alocação exibidas acima são: IN_ROW_DATA, LOB_DATA e ROW_OVERFLOW_DATA. Cada unidade de alocação armazena linhas de acordo com seus tamanhos, ou seja, dentro da unidade de alocação IN_ROW_DATA são armazenadas as linhas de dados e index para valores comuns que não ultrapassem o limite de 8K, dentro da unidade de alocação LOB_DATA serão armazenados dados do tipo LOB (Large Objects) exemplo são dados do tipo TEXT e na unidade de alocação ROW_OVERFLOW_DATA serão armazenados dados que excedem os 8K como dados do tipo VARCHAR que ultrapassem o limite.

Toda tabela ou índex contém uma entrada na DMV sys.objects de qual armazena informações sobre a tabela, como tipo do objeto que no caso é tabela de usuário ou de sistema, data de criação, data de modificação, etc. A DMV sys.objects se relaciona com a DMV sys.indexes através da coluna Object_ID de qual armazena informações sobre os indexes no objeto. Para uma Heap a informação retornada da DMV sys.indexes na coluna Type_desc é HEAP, se for criado um índex Clustered a coluna Type_Desc retorna CLUSTERED para o objeto informado, pois quando se indexa uma tabela com um índex clustered os dados da tabela são parte do índex.

A DMV sys.indexes se relaciona com a DMV sys.partitions no modelo 1 para N até o limite de 1000 partições, através da coluna Object_ID, com isso podemos visualizar as partições para uma determinada tabela ou index. A DMV sys.partitions se relaciona com a DMV sys.allocation_units de qual lista as unidades de alocação de uma determinada partição.

Agora que entendemos um pouco da estrutura de armazenamento de uma tabela ou index e os relacionamentos das DMV´s responsáveis, vamos criar nosso ambiente de testes para responder nossas perguntas enumeradas acima. Para criar nosso ambiente vamos criar um banco de dados de teste chamado Storage_Engine e criar nossa tabela chamada TB_Storage conforme o script abaixo:

–Criando o banco de dados.
CREATE DATABASE Storage_Engine

–Criando a tabela de teste TB_Storage.
USE Storage_Engine
CREATE TABLE TB_Storage
(
     ID INT IDENTITY(1,1)
    ,NAME CHAR(8000)
)

Criei a tabela TB_Storage com o tipo de dados CHAR(8000) para que a cada linha inserida a tabela ocupe uma página de dados. Com nossa tabela criada vamos consultar a DMV sys.objects e a DMV sys.indexes para verificar algumas informações das quais detalhamos anteriormente. Poderíamos fazer um Inner Join entre as duas DMV´s, mas para visualizar todas as colunas de cada DMV, preferi dividir em dois lotes de consultas.

–Consultando a DMV sys.objects
SELECT *
FROM sys.objects
WHERE Object_id = Object_id(‘TB_Storage’)

–Consultando a DMV sys.indexes
SELECT *
FROM sys.indexes
WHERE Object_id = Object_id(‘TB_Storage’)

result1 

 

 

Com os dados retornados a partir das consulta na DMV sys.objects podemos verificar o nome da tabela, o object_id do objeto, descrição da tabela como User_Table, data de criação, modificação, etc. Com os dados retornados pela DMV sys.indexes podemos verificar algumas informações sobre index como Index_ID que no caso é 0 pois nao contém nenhum index em nossa tabela, o tipo de descrição é HEAP como discutimos anteriormente.

Agora que já listamos informações sobre nossa tabela, vamos começar a tentar responder as perguntas no inicio do artigo. Para responder a primeira pergunta vamos utilizar a query abaixo de qual lista as páginas de dados utilizadas por nossa tabela.

–Inserindo alguns valores para o SQL Server alocar páginas para a tabela
INSERT INTO TB_STORAGE VALUES(‘A’)
INSERT INTO TB_STORAGE VALUES(‘B’)
INSERT INTO TB_STORAGE VALUES(‘C’)

–Listando as páginas de dados usadas e reservadas para a tabela.
SELECT au.*
FROM sys.allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
WHERE p.object_id = object_id(‘TB_Storage’)

result2 

Através da saída acima podemos observar as colunas total_pages, used_pages, data_pages na coluna Total_pages retorna o total de páginas alocadas para a tabela incluíndo páginas IAM que são para controle do SQL Server, como em nosso exemplo inserimos 3 valores foram criadas 3 páginas de dados. Na coluna Used_Pages como o nome já diz, é retornado todas as páginas de dados usadas pela tabela incluindo a página IAM, é diferente da coluna Total_pages de qual lista as páginas alocadas, que não necessáriamente estão usadas pela tabela, supondo que inserimos mais 6 linhas, nossa tabela teria 9 páginas de dados com isso o SQL Server iria alocar um extend uniforme para a tabela, com isso a coluna Total_pages retornaria 17 páginas (16 páginas de dados alocadas + 1 página IAM) e a coluna Used_pages retornariam 10 páginas (9 páginas de dados + 1 página IAM). Na coluna Data_pages é retornado somente as páginas utilizadas como páginas de dados para a tabela, em nosso exemplo 3 páginas de dados.

Reparem que com essa query respondemos duas perguntas, a pergunta 1 a 4. Como em nosso exemplo somente temos dados comuns, estes são apenas armazenados na unidade de alocação IN_ROW_DATA para exemplificar isto, vamos alterar a estrutura da tabela adicionando duas novas colunas uma do tipo varchar(8000) e outra do tipo de dados text, vamos verificar o que acontece.

–Criando a coluna do tipo varchar(8000)
ALTER TABLE TB_STORAGE
ADD EMAIL VARCHAR(8000)

–Criando a coluna do tipo text
ALTER TABLE TB_STORAGE
ADD ENDERECO TEXT

–Listando as unidades de alocações e suas respectivas páginas para a tabela.
SELECT au.*
FROM sys.allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
WHERE p.object_id = object_id(‘TB_Storage’)


result3 

O SQL Server criou mais 2 unidades de alocação para a tabela. Observem que com a mesma query respondemos as perguntas 1 e 4. Foi listado as tres unidades de alocação para nossa tabela e suas páginas de dados, como não inserimos dados nas colunas criadas, as unidades de alocação não armazenam nenhuma página de dados.

Para respondermos as pergunta 2 vamos utilizar o comando DBCC IND de qual retorna diversais informações inclusive o ID do arquivo físico de qual nossa tabela reside. Essa informação é retornada através da coluna PageFID que significa Index File ID, para listar o nome lógico e o filegroup associado ao arquivo utilizamos a query abaixo de qual relaciona as DMV´s sys.sysfiles e sys.filegroups.

–Listando o ID do Data File da tabela TB_Storage
DBCC IND(Storage_Engine,TB_Storage,-1)

–Listando o nome lógico e físico do arquivo retornado.
SELECT   f.name
             ,f.filename
             ,fg.name as ‘Filegroup Name’
             ,fg.type_desc
FROM sys.sysfiles f
INNER JOIN sys.filegroups fg
ON f.fileid = fg.data_space_id
WHERE f.fileid = 1

result4

Além da coluna PageFID a saída do comando DBCC IND nos retorna informações úteis sobre a tabela como o ID da Partição, Index ID, Tipo de Página (1= Página de dados, 10 = Página IAM) entre outras informações. Através da coluna Partition ID podemos responder a pergunta 3 pois, a saída do comando DBCC IND já nos lista as partições de nossa tabela, mas para ficar mais claro essas informações vamos utilizar a DMV sys.partitions.

–Listando partições da tabela TB_Storage
SELECT *
FROM sys.partitions
WHERE object_id = object_id(‘TB_Storage’)

result5 

Podemos observar que nossa tabela tem apenas uma partição, o número da partição é 1 e existem 3 linhas de dados nessa partição. Vamos agora responder a nossa quinta pergunta listando as colunas e tipos de dados em nossa tabela, informações sobre collation e propriedade identity. Para pegarmos esses valores devemos relacionar as DMV´s sys.columns e sys.types de quais armazenam as colunas para cada tabela e os tipos de dados de sistema e tipos de dados definidos por usuário. Para visualizarmos os nomes dos tipos de dados para cada coluna precisamos relacionar as DMV´s pois na DMV sys.columns é exibido somente o código do tipo de dados, por isso pegamos esse nome a partir da DMV sys.types conforme o script abaixo.

SELECT    C.object_id
              ,C.name
              ,C.column_id
              ,D.name as ‘Data Type’
              ,C.max_length
              ,C.collation_name
              ,C.is_nullable
              ,C.is_identity
              ,C.is_computed
FROM sys.columns C
INNER JOIN sys.types D
ON C.system_type_id = D.system_type_id
WHERE C.Object_id = Object_id(‘TB_Storage’)
ORDER BY C.column_id

result6

Com essa saída de dados podemos verificar qual a ordem das colunas através da coluna column_id, o tipo de dados, tamanho máximo, nome da collation para cada coluna, se a coluna aceita valores nulos e se é uma coluna computada.

Algumas DMV´s utilizadas para responder algumas perguntas também poderiam ser utilizadas para responder outras ou mais de duas questões, porém utilizei DMV´s diferentes para mostrar de forma clara cada uma e suas informações retornadas. Na verdade esse é um dos principais benefícios em utilizar DMV´s pois os dados estão sempre relacionados e as DMV´s nos mostram uma informação completa, precisa e atualizada dos objetos e estruturas internas do SQL Server.

Espero que o artigo seja útil para a comunidade. Até o próximo!

Advertisements

2 Responses to Usando DMV´s para consultar metadados de uma tabela

  1. Fabrício says:

    Show de bola Felipe. Ainda não tinha estudado Internals de armazenamento dos registros. Parabéns.

  2. Felipe says:

    Legal FabrícioO Internal de armazenamento é muito interessante, tentei explicar pelo menos um pouco deste conceito, que bom que lhe serviu como uma noção.Abraço.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: