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

Oi Pessoal,

Uma necessidade comum na administração de banco de dados é obter informações a cerca de tamanho e número de registros de uma tabela, um determinado grupo de tabelas ou ainda de todo o banco de dados. Essas informações tem diversas finalidades como auxiliar na estratégia de reindexação (reconstruir vs reorganizar), planejar alterações de estrutura, elaborar rotinas de cargas, etc. Existem diversas formas de obter esse tipo de métrica.

A forma mais elementar de obter o número de linhas e o espaço ocupado é através da stored procedure sp_spaceused. Essa stored procedure retorna não somente o número de linhas e o espaço ocupado, mas informações sobre espaço não utilizado, espaço ocupado pelos índices, etc. Antes de propriamente utilizá-la, criarei uma tabela para observarmos os resultados.

— Cria a tabela
CREATE TABLE Pessoas (ID INT, Nome VARCHAR(50), CPF CHAR(11))

— Retorna métricas de armazenamento da tabela Pessoas
EXEC sp_spaceused ‘Pessoas’

Os dados são exibidos conforme o quadro abaixo:

name

rows

reserved

data

index_size

unused

Pessoas

0

0 KB

0 KB

0 KB

0 KB

Ao executar a procedure sp_spaceused, nota-se que a tabela Pessoas não tem nenhuma linha, não ocupa nenhum espaço, não tem espaço alocado e nem espaço utilizado. Isso é esperado já que a tabela está vazia. Até mesmo uma tabela vazia ocupa espaço, pois, suas páginas de metadados são criadas junto com a tabela, mas essa procedure não leva isso em consideração. Após inserir alguns registros, essas métricas são alteradas:

— Popula alguns registros
DECLARE @Iterador INT
SET @Iterador = 1

WHILE @Iterador <= 1000
BEGIN
    INSERT INTO
Pessoas (ID, Nome, CPF)
    VALUES (@Iterador, ‘Espaço referente ao registro ‘ + CAST(@Iterador AS VARCHAR(4)),
    REPLICATE(CAST((@Iterador % 10) AS CHAR(1)),11))
    SET @Iterador = @Iterador + 1
END

— Retorna métricas de armazenamento da tabela Pessoas
EXEC sp_spaceused ‘Pessoas’

O resultado da execução da stored procedure é:

name

rows

reserved

data

index_size

unused

Pessoas

1000

72 KB

64 KB

8 KB

0 KB

A tabela Pessoas tem 1000 linhas e ocupa uma espaço de 72KB sendo que desses 72KB há 64KB gastos pelos registros (Data) e 8KB gastos pelos índices. Embora não tenha sido criado nenhum índice para essa tabela, isso não significa que não exista nenhuma estrutura de indexação presente. Mesmo tabelas sem nenhum índice, possuem estruturas de índices interna já que é necessário mapear as páginas dessa tabela. Essa é a razão pela qual os 8KB estão alocados. Para que o exemplo fique um pouco mais interessante, alguns índices devem ser criados.

— Criação de índices
CREATE CLUSTERED INDEX IC_Pessoas_ID ON Pessoas (ID)
CREATE NONCLUSTERED INDEX IC_Pessoas_CPF ON Pessoas (CPF)

— Retorna métricas de armazenamento da tabela Pessoas
EXEC sp_spaceused ‘Pessoas’

Após a criação dos índices, os resultados ficaram um pouco diferentes:

name

rows

reserved

data

index_size

unused

Pessoas

1000

176 KB

64 KB

56 KB

56 KB

Dessa vez apareceu um espaço não utilizado (unused). Isso deve ao fato de que as páginas de índices foram alocadas, mas não foram totalmente utilizadas. Embora o espaço ocupado pelo índice e o espaço não utilizado seja o mesmo (o que poderia sugerir um problema), é preciso considerar que essa tabela é extremamente pequena e é comum que alguns "desperdícios" ocorram em tabelas muito pequenas.

