인덱스 유지관리 작업과 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/

 




강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 34868
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 17235
2014 트랜잭션로그 파일이 손상된 데이터베이스 복원 하기 jevida(강성욱) 2017.01.11 4717
2013 트랜잭션 로그 백업을 읽고 트랜잭션 발생 시간 및 사용자 찾기 jevida(강성욱) 2017.01.11 3373
2012 RESOURCE_GOVERNOR_IDLE과 쿼리 성능 jevida(강성욱) 2017.01.11 2086
2011 TDE 암호화된 데이터베이스 복원 jevida(강성욱) 2017.01.11 2560
2010 재해복구를 위한 SQL Server 역할 가져오기 jevida(강성욱) 2017.01.11 2345
2009 비관리자 계정에 Profiler 실행 권한 부여하기 jevida(강성욱) 2017.01.11 3343
2008 SQL Server Agent 공유 일정 생성하기 jevida(강성욱) 2017.01.11 2225
2007 인덱스 리빌드는 통계를 업데이트 할까? jevida(강성욱) 2017.01.11 2565
» 인덱스 유지관리 작업과 SQL Server 쿼리 성능 jevida(강성욱) 2017.01.11 3455
2005 네트워크 드라이브에 데이터베이스 복원하기 jevida(강성욱) 2017.01.11 4335
2004 확장 저장 프로시저를 활용한 논리디스크 용량 확인 jevida(강성욱) 2017.01.11 2546
2003 날짜 참조 테이블 만들기 jevida(강성욱) 2017.01.11 3268
2002 인덱스 상세 정보 확인 jevida(강성욱) 2017.01.11 3843
2001 DTC Transacntion 오버헤드 jevida(강성욱) 2017.01.11 1339
2000 대용량 로드를 위한 BULK INSERT 옵션 jevida(강성욱) 2017.01.11 5607
1999 SQL Server 2014 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1665
1998 SQL Server 2012 Contained Database jevida(강성욱) 2017.01.11 1078
1997 SQL Server 2008R2 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1467
1996 SQL Server 2005 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1406
1995 601 Error, Could not continue scan with NOLOCK due to SQL Server data Movement jevida(강성욱) 2017.01.11 4185





XE Login