-- 아래 -

microsoft mvp 김정선 강사님이 발표한 세미나 입니다.
블로그에서 보던 대로 후덕한 이미지와 구수한 입담이 아주 재미졌습니다.

1부. 향상된 혹은 새로운 Query Optimizing 기능
 - 실행계획 표시 정보 풍부
  2000, 2005, 2008로 진화 하면서 점점 더 많은 정보를 포함하기 때문에 프로파일러 확인 시 반드시 실행계획 보기를 끄고 측정 해야합니다.

 - 검색수(SCAN COUNT, 인덱스, 테이블 엑세스 카운트)
  현재까지 2008의  검색수는 비정상적으로 나옴, 신경 끄도록 하자!!
 
 - FORCESEEK 힌트
  조건절에 다음과 같이 조회할 경우, 2008 이전에는 인덱스를 탈 수 없었다.
  WHERE OrderID <= (1111+3)
  하지만 2008 부터 FORCESEEK 힌트는 사용가능한 임의의 인덱스에 대해 SEEK 로 탐색 가능

  SELECT *
  FROM dbo.order WITH (FORCESEEK)
  WHERE OrderID <= (1111+3)
  -> IN, LIKE 검색 시 또는 뷰테이블에 대해서도 SEEK 검색 할 수 있도록 도와줌..
      아직까지 특정 인덱스에 대해 FORCESEEK를 타도록은 할수 없다.(옵티마이저 맘대로..)

 - 조건절 매개변수의 선택도 기준
  매개변수로 조회할 경우 런타임 이전에는 변수값을 알수 없기 때문에 통계정보를 이용할 수 없었다.

  로컬변수와 OPTION ( OPTIMIZE for UNKNOWN ) 힌트 사용 시 통계정보를 이용하여 실행계획 작성
  DECLARE @0 VARCHAR(100) --로컬변수 사용해야함!!
  SELECT * FROM ORDER WHERE CUSTOMERID = @0 OPTION ( OPTIMIZE for UNKNOWN )
  ※DBCC FreeProccache : 실행계획 캐쉬 초기화

 - Query hint에서 Table Hint 허용, 2008부터 지원. 좋다 ^^
  SELECT *
  FROM ORDERS
  WHERE ORDERDATE <= '20090101'
  OPTION (TABLE HINT(ORDERS, INDEX(ORDERDATE)))

exec sp_create_plan_guide
@name='tbhint_4',
@stmt='SELECT o.OrderID,od.OrderID,o.OrderDate,o.EmployeeID FROM Orders o JOIN [OrderDetails] od ON o.OrderId=od.OrderId WHERE o.EmployeeID=1',
@type='SQL',
@module_or_batch=null,
@params=null,
@hints='OPTION (TABLE HINT(o,FORCESEEK), TABLE HINT(od,FORCESEEK))';
go

exec sp_control_plan_guide 'ENABLE','tbhint_4'

SELECT o.OrderID,od.OrderID,o.OrderDate,o.EmployeeID FROM Orders o JOIN [OrderDetails] od ON o.OrderId=od.OrderId WHERE o.EmployeeID=1

OPTION (TABLE HINT (table_alias1, hint1), (table_alias2, hint2)...)

 - Plan Guide 에서 Tabie Hint 활용
  Plan Guide 첨 들어본 녀석.. 요점인 즉은 Plan Guide 에 특정 쿼리에 대한 실행계획에 영향을 미칠 요소들을 등록 후 런타임 시 가이드 역할을 함
  EXEC SP_CREATE_PLAN_GUIDE
  @name = N'Guide3', -- 플랜 명
  @stmt = N'SELECT *
  FROM dbo.Orders AS om
  WHERE OrderDAte <= ''19970101'';',
  @type = N'SQL',
  @module_or_batch = NULL,
  @params = NULL,
  @hints = N'OPTION (TABLE HINT (om, INDEX(OrderDate)))'; --테이블 힌트 적용

 - 대량 INSERT 작업 Tx Log(트랜잭션 로그) 최소화
  INSERT INTO dbo.t_heap WITH (TABLOCK) -- 트랜잭션 로그 기록 생략,
  SELECT * FROM dbo.t_source

 - Partitioning - 파티션 인식 Seek 연산자
  2005의 경우 Constant Scan이라는 파티션 위치를 찾는 연산자가 선행 됐지만
  2008은 Constant Scan 생략(파티션 정보를 어딘가에서 저장하고 있다나.. 잘 모르겠심)

 - Partitioning - Lock Escalation 동작
  2005, 2008 디폴트는 테이블 (LOCK_ESCALATION = TABLE) 이다.
  테이블 업데이트 시 전체 테이블 락 발생..
  2008에서 ALTER TABLE mytable SET (LOCK_ESCALATION = AUTO), 특정 파티션에만 락이 발생.

 - Filtered Index - 매개변수 쿼리 이슈
  특정구간만 인덱스 사용하고자 할 경우 사용하며 
  예) NULL이 많은 컬럼의 경우 유용 할듯.. 
  현재(2009.12,08)까지 AND, OR, IN  연산자들 중복사용은 할수 없음
  매개변수나 프로시저에서 사용 시 UnmatchedIndexes 발생
  
  CREATE NONCLUSTED INDEX NC_PRODUCT_ACC
  ON PRODUCTITON.PRODUCT(PRODUCTSUBCATEGORYID)
  WHERE PRODUCTSUBCATEGORYID >= 27
    AND PRODUCTSUBCATEGORYID <= 30

 - Plan Freezing
  특정 쿼리의 실행계획 고정 하는 방법, 자세히 하는 방법 웹에서 검색해 보시라. ㅜㅜ
  Exec sp_create_plan_guide_from_handle
    @name = '...' -- 오브젝트명
    @plan_handle = 0x06000E... -- 실행캐쉬 메모리 주소
    @statment_start_offset = ...

 - Plan Guide 보기 
  SELECT msgnum, serverity, state, message
  FROM sys.plan_guides
  CROSS APPLY fn_validate_plan_guide(plan_guide_id)

 - Plan Cache 오용고찰
  adhoc 쿼리 수,  재사용안된 adhoc 쿼리 수를 통해 부분별한 adhoc 쿼리 발견 할 수 있다.
  ->매개변수 쿼리로 수정해야만 함
  adhoc 쿼리 수
   select objtype, count(*)
   from sys.dm_exec_cached_plans
   cross apply sys.dm_exec_sql_text(plan_handle) 
   where cacheobjtype = 'compiled plan'
   group by objtype

  재사용안된 adhoc 쿼리 수
   select objtype, count(*)
   from sys.dm_exec_cached_plans
   where cacheobjtype = 'compiled plan' and usecounts = 1
   group by objtype

 - optimize for ad hoc workloads 서버 옵션, 2008 부터 지원
  adhoc 쿼리가 최초 실행될 경우 compiled plan stub, 2번째 사용될 경우 비로소 plan cache에 기록

 - 유사쿼리 검색기능, 왕눈이(김정선 작품) 깔면 유사 기능 포함되어 있음
  SELECT TOP 5 query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text",
    COUNT(*) "similar_query_count"
  FROM
    (SELECT QS.*,
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
  GROUP BY query_stats.query_hash
  ORDER BY 4 DESC;

2부. 재미있는 이슈들
 - Date 및 Time - 흥미로운 동작
  where convert(DATE, OrderDate) = '20020101'; -> DATE로 형변환 하는 좌변 가공의 경우 인덱스 처리 됨

 - Index Ordered Scan vs Allocation Ordered Scan
  클러스터 인덱스가 있는 테이블의 경우,
  select * from order; 와 같이 검색할 경우 등록순서대로 검색
  with(nolock) 으로 검색 시 할당 순서로 액세스가 되며 조각화가 심할 경우 데이터 무결성 보장하지 못함

 - OPTION (RECOMPILE) hint
  만능쿼리 조회 시 아래와 같이 조회 할 경우 런타임 전에는 매개변수의 값을 알수 없으므로 정상적인 인덱스를 탈수 없다.
  where (orderid = @orderid OR @orderid is null)
    and (customerid = @customer OR @customerid is null)
    and (employeeid = @employeeid OR @employeeid is null)
  OPTION(RECOMPILE) -- 런타임전에 매개변수 값으로 컴파일 실행, 런타임 시 컴파일(SP1 + CU5 적용 시 정상 작동)