(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