Any way to find which procedures are causing serializable lock mode?

Question

Any way to find which procedures are causing serializable lock mode?

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
January 18, 2016 at 12:01 pm

#303887

Hello
Is there anyway to find out the list of procedures causing SERIALIZABLE lock modes? I see a lot of key locks in DMV 'sys.dm_tran_locks' but unable to find the historic lock details caused by the procs.
Thanks.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

There is no easy way to do this.

You could try querying the DMV sys.dm_all_sql_modules with a WHERE clause like WHERE definition LIKE '%SearchString%', and then use searchstrings that invoke serializable isolation level (SET TRANSACTION LEVEL SERIALIZABLE or (WITH SERIALIZABLE) off the top of my head).

But that will not give you stored procedures invoked by stored procedures that set the isolation level to serializable. Or invoked by SQL Agent jobs that set the isolation level to serializable. Or invoked by client code that sets the isolation level to serializable before making the call. (And there may be more similar scenarios).

Alternatively, set up an extended events session to see when the isolation level is set to serializable.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

I'd probably go with the extended events session. I'm at least sure you can capture the information there as opposed to the DMVs.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Grant Fritchey (1/19/2016)
I'd probably go with the extended events session.

Thirded. I'm starting to love XEs (late to the party, I know).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 4

GilaMonster (1/19/2016)
Grant Fritchey (1/19/2016)
I'd probably go with the extended events session.
Thirded. I'm starting to love XEs (late to the party, I know).

But very welcome all the same!

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning