안녕하세요. 하만철입니다.

SQL 2008부터 힙 테이블을 관리하는 방식이 변경된 것으로 보입니다.
데이터 삭제와 관련된 내용인데요. SQL 2000, 2005와 같이 이전 버전에서는 힙 테이블에 데이터를 입력한 다음 DELETE로 삭제하면 삭제된 공간을 재사용 할 수 있도록 관리 하였는데 SQL 2008의 힙 테이블에서는 DELETE로 삭제한 공간을 재사용할 수 없습니다.
때문에 INSERT , DELETE가 빈번한 힙 테이블은 실제 데이터가 몇건 없더라도 큰 공간을 차지할 수 있습니다.
또, 이 테이블을 조회하는 쿼리가 테이블 스캔을 하는 경우 힙 테이블 구조상 더블링크드 리스트가 없기 때문에 IAM페이지를 통하여 할당된 공간 전체를 스캔하여 소량의 데이터가 있더라도 테이블 스캔에 많은 IO가 발생할 수 있습니다.
이때는 클러스터드 인덱스를 생성, 사용 쿼리에 인덱스 힌트 추가 또는 주기적으로 TABLE REBUILD를 하여 이런 문제를 해결할 수 있습니다.

select @@VERSION

Microsoft SQL Server 2008 (SP1) - 10.0.2746.0 (Intel X86)

       Nov  9 2009 16:59:31

       Copyright (c) 1988-2008 Microsoft Corporation

       Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

 

USE [master]

GO

 

-- DB생성

CREATE DATABASE [TESTDB] ON  PRIMARY

( NAME = N'TESTDB_Data', FILENAME = N'D:\MSSQL\DATA\TESTDB_Data.MDF' , SIZE = 7000 , MAXSIZE = UNLIMITED, FILEGROWTH = 500 )

 LOG ON

( NAME = N'TESTDB_Log', FILENAME = N'D:\MSSQL\DATA\TESTDB_Log.LDF' , SIZE = 100 , MAXSIZE = UNLIMITED, FILEGROWTH = 500 )

GO

 

ALTER DATABASE [TESTDB] SET RECOVERY SIMPLE

GO

 

 

-- 테이블생성

use TESTDB

GO

 

CREATE TABLE [dbo].[tA](

 [col1] [int] NOT NULL,

 [col2] [smallint] NOT NULL,

 [col3] [varchar](20) NOT NULL,

 [col4] [varchar](400) NOT NULL,

 [col5] [int] NULL

) ON [PRIMARY]

GO

 

 

-- 테이블 데이터 입력

insert into tA with(TABLOCK)

select top 100000 ROW_NUMBER() over(order by (select 1)) r

,      ROW_NUMBER() over(order by (select 1)) % 32000 r

,      left(isnull(A.name, 'aaa') , 20)

,      isnull(A.name, 'bbb')

,      ROW_NUMBER() over(order by (select 1)) r

from sysindexes A

       , sysindexes B

       , sysindexes C

 

--넌클러스터드인덱스 생성(넌클인덱스가 있는 경우 인덱스로 할당된 공간은 정상적으로 반환함)

--alter table tA add CONSTRAINT [PK_tA] PRIMARY KEY NONCLUSTERED ([col1] ASC,[col2] DESC,[col3] ASC)

--CREATE NONCLUSTERED INDEX [IX_tA] ON [dbo].[tA] ([col1] ASC,[col3] ASC,[col2] ASC)

 

-- 데이터 모두 삭제

delete top (1000)

from tA

go 100

 

 

데이터 입력과 삭제를 반복 할때마다 계속해서 할당 공간이 증가하는것을 확인할 수 있습니다.

저는 테스트를 위해 300회 정도를 수행해 보았습니다. 

   

 

-- 할당된 공간 확인(건수가 0건인데 약 1.7GB정도 할당됨)

sp_spaceused tA

tA     0     1773456 KB   1773448 KB   8 KB   0 KB

 

 

-- 쿼리조회시IO 확인 (건수는 0인데 논리적 읽기수가 약 22만 페이지로 나옴)

set statistics io on

select * from tA

set statistics io off

 

(0 행이 영향을 받음)

테이블 'tA'. 검색 1, 논리적 읽기 221681, 물리적 읽기 2558, 미리 읽기 162886, LOB 논리적 읽기 0, LOB 물리적 읽기 0, LOB 미리 읽기 0.

 

 

-- 해결방법

1. alter table tA rebuild

   테이블 리빌드를 통하여 사용하지 않는 공간을 반환함.

 

2. create clustered index CL_tA on tA (col1)

   힙 테이블에 클러스터드 인덱스를 생성함.

 

3. select top 1 * from tA with(index(인덱스명))

   조회 쿼리에서 테이블 스캔을 하지 않도록 인덱스 힌트를 추가함. 



하만철 / Ha Man-cheol

AND