SQL Server Administração

Identificar qual query está enchendo o log do tempdb

Posted on by Wesley MotaLeave a Comment on Identificar qual query está enchendo o log do tempdb

Identificar qual query está enchendo o log do tempdb
Aprenda como Identificar qual query está enchendo o log do tempdb.

Para quem gostaria de identificar qual query está enchendo o log do tempdb abaixo colocarei algumas dicas:

Query:

;WITH tab AS (
 SELECT session_id,
 request_id,
 SUM(internal_objects_alloc_page_count) AS alloc_pages,
 SUM(internal_objects_dealloc_page_count) AS dealloc_pages
 FROM sys.dm_db_task_space_usage WITH (NOLOCK)
 WHERE session_id <> @@SPID
 GROUP BY session_id, request_id
)
SELECT TSU.session_id,
 TSU.alloc_pages * 1.0 / 128 AS [Espaço interno do objeto em MB],
 TSU.dealloc_pages * 1.0 / 128 AS [Espaço Interno do Objeto Desalocado em MB],
 EST.text,
 -- Extraindo o texto da Query
 ISNULL(
 NULLIF(
 SUBSTRING(
 EST.text,
 ERQ.statement_start_offset / 2, 
 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset 
 THEN 0 
 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
 ), ''
 ), EST.text
 ) AS [Texto da instrução],
 EQP.query_plan
FROM tab AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
 ON TSU.session_id = ERQ.session_id
 AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC;

Aqui também vou colocar algumas dicas para minimizar esse crescimento.

1. Use poucas tabelas temporárias #tabela e tabelas variáveis @tabela
2. Minimize a manutenção de índices e evite ordenar índice no tempdb (SORT_IN_TEMPDB) se não for necessário.
3. Evite utilizar cursores, cursores estáticos usam work tables no tempdb.
4. Tente evitar spools
5. Evite triggers em operações de bulk
6. Não defina tempdb para ser o banco de dados padrão de todos.
7. Não use MARS.

Você também pode considerar que seu uso de log tempdb pode ser causado por processos internos que você tem pouco ou nenhum controle sobre eles – por exemplo, database mail, notificações de eventos, notificações de consulta e corretor de serviço todos usam tempdb de alguma forma. Você pode parar de usar esses recursos, mas se você estiver usando-os, você não pode ditar como e quando eles usam tempdb.

Identificar qual query está enchendo o log do tempdb
The following two tabs change content below.
My Twitter profileMy Facebook profileMy LinkedIn profile

Wesley Mota

DBA SQL Server
Profissional graduado em Banco de Dados e Sistemas de Informação com mais de 7 anos de experiência em empresas de software. Certificado MCSA Microsoft SQL Server possui intensa vivência em administração de banco de dados, Tunning, Performance SQL Server, levantamento de melhorias e monitoramento de banco de dados e servidores SQL Server. Consultoria SQL Server em diversos clientes no Brasil e ao redor do mundo. Escritor no blog dbasqlserverbr.com.br/blog. Onde compartilha conhecimento, experiências e dicas de performance para DBAs SQL Server. Conhecimentos em Oracle e ambientes de alta disponibilidade. Desenvolvimento de softwares web e mobile.Gerenciamento de equipe e projetos.
My Twitter profileMy Facebook profileMy LinkedIn profile

Latest posts by Wesley Mota (see all)