흔히, PLAN를 재사용하여야 하고, RECOMPILE은 나쁘다라고 한다.
과연 정말 그럴까?
How Values with Irregular Selectivity Impact SQL Server Database Performance
(불규칙적인 선택도를 가지는 값이 SQL서버의 성능에 얼만큼 영향을 미칠까? 정도의 해석)http://www.lakesidesql.com/articles/?p=8 (SQL2000기준)
에서 이에 대한 명확한 설명과 예제가 있다.
이것을 SQL 2005에 맞게 다시 재정리했다.
---------------------
테이블 및 인덱스 생성
---------------------
create table Customers (
n int identity primary key,
SSN char(9) not null,
Amount money not null,
Name varchar(128)
)
GO
create index IX_SSN on Customers (SSN)
GO
---------------------
태스트 데이터 입력
---------------------
set nocount on
declare @n int
set @n=100000
loop:
insert into Customers (SSN,Name,Amount)
select convert(varchar,@n),'This is our US customer N'+
convert(varchar,@n),@n*100
set @n=@n-1
if @n>0 goto loop
---------------------
태스트 데이터 입력
---------------------
declare @n int
set @n=10000
loop:
insert into Customers (SSN,Name,Amount)
select 'N/A','This is our foreign customer N'+convert(varchar,@n),@n*30
set @n=@n-1
if @n>0 goto loop
SSN컬럼은 unique social security number를 의미하는 컬럼인데 US(미국)사람은
정상적인 unique social security number를 넣었고, 그외(미국인 아닌나라)는 N/A로
데이터를 채운것이 문제를 발생하는 케이스이다.
SSN컬럼은 인덱스가 걸려있고, 미국인이 아닌 사람들은 사회보장번호를 모두 N/A로
넣게 되어 결국 unique가 되지 않게된 것이다.
쿼리나 프로시저를 수행하기에 앞서
Microsoft SQL Sever Management Studio의 툴바에서 실행계획포함을
클릭한 후
--------------------
set statistics io on
쿼리/프로시저 수행
set statistics io off
--------------------
위와 같이 테스트 하기 바란다.
1) Ad-hoc queries쿼리
1-1)
select min(Amount) from Customers where SSN='345'
결과
--index seek
--논리적읽기 6
1-2)
select min(Amount) from Customers where SSN='N/A'
결과
--scan
--논리적 읽기 881
1-3)
select min(Amount) from Customers with(index=IX_SSN) where SSN='N/A'
결과
--index seek
--논리적 읽기 30658
위의 세가지 예를 보자!
1-2가 1-1보다 논리적 읽기를 많이 했고, scan을 했다.
N/A를 찾는데 881개의 논리페이지를 읽어 계산한 것은, 우리가 넣은 N/A가 넣은 1만건과
비교해볼 때 10%도 안되는 범위안이니 만큼 그리 문제가 되지 않는다.
문제는 index seek를 하지 않고 scan을 했다는 점인데, SQL서버가 자동으로 인덱스를
통해 찾는 것보다 scan을 통해 찾는 것이 더 낫다고 판단한 경우인데 이게 맞는 판단이냐는
것이다.
1-3이 강제로 SSN컬럼에 생성한 인덱스를 힌트를 사용하여 강제한 경우이다.
결과는 참혹했다. 논리페이지를 30658페이지를 뒤져 결과를 내놓았다. 이것은 레코드건수의
약 3배정도를 더 찾은 경우이다.
즉, 시퀄은 똑똑한 넘이라는 것이다!
2) normal procedure
2-1)
-----------------------------------------
CREATE procedure [dbo].[SelectMinAmount]
@ssn char(9)
as
select min(Amount) from Customers where SSN=@ssn
-----------------------------------------
exec SelectMinAmount '345'
exec SelectMinAmount 'N/A'
결과 : 프로시저는 동일 프로지저호출일 경우 맨처음 사용한 Plan을 사용함
scan을 해야 할 exec SelectMinAmount 'N/A'가
exec SelectMinAmount '345' 수행했던 index seek를 이용하므로 해서
오히려 성능저하(논리적 읽기 30024)
2-2)
-----------------------------------------
CREATE procedure [dbo].[SelectMinAmount_Copy]@ssn char(9)
as
select min(Amount) from Customers where SSN=@ssn
-----------------------------------------
exec SelectMinAmount_Copy 'N/A'
exec SelectMinAmount_Copy '345'
결과 : exec SelectMinAmount_Copy 'N/A'가 맨처음 사용한 Plan 을 사용함으로 해서
exec SelectMinAmount_Copy '345' 는 Scan을 사용하게된다.
따라서, index seek를 하지못하고 오히려 성능저하 (논리적 읽기 수 881)
정리하자면, 프로시저는 수행될 때 최초 생성한 Plan을 재사용하게 되는데
2-1에서는 index seek한 plan을 재사용하므로 해서 scan을 타야할 2번째 프로시저는
인덱스 힌트를 강제로 사용한 것과 같이 더 성능이 나빠졌다.
2-2에서는 scan을 한 plan을 재사용하므로 해서 index seek를 타야할 2번째 프로시저는
scan을 타므로 해서 성능이 더 나빠진 경우이다.
★★이제부터 이를 해결하는 법을 소개한다
3) procedure and with recompile
-----------------------------------------
CREATE procedure [dbo].[SelectMinAmount1]
@ssn char(9)
with recompile
as
select min(Amount) from Customers where SSN=@ssn
-----------------------------------------
--scan
exec dbo.SelectMinAmount1 'N/A'
--index seek
exec dbo.SelectMinAmount1 '345'
with recompile를 사용하므로 해서 프로시저도 재사용하지 않고, Plan도 만들어 진것이 없다
매번 Plan을 만들어서 사용하므로 SQL서버는 자신이 판단하여 index seek를 사용하는 것이
좋은지 scan을 타야 좋은지 판단한다.
결과는 SQL서버는 제대로 판단해서 (1)과 (2)와 같은 문제를 유발 하지 않았다
이것은 정확히 우리가 원하는 결과임.
이전 포스트(동적조회조건에서의 정적프로시저와 동적프로시저)
에서 소개한 아래 쿼리를 사용하여보면 알 수있다.
(http://blog.naver.com/unbimanse?Redirect=Log&logNo=30010736126)
♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣
[1]프로시저를 몇번 재사용했는지 알 수 있다
♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣
SELECT TOP 100 usecounts, size_in_bytes, cacheobjtype, objtype,
REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
WHERE p.objtype = 'Proc' AND cacheobjtype = 'Compiled Plan'
ORDER BY usecounts DESC
♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣
[2]쿼리 혹은 프로시저를 실제 몇 번 Plan cache를 알 수 있다
♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣
SELECT TOP 100 usecounts, size_in_bytes, cacheobjtype, p.objtype,
SUM (total_worker_time / 1000) AS total_cpu_time_in_ms,
SUM (total_physical_reads) AS total_physical_reads,
SUM (total_logical_reads) AS total_logical_reads,
SUM (total_logical_writes) AS total_logical_writes,
REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
FROM sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
WHERE p.objtype in ('Proc','Prepared','Adhoc') AND cacheobjtype = 'Compiled Plan' AND [text] NOT LIKE 'SELECT TOP%'
GROUP BY usecounts, size_in_bytes, cacheobjtype,p.objtype, [text]
ORDER BY usecounts DESC
-----------------------------------------
* Proc는 프로시저를 말하며 [text]에 프로시저명을 볼 수 있다 (Plan cache)
* Prepared는 주로 sp_executesql를 사용할 때 나타나는데 parameterized된 statement기준으로 Plan cache한다.
예를 들면 (@xssn char(9))select min(Amount) from Customers where SSN=@xssn 와 같은 statement로 cache하는 것이 adhoc쿼리와 다른점이다
* adhoc는 select min(Amount) from Customers where SSN='345'와 select min(Amount) from Customers where SSN='346' 을 다른 쿼리로 인식한다. 즉, string이 정확히 일치 해야만 cache한다
하지만, prepared는 위의 두가지 쿼리를 같은 쿼리로 인식하여 동일 plan을 재사용(cache)한다
자세한사항은 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
를 참조하라.
-----------------------------------------
4) dynamic SQL (EXEC) and with recompile / with recompile 미사용
-----------------------------------------
CREATE procedure [dbo].[SelectMinAmount2]@ssn char(9)
--with recompile
as
exec('select min(Amount) from Customers '+
'where SSN='''+@ssn+'''')
-----------------------------------------
exec dbo.SelectMinAmount2 'N/A'
exec dbo.SelectMinAmount2 '345'
exec dbo.SelectMinAmount2 '346'
[2]를 통해서 plan cache를 측정하면
select min(Amount) from Customers where SSN='345 '
select min(Amount) from Customers where SSN='346 '
select min(Amount) from Customers where SSN='N/A '
결과는 with recompile을 사용하지 않으면, 프로시저는 재사용 했지만( [1]수행해보라 )
plan cache는 하지 못했다. 즉, adhoc과 동일한 결과(정확히 일치하는 문장만 cache)이다.
with recompile를 사용하면, 프로시저도 재사용안하고, adhoc과 동일한 cache매카니즘을
따른다.(<- 최종 결과는 SQL서버가 판단하게 하므로 원하는 답은 얻을 수있으나 별로 권장되지
않는 방법)
5) sp_executesql 와 with recompile미사용
-----------------------------------------
CREATE procedure [dbo].[SelectMinAmount3]
@ssn char(9),
@debug bit = 0
as
DECLARE @sql nvarchar(max),
@paramlist nvarchar(max)
SELECT @sql = 'select min(Amount) from Customers '+
'where SSN=@xssn'
IF @debug = 1
PRINT @sql
SELECT @paramlist = '@xssn char(9)'
EXEC sp_executesql @sql, @paramlist, @ssn
-----------------------------------------
exec dbo.SelectMinAmount3 'N/A' --논리적 읽기 수 881
exec dbo.SelectMinAmount3 '345' --논리적 읽기 수 881
exec dbo.SelectMinAmount3 '346' --논리적 읽기 수 881
결과 모두 프로시저 재사용, Plan cache(Proc레벨)함
Plan을 재사용하므로 해서 맨 첫번째인 exec dbo.SelectMinAmount3 'N/A'
를 수행시 생성한 scan을 하는 plan이 캐쉬되어 나머지 모든 프로시저 수행도
scan을 하게되었다
이것은 문제가 된다!
6) sp_executesql 와 with recompile사용
-----------------------------------------
CREATE procedure [dbo].[SelectMinAmount3_1]
@ssn char(9),
@debug bit = 0
as
DECLARE @sql nvarchar(max),
@paramlist nvarchar(max)
SELECT @sql = 'select min(Amount) from Customers '+
'where SSN=@xssn OPTION(RECOMPILE)'
IF @debug = 1
PRINT @sql
SELECT @paramlist = '@xssn char(9)'
EXEC sp_executesql @sql, @paramlist, @ssn
-----------------------------------------
exec dbo.SelectMinAmount3_1 'N/A' --논리적 읽기 수 881
exec dbo.SelectMinAmount3_1 '345' --논리적 읽기 수 6
exec dbo.SelectMinAmount3_1 '346' --논리적 읽기 수 6
결과는 프로시저를 재사용했으며, Plan cache는 Prepared수준에서 재사용했음
이것은 정확히 우리가 원하는 결과임.
**여기서는 with recompile를 사용하지 못한다.
OPTION(RECOMPILE)을 사용하여야 한다.
7) IF와 index hint사용
-----------------------------------------
CREATE procedure [dbo].[SelectMinAmount4]
@ssn char(9)
as
if @ssn = 'N/A'
select min(Amount) from Customers where SSN='N/A'
else
select min(Amount) from Customers with(index=IX_SSN) where SSN=@ssn
-----------------------------------------
exec dbo.SelectMinAmount4 'N/A' --논리적 읽기 수 881
exec dbo.SelectMinAmount4 '345' --논리적 읽기 수 6
exec dbo.SelectMinAmount4 '346' --논리적 읽기 수 6
결과는 프로시저 재사용 및 Plan cache도 Proc수준으로 재사용했음
결과도 원하는 결과임
하지만, 이것은 SQL서버가 판단하는 것이 아니라 사용자가 IF로 분기한 것이다.
즉, 지금은 컬럼이 하나여서 그렇지 IF판단기준이 많으면 코딩은 점점 커지고 복잡하게 될 것이다
그리 권장할 만한 바는 못된다
자 이렇게 다양한 방법으로 테스트를 하였다.
정리를 해보자!
최초 질문은
"흔히, PLAN를 재사용하여야 하고, RECOMPILE은 나쁘다라고 한다.
과연 정말 그럴까?" 에서 출발하였다..
이제 Plan을 항상 재사용한다고 해서, Recompile을 매번 한다고 해서 항상 나쁘다고 말할 수있을까?
[SQL2000상황]
문제는 여기에 있다. 프로시저가 재컴파일을 하면 다른 사용자가 동일한 프로시저를 호출시에
재컴파일 할 동안 기다린다는데 문제가 있다고 하겠다. 항상 재컴파일 하고 다수의 사용자가 기다릴테니깐....
SQL2005에서는 이러한 상황 어떻게 달라졌는지는 아직 모르겠다.
따라서, 이것에 대한 답은 미진하지만 결론은 아래와 같다.
우선, 판단의 근거는 다음과 같다.
보통 Recompile옵션이 필요한 경우가 모든 상황에서 그렇다는 것이 아니다
라는 것이다.
♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣
개발을 끝내고 오픈 테스트 혹은 운영시에 갑자기 성능이 나빠진다면, 그리고 그런 상황이
위에서 발생한 상황과 같은 종류의 상황이라면
문제를 발생시키는 프로시저만 Recompile옵션을 주고 해보는 것이다.
판단은 그때가서 비교해보면서 결정하는것이다.
아무런 대책이 없다는 것과 위와 같이 이러한 Tip도 있으니 비교해서 상황에 맞게
대처하라는 것이다.
즉, Plan을 반드시 재사용해야한다. Recompile이 되서는 안된다라고
못박지 말자라는 것이다.!!
♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣♣
**반드시 [1],[2]을 사용해가면서 어떤 쿼리가 프로시저가 캐쉬를 하는지 안하는지 잘 살펴보기를 바란다
**DBCC FREEPROCCACHE (캐쉬초기화)