보미네

USE MASTER

GO

IF EXISTS (SELECT * FROM SYS.DATABASES WHERE NAME ='DB_PLAN_GUIDE_TEST')

BEGIN

       ALTER DATABASE  DB_PLAN_GUIDE_TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE

       DROP DATABASE DB_PLAN_GUIDE_TEST

END

GO

CREATE DATABASE DB_PLAN_GUIDE_TEST

 CONTAINMENT = NONE

 ON  PRIMARY

( NAME = N'DB_PLAN_GUIDE_TEST', FILENAME = N'L:\MSSQL\DB_PLAN_GUIDE_TEST.MDF' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB )

 LOG ON

( NAME = N'DB_PLAN_GUIDE_TEST_LOG', FILENAME = N'L:\MSSQL\DB_PLAN_GUIDE_TEST_LOG.LDF' , SIZE = 100MB , MAXSIZE = 2048GB , FILEGROWTH = 100MB )

GO

USE DB_PLAN_GUIDE_TEST

GO

-- 기본 상태로 만든다.

ALTER DATABASE DB_PLAN_GUIDE_TEST SET PARAMETERIZATION SIMPLE

GO

SELECT * FROM SYS.plan_guides

GO

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST1')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST1

GO

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST2')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST2

GO

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST3')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST3

GO

IF OBJECT_ID ('TBLX') IS NOT NULL

DROP TABLE TBLX

GO

WITH TEMP AS

(

SELECT TOP 1000000

          CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS INT) IDX

        , 'PNAME_'+RIGHT(REPLICATE('0',7)+CAST(CAST(ABS(CHECKSUM(NEWID())) % 1000 AS INT) AS VARCHAR(10)),7)+CHAR(ASCII('A') + ABS(CHECKSUM(NEWID()))%10) PRODUCTNAME

  FROM SYS.OBJECTS A1

 CROSS JOIN SYS.OBJECTS A2

 CROSS JOIN SYS.OBJECTS A3

 CROSS JOIN SYS.OBJECTS A4

 CROSS JOIN SYS.OBJECTS A5

)

SELECT IDX, PRODUCTNAME, CAST(SUBSTRING(PRODUCTNAME,7,7) AS INT) PRODUCTID

        , CAST(ABS(CHECKSUM(NEWID())) % 100 AS INT) SHOPID

        , CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS INT) PRICE

        , DATEADD(MINUTE, CAST(ABS(CHECKSUM(NEWID())) % 20000 AS INT) * -1, GETDATE()) INSERTTIME

  INTO TBLX

  FROM TEMP

GO

--SELECT COUNT(*) FROM TBLX

--SELECT TOP 10 * FROM TBLX

CREATE UNIQUE CLUSTERED INDEX UCL_TBLX ON TBLX (IDX)

CREATE NONCLUSTERED INDEX NC_TBLX_02 ON TBLX (PRODUCTID)

GO

DBCC FREEPROCCACHE

GO

SET STATISTICS PROFILE ON

GO

-- SIMPLE PARAMETERIZATION을 방지하기 위해 JOIN 함

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 0

-- SERIAL PLAN

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 1000000

-- PARALLEL PLAN

SET STATISTICS PROFILE OFF

-- 이제 항상 PARALLEISM 으로 동작 시키기 위해서 PLAN 을 고정시켜 보고자 한다.

-- QUERY A START

DECLARE @STMT NVARCHAR(MAX);

DECLARE @PARAMS NVARCHAR(MAX);

EXEC SP_GET_QUERY_TEMPLATE N'

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 1000000

 ',

    @STMT OUTPUT,

    @PARAMS OUTPUT;

-- 확인을 위한 쿼리

SELECT @STMT, @PARAMS

EXEC SP_CREATE_PLAN_GUIDE

@NAME = N'PLAN_GUIDE_TEST1',

@STMT = @STMT,

@TYPE = N'SQL',

@MODULE_OR_BATCH = NULL,

@PARAMS = @PARAMS,

@HINTS = N'OPTION (OPTIMIZE FOR (@0 = 1000000))'

-- QUERY A END

SELECT * FROM SYS.plan_guides WHERE NAME = 'PLAN_GUIDE_TEST1'

-- 이렇게 해서는 PLAN_GUIDE 가 동작하지 않는다.

-- 왜냐구? 파라미터된 계획과 그렇지 않는 계획은 다른것 이기 때문이다.

-- 아래 쿼리가 PARALLEL 로 돌아야 하는데 SERIAL 로 동작한다.

-- 속성에도 PLAN GUIDE 가 쓰지 않은 것으로 뜬다.

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 1

-- SERIAL PLAN 으로 동작함

-- 옵셥 확인

SELECT CASE WHEN IS_PARAMETERIZATION_FORCED = 0 THEN 'DISABLED' ELSE 'ENABLED' END

     , COMPATIBILITY_LEVEL

  FROM SYS.DATABASES

 WHERE NAME ='DB_PLAN_GUIDE_TEST'

GO

-- 강제 PARAMETERIZATION 활성화

ALTER DATABASE DB_PLAN_GUIDE_TEST SET PARAMETERIZATION FORCED

GO

-- 이제는 플랜가이드가 동작한다.

-- 기타 체크

-- 호환성 레벨 확인법

--exec sp_helpdb paging

--exec sp_dbcmptlevel paging

-- 호환성 레벨 변경방법

--ALTER DATABASE DB_PLAN_GUIDE_TEST

--SET COMPATIBILITY_LEVEL = 110

-- 호환성 레벨이 90 이상이고 IS_PARAMETERIZATION_FORCED 이 1 이면 가능하다.

DBCC FREEPROCCACHE

GO

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 0

-- 병렬실행 계획이 동작한다면 정상이다.

-- 이 하나의 쿼리를 파라미터화 해서 쓰기 위해 전체 데이터베이스 옵션을 변경 할 수는 없다.

-- 그래서 다시 SIMPLE 로 돌린다.

ALTER DATABASE DB_PLAN_GUIDE_TEST SET PARAMETERIZATION SIMPLE

GO

DBCC FREEPROCCACHE

GO

-- 해당 쿼리만 PARAMETERIZATION FORCED 옵션을 만들어주는 플랜가이드를 하나 더 만든다.

-- 기발한 생각이죠?

DECLARE @STMT NVARCHAR(MAX);

DECLARE @PARAMS NVARCHAR(MAX);

EXEC SP_GET_QUERY_TEMPLATE N'

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 1000000

 ',

    @STMT OUTPUT,

    @PARAMS OUTPUT;

EXEC sp_create_plan_guide

    N'PLAN_GUIDE_TEST2',

    @stmt,

    N'TEMPLATE',

    NULL,

    @params,

    N'OPTION(PARAMETERIZATION FORCED)';

GO

-- 아래와 같이 두개의 플랜가이드를 확인한다.

SELECT * FROM SYS.plan_guides

GO

plan_guide_id name   create_date  modify_date  is_disabled  query_text   scope_type       scope_type_desc     scope_object_id     scope_batch  parameters   hints

65537  PLAN_GUIDE_TEST1    2012-12-08 21:27:21.103    2012-12-08 21:27:21.103    0      select SUM ( A . SHOPID ) from TBLX A join TBLX B on A . IDX = B . IDX where B . PRODUCTID < @0       2      SQL    NULL   select SUM ( A . SHOPID ) from TBLX A join TBLX B on A . IDX = B . IDX where B . PRODUCTID < @0     @0 int OPTION (OPTIMIZE FOR (@0 = 1000000))

65539  PLAN_GUIDE_TEST2    2012-12-08 23:31:13.657    2012-12-08 23:31:13.657    0      select SUM ( A . SHOPID ) from TBLX A join TBLX B on A . IDX = B . IDX where B . PRODUCTID < @0       3      TEMPLATE     NULL   select SUM ( A . SHOPID ) from TBLX A join TBLX B on A . IDX = B . IDX where B . PRODUCTID < @0      @0 int OPTION(PARAMETERIZATION FORCED)

-- 결국 위 두개의 PLAN_GUIDE 조합으로 PARAMETERIZATION 이 SIMPLE 인데도 위 쿼리는 파라미터화 해서 동작 시킬 수 있다.

-- 아래 쿼리를 동작시키면 병렬로 동작 할 것이다.

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 0

-- 일반적인 테스트 끝

-- 원상복귀

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST1')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST1

GO

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST2')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST2

GO

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST3')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST3

GO

-- 동작의 검증

--PROFILER 에서 PERFORMANCE > PLAN GUIDE SUCCESSFUL , PLAN GUIDE UNSUCCESSFUL 로 확인 가능하다.

--GRAPHIC 실행계획의 마지막 이터레이터에서 속성을 확인하면 플랜가이드가 사용된 것을 확인 할 수 있다.

-- 테스트 시나리오 2

-- 아래는 SP_CREATE_PLAN_GUIDE_FROM_HANDLE 으로 플랜을 고정시키는 방법이다.

-- 플랜에 올라온 실행계획을 고정시키는 방법이다.

-- 그러나, 이 방법으로 MEMORY GRANT 는 고정시킬 수 없고, 역시 PARAMETERIZATION FORCE 는 개별로 ENABLE 시키던지

-- 데이터베이스 전역으로 ENABLE 시켜야 한다.

-- 그러므로 OPTIMIZER FOR 를 이용해 실행시 PARAMETER 를 인식시켜 충분한 메모리를 할당 받을 수

-- 있도록 하는것이 최선의 방법이다.

-- 어떤 방법이든, 방법의 한계를 잘 이해하는 것이 중요하다.

-- 역시 SIMPLE인 상태에서 파라미터화 해서 쓰기위해서 다음 플랜가이드를 먼저 만든다.

DECLARE @STMT NVARCHAR(MAX);

DECLARE @PARAMS NVARCHAR(MAX);

EXEC SP_GET_QUERY_TEMPLATE N'

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 1000000

 ',

    @STMT OUTPUT,

    @PARAMS OUTPUT;

EXEC sp_create_plan_guide

    N'PLAN_GUIDE_TEST2',

    @stmt,

    N'TEMPLATE',

    NULL,

    @params,

    N'OPTION(PARAMETERIZATION FORCED)';

GO

DBCC FREEPROCCACHE

GO

-- 병렬쿼리로 동작 할꺼다.

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 10000000

GO

-- 이 플랜을 받아서 고정시켜보자

-- 그런데 이번에는 좀 스마트하게 SP_CREATE_PLAN_GUIDE_FROM_HANDLE 에서 받아볼까?

DECLARE @PLAN_HANDLE VARBINARY(1000)

SELECT @PLAN_HANDLE = PLAN_HANDLE

--SELECT *

  FROM SYS.DM_EXEC_QUERY_STATS QS

 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) SQT

 WHERE TEXT LIKE '%SUM%SHOPID%PRODUCTID%'

   AND TEXT NOT LIKE '%SYS.DM_EXEC%'

SELECT @PLAN_HANDLE

EXEC SP_CREATE_PLAN_GUIDE_FROM_HANDLE 'PLAN_GUIDE_TEST3', @PLAN_HANDLE=@PLAN_HANDLE

GO

DBCC FREEPROCCACHE

GO

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 0

GO

--MemoryGrant="25736"

DBCC FREEPROCCACHE

GO

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 10000000

GO

--MemoryGrant="205960"

-- 위와 같이 플랜은 같게 플랜 가이드를 잘 쓰지만 메모리 GRANT 는 크게 차이가 나고 처음에 0을 넣고

-- 돌린 쿼리가 돌게되면 두번째 쿼리 변수가 크면 이번에는 HASH WARNING 이 발생하면서 TEMPDB SPILL 이 발생한다.

-- 동작 속도도 엄청나게 떨어지게 된다.

-- 테스트를 완료 했으면, 플랜 가이드를 지운다.

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST1')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST1

GO

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST2')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST2

GO

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST3')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST3

GO

-- 2번 테스트 끝

-- 강제 파라미터화를 위해 PLAN_GUIDE_TEST2 는 지우지 않았다.

-- 테이블 힌트를 통해서 플랜가이드를 주었을때 해당 인덱스가 삭제되면

-- 어떻게 될까? (동작하지 않는다.)

-- CREATE NONCLUSTERED INDEX NC_TBLX_02 ON TBLX (PRODUCTID)

GO

DECLARE @STMT NVARCHAR(MAX);

DECLARE @PARAMS NVARCHAR(MAX);

EXEC SP_GET_QUERY_TEMPLATE N'

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 1000000

 ',

    @STMT OUTPUT,

    @PARAMS OUTPUT;

EXEC sp_create_plan_guide

    N'PLAN_GUIDE_TEST2',

    @stmt,

    N'TEMPLATE',

    NULL,

    @params,

    N'OPTION(PARAMETERIZATION FORCED)';

GO

DECLARE @STMT NVARCHAR(MAX);

DECLARE @PARAMS NVARCHAR(MAX);

EXEC SP_GET_QUERY_TEMPLATE N'

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 1000000

 ',

    @STMT OUTPUT,

    @PARAMS OUTPUT;

-- 확인을 위한 쿼리

SELECT @STMT, @PARAMS

EXEC SP_CREATE_PLAN_GUIDE

@NAME = N'PLAN_GUIDE_TEST1',

@STMT = @STMT,

@TYPE = N'SQL',

@MODULE_OR_BATCH = NULL,

@PARAMS = @PARAMS,

@HINTS = N'OPTION (TABLE HINT (B, INDEX(NC_TBLX_02)))'

-- QUERY A END

-- 인덱스 힌트를 주고 해당 인덱스를 삭제할 경우

-- 플랜 가이드에 명시적으로 TABLE HINT 로 인덱스가 명시되어 있는데 없어지면 실행이 되지 않는다.

DROP INDEX TBLX.NC_TBLX_02

GO

DBCC FREEPROCCACHE

GO

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 1000000

GO

-- 동작하지 않는다.

SELECT *

  FROM SYS.PLAN_GUIDES A

 CROSS APPLY SYS.FN_VALIDATE_PLAN_GUIDE (A.PLAN_GUIDE_ID)

GO

-- 인덱스 만들면 잘 동작한다.

CREATE NONCLUSTERED INDEX NC_TBLX_02 ON TBLX (PRODUCTID)

GO

-- Index 'NC_TBLX_02' on table 'TBLX' (specified in the FROM clause) does not exist.

-- 라고 기록되며 실행되지 않는다.

-- 동일한 인덱스 이름을 만들어 주면 잘 동작한다.

-- 오류가 생기는 시점은 인덱스가 삭제되면 바로 발생한다.

-- 해당 테스트는   JOIN TBLX B WITH(INDEX(NC_TBLX_02)) 로 힌트를 주더라도 같다.

-- USE PLAN 에서 사용된 인덱스가 없어져도 무시하고 실행되는 경우는

-- 명시적 인덱스 힌트를 사용하지 않고 만들어진 쿼리플랜을 USE PLAN 으로 사용할 때

-- 인덱스를 지우면 FN_VALIDATE_PLAN_GUIDE 에 오류가 남고 실행은 된다.

-- 실행은 되나 플랜의 최 하단 이터레이터에서 속성을 확인하면, 플랜 가이드를 쓰지 않은것을 볼 수 있다.

-- 다른 테스트를 위해 플랜 가이드 제거

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST1

GO

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST2

GO

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST3

GO

DBCC FREEPROCCACHE

GO

--DROP INDEX TBLX.NC_TBLX_02

--GO

DECLARE @STMT NVARCHAR(MAX);

DECLARE @PARAMS NVARCHAR(MAX);

EXEC SP_GET_QUERY_TEMPLATE N'

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 1000000

 ',

    @STMT OUTPUT,

    @PARAMS OUTPUT;

EXEC sp_create_plan_guide

    N'PLAN_GUIDE_TEST2',

    @stmt,

    N'TEMPLATE',

    NULL,

    @params,

    N'OPTION(PARAMETERIZATION FORCED)';

GO

CREATE NONCLUSTERED INDEX NC_TBLX_02 ON TBLX (PRODUCTID)

GO

-- 아래 쿼리는 TBLX NC_TBLX_02 를 쓰는 실행계획이다.

-- 플랜 확인 할 것

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 1000000

GO

-- USE PLAN 을 이용해 PLAN GUIDE 생성하기

-- QUERY B START

DECLARE @SQL_XML_PLAN NVARCHAR(MAX)

DECLARE @STMT NVARCHAR(MAX);

DECLARE @PARAMS NVARCHAR(MAX);

SELECT @SQL_XML_PLAN =     

       CONVERT(NVARCHAR(MAX),SQP.QUERY_PLAN)

  FROM SYS.DM_EXEC_QUERY_STATS QS

 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) SQT

 CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(QS.PLAN_HANDLE) SQP

 WHERE TEXT LIKE '%SUM%SHOPID%PRODUCTID%'

   AND TEXT NOT LIKE '%SYS.DM_EXEC%'

EXEC SP_GET_QUERY_TEMPLATE N'

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 1000000

 ',

    @STMT OUTPUT,

    @PARAMS OUTPUT;

-- 확인을 위한 쿼리

SELECT @STMT, @PARAMS

SELECT @SQL_XML_PLAN

   SET @SQL_XML_PLAN = 'OPTION(USE PLAN '''+@SQL_XML_PLAN+''')'

EXEC SP_CREATE_PLAN_GUIDE @NAME =N'PLAN_GUIDE_TEST3'

   , @STMT = @STMT

   , @TYPE = N'SQL'

   , @MODULE_OR_BATCH = NULL

   , @PARAMS = @PARAMS

   , @HINTS = @SQL_XML_PLAN

-- QUERY B END

DROP INDEX TBLX.NC_TBLX_02

GO

-- 플랜 가이드를 무시하고, 클러스터 인덱스를 이용해 잘 실행한다.

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 0

-- 인덱스 삭제등의 문제가 있으면 아래 쿼리로 확인 가능하다.

SELECT *

  FROM SYS.PLAN_GUIDES A

 CROSS APPLY SYS.FN_VALIDATE_PLAN_GUIDE (A.PLAN_GUIDE_ID)

GO

-- 인덱스 만들어주면 다시 잘 동작한다.

CREATE NONCLUSTERED INDEX NC_TBLX_02 ON TBLX (PRODUCTID)

GO

-- 이번에는 플랜가이드를 사용할 것이다.

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 0

--Index 'DB_PLAN_GUIDE_TEST.dbo.TBLX.NC_TBLX_02', specified in the USE PLAN hint, does not exist. Specify an existing index, or create an index with the specified name.

-- 플랜가이드 제거

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST1')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST1

GO

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST2')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST2

GO

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST3')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST3

GO

-- 원본 쿼리의 힌트제거 방법

-- 아래와 같이 ISV 개발자가 임으로 HASH JOIN 을 기술해 개발해 놓았으나

-- 내 데이터베이스에서는 해당 힌트가 동작하지 않았으면 한다.  (무시하길 원한다.)

-- 단, 아래 방법은 FROM 절에 JOIN 힌트로 기술되어 있거나, 다른 JOIN 방법을 명시하는 것은 불가능 하다.

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 0

OPTION (HASH JOIN)

-- 1. SIMPLE PARAMETERIZATION 데이터베이스에서 테스트 하기 위해 개별 쿼리에

-- PARAMETERIZATION 을 활성화 한다.

DECLARE @STMT NVARCHAR(MAX);

DECLARE @PARAMS NVARCHAR(MAX);

EXEC SP_GET_QUERY_TEMPLATE N'

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 0

OPTION (HASH JOIN)

 ',

    @STMT OUTPUT,

    @PARAMS OUTPUT;

EXEC sp_create_plan_guide

    N'PLAN_GUIDE_TEST2',

    @stmt,

    N'TEMPLATE',

    NULL,

    @params,

    N'OPTION(PARAMETERIZATION FORCED)';

GO

-- 2. HINT 절에 NULL 을 기술해 힌트를 제거한다.

DECLARE @STMT NVARCHAR(MAX);

DECLARE @PARAMS NVARCHAR(MAX);

EXEC SP_GET_QUERY_TEMPLATE N'

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 0

OPTION (HASH JOIN)

 ',

    @STMT OUTPUT,

    @PARAMS OUTPUT;

-- 확인을 위한 쿼리

SELECT @STMT, @PARAMS

EXEC SP_CREATE_PLAN_GUIDE

@NAME = N'PLAN_GUIDE_TEST1',

@STMT = @STMT,

@TYPE = N'SQL',

@MODULE_OR_BATCH = NULL,

@PARAMS = @PARAMS,

@HINTS = NULL

-- QUERY A END

GO

DBCC FREEPROCCACHE

GO

SELECT SUM(A.SHOPID)

  FROM TBLX A

  JOIN TBLX B

    ON A.IDX = B.IDX

 WHERE B.PRODUCTID < 0

OPTION (HASH JOIN)

-- 플랜가이드 제거

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST1')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST1

GO

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST2')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST2

GO

IF EXISTS (SELECT * FROM SYS.plan_guides WHERE NAME ='PLAN_GUIDE_TEST3')

EXEC SP_CONTROL_PLAN_GUIDE N'DROP', PLAN_GUIDE_TEST3

GO