Serializable Lock Hints Detected

If a query uses the SERIALIZABLE isolation level or HOLDLOCK hints, SQL Server will hold shared range locks until a transaction is completed. Normally SQL Server lets go of locks as soon as it’s done processing that part of a query, but here, the query has forced all other queries to stop until this one is done.

This means your SQL Server is executing one query at a time on those tables or data pages, and there’s nothing you can do from an administration or tuning perspective to make that server run faster.

This also may make you prone to deadlocks.

But We Don’t Use Serializable… Why Would We Get This Warning?

You might use the serializable isolation level without realizing it. This could be because:

• It’s set in your application tier somewhere you’re unaware of
• You’re using cascading updates and deletes. When those fire, SQL Server uses and “isolation level upgrade” to ensure data consistency: and that means it starts using serializable, even if you didn’t ask.

How sp_Blitz® Catches Serializable Lock Waits In The Act

Here’s how we catch it happening: whenever SQL Server needs to wait while it’s executing queries, it tracks that time as a wait statistic. We can query the waits tables to get overall bottleneck data over time. We check sys.dm_os_wait_stats looking for more than 5 seconds cumulative wait times for serializable locking.

How to Track Down the Source of Serializable Lock Waits

This can be tricky to do, but with our help you’ve got a few tricks up your sleeve.

Look at the isolation level of your connections: This isn’t fool-proof. It only looks at current connections and looks at their isolation level right now– they could be running queries with table level hints, or they might not even be connected. But it’s a start.

Check for cascading updates and deletes: sp_BlitzIndex® lets you know if these are configured.

Set up the blocked process report to lie in wait and report back: Read more on how to do that here.

Check index waits: Use sp_BlitzIndex® to examine which indexes have high lock waits, then find the queries that hit these indexes while using WITH SERIALIZABLE or WITH HOLDLOCK hints.

Sift through your plan cache:

• One way to track them down is to look at the SQL Server plan cache for queries with long overall durations, but relatively small amounts of CPU or reads. This isn’t perfect, but it can indicate that there’s blocking happening in that query, or other queries that are holding up this one.
• You can also try examining the plan cache for all uses of WITH SERIALIZABLE or WITH HOLDLOCK hints, but that starts to get more performance-intensive.

How Do We Fix It?

This will vary depending on what it is. Sometimes, you can change the isolation level. Sometimes you can add an index. Sometimes, you need to re-write the code.