인덱스 재구성과 통계 업데이트시 발생하는 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)) ASLEVEL, 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가 대기하게 되어 위에서 설명한 것과 동일한 상황이 발생할 수 있다.
[참고자료]
2019-01-22 / Sungwook Kang / http://sqlmvp.kr
SQL Server, index reorganize, update statistics, 인덱스 재구성, 통계 업데이트, mssql, DBA
출처: https://sqlmvp.tistory.com/1279?category=618825 [Database Lab]
