Execute a statement within a transaction without enlisting it in that transaction

user_1687613 0
Viewed 1345

I have some SQL statements in a batch that I want to profile for performance. To that end, I have created a stored procedure that logs execution times.

However, I also want to be able to roll back the changes the main batch performs, while still retaining the performance logs.

The alternative is to run the batch, copy the performance data to another database, restore the database from backup, re-apply all the changes made that I want to profile, plus any more, and start again. That is rather more time-consuming than not including the act of logging in the transaction.

Let us say we have this situation:

BEGIN TRANSACTION
SET @StartTime = SYSDATETIME
-- Do stuff here
UPDATE ABC SET x = fn_LongRunningFunction(x)
EXECUTE usp_Log 'Do stuff', @StartTime
SET @StartTime = SYSDATETIME
-- Do more stuff here
EXEC usp_LongRunningSproc()
EXECUTE usp_Log 'Do more stuff', @StartTime
ROLLBACK

How can I persist the results that usp_Log saves to a table without rolling them back along with the changes that take place elsewhere in the transaction?

It seems to me that ideally usp_Log would somehow not enlist itself into the transaction that may be rolled back.

I'm looking for a solution that can be implemented in the most reliable way, with the least coding or work possible, and with the least impact on the performance of the script being profiled.

EDIT

The script that is being profiled is extremely time-consuming - taking from an hour to several days - and I need to be able to see intermediate profiling results before the transaction completes or is rolled back. I cannot afford to wait for the end of the batch before being able to view the logs.

sql-server-2017 transactions tsql sql-server
4 Answers

You can use a table variable for this. Table variables, like normal variables, are not affected by ROLLBACK. You would need to insert your performance log data into a table variable, then insert it into a normal table at the end of the procedure, after all COMMIT and ROLLBACK statements.

user_50866310

It might sound a bit overkill (given the purpose), but you can create a CLR stored procedure which will take over the progress logging, and open a separate connection inside it for writing log data.

Normally, it is recommended to use context connection within CLR objects whenever possible, as it simplifies many things. In your particular case however, you wish to disentangle from the context (especially from the current transaction), so regular connection is a way to go.

Caveat emptor: if you never dabbled with CLR programming within SQL Server before, you may find the learning curve a bit too steep. That, and the amount of server reconfiguration (both the SQL Server instance and the underlying OS) required to make it work might also seem to be prohibitively expensive, and not worth the hassle. Still, I would consider it a viable approach.

user_39252320

So, as Roger mentions above, SQLCLR is one option. However, since SQLCLR is not permitted, you are out of luck.

In SQL Server 2017 there is another option and that is to use the SQL Server extensibility framework and the support for Python.

You can use this to have Python code which calls back into your SQL Server instance and executes the usp_log procedure.

user_76568800

Another, rather obscure, option is to bind other sessions to the long-running transaction for monitoring.

At the beginning of the transaction call sp_getbindtoken and display the bind token.

Then in another session call sp_bindsession, and you can examine the intermediate state of the transaction.

Or you can read the logs with (NOLOCK).

Or you can use RAISERROR WITH LOG to send debug messages to the client and mirror them to the SQL Log.

Or you can use custom user-configurable trace events, and monitor them in SQL Trace or XEvents.

Or you can use a Loopback linked server configured to not propagate the transaction.

user_72977000
Related