Skip to content

Home 서버 Windows MSSQL
✔ 뷰어로 보기
2010.02.24 23:55

SQL Server 2008의 새로운 기능 - Parameter Embedding Optimization

l2zeo
조회 수 18364 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄

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
TAG •

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.

Facebook Twitter Google Pinterest
위로 아래로 댓글로 가기 인쇄
에디터 선택하기
?

  1. [MS-SQL] MS-SQL 2000 이하에서 지원되던 오래된 쿼리 2008 이...

    Date2014.09.28 ByADMINPLAY Views6617
    Read More

  2. MicroSoft SQL Server 2008 1433 port 원격(외부)접속 허용하기

    Date2014.09.20 ByADMINPLAY Views4969
    Read More

  3. SET LOCK_TIMEOUT

    Date2012.01.16 ByADMINPLAY Views14544
    Read More

  4. SQL Server 2008 R2 버전에서 지원하는 기능

    Date2011.03.30 ByADMINPLAY Views16974
    Read More

  5. Fatal error: Call to undefined function: mssql_connect()

    Date2010.06.03 ByADMINPLAY Views23011
    Read More

  6. DBCC SHRINKFILE 트랜잭션 로그파일 축소

    Date2010.05.29 Byl2zeo Views21795
    Read More

  7. 마소제공 mssql 관리툴

    Date2010.04.23 ByADMINPLAY Views19889
    Read More

  8. SQL Server 데이터 파일 축소

    Date2010.03.28 Byl2zeo Views20821
    Read More

  9. [FAQ]join을 하다가 막혔어요..

    Date2010.02.25 Byl2zeo Views16732
    Read More

  10. [FAQ]mssql2005 디버깅 어떻게 해야 하나요?

    Date2010.02.25 Byl2zeo Views21073
    Read More

  11. [FAQ]하나의 서버에 두개의 database를 설치하려고 합니다.

    Date2010.02.25 Byl2zeo Views19702
    Read More

  12. 데이터 원본에 저장 프로시저 사용하기

    Date2010.02.25 Byl2zeo Views18772
    Read More

  13. MSSQL 버전별 최대 용량 비교표

    Date2010.02.24 Byl2zeo Views23862
    Read More

  14. SQL Server 2008의 새로운 기능 - Parameter Embedding Optimi...

    Date2010.02.24 Byl2zeo Views18364
    Read More

  15. SQL서버 성능카운터 활용을 위한 팁

    Date2010.02.24 Byl2zeo Views20195
    Read More

  16. SQL Server 2005 백업 설정 방법

    Date2010.02.24 Byl2zeo Views18035
    Read More

  17. SQL Agent 작업 실행 상태 확인하기

    Date2010.02.23 Byl2zeo Views18166
    Read More

  18. DB 서버에 특정 IP 접근 차단 방법

    Date2010.02.21 Byl2zeo Views22190
    Read More

  19. [MSSQL 2000] 성능 모니터링

    Date2010.02.21 Byl2zeo Views15188
    Read More

  20. [SQL Server] Build Number of Each Version of SQL Server - ...

    Date2009.12.20 ByADMINPLAY Views34413
    Read More
쓰기
Board Pagination Prev 1 2 Next
/ 2

Copyright ADMINPLAY corp. All rights reserved.

abcXYZ, 세종대왕,1234

abcXYZ, 세종대왕,1234