Como retornar o número de linhas e o espaço ocupado por tabela – Parte II

No artigo anterior, demonstrei como utilizar a stored procedure sp_spaceused para coletar métricas de armazenamento de tabelas. O uso da stored procedure sp_spaceused é interessante, mas combinar os dados pode ser um pouco mais trabalhoso visto que essa stored procedure não permite retornar todos os dados de todas as tabelas em uma única execução. Hoje demonstrarei uma outra alternativa para retornar o número de linhas e o espaço ocupado por uma tabela através de uma alternativa diferente.

O SQL Server mantém um conjunto de tabelas internas que contem diversas informações sobre vários elementos (tabelas, configurações do servidor, relação de logins, etc). No SQL Server 2000 essas tabelas podem ser consultadas diretamente para retornar essas informações. No SQL Server 2005, por padrão, não é possível acessar as tabelas de sistemas diretamente, mas as mesmas informações estão disponíveis através de Views que encapsulam as tabelas de sistema. O script abaixo cria duas tabelas que serão utilizadas para recuperação de métricas de armazenamento para futura demonstração de como recuperar essas métricas utilizando os objetos de sistema.

— Criação de tabelas
CREATE TABLE T1 (ID INT IDENTITY(1,1),
    IDSEC UNIQUEIDENTIFIER DEFAULT NEWID(),
    TIPO CHAR(3) DEFAULT ‘REG’)

— Criação de tabelas
CREATE TABLE T2 (ID INT IDENTITY(1,1),
    IDSEC UNIQUEIDENTIFIER DEFAULT NEWID(),
    TIPO CHAR(3) DEFAULT ‘REG’)

— Popula os registros com os valores DEFAULT
DECLARE @Iterador INT
SET @Iterador = 1

WHILE @Iterador <= 10000
BEGIN
    INSERT INTO
T1 DEFAULT VALUES
    INSERT INTO T2 DEFAULT VALUES
    SET @Iterador = @Iterador + 1
END

— Exclui dois mil registros de T2
DELETE FROM T2 WHERE ID <= 2000

— Cria uma nova tabela T3 apartir de T1
SELECT ID * 2 AS Codigo, TIPO INTO T3 FROM T1 WHERE ID > 3500

— Cria os índices
CREATE CLUSTERED INDEX IXCT1_ID ON T1 (ID)
CREATE NONCLUSTERED INDEX IXNT1_IDSEC ON T1 (IDSEC)
CREATE NONCLUSTERED INDEX IXNT2_TIPO ON T2 (TIPO)

Uma forma de consultar métricas de armazenamento é baseada na tabela SysIndexes (SQL Server 2000) ou na View de mesmo nome (SQL Server 2005). Inicialmente (até pelo nome), essa tabela contém a relação de todos os índices existentes no banco de dados. Na verdade há mais que isso. Toda tabela terá pelo uma entrada em SysIndexes e o campo IndID tem um papel importante na diferenciação dos registros se houver mais de um por tabela. Quando a coluna IndID é igual a zero, significa que o objeto em questão trata-se de uma Heap Table. Quando o IndID é igual a uma significa que o objeto em questão possui um índice clusterizado. Se IndID for maior que 1, os dados referem-se a índices nonclustered e estatísticas. Toda tabela possui uma entrada em SysIndexes quer seja referente a uma Heap Table (IndID =0) quer seja referente a um índice clusterizado (IndID = 1). Não há nenhum impeditivo para que uma Heap Table tenha índices NonClustered (IndID > 1).

Algumas colunas sobre essa tabela merecem atenção já que revelam boa parte dos dados retornados pela sp_spaceused:

  • ID – ID do objeto
  • IndID – Tipo de Estrutura de Indexação ou Estatística (previamente comentada)
  • DPages – Total de Páginas destinadas a dados presentes nos nós folhas
  • Used – Total de Páginas de 8K realmente ocupada
  • Reserved – Total de Páginas de 8K para os dados previamente reservada
  • Name – Nome da estrutura de Indexação ou Estatística

Uma forma fácil de entender como relacionar essa colunas é executar a sp_spaceused e correlacionar as colunas (ou observar o código fonte da mesma). Se executarmos a stored procedure sp_spaceused contra T1, teremos os seguintes resultados:

name

rows

reserved

data

index_size

unused

T1

10000

728 KB

320 KB

296 KB

112 KB

Um SELECT na SysIndexes contra T1 pode ser feito com o comando abaixo.

SELECT OBJECT_NAME(ID) As Objeto, Name As Indice, Rows As Linhas,
        Reserved * 8 As Reservado, DPages * 8 As Dados, Used * 8 As Utilizado
FROM SYSINDEXES WHERE ID = OBJECT_ID(‘T1’) AND IndID IN (0,1) AND
(NAME NOT LIKE ‘_WA%’ OR NAME IS NULL)

Alguns pontos merecem ser explicados sobre a cláusula WHERE.

  • A função OBJECT_ID foi utilizada para capturar o ID da tabela T1 evitando um JOIN com a SysObjects
  • O predicado IndID IN (0,1) foi utilizado para coletar métricas do índice clustered ou da heap table visto que essas duas estruturas representam a tabela em si. 
  • O predicado NOT LIKE ‘_WA%’ foi utilizado para descartar as estatísticas (embora estatísticas não tenham o IndID 0 ou 1)
  • O predicado NAME IS NULL foi utilizado, pois, Heap Table não tem nome já que não representam um índice

Por uma mera questão visual, é possível identificar algumas "coincidências" entre o resultado da SP e da consulta (mesmo sem olhar o fonte da SP).

name

rows

reserved

data

index_size

unused

T1

10000

728 KB

320 KB

296 KB

112 KB

Objeto

Nome

Linhas

Reservado

Dados

Utilizado

T1

IXCT1_ID

10000

728

320

616

A utilização da stored procedure sp_spaceused e a consulta contra a SysIndexes revelam informações em comum, mas parece que a stored procedure sp_spaceused retorna informações que a SysIndexes aparentemente não possui e vice-versa. Embora a SysIndexes não possua as informações "prontas", nada impede que as mesmas sejam calculadas superando inclusive as retornadas pela SysIndexes.

SELECT OBJECT_NAME(ID) As Objeto, Rows As Linhas,
        Reserved * 8 As Reservado, DPages * 8 As Dados,
        (Used – DPages) * 8 As Indice,
        (Reserved – Used) * 8 As NaoUtilizado
FROM SYSINDEXES WHERE ID = OBJECT_ID(‘T1’) AND IndID IN (0,1) AND
        (NAME NOT LIKE ‘_WA%’ OR NAME IS NULL)

O resultado obtido é bem familiar:

Objeto

Linhas

Reservado 

Dados

Indice

NaoUtilizado

T1

10000

728

320

296

112

A lógica para obter o mesmo retorno da sp_spaceused é bem simples. Basta seguir algumas regras:

  1. Toda tabela será Heap ou Clustered Index (por isso o filtro em IndID indicando 0 para heap table ou 1 para clustered index).
  2. Toda tabela terá uma quantidade de páginas reservadas (mesmo que não utilizada) representada pela coluna Reserved
  3. A diferença entre o reservado e o utilizado significa área não utilizada
  4. Mesmo que haja um filtro em IndID para 0 (heap table) e 1 (clustered index), os valores em Reserved e Used contabilizam o espaço dos demais índices.
  5. A parte utilizada será dividida entre os dados, índices nonclustered e as páginas que mapeiam esses dados (Root e NonLeaf Levels para índices Clustered e IAMs para Heap Tables)

De acordo com a regra 2, a tabela tem 728Kb alocados que é resultado da multiplicação de 91 páginas pelo valor da página que corresponde a 8K.

De acordo com a regra 3, a tabela tem 91 páginas reservadas que correspondem a 728Kb e 77 páginas efetivamente utilizadas que correspondem a 616Kb. A diferença entre essas duas métricas é de 14 páginas que corresponde a 112Kb de área não utilizada.

De acordo com a regra 5, da parte realmente utilizada, o que não for dados será área destinada a mapear a localização dos dados e índices auxiliares (nonclustered). Se a parte utilizada corresponde a 77 páginas (616KB) e dessa parte há 40 páginas de dados (320KB) restam então 37 páginas (296Kb) que representam a área de mapeamento e índices.

