[세미나후기] SQL Server 2008 향상된 Query Optimizing

삼성동 포스코 서관에서 진행된 SQL Server 2008의 향상된 옵티마이징 기능에 대한 세미나를 듣고 왔다.
2008... 나름 장족의 발전이 있었지만 아직 미흡한 점도 있는 것 같다.

1부. 향상된 혹은 새로운 Query Optimizing 기능

* 실행계획 표시정보 장,단점
  <장점>
    - 가독성 좋은 연산자
    - 풍부해진 속성 정보
    - 새로운 Optimizing 기술 정보
    - xml 실행계획
    - 확장된 모니터링 도구 
  <단점> 
    - 더 무거워진 부하
    - 사용불편
    - 정보부재 및 혼선 등의 단점도 존재

* SET STATISTICS IO의 SCAN COUNT
  - 테이블 'table_name' , 검색 수 211, 논리적 읽기 수 632 --> 2005
  - 테이블 'table_name' , 검색 수 2, 논리적 읽기 수 632 --> 2008
  같은 테이블에 대한 쿼리라도 2005와 2008에서 실행하였을때 검색 수가 상이하게 나오는데 왜 그런지는 알수없다는...관심끄자!!

* FORCESEEK table Hint
  - Table에 있는 인덱스를 seek로 찾으라는 힌트
  - 다중인덱스 사용제한 (인덱스 경합 발생시 효율적인 인덱스 선택하도록 함)
  - LIKE 혹은 IN 의 사용시 올바른 실행계획 제공
  - View에도 힌트적용하여 올바른 실행계획 제공

ex)
  SELECT * FROM dbo.Order
  WHERE OrderID <= (10247 +8) --> OrderID에 인덱스가 존재해도 Table Scan 일어남

  SELECT * FROM dbo.Order WITH (FORCESEEK) 
  WHERE OrderID <= (10247 +8) --> OrderID에 존재하는 인덱스에 Index Seek 로 실행계획 변경됨

  SELECT * FROM t WITH (FORCESEEK)
  WHERE i IN (@P1, @P2, @P3, @P4, @P5) --> IN에 변수가 와도 인덱스 사용

  SELECT * FROM t WITH (FORCESEEK)
  WHERE vc LIKE 'test%' --> 

  DECLARE @vc VARCHAR(100) 

  SELECT * FROM t WITH (FORCESEEK)
  WHERE vc LIKE @vc

* FORCESEEK - 권장사항
  - 힌트사용전
    1) 통계정보 존재 여부 및 확장성 검토(필요시 최신업데이트 후 재 확인, 제한적으로 사용)
    2) 선택도(예상행수) 및 비용 예측 오류 여부 검토 ex(지역변수, 다중문 테이블값 함수, 테이블 변수, TVP등)
    3) 상수화 유도 실패 여부 검토
  - INDEX 힌트와 조합은 주의  ( INDEX(0)와 사용하면 오류)
  - USE PLAN 쿼리 힌트와 함께 사용 시 무시

* 매개변수의 선택도 기준
  - 인덱스 컬럼 조회 시 특정 값에따라 예상행수가 달라질 경우 INDEX SCAN or INDEX SEEK 둘중 어느것으로 고정?
    1) OPTION (OPTIMIZE FOR UNKNOWN) 힌트를 적용하면 인덱스컬럼에 대한 평균 밀도를 측정 하여 실행계획을 고정하게 됨. 지역변수 사용
    ex) (@var VARCHAR(100)) SELECT * FROM T WHERE NAME = @var OPTION (OPTIMIZE FOR UNKNOWN)
    2) AD Hoc 쿼리
    3) UNION을 사용한 실행계획 분리

* Query Hint에서 Table HINT 허용
  - 모듈이나 패키지 프로그램 같은 소스수정이 불가능한 경우 고정된 실행계획을 Plan Guide에서 찾아 OPTION을 적용하여 실행계획을 변경하고자 할경우 사용할 수 있음.
    ex) SELECT * FROM t WHERE date <= '20000101' OPTION (TABLE HINT (om, INDEX(date)))

* 대량 INSERT 작업 시 트랜잭션 로그 최소화
  - 2008부터 테이블 인서트시 로그를 작게 남기는 것이 가능함.
    ex) INSERT INTO dbo.t WITH (TABLOCK) SELECT * FROM dbo.t_s

* Partitioning - 이력데이터 저장시 유용
  1) 파티션 인식 Seek 연산자 - Constant Scan 2005까지는 실행계획에 보이지만 2008부터는 파티션 정보가 물리적 테이블 정보와 함께 존재하여 보이지 않음
  2) 병렬처리
    - 하나의 쓰레드가 하나의 파티션에 할당 할 수 있음
    - 여러개의 쓰레드를 동시에 한파티션에 할당 할수 있음
   3) Lock Escalation 동작
    - 2005의 문제점은 하나의 파티션에 락이 발생하게 되면 다른 파티션에 대한 접근도 제한된다.
    - 2008은 이러한 문제점을 개선 하나의 파티션에 락이 발생하더라도 다른파티션 접근 가능함. 하지만 두개의 파티션에 락이 발생한후 서로 접근시도시 데드락 발생함.
    - 2008의 기본 모드는 TABLE 락으로 설정됨. 다음의 쿼리로 변경해주어야 함.
      ex) ALTER TABLE t SET (LOCK_ESCALATION = AUTO)

* Filtered Index - 인덱스 생성시 조건을 주어 생성이 가능함. 단 조건은 간단해야함
  1) 매개변수 쿼리 이슈
     - 컴파일시 매개변수에 어떠한값이 들어오는지 알수 없다. Filtered Index를 사용하지 못하는 경우 발생
      해결책 :  런타임시 리컴파일 옵션 힌트 사용, 쿼리 변경,  ADHOC쿼리

* Plan Freezing
  - Plan Guide 에 플랜해쉬값으로 실행계획을 고정시켜놓아 어떠한 값이 오더라도 해당 계획으로 실행하도록함.
    ex) EXEC sp_create_plan_guide_from_handle @name = '...', @plan_handle = 0x0600..., @statement_start_offset = ...
  - fn_validate_plan_guide 함수로 플랜에 대한 Plan Freezing 지정여부를 체크하여 유효성을 검사 할 수 있음. 
    인덱스 삭제시 에러메시지 나오도록 함

* Plan Cache (ad hoc)
  - 잘못된 Adhoc쿼리는 재사용율을 저하시키고 메모리 점유율을 높여 성능을 저하 시킴
  - 'optimize for ad hoc workloads' 서버 옵션을 적용하면 최초 한번의 ad hoc 쿼리에 대해 메모리 사이즈를 적게 잡고
    두번째 실행시 제대로된 쿼리 플랜을 생성하여 메모리에 올려놓는다.

* 유사쿼리 검색 기능 (왕눈이 프로그램 제공)
  SELECT TOP 200 qs.query_hash, query_plan_hash
  , count(*) as similar_query_count
  , sum(qs.total_worker_time)/sum(qs.execution_count)
  FROM sys.dm_exec_query_stats as qs
  GROUP BY qs.query_hash, query_plan_hash
  ORDER BY similar_query_count DESC

2부. 재미있는 이슈들

* Date 및 Time
  - 위 형에 대해서 좌변을 가공하여도 인덱스를 사용할수 있음
   ex) WHERE CONVERT(DATE, OrderDate) = '20090101'

* 쿼리 단순화 및 Constant Folding(상수축약) 이슈
  - WHERE절에 중복 범위를 폼함하는 조건이 온다면 Optimizer가 변환하여 올바른조건으로 수행
  ex) WHERE inum >100 and inum > 200 and inum = 0

* Partitioning - 파티션 제외 기능 이슈
  ex) SELECT * FROM t1 where a IN (@i1, @i2) --파티션 사용 불가
        SELECT * FROM t1 where a IN (50, 150) --파티션 사용 가능

* Index Ordered Scan VS Allocation Ordered Scan
  - Read Uncommitted(혹은 읽기전용) + 비정렬 시 결과 값이 틀리게 나올수 있음
   ex) SELECT ... FROM t WITH ( NOLOCK) 실행 시 Order By 절이 없고 조각이 심하게 발생 하게 되면 틀린결과값 가능성.

* Contradiction Detection 예외 이슈
  - 체크제약 조건을 준 컬럼에 대한 조회시에도 변수 값으로 쿼리를 만들게 되면 TABLE SCAN 발생
  ex) CREATE TABLE t ( ... , OrderFlag varchar(1) NOT NULL CHECK(OrderFlag IN ('1','2') ....)
        SELECT count(*) FROM t WHERE OrderFlag = '3' --> Table Scan 일어남

  - 매개변수 쿼리나 저장프로시저는 컴파일전 변수 값을 알수 없음

* OPTION (RECOMPILE) Hint - 만능조회에 사용시 효과.  
   ex) WHERE (OrderID = @OrderID  OR @OrderID IS NULL)
         AND (OrderDate = @OrderDate OR @OrderDate IS NULL)
         OPTION (RECOMPILE)
 로 사용하면 각 컬럼에 인덱스가 생성되어 있다면 인덱스 사용가능함.. 그전 버전에는 사용못하다가 SP1 + CU5 버전에서 해결함

-------------
이상으로 세미나 후기를 마침니다.
2008에 대한 유익한 세미나 였던거 같습니다. 다음 세미나가 벌써 기대가 되는군요~~
바로 적었는데도 기억이 잘 ..ㅋㅋ