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
