728x90

김연욱(Vichang)님의 Inside Query Performance - (2)

 

안녕하세요. 김대우 입니다. 더운 여름 잘 지내고 계신지요?

이번에 소개해 드릴 내용은 김연욱님이 이끌어 주시는 Inside Query Performance 입니다.

아주 좋은 SQL 쿼리 성능에 대한 연재글을 올려 주고 계시며 SQLER에 많은 도움을 주시는 분이지요.

이하 내용은 김연욱님이 SQLER 자유게시판에 올려주신 항목을 정리한 내용입니다.

언제나 많은 도움 주시는 김연욱님 감사 드리구요. 앞으로도 좋은... 더운 여름 더더욱 열나게 하는 ^_^;;;

좋은 글 부탁 드립니다. 감사합니다.

이하 김연욱님의 글입니다.


 

 

일반적으로 이력관리 모델에서는 시작일자와 종료일자컬럼을 가진다.

--> 이부분에 대한것은 워낙에 내용이 길기때문에 생략.

--> 뭐 그냥 시작일자나 종료일자 하나만 가지는 설계를 사용하신다면 할 말 없음

이력모델에서 시작일자 + 종료일자 인덱스를 사용하는것이 좋을지

아니면 종료일자 + 시작일자 인덱스를 사용하는것이 좋을지

한번 테스트 해보자.

 


 


선행지식을 위해 between을 생각해보자.

일반적으로 우리가 사용하는 between은 컬럼 between 상수값1 and 상수값2  의 형태이다.

하지만, 조금 변형하여 상수값 between 컬럼1 and 컬럼2 의 형태로도 사용이 가능하다.

간단하게 예를 들어보면

벙개를 위한 장소를 몰색하던 00군이 인터넷으로 벙개용 좌석예약을 할려구 한다.

 

--drop table 좌석예약
create table 좌석예약(좌석등급 char(1), 시작번호 int, 종료번호 int, 좌석수 int, 구분 varchar(10))
go
insert into 좌석예약 values('A', 1, 100, 100, '공석')
insert into 좌석예약 values('B', 1, 100, 100, '공석')
go

 


마침 00주점에 이러한 시스템이 있었다.

사무실에서 pc로 단가가 적은 B석 51번부터 10개의 좌석을 예약한다고 해보자.

이럴때 어떻게 쿼리해야할까 ?

 

--1. 좌석상태확인
select * from 좌석예약
where 좌석등급 = 'B' and 좌석수 >= 10 and 구분='빈자리'
and 51 between 시작번호 and 종료번호

좌석 등급        시작번호    종료번호    좌석수         구분        
---- ----------- ----------- ----------- ----------
B    1           100         100         빈자리

(1개 행 적용됨)


이제 between 사용에 감이 잡히겠지요.

기왕 하는김에 좌석예약을 해보자.
 

 

--2. 좌석예약
set nocount on
declare @좌석등급 char(1), @좌석수 int, @시작번호 int, @종료번호 int, @구분 varchar(10)
declare @좌석위치 int
set @좌석등급 = 'B'
set @좌석수 = 10
set @좌석위치 = 51
set @구분 = 'SQLER 벙개'

set xact_abort on
begin tran
   if exists(select * from 좌석예약 where 좌석등급 = 'B' and 좌석수 >= 10 and 구분='빈자리'
                                      and @좌석위치 between 시작번호 and 종료번호)
      begin
         update 좌석예약
         set 시작번호 = @좌석위치,
             종료번호 = @좌석위치 + @좌석수 - 1,
             좌석수 = @좌석수,
             구분 = @구분,
             @시작번호 = 시작번호,
             @종료번호 = 종료번호
         where 좌석등급 = 'B' and 좌석수 >= 10 and 구분='빈자리'
           and @좌석위치 between 시작번호 and 종료번호

         if @시작번호 < @좌석위치  
            begin
               insert into 좌석예약 values (@좌석등급, @시작번호, @좌석위치-1,
                                            @좌석위치-@시작번호, '빈자리')
            end

         if @종료번호 > (@좌석위치 + @좌석수)
            begin
               insert into 좌석예약 values (@좌석등급, @좌석위치+@좌석수, @종료번호,
                                            @종료번호-@좌석위치-@좌석수, '빈자리')
            end
      end
commit tran
set nocount off
go

 



예약을 잘되었는지 확인해보면...

 

 

select * from 좌석예약 where 좌석등급 = 'B' order by 시작번호

좌석 등급        시작번호    종료번호    좌석수         구분        
---- ----------- ----------- ----------- ----------
B    1           50          50          빈자리
B    51          60          10          SQLER 벙개
B    61          100         39          빈자리

(3개 행 적용됨)

51번부터 60번까지 예약이 잘되있다...  근데 난 언제나 벙개에 함 나갈볼런지...

 


 

 

/*
선행지식
    1. 재미없는 긴글을 끝까지 읽어줄 강인한 인내력 (필수)

    2. sql서버는 복합컬럼인덱스일경우 첫번째 컬럼의 통계정보만을 사용함. (중요)
    3. 인덱스에 대한 기본지식
    4. between에 대한 기본지식
       일반적으로 between을 이용할경우는 변수 between 시작값 and 종료값 의 형식으로
       사용하지만 상수값 between 시작변수 and 종료변수 와 같이 사용할 수 도 있다.
       (좀더 자세한 내용은 관련 서적 참고)

    해당인덱스를 clustered index 로 할것인지 nonclustered index 로 할것인지는 별개의
    문제이므로 논외로 합니다.
*/

--언제나 테스트는 tempdb에서
use tempdb
go

--역시나 테스트를 위해서 테이블 하나 만들구
--drop table a
create table a(edt int not null, sdt int not null, num int identity)
go

--테스트를 편하게 하고자 날짜형 대신에 숫자형으로 테스트
/* ========================================================================== */
--켜져있으면 끄놓구
set statistics io off
set statistics profile off

--테스트용 샘플 넣구
set nocount on
declare @i int
set @i = 0
while (@i < 100000)
begin
insert a values(@i+1, @i)
set @i = @i + 2
end
set nocount off
select count(*) from a  --50000건

--테스트를 위해 켜두고
set statistics io on
set statistics profile on
go

 


 

 

--아무런 제약조건, 인덱스 없는 상태에서 그냥
select num from a

|--Table Scan(OBJECT:([tempdb].[dbo].[A]))
--논리적 읽기 수 137 (전체 페이지수가 137page임)
 

--먼저 시작일 + 종료일자로 pk 잡아주고
alter table a add constraint pk_a primary key nonclustered(sdt, edt)


--저 앞에 있는놈을 보면
select num from a where 90 between sdt and edt

|--Table Scan(OBJECT:([tempdb].[dbo].[A]), WHERE:(Convert([@1])>=[A].[SDT]
                                                AND Convert([@2])<=[A].[EDT]))
-- 논리적 읽기 수 137
 

-- 저 뒤에 있는 놈을 보면
select num from a where 90000 between sdt and edt

|--Table Scan(OBJECT:([tempdb].[dbo].[A]), WHERE:(Convert([@1])>=[A].[SDT]
                                                AND Convert([@2])<=[A].[EDT]))
--논리적 읽기 수 137
 

-- 강제로 pk를 타라고 힌트를 주면 어떨까 ?
select num from a (index=pk_a) where 90000 between sdt and edt
  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))

       |--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[SDT] <= 90000),
                                                         WHERE:(90000<=[A].[EDT])
--논리적 읽기 수 108
 

--그럼, top 1 을 주고 인덱스를 타라고 힌트를 주면 어떨까 ?
select top 1 num from a (index=pk_a) where 90000 between sdt and edt

  |--Top(1)
       |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
            |--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[SDT] <= 90000),
                          WHERE:(9000<=[A].[EDT]) ORDERED FORWARD)

--논리적 읽기 수 108
 

--일단, pk 지우고
alter table a drop constraint pk_a
 

--이번에는 종료일 + 시작일루 pk 잡아주고
alter table a add constraint pk_a primary key nonclustered(edt, sdt)
 

--저 앞에 있는놈을 보면
select num from a where 90 between sdt and edt

  |--Table Scan(OBJECT:([tempdb].[dbo].[A]), WHERE:(Convert([@1])>=[A].[SDT]
                                                AND Convert([@2])<=[A].[EDT]))
--논리적 읽기 수 137
 

--그럼, pk를 타라구 힌트를 주면
select num from a (index(pk_a))
where 90 between sdt and edt
select num from a (index(pk_a))  where 90 between sdt and edt

  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90), 
                             WHERE:(90>=[A].[SDT]) ORDERED FORWARD)
--논리적 읽기 수 120
 

--요번에는 top 1 까지 주면
select top 1 num from a (index(pk_a))
where 90 between sdt and edt

  |--Top(1)
       |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
            |--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90), 
                                  WHERE:(90>=[A].[SDT]) ORDERED FORWARD)
--논리적 읽기 수 3
 

-- 요번엔 저뒤에 있는놈을 보면
select num from a where 90000 between sdt and edt

  |--Table Scan(OBJECT:([tempdb].[dbo].[A]), WHERE:(Convert([@1])>=[A].[SDT]
                                               AND Convert([@2])<=[A].[EDT]))
--논리적 읽기 수 137

--이번에두 힌트사용
select num from a (index(pk_a)) where 90000 between sdt and edt

  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90000), 
                             WHERE:(90000>=[A].[SDT]) ORDERED FORWARD)
--논리적 읽기 수 15
 

--그럼, top 1 을 주면
select top 1 num from a (index(pk_a)) where 90000 between sdt and edt

  |--Top(1)
       |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
            |--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90000), 
                          WHERE:(90000>=[A].[SDT]) ORDERED FORWARD)
--논리적 읽기 수 3
 

-- 통계정보를 만들어 주면 좀더 낳지 않을까(역시 동일한 결과) - 각자 해보세요...
CREATE STATISTICS ust_a_edt
   ON test..a (edt)
   WITH FULLSCAN
CREATE STATISTICS ust_a_sdt
   ON test..a (sdt)
   WITH FULLSCAN
UPDATE STATISTICS a(pk_a)
   WITH SAMPLE 100 PERCENT

 


결론 : sql서버에서 --> 상수값 between 시작값 and 종료값 조인을 할경우
1. 시작일 + 종료일 인덱스일경우
  1) 시작일만을 사용하므로 해당조건의 선택도가 항상동일함에도 불구하고,

     시작일 <= 상수값 의 조건만을 활용한다.

       |--Index Seek(OBJECT:([test].[dbo].[A].[PK_A]), SEEK:([A].[SDT] <= 90), 
                     WHERE:(90<=[A].[EDT]) ORDERED FORWARD)

  2) 시작일 <= 상수값의 조건으로 인덱스를 스캔하면서 종료일 >= 상수값의 조건으로

     필터처리한다.(무식한놈)


2. 종료일 + 시작일 인덱스일경우
  1) 종료일만을 사용하므로 해당조건의 선택도가 항상동일함에도 불구하고,

     종료일 >= 상수값 의 조건만을 활용한다.

        |--Index Seek(OBJECT:([test].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90000), 
                      WHERE:(90000>=[A].[SDT]) ORDERED FORWARD)

  2) 종료일 >= 상수값의 조건으로 인덱스를 스캔하면서 시작일 <= 상수값의 조건으로

     필터처리한다.(무식한놈)


3. 결론
  1) 종료일 + 시작일 순으로 인덱스를 만들고나서

  2) SELECT 절에 TOP 1 을 추가하고

  3) INDEX(인덱스명) 힌트를 사용하면 항상 동일한 결과를 나타낸다.

  ※ 시작일 + 종료일 순으로 인덱스를 만들면 위와같이(1번에서 3번까지) 해도 비효율적인 스캔을 한다.

 


누군가가 딴지를 걸어주기를 기다렸는데 아무두 안걸어주어서 내가 딴지건다.

딴지) 뭐땜시, 복잡하게 종료일자 + 시작일자로 인덱스를 만드나, 그냥 시작일자 하나만 있어두 되던데...

 

--켜져있으면 끄놓구
set statistics io off
set statistics profile off
go

--일단, pk 지우고
alter table a
drop constraint pk_a

--이번에는 종료일 + 시작일루 pk 잡아주고
alter table a add constraint pk_a primary key nonclustered(sdt)

--테스트를 위해 다시 켜두고
set statistics io on
set statistics profile on
go

select top 1 num from a where sdt <= 90 order by sdt desc

  |--Top(1)
       |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
            |--Index Seek(OBJECT:([tempdb].[dbo].[a].[pk_a]),
                     SEEK:([a].[sdt] <= 90) ORDERED BACKWARD)
--논리적 읽기 수 3

select top 1 num from a where sdt <= 9000 order by sdt desc

  |--Top(1)
       |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
            |--Index Seek(OBJECT:([tempdb].[dbo].[a].[pk_a]),
                     SEEK:([a].[sdt] <= 9000) ORDERED BACKWARD)
--논리적 읽기 수 3

 

두경우 모두 논리적 읽기 수가 3이다. 이것이 더 좋지 않느냐, 쿼리도 깔끔하구..
 


그럼, sample을 조금 바꿔서 다른경우를 살펴보자.

 

