Index Reorganize and Update Stats Causing SQL Server Blocking

Problem

We all know that both operations, an index reorganization and update statistics in SQL Server, will not block normal DML statements on their own. (i.e. ANY SELECT, INSERT, UPDATE or DELETE). That said, I ran into a weird situation where I had a really simple query that did an index lookup using the primary key that usually takes milliseconds, but was for some reason running for over 10 minutes while executing during my index maintenance window. I looked at the output of sp_who2 and I confirmed that the index reorganization was at the head of my blocking chain. Let's dig a little deeper and see if we can explain how this could happen.

Solution

In order to demonstrate what was happening we will need to first setup a simple test scenario. If you'd like to follow along in your own environment the demonstration below was done using the AdventureWorks2014 database with the addition of this script applied in order to create a larger dataset so the blocking operations would be present longer making them easier to detect.

We will use 3 statements in order to show the blocking: ALTER INDEX … REORGANIZE, UPDATE STATISTICS and a simple SELECT statement. First, we will start the index reorganization in a session using the following T-SQL code.

ALTER INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID] 
 ON [Sales].[SalesOrderDetailEnlarged] REORGANIZE;

Now in another session you can start the statistics update using the following T-SQL code.

UPDATE STATISTICS [Sales].[SalesOrderDetailEnlarged] WITH FULLSCAN, ALL;

Let’s run sp_who2 and check that both processes are running (some columns removed due to space restrictions).

Now let's start a simple query from a third session using the following T-SQL code.

SELECT * 
FROM [Sales].[SalesOrderDetailEnlarged]
WHERE [SalesOrderId]=1302257;

Now if we take a look at the sp_who2 output we can see there is some blocking (again some columns are removed due to space restrictions).

Now this is a fairly straightforward blocking scenario and you can see that the SELECT is blocked by the UPDATE STATISTICS which is in turn blocked by the INDEX REORG (DBCC), but if you’d rather not scroll through the sp_who2 output trying to find all the SPIDs in the blocking chain (this can be really difficult in a system with a high number of concurrent connections) you could also run the following script (credit for script and more details can be found here) which displays the complete blocking tree and only includes the SPIDs involved in the blocking chain.

SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH INTO #T
FROM sys.sysprocesses R 
CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS (SELECT 
       SPID, 
       BLOCKED,
       CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
       BATCH 
   FROM #T R
   WHERE (BLOCKED = 0 OR BLOCKED = SPID)
   AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
   UNION ALL
   SELECT 
      R.SPID,
      R.BLOCKED,
      CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
      R.BATCH
   FROM #T AS R
   INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID 
   WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
   )
SELECT 
   N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +
   CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END
   + CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

Looking at the output below (without having to sort through other SPIDs) it’s clear that the ALTER INDEX is at the head of the chain and it is blocking the UPDATE STATISTICS which is in turn blocking the simple SELECT statement.

If we look even deeper at the locks acquired by each statement using the following T-SQL code we can see exactly which locks are causing the lock contention. (Note: if following along in your own environment you’ll need to update the SPIDs in the WHERE clause.

SELECT 
  tl.request_session_id as spid,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,
  tl.resource_description,
  request_mode, 
  request_type, 
  request_status,
  wt.blocking_session_id as blocking_spid
FROM sys.dm_tran_locks tl 
LEFT JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
WHERE tl.request_session_id in (57,58,59);

You can see from the highlighted section of this output that the lock on the STATS resource is what is causing the blocking.  The Sch-S (schema stability) lock held by the ALTER INDEX is blocking the Sch-M (schema modification) lock that the UPDATE STATISTICS is trying to acquire.  This is turn is blocking the Sch-S lock that the SELECT query is trying to acquire.  Unfortunately, there is not much you can do to resolve this contention other than make sure you are not trying to update statistics during your index maintenance window, but at least as a DBA you can control when most of those operations occur.  The only time you wouldn’t be in control of this is if AUTO_UPDATE_STATISTICS is enabled on your database and AUTO_UPDATE_STATISTICS_ASYNC was disabled.  This would cause your query to wait until the statistics were updated before running your query and you could run into the same situation demonstrated above.

Next Steps

• Read more information on SQL Server locking
  • Locking and blocking
  • Lock compatibility
• Read more information on the SQL Server auto update statistics configuration option