인덱스 재구성과 통계 업데이트시 발생하는 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 VARCHARAS 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)), 4AS 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 대기하게 되어 위에서 설명한 것과 동일한 상황이 발생할  있다.

 

[참고자료]

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



출처: https://sqlmvp.tistory.com/1279?category=618825 [Database Lab]
No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 41489
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 22482
2174 SQL Server Agent Job에서 sysploicy_purge_history 작업실패 jevida(강성욱) 2019.09.04 1473
2173 where절 case관련... BGT051 2019.09.03 1485
2172 SQL Server에서 테이블 이름을 변경하지 않고 테이터만 교체하기 jevida(강성욱) 2019.05.03 2238
2171 SQL Server master 데이터베이스 정리 jevida(강성욱) 2019.04.23 4890
2170 SQL Server 프로토콜과 SQLCMD를 사용한 연결 jevida(강성욱) 2019.04.23 1760
2169 SQL Server Edition 다운그레이드 후 확인사항 [1] jevida(강성욱) 2019.04.23 1661
2168 AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기 jevida(강성욱) 2019.04.23 1044
2167 SSRS에서 E-mail 세팅시 SSL 사용 유무 수정하기 jevida(강성욱) 2019.03.26 946
2166 SQL Linux Instance Name 변경 jevida(강성욱) 2019.03.26 832
2165 SQL Linux 업그레이드 jevida(강성욱) 2019.03.26 1084
2164 Azure SQL Managed Instance 에서 SQL Agent의 Job history 기록 보관하기 jevida(강성욱) 2019.03.26 951
2163 Azure VM에서 SQL Server에 대한 저장소 구성 지침 jevida(강성욱) 2019.03.26 953
2162 Azure SQL에 도입된 새로운 집계 함수 APPROX_COUNT_DISTINCT jevida(강성욱) 2019.03.26 737
2161 Azure SQL의 데이터베이스 소유권 체인 jevida(강성욱) 2019.03.26 834
2160 Azure SQL의 내부 디스크 할당 방법 및 저장 가능 용량 확인 jevida(강성욱) 2019.03.26 858
2159 Azure SQL에서 이메일 보내기 jevida(강성욱) 2019.03.26 745
2158 Azure SQL에서 읽기 전용 복제본에 대한 접속 및 링크 서버 생성 jevida(강성욱) 2019.03.26 843
2157 Azure SQL에서 네트워크를 구성하는 방법 jevida(강성욱) 2019.03.26 591
» 인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block jevida(강성욱) 2019.03.26 855
2155 SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상 jevida(강성욱) 2019.03.26 734





XE Login