Made By SQL: Frequently running stored procedure hangs from time to time

We have encountered situation when we have a frequently

(about 50 times in a second) executed stored procedure

That hangs for couple of seconds and blocks all other

requests with this stored procedure.

We are talking about OLTP system.

In general, this SP runs for 20-30 ms,

but once in 2-3 days, we saw its runtime

increases up to 20-30 seconds

(only single thread/execution) and after that

all comes back to normal.

also when this ‘hanging’ occurs, all other threads that execute this SP wait for the problematic thread/execution to finish.

In the blocking event in the profiler, we saw that the other blocked

Threads that execute the same SP were blocked due to compile lock.

The issue was related to the fact that SQL Server

Before executing a SP, first checks if the Statistics are up to date,

And in case they are not, it will first update the statistics and only then

Continue to execute the SP by re-compiling it and creating fresh execution plan.

The most interesting part is that all other threads with this SP were blocked

Until the process of updating statistics and creating the fresh execution plan finished.

The reason for the blocking of the rest concurrent calls to the same SP

Explained by below:

In Microsoft SQL Server, only one copy of a stored procedure plan is generally in cache at a time.

Enforcing this requires serialization of some parts of the compilation process,

and this synchronization is accomplished in part by using compile locks.

If many connections are concurrently running the same stored procedure and a compile lock must be obtained

for that stored procedure every time that it is run, system process IDs (SPIDs)

might begin to block one another as they each try to obtain an exclusive compile lock on the object.

We can control if we want the AUTO_UPDATE_STATISTICS to occur in synchronized way

or in A-Synchronized mode by setting the AUTO_UPDATE_STATISTICS_ASYNC option.

Once we set the AUTO_UPDATE_STATISTICS_ASYNC option to true, the problem was gone.

SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on

FROM sys.databases

Made By SQL