데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

매개변수 값의 변경과 SQL 서버 성능 저하

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

매개변수 값의 변경과 그에 따른 SQL Server의 성능문제에 대해서 살펴 본다.

 

해당 내용은 CSS SQL Server Engineer 팀블로그에 기재된 내용으로 원문을 참고로 하여 필자가 이해한 내용을 바탕으로 정리 하였으며 번역의 오류 및 기술적인 오류가 있을 수 있음을 미리 인지 한다.

 

매개변수 스니핑은 이미 많은 사용자들에게 알려져 있다. 하지만 매개변수 스니핑이 정확하지 않을 때가 있다. 예를 들어 사용자 프로시저 안에서 매개변수의 값을 변경하는 경우 SQL Server는 알 수가 없다. 따라서 프로시저가 처음에 컴파일 되면서 카디널리티를 예측한 매개변수의 값을 사용하여 잘못된 예측을 할 수 있다.

 

아래 예제 스크립트를 이용하여 확인해 보자.

[기초 데이터 생성]

use tempdb

go

if object_id ('[Check]') is not null drop table [Check]

go

if object_id ('[Batch]') is not null drop table [Batch]

go

create table Batch (BatchID int identity primary key, BatchType tinyint)

go

create table [Check] (CheckID int identity primary key, BatchID int references Batch (BatchID))

go

 

set nocount on

 

declare @i int = 0, @j int = 0

begin tran

while @i < 500

begin

insert into Batch values (1)

declare @batchid int = @@identity

set @j = cast (RAND() * 1000 as int)

 

declare @k int = 0

while @k < @j

begin

insert into [Check] (BatchID) values (@batchid)

set @k = @k + 1

end

set @i = @i + 1

end

commit tran

 

go

create index ix_check_batchid on [check] (BatchID)

go

 

 

 

 

if object_id ('p_test') is not null drop procedure p_test

go

create procedure p_test @BatchID int output

as

set nocount on

insert into [Batch] (BatchType) values (1)

select @BatchID = @@IDENTITY

--insert some 200 fake values

insert into [Check] (BatchID) select top 200 @BatchID from [Check]

--now select

select * from [Check] where BatchID = @BatchID

 

go

 

 

[상황1- 매개변수 변경 후 잘 못 된 예측]

여기서 주목 해야 할 부분은 프로시저 p_test 내의 @BatchID 라는 매개변수이다. 이 프로시저의 목적은 Batch 테이블에서 얻은 ID 값을 다른 테이블에 삽입하는 것이다.

여기서 문제는 SQL Server가 select * from [Check] where BatchID = @BatchID

구문에서 BatchID = @BatchID 조건을 추정할 수 없다는 것이다.

 

@BatchID는 출력 매개변수로 선언되고 [exec p_test @Batch ouput] 프로시저를 호출 하였을 때 컴파일된 @BatchID 값은 NULL이 된다. 즉 카디널리티는 NULL 값을 예측하여 사용하지만 나중에 @BatchID 값은 ID 값으로 변경된다.

insert into [Check] (BatchID) select top 200 @BatchID from [Check] 구문에서 우리는 SELECT의 200행의 결과물이 산출되는 것을 알 수 있지만 SQL Server는 매개변수 변경을 알 수 없기 때문에 잘못된 값을 예측 한다. 이는 다른 테이블과 조인되는 환경에서는 더 큰 성능상의 문제를 발생 시킬 수 있다.

 

P_test 프로시저를 실행해 보면 실제 200 행수를 검색하여 입력함에도 불구하고 SQL Server는 1행이라고 예측 한 값을 사용한다.

set statistics profile on

go

declare @batchid int

exec p_test @Batchid output

select @batchid

go

set statistics profile off

 

 

 

[상황2 – 옵션 Recompile 사용, 통계 업데이트]

기존의 p_test 프로시저에서 @BatchID 값이 부정확하기 때문에 우리는 프로시저가 실행 할 때마다 옵션을 사용하여 강제로 재컴파일 하도록 하였다.

if object_id ('p_test2') is not null drop procedure p_test2

go

create procedure p_test2 @BatchID int=null output

as

set nocount on

insert into [Batch] (BatchType) values (1)

select @BatchID = @@IDENTITY

insert into [Check] (BatchID) select top 200 @BatchID from [Check]

