Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Why is INDEX REORGANIZE and UPDATE STATISTICS causing SQL Server blocking?


By:   |   Last Updated: 2019-01-17   |   Comments   |   Related Tips: More > Locking and 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).

SPID Status BlkBy DBName Command CPUTime DiskIO LastBatch
57 RUNNABLE . AdventureWorks2014 DBCC 4735 39310 01/03 19:49:12
59 RUNNABLE . AdventureWorks2014 UPDATE STATISTIC 3047 695318 01/03 19:49:15

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).

SPID Status BlkBy DBName Command CPUTime DiskIO LastBatch
57 RUNNABLE . AdventureWorks2014 DBCC 17469 376072 01/03 19:49:12
58 SUSPENDED 59 AdventureWorks2014 SELECT 408233 6263681 01/03 19:49:36
59 RUNNABLE 57 AdventureWorks2014 UPDATE STATISTIC 21236 1229630 01/03 19:49:15

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.

BLOCKING_TREE
HEAD - 57 alter index [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]...
| |------ 59 UPDATE STATISTICS Sales.SalesOrderDetailEnlarged WITH FULLSCAN, ALL
| | |------ 58 SELECT * FROM [Sales].[SalesOrderDetailEnlarged] WHERE [SalesOrderId]=...

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);
lock information

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


Last Updated: 2019-01-17


next webcast button


next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools