Performance tuning tips from the SQLGrease team

Does updating statistics cause blocking?

Earlier today I had a conversation with a colleague who thought statistics updates could cause blocking. I have never seen this before, but since I consider this colleague to be pretty competent I second guessed myself. I decided to put together a post as soon as I got home to verify this.

To start with I used a generally large table in one of my test systems and launched a statistics update with full scan on the table (along with all its indexes).

UPDATE STATISTICS ExecQueryWaitStats WITH FULLSCAN, ALL

As the stats update was executing, I ran the following to see what locks the session was holding. For this I queried sys.dm_tran_locks:

Based on the output of this, the answer is usually no. The first row indicates a database shared lock (S). This is pretty much a given that this will occur if a user is logged in. This is here whether or not the session is updating stats.

The next row (row 2) is placing an exclusive lock (X) on stats being updated (UPDSTATS). If you try updating stats from another session on this table it will lock. This is not something that would normally occur – so this is not a concern.

The remaining rows are all holding Schema stability locks (Sch-S). Based on the lock compatibility matrix:

I thought I should point out I did say usually no. If we were to perform a trace we would see a Sch-M lock get taken very briefly at the end of the statistics update when the old stats and new stats were swapped.

Schema stability locks should not interfere with any DML operations such as inserts updates and deletes. Just for the sake of testing this I ran some concurrent load performing updates against this table and could not generate any lock contention/blocking.