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
Oi Fabrício,
Parabens pelo blog, muito bom o artigo.
Valeu!
Valeu Felipe.
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
Claro que permito! Fica bem melhor para visualizar em um mesmo result set.
Valeu Vladimir.
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
Show.. Valeu por compartilhar Thiago.
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!
Tudo certo Junio,
Teria que analisar novamente as tabelas da query. Também já peguei alguns casos que não batiam, mas pelo menos me indicavam quem usava mais e eu podia melhorar.