인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block

인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block

 

·         Version : SQL Server

 

SQL Server에서 인덱스 재구성 및 통계 업데이트 작업을 할 때 일반적으로 DML(SELECT, INSERT, UPDATE, DELETE)문은 차단하지 않는 것으로 알고 있다. 하지만 인덱스 재구성할 때 SELECT 문에서 차단이 발생하는 경우가 있다. 아래 실습을 통해서 어떻게 차단이 발생하는지 알아본다.

 

실습을 진행하기 위해 간단한 시나리오를 만든다. 해당 시나리오는 SQL Server Adventurework2014 데이터베이스를 사용하며 더 큰 데이터 집합을 만들기 위해 아래 링크의 스크립트를 적용하였다.

·         Enlarging the AdventureWorks Sample Databases : https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/

 

차단이 발생하는것을 재현하기 위해 ALTER INDE…REORGANIZE를 실행하고 UPDATE 구문 및 SELECT 구문을 실행 한다.

세션 1에서 아래 스크립트를 실행 한다.

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

 

세션2에서 아래 스크립트를 실행한다.

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

 

sp_who2를 실행하고 두 프로세스가 모두 실행중인지 확인한다.

SPID	Status	BlkBy	DBName	Command
57	RUNNABLE	.	AdventureWorks2014	DBCC
59	RUNNABLE	.	AdventureWorks2014	UPDATE STATISTIC

 

 

세션3에서 아래 스크립트를 실행한다.

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

 

sp_who2를 실행하면 BlkBy 컬럼 정보를 통해서 블럭킹이 발생한 것을 확인할 수 있다.

SPID	Status	BlkBy	DBName	Command
57	RUNNABLE	.	AdventureWorks2014	DBCC
58	SUSPENDED	59	dventureWorks2014	SELECT
59	RUNNABLE	57	AdventureWorks2014	UPDATE STATISTIC

 

지금까지는 매우 간단한 시나리오였으며 SELECT가 UPDATE STATISTICS에 의해 차단되고 UPDATE STATISTICS는 INDEX REORG(DBCC)에 의해 차단되었음을 확인할 수 있다. 실제 서비스에서 sp_who2를 실행하면 블로킹 체인의 SPID를 한번에 확인하기 어려울 수 있으므로 아래 스크립트를 사용하면 전체 블럭킹 트리가 생성되고 체인에 포함된 SPID를 확인할 수 있다.

(https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/ )

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

 

아래 출력을 보면 ALTER INDEX가 체인의 머리 부분에 있고 UPDATE STATISTICS가 차단되어 있어 간단한 SELECT문을 차단하고 있음을 확인할 수 있다.

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]=...

 

아래 스크립트를 사용하면 각 명령문에서 획득한 잠금을 보다 깊게 어떤 잠금이 잠금 경합을 일으키는지 확인할 수 있다. WHERE절에 SPID를 수정해서 사용한다.

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

 

 

스크립트 실행결과에서 STATS 자원의 잠금이 차단을 야기하는 것을 볼 수 있다. ALTER INDEX가 보유한 Sch-S(스키마 안정성) 잠금은 UPDATE STATISTICS가 획득하려고 시도하는 Sch-M(스키마 수정) 잠금을 차단한다. 이것은 SELECT 쿼리가 획득하려고 하는 Sch-S 잠금을 막는것이다.

AUTO_UPDATE_STATISTICS가 활성화 되어 있고 AUTO_UPATE_STATISTICS_ASYNC가 비활성화된 경우 조회를 실행하기 전에 통계가 갱신될 때 까지 SELECT가 대기하게 되어 위에서 설명한 것과 동일한 상황이 발생할 수 있다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5880/why-is-index-reorganize-and-update-statistics-causing-sql-server-blocking/

 

 

2019-01-22 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, index reorganize, update statistics, 인덱스 재구성, 통계 업데이트, mssql, DBA