누가 정리해 놓은 김정선 강사님 강의내용 ~ 함 보셔요~
스터디에서 아래 내용을 자세히 함 파 드리겠습니다.~
-- 아래 -
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 적용 시 정상 작동)