How to find out who / what is hammering SQL Server TempDB

Question

I am using SQL Server 2012 Enterprise. The server has one availability group configured. There are several databases for different tools on the instance. The tools all belong together and therefore were rolled out togehter. I recognized a rise in the perfmon counter Transaction / sec for TempDB after the rollout. Before the rollout the counter was araound 30 in average and now it is around 300. In the past I had already a similar problem and found out the the new release of one tool implemented a cursor which was called all the time. But this time this seems not to be the problem. I used the SQL profiler to trace the cursor events but did not find anything interesting. I also tried to trace with a filter on tempDB itself but it look like there is nothing going on there.

Has anyone an idea how I can find out who or what is causing these high transaction rates in TempDB?

I am not sure if this might be a performance problem at all but I want to find out why this figure changed that much.

Answer 1

I've used this script in the past to see which spid has a lot of pages in 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;

(can't remember where I got it from, sorry). This won't give you the culprit (because he might be writing the same pages over and over).

Using this script gives you who is doing a lot of 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

(Thanks Dave!)

Combining these 2 should give you a good idea.