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.
Best Answer
I've used this script in the past to see which spid has a lot of pages in tempdb:
(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:
(Thanks Dave!)
Combining these 2 should give you a good idea.