sp_Blitz_who : transaction_isolation_level wrongly reported about sql-server-first-responder-kit HOT 8 CLOSED

I've actually seen this when the user's database context is something else - for example, say they're executing the query from master, but querying objects in another database. The overall session's isolation level really is Read Committed (because it's run from master), but people think it's supposed to be RCSI because they're querying objects in other databases.

If you run into a situation where you can prove that the DMV's results are wrong, and you can reproduce it with a query, I'd definitely love to hear about it and we can go from there. But otherwise, until I see proof, I hate to say this, but I have to assume it's what I mentioned in the first paragraph.

from sql-server-first-responder-kit.

Hi Brent,

Thank's a lot for this informative answer.

What whe have done to progress with this issue is adding the column r.database_id in the output of the sp_BlitzWho stored procedure.

For the case we have capture with the bad value for the isolation level, r.database_id is indeed null.

This is not a suprise since a left join is done on sys.dm_exec_requests AS r.

Best regards.

from sql-server-first-responder-kit.

OK, cool, there we go! Good work, glad to hear it.

from sql-server-first-responder-kit.

Will the sp_BlitzWho procedure be modified to correct that bug Brent?

from sql-server-first-responder-kit.

Sorry, I'm not understanding what the bug is? If r.database_id is null, I don't understand what change you'd want us to make.

You might want to submit a pull request with the changes you're proposing?

from sql-server-first-responder-kit.

To be consistant :

Instead of having this line:

WHEN s.transaction_isolation_level = 2 AND EXISTS (SELECT 1 FROM sys.databases WHERE name = DB_NAME(r.database_id) AND is_read_committed_snapshot_on = 1) THEN ''Read Committed Snapshot Isolation''

Maybe this line would be better:

WHEN s.transaction_isolation_level = 2 AND EXISTS (SELECT 1 FROM sys.databases WHERE name = COALESCE(DB_NAME(r.database_id), DB_NAME(blocked.dbid), ''N/A'') AND is_read_committed_snapshot_on = 1) THEN ''Read Committed Snapshot Isolation''

Since this logic is used at some other places in this stored procedure.

from sql-server-first-responder-kit.

OK, can you make the changes you want to see in the proc, and then test them in your environment first to make sure you're getting the results you desire?

You're asking me to change something that I don't see, can't reproduce, etc. By all means, I want to help, but I need you to help me too. Is that fair?

from sql-server-first-responder-kit.

It is fair Brent.

Best regards

from sql-server-first-responder-kit.