Skip to content

[레벨:19]id: id: l2zeo
댓글 0 조회 수 15913 추천 수 0
?

단축키

이전 문서

다음 문서

+ - Up Down Comment Print
?

단축키

이전 문서

다음 문서

+ - Up Down Comment Print

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

?Who's l2zeo

profile

I see no changes, wake up in the morning and I ask myself

Is life worth living should I blast myself

Things would never be the same.


번호 제목 글쓴이 날짜 조회 수
39 [MS-SQL] MS-SQL 2000 이하에서 지원되던 오래된 쿼...   [레벨:30]id: ADMINPLAY 2014.09.28 2719
38 MicroSoft SQL Server 2008 1433 port 원격(외부)접...   [레벨:30]id: ADMINPLAY 2014.09.20 2980
37 SET LOCK_TIMEOUT   [레벨:30]id: ADMINPLAY 2012.01.16 12710
36 SQL Server 2008 R2 버전에서 지원하는 기능   [레벨:30]id: ADMINPLAY 2011.03.30 14464
35 Fatal error: Call to undefined function: mssql_co...   [레벨:30]id: ADMINPLAY 2010.06.03 20788
34 DBCC SHRINKFILE 트랜잭션 로그파일 축소   [레벨:19]id: id: l2zeo 2010.05.29 20153
33 마소제공 mssql 관리툴   [레벨:30]id: ADMINPLAY 2010.04.23 18521
32 SQL Server 데이터 파일 축소   [레벨:19]id: id: l2zeo 2010.03.28 19011
31 [FAQ]join을 하다가 막혔어요..   [레벨:19]id: id: l2zeo 2010.02.25 14993
30 [FAQ]mssql2005 디버깅 어떻게 해야 하나요?  fileimage [레벨:19]id: id: l2zeo 2010.02.25 19285
29 [FAQ]하나의 서버에 두개의 database를 설치하려고 합니...   [레벨:19]id: id: l2zeo 2010.02.25 17923
28 데이터 원본에 저장 프로시저 사용하기  fileimage [레벨:19]id: id: l2zeo 2010.02.25 17046
27 MSSQL 버전별 최대 용량 비교표   [레벨:19]id: id: l2zeo 2010.02.24 17133
» SQL Server 2008의 새로운 기능 - Parameter Embedd...   [레벨:19]id: id: l2zeo 2010.02.24 15913
25 SQL서버 성능카운터 활용을 위한 팁  file [레벨:19]id: id: l2zeo 2010.02.24 18468
24 SQL Server 2005 백업 설정 방법  fileimage [레벨:19]id: id: l2zeo 2010.02.24 16570
23 SQL Agent 작업 실행 상태 확인하기   [레벨:19]id: id: l2zeo 2010.02.23 16545
22 DB 서버에 특정 IP 접근 차단 방법  fileimage [레벨:19]id: id: l2zeo 2010.02.21 19831
21 [MSSQL 2000] 성능 모니터링  fileimage [레벨:19]id: id: l2zeo 2010.02.21 13575
20 [SQL Server] Build Number of Each Version of ...   [레벨:30]id: ADMINPLAY 2009.12.20 15465
Board Pagination ‹ Prev 1 2 Next ›
/ 2
Designed by hikaru100

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소

SketchBook5,스케치북5

SketchBook5,스케치북5

SketchBook5,스케치북5

SketchBook5,스케치북5

Copyright ADMINPLAY corp. All rights reserved.

abcXYZ, 세종대왕,1234

abcXYZ, 세종대왕,1234