--켜져있으면 끄놓구
set statistics io off
set statistics profile off


--drop table k1
create table k1(상품코드 int not null, 상품명 varchar(100))
go
alter table k1
   add constraint pk_k1 primary key nonclustered(상품코드)
go
set nocount on
declare @i int
set @i = 1
while(@i <= 1000)
begin
   insert into k1 values(@i, 'HDD ' +convert(varchar(10),@i)+'G')
   set @i = @i + 1
end
set nocount off
go

--drop table k2
create table k2(적용일자 datetime not null, 종료일자 datetime not null, 단가 int)
go
set nocount on
declare @i datetime
set @i = '2000-01-01'
while(@i <= '2003-04-30')
begin
    insert into k2 values(@i, dateadd(dd,1,@i), convert(int, rand()*1000))
    set @i = dateadd(dd, 1, @i)
end
set nocount off

--drop table k3
create table k3(상품코드 int not null, 적용일자 datetime not null,
                종료일자 datetime not null, 단가 int)
go
alter table k3
   add constraint pk_k3 primary key nonclustered(상품코드, 적용일자)
go
set nocount on
insert into k3
   select k1.상품코드, k2.적용일자, k2.종료일자, k2.단가
   from k1 cross join k2
set nocount off

--drop table k4
create table k4(상품코드 int not null, 적용일자 datetime not null,
                종료일자 datetime not null, 단가 int)
go
alter table k4
   add constraint pk_k4 primary key nonclustered(상품코드, 종료일자, 적용일자)
go
set nocount on
insert into k4
   select k1.상품코드, k2.적용일자, k2.종료일자, k2.단가
   from k1 cross join k2
set nocount off

 


 


 

 

--다시 테스트를 위하여 켜놓구
set statistics io on
set statistics profile on
go

select count(*) from k3 --1216000
'k3' 테이블. 스캔 수 1, 논리적 읽기 수 3458

select count(*) from k4 --1216000
'k4' 테이블. 스캔 수 1, 논리적 읽기 수 4662

 

인덱스size 차이로 인하여 k4가 page수가 더 많다.

 

--1. 1번 상품의 '2003-03'월 단가변동내역이 필요하다면 ?

select 적용일자, 단가
from k3
where 적용일자 <= '2003-03-31' and 종료일자 >= '2003-03-01'
and 상품코드=1


|--Table Scan(OBJECT:([tempdb].[dbo].[k3]),
           WHERE:(([k3].[적용일자]<=Convert([@1]) AND [k3].[종료일자]>=Convert([@2]))
           AND [k3].[상품코드]=Convert([@3])))

'k3' 테이블. 스캔 수 1, 논리적 읽기 수 5197

 

적용일자가 2003-03-31일 보다 작은것이 대부분이기 때문에 table scan을 선택했다.

 

 

/* ========================================================================== */

select 적용일자, 단가
from k4
where 적용일자 <= '2003-03-31' and 종료일자 >= '2003-03-01'
and 상품코드=1

  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[k4]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[k4].[pk_k4]),
                SEEK:([k4].[상품코드]=Convert([@3]) AND [k4].[종료일자] >= Convert([@2])), 
                WHERE:([k4].[적용일자]<=Convert([@1])) ORDERED FORWARD)

'k4' 테이블. 스캔 수 1, 논리적 읽기 수 36

 

인덱스만을 읽어서 범위를 줄인후에 테이블을 읽어서 단가를 가져왔다.

논리적읽기수를 비교해봐라 엄청난 차이다.


물론, 상황에 따라서 적절한 인덱스 설계를 해야하겠지만

일반적으로 이력모델에서는 종료일자 + 시작일자 의 복합인덱스가 유리한 경우가 많다.

 


김연욱(Vichang)님의 Inside Query Performance - (2)

 

저작권 : 이 홈페이지의 내용은 모두 자유롭게 사용하실 수 있습니다.

728x90

김연욱(Vichang)님의 Inside Query Performance - (1)

 

안녕하세요. 김대우 입니다. 더운 여름 잘 지내고 계신지요?

이번에 소개해 드릴 내용은 김연욱님이 이끌어 주시는 Inside Query Performance 입니다.

아주 좋은 SQL 쿼리 성능에 대한 연재글을 올려 주고 계시며 SQLER에 많은 도움을 주시는 분이지요.

이하 내용은 김연욱님이 SQLER 자유게시판에 올려주신 항목을 정리한 내용입니다.

언제나 많은 도움 주시는 김연욱님 감사 드리구요. 앞으로도 좋은... 더운 여름 더더욱 열나게 하는 ^_^;;;

좋은 글 부탁 드립니다. 감사합니다.

이하 김연욱님의 글입니다.


 

 

1. 문자컬럼에다가 숫자컬럼을 비교하면 인덱스를 타는가 ?

2. 숫자컬럼에다가 문자컬럼을 비교하면 인덱스를 타는가 ?

3. 문자컬럼에다가 datetime컬럼을 비교하면 인덱스를 타는가 ?

4. datetime컬럼에다가 문자컬럼을 비교하면 인덱스를 타는가 ?

 

밑에 결과 보지말구 그냥 5초만 생각보세요.

중수이상 되시는 분은 빨랑 백스페이스를 눌러서 나가주세용.

 

--tempdb에서 테스트
use tempdb
go


--있으면 지우고
drop table a
go


--테스트용 테이블 하나 만들고
create table a(id int identity, cha varchar(10))
go
 

--역시나 테스트용 데이터 넣구
set nocount on
go
declare @i int
set @i = 1
while (@i <= 10000)
begin
insert into a(cha) values (convert(varchar(10), @i))
set @i = @i + 1
end
go
set nocount off
go


--인덱스 만들구
create index idx on a (cha)
create index idx2 on a (id)
go

--테스트를 위해 io량을 보고, 실행계획을 보기위해 설정
set statistics io on
set statistics profile on
go

 


 

 

--어떤경우에 인덱스를 쓸까 ?
--1. 그냥한번 풀로 읽어보면
select * from a

 

|--Table Scan(OBJECT:([tempdb].[dbo].[a]))

'a' 테이블. 스캔 수 1, 논리적 읽기 수 28
 

 

--2. 정상적으로 컬럼형식을 맞춰서 검색하면 ?
select * from a where id = 1

 


|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx2]), SEEK:([a].[id]=Convert([@1])) ORDERED FORWARD)

'a' 테이블. 스캔 수 1, 논리적 읽기 수 3

 

 

select * from a where cha='1'

 

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]), SEEK:([a].[cha]='1') ORDERED FORWARD)

