[2k5] 실행 계획 캐싱 유형 데모

1. /**********************************************************************************************
   -- Title : [2k5] 실행 계획 캐싱 유형 데모
   -- Reference : mcpworld.com
   -- Key word : parameterization sp_executesql handle 핸들 prepared qeury exec
   **********************************************************************************************/
   ----------------------------------------------------------
   --1. Ad-hoc query
   ----------------------------------------------------------
   USE AdventureWorks;
   GO
   
   --기존 실행 계획 및 실행 버퍼 풀 초기화
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
   GO
   
   --Space 및 줄 바꿈에 대해서도 서로 다른 실행 계획 생성됨. (Case Sensitive, Space Sensitive)
   SELECT ProductID
   FROM Sales.SalesOrderDetail
   GROUP BY ProductID
   HAVING AVG(OrderQty) > 5
   ORDER BY ProductID;
   
   SELECT ProductID FROM Sales.SalesOrderDetail
   GROUP BY ProductID
   HAVING AVG(OrderQty) > 5
   ORDER BY ProductId;
   
   SELECT bucketid, cacheobjtype, objtype, objid, usecounts, sql FROM sys.syscacheobjects order by sql;
   
   ----------------------------------------------------------
   --2. Auto-parameterized 쿼리
   ----------------------------------------------------------
   USE AdventureWorks;
   GO
   
   /*
   SELECT is_parameterization_forced FROM master.sys.databases where name = 'AdventureWorks'
   GO
   --0 : Auto-parameterized 비활성화된 상태
   */
   
   --기존 실행 계획 및 실행 버퍼 풀 초기화
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
   GO
   
   SELECT ProductID, SalesOrderID, LineTotal
    FROM Sales.SalesOrderDetail
   WHERE ProductID = 707
   ORDER BY ProductID;
   GO
    
   SELECT ProductID, SalesOrderID, LineTotal
    FROM Sales.SalesOrderDetail
   WHERE ProductID = 966
   ORDER BY ProductID;
   GO
   
   --캐싱된 실행 계획 확인 : 각각의 실행 계획이 존재
   SELECT bucketid, cacheobjtype, objtype, objid, sql FROM sys.syscacheobjects order by sql;
   
   ------------------------------------------
   --현재 Database를 Auto-parameterized 로 설정
   ------------------------------------------
   ALTER DATABASE AdventureWorks
   SET Parameterization FORCED;
   GO
   
   --기존 실행 계획 및 실행 버퍼 풀 초기화
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
   GO
   
   SELECT ProductID, SalesOrderID, LineTotal
    FROM Sales.SalesOrderDetail
   WHERE ProductID = 707
   ORDER BY ProductID;
   GO
   
   SELECT ProductID, SalesOrderID, LineTotal
    FROM Sales.SalesOrderDetail
   WHERE ProductID = 966
   ORDER BY ProductID;
   GO
   
   --캐싱된 실행 계획 확인 : objtype가 Prepared 인 Compiled Plan이 존재
   SELECT bucketid, cacheobjtype, objtype, objid, sql FROM sys.syscacheobjects order by sql
   GO
   
   --원상 복구
   ALTER DATABASE AdventureWorks
   SET Parameterization SIMPLE;
   GO
   
   ----------------------------------------------------------
   --3. sp_executesql 쿼리
   ----------------------------------------------------------
   USE AdventureWorks;
   GO
   
   --기존 실행 계획 및 실행 버퍼 풀 초기화
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
   GO
   
   --실제 실행 계획(CTRL+M)  표시후 다음을 수행
   EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber
   FROM Production.Product p
   INNER JOIN Production.ProductDescription pd
   ON p.ProductID = pd.ProductDescriptionID
   WHERE p.ProductID >= @a', N'@a int', 170
   GO
   --Merge Join 수행
   
   EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber
   FROM Production.Product p
   INNER JOIN Production.ProductDescription pd
   ON p.ProductID = pd.ProductDescriptionID
   WHERE p.ProductID >= @a', N'@a int', 900
   GO
   --Merge Join 수행
   
   SELECT bucketid, cacheobjtype, objtype, objid, sql FROM sys.syscacheobjects order by sql;
   GO
   
   --기존 실행 계획 및 실행 버퍼 풀 초기화
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
   GO
   
   EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber
   FROM Production.Product p
   INNER JOIN Production.ProductDescription pd
   ON p.ProductID = pd.ProductDescriptionID
   WHERE p.ProductID >= @a', N'@a int', 900
   GO
   --Nested Loops Join 수행
   
   EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber
   FROM Production.Product p
   INNER JOIN Production.ProductDescription pd
   ON p.ProductID = pd.ProductDescriptionID
   WHERE p.ProductID >= @a', N'@a int', 170
   GO
   --Nested Loops Join 수행
   
   ----------------------------------------------------------
   --4. Prepared 쿼리
   ----------------------------------------------------------
   USE AdventureWorks;
   GO
   
   --기존 실행 계획 및 실행 버퍼 풀 초기화
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
   GO
   
   Declare @HD int  --Handle 값을 저장
   Exec sp_prepare @HD output,
     N'@a int',
     N'SELECT p.ProductID, p.Name, p.ProductNumber
   FROM Production.Product p INNER JOIN Production.ProductDescription pd
   ON p.ProductID = pd.ProductDescriptionID
   WHERE p.ProductID >= @a'
   Select @HD as Handle
   
   --핸들 값이 1인 경우
   Exec sp_execute 1,900;
   Exec sp_execute 1,800;
   Exec sp_execute 1,700;
   
   --캐싱된 실행 계획 확인 : objtype가 Prepared 인 Compiled Plan이 존재
   SELECT bucketid, cacheobjtype, objtype, objid, sql FROM sys.syscacheobjects order by sql
   GO
   
   ----------------------------------------------------------
   --7. EXEC
   ----------------------------------------------------------
   USE AdventureWorks;
   GO
   
   --기존 실행 계획 및 실행 버퍼 풀 초기화
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
   GO
   
   Declare @a int
   SET @a = 170
   EXEC ( 'SELECT p.ProductID, p.Name, p.ProductNumber
   FROM Production.Product p INNER JOIN Production.ProductDescription pd
   ON p.ProductID = pd.ProductDescriptionID
   WHERE p.ProductID >= ' + @a)
   GO
   
   Declare @a int
   SET @a = 900
   EXEC ( 'SELECT p.ProductID, p.Name, p.ProductNumber
   FROM Production.Product p INNER JOIN Production.ProductDescription pd
   ON p.ProductID = pd.ProductDescriptionID
   WHERE p.ProductID >= ' + @a)
   GO
   
   --캐싱된 실행 계획 확인 : 각각의 실행 계획이 생성된 것을 확인
   SELECT bucketid, cacheobjtype, objtype, objid, sql FROM sys.syscacheobjects order by sql
   GO