Loading…

Querys do Dia a Dia: Como encontrar as conexões que mais ocupam espaço no Tempdb

Fala Pessoal,

Um certo dia, no meio do meu expediente normal de trabalho, recebi a notificação abaixo por e-mail:

T: Label:TempDB is 92 Percentage, threshold value for this monitor is 80 Percentage

Estava acabando o espaço em disco no drive T: onde o Tempdb está armazenado em um dos  meus servidores SQL Server.

Na mesma hora, executei a query abaixo, que identifica o espaço que está sendo utilizado por cada conexão do SQL Server:

SELECT A.session_id,B.host_name, B.Login_Name ,
(user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 as TotalalocadoMB,
D.Text
FROM sys.dm_db_session_space_usage A
JOIN sys.dm_exec_sessions B  ON A.session_id = B.session_id
JOIN sys.dm_exec_connections C ON C.session_id = B.session_id
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) As D
WHERE A.session_id > 50
and (user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 > 100 — Ocupam mais de 100 MB
ORDER BY totalalocadoMB desc
COMPUTE sum((user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128)

Com essa query, identifiquei uma conexão de uma pessoa da nossa própria equipe de TI que estava ocupando 51.5 GB do Tempdb.

Na mesma hora matei a conexão e fui obrigado a executar um Shrink(Fui Obrigado!) para liberar espaço no drive do Tempdb. Caso você não consiga diminuir o espaço ocupado pelo TempDB executanto um shrink, que foi exatamente o meu caso, esse artigo é a solução para esse problema: tempdb, Please allow me to shrink you.

As duas variáveis que são somadas para contar o espaço alocado do Tempdb tem as seguintes propriedades:
User_objects_alloc_page_count – Número de páginas reservadas ou alocadas para objetos de usuários, tais como:

  • Tabelas e Índices de Usuários
  • Tabelas e Índices de Sistema
  • Tabelas e Índices temporários globais
  • Tabelas e Índices temporários locais
  • Tabelas de variáveis
  • Tabelas retornadas em uma Table-Function

Internal_objects_alloc_page_count – Número de páginas reservadas ou alocadas para objetos internos, tais como:

  • Work Tables para cursor, Spool Operations e LOB storage
  • Work Files para operações como Hash Join
  • Sort Runs

Essa query de monitoramento do espaço alocado no TempDB, retorna apenas informações de consultas que já rodaram, portanto, as querys em execução não são consideradas. Para monitorar as querys que ainda estão em execução, aconselho a utilização da procedure Who_is_Active.

Gostou dessa dica?

Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.

Aprenda a criar essa e outras rotinas para administrar melhor seu SQL Server no meu Treinamento de Tarefas do Dia a Dia de um DBA.

Abraços,

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006

8 thoughts on “Querys do Dia a Dia: Como encontrar as conexões que mais ocupam espaço no Tempdb

  1. Fabrício, se me permitir, fiz uma versão sem utilizar o COMPUTE (já que na versão posterior ao SQL 2008 R2 ela não existirá mais) e mostrando o somatório em um mesmo result set.

    ;with tab(session_id, host_name, login_name, totalalocadomb, text)
    as(
    SELECT a.session_id,
    b.host_name,
    b.login_name,
    ( user_objects_alloc_page_count + internal_objects_alloc_page_count ) * 1.0 / 128 AS totalalocadomb,
    d.TEXT
    FROM sys.dm_db_session_space_usage a
    JOIN sys.dm_exec_sessions b ON a.session_id = b.session_id
    JOIN sys.dm_exec_connections c ON c.session_id = b.session_id
    CROSS APPLY sys.Dm_exec_sql_text(c.most_recent_sql_handle) AS d
    WHERE a.session_id > 50
    AND ( user_objects_alloc_page_count + internal_objects_alloc_page_count ) * 1.0 / 128 > 100 — Ocupam mais de 100 MB
    )
    select * from tab
    union all
    select null,null,null,sum(totalalocadomb),null from tab

  2. Ola Fabricio, blz? Show de bola esse post ai. Eu ja tive um problema parecido no meu ambiente. Fiz uma modificação na query que você postou pra considerar os comandos que estão sendo executadas na tempdb. Apenas adicionei a sys.dm_exec_requests no lugar da sys.dm_exec_connections. Segue alteração:
    JOIN sys.dm_exec_requests c ON c.session_id = b.session_id
    CROSS APPLY sys.Dm_exec_sql_text(c.sql_handle) AS d
    Parabéns…..
    Absss

  3. Fala Fabricio, tudo bem?

    Cara, uma pequena dúvida:

    Ao realizar a query, me retornou que tenho uma conexão que está com 76gb de espaço alocado, porém, minha Tempdb está com 43gb, aonde ele alocou essa diferença de 30gb?

    Valew cara!

Deixe uma resposta