4장. 열 통계(STATISTICS)
- 인덱스 생성 시 통계 정보 자동 생성 (FullScan)
. (a,b,c,..) "a" 첫번째 필드 정보에 대해서만 통계 생성
- 비 인덱스 경우 쿼리 실행 시 통계 정보 자동 생성 (Sampling 일부 데이터로)
SELECT *
FROM sys.stats st
WHERE st.object_id = OBJECT_ID('myOrders')
-- 통계 내용 확인
DBCC SHOW_STATISTICS(myOrders, NC_myOrders_OrderID)
- 통계 보기
. Steps (200) : 막대기 수, 최대 200개, null 추가 시 201 (수가 적을 경우 예상 행 수 추정에 문제)
. All density : 열 밀도 (0에 가까울 수록 유니크한 값이 많다. 1에 가까울 수록 중복 데이터가 많다)
. EQ_ROWS : RANGE_HI_KEY 본인 값에 해당 되는 레코드 수
. DISTINCT_RANGE_ROWS : 앞에 있는 값에서 해당 값까지 생략된 레코드 수 (중복 제거)
. RANGE_ROWS : 앞에 있는 값에서 해당 값까지 생략된 레코드 수
. AVG_RANGE_RIWS = RANGE_ROWS / DISTINCT_RANGE_ROWS
. 가정법 : 열 통계 정보에 없는 데이터는 어떻게 추정하느냐? (마지막 데이터 < ?)
- ver 2014 이상 : 예상 행 수가 많은 것으로 처리 된다. (seek --> scan)
. 행수 추정 방해 요소 : 로컬변수 DECLARE @.... : col <= @... , col = @...
. 평균 선택도 = Rows * All density (col = @...)
. 오늘 기준 데이터는 통계 정보에 없기 때문에 예상 행 수 N건으로 처리
. 통계 정보 업데이트 재 생성(계산)해야 한다
. 통계 정보 업데이트 생성을 매일 1~2번은 재 계산 할 수 있도록 한다.
. update statistics 통계명
. alter database {db명} set auto_update_statistics {on|off}
. BTS 예매 시 대용량 데이터 발생 시 최종 티켓 판매 수 값을 예상 행 수 추정이 어렵지만 ver 2014 이상 일 경우 N건 처리
5장. 잠금 (Lock) 및 차단 (Blocking)
. update
- select -> update 진행
- 테이블 및 인덱스도 잠금
. READ UNCOMMITTED ( = NOLOCK) : Dirty Read (수정 중인 데이터 읽기)
. 행 단위 잠금
. COM+ : 기본적으로 Serializable 격리 수준 부하 (대량 DeadLock 발생)
. new TransactionScope() : 분산 처리, 무겁다, 기본 Serializable 격리 수준 (변경해서 사용 할 것)
. NOLOCK : Dirty Read + Index Allocation Scan 발생 시 쿼리 결과 정합성 이슈 주의
. 정합성 이슈 : NOLOCK 사용 시 ORDER BY 생략하고 대량의 데이터 변경 시 데이터를 인식 못하거나 동일한 데이터가 나타나는 현상 발생
. Lock Escalation : 행 단위 lock이 많을 경우 테이블 lock 처리
6장. 기본 쿼리 튜닝
. SARG (Search ARGument, 검색인수)
. 정적 데이터는 케쉬 사용 권장
*** 프로시저문 (인덱스 적용이 가능하도록)
WHERE (OrderID = @OrderID OR @OrderID IS NULL)
WHERE OrderID = COALESCE(@OrderID, OrderID)
OPTION (RECOMPILE) -- PEO
select *
from qqqq ....
INNER JOIN uf_aaa('1,2,3,4,3,5,2') -- distinct 안됨
WHERE aaa in (select val from uf_aaa('1,2,3,4,3,5,2')) -- 자동 distinct 됨
GROUP BY (1) DIFF... (2) YEAR(... and MONTH(... (3) LEFT(... : 성능이 좋은 순서
Hash Match : Index 사용 못하는 경우, 대용량 인 경우 (OLAP성 쿼리)
Hash Match는 OLTP성 쿼리에서 나오면 안됨 (인덱스 확인해야 함, 예상 행 수 문제,...)
UNION <--> OR <--> IN
... AND (CASE aa THEN 'C' THEN b.bbb ELSE c.ccc END) = 'aaaaa'
--> ... AND aa = b.bbb ... UNION ... AND aa = c.ccc ...
