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:
SELECT tl.resource_type, tl.resource_subtype,
CASE WHEN resource_type = 'OBJECT'
THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)
ELSE '' END AS object,
request_mode, request_type, request_status
FROM sys.dm_tran_locks tl WHERE tl.request_session_id = 72
The output of this is below:
Based on the output of this, the answer is 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:
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.
Watch us diagnose a query timeout with SQLGrease:
See how you can get query level wait events without Query Store:
SQLGrease can help you quickly identify if maintenance tasks are interfering with your production workload.