select * from [Check] where BatchID = @BatchID option (recompile)

go

 

set statistics profile on

go

declare @batchid int

exec p_test2 @Batchid output

select @batchid

go

set statistics profile off

 

 

결과는 추정치의 값은 변경되지 않았다. 실제 행은 200이지만 예측 값은 1이다. 재컴파일을 하였는데에도 예측을 잘못 하는 것일까?

 

그 이유는 CHECK 테이블의 Batchid의 값이 오름차순이기 때문에 통계 업데이트를 하지 않아 BATCHID의 값 일부가 통계에 반영이 되지 않았기 때문이다. (통계 업데이트의 경우는 테이블의 데이터 ROW수에 따라 다르므로 테스트 결과가 달라질 수 있다.)

 

이럴 때 해결 방법은 추적 플래그 2389, 2390 을 사용하여 통계 업데이트를 트리거 하는 것이다.

dbcc traceon (2389,2390,-1)

 

set statistics profile on

go

declare @batchid int

exec p_test2 @Batchid output

select @batchid

go

set statistics profile off

 

 

 

[상황 3 – UNKNOW 사용]

자주 통계 없데이트를 하는 것은 시스템에 오버헤드를 발생 시킬 수 있다. UNKNOW 을 사용하여 최적화 하는 방법을 알아 보자. UNKNOW의 경우에는 단순히 카디널리티에 대한 평균 밀도를 선택 한다. 데이터가 균일하게 분포되어 있는 경우 이 방법이 잘 작동한다.

 

프로시저 p_test3를 생성한다.

create procedure p_test3 @BatchID int=null output

as

set nocount on

insert into [Batch] (BatchType) values (1)

select @BatchID = @@IDENTITY

insert into [Check] (BatchID) select top 200 @BatchID from [Check]

select * from [Check] where BatchID = @BatchID option (optimize for (@BatchID unknown))

go

 

set statistics profile on

go

declare @batchid int

exec p_test3 @Batchid output

select @batchid

go

set statistics profile off

 

 

 

SQL Server에서 옵티마이저가 최적의 실행 계획을 사용하기 위해서는 여러가지 데이터를 참고하는데 이때 정확한 예측을 할 수 있도록 SQL Server 명령을 실행하면 좋은 성능을 얻을 수 있다.

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1830 SQL Server 2012 Sp1 설치 이슈 및 해결 jevida(강성욱) 2016.09.30 1578
1829 SSD에서 DBCC CHECKDB 성능 벤치마킹 jevida(강성욱) 2016.09.30 1932
1828 Collation에 따른 ALTER DATABASE 실패 jevida(강성욱) 2016.09.30 1942
1827 LDF 파일이 잘리지 않는 이유 jevida(강성욱) 2016.09.30 2054
1826 Tempdb 경합 확인 및 해결 (Tempdb Contention) jevida(강성욱) 2016.09.30 2220
1825 SQL Server IO 병목 확인과 오해 jevida(강성욱) 2016.09.30 2502
1824 SQL Version에 따른 sp_prepare 정보 반환 jevida(강성욱) 2016.09.30 1716
1823 DReplay 활성 세션 초과 에러 jevida(강성욱) 2016.09.30 1542
1822 저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일 jevida(강성욱) 2016.09.30 1870
1821 NUMA 노드와 추척플래그 8048 jevida(강성욱) 2016.09.30 820
1820 온라인 인덱스 리빌드와 조각화 증가 jevida(강성욱) 2016.09.30 1608
1819 VARCHAR(MAX) and NTEXT 쿼리 성능 jevida(강성욱) 2016.09.30 4476
1818 CPU 리소스 상태에 따른 병렬 처리 제한 jevida(강성욱) 2016.09.30 1077
» 매개변수 값의 변경과 SQL 서버 성능 저하 jevida(강성욱) 2016.09.30 1271
1816 IN 절 사용시 예기치 못한 액세스 위반과 SQL Server 종료 jevida(강성욱) 2016.09.30 1060
1815 SQL 버전과 CLR (.NET Framework 버전에 따른 오류) jevida(강성욱) 2016.09.30 1189
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1327
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1233
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1636
1811 CLR 사용시 CPU 사용률 증가 현상 jevida(강성욱) 2016.09.29 1761





XE Login