'a' 테이블. 스캔 수 1, 논리적 읽기 수 3
--제대로 3페이지만 인덱스를 사용하여 검색

 

 

--3. 그럼 숫자컬럼에다가 문자를 비교하면 ?
select * from a where id = '1'

 


|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx2]), SEEK:([a].[id]=Convert([@1])) ORDERED FORWARD)

'a' 테이블. 스캔 수 1, 논리적 읽기 수 3

 

 

--4. 문자컬럼에다가 숫자를 비교하면 ?
select * from a where cha=1

 


  |--Table Scan(OBJECT:([tempdb].[dbo].[a]), WHERE:(Convert([a].[cha])=Convert([@1])))

'a' 테이블. 스캔 수 1, 논리적 읽기 수 28

--웬 테이블 스캔....

생각하신대로 결과가 나왔나요 ?


그럼 이번에는 많이 사용하는 datetime 컬럼을 테스트해보자.

 

--다른 샘플을 만들기 위해 잠시 off시키고
set statistics io off
set statistics profile off
go
 

--있으면 지우고
drop table b
go
 

--테스트용 테이블 하나 만들고
create table b(dt1 datetime, dt2 char(8))
go
 

--역시나 테스트용 데이터 넣구
set nocount on
go
declare @i datetime
set @i = '2000-01-01'
while (@i <= '2003-12-31')
begin
insert into b values (@i, convert(varchar(8), @i, 112))
set @i = dateadd(dd, 1, @i)
end
go
set nocount off
go
 

--인덱스 만들구
create index idx on b (dt1)
create index idx2 on b (dt2)
go

--테스트를 위해 io량을 보고, 실행계획을 보기위해 설정
set statistics io on
set statistics profile on
go

 



 

 

--어떤경우에 인덱스를 쓸까 ?
--1. 그냥한번 풀로 읽어보면
select * from b

 


|--Table Scan(OBJECT:([tempdb].[dbo].[b]))

'b' 테이블. 스캔 수 1, 논리적 읽기 수 5

 

 

--2. 정상적으로 컬럼형식을 맞춰서 검색하면 ?
declare @dt1 datetime
set @dt1 = '2003-05-05'
select * from b where dt1 = @dt1

 


|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[b]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[b].[idx]), SEEK:([b].[dt1]=[@dt1]) ORDERED FORWARD)

'b' 테이블. 스캔 수 1, 논리적 읽기 수 3

 

 

declare @dt2 char(8)
set @dt2 = '20030505'
select * from b where dt2 = @dt2

 


|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[b]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[b].[idx2]), SEEK:([b].[dt2]=[@dt2]) ORDERED FORWARD)

'b' 테이블. 스캔 수 1, 논리적 읽기 수 3

 

 

--3. datetime 컬럼에다가 문자를 비교하면 ?
declare @dt2 char(8)
set @dt2 = '20030505'
select * from b where dt1 = @dt2

 


|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[b]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[b].[idx]), SEEK:([b].[dt1]=Convert([@dt2])) ORDERED FORWARD)

'b' 테이블. 스캔 수 1, 논리적 읽기 수 3

 

 

--4. 문자컬럼에다가 datetime을 비교하면 ?
declare @dt1 datetime
set @dt1 = '2003-05-05'
select * from b where dt2 = @dt1

 


|--Table Scan(OBJECT:([tempdb].[dbo].[b]), WHERE:(Convert([b].[dt2])=[@dt1]))

'b' 테이블. 스캔 수 1, 논리적 읽기 수 5


--웬 테이블 스캔....

 

생각하신대로 결과가 나왔나요 ?
 



결론, 문자대 숫자의 비교는 무조건 문자를 숫자로 변환후, 또는 문자를 datetime형으로

변환후 비교할려구 한다.(자동형변환에 의하야 - 무조건 형식을 맟춰서 사용해야하겠네요.)

프로그램 개발시 주의하세요 !!!

난이의 밥벌이에 대해서 좀더 알아 보도록 하겠습니다. ^_^

 


김연욱(Vichang)님의 Inside Query Performance - (1)

 

저작권 : 이 홈페이지의 내용은 모두 자유롭게 사용하실 수 있습니다.

728x90

인덱스 관리 개선기능

인덱스는 데이터베이스에 저장된 데이터에 좀 더 빠른 접근을 가능하게 하는 핵심 기능입니다.

하지만, 데이터베이스에 저장된 데이터가 변경됨에 따라 적절한 인덱스를 유지 보수하는 작업은

상당한 시간과 자원을 필요로 합니다. 데이터베이스 관리자가 인덱스를 생성, 변경, 삭제하는

방법을 이해하고 있어야 할 뿐만 아니라, 인덱스가 적절하게 사용될 수 있도록 조각화에 대한

대처 방법을 알고 있어야 합니다.

SQL Server 2005에서는 인덱스 관리측면에서 뛰어난 개선기능을 제공하며, 새로운 DDL 문장과

인덱스 관련 기능이 제공됩니다.


SQL Server 2005의 새로운 인덱스 관리 기능

SQL Server 2005에서도, SQL Server 2000과 마찬가지로 클러스터형 인덱스와 비클러스터형

인덱스를 제공합니다. 또한, 계산된 컬럼이나 뷰에 대해서도 인덱스를 생성할 수 있습니다.

하지만, SQL Server 2005에서는 인덱스를 생성하고 처리하는 방법에 대한 개선된 기능을

제공하고, 새로운 형식의 인덱스를 생성할 수 있습니다.

ALTER INDEX 구문
SQL Server 2005의 새로운 인덱스 관련 기능 중 하나는 ALTER INDEX T-SQL 구문에 대한

지원으로, 인덱스를 재생성하고, 재조직하고, 비활성화하고, 인덱스 관련 옵션을 조정하기

위해서 사용합니다. ALTER INDEX 구문을 사용하면, 인덱스의 조각모음을 위해 인덱스를

삭제하고 재 생성할 필요가 없어집니다. 또한, 이전 버전 SQL Server에서 사용하였던

DBCC 명령의 일부를 대체하게 됩니다.

온라인 인덱스 작업
SQL Server 2005에서는 인덱스가 사용중인 상태에서 인덱스에 대한 관리작업을 수행할 수

있기 때문에, 관리작업을 위한 부하가 최소화될 수 있습니다. 이전 버전 SQL Server에서는

인덱스에 대한 작업을 하는 동안 테이블과 인덱스에 대해서 배타적 잠금을 설정하였기

때문에, 인덱스 작업이 완료될 때까지는 해당 테이블에 차단현상이 발생하였습니다.

온라인 인덱스 작업을 수행하기 위해서는 해당 작업을 지원하기 위한 추가적인 디스크

