Encontrando tabelas não utilizadas

Boa Tarde Pessoal,

Estou aqui no vôo de volta para Brasília após o Community Zone 2010. O vôo demora algum tempo e para matar o tédio já que não tem mais nada para fazer aqui, resolvi aproveitar o tempo para blogar alguma dica rápida. Mantendo a linha de um artigo recente (Encontrando índices não utilizados), irei postar uma forma razoável de listar as tabelas não utilizadas. Não raras às vezes aparecem situações nas quais a existência de uma ou mais tabelas é "questionável". Isso é bem comum de acontecer, pois, as aplicações evoluem e é normal que novas tabelas surjam e tabelas mais antigas deixem de ser utilizadas. Há também casos onde um acesso indevido (leia-se desenvolvedores com acesso a produção) acaba produzindo aquelas tabelas com o intuito de fazer algum teste rápido na área de produção (normalmente o teste é concluído, mas as tabelas não são excluídas e podem ficar lá durante anos). O fato é que mais tabelas incorrem em mais espaço, mais rotinas de administração, maior janela de backup e certamente um desperdício de recursos. Como fazer então para identificá-las ? Vejamos um exemplo prático:

— Cria cinco tabelas
CREATE TABLE T1 (ID INT NOT NULL, NOME VARCHAR(80))
CREATE TABLE T2 (COD INT NOT NULL, DESCRICAO VARCHAR(50))
CREATE TABLE T3 (MATRICULA CHAR(10) NOT NULL, CPF CHAR(11), NOME VARCHAR(80))
CREATE TABLE T4 (SEQ INT IDENTITY(1,1) NOT NULL, Detalhes XML)
CREATE TABLE T5 (CPF CHAR(11) NOT NULL, Lancamento MONEY)

— Cria três constraints
ALTER TABLE T1 ADD CONSTRAINT PKT1 PRIMARY KEY (ID)
ALTER TABLE T2 ADD CONSTRAINT PKT2 PRIMARY KEY (COD)
ALTER TABLE T3 ADD CONSTRAINT PKT3 PRIMARY KEY (MATRICULA)

— Cria dois índices
CREATE INDEX IX_CPF ON T3 (CPF)
CREATE INDEX IX_NOME ON T3 (NOME)

Uma das minhas DMVs preferidas é a sys.dm_db_index_usage_stats. Essa DMV mostra a utilização dos índices das tabelas de todos os bancos de uma instância. Como toda tabela fatalmente terá uma estrutura de indexação, essa DMV pode ser muito útil para descobrir quando e como essas estruturas foram utilizadas e por consequência a utilização da tabela.

SELECT * FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID() And OBJECTPROPERTYEX(object_id,‘IsUserTable’) = 1

A execução de um comando de SELECT contra essa DMV não irá retornar registros para as tabelas recém criadas já que como elas ainda não foram utilizadas, a DMV não populou os dados de utilização das estruturas de indexação dessas tabelas. Os comandos a seguir executam diversas instruções que referenciam algumas delas.

— Insere dois registros em T1
INSERT INTO T1 (ID, NOME) VALUES (1,‘Bruno’)
INSERT INTO T1 (ID, NOME) VALUES (2,‘Roberto’)

— Insere dois registro em T2
INSERT INTO T2 (COD, DESCRICAO) VALUES (1,‘Nosso equilíbrio na balança global’)
INSERT INTO T2 (COD, DESCRICAO) VALUES (2,‘Educação por um país mais competitivo’)

— Lê dados em T3 (mesmo que não haja registros, o comando é válido)
SELECT * FROM T3 WHERE MATRICULA = ‘MKT0000001’

— Excluí dados em T4 (mesmo que não haja registros, o comando é valido)
DELETE FROM T4

Agora que houve acesso por operações de leitura e gravação, é possível fazer algumas inferências usando a sys.dm_db_index_usage_stats.

SELECT
    OBJECT_NAME(object_id,database_id) As Tabela, Index_Id,
    last_user_seek, last_user_scan, last_user_lookup, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID() And OBJECTPROPERTYEX(object_id,‘IsUserTable’) = 1
ORDER BY Tabela, Index_ID

Tabela Index_ID Seek Scan Lookup Update
T1 1 NULL NULL NULL 2010-02-27 14:24:24.170
T2 1 NULL NULL NULL 2010-02-27 14:24:24.170
T3 1 2010-02-27 14:24:24.170 NULL NULL NULL
T3 2 NULL 2010-02-27 14:24:24.170 NULL NULL
T3 3 NULL 2010-02-27 14:24:24.170 NULL NULL
T4 0 NULL 2010-02-27 14:24:24.170 NULL 2010-02-27 14:24:24.170

As tabelas T1, T2 e T3 possuem uma chave primária clusterizada (padrão) e por isso a coluna Index_ID tem o valor 1. No caso da tabela T3, há ainda dois índices adicionais representados pelos valores 2 e 3 na coluna Index_ID. A tabela T4 não possui índice clustered e por isso a coluna Index_ID está com o valor igual 0 que representa que T4 é uma HEAP TABLE. T5 não foi referenciada e por isso não está contemplada na consulta. O script produziu várias ações de leitura e gravação nas tabelas e por isso as colunas Seek, Scan, Lookup e Update tem diferentes valores. Podemos ver que T5 não está referenciada no resultado da sys.dm_db_index_usage_stats. Basta então combinar a sys.tables com essa DMV para listar as tabelas não utilizadas.

SELECT Name FROM sys.tables As T
WHERE NOT EXISTS (
    SELECT * FROM sys.dm_db_index_usage_stats As U
    WHERE T.object_id = U.object_id AND U.database_id = DB_ID())

Como podemos ver, o único resultado é T5, pois, é justamente essa tabela que não sofreu nenhum acesso quer seja de leitura ou de gravação. A estratégia é interessante, mas é preciso lembrar que a sys.dm_db_index_usage_stats, assim como toda DMV, é automaticamente "zerada" quando o SQL Server é reiniciado. Se uma tabela é utilizada na produção de relatórios mensais, trimestrais, anuais, etc e houver a reinicialização do SQL Server entre esses intervalos, é possível inferir erroneamente que uma tabela não é utilizada (mesmo que ela seja).

Já vi alguns DBAs utilizando scripts semelhantes para excluir as tabelas não utilizadas. É perfeitamente compreensível já que muitas tabelas não utilizadas incorrem em alguns problemas como os abordados no início do artigo. Minha recomendação é que esse script seja utilizado para identificar as potenciais tabelas não utilizadas e bloquear o acesso. Nesse caso, quando alguém desejar acessá-las será possível descobrir que aplicações às utilizam. Se a exclusão for feita ao invés da negação do acesso, quando a tabela for novamente necessária, haverá muito mais esforço em recuperá-la.

[ ]s,

Gustavo

2 Respostas para “Encontrando tabelas não utilizadas

  1. Olá Gustavo. Gostei muito do artigo, isso ajuda e muito no momento que precisamos saber se realmente está sendo utilizada uma determinada tabela. Já trabalhei em um cliente onde ele tinha várias tabelas com o nome de "Teste_alguma_coisa" mas ninguem sabia se ainda estava sendo usada por desenvolvedores ou até mesmo em produção.AbraçoClayton Santos

  2. Oi Clayton,É verdade. Muitas vezes a gente "herda" alguns bancos e tem muitas tabelas potencialmente "duvidosas". Se você gostou desse artigo, acredito que se interessará muito pelo meu artigo mais recente "Como descobrir a data do último acesso a uma tabela ?" disponível em: http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!964.entry%5B ]s,

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