O retorno da procedure permite algumas conclusões interessantes. A primeira delas refere-se a obtenção de um tamanho médio do registro. Se 1000 linhas ocupam 176KB, podemos afirmar que em média cada registro ocupa 176 bytes. Essa é uma das mais importantes métricas para prever o tamanho futuro de um banco de dados. Se soubermos o número esperado de linhas, basta multiplicar esse número por 176 bytes e obter uma estimativa futura. O ideal é trabalhar com uma carga de registros considerável para que as distorções de tabelas pequenas não influenciem a precisão do tamanho do registro. A segunda refere-se a uma fórmula matemática (Reserved = Data + Index_Size + Unused).

A utilização da procedure sp_spaceused pode ser utilizada para retornar métricas de tamanho do banco de dados. Basta utilizá-la sem parâmetros. A execução abaixo foi realizada contra o tempdb.

— Muda o contexto do banco de dados
USE TempDB;

— Retorna métricas de armazenamento
EXEC sp_spaceused @updateusage = ‘true’

O resultado obtido é um pouco diferente de quando a procedure é executada contra uma tabela.

database_name

database_size

unallocated space

tempdb

9.50 MB

6.67 MB

reserved

data

index_size

unused

1360 KB

592 KB

648 KB

120 KB

O resultado é consolidado e representa a soma das métricas de armazenamento de todas as tabelas. Algumas situações mostrarão uma grande divergência entre o tamanho exibido (database_size) e o tamanho real do banco de dados. A principal razão para essa diferença é que a stored procedure sp_spaceused retornará sempre o espaço referente à área de dados. O tamanho do banco dependerá dessa área e adicionalmente a área do log de transações. A área do log de transações não é considerada no retorno da stored procedure sp_spaceused. Quanto maior o tamanho do log de transações, maior será a divergência.

A stored procedure sp_spaceused baseia-se em estatísticas. Ainda que a atualização de estatísticas seja automática (auto update statistics) ela é realizada em intervalos. Isso possibilita que quando a stored procedure sp_spaceused seja executada, as estatísticas estejam desatualizadas levando a valores errados (tipicamente negativos). O parâmetro @updateusage força que as estatísticas necessárias sejam atualizadas. Esse parâmetro deve ser utilizado quando houver suspeita a cerca dos resultados. Não o utilize em demasia, pois, o overhead de atualização de estatísticas irá retardar o retorno além de demandar mais trabalho ao banco de dados ou tabela em questão.

A principal limitação da procedure sp_spaceused é que ela só retorna as informações de uma tabela por vez. Na ausência de parâmetros, o retorno é em nível de banco que significa a consolidação de todas as tabelas. Não existe nenhuma variação para mostrar os dados de todas as tabelas de uma só vez. Se essa é a necessidade, é necessário executá-la múltiplas vezes através de um cursor.

— Declara uma tabela temporária
— A tabela será usada para coletar as métricas de todas as tabelas

CREATE TABLE #Resumo (
    Name NVARCHAR(128),
    Rows CHAR(11),
    Reserved VARCHAR(18),
    Data VARCHAR(18),
    Index_Size VARCHAR(18),
    Unused VARCHAR(18))

— Declara uma variável para armazenar o nome da tabela
DECLARE @Tabela NVARCHAR(128)

— Declara um cursor para ler todas as tabelas
DECLARE Tabelas CURSOR
FAST_FORWARD FOR
SELECT
TABLE_SCHEMA + ‘.’ + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’

OPEN Tabelas

FETCH NEXT FROM Tabelas INTO @Tabela

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO
#Resumo EXEC sp_spaceused @Tabela
    FETCH NEXT FROM Tabelas INTO @Tabela   
END

CLOSE Tabelas

DEALLOCATE Tabelas

— Retorna as métricas
SELECT Name, Rows, Reserved, Data, Index_Size, Unused FROM #Resumo

DROP TABLE #Resumo

Como eu havia dito, a stored procedure sp_spaceused é uma das alternativas para retornar o número de linhas e o espaço ocupado por tabela mas certamente ela não é a única. Existem algumas alternativas mais diretas. Demonstrarei-as futuramente.

[ ]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