Como descobrir quem / o que está martelando o SQL Server TempDB

Question

Estou usando o SQL Server 2012 Enterprise. O servidor tem um grupo de disponibilidade configurado. Existem vários bancos de dados para diferentes ferramentas na instância. Todas as ferramentas pertencem e, portanto, foram implementadas juntas. Eu reconheci um aumento no contador de perfmon Transaction / sec para TempDB após o rollout. Antes do lançamento, o contador era 30 em média e agora é em torno de 300. No passado, eu já tinha um problema semelhante e descobri que a nova versão de uma ferramenta implementava um cursor que era chamado o tempo todo. Mas desta vez isso não parece ser o problema. Eu usei o profiler SQL para rastrear os eventos do cursor, mas não encontrei nada interessante. Eu também tentei rastrear com um filtro no próprio tempDB, mas parece que não há nada acontecendo lá.

Alguém tem uma ideia de como eu posso descobrir quem ou o que está causando essas altas taxas de transação no TempDB?

Não tenho certeza se isso pode ser um problema de desempenho, mas quero descobrir por que esse número mudou muito.

    

Answer 1

Eu usei este script no passado para ver qual spid tem muitas páginas em tempdb:

;WITH s AS
(
    SELECT 
        s.session_id,
        [pages] = SUM(s.user_objects_alloc_page_count 
          + s.internal_objects_alloc_page_count) 
    FROM sys.dm_db_session_space_usage AS s
    GROUP BY s.session_id
    HAVING SUM(s.user_objects_alloc_page_count 
      + s.internal_objects_alloc_page_count) > 0
)
SELECT s.session_id, s.[pages], t.[text], 
  [statement] = COALESCE(NULLIF(
    SUBSTRING(
        t.[text], 
        r.statement_start_offset / 2, 
        CASE WHEN r.statement_end_offset < r.statement_start_offset 
        THEN 0 
        ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
      ), ''
    ), t.[text])
FROM s
LEFT OUTER JOIN 
sys.dm_exec_requests AS r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
ORDER BY s.[pages] DESC;

(não me lembro de onde eu consegui, desculpe). Isso não lhe dará o culpado (porque ele pode estar escrevendo as mesmas páginas várias vezes).

Usar esse script mostra quem faz muito IO:

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

( Obrigado Dave ! )

Combinando esses 2, você deve ter uma boa ideia.