Essas mesmas regras podem ser aplicadas sobre as tabelas T2 e T3 e os resultados serão idênticos aos retornados pela stored procedure sp_spaceused.

Uma das vantagens imediatas da utilização da SysIndexes sobre a stored procedure sp_spaceused é o fato de que ela pode retornar métricas sobre todas as tabelas de uma só vez. Isso é possível de ser realizado com a stored procedure sp_spaceused como foi demonstrado no artigo anterior, mas a utilização da SysIndexes dispensa o uso dos cursores. É necessário apenas eliminar um filtro da cláusula WHERE ou opcionalmente indicar as tabelas de interesse através da função OBJECT_ID ou uma comparação (Join ou Subquery) contra a SysObjects (recomendável para descartar as tabelas de sistema da consulta).

SELECT OBJECT_NAME(ID) As Objeto, Rows As Linhas,
        Reserved * 8 As Reservado, DPages * 8 As Dados,
        (Used – DPages) * 8 As Indice,
        (Reserved – Used) * 8 As NaoUtilizado
FROM SYSINDEXES
WHERE
    ID IN (SELECT ID FROM SYSOBJECTS WHERE NAME LIKE ‘T%’)
    AND IndID IN (0,1) AND (NAME NOT LIKE ‘_WA%’ OR NAME IS NULL)

O resultado obtido contempla o retorno da procedure sp_spaceused contra cada uma das três tabelas independente de sua estrutura (Heap Table ou Clustered Index)

Objeto

Linhas

Reservado 

Dados

Indice

NaoUtilizado

T1

10000

728

320

296

112

T2

8000

592

336

160

96

T3

6500

136

112

8

16

Um outro detalhe muito interessante, é que a SysIndexes possui dados dos índices NonClustered. Embora os índices NonClustered não façam parte da tabela ou tenham qualquer influência sobre sua organização, normalmente há um interesse em considerá-los nas métricas de armazenamento, uma vez que o tamanho desses índices é diretamente influenciado pelo tamanho da tabela já que ambos variam em função da quantidade de registros. O espaço ocupado pelos índices Nonclustered é considerado mesmo que se utilize o filtro baseado no IndID, mas se os índices nonclustered possuem um registro em separado, é possível contabilizar seu tamanho em separado. A consulta abaixo retorna o tamanho da tabela e adiciona o espaço alocado e utilizado por índices nonclustered.

SELECT OBJECT_NAME(ID) As Objeto, Rows As Linhas,
        Reserved * 8 As Reservado, DPages * 8 As Dados,
        (Used – DPages) * 8 As Indice,
        (Reserved – Used) * 8 As NaoUtilizado,

        (SELECT SUM(Reserved * 8) FROM SYSINDEXES As TInt
        WHERE TOut.ID = TInt.ID AND
        IndID > 1 AND NAME NOT LIKE ‘_WA%’) As ReservadoNC,

        (SELECT SUM(Used * 8) FROM SYSINDEXES As TInt
        WHERE TOut.ID = TInt.ID AND
        IndID > 1 AND NAME NOT LIKE ‘_WA%’) As AlocadoNC,

        (SELECT SUM((Reserved – Used) * 8) FROM SYSINDEXES As TInt
        WHERE TOut.ID = TInt.ID AND
        IndID > 1 AND NAME NOT LIKE ‘_WA%’) As NaoAlocadoNC

FROM SYSINDEXES AS TOut

WHERE
    ID IN (SELECT ID FROM SYSOBJECTS WHERE NAME LIKE ‘T%’)
    AND IndID IN (0,1) AND (NAME NOT LIKE ‘_WA%’ OR NAME IS NULL)

A consulta acima retorna as métricas de armazenamento das tabelas bem como o espaço ocupado por seus índices nonclustered. A tabela T3 não possui nenhum índice clustered e por isso não possui métricas referentes a esse índice (razão do NULL no resultado).

Objeto

Linhas

Reservado 

Dados

Indice

NaoUtilizado

ReservadoNC

AlocadoNC

NaoAlocadoNC

T1

10000

728

320

296

112

392

280

112

T2

8000

592

336

160

96

200

152

48

T3

6500

136

112

8

16

NULL

NULL

NULL

