SQL Server System Processes Blocking User Processes when Auto Update Stats Async is Enabled.

Recently, we decided to buy a few licenses of Confio Ignite (Now SolarWinds Database Performance Monitor) to monitor some of our high-profile SQL Servers.  I'm definitely not posting a sales pitch, but for me, it's provided insight into SQL Server that is much more difficult to obtain through traditional methods.  One of the best features of the product is the ability to break activity down by time and to see historical performance information.  Among many metrics, the application displays blocking in the database.

I noticed nearly immediately that the most common blocking was being caused by System processes, not User processes.  Blocking by System processes is a bit harder to nail down.  From my experience, the blocked process report in SQL Profiler doesn't show this type of issue.  Extended Events have been on my to-do list for a while, but I haven't gotten comfortable  with them quite yet.

The SPID of the system process listed as the head blocker would typically say TASK MANAGER when pulled up in SSMS.  The wait type was LCK_M_SCH_M.  This is a Schema lock.  This occurs when changes are being made to a table, such as altering an index or other DDL.

So, I started to give it some thought as to what the system could be doing.  Finally I got the idea to look into Auto Update Stats.  That's when I noticed that my system was enabled for Auto Update Stats Async.  A little research on how this works showed that the system basically outsources the Auto Update Stats task to a system process.  My original understanding was that this would happen in the background, but in reality it seems that the original query that executes is allowed to run, but once the Auto Update event starts, it can and will block subsequent queries.  Not even a SELECT with NOLOCK can break through a Schema lock.

I ran a SQL Profiler trace for only the Auto Update Stats events and grabbed a list of object IDs that were being updated.  These events were firing fairly consistently, with some of them lasting for up to 10 seconds or more in duration.  A simple query will show you the name of the table.

1Select * from <dbname>.sys.objects where object_id = <ID>

Once I had the list of tables affected by Auto Update Stats events, I checked the row counts of the tables.  I noticed that they all had a few hundred rows or less.

These tables are used for current "Work in Progress" by the application and the contents of the tables are changing rapidly, never exceeding a few hundred rows.  Once the SQL Server determines that the data has changed by 20% or more, it will fire the Auto Update Stats the next time the data is accessed.  In my system, this meant blocking every few minutes for up to 10 seconds.

Since the data in the tables was so limited, I decided to set the tables to NORECOMPUTE.  Setting NORECOMPUTE on individual statistics will prevent them from automatically updating.  To enable NORECOMPUTE, run an update statistics command against each table experiencing the issue:

1UPDATE STATISTICS  database.dbo.table  with FULLSCAN, NORECOMPUTE

Immediately after updating the statistics to NORECOMPUTE, all of the system blocking was alleviated.  I've been running blocking-free for weeks now with no adverse effects.  If you change your mind and want to remove RECOMPUTE, simply update the statistics again without the NORECOMPUTE option specified.  This also means that if you have automated weekly maintenance in place that will update the statistics on these problem tables, the NORECOMPUTE option will be removed automatically.  To get around this, I simply added another step after my Update Statistics task to manually update my problem table statistics with the NORECOMPUTE option.