Pesquisar este blog

quinta-feira, 2 de dezembro de 2010

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



image 

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:

image 

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:

image 

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.

14 comentários:

  1. Muito bom, vai ser muito util.

    Estou com uma base de dados com um tamanho monstruoso e preciso esvaziar algumas tabelas.

    Agora ficou mais facil, pois sei quais são elas ;)

    ResponderExcluir
  2. Parabéns, graças a seu comando consegui localizar vários erros de modelagem em um sistema ERP que é uma tartaruga.

    ResponderExcluir
  3. Parabéns, graças a seu comando consegui localizar vários erros de modelagem em um sistema ERP que é uma tartaruga.

    ResponderExcluir
  4. Como eu Faço para consultar o tamanho das tabelas de todos os meus bancos em uma instancia?

    ResponderExcluir
    Respostas
    1. Você me sugeriu um bom artigo. Hoje devo colocá-lo no ar... por favor, acompanhe o blog que eu irei postar uma solução bem bacana...

      Excluir
    2. Segue o artigo, qualquer dúvida avisa aí:

      http://sqlburger.blogspot.com.br/2013/01/tamanho-de-todas-as-tabelas-de-todos-os.html

      Excluir
  5. E no caso de uma tabela particionada?

    Como contar separadamente as partições.

    ResponderExcluir
    Respostas
    1. Luiz, vou pesquisar e retorno com uma resposta... não havia pensado nisto.

      Excluir
  6. Muito bom mesmo o artigo parabéns e muito obrigado.

    ResponderExcluir
  7. tttttttttttttttttttttooooooooooooooooooooooppppppppppppppppppppp

    ResponderExcluir
  8. Tentei dar o commando:

    exec sp_spaceused px1.[/XNFE/B2BSTATUS]

    Error:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '.'.

    ResponderExcluir
  9. Parabéns, ótimo artigo!!

    ResponderExcluir