Forwarded Record (행 이동에 대한 포워드)

Forwarded Record (행 이동에 대한 포워드)

 

가변 컬럼(varcahr, nvarchar)에서 컬럼이 값이 현재 저장되어 있는 값 보다 큰 값으로 수정 되면 어떻게 될까?

결론부터 말하면 이 때에는 기존 페이지에는 더 큰 값으로 수정된 행의 값에 대한 공간이 없어서 새로운 값을 넣을 수 없어 새로운 행을 다른 페이지에서 할당 받아 값을 넣고 기존의 행에서 새로운 행으로 포워딩 하게 됩니다.

포워드 된 행은 Record Type에 FORWARDING_STUB로 표시 됩니다.

 

다음 실습을 통해서 알아 보도록 하겠습니다.

• 전체 코드

BEGIN TRY     DROP TABLE TBL_X END TRY BEGIN CATCH END CATCH GO   CREATE TABLE TBL_X (NUM INT, NAME NVARCHAR(400)) GO   INSERT INTO TBL_X SELECT ADDRESSID, CITY FROM ADVENTUREWORKS.PERSON.ADDRESS GO   --======================================================   SET STATISTICS PROFILE ON SET STATISTICS IO ON   SELECT * FROM TBL_X   SET STATISTICS PROFILE OFF SET STATISTICS IO OFF   EXEC SP_SPACEUSED TBL_X DBCC CHECKTABLE(TBL_X)       ---=============================================================   DBCC TRACEON (3604, 2514, 2509) DBCC IND(SW_TEST, TBL_X, 1) DBCC PAGE (SW_TEST, 1, 2561, 1)WITH TABLERESULTS   SET STATISTICS PROFILE ON SET STATISTICS IO ON   UPDATE TBL_X SET NAME = CONVERT(CHAR(300), 'HTTP://SQLMVP.KR') WHERE NUM % 3 = 0 GO   SELECT * FROM TBL_X GO   SET STATISTICS PROFILE OFF SET STATISTICS IO OFF   DBCC PAGE (SW_TEST, 1, 2561, 1)WITH TABLERESULTS   EXEC SP_SPACEUSED TBL_X   DBCC CHECKTABLE(TBL_X)   --================= FORWARDED RECORD 성능문제해결 --1. 데이터를기존의데이터값이하로축소한다.   UPDATE TBL_X SET NAME = 'SQLMVP' WHERE NUM % 3 = 0 GO DBCC CHECKTABLE (TBL_X)   --2. SHRINKDATABASE DBCC SHRINKDATABASE (SW_TEST) DBCC CHECKTABLE (TBL_X)   --3. CLUSTERED INDEX 생성. --위의예제에서는컬럼길이가4000바이트이므로인덱스사생성되지않으니 --900바이트이하의컬럼에서인덱스를생성합니다. CREATE CLUSTERED INDEX CI_TBL_X_NAME ON TBL_X (NAME) DBCC CHECKTABLE (TBL_X)

 

 

테스트 테이블을 생성하고 테이블을 조회 합니다. 다음과 같이 19614의 행을 읽는데 읽기 수 : 84의 비용이 소모 되었습니다.

SET STATISTICS PROFILE ON SET STATISTICS IO ON   SELECT * FROM TBL_X   SET STATISTICS PROFILE OFF SET STATISTICS IO OFF

 

 

 

SP_SPACEUSED 명령을 통해 할당된 공간을 확인 합니다.

EXEC SP_SPACEUSED TBL_X

 

 

DBCC CHECKTABLE 명령으로 테이블에 대한 정보를 확인 합니다.

DBCC CHECKTABLE(TBL_X)

 

 

 

DBCC PAGE명령어를 이용하여 해당 페이지의 데이터를 확인 합니다.

DBCC TRACEON (3604, 2514, 2509) DBCC IND(SW_TEST, TBL_X, 1) DBCC PAGE (SW_TEST, 1, 2561, 1)WITH TABLERESULTS

 

 

 

UPDATE 구문을 통하여 현재 데이터보다 큰 값으로 NAME 컬럼의 데이터를 수정합니다.

UPDATE TBL_X SET NAME = CONVERT(CHAR(5000), 'HTTP://SQLMVP.KR') WHERE NUM % 3 = 0 GO   SET STATISTICS PROFILE ON SET STATISTICS IO ON   SELECT * FROM TBL_X   SET STATISTICS PROFILE OFF SET STATISTICS IO OFF

 

수정 전의 행과 동일한 19614의 행을 조회하는데 13160의 읽기 비용이 소모 되었습니다.

 

 

테이블의 크기를 확인해 봅니다.

EXEC SP_SPACEUSED TBL_X

 

 

DBCC CHECKTABLE 명령어로 확인해 보면 Forwarded Record Count = 6538로 업데이트 된 행의 개수만큼 포워드 된 것을 확인 할 수 있습니다.

DBCC CHECKTABLE(TBL_X)

 

 

DBCC PAGE를 이용하여 데이터를 확인해 보면 Record Type에 FORWARDING_STUB로 변경 된 것을 확인 할 수 있습니다.

DBCC PAGE (SW_TEST, 1, 2561, 1)WITH TABLERESULTS

 

 

성능 모니터의 [SQLServer:Access Methods]의 [Forwarded Records/sec]항목을 통하여 모니터링이 가능 합니다.

 

 

지금까지의 실습을 통해 기존의 행 보다 큰 값으로 수정 될 경우 기존 페이지가 아닌 새로운 페이지를 할당 받아 저장하고 기존의 행에는 새로운 행을 포워드 정보만 가지고 있다는 것을 확인 할 수 있습니다.

이동된 행의 수가 많아 질수록 이 행들을 읽기 위한 I/O 비용이 증가 함에 따라 SELECT, INSERT, UPDATE, DELETE의 성능이 나빠집니다.

 

[Forwarded Record에 따른 성능 문제 해결 방법]

• 수정 된 행들에 값에 대하여 기존의 행 크기 이하로 줄인다.
• DBCC SHRINKDATABASE를 실행한다.
• 클러스터 인덱스를 생성한다.

 

[수정 된 행들에 값에 대하여 기존의 행 크기 이하로 줄인다]

 

 

[DBCC SHRINKDATABASE를 실행한다]

 

[클러스터 인덱스를 생성한다.]

 

 

2012-08-06 / 강성욱 / http://sqlmvp.kr