'인덱스 재구성' 태그의 글 목록

DBA, index reorganize, MSSQL, SQL Server, Update Statistics, 인덱스 재구성, 통계 업데이트

인덱스 재구성과 통계 업데이트시 발생하는 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

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server Edition 다운그레이드 후 확인사항 (0)	2019.04.12
AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기 (0)	2019.04.11
SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상 (0)	2019.03.26
SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기 (0)	2019.03.26
SQL Server MySQL PostgreSQL 비교 (0)	2019.03.26

MSSQL, Online Index Rebuild, SQL 2014, SQL Server, 온라인 인덱스 리빌드, 인덱스 재구성, 인덱스 조각화

SQL Server 2014 온라인 인덱스 리빌드와 잠금 우선순위 옵션

Version : SQL Server 2014

SQL Server에서 인덱스를 다시 작성하면 인덱스가 삭제된 다음 다시 생성된다. 인덱스가 생성되는 과정에서 조각화가 제거되고 지정된 채우기 비율 또는 기존 채우기 비율 설정을 기준으로 페이지를 압축하여 디스크 공간을 회수하고 인덱스 행을 연속된 페이지로 다시 정렬할 수 있다. 익스텐트가 128개 이상인 인덱스를 다시 작성하면 데이터베이스 엔진에서 실제 페이지 할당 취소와 해당 관련 잠금이 트랜잭션 커밋후까지 지연된다. 인덱스의 크기가 작은 경우에는 혼합 익스텐트에 저장되기 때문에 리빌드 후에도 조각화가 줄어들지 않는 경우가 있다.

ALTER INDEX : https://msdn.microsoft.com/ko-kr/library/ms188388(v=sql.120).aspx

SQL Server 2014에서 온라인 인덱스 리빌드시 처리 과정에서 SCH-M 잠금과 SCH-S 잠금을 처리해야 하기 때문에 블록이 발생한다. SCH-M 잠금은 다른 잠금 때문에 차단 시스템 둔화의 원인이 되는데 테이블에 잠금이 부여되는 다른 모든 프로세스와 호환되지 않는다. SQL Server 2014 Enterprise에서 온라인 인덱스 리빌드시 잠금 우선순위 매커니즘을 소개한다.

실습을 위해 테스트 테이블을 생성한다. 실습에서는 SQL Server Enterprise 또는 SQL Server Developer 에디션을 사용해야 한다.

-- Create a table with primary key

create table randomdata (

id int identity,

randomNumeric numeric (18,12),

randomDatetime datetime2,

randomVarchar varchar(100)

);

create clustered index cl_idx_id on randomdata(id)

--fill the table with data--on my machine this took just under 2 minutes.

set nocount on

declare @loopcounter int = 1

declare @randomNumeric numeric(18,12)

declare @randomDatetime datetime2

declare @randomVarchar varchar(1000)

declare @randomString VARCHAR(1000)

set @randomString = 'zi g upi vsm trsf yjod o jsbr vpmvrtmd eoyj jpe ;pmh upi