공간이 필요합니다. CREATE INDEX, ALTER INDEX, DROP INDEX 명령을 실행할 때는,

해당작업을 온라인으로 작업할 것인지 여부를 지정해야 합니다.

병렬인덱스 작업
인덱스를 생성, 변경, 삭제하는 작업에는 정렬 및 스캔을 작업을 위해 여러 개의 CPU를

사용하여 병렬처리 작업을 수행하는 것이 효과적입니다. SQL Server 2005에서는 인덱스

작업에 대한 병렬처리가 자동으로 수행되며, 별도의 MAXDOP 옵션을 지정하여 병렬처리에

사용할 CPU 개수를 지정할 수 있습니다. CREATE INDEX, ATLER INDEX, DROP INDEX TSQL

명령을 수행할 때 MAXDOP 쿼리 힌트를 사용하여 해당 작업에 대한 병렬처리를 위해 사용

할 CPU 개수를 지정할 수 있습니다.

잠금옵션
SQL Server 2005에서는 인덱스를 사용할 때, 새로운 두 가지 잠금 옵션을 제공합니다. 두가지

잠금옵션은 동시에 활성화될 수 있습니다.

    ALLOW_PAGE_LOCKS
       ALLOW_PAGE_LOCK 옵션이 ON으로 설정되면, 테이블 잠금이나 페이지 잠금을 사용하여

       인덱스에 접근하도록 설정합니다. OFF로 설정되면, 페이지 잠금이 사용되지 않습니다.

    ALLOW_ROW_LOCKS
       ALLOW_ROW_LOCKS 옵션이 ON으로 설정되면, 테이블 잠금이나 행 단위 잠금을 사용하여

       인덱스에 접근하도록 설정합니다. OFF로 설정되면, 행 단위 잠금이 사용되지 않습니다.

인덱스에 포함되는 컬럼 지정
SQL Server 2005에 비클러스터형 인덱스에는 리프 노드에 인덱스 키가 아닌 데이터도 포함될

수 있습니다. 인덱스에 포함된 컬럼에 대한 접근이 필요한 쿼리가 커버된 쿼리로 실행될 수

있기 때문에 성능을 향상시킬 수 있지만, 해당 데이터를 관리하기 위한 별도의 추가적인

디스크 공간이 필요합니다. 인덱스에 포함된 컬럼은 이전 버전 SQL Server에서 사용하였던

커버된 인덱스의 기능을 대체하기 위해 사용됩니다.

인덱스에 포함되는 컬럼을 지정하는 기능의 부가효과로 인덱스 키에 대한 크기의 제약이

확장됩니다. SQL Server 2000에서는 인덱스 키값의 최대 크기가 900 바이트로 제한되어

있었습니다. SQL Server 2005의 인덱스에 포함된 컬럼기능을 사용하면, 인덱스의 리프

노드에, 최대 행 크기과 동일하게, 8060 바이트까지 저장할 수 있습니다.

분할 인덱스 (Partition)
분할 인덱스는 SQL Server 2005의 인덱스 관련 주요 개선 중 하나입니다. 분할 인덱스이란,

인덱스를 하나 이상으로 구성된 파일그룹에 분산하여 저장하는 기능입니다. 인덱스 분할은

주로 분할된 테이블에 생성됩니다. 분할 인덱스는 데이터를 여러 개의 파일에 분산시키기

때문에 성능을 향상시킬 수 있고, 경합현상을 줄여줄 수 있으며, IO 작업이 병렬로 처리될 수

있는 가능성을 높여주는 역할을 합니다.

분할 인덱스에 대한 좀 더 자세한 정보는 SQL Server 2005 온라인 도움말의“분할 테이블 및

인덱스의 개념”부분을 참조하십시오.

XML 인덱스
SQL Server 2005 에서는 XML 데이터형을 지원합니다. CREATE PRIMARY XML INDEX와 CREATE XML INDEX T-SQL 명령을 사용하여 XML 데이터형 컬럼에 XML 인덱스를 생성할 수

있습니다. XML 인덱스는 XML 데이터형 컬럼에 데이터가 변경되었을 때 부가적인 비용을

발생시키기는 하지만, XML 데이터에 대한 XQuery 작업의 성능을 극적으로 향상시켜 줍니다.

XML 인덱스에 대해서는 몇 가지 제약사항이 있습니다. XML 인덱스에 대한 좀 더 자세한

정보는 SQL Server 2005 온란인 도움말의“XML 데이터 유형 열의 인덱스”부분을 참조하십시오.


인덱스 생성

CREATE INDEX 명령에 대한 설명과 기능에 대해서 살펴보고, 각 기능을 설명하는 예제

문과 코드 예제를 소개합니다.

Create Index 구문
CREATE INDEX 명령은 인덱스를 생성하는 역할을 합니다. SQL Server 2005에서는 새로운

인덱스 관련 기능을 지원하기 위해 CREATE INDEX 구문에 대한 기능이 확장되었습니다.

새로운 인덱스 관련 기능을 사용하기 위해서, SQL Server Management Studio의 개체

탐색기의 새 인덱스 생성 대화상자를 사용할 수 있습니다.

[참고]
CREATE INDEX 명령에서 유지 관리 마법사에서 사용하는 PAD_INDEX, SORT_IN_TEMPdb, IGNORE_DUP_KEY, ONLINE 옵션도 지정하여 사용할 수 있습니다.


CREATE [UNIQUE][CLUSTERED | NONCLUSTERED] INDEX index_name
ON [{database_name.[schema_name]. | schema_name.}]
{table_or_view_name}(column [ASC | DESC][,...n])
[INCLUDE (column_name[,...n])]
[WITH(<relational_index_option>[,...n])]
[ON {partition_scheme_name(column_name[,...n])
| filegroup_name | DEFAULT}]
<relation_index_option>::=
{ PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| IGNORE_DUP_KEY = {ON | OFF}
| STATISTICS_NO_RECOMPUTE = {ON | OFF} | DROP_EXISTING = {ON | OFF}
| ONLINE = {ON | OFF}
| ALLOW_ROW_LOCKS = {ON | OFF}
| ALLOW_PAGE_LOCKS = {ON | OFF}
| MAXDOP = number_of_processors }


온라인 인덱스 작업
CREATE INDEX 구문의 WITH 절에 ONLINE=ON 옵션을 지정하면, 인덱스 생성작업을 다른 DML 작업과 병행해서 수행할 수 있습니다.

[참고]
xml, text, ntext, image, varchar(max), nvarchar(max), varbinary(max), filestream 데이터형이

사용된 테이블의 경우에는 온라인 인덱스 작업을 수행할 수 없습니다.


CREATE INDEX IX_Employee_ManagerID
ON HumanResources.Employee (ManagerID)
WITH (ONLINE=ON)

 

병렬처리수준 지정
기본값으로, 데이터베이스 엔진에서는 최대병렬처리수준(MAXDOP) 구성옵션의 값을

기준으로 인덱스를 생성하는 작업에서 사용할 최대 CPU 수를 결정하게 됩니다. 필요에 따라,

인덱스 작업과 병행하여 처리되는 다른 작업에 영향을 최소화하기 위해, 별도의 MAXDOP

옵션을 지정하여, 인덱스 작업에서 사용하는 CPU를 제한할 수 있습니다.

MAXDOP 옵션을 지정하면, 서버에 구성되어 있는 최대병렬처리수준(MAXDOP) 옵션값보다

우선하여 적용됩니다. MAXDOP =0이라고 지정하면, 현재 데이터베이스 엔진의 작업부하를

고려한 상태에서, 사용할 수 있는 전체 CPU를 인덱스 작업에 사용합니다.

[참고]
ONLINE 옵션과 함께, 인덱스 생성, 변경, 삭제 작업에만 적용되는 별도의 MAXDOP 옵션을

설정할 수 있습니다. 쿼리 최적화기에서는 해당 인덱스를 참조하는 쿼리를 최적화하기

위해서 기존에 설정된 최대병렬처리수준 (MAXDOP) 구성을 사용합니다.

CREATE INDEX IX_Employee_ManagerID
ON HumanResources.Employee (ManagerID)
WITH (ONLINE=ON, MAXDOP = 3)


인덱스에 포함할 컬럼 지정
인덱스 리프(leaf) 노드에 포함될 컬럼을 추가로 지정할 수 있습니다.
커버된 인덱스를 생성하기 위해, 예제와 같이, AddressLine1 컬럼과, AddressLine2 컬럼을 AddressID컬럼에 대한 인덱스에 추가할 수 있습니다. 하지만, 해당 테이블에 대한 INSERT, UPDATE, DELETE 작업을 수행할 때는, 인덱스에 중복된 데이터를 유지하기 위해 추가

오버헤드가 발생하게 됩니다.

text, ntext, image 데이터형 컬럼에 대해서는 사용할 수 없습니다.

CREATE INDEX IX_AddressDetails
ON Person.Address (AddressID)
INCLUDE (AddressLine1, AddressLine2)


인덱스 분할(Partition)
CREATE INDEX 명령에 파일그룹과 파티션 스키마를 지정하여 분할 인덱스를 정의할 수

있습니다.

인덱스가 설정된 테이블은 분할된 상태이고, 인덱스 분할을 지정하지 않은 상태라면, 해당

인덱스는 테이블이 사용하고 있는 파티션 스키마와 동일한 위치에 생성됩니다.

CREATE INDEX IX_CustomerDetails
ON Sales.Customer(CustomerID)
ON Sales.CustomerScheme(CustomerID)


XML 인덱스 생성
CREATE [PRIMARY] XML INDEX 명령을 사용하여 XML 인덱스를 생성할 수 있습니다.
테이블에 XML 인덱스를 생성하기 전에 해당 테이블에 반드시 클러스터형 인덱스를 생성해야 합니다. 부가(secondary) XML 인덱스를 생성하기 전에, 해당 XML 컬럼에 기본(primary)

XML 인덱스가 생성되어 있어야 합니다. XML 인덱스를 생성할 때는, ONLINE 옵션을 사용할

수 없습니다.

CREATE [PRIMARY] XML INDEX index_name
ON [{database_name.[schema_name]. | schema_name.}]table_name
(xml_column_name)
[ USING XML INDEX xml_index_name
[ FOR { VALUE | PATH } ]
[WITH(<xml_index_option>[,...n])]
<xml_index_option>::=
{ PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| STATISTICS_NO_RECOMPUTE = {ON | OFF}
| DROP_EXISTING = {ON | OFF}
| ALLOW_ROW_LOCKS = {ON | OFF}
| ALLOW_PAGE_LOCKS = {ON | OFF}
| MAXDOP = number_of_processors }


다음과 같은 구문으로 XML 인덱스를 생성할 수 있습니다.

CREATE PRIMARY
XML INDEX IXML_ContactAddContact
ON Person.Contact(AdditionalContactInfo)

인덱스 수정

ALTER INDEX 명령에 대한 설명과 기능에 대해서 살펴보고, 각 기능을 설명하는 예제 구문과

코드 예제를 소개합니다

ALTER INDEX 구문
ALTER INDEX 구문은 인덱스를 재생성하고, 재조직화하고, 비활성화하고, 인덱스 관련 옵션을 변경하기 위해서 사용합니다. SQL Server Management Studio의 개체 탐색기의 인덱스 속성

대화상자를 통해 인덱스에 대한 변경작업을 수행할 수 있습니다.

다음 표는 ALTER INDEX 명령을 사용하여 수행할 수 있는 각 작업에 대한 간략한 설명입니다.

키워드 설명
REBUILD

인덱스를 재생성하면, 테이블에 저장된 데이터의 내부구조에 따라 인덱스를 재정렬하게 되고, 인덱스가 사용하고 있는 디스크 공간을 축소하기 위하여,

인덱스를 삭제했다가 다시 생성합니다.

REORGANIZE 테이블에 저장된 데이터의 논리적인 순서에 따라 인덱스의 리프 페이지를 재정렬합니다.
DISABLE 해당 인덱스를 사용하지 못하도록 하기 위해서 특정 인덱스(클러스터형 인덱스도 포함)를 비활성화합니다.
SET

인덱스를 유지관리하고 접근방법을 지정하기 위해 사용되는 인덱스 옵션을

조정합니다.


ALTER INDEX 명령은 인덱스 분할 정보를 변경하거나, 해당 인덱스에 컬럼을 추가하거나

삭제하기 위해서는 사용할 수 없습니다. 이러한 작업을 수행하기 위해서는 CREATE INDEX

명령을 WITH DROP_EXISTING 옵션과 함께 사용해야 합니다.

 

ALTER INDEX {index_name | ALL}
ON [{database_name.[schema_name]. | schema_name.}]
{table_or_view_name}
{ REBUILD [WITH(<rebuild_index_option>[,...n])]
| REORGANIZE [ WITH( LOB_COMPACTION = {ON | OFF})]
| DISABLE
| SET (<set_index_option>[,...n]) }

<rebuild_index_option>::=
{ PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| IGNORE_DUP_KEY = {ON | OFF}
| STATISTICS_NO_RECOMPUTE = {ON | OFF}
| ONLINE = {ON | OFF}
| ALLOW_ROW_LOCKS = {ON | OFF}
| ALLOW_PAGE_LOCKS = {ON | OFF}
| MAXDOP = number_of_processors }

<set_index_option>::=
{ IGNORE_DUP_KEY = {ON | OFF}
| STATISTICS_NO_RECOMPUTE = {ON | OFF}
| ALLOW_ROW_LOCKS = {ON | OFF}


인덱스 재생성
REBUILD 절을 통해, FILLFACTOR, PAD_INDEX, SORT_IN_TEMPDB, IGNORE_DUP_KEY, STATISTICS_NORECOMPUTE 등의 옵션을 지정하여 인덱스의 구조를 변경할 수 있습니다.

온라인 작업으로 크기가 큰 인덱스(128 익스텐트 이상의 인덱스)를 재생성하게 되면, 데이터

베이스 엔진은 해당 인덱스가 사용하고 있던 기존 디스크 공간을 할당해제하기 전에 새로운

디스크 공간을 할당한 다음, 인덱스관련 데이터를 복사한 다음 재정렬작업을 수행합니다.

반드시 인덱스 재생성 작업을 수행하기 전에는 해당 작업을 수행할 수 있을 만한 충분한

디스크 공간을 확보해 두어야 합니다.

[참고]
클러스터형 인덱스를 재성성한다고 해서 자동으로 모든 비클러스터형 인덱스가 재생성되지는 않습니다. 하지만, ALTER INDEX ALL … REBUILD … 명령을 사용하여 단일 작업으로 특정 테이블에 존재하는 모든 인덱스를 삭제하고 다시 생성할 수 있습니다.

 

REBUILD 절에 ONLINE=ON 옵션을 지정하면, 온라인 작업으로 인덱스에 대한 변경작업을 수행

할 수 있고, MAXDOP 옵션을 지정하면, 별도의 최대병렬처리정도를 지정할 수 있습니다.

ALTER INDEX IX_Employee_ManagerID
ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80)


인덱스 재조직화
인덱스를 재조직화하면, 인덱스를 사용하여 순차적인 행을 대량으로 스캔하는 작업을

수행하는 쿼리의 성능을 향상시킬 수 있습니다. 재조직화된 인덱스는 이미 할당되어 있는

동일한 인덱스 페이지를 재사용하게 되며, 인덱스 재조직화의 결과로 완전히 빈 상태가 된

페이지만 할당해제하게 됩니다. 또한, 인덱스 재조직화작업은 Tempdb에 추가적인 작업

공간을 필요로 하지 않게 되면, 모든 데이터는 기존 인덱스 페이지내에서 재분배됩니다.

REORGANIZE 절에 지정할 수 있는 유일한 옵션은 LOB_COMPACTION으로, 대량개체데이터(image, text, ntext, varchar(max), nvarchar(max), varbinary(max)형 데이터)도 재조직화하도록

설정합니다.

인덱스 재조직화작업은 항상 온라인으로 수행되기 때문에, 병행해서 수행되는 동시성 쿼리나 데이터 변경작업을 차단하지 않습니다. MAXDOP 옵션을 별도로 지정할 수 없으며, 서버에

설정된 최대병렬처리정도(MAXDOP) 옵션도 무시됩니다.

ALTER INDEX IX_Employee_ManagerID
ON HumanResources.Employee
REORGANIZE WIT


인덱스 비활성화
쿼리 최적화기는 SQL 문장에 대한 실행계획을 수립할 때, 비활성화되어 있는 인덱스는

고려의 대상으로 포함시키지 않습니다. 비활성화되어 있는 인덱스에 대한 참조 힌트가 설정된 경우에는 쿼리 최적화기에서 오류를 발생시킵니다. 인덱스가 비활성화되면, 해당 인덱스에

연결된 모든 제약조건과 기본키 및 외래키 제약조건도 함께 비활성화됩니다.

데이터베이스 엔진은 비활성화된 인덱스를 유지보수하지 않습니다. ALTER INDEX…REBUILD… 명령이나 CREATE INDEX… WITH DROP_EXISTING 명령을 사용하여, 인덱스를 다시 활성화할

수 있습니다. ALTER TABLE 명령을 사용하여 비활성화된 제약조건도 반드시 활성화시켜

주어야 합니다.

[참고]
테이블에 대해 클러스터형 인덱스를 비활성화한 경우에는, 테이블이 OFFLINE 상태로

표시됩니다. 모든 어플리케이션에서는 해당 클러스터형 인덱스가 활성화될 때가지는

해당 테이블을 사용할 수 없습니다. 물론, SELECT 쿼리를 수행할 수도 없습니다.

ALTER INDEX IX_Employee_ManagerID
ON HumanResources.Employee
DISABLE


인덱스 옵션 변경
SET 절을 사용하여 인덱스에 사용하는 ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, GNORE_DUP_KEY, STATISTICS_NORECOMPUTE 옵션을 변경할 수 있습니다.


 

ALTER INDEX IX_Employee_ManagerID
ON HumanResources.Employee
SET (ALLOW_PAGE_LOCKS = ON)


인덱스 삭제

DROP INDEX 명령을 사용하여 테이블에 설정된 인덱스를 삭제할 수 있습니다. DROP INDEX

명령은 XML 인덱스를 포함한 전체 인덱스에 대해서 사용될 수 있습니다. SQL Server Management Stuoio의 개체 편집기의 해당 인덱스에 대한 단축메뉴에서 삭제 옵션을

용하여, 인덱스를 삭제할 수도 있습니다. DROP INDEX 명령에 대한 설명과 기능에 대해서

살펴보고, 각 기능을 설명하는 예제 구문과 코드 예제를 소개합니다.

DROP INDEX 구문
DROP INDEX 명령에 대한 옵션은 클러스터형 인덱스를 삭제하는 경우에만 사용할 수

있습니다. MOVETO 옵션은 인덱스를 삭제한 다음, 데이터를 저장할 위치를 지정하기

위해서 사용합니다.

DROP INDEX index_name
ON [schema_name.]{table_or_view_name}
[ WITH (<drop_index_option>[,...n]) ]
<drop_index_option>::=
{ ONLINE = {ON | OFF}
| MAXDOP = number_of_processors
| MOVETO { partition_scheme_name(column_name[,...n])] |
filegroup_name | DEFAULT }


인덱스 삭제
SQL Server 2005에서는 DROP INDEX 명령에 대한 구문이 변경되었습니다. 이전 버전 SQL

Server에서 사용하던 마침표(.) 형식의 구문대신, ON 절을 사용하여 삭제할 인덱스가 설정된

테이블을 지정합니다.

비활성화되어 있는 비클러스터형 인덱스도 삭제할 수 있습니다.

DROP INDEX IX_Employee_ManagerID
ON HumanResources.Employee

[참고]
이전 버전에서 사용하던 구문이 대부분 그대로 사용할 수 있기는 하지만(물론, XML

인덱스에 대해서는 예외), 신규 개발 프로젝트에서는 이전 버전에서 사용하던 구문을

사용하지 않는 것이 바람직합니다


클러스터형 인덱스를 위한 옵션지정
DROP INDEX 명령은 클러스터형 인덱스를 삭제하는 경우에 대해서만, MAXDOP 옵션과

ONLINE 옵션을 지원합니다.

비활성화되어 있는 클러스터형 인덱스는 삭제할 수 없으며, 대형개체데이터(LOB)가 포함된

클러스터형 인덱스의 경우에는 ONLINE 옵션을 사용할 수 없습니다.

DROP INDEX IX_Cluster
ON Sales.Customer
WITH (ONLINE = ON, MAXDOP = 3)


클러스터형 인덱스 데이터 위치재조정
클러스터형 인덱스를 삭제하는 경우에는, MOVETO 옵션을 사용하여 리프 페이지에 저장된

데이터 행을 저장할 위치를 지정할 수 있습니다. 클러스터형 인덱스가 삭제되면, 데이터는

지정된 위치에 테이블로 다시 생성됩니다. 데이터를 저장할 대상 위치로 파티션 스키마나

파일그룹을 지정할 수도 있습니다.

DROP INDEX IX_Cluster
ON Sales.Customer
WITH (MOVE TO NewCustomerScheme)


인덱스 조각화 관리

초기에는, 인덱스에 포함된 데이터는 기존 테이블의 행 순서에 따라 잘 정렬되어 저장됩니다. SQL Server 2005 데이터베이스 엔진에서는 테이블에 행이 삽입, 변경, 삭제될 때마다 자동적

으로 인덱스를 변경합니다. 하지만, 테이블의 데이터가 변경되는 것처럼, 데이터베이스

파일에 저장된 물리적인 저장위치가 변경되는 것은 아닙니다. 테이블의 데이터가 변경되면,

인덱스가 실제 테이블의 물리적인 행 순서와 일치하지 않으며, 결국 성능저하를 일으키는

원인이 될 수 있습니다. 이러한 현상을 조각화라고 합니다. 결과적으로, 조각화를 제거하기

위해 인덱스에 대해 재조직화 또는 재생성작업을 수행해야 합니다.

재조직화(defrag) vs 재생성(rebuild)
인덱스의 조각화정도에 따라 인덱스를 재조직화할 것인지, 재생성할 것인지를 결정하게 됩니다. 클러스터형 인덱스 또는 비클러스터형 인덱스를 재조직화하게 되면, 인덱스의 리프노드의 조각화를 제거하고, 인덱스에 설정된 채우기비율(Fillfactor)에 따라 내부노드의 정보를

정리하게 됩니다. 이러한 조각모음 작업결과, 그동안 행 삭제 및 변경으로 인해 발생한

빈공간을 제거하기 때문에, 인덱스는 그만큼 더 작게 축소되며, 실제 테이블과 동일한 순서로

리프 노드가 재정렬됩니다. 인덱스 재조직화작업은 테이블에 포함된 대량개체컬럼(LOB)도

정리하게 됩니다.

인덱스의 조각화정도가 매우 극심한 경우에는 인덱스를 재생성하는 것이 바람직합니다.

인덱스 재생성작업은 인덱스를 삭제했다가 다시 생성합니다. 인덱스를 재생성하는 경우,

채우 기비율과 같은 인덱스 관련 설정을 변경할 수 있습니다. 인덱스 재조직화의 경우는,

이러한 인덱스 관련 설정을 변경할 수 없습니다.

조각화 정보 수집
인덱스를 재생성하거나 재조직화하는 작업은 상당한 비용과 자원을 소모하는 작업입니다.
인덱스 재생성작업이 인덱스 재조직화 작업에 비해 더 높은 비용을 소모합니다 그러므로,

인덱스의 조각화정보를 조사하여 반드시 필요한 경우에만 인덱스 재생성작업을 수행하는

것이 바람직합니다. sys.dm_db_index_physical_stats 함수를 사용하여,

인덱스의 조각화정도를 수집할 수 있습니다.

다음 예제는 현재 데이터베이스내의 모든 테이블에 대해서 인덱스 조각화정보를 수집하기

위해, sys.dm_db_index_physical_stats 함수를 사용하는 방법을 나타내고 있습니다.

SELECT TableName, IndexName, AvgFragmentation
FROM sys.dm_db_index_physical_stats(DEFAULT,‘ *’, DEFAULT,‘ DETAILED’)


sys.dm_db_index_physical_stats 함수에 전달되는 매개변수는 순서에 따라 다음과 같습니다.

    @TableName. 정보를 수집할 테이블을 지정합니다. 특정 테이블의 이름을 지정할 수도

       있고, DEFAULT, NULL 을 지정할 수도 있습니다. DEFAULT 또는 NULL이 지정되면,

       sys.dm_db_index_physical_stats 함수는 현재 데이터베이스의 모든 테이블에 대한 정보를

       반환합니다.

    @IndexName. 정보를 수집할 인덱스를 지정합니다. 특정 인덱스의 이름을 지정할 수도

       있고, DEFAULT, NULL,‘ *’를 지정할 수도 있습니다. DEFAULT와 NULL이 지정되면, 기본

       테이블에 대한 정보(또는 클러스터형 인덱스에 대한 정보)만 반환합니다.
       ‘*’이 지정되면, 테이블에 존재하는 모든 인덱스에 대한 정보가 반환됩니다.

    @PartitionId. 인덱스의 분할파티션 ID 번호를 지정합니다. DEFAULE, NULL,0 으로 지정

       되면 모든 분할파티션에 대한 정보가 반환됩니다.

    @Mode. 요청된 정보를 수집하기 위한 스캔 수준을 지정합니다. LIMITED 옵션을 사용할

       것을 권고하며, 부모-수준의 페이지만 읽기 때문에 매우 빠르게 작업을 수행할 수

       있습니다. SAMPLED 옵션이 지정되면, 부모-수준의 페이지와 샘플링된 리프 페이지를

       읽게 되며, DETAILED 옵션이 지정되면, 부모-수준의 페이지와 모든 리프 페이지를 읽게

       됩니다.


sys.dm_db_index_physical_stats 함수에 대한 좀 더 자세한 정보는, SQL Server 2005 온라인

도움말의 T-SQL 참조 부분을 참조하십시오.


* 출처 : DB 가이드 넷(http://www.dbguide.net/sql2005_admin_guide/sql2005_admin_guide_06.jsp)

+ Recent posts