컴퓨토피아

SQL Server for Developer: 관리자를 위한 튜닝 가이드

인덱스

차주언 | SQL 컨설턴트

인덱스

수칙1.적절한 인덱스가 걸려 있는가?

적절한 인덱스가 걸려있는지 인덱스 튜닝마법사로 확인할 수 있습니다. 또는 CTRL + K로 실행계획을 관찰 해도 됩니다.

인덱스를 만들어야 하는 장소
가. 참조키
나. 참조키가 아니더라도 join에 빈번히 사용되는 경우
다. select절에 자주 사용되는 칼럼
라. where,group by,order by절에 자주 사용되는 곳

수칙2.인덱스 튜닝마법사로 점검했는가?

인덱스란 책의 목차나 책 뒤쪽의 찾아보기와 매우 유사합니다. 예를 들면 40 메가의 데이터중 필요한 내용을 찾고자 한다면 40 메가를 모두 검색해야하지만 인덱스를 만들어 둔다면 인덱스만 읽음으로써 보다 적은 리소스에 사용만으로, 필요한 내용 검색을 끝낼 수 있습니다.

[따라하기 적절한 인덱스 자동 만들기]
1.다음과 같은 테이블을 예제 테이블을 pubs 데이터베이스에 만들어서 가상 Data 10 만개를 입력해 봅시다.

2.10 만개의 데이터가 입력됐으면 select 쿼리로 실험을 시작하도록 합니다. 먼저 총 I/O 가 얼마가 일어나는지 또 실행 계획은 무엇인지 알아보겠습니다. 단축키 CTRL + K 를 클릭하고 다음의 설정을 한후 쿼리를 실행합니다.

3. 데이터 한 개를 가져오기위해 테이블 전체를 검색하고 Data 페이지 6250 페이지를 읽었군요. 6250 페이지는 6250 * 8 다시말해 약 50 메가를 검색하고 있습니다.

4. 여기에서 인덱스를 만들어 보겠습니다.

create index idx on tb_test(id)

5.다시 쿼리를 실행하면 I/O 는 몇페이지가 나옵니까?

select * from tb_test where id = 1

6.인덱스를 제거하고 이번에는 자동 인덱스 추천을 한번 해보겠습니다.

drop index tb_test.idx

7.select 쿼리를 드래그 하여 선택 후 쿼리 메뉴에서 인덱스튜닝마법사(CTRL+I)를 선택합니다.

08.SQL쿼리분석기 선택 체크를 선택합니다.

09. 튜닝할 테이블만을 선택합니다.

10. 다음과 같은 인덱스가 권장 되었습니다.

11. 다음과 같이 변경 내용 적용과 함께 인덱스를 만드는데 사용한 소스도 저장합니다.

12. 마침을 클릭합니다. 인덱스도 훌륭히 적용된 것을 알 수 있습니다. 생성된 쿼리도 열어서 확인합니다.

13. 인덱스 결과도 확인합니다. 응용으로는 한번에 많은 쿼리를 선택한 후 인덱스 튜닝 마법사를 실행 할 수 있다는 것입니다.

sp_helpindex tb_test

[관련링크]
http://support.microsoft.com/default.aspx?scid=kb;ko;271509

위의 파일에 오류가 있는데 수정한 것은 다음과 같습니다.

use master
go

if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss80') and
sysstat & 0xf = 4)
drop procedure dbo.sp_blocker_pss80
GO

create proc sp_blocker_pss80 (@fast int = 0)
as
set nocount on
declare @spid varchar(6), @uid varchar(6), @blocked varchar(6)
declare @tmpchar varchar(255)
declare @time datetime

select @time = getdate()

declare @probclients table(spid smallint, blocked smallint, waittype binary(2),
primary key (blocked,spid))
insert @probclients select spid, blocked, waittype from sysprocesses where blocked!=0
or waittype != 0x0000
if exists (select spid from @probclients)
begin
select @tmpchar='Start time: ' + convert(varchar(26), @time, 113)
print @tmpchar

insert @probclients select blocked, 0, 0x0000 from @probclients
where blocked not in (select spid from @probclients) and blocked != 0

print ' '
if (@fast = 1)
begin
select spid, status, blocked, open_tran, waitresource, waittype,
waittime, cmd, lastwaittype, cpu, physical_io,
memusage,last_batch=convert(varchar(26), last_batch,113),
login_time=convert(varchar(26), login_time,113), net_address,
net_library,dbid, ecid, kpid, hostname,hostprocess,
loginame,program_name, nt_domain, nt_username, uid, sid
from master..sysprocesses
where blocked!=0 or waittype != 0x0000
or spid in (select blocked from @probclients where blocked != 0)
or spid in (select spid from @probclients where blocked != 0)

select spid = convert (smallint, req_spid),
ecid = convert (smallint, req_ecid),
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
Type = case rsc_type when 1 then 'NUL'
when 2 then 'DB'
when 3 then 'FIL'
when 4 then 'IDX'
when 5 then 'TAB'
when 6 then 'PAG'
when 7 then 'KEY'
when 8 then 'EXT'
when 9 then 'RID'
when 10 then 'APP' end,
Resource = substring (rsc_text, 1, 16),
Mode = case req_mode + 1 when 1 then NULL
when 2 then 'Sch-S'
when 3 then 'Sch-M'
when 4 then 'S'
when 5 then 'U'
when 6 then 'X'
when 7 then 'IS'
when 8 then 'IU'
when 9 then 'IX'
when 10 then 'SIU'
when 11 then 'SIX'
when 12 then 'UIX'
when 13 then 'BU'
when 14 then 'RangeS-S'
when 15 then 'RangeS-U'
when 16 then 'RangeIn-Null'
when 17 then 'RangeIn-S'
when 18 then 'RangeIn-U'
when 19 then 'RangeIn-X'
when 20 then 'RangeX-S'
when 21 then 'RangeX-U'
when 22 then 'RangeX-X'end,
Status = case req_status when 1 then 'GRANT'
when 2 then 'CNVT'
when 3 then 'WAIT' end
,req_transactionID As TransID,
req_transactionUOW As TransUOW
from master.dbo.syslockinfo s,
@probclients p
where p.spid = s.req_spid

