October 24, 2025 at 12:00 am
Comments posted to this topic are about the item How Implicit Transactions Hurt SQL Server Performance Without You Knowing
October 24, 2025 at 7:37 am
This article is one of the reasons I keep on reading the daily email feed. I have used SQL in a data warehouse setting for a long time but I have never come across implicit transactions. Now I have it in the brain somewhere ready for the next time at a future client where something seems to be hanging. Thanks for this!
October 26, 2025 at 2:24 am
One of the first things I do on any new SQL Server instance is create a SQL Agent job that runs every 2 minutes, queries sys.dm_tran_database_transactions for any open transaction on a user database (database_id > 4) that was started more than 2 minutes ago, and fire off an alert if it finds anything. Obviously not a good idea in an environment where long-running transactions are regularly expected, but in my personal experience those have been few and far between, and if a transaction is running for over 2 minutes, whether implicit or explicit, I generally want to know.
I do find this article title a bit misleading, as technically implicit transactions aren't a problem, uncommitted transactions are the problem. Of course, that's nowhere near as misleading as SQL Server itself, as I'd argue that instead of SET IMPLICIT_TRANSACTIONS ON the setting should be SET AUTOCOMMIT OFF as that seems like a much clearer indication of what it does and why it can be a major problem. Personally, I'd always advise against implicit transactions, but Oracle users may be more comfortable with it as it is (or at least was, last time I looked) the default mode there. Still a useful article and important setting to know about, thanks for contributing!
October 27, 2025 at 11:03 pm
Is the query code here assuming a particular SQL Server version? When I run this query
SELECT s.session_id, r.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS s
WHERE r.status = 'running';
I get this error text:
Msg 207, Level 16, State 1, Line 15
Invalid column name 'session_id'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'text'.
I'm running SQL Server 2016 (yes, it's old).
October 28, 2025 at 3:58 pm
SELECT r.session_id, s.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS s
WHERE r.status = 'running';
This will work. The author used the wrong aliases 🙂
BTW: I would not check for "running" because in the given example the process will show "sleeping" because the INSERT has finished and the connection awaits the next command....
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply