with SQL Server

아주 오랜만에 포스팅 하는 것 같습니다.

이번 포스팅 주제는 SQL Server 힙 테이블에서 발생할 수 있는 Forwarded Record 입니다.

가변형 컬럼에 기존 값보다 큰 값이 저장되면 Clustered Index 테이블에서는 페이지 분할이 발생하지만

Heap 테이블에서는 기존 페이지에 Forwarding Pointer만 저장되고 데이터는 새로운 곳에 저장 됩니다.

결국 테이블 조회시 기존 보다 훨씬 많은 페이지를 조회해야 되기 때문에 IO가 증가 됩니다.

Forwarded Record가 발생했을 때 해결 방법이 몇 가지 있는데 

데모를 통해 확인하고 방법을 공유해 보도록 하겠습니다.

▶ 테스트용 테이블을 만들고 DMV를 통해 해당 테이블 내  Forwarded Record 카운트를 확인

USE AdventureWorks2008R2;

GO

IF EXISTS    (

                      SELECT  1

                      FROM   sys.tables

                      WHERE  NAME = 'Address2'

                      AND       SCHEMA_ID = 1

                  )

           DROP TABLE dbo.Address2;

GO

-- 테이블복사

SELECT  *

           ,          CONVERT(VARCHAR(500), 'comments') AS comments

INTO    Address2

FROM   Person.Address;

GO

-- Address2 테이블forwarded_record_count 조회

SELECT           index_type_desc

           ,          page_count

           ,          avg_page_space_used_in_percent

           ,          avg_record_size_in_bytes

           ,          forwarded_record_count

FROM   sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008R2'), OBJECT_ID('Address2'), NULL, NULL, 'detailed');

GO

▷ 변경사항이 없기 때문에 Forwarded Record 카운트가 0 인 것 을 확인 가능

▶ 가변형 컬럼에 기존 값 보다 큰 값을 업데이트하고 IO 확인

-- 기존저장된값보다큰값으로업데이트

UPDATE         Address2

SET               comments = REPLICATE('a', 500);

GO

-- Address2 테이블forwarded_record_count 조회

SELECT           index_type_desc

           ,          page_count

           ,          avg_page_space_used_in_percent

           ,          avg_record_size_in_bytes

           ,          forwarded_record_count

FROM   sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008R2'), OBJECT_ID('Address2'), NULL, NULL, 'detailed');

GO

▷ Forwarded Record 카운트가 증가 확인

-- IO 확인

SET STATISTICS IO ON;

SELECT  *

FROM   Address2;

SET STATISTICS IO OFF;

▷ 테이블 검색 시 논리적 읽기 수(page_count + forwarded_record_count) 확인

    Logical IO가 증가되어 성능에 영향을 미칠 수 있습니다.

★ Solution

▶ 증가되어 있는 값을 축소 시킨다.

-- 기존값은제거

UPDATE Address2

SET                 comments = '';

GO

SELECT           index_type_desc

           ,          page_count

           ,          avg_page_space_used_in_percent

           ,          avg_record_size_in_bytes

           ,          forwarded_record_count

FROM   sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008R2'), OBJECT_ID('Address2'), NULL, NULL, 'detailed');

GO

▷ 많이 감소 하였지만 완전한 해결책은 되지 못한다.

▶ Forwarded Record는 Heap 테이블에서 발생하므로 Clustered Index를 생성한다. 

-- 클러스터인덱스생성BEST

CREATE CLUSTERED INDEX cix_Address2_AddressID ON Address2(AddressID);

GO

SELECT           index_type_desc

           ,          page_count

           ,          avg_page_space_used_in_percent

           ,          avg_record_size_in_bytes

           ,          forwarded_record_count

FROM   sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008R2'), OBJECT_ID('Address2'), NULL, NULL, 'detailed');

GO

▷ Clustered Index가 생성되면서 Forwarded Record 해결

▶ SQL Server 2008 이상 버전이라면 테이블 리빌드 작업으로 해결 가능하다. 

-- 테이블리빌드SQL Server 2008 이상가능

ALTER TABLE Address2 REBUILD;

GO

SELECT           index_type_desc

           ,          page_count

           ,          avg_page_space_used_in_percent

           ,          avg_record_size_in_bytes

           ,          forwarded_record_count

FROM   sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008R2'), OBJECT_ID('Address2'), NULL, NULL, 'detailed');

GO

▷ 테이블 리빌드로 해결 가능 확인

관리중인 서버에 Forwarded Record가 증가된 힙 테이블이 존재한다면

위에서 제시한 방법을 통해 해결하시는 것도 고려해 보시길 바랍니다.

Reference : http://sqlblog.com/blogs/kalen_delaney/archive/2008/05/25/whats-worse-than-a-table-scan.aspx