SQL 2008에서 힙 테이블 관리
안녕하세요. 하만철입니다.
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
-- 할당된 공간 확인(건수가 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
