Sql-server – Very high transactions per second

sql-server-2008tempdb

Our production server runs on average 4,000 transactions per second. In the past few days the average has jumped to 175,000 transactions per second. That is not a typo, it's 175K per sec.
Looking at the DMVs for transactions, we can't link it to user sessions directly, but we do see this:

SELECT NAME,
       COUNT(*)
FROM   sys.dm_tran_active_transactions
GROUP  BY NAME
ORDER  BY 2 DESC 

+------------------------------+-------+
|             Name             | Count |
+------------------------------+-------+
| WorkFileGroup_fake_worktable |   627 |
| LobStorageProviderSession    |   217 |
| workfile                     |   171 |
+------------------------------+-------+

Can anyone shed light on these types of transactions? Or am I chasing ghosts here?

Best Answer

Watch for the high activity again; when you see it, start a server side trace or if necessary use Profiler briefly to see what's going on.

Alternately, use a packet sniffer like Wireshark to capture raw wire activity.

Check dm_exec_cached_plans to see if that gives any idea what's going on.

Watch dm_io_virtual_file_stats to see which files in particular, if any, are being hit.