컴퓨토피아

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

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

잠금

차주언 | SQL 컨설턴트

잠금

수칙1과2.시간이 너무오래걸리는 트랜잭션이나 데드락을 모니터링 해서 뽑아내는가?

위의 두가지 사항은 손쉽게 프로필러로 뽑아낼 수 있습니다. 다음의 따라하기는 단순한 모니터링일뿐 근원적인 해결책은 아 닙니다. 해결은 도움말 잠금편을 읽어보고 해결해야만 합니다. 또는 외부에 해결을 의뢰하기 위해 파일로 저장할 수도 있습 니다.

[따라하기]
01.데드락 유발쿼리 입니다 쿼리창을 열어 각각의 쿼리를 거의 동시에 수행해 봅시다. 물론 아래의 데드락 해결방법은 쿼리 실행 순서를 둘다 동일하게 tb_test,tb_test2순으로 작성하면 아무런 문제가 없어집니다.

02.프로필러를 실행 시킵니다. 새 추적을 클릭합니다

03. 데드락을 감시하고자 하는 서버에 접속합니다.

04. 데드락을 검사하려면 이름을 정하고

05. 이벤트텝에서 잠금 항목의 데드락 및 체인을 선택합니다.

06. 데드락으로 표시되는부분은 붉게 표시되어 나타납니다. 개발자와 상담하여 데드락을 유발시키는 쿼리를 라이브락으로 변경 시켜주어야합니다.

07.기다리면 시스템에서 발생하는 쿼리들중 데드락인 부분은 붉게 표시되어 나타납니다. 추가로 앞서 제작해 두었던 sp_blocker_pss80 도 꼭 실험을 해보도록 해야합니다.

그렇다면 보다 자세한 잠금관련 공부를 해보겠습니다

잠금관련 권고사항
가. 트랜잭션은 가능한 짧게 한다
나. 데드락을 피하기 위해
  A. 같은방향으로 트랜잭션을 진행합니다.
  B. 잠금수준을 올려줄 수도 있어야 합니다
다. 잠금수준을 내려서 불필요한 잠금을 없애야합니다(read uncommitted)
라. 트리거를 사용하지 않습니다.
마. 대규모 데이터 변경시에만 커서를 사용합니다.

[따라하기- 트랜잭션은 왜 짧아야 하는가?]
트랜잭션이 길어지면 잠금이 길어지고 그만큼 다른 작업도 지연된다 다음 대표적인 잠금대기 실험을 해보겠습니다.

1.다음과 같이 창을 두개 만듭니다. CTRL+N을 클릭하여 새창을 만든 후 메뉴에서 창 > 새로 바둑판식 배열을 선택합니다.

2.왼쪽창에선 트랜잭션 중간에 멈추고 오른쪽에서 그 트랜잭션이 잠그는 부분과 충돌하는 장면을 연출해 봅시다.

3.왼쪽창을 다 실행한 다음 오른쪽을 실행합니다.(price는 기존의 값이 아닌값이면 됩니다)

3.실행결과 오른쪽 쿼리는 무한대기가 걸린다. 언제까지 무한대기냐면 왼쪽에서 commit tran (rollback tran)할때까지 입니 다. 즉 트랜잭션이 끝날때까지 입니다. 이를 라이브락 이라합니다.

4.성공적으로 오른쪽 쿼리가 실행됩니다. 따라서 트랜잭션은 빠르면 빠를 수 록 좋습니다.

[따라하기 - 잠금수준을 낮춰서 잠금 충돌을 회피합시다]
1.왼쪽 쿼리를 commit tran을 하지 않은 상태에서 다음과 같은 오른쪽 쿼리를 실행합니다.

2.실제 데이터가 아닌 버퍼에 있는 값을 읽어오므로 잠금과는 상관이 없는 readuncommitted를 사용한 예제입니다.

[따라하기 - 데드락을 피하는 노력들!]
1.실험에 사용할 테이블 두개를 만듭니다.

2.데드락 중에서 순환데드락을 구현해 보겠습니다 순환 데드락이란 서로가 서로 업데이트할 내용을 막고 있는 형태입니다.

3.위의 상황은 업데이트 잠금 때문에 다른 트랜잭션의 업데이트가 실행되지 못하는 것 입니다 해결하려면 다음과 같이 해줘 야 합니다. 같은 방향으로 트랜잭션을 진행합니다.

[따라하기 - 변환 데드락]
1.같은 방법으로 이번엔 잠금수준을 변경함으로써 데드락을 해소해 보겠습니다. 우선 단순히 양쪽쿼리는 select 후 update 를 시도합니다. 여기서 repeatableread를 한 이유는 select(공유)잠금이 트랜잭션 동안 유지되게 하기 위해서입니다.

2.select 와 공유는 동시에 되지 않습니다. 차라리 select 할때 다른 트랜잭션도 select못하도록 잠금 수준을 향상 시킵니다 .

데드락의 대표예 두가지를 성공적으로 해결했습니다. 결론을 다시 반복하자면 순환 데드락의 경우는 트랜잭션 방향을 일방 통행으로 변환데드락은 잠금 수준을 조정함으로써 해결 해야합니다.

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