While they don’t occur very often, compile locks can become a problem with stored procedures. This post will outline some common things to look for that can cause compile locks and how to address them.
What is a compile lock?
SQL Server keeps one version of an execution plan in the plan cache at any given point in time (for the most part). There is a brief period when a stored procedure compiles that requires some level of serialization to the plan in the cache. This serialization is what generates compile locks.
Common causes and fixes
There are several common causes for compile locks. These are some of the most common causes as well as some potential fixes.
Frequent recompiles caused by with recompile
If you defined a stored procedure with RECOMPILE it will force frequent recompiles. This will place a compile lock and cause lock contention. You should evaluate if this is really necessary. I’ve seen where RECOMPILE gets used to get around issues where there is a lot of skew in data volume based on parameters passed into the stored procedure. You should probably consider using #temp tables as an intermediary in data selection so that row estimates are more realistic with the problem queries.
Stored Procedure is executed without Fully Qualified Name
When the stored procedure is executed without the fully qualified name (i.e. owner), a different user (other than owner) will need to validate which procedure is to be called. It must check if there is a version owned by the executing user exists in the cache. This takes out a lock and can cause contention.
Naming stored procedures with sp_*
This one kind of surprised me, but apparently SQL Server gives special attention to stored procedures starting with sp_* outside of the master database. Apparently, SQL Server assumes this is a system stored procedure and follows different name resolution rules as a result. Don’t name stored procedures with sp_. You should name them with usp_ instead.
Large parameters
If you pass a parameter in excess of 8k to a stored procedure it will cause a recompilation and in turn a compile lock.