Montando gráficos de espaço com o SQL

Olá pessoal. Neste artigo vou mostrar uma maneira simples de visualizar graficamente o tamanho de espaço em disco e do transaction log do SQL Server.

Antes de entrar nos detalhes técnicos, gostaria de deixar claro que o que vou mostrar aqui é apenas uma maneira simples de visualizar as informações a respeito do tamanho do banco de dados e do transaction log do SQL Server. Existem outras maneiras de mostrar estes dados, inclusive mais bonitas e sofisticadas. Mas os scripts que apresentarei são simples e permitem visualizar rapidamente uma informação puramente textual diretamente no Query Analyser ou no Management Studio.

A idéia também serve para mostrar que o SQL não precisar ser utilizado exclusivamente para mostrar dados em texto. Com um pouco de criatividade e trabalho podemos facilmente transformar o dado que conhecemos para um formato onde é mais fácil a sua compreensão.

Começaremos pela informação do transaction log. Todo o banco de dados no SQL Server possui um transaction log, que nada mais é do que um conjunto de arquivos que armazena as instruções que serão executadas no banco de dados.

Para visualizar rapidamente qual é o tamanho total do transaction log de todos os bancos de dados de um SQL Server basta utilizar o comando DBCC SQLPERF(LOGSPACE). Este comando mostra o tamanho dos transaction log de todos os bancos de dados, em megabytes, e também o quanto deste total está ocupado, em percentual. A Figura 1 mostra um exemplo do retorno do comando DBCC SQLPERF(LOGSPACE):

Figura 1. Resultado do comando DBCC SQLPERF(LOGSPACE).

A partir da Figura 1 podemos perceber que o resultado apresentado é puramente textual. Além da coluna Database Name, temos também a coluna que traz o tamanho total do transaction log, independente do número de arquivos, o quanto o transaction log está cheio e o status do Transaction Log. Obviamente este comando é muito útil, porém precisamos gastar um pouco mais de nossa cognição para interpretar os dados.

O que sugiro para facilitar esta visualização é uma stored procedure que transforma o valor percentual em uma barra horizontal. Deste modo fica mais fácil interpretar os dados e visualizar rapidamente se algum transaction log está perigosamente cheio.

Para montar esta procedure o que devemos fazer é pensar em uma regra de três e utilizar um caractere especial para indicar uma unidade de preenchimento. Neste exemplo utilizei um caractere que parece uma estrela (CHAR(164)) junto com função REPLICATE() do SQL Server para obter quantas estrelas devem ser preenchidas para cada linha. Além disso, também coloquei um parâmetro para indicar que o resultado deve ser ordenado por nome do banco, tamanho do transaction log ou pelo transaction log que mais estiver cheio.

O resultado desta stored procedure, que se chama ST_TAMANHO_LOG e passa como parâmetro o número 1 indicando ordem por preenchimento do transaction log, pode ser visualizado na Figura 2. Os resultados foram trazidos no formato de texto para melhor visualização.

Figura 2. Resultado da execução da stored procedure ST_TAMANHO_LOG.

Tão importante quando mostrar o tamanho do transaction log é mostrar o quanto de espaço reserva existe nos banco de dados de um servidor. Porém para mostrar estes valores é preciso utilizar o comando DBCC SHOWFILESTATS, que retorna quantas extents estão alocadas para o banco de dados e quantas estão efetivamente sendo utilizadas. Este comando retorna as informações apenas para o banco de dados atual e separadas por cada arquivo de dados.

A Figura 3 mostra um exemplo do comando DBCC SHOWFILESTATS sendo executado no banco de dados de exemplo do SQL Server 2005 chamado AdventureWorks.

Figura 3. Resultado da execução do comando DBCC SHOWFILESTATS.

Como cada extent equivale a 64k precisamos realizar uma pequena continha para saber qual é o tamanho total do banco de dados (apenas para os dados) e o quanto deste total está ocupado.

Para facilitar a observação destes dados criei uma stored procedure chamada ST_TAMANHO_BASES, que além de mostrar em uma barra horizontal o tamanho das bases de dados já faz a conta das extents. Da mesma forma que a procedure ST_TAMANHO_LOG, a stored procedure ST_TAMANHO_BASES também aceita como parâmetro um valor numérico que indica qual será a ordem dos resultados. De acordo com o parâmetro, pode-se mostrar o resultado em ordem de nome do banco de dados, em ordem do tamanho do banco de dados (total) ou em ordem de preenchimento da base. A Figura 4 traz o resultado da execução da stored procedure ST_TAMANHO_BASES, retornando os dados de acordo com o preenchimento das bases.

Figura 4. Resultado da execução da stored procedure ST_TAMANHO_BASES.

A partir da idéia mostrada neste artigo é possível desenvolver algumas rotinas que facilitam o trabalho de visualização de dados puramente textuais. Desta maneira fica mais fácil e prático visualizar informações essenciais para a administração de bases de dados em um servidor SQL Server.

Para fazer o download do script contendo as stored procedures apresentadas neste artigo, basta clicar aqui.

Um grande abraço e até a próxima pessoal!