De posse desses valores pode-se fazer outras considerações como calcular as métricas da tabela de forma a não considerar seus índices nonclustered. Como as colunas Reserved e Used consideram os valores dos índices nonclustered (mesmo com o filtro em IndID), basta subtrair dessas colunas a somatória dos valores ocupados pelos índices Nonclustered através do SELECT abaixo:

SELECT OBJECT_NAME(ID) As Objeto, Rows As Linhas,

        (Reserved * 8) – ISNULL(
        (SELECT SUM(Reserved * 8) FROM SYSINDEXES As TInt
        WHERE TOut.ID = TInt.ID AND
        IndID > 1 AND NAME NOT LIKE ‘_WA%’),0) As Reservado,

                DPages * 8 As Dados,

        (Used * 8) – ISNULL(
        (SELECT SUM(Used * 8) FROM SYSINDEXES As TInt
        WHERE TOut.ID = TInt.ID AND
        IndID > 1 AND NAME NOT LIKE ‘_WA%’),0) As Utilizado,

        ((Reserved – Used) * 8) – ISNULL(
        (SELECT SUM((Reserved – Used) * 8)FROM SYSINDEXES As TInt
        WHERE TOut.ID = TInt.ID AND
        IndID > 1 AND NAME NOT LIKE ‘_WA%’),0) As NaoAlocado

FROM SYSINDEXES AS TOut

WHERE
    ID IN (SELECT ID FROM SYSOBJECTS WHERE NAME LIKE ‘T%’)
    AND IndID IN (0,1) AND (NAME NOT LIKE ‘_WA%’ OR NAME IS NULL)

O resultado é expresso abaixo:

Objeto

Linhas

Reservado 

Dados

Utilizado 

NaoUtilizado

T1

10000

336

320

336

0

T2

8000

392

336

344

48

T3

6500

136

112

120

16

Essas são as métricas reais das tabelas sem considerar o uso de índices. Uma forma de verificar se de fatos as métricas estão certas, é eliminar os índices nonclustered e executar novamente a procedure sp_spacedused contra cada tabela.

— Verifica o espaço alocado
EXEC sp_spaceused ‘T1’
EXEC sp_spaceused ‘T2’
EXEC sp_spaceused ‘T3’

— Exclui os índices nonclustered
DROP INDEX T1.IXNT1_IDSEC
DROP INDEX T2.IXNT2_TIPO

— Verifica novamente o espaço alocado
EXEC sp_spaceused ‘T1’
EXEC sp_spaceused ‘T2’
EXEC sp_spaceused ‘T3’

Os mais atenciosos podem perguntar se o espaço ocupado pelos índices nonclustered também foi incluído como métrica de espaço ocupado, por que não incluir o tamanho alocado pelas estatísticas ? Afinal elas também irão variar de acordo com o número de registros. Esse raciocínio faz sentido e com certeza estatísticas ocupam espaço. Entretanto seu espaço pode ser desprezado e ainda que se optasse por considerá-lo, a SysIndexes não armazena o espaço ocupado pelas estatísticas. Caso o predicado "NAME NOT LIKE ‘_WA%’" fosse retirado das subqueries, o resultado continuaria o mesmo. A utilização desse predicado foi incluído apenas para propósitos de clareza nos resultados. Embora seu espaço não seja contabilizado em SysIndexes elas poderiam retornar falsas contagens de índices Nonclustered.

Pode-se argumentar que utilizar a procedure sp_spaceused dispensa conhecimentos mais avançados sobre a SysIndexes e que isso "facilitaria" para usuários mais inexperientes além de proteger-se contra uma eventual mudança nas tabelas de sistema. Não posso deixar de considerar essas afirmações, mas as vantagens da utilização da SysIndexes são muito claras, uma vez que permite Joins com outros objetos e fornece informações que a stored procedure sp_spaceused não fornece. Em todo caso, é bem verdade que utilizar tabelas de sistema diretamente pode introduzir problemas uma vez que sua continuidade não é garantida. Tenha cuidado para não "abusar" da SysIndexes e se surpreender se um upgrade de versão ou Service Pack fizer com que ela retorne valores inesperados.

Por hoje é só pessoal. Já está tarde e uma merecida semana de recesso me aguarda. Vejo vocês no ano que vem.

[ ]s,

Gustavo

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s