Como descobrir a data do último acesso a uma tabela ?

Boa Noite Pessoal,

Quem nunca se deparou com a necessidade de saber quando a tabela foi acessada pela última vez ? Normalmente esse tipo de dúvida é comum para relacionar se uma tabela é ou não utilizada ou ainda qual o momento em que ocorreu algum leitura ou gravação indevida. No SQL Server 2000 havia alguns truques para descobrir, mas além de trabalhoso nem sempre os dados eram precisos. No SQL Server 2005 e posteriores, o processo é relativamente mais fácil, bastando apenas algumas consultas. Aproveitando um pouco os dados e explicações do meu último post, 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)

No último artigo, eu falei um pouco sobre a DMV sys.dm_db_index_usage_stats. Se toda tabela possui uma estrutura de indexação, essa DMV é útil para averiguar a utilização dessas estruturas e consequentemente a utilização das tabelas associadas. O script abaixo provoca intencionalmente acessos a essas tabelas com esperas propositais.

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

— Força uma espera
WAITFOR DELAY ’00:00:04′

— 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’)

— Força uma espera
WAITFOR DELAY ’00:00:05′

— Lê dados em T3 (mesmo que não haja registros, o comando é válido)
— Cover Index no CPF

SELECT CPF FROM T3

— Força uma espera
WAITFOR DELAY ’00:00:03′

— Cover Index no Nome
SELECT
NOME FROM T3

— Força uma espera
WAITFOR DELAY ’00:00:08′

SELECT * FROM T3 WHERE MATRICULA = ‘MKT0000001’

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

Após o acesso através de comandos de leitura e gravação, vejamos o retorno da 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-03-02 18:48:12.573
T2 1 NULL NULL NULL 2010-03-02 18:48:16.590
T3 1 2010-03-02 18:48:32.620 NULL NULL NULL
T3 2 NULL 2010-03-02 18:48:21.590 NULL NULL
T3 3 NULL 2010-03-02 18:48:24.590 NULL NULL
T4 0 NULL 2010-03-02 18:48:32.620 NULL 2010-03-02 18:48:32.620

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.

Para descobrir o momento mais recente em que a tabela que foi acessada, basta olhar a operação sobre os índices que tem a maior data. No caso da tabela T1 isso é fácil de ser realizado, pois, basta apenas olhar a coluna de update já que as demais colunas estão nulas (o que era esperado, pois, o script só fez gravações). O problema ocorre com tabelas que tem mais de um índice como é o caso de T3. É preciso fazer a comparação das colunas Seek, Scan, Lookup e Update para cada índice e verificar a maior data (no caso 02/03/2010 às 18:48:32.620). Isso poderia ser feito com alguns CASEs e GROUP BYs, mas ia tornar a consulta bastante poluída. Uma forma rápida de obter as datas desejadas é a utilização do operador UNPIVOT para transformar as colunas em linhas.

;WITH Utilizacao (Tabela, Index_ID, Seek, Scan, LookUp, [Update])
As (
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)

SELECT * FROM Utilizacao
UNPIVOT
(DataReferencia FOR Operacao IN ([Seek], [Scan], [LookUp], [Update])) As UP
ORDER BY Tabela, Index_ID

Tabela Index_ID Data de Referência Operação
T1 1 2010-03-02 18:48:12.573 Update
T2 1 2010-03-02 18:48:16.590 Update
T3 1 2010-03-02 18:48:32.620 Seek
T3 2 2010-03-02 18:48:21.590 Scan
T3 3 2010-03-02 18:48:24.590 Scan
T4 0 2010-03-02 18:48:32.620 Scan
T4 0 2010-03-02 18:48:32.620 Update

Uma vez que as colunas com as datas foram transformadas em linhas, é muito fácil aplicar a função MAX, para descobrir a última data de referência. Aproveitei para fazer alguns "ajustes" na consulta como mostrar a última operação bem como contemplar as tabelas que não possuem entradas na sys.dm_db_index_usage_stats.

;WITH Utilizacao (object_id, Index_ID, Seek, Scan, LookUp, [Update])
As (
SELECT
    T.object_id, Index_Id,
    last_user_seek, last_user_scan, last_user_lookup, last_user_update
FROM sys.dm_db_index_usage_stats As I
INNER JOIN sys.tables As T ON I.object_id = T.object_id
WHERE database_id = DB_ID()),

Referencias (object_id, DataReferencia, Operacao)
As (
SELECT object_id, DataReferencia, Operacao FROM Utilizacao
UNPIVOT
(DataReferencia FOR Operacao IN ([Seek], [Scan], [LookUp], [Update])) As UP),

UltimoAcesso (object_id, UltimaData)
As (
SELECT object_id, MAX(DataReferencia) FROM Referencias
GROUP BY object_id),

UltimasOperacoes (object_id, UltimaData, Operacoes)
As (
SELECT U.*,
    (SELECT DISTINCT Operacao FROM Referencias As R
    WHERE U.object_id = R.object_id AND U.UltimaData = R.DataReferencia
    FOR XML AUTO)
 FROM UltimoAcesso As U)

SELECT
    Name As Tabela, UltimaData As UltimoAcesso,
    REPLACE(REPLACE(Operacoes,‘"/>’,‘, ‘),‘<R Operacao="’,) As UltimasOperacoes
FROM sys.tables As T
LEFT OUTER JOIN UltimasOperacoes As U ON T.object_id = U.object_id

Tabela Último Acesso Últimas Operações
T1 2010-03-02 18:48:12.573 Update,
T2 2010-03-02 18:48:16.590 Update,
T3 2010-03-02 18:48:32.620 Seek,
T4 2010-03-02 18:48:32.620 Scan, Update,
T5 NULL NULL

A consulta mostra todas as tabelas, quando foi o último acesso e que operações estavam sendo realizadas naquele momento. Embora não tenha colocado no script, é uma boa sugestão retirar os segundos e milissegundos da consulta, pois, dificilmente haverá muitas operações no mesmo milissegundo (deixo essa como dever de casa).

Ainda que a consulta sirva de base para recuperar os últimos acessos a uma tabela, há um ponto "fraco" nessa estratégia. A DMV sys.dm_db_index_usage_stats é zerada toda vez que o SQL Server é reiniciado. Isso pode levar a falsas interpretações principalmente para relatórios trimestrais, anuais, etc caso o SQL Server tem sido reiniciado nesse meio tempo. É preciso levar isso em consideração quando consultas contra as DMVs são elaboradas.

[ ]s,

Gustavo

10 Respostas para “Como descobrir a data do último acesso a uma tabela ?

  1. Muito interessante Gustavo!

  2. Pra variar, perfeita explicação.Obrigado! =D

  3. Oi Pessoal,Obrigado. Que bom ter esse retorno de pessoas como vocês. Eu acho essa dúvida bem comum e resolvi postar. O próximo vai ser um de SPs.[ ]s,

  4. Como sempre, uma AULA ;).

  5. CARA QUE DEUS TE ABENÇOE. POST ANIMAL!!!

  6. Adriana Rinaldi

    Excelente post, parabéns!

  7. Ola Gustavo,

    Otimo script!! Me ajudou muito.

    Muito Obrigado.
    Abraços,
    Marcos Freccia

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