9

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?

Improve this question
4
  • Maybe you can profile the server by executing sp_whoisactive repeatedly. What queries come up most often?
    – usr
    Jul 31, 2015 at 18:03
  • Probably not clear but in the original text, I stated there is no correlation between user processes and the transactions. Normally we have about 4000 connected users and at any given point in time, between 40-60 of them are runnable spids. During this inflated transaction period, there were still 40-60 runnable spids - no difference.
    – paulbarbin
    Aug 14, 2015 at 19:10
  • Update: the tps is back down to it's normal value and we don't see any real reason for it happening. The only thing that makes any sense is that we had a linked server query executing where it looked like the entire table was being pulled across the wire into the tempdb. That process was taking much longer than usual. Is it possible that the tps was getting counted like 1 row in the table = 1 transaction?? The table has 50K rows in it and it gets executed adhoc by the users so, 3 times per second it gets called and that COULD add up but it doesn't seem likely.
    – paulbarbin
    Aug 14, 2015 at 19:14
  • 2
    If it was my server, I'd run a quick server-side trace. Maybe just a 5 minute trace to see if there is any chance the tps count is spurious. I'd definitely spam sp_whoisactive to observe in-flight queries too.
    – Peter
    Sep 29, 2015 at 19:41

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.