Sql Server

Como Verificar o Uso de Espaço em Disco para Tabelas no SQL Server Usando T-SQL

Ao gerenciar um banco de dados, entender quanto espaço em disco cada tabela está consumindo é vital para otimização e planejamento de capacidade. Enquanto muitos usuários podem estar acostumados a verificar as propriedades da tabela através da interface gráfica do SQL Server Management Studio (SSMS), saber como realizar a mesma tarefa usando T-SQL pode ser extremamente benéfico, especialmente para automação e tarefas avançadas de gerenciamento de banco de dados.

Neste post do blog, vamos explorar um método simples para determinar o espaço em disco ocupado por tabelas no SQL Server através do T-SQL. Essa abordagem não apenas aumenta sua eficiência, mas também oferece uma oportunidade para se aprofundar na escrita de scripts SQL.

Visão Geral da Solução

Utilizaremos tabelas temporárias juntamente com o procedimento armazenado do sistema sp_spaceused para coletar informações sobre as várias tabelas em seu banco de dados. Aqui está um resumo do que vamos realizar:

  1. Criar tabelas temporárias para armazenar informações de tamanho.
  2. Executar o comando sp_spaceused para coletar dados de tamanho de todas as tabelas.
  3. Analisar os dados coletados em métricas legíveis.
  4. Exibir a saída ordenada pelo tamanho reservado.

Vamos começar!

Passo 1: Criar Tabelas Temporárias

O primeiro passo é definir duas tabelas temporárias que nos ajudarão a armazenar os detalhes de tamanho que vamos extrair. No SQL Server, tabelas temporárias têm o prefixo #.

CREATE TABLE #tmpSizeChar (
     table_name sysname ,
     row_count int,
     reserved_size varchar(50),
     data_size varchar(50),
     index_size varchar(50),
     unused_size varchar(50))                              

CREATE TABLE #tmpSizeInt (
     table_name sysname ,
     row_count int,
     reserved_size_KB int,
     data_size_KB int,
     index_size_KB int,
     unused_size_KB int)

Explicação:

  • #tmpSizeChar: Armazena resultados em formato de caractere, que retém o tamanho com unidades (por exemplo, MB, KB).
  • #tmpSizeInt: Armazena resultados em formato inteiro para facilitar o cálculo.

Passo 2: Coletar Dados de Tamanho das Tabelas

Usamos o procedimento armazenado sp_msforeachtable para executar o comando sp_spaceused em cada tabela do banco de dados. Este comando fornece informações detalhadas sobre a utilização de espaço de cada tabela.

SET NOCOUNT ON
INSERT #tmpSizeChar
EXEC sp_msforeachtable 'sp_spaceused ''?'''

Explicação:

  • NOCOUNT ON: Impede que a mensagem que indica o número de linhas afetadas por uma instrução T-SQL seja retornada, o que melhora o desempenho para scripts com múltiplas instruções.

Passo 3: Analisar os Dados Coletados

Depois de coletar os dados, agora precisamos transformar os dados em caracteres em valores inteiros adequados para análise.

INSERT INTO #tmpSizeInt (
        table_name,
        row_count,
        reserved_size_KB,
        data_size_KB,
        index_size_KB,
        unused_size_KB
        )
SELECT  [table_name],
        row_count,
        CAST(SUBSTRING(reserved_size, 0, PATINDEX('% %', reserved_size)) AS int) reserved_size,
        CAST(SUBSTRING(data_size, 0, PATINDEX('% %', data_size)) AS int) data_size,
        CAST(SUBSTRING(index_size, 0, PATINDEX('% %', index_size)) AS int) index_size,
        CAST(SUBSTRING(unused_size, 0, PATINDEX('% %', unused_size)) AS int) unused_size
FROM #tmpSizeChar

Explicação:

  • Este passo extrai a parte numérica dos tamanhos reservados e os converte em inteiros para cálculos confiáveis. Ele remove essencialmente os caracteres (como KB, MB) das strings de tamanho.

Passo 4: Exibir os Resultados

Por fim, recuperamos e exibimos os resultados resumidos de nossa tabela temporária de tamanho inteiro.

SELECT * FROM #tmpSizeInt
ORDER BY reserved_size_KB DESC

Explicação:

  • Este comando SQL recupera todos os registros de #tmpSizeInt e os ordena pela coluna reserved_size_KB, permitindo que você veja quais tabelas consomem mais espaço em disco.

Conclusão

Seguindo os passos descritos acima, você agora possui um método estruturado para determinar quanto espaço em disco uma tabela está utilizando no SQL Server com T-SQL. Esse método pode ser particularmente útil para administradores de banco de dados e desenvolvedores que buscam manter um desempenho ideal e acompanhar o uso de recursos em seus ambientes SQL Server.

Agora, você pode substituir os cliques na interface gráfica por uma abordagem poderosa na linha de comando, melhorando fundamentalmente suas habilidades de gerenciamento de banco de dados!