br nrrm eptlomh pm upit vp,[iyrt/ [rtsj[d oyd yo,r pgt s bsvsyopm/ mpy vtoyovodomh/ kidy pbrtdtbomh js[[u gsvr'

while @loopcounter < 1000000

begin

set @randomNumeric = round(rand() * 100,10)

set @randomDatetime = dateadd(minute,@randomnumeric,getdate())

set @randomVarchar = SUBSTRING(@randomString,convert(int,@randomNumeric),100 )

insert randomdata

( randomNumeric, randomDatetime, randomVarchar)

values

(@randomNumeric, @randomDatetime, @randomVarchar)

set @loopcounter = @loopcounter + 1

end

온라인 인덱스 리빌드 동작을 시뮬레이션 하기위해 SSMS에서 3개의 다른 창을 생성하고 각각의 스크립트를 추가한다.

--Window 1

begin transaction

select top 1000 * from randomdata with (holdlock)

--commit --Commented out on purpose so the transaction holds a lock on the table

--Window 1

begin transaction

select top 1000 * from randomdata with (holdlock)

--commit --Commented out on purpose so the transaction holds a lock on the table

--Window 3

select top 10 * from randomdata

쿼리창1에서 스크립트를 실행하여 트랜잭션을 시작한다. 커밋 구문은 잠금 동작을 입증하기 위해서 주석처리한다. 그리고 쿼리창3을 실행하면 쿼리창1에서 트랜잭션이 실행중임에도 불구하고 쿼리3에서 데이터가 조회되는 것을 확인할 수 있다. 쿼리창1의 SPID로 sp_lock을 조회해보면 IS 및 S 잠금을 확인할 수 있다.

SSMS에서 쿼리창2를 실행하여 온라인 인덱스 리빌드를 실행한다. (현재 쿼리창1에서는 트랜잭션이 그대로 유지되고 있다.) 다른 창을 실행하여 현재 실행중인 쿼리2의 SPID에 대한 잠금 정보를 조회한다. 스키마 잠금을 보유하고 있지만 다음 잠금을 위한 변화를 기다리는 동안 인덱스 작업은 계속 된다.

기존의 인덱스가 교체될 작업이 끝나면 SCH-M 잠금을 획득해야 다음 인덱스를 다시 작성하는 과정을 진행하고 SQL Server는 SCH-S 잠금을 유지한다. 잠금 호환성에 대한 표는 마이크로소프트 웹사이트를 참고한다.

Lock Compatibility : https://technet.microsoft.com/en-us/library/ms186396(v=sql.105).aspx

현재 쿼리창1에서 홀드 잠금이 진행중인 상태에서 쿼리창2의 온라인 인덱스 리빌드 작업이 진행 중이다. 온라인 인덱스 리빌드 작업이 SCH-M 잠금을 획득한 상황에서 쿼리창3을 실행하면 이전과 달리 데이터가 조회되지 않는다. sp_who2를 사용하여 쿼리창3의 SPID를 조회하면 쿼리창2의 SPID에 의해 차단된 것을 확인할 수 있다.

쿼리창1에서 현재 트랜잭션이 실행되고 있는 프로세스 커밋이 완료되면 쿼리창2의 온라인 인덱스 리빌드 작업이 다음 잠금을 획득하여 프로세스를 완료할 수 있다. 그리고 잠금으로 인해 대기하고 있던 쿼리창3도 데이터조회가 완료 된다.

SQL Server 2014 엔터프라이즈 버전에서는 온라인 인덱스 리빌드시 잠금에 관한 우선순위를 적용할 수 있다.

WAIT_AT_LOW_PRIORITY

온라인 인덱스 리빌드는 다른 잠금요청보다 낮은 우선순위로 대기

MAX_DURATION

분단위로 기다리는 시간

ABORT_AFTER_WAIT

MAX_DURATION이 충족된 후에 조취를 결정

None : 이전 동작을 따라 잠금 큐를 입력
Self : 온라인 인덱스 리빌드 작업 중지
Blockers : 블록킹 SPID를 강제 종료하고 온라인 인덱싱을 진행

온라인 인덱스 리빌드시 잠금 우선순위를 사용하기 위해 쿼리창2의 스크립트를 아래 스크립트로 변경한다. 변경된 스크립트는 최대 1분을 기다린 후 다음 작업을 수행한다.

--Window 2

alter index cl_idx_id on randomdata rebuild

with (ONLINE = ON(WAIT_AT_LOW_PRIORITY

(MAX_DURATION = 1 MINUTES , ABORT_AFTER_WAIT = SELF )), fillfactor = 80)

이전과 같이 쿼리창1, 쿼리창2, 쿼리창3을 실행하면 이전과 다른 동작을 확인할 수 있다. 쿼리창2에 적용된 새로운 잠금 우선순위로 낮은 잠금 우선위로 실행중인 쿼리창3이 실행되고 쿼리창2의 작업은 일정 시간이 지난뒤 중지된다.

쿼리창2의 스크립트 옵션을 블록킹 세션을 강제로 종료 후 실행하도록 수정한 다음 실행해보자.

--Window 2

alter index cl_idx_id on randomdata rebuild

with (ONLINE = ON(WAIT_AT_LOW_PRIORITY

(MAX_DURATION = 1 MINUTES , ABORT_AFTER_WAIT = BLOCKERS )), fillfactor = 80)

우선순위 옵션에서 MAX_Duration = 0을 사용했을 경우 아래와 같은 오류가 나타났다.

--Window 2

alter index cl_idx_id on randomdata rebuild

with (ONLINE = ON(WAIT_AT_LOW_PRIORITY

(MAX_DURATION = 0 MINUTES , ABORT_AFTER_WAIT = SELF )), fillfactor = 80)

SQL Server에서 트랜잭션의 잠금 관리는 매우 중요하다. 잠금 옵션의 사용으로 유지보수시 효율적인 작업을 진행 할 수 있도록 한다.

[참고자료]

https://www.mssqltips.com/sqlservertip/4085/using-sql-server-managed-lock-priority-for-online-index-rebuilds/

2015-11-26 / 강성욱 / http://sqlmvp.kr

SQL Server, MSSQL, SQL 2014, 온라인 인덱스 리빌드, Online Index Rebuild, 인덱스 재구성, 인덱스 조각화

'SQL Server > SQL Server Tip' 카테고리의 다른 글

Spool 연산자와 추적 플래그 8690 (0)	2016.01.11
.NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작 (0)	2015.12.04
함수 통계 정보 확인 (sys.dm_exec_function_stats) (0)	2015.11.24
NULL 데이터가 포함된 데이터 사용 시 주의점 (0)	2015.11.20
통계정보와 실제 데이터 분포 확인하기 (0)	2015.11.17

DB 튜닝, index rebuild, index reorg, MSSQL, sql 인덱스, SQLServer, SQL튜닝, 인덱스 리빌드, 인덱스 재구성

인덱스 유지관리 작업과 SQL Server 쿼리 성능

Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

SQL Server 데이터베이스 엔진에서는 기본 데이터에 삽입, 업데이트 또는 삭제 작업을 수행할 때마다 인덱스를 자동으로 유지 관리한다. 이러한 수정이 거듭되면 시간이 흐름에 따라 인덱스의 정보가 조각화되어 데이터베이스 내에 흩어지게 될 수 있다. 조각화는 키 값을 기준으로 하는 인덱스의 논리적 페이지 순서가 데이터 파일 내의 물리적 순서와 일치하지 않을 때 나타난다. 심하게 조각화된 인덱스는 쿼리 성능을 저하시키고 응용 프로그램의 응답이 느릴 수 있다.

인덱스 조각화가 심할 경우에는 Reorganization 또는 Rebuild에 대한 고민을 하게 되는데 이러한 작업 이후 쿼리 성능이 어떻게 되는지 살펴보자.

인덱스 다시 구성 및 다시 작성 :

https://msdn.microsoft.com/ko-kr/library/ms189858.aspx

테스트를 하기 위해 샘플 테이블 및 인덱스를 생성 한다.(데이터 생성에 많은 시간이 걸린다. 필자는 30분동안 데이터를 생성하였다.)

-- Create sample table and indexes

CREATE TABLE testtable ([col1] [int] NOT NULL primary key clustered,

[col2] [int] NULL,

[col3] [int] NULL,

[col4] [varchar](50) NULL);

CREATE INDEX idx_testtable_col3 on testtable (col3 asc);

-- Load sample data into table

DECLARE @val INT

SELECT @val=1

WHILE @val < 5000000

BEGIN

INSERT INTO testtable (col1, col2, col3, col4)

VALUES (@val,round(rand()*100000,0),round(rand()*100000,0),'TEST' + CAST(@val AS VARCHAR))

SELECT @val=@val+1

END

GO

샘플 데이터가 완성되었으면 SQL Server 인덱스 성능을 테스트 한다. 생성된 인덱스의 조각화 정보 확인 및 실제 조회 시 사용된 CPU, Reads, Write, Duration을 체크 한다. 이때 쿼리는 단일 값 및 범위, 인덱스 스캔을 할 수 있는 3가지 유형을 테스트하였다.

인덱스 조각화 정보 확인

SELECT object_name(object_id) as tablename,

index_id,index_type_desc,

avg_fragmentation_in_percent,

page_count,page_count*8/1024 as [size(mb)]

FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, DEFAULT)

WHERE object_name(object_id) = 'testtable' and index_id=2

각 쿼리의 성능을 비교 한다. 아래 성능표는 각 쿼리를 실행 했을 때 프로파일러를 통해 확인한 값이다.

SELECT col2 FROM testtable WHERE col3=55627;

SELECT col2 FROM testtable WHERE col3 BETWEEN 72000 AND 75000;

SELECT count(col3) FROM testtable;

	Test Scenario	CPU	Read	Write	Duration
1회	Single Value	0	189	0	0
	By Range	329	22583	0	716
	Index scan	1548	13662	0	423
2회	Single Value	0	189	0	1
	By Range	483	22583	0	687
	Index scan	1174	13662	0	331
3회	Single Value	0	189	0	0
	By Range	452	22583	0	668
	Index scan	1283	13662	0	356

인덱스 Reorganization 및 Rebuild를 통해서 성능을 확인하기 위해 인덱스 유지관리 작업을 한다. 이때 동일한 환경에서 작업 후 성능을 평가하기 위해 데이터베이스 백업 작업을 진행 한다. 이후 각 테스트마다 백업 된 데이터베이스를 복원하여 유지관리 작업을 진행하여 성능을 평가 한다.

인덱스 Rebuild

-- REBUILD test

-- After this completes run query against [dm_db_index_physical_stats] to

-- get fragmentation stats

ALTER INDEX [idx_testtable_col3] ON [dbo].[testtable] REBUILD PARTITION = ALL

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,

ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

	Test Scenario	CPU	Read	Write	Duration
1회	Single Value	0	193	0	0
	By Range	593	22587	0	699
	Index scan	1171	8774	0	324
2회	Single Value	0	189	0	0
	By Range	517	22583	0	703
	Index scan	1219	8772	0	357
3회	Single Value	0	189	0	0
	By Range	671	22583	0	824
	Index scan	1094	8772	0	315

인덱스 Reorganization

-- REORG test

-- After this completes run query against [dm_db_index_physical_stats] to

-- get fragmentation stats

ALTER INDEX [idx_testtable_col3] ON [dbo].[testtable] REORGANIZE

WITH ( LOB_COMPACTION = ON );

	Test Scenario	CPU	Read	Write	Duration
1회	Single Value	0	484	0	21
	By Range	640	22587	0	814
	Index scan	1156	8936	0	299
2회	Single Value	0	189	0	1
	By Range	453	22583	0	747
	Index scan	1485	8934	0	402
3회	Single Value	0	189	0	0
	By Range	531	22583	0	746
	Index scan	1110	8934	0	333

이 테스트 결과를 보면 인덱스 유지관리를 하였을 때 전체적으로 쿼리 성능이 크게 변하지 않는 것을 확인 할 수 있었다. 하지만 인덱스 스캔의 Read의 경우 매우 많은 성능 이점을 확인 할 수 있었다. 인덱스 재구성 후 인덱스 페이지가 감소되어 인덱스 스캔의 성능이 빨라진 것으로 유추할 수 있다.

이번 테스트 이후 인덱스 리빌드 작업은 과연 모든 운영 시스템에 필요한지 다시 한번 생각하게된다. 인덱스 스캔이 많다면 고려해볼만 하지만 대부분의 쿼리는 단일 값 또는 범위 검색을 하기 때문이다. 비즈니스를 확인하고 선택은 스스로 판단할 수 있도록 한다.

[참고자료]

http://www.mssqltips.com/sqlservertip/3533/sql-server-query-performance-after-index-maintenance-for-reorganization-vs-rebuild-operations/

2015-03-05 / 강성욱 / http://sqlmvp.kr

인덱스 리빌드, 인덱스 재구성, sqlserver, mssql, index rebuild, index reorg, sql 인덱스, DB 튜닝, sql튜닝

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server Agent 공유 일정 생성하기 (0)	2015.07.23
인덱스 리빌드는 통계를 업데이트 할까? (0)	2015.07.23
네트워크 드라이브에 데이터베이스 복원하기 (0)	2015.07.23
확장 저장 프로시저를 활용한 논리디스크 용량 확인 (0)	2015.07.23
날짜 참조 테이블 만들기 (0)	2015.07.23

Data Science Lab