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

매개변수 값의 변경과 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 명령을 실행하면 좋은 성능을 얻을 수 있다.

 

2013-07-17 / 강성욱 / http://sqlmvp.kr