Calculando o tamanho de cada tabela no SQL Server

Hoje vou descrever uma solução que adotei durante uma visita a um cliente que estava com problemas de tamanho e performance no banco de dados. Ele utiliza Microsoft SQL Server 2005 para armazenar os dados do seu aplicativo. O problema é que precisávamos detectar tabelas que estavam grandes demais, e estavam absorvendo muitos recursos de armazenamento (tanto no servidor como no backup) e também recursos de processamento e memória. Só não confunda espaço do banco de dados com arquivo de LOG, que abordarei em um próximo artigo.

O trabalho teve várias etapas, porém vou descrever as que trouxeram maior resultado efetivo. Eu dividi o trabalho em duas partes. Primeiro identifiquei as tabelas que ocupando mais espaço. Depois, remodelei as tabelas de forma a utilizarem o espaço do banco de dados de forma mais adequada. Muitas vezes, um sistema utilizado em diversos segmentos, pode acabar tendo suas dimensões desproporcionais. Vou dar um exemplo muito simples. Você tem um sistema ERP que foi desenhado para indústrias, porém o implanta em uma distribuidora que emite 300 notas fiscais por dia, diferente do que havia sido previsto quando o sistema foi pensado para uma fábrica de médio porte. Neste exemplo, se as tabelas de notas fiscais conter campos mal dimensionados, ou relacionamentos redundantes, podem acarretar problemas de espaço e performance a longo prazo.

Neste artigo, vou abordar exclusivamente o cálculo do tamanho das tabelas. As modificações estruturais de tabelas, e demais otimizações, não vou abordar neste momento. O primeiro código que vamos analisar, é uma rotina que criei que relaciona todas as tabelas no banco de dados, calcula individualmente o espaço utilizado por cada uma e exibe no final um relatório ordenado pelas tabelas que mais utilizam espaço do banco.

sys.sp_spaceused

Este procedimento interno do SQL Server retorna informações sobre o tamanho do banco de dados, e também especificamente de tabelas passadas como parâmetro.

Code Snippet

1.             exec sp_spaceused-- retorna informações do banco
2.  
3. OU
4.  
5. exec sp_spaceused 'cadastro' -- retorna informações da tabela

 

RESERVED: Espaço total reservado pelo banco de dados “para armazenamento de dados”. Por mais que sejam excluídos registros do banco, ele nunca reduz o seu tamanho (a não ser que seja feito o Shrink Database, que irá devolver ao disco o espaço UNUSED do banco).

DATA: Quantidade em KB de dados no banco de dados

INDEX_SIZE: Tamanho utilizado somente para a indexação de valores. Muitos índices em um banco podem elevar consideravelmente o tamanho do banco, é necessário estar atento.

UNUSED: Espaço que foi alocado ao banco de dados, porém não está sendo utilizado. Este espaço pode ser retomado ao disco através da ferramenta Shrink Database.


Quando passamos o nome de uma tabela como parâmetro, obtemos a mesma informação, porém os valores informados especificamente da tabela, com a diferença que temos uma coluna a mais, chamada ROWS, que retorna o número de linhas nesta tabela:

 

Utilizando esta ferramenta do sistema, criei o seguinte procedimento:

Code Snippet

1. create proc sp_espacotabela
2. as
3.  
4. /*
5.   Fabiano Cores 02.12.2010
6.   http://sqlburger.blogspot.com
7. */
8.  
9.   declare @vname sysname
10.   declare @tmpTamTabela table (
11.     name       sysname     null
12.   , rows       int         null
13.   , reserved   varchar(25) null
14.   , data       varchar(25) null
15.   , index_size varchar(25) null
16.   , unused     varchar(25) null )
17.  
18.   declare cp1 cursor local fast_forward read_only for
19.     select name
20.       from sysobjects
21.      where type = 'U'
22.      order by name
23.  
24.   open cp1
25.  
26.   while 1 = 1
27.   begin
28.     fetch next from cp1 into @vname
29.     if @@fetch_status <> 0 break
30.  
31.     insert into @tmpTamTabela (name, rows, reserved
32.                              , data, index_size, unused)
33.       exec sp_spaceused @vname
34.  
35.   end
36.   close cp1
37.   deallocate cp1
38.  
39.   select name as 'Nome'
40.        , rows as 'Linhas'
41.        , convert(int, replace(reserved, ' KB','')) as 'Tamanho total'
42.        , convert(int, replace(data, ' KB',''))as 'Dados'
43.        , convert(int, replace(index_size, ' KB',''))as 'Index'
44.        , convert(int, replace(unused, ' KB',''))as 'Não utilizado'
45.     from @tmpTamTabela
46.    order by convert(int, replace(reserved, ' KB','')) desc

O código acima já está preparado para ser salvo no banco de dados, através do nome sp_espacotabela. Vamos ver agora sua execução, e o resultado apresentado:

 

O resultado é um relatório que retorna todas as tabelas, e os respectivos valores de consumo de tamanho em disco, conforme explicado acima. Você pode exportar esta lista para o excel, e fazer várias visões, como analisar índices x quantidade de linhas, ordenar por espaço não utilizado, etc). Observe que pode ser modificado o último SELECT do procedimento, para ordenar por outro valor. Eu optei em ordenar pelo tamanho total da tabela (RESERVED), que é o que importa nesta análise.

Após visualizar o relatório, pegamos as 10 tabelas mais inchadas do banco (de um banco com aproximadamente 2500 tabelas). Geralmente são as tabelas de LOG, notas fiscais, cupons, etc. Observamos a presença de campos tipo TEXT desnecessários (para armazenar média de 80 a 180 caracteres). Mudamos para CHAR(250), e assim por diante. Com uma pequena modificação como esta, chegamos a otimizar 3GB de uma única tabela (as imagens utilizadas aqui são somente para exemplificar).

Outras tabelas, como notas fiscais e lançamentos financeiros, fizemos uma espécie de datawarehouse, deixando na base de dados de trabalho 3 anos de histórico, os demais anos foram separados em uma base onde os relatórios consolidam estes bancos. A transição dos dados é feita automaticamente a partir de uma JOB, sem a necessidade de intervenção do usuário. O backup do período anterior fica intacto, armazenado em mídia separada. O backup feito diariamente passou a ser dos últimos 3 anos.

Todas estas modificações trouxeram grandes benefícios, como otimização dos backups, mais velocidade em processos críticos do sistema (faturamento, log), banco de dados mais enxuto, sem alocar grandes espaços desnecessários, e assim por diante.

Resultados

No final, aplicando as técnicas aqui descritas, e outras manutenções, o banco de dados de produção, que estava com 13,5GB de tamanho, ficou com modestos 1,8GB. O backup, que é totalmente automatizado, estava levando mais de 5 horas para ser realizado, pois o banco compactado ficava com 3,0GB. Levava 1 hora para compactar, e 4 horas para transmitir. E se houvessem quedas de internet durante a transmissão, esta tinha que ser feita manualmente. Hoje, o arquivo de backup compactado está com 122mb, leva 2 minutos para ser gerado e 20 minutos para ser transmitido.