Implicit Transactions and the performance issue

Last week, users of one of the application started complaining of performance issue. As always, the application team will reach out to the DBA Team to check the Database Server’s performance.

The first thing that we observed was that there was lot of blocked sessions. All the sessions from that application were getting blocked and there was a lead blocker. The worst part was that the lead blocker would change every minute !

Apart from this, the Server performance was normal. The CPU, Memory usage was minimal and there were not noticeable errors in any of the logs.

It was very clear that the blocking sessions are degrading the performance of the applications. As a first step, all the tables on the database were re-indexed, followed by updating the statistics. Now the DBA on call proudly announced “Please check the application now” assuming the magic wand had worked. It was not to be. Same issue and the blocking sessions continued to pile up. As mentioned earlier, the lead blocker would change every minute.

However, there was a  query on a particular table which would be the lead blocker many of the times. When the DBA was about relax that he had found out the root cause, it was found out that this table had only 23 rows ! Moreover, all the tables’ statistics were up to date and there was hardly any fragmentation.

Now we assumed something from the application side had changed. No, nothing was changed on the application. It was the same code that they had been using for years.

Now the ball was again the DBA’s court. While checking for blocked sessions, one thing was noticable. The status for all the blocked sessions was Awaiting Command. A sessions with Awaiting Command status is a client connection with no query to the SQL Server. Strange! No query to execute still getting blocked on the instance?

This was a kind of confirmation that nothing was wrong from SQL Server perspective. However, a Profiler trace was run for the duration when the sessions are getting blocked. This was the key step that helped us to rule out any SQL Server issue. All the sessions that were getting blocked and the sessions which blocked other had one thing in common. All sessions were Implicit Transactions. The query had the below line as the first thing.

set implicit_transactions on

The definition of Implicit Transaction on MSDN is as follows.

When a connection is operating in implicit transaction mode, the instance of the SQL Server Database Engine automatically starts a new transaction after the current transaction is committed or rolled back. You do nothing to delineate the start of a transaction; you only commit or roll back each transaction. Implicit transaction mode generates a continuous chain of transactions.

If there is an INSERT statement running as an Implicit Transaction, then a separate transaction is created for that. If it was an explicit transaction, several INSERTs would be wrapped together in one transaction. Also for an Implicit Transaction, SQL Server needs to write the transaction log to disk every time . The session running the Implicit Transaction will remain open until a COMMIT/ROLLBACK transaction command is issued.

Since our case, each of the sessions getting blocked were Implicit Transactions. For example one session with Implicit Transactions on, selects data from a table and holds the lock. Until this session receives the commit/rollback transaction command from the client, the session will remain open on the instance. Since this transaction is holding the locks on that table, the subsequent sessions requesting data from that table will be blocked.

By now, it was confirmed that there is delay between the client and database server communication. Hence, the network team was called in to check the performance. They finally figured out that one of the switches had gone bad. The switch was replaced and the blocking sessions disappeared !

For a change, the DBA team did not have to the Root Cause Analysis.

3 thoughts on “Implicit Transactions and the performance issue

  1. Vikas Rajput

    Hi Pradeep,

    Good article and explanation. Though am confused with the last two para’s.
    Are you saying that transactions were moulded to EXPLICIT mode which resolved the (blocking) issue.
    Or, are you saying that issue was at network layer where latency was observed for a bad switch. Will appreciate if you can confirm.

    Regards,
    Vikas

    1. PradeepAdiga Post author

      Hi Vikas,

      Thanks. May be I chose the wrong words to express 😉
      The transactions were not changed to use EXPLICIT mode. The issue was with one of the network switches

      With warm regards,
      Pradeep Adiga

Comments are closed.