Retrieving Transaction Isolation levels in blocked process reports

SEarle86 · January 4, 2023 2:29PM

There are two methods I have been using to understand blocking situations on our servers:

The processes tab in the Blocking Process alert
The blocking processes section in the server overview

I can get a lot of information from both of these methods but I don't seem to be able to get the isolation levels of the blocking or blocked queries which would be helpful in understanding why the blocking situation has occurred.

Is there a way I can get this information?

Alex B · January 9, 2023 12:11PM

Hi @SEarle86,

There isn't a specific mechanism to see the transaction isolation level. This documentation page goes over some scenarios that give indications of which are set based on the behaviour. Beyond that, you can see the default Transaction Isolation level for the database by using DBCC USEROPTIONS; or, if it is set in the query batch that is blocking or being blocked (i.e. between the GO statements), then it should be shown in the SQL Fragment, otherwise there is not a way to see this in SQL Monitor I'm afraid.

I would suggest raising a SQL Monitor Uservoice suggestion.

Kind regards,
Alex

SEarle86 · January 9, 2023 1:40PM

Thanks Alex,
I wasn't aware of the uservoice page and have now raised a suggestion there

Thanks
Steve

Alex B · January 9, 2023 3:40PM

Hi Steve,

No worries, thanks for raising that!

Kind regards,
Alex

Retrieving Transaction Isolation levels in blocked process reports

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk