SQL 사용자 Tip & 강좌
SQL Server 2008에 재미있는 옵티마이저 기능이 추가 되었습니다.
하지만 버그로 인해서 SP1 또는 RTM CU4부터는 이 기능을 사용할 수 없었지만,
얼마 전에 나온 SP1 CU5에서 버그를 수정하여 이 기능을 다시 제공하고 있습니다.
Parameter Embedding Optimization이라고 불리며, OPTION(RECOMPILE)을 사용하는 쿼리의 경우 실행계획을 컴파일 할 때 실제 매개변수의 값을 가지고 컴파일 하는 기능입니다. 그렇다면 분명 기존보다 효율적인 플랜을 만들어줄 것 입니다. 뭐 당연하다고 생각할 수 도 있을 것 같습니다. 플랜 캐시를 재사용을 포기하며 recompile옵션도 추가했는데~ 플랜까지 이상하다면 좀 이상하겠죠!
이 기능이 도움을 줄 수 있는 경우는 쿼리가 변수에 따라서 플랜이 변경되어야 하는 경우 입니다.
입력되는 변수에 따라 WHERE 조건이 변경되는 경우 SQL Server 2008 이전 버전을 사용했을 때 적절한 플랜으로 처리 되기 위해서는 모든 경우에 따라 분기 문 또는 SP를 분리하여 사용하거나, 쿼리 문자열을 조합하는 형태의 동적 쿼리를 사용할 수 밖에 없었습니다. 동적 쿼리는 플랜 재사용도 거의 안되면서 플랜캐시에 상주할 수 있기에 플랜 캐시가 늘어나는 문제와 보안적인 문제를 가질 수 있습니다. 경우의 수만큼 분기 문을 사용한 쿼리는 유지 보수를 하기가 참~ 어려웠습니다.
그럼 이 기능으로 플랜이 어떻게 변경되는지 확인해 보겠습니다.
SQL Server 2008 SP1 + CU5와 SQL Server 2008 SP1 버전으로 비교해 보았습니다.
[테스트 1] 입력되는 변수 값에 따라 WHERE 조건이 변경되는 경우
SP1과 SP1 + CU5환경에서 실행하면 아래와 같은 실행계획을 확인 할 수 있습니다.
SP1의 경우는 TABLE SCAN으로 풀렸으며, SP1+CU5의 경우는 Index Seek + RID Lookup을 사용하였습니다.
아래 플랜에서 노란색으로 표시된 부분을 보면 SP1의 경우 조건 절에서 변수로 비교하며, CU5의 경우 상수로 비교하고 있습니다.
그래서 플랜이 서로 다른 모습을 보여주고 있으며, CU5가 파라미터의 값을 가지고 플랜을 생성하여 보다 효율적인 쿼리 플랜을 생성했습니다.
SQL Server 2008 SP1
|
Rows |
Executes |
StmtText |
|
1 |
1 |
SELECT * FROM tbl90 WHERE (col1 = @a AND @a IS NOT NULL) OR (col2 = @a2 AND @a2 IS NOT NULL) OR (col3 = @a3 AND @a3 IS NOT NULL) OR (col4 = @a4 AND @a4 IS NOT NULL) OR (col5 = @a5 AND @a5 IS NOT NULL) OR (col6 = @a6 AND @a6 IS NOT NULL) OPTION(RECOMPILE) |
|
1 |
1 |
|--Table Scan(OBJECT:([test].[dbo].[tbl90]), WHERE:([test].[dbo].[tbl90].[col1]=[@a] AND [@a] IS NOT NULL OR [test].[dbo].[tbl90].[col2]=[@a2] AND [@a2] IS NOT NULL OR [test].[dbo].[tbl90].[col3]=[@a3] AND [@a3] IS NOT NULL OR [test].[dbo].[tbl90].[col4]=[@a4] AND [@a4] IS NOT NULL OR [test].[dbo].[tbl90].[col5]=[@a5] AND [@a5] IS NOT NULL OR [test].[dbo].[tbl90].[col6]=[@a6] AND [@a6] IS NOT NULL)) |
SQL Server 2008 SP1 + CU5
|
Rows |
Executes |
StmtText |
|
1 |
1 |
SELECT * FROM tbl90 WHERE (col1 = @a AND @a IS NOT NULL) OR (col2 = @a2 AND @a2 IS NOT NULL) OR (col3 = @a3 AND @a3 IS NOT NULL) OR (col4 = @a4 AND @a4 IS NOT NULL) OR (col5 = @a5 AND @a5 IS NOT NULL) OR (col6 = @a6 AND @a6 IS NOT NULL) OPTION(RECOMPILE) |
|
1 |
1 |
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) |
|
1 |
1 |
|--Index Seek(OBJECT:([tempdb].[dbo].[tbl90].[ix_tbl903]), SEEK:([tempdb].[dbo].[tbl90].[col3]=(1)) ORDERED FORWARD) |
|
1 |
1 |
|--RID Lookup(OBJECT:([tempdb].[dbo].[tbl90]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) |
DROP TABLE tbl90
CREATE TABLE tbl90 (col1 INT NOT NULL, col2 INT ,col3 INT,col4 INT,col5 INT,col6 INT)
INSERT INTO tbl90
SELECT TOP 100000
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1))
FROM sys.sysindexes a,sys.sysindexes a1,sys.sysindexes a2,sys.sysindexes a3
CREATE INDEX ix_tbl90 ON tbl90 (col1)
CREATE INDEX ix_tbl902 ON tbl90 (col2)
CREATE INDEX ix_tbl903 ON tbl90 (col3)
CREATE INDEX ix_tbl904 ON tbl90 (col4)
CREATE INDEX ix_tbl905 ON tbl90 (col5)
-- CREATE INDEX ix_tbl906 ON tbl90 (col6)
GO
CREATE PROC UP_90
@a INT = NULL
,@a2 INT = NULL
,@a3 INT = NULL
,@a4 INT = NULL
,@a5 INT = NULL
,@a6 INT = NULL
AS
SELECT * FROM tbl90
WHERE
(col1 = @a AND @a IS NOT NULL)
OR (col2 = @a2 AND @a2 IS NOT NULL)
OR (col3 = @a3 AND @a3 IS NOT NULL)
OR (col4 = @a4 AND @a4 IS NOT NULL)
OR (col5 = @a5 AND @a5 IS NOT NULL)
OR (col6 = @a6 AND @a6 IS NOT NULL)
OPTION(RECOMPILE)
GO
SET STATISTICS PROFILE ON
exec up_90 @a3 = 3
[테스트 2] 입력되는 변수 값에 따라 조회하는 테이블이 변경되는 경우
UNION ALL을 통해서 변수 값에 조회할 테이블을 선택할 수 있는 경우 입니다.
많이 사용되는 쿼리 중 하나인데요, SP1 + CU5버전에서 RECOMPILE옵션을
추가하면 입력된 변수 값에 따라 실질적으로 읽어야 할 테이블에 대해서만 조회를 하는 것을 볼 수 있습니다.
SQL Server 2008 SP1 + CU5
|
Rows |
Executes |
StmtText |
|
1 |
1 |
SELECT * FROM tbl90 WITH(NOLOCK) WHERE @a IS NOT NULL AND @a = col1 UNION ALL SELECT * FROM tbl91 WITH(NOLOCK) WHERE @a1 IS NOT NULL AND @a1 = col1 OPTION(RECOMPILE) |
|
0 |
0 |
|--Compute Scalar(DEFINE:([Union1008]=[tempdb].[dbo].[tbl90].[col1], [Union1009]=[tempdb].[dbo].[tbl90].[col2], [Union1010]=[tempdb].[dbo].[tbl90].[col3], [Union1011]=[tempdb].[dbo].[tbl90].[col4], [Union1012]=[tempdb].[dbo].[tbl90].[col5], [Union1013]=[tempdb].[dbo].[tbl90].[col6])) |
|
1 |
1 |
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) |
|
1 |
1 |
|--Index Seek(OBJECT:([tempdb].[dbo].[tbl90].[ix_tbl90]), SEEK:([tempdb].[dbo].[tbl90].[col1]=(1)) ORDERED FORWARD) |
|
1 |
1 |
|--RID Lookup(OBJECT:([tempdb].[dbo].[tbl90]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) |
SQL Server 2008 SP1
|
Rows |
Executes |
StmtText |
|
1 |
1 |
SELECT * FROM tbl90 WITH(NOLOCK) WHERE @a IS NOT NULL AND @a = col1 UNION ALL SELECT * FROM tbl91 WITH(NOLOCK) WHERE @a1 IS NOT NULL AND @a1 = col1 OPTION(RECOMPILE) |
|
1 |
1 |
|--Concatenation |
|
1 |
1 |
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) |
|
1 |
1 |
| |--Filter(WHERE:(STARTUP EXPR([@a] IS NOT NULL))) |
|
1 |
1 |
| | |--Index Seek(OBJECT:([TEST2].[dbo].[tbl90].[ix_tbl90]), SEEK:([TEST2].[dbo].[tbl90].[col1]=[@a]) ORDERED FORWARD) |
|
1 |
1 |
| |--RID Lookup(OBJECT:([TEST2].[dbo].[tbl90]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) |
|
0 |
1 |
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004])) |
|
0 |
1 |
|--Filter(WHERE:(STARTUP EXPR([@a1] IS NOT NULL))) |
|
0 |
0 |
| |--Index Seek(OBJECT:([TEST2].[dbo].[tbl91].[ix_tbl91]), SEEK:([TEST2].[dbo].[tbl91].[col1]=[@a1]) ORDERED FORWARD) |
|
0 |
0 |
|--RID Lookup(OBJECT:([TEST2].[dbo].[tbl91]), SEEK:([Bmk1004]=[Bmk1004]) LOOKUP ORDERED FORWARD) |
DROP TABLE tbl91
CREATE TABLE tbl91 (col1 INT NOT NULL, col2 INT ,col3 INT,col4 INT,col5 INT,col6 INT)
INSERT INTO tbl91
SELECT TOP 100000
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1))
FROM sys.sysindexes a,sys.sysindexes a1,sys.sysindexes a2,sys.sysindexes a3
CREATE INDEX ix_tbl91 ON tbl91 (col1)
CREATE INDEX ix_tbl912 ON tbl91 (col2)
CREATE INDEX ix_tbl913 ON tbl91 (col3)
CREATE INDEX ix_tbl914 ON tbl91 (col4)
CREATE INDEX ix_tbl915 ON tbl91 (col5)
CREATE INDEX ix_tbl916 ON tbl91 (col6)
GO
CREATE PROC UP_93
@a INT = NULL
,@a1 INT = NULL
AS
SELECT * FROM tbl90 WITH(NOLOCK) WHERE @a IS NOT NULL AND @a = col1
UNION ALL
SELECT * FROM tbl91 WITH(NOLOCK) WHERE @a1 IS NOT NULL AND @a1 = col1
OPTION(RECOMPILE)
GO
EXEC UP_93 @a = 1
추가적인 정보는 아래 링크를 참조하세요.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=244298
http://support.microsoft.com/kb/976603/
송 혁, SQL Server MVP
sqler.com // sqlleader.com
hyoksong.tistory.com
![[레벨:75]](http://www.sqler.com/modules/point/icons/warrock_17x17/75.gif "포인트:11281point (10%), 레벨:75/100"){kind=icon}
Alucard
![[레벨:50]](http://www.sqler.com/modules/point/icons/warrock_17x17/50.gif "포인트:5149point (73%), 레벨:50/100"){kind=icon}
석이
1. 보안을 제외하고 sp_executesql 과 성능비교.
2. 모든버전에서 될 것 같은데.....^^
3. 쿼리에 어떻게 하면 컬러가 나오게 할 수있는거지? 좀 갈쳐주삼....
![[레벨:16]](http://www.sqler.com/modules/point/icons/warrock_17x17/16.gif "포인트:516point (6%), 레벨:16/100"){kind=icon}
송혁1. 해보지는 않았지만 동일할듯.
2. 네~ 모든 버젼에서 지원함!(Standard이하는 테스트 못해봄)
3. SSMS에 있는 쿼리를 워드에 복사 후 다시 복사하여 붙여넣기 하면됨!
석이
TEST 결과 Dynamic SQL 이 약 5배 정도 빠릅니다.
혹시 테스트가 잘 못 되었을 수 있으니 한번 테스트 부탁~
ostress -q 모드로 테스트 했음 5개 세션에 1000회 실행 결과는 1건만 가져오는 것으로 생성
where 컬럼은 동적으로 구성되어 있지만 바뀌지 않도록 인풋 조정 ( 대부분의 부하가 특정 컬럼 조건으로 들어오고 각각 플랜이 생긴다는 가정)
TEST 결과가 어찌 되었건 SQL Server 의 옵티마이저가 참 좋아지고 있다는 것을 느끼게 되었습니다.
이하 타 DB의 몇 몇 옵티마이징에서 부러운 점
1. Adaptive Cursors (Oracle 11g)
2. Result Set Cache (Oracle, MySQL)
3. Index SkipScan (Oracle)
사실 이 기능이 없어서 아주 괴로운 적도 있었습니다. 성능 테스트가 올바르다면, 관리용 프로시저에는 아주 좋을 듯 합니다. TEST 가 잘 못 되었고 성능이 같다면, 정말 SQL Server 옵티마이저는 짱 입니다. 짱! ^-^;
이하 테스트 스크립트 ((복잡도, 보안 제외), 성능 위주의 테스트)
IF OBJECT_ID('TBL90') IS NOT NULL
DROP TABLE TBL90
GO
CREATE TABLE tbl90 (col1 INT NOT NULL, col2 INT)
GO
INSERT INTO tbl90
SELECT TOP 10000
ROW_NUMBER() OVER(ORDER BY(SELECT 1)),
ROW_NUMBER() OVER(ORDER BY(SELECT 1))+1
FROM sys.sysindexes a,sys.sysindexes a1,sys.sysindexes a2,sys.sysindexes a3
GO
CREATE INDEX ix_tbl901 ON tbl90 (col1)
CREATE INDEX ix_tbl902 ON tbl90 (col2)
GO
IF OBJECT_ID ('UP_90_S') IS NOT NULL
DROP PROC UP_90_S
GO
CREATE PROC UP_90_S
@a1 INT = NULL
,@a2 INT = NULL
AS
SELECT *
FROM tbl90
WHERE (col1 = @a1 AND @a1 IS NOT NULL)
OR (col2 = @a2 AND @a2 IS NOT NULL)
OPTION(RECOMPILE)
GO
IF OBJECT_ID ('UP_90_D') IS NOT NULL
DROP PROC UP_90_D
GO
CREATE PROC UP_90_D
@a1 INT = NULL
,@a2 INT = NULL
AS
declare @stmt as nvarchar(100) = N''
declare @params as nvarchar(100) = N''
declare @whereCol as int
set @stmt = N'select top 1 col1, col2
from dbo.tbl90'
if @a1 is not null begin
set @stmt = @stmt + N' where col1 = @whereCol'
set @params = N'@whereCol int'
set @whereCol = @a1
end
else if @a2 is not null begin
set @stmt = @stmt + N' where col2 = @whereCol'
set @params = N'@whereCol int'
set @whereCol = @a2
end
--select @stmt, @params, @whereCol
exec sp_executesql @stmt, @params, @whereCol=@whereCol
go
ostress -S.,1433 -Usqler -P000 -daw -Q"exec dbo.UP_90_S @a1 = 2" -n5 -r1000 -q
ostress -S.,1433 -Usqler -P000 -daw -Q"exec dbo.UP_90_D @a1 = 2" -n5 -r1000 -q
static OSTRESS exiting normally, elapsed time: 00:00:10.525
dynamic OSTRESS exiting normally, elapsed time: 00:00:02.323
![[레벨:20]](http://www.sqler.com/modules/point/icons/warrock_17x17/20.gif "포인트:806point (7%), 레벨:20/100"){kind=icon}
연이
.gif "emoticon")
송혁올려주신 스크립트로 동적 쿼리 vs OPTION(RECOMPILE) 테스트 해봤습니다.
해당 쿼리로만 수행하게 되면, 분명하게 동적쿼리쪽이 성능이 좋습니다.
동적쿼리쪽이 성능이 좋은 이유가~ 플랜이 달라서 그런 것이 아니라
플랜캐시를 재사용하기에 빠른 것으로 확인했습니다.
동적쿼리쪽에 강제로 RECOMPILE하도록 설정하면 두 경우가 비슷하게 나오네요~
대부분 상황에 대해서 성능만을 본다면 동적쿼리는 쿼리 플랜을 재사용할 수 있을
가능성이 높기에 성능적으로 좋을 것으로 보입니다.
하지만 동적쿼리에서 플랜을 재사용하게 될 때 파라미터스니핑 문제가 발생할 가능성은 충분히 있어 보입니다.
대충 결론을 지으면,
많은 요청수로 인해 리 컴파일에 대한 비용이 부담된다면 조금이나마 플랜 캐시를 재사용할 수 있는 동적쿼리를 사용하는 것이 성능에 유리하다.
하지만 재사용으로 인한 파라미터스니핑으로 오히려 문제가 될 수 있다.
석이
컬럼을 다르게, 테이블을 다르게 와 파라메터 스니핑과 플랜 재형성을 하는 이슈는 좀 다른 이슈인데 사용자가 이런 차이를 알면 좋을것 같습니다. ^.^ 논점은 option (recompile) 이 들어 있음으로 해서 성능이 많이 떨어질 수 있다는 이야기 입니다. 바꾸어 이야기 하면, 파라메터 스니핑이 일어날 수 있으니 프로시저는 모두 recompile 걸어라는 좀 아니잖아요 ^^; 그래서 컬럼이 달라지거나 테이블이 달라질 경우는 성능을 위해서는 dynamic sql 의 sp_executesql 을 쓰면 좀 더 성능이 좋을 거라는 이야기 입니다. 그래도 관리용 이라면 전 송혁님이 추천하신 방법을 쓸 듯 합니다. 그러나 서비스용 에서 저런 이슈가 있다면 동적 프로시저나 프로시저를 모두 따로 만들 것 같습니다. 문론 파라메터 스니핑이 일어나 이슈가 발생할 가능성이 있는 데이터의 경우는 반드시 statement 단위의 recompile 옵션인 option (recompile) 을 적어줘야 하겠죠.
동적 프로시저의 경우 where column 에 따라서 from table 에 따라서 각각 플랜을 다양하게 따로 가지고 있을 수 있습니다. 그러니 파라메터 스니핑 이슈는 제외하고, 컬럼이 달라짐에 따라서나 테이블이 달라짐에 따라서 최적의 성능을 구현하기 위해서는 sp_executesql 이 아닐까 생각 합니다.
최초 글에서 성능을 이야기 한 것은 이 논점 입니다.
![[레벨:40]](http://www.sqler.com/modules/point/icons/warrock_17x17/40.gif "포인트:3205point (3%), 레벨:40/100"){kind=icon}
하만철
좋은 정보 감사합니다~ ^^
SQL 2008에서는 옵티마이저의 기능을 추가하려는 시도들이 보이는것 같던데 이런것도 있었네요~
![[레벨:49]](http://www.sqler.com/modules/point/icons/warrock_17x17/49.gif "포인트:4923point (61%), 레벨:49/100"){kind=icon}
불타는불쥐
![[레벨:6]](http://www.sqler.com/modules/point/icons/warrock_17x17/6.gif "포인트:96point (92%), 레벨:6/100"){kind=icon}
후니훈2008 SP1+ CU5 : OPTION (RECOMPILE) 옵션으로 index scan -> index seek 가 되어서 참빠르다 생각 했는데 플랜캐쉬 사용이 좀더 빠르군요! 좋은 정보 알았습니다.
허나 동적 쿼리는 해당 table에 select권한이 있어야 하기때문에 보안상 좋지 못할 거 같네요.
자주 요청이 들어오는 쿼리가 아닌 이상 약간의 비용손실로 모두 해결 할 수있을 거 같습니다.
CU6에서도 잘되는지 테스트 해봐야 겠네요..안될 수도 있다고 그러던데 ㅎㅎ
문의) 위 store proc를 실행 할 때 2000, 2005에서는 CU update를 통해 or 특정 힌트 옵션으로 index seek를 할 수 있게 하는 방법이 있나요? 방법좀 문의 드립니다.