end -- fast set

else
begin -- Fast not set
select spid, status, blocked, open_tran, waitresource, waittype,
waittime, cmd, lastwaittype, cpu, physical_io,
memusage,last_batch=convert(varchar(26), last_batch,113),
login_time=convert(varchar(26), login_time,113), net_address,
net_library,dbid, ecid, kpid, hostname,hostprocess,
loginame,program_name, nt_domain, nt_username, uid, sid
from master..sysprocesses

print ''
print 'SPIDs at the head of blocking chains'
select spid from @probclients
where blocked = 0 and spid in (select blocked from @probclients where spid !=
0)

print ''

select spid = convert (smallint, req_spid),
ecid = convert (smallint, req_ecid),
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
Type = case rsc_type when 1 then 'NUL'
when 2 then 'DB'
when 3 then 'FIL'
when 4 then 'IDX'
when 5 then 'TAB'
when 6 then 'PAG'
when 7 then 'KEY'
when 8 then 'EXT'
when 9 then 'RID'
when 10 then 'APP' end,
Resource = substring (rsc_text, 1, 16),
Mode = case req_mode + 1 when 1 then NULL
when 2 then 'Sch-S'
when 3 then 'Sch-M'
when 4 then 'S'
when 5 then 'U'
when 6 then 'X'
when 7 then 'IS'
when 8 then 'IU'
when 9 then 'IX'
when 10 then 'SIU'
when 11 then 'SIX'
when 12 then 'UIX'
when 13 then 'BU'
when 14 then 'RangeS-S'
when 15 then 'RangeS-U'
when 16 then 'RangeIn-Null'
when 17 then 'RangeIn-S'
when 18 then 'RangeIn-U'
when 19 then 'RangeIn-X'
when 20 then 'RangeX-S'
when 21 then 'RangeX-U'
when 22 then 'RangeX-X'end,
Status = case req_status when 1 then 'GRANT'
when 2 then 'CNVT'
when 3 then 'WAIT' end
,req_transactionID As TransID,
req_transactionUOW As TransUOW
from master.dbo.syslockinfo
end -- Fast not set

dbcc traceon(3604)
Print ''
Print ''
Print '*********************************************************************'
Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
Print 'Print out DBCC PSS info only for SPIDs at the head of blocking chains'
Print '*********************************************************************'

declare ibuffer cursor fast_forward for
select cast (spid as varchar(6)) as spid, cast (blocked as varchar(6)) as blocked
from @probclients
where (spid <> @@spid) and (blocked!=0 or waittype != 0x0000
or spid in (select blocked from @probclients where blocked != 0))
open ibuffer
fetch next from ibuffer into @spid, @blocked
while (@@fetch_status != -1)
begin
print ''
print ''
print 'DBCC INPUTBUFFER FOR SPID ' + @spid
exec ('dbcc inputbuffer (' + @spid + ')')
print ''
if (@blocked = '0')
-- if DBCC PSS is not required, comment the line above, remove the
-- comment on the line below and run the stored procedure in fast
-- mode
-- if (@blocked = '0' and @fast = 0)
begin
print 'DBCC PSS FOR SPID ' + @spid
exec ('dbcc pss (0, ' + @spid +')')
print ''
print ''
end
fetch next from ibuffer into @spid, @blocked
end
deallocate ibuffer

if datediff(millisecond, @time, getdate()) > 1000
begin
select @tmpchar='End time: ' + convert(varchar(26), getdate(), 113)
print @tmpchar
end

dbcc traceoff(3604)
end -- All
go

[참고] 그러나 현업에선 프로 파일러가 UI부하 때문에 사용이 망설여 집니다. 그래서 proc 로 제작해서 사용하는 것을 권장합니다. 위의 시스템 프로시저를 제작한 후 사용해 봅니다. 직접 아래 쿼리를 수행하거나 결과를 파일로 만들어 저장할 수 있습니다.

-- checkblk.sql

DBCC TRACEON (3604)
GO
WHILE 1=1
BEGIN
-- EXEC sp_blocker_pss80
-- Or for fast mode
EXEC sp_blocker_pss80 1
WAITFOR DELAY '00:00:15'
END
GO

실제 위의 파일을 저장한 곳에서 다음의 명령 프롬프트에서 이렇게 수행하면 됩니다.

osql -E -icheckblk.sql -ocheckblk.out -w2000

그 다음은 결과만 분석하면 됩니다. 락에 대기중인 쿼리가 딱 나와있습니다.

수칙3.상황 발생시 인덱스 채우기 비율을 조정하는가?

인덱스는 검색할 때 속도는 무척 우수합니다. 그러나 insert작업시 인덱스의 많은 변화가 요구되는 페이지 분할(Page Split) 발생할 수가 있어서 아예 여유공간을 비워두는 것이 좋습니다.

[따라하기]
01. 데이터베이스 유지관리 마법사 노드에서 마우스 오른쪽 클릭 후 새 유지관리 계획 마법사를 실행합니다.

02.인덱스를 정돈할 데이터베이스를 선택합니다

03.다음과 같이 인덱스 비우기 비율을 적당량(상황에따라)을 선택한 후 다음버튼을 클릭합니다.

04. 정기적으로 재정비 해주면 좋습니다.

제공 : DB포탈사이트 DBguide.net