MSSQL - 중급교육 #3

 

* MSSQL 실행 순서 : 쿼리문 -> 컴파일 -> 통계정보 -> 실행계획 -> Seek(OLTP) or Scan(OLAP) 실행

* WHERE 첫 번째 조건은 무조건 "=" 조건이 되도록 

  . SARG 조건

  . WHERE 구문에 맞게 Index 생성 (오름차순, 내림차순도 동일하게 적용)

  . 사용자 정의 함수 X

  . 로컬 변수, 로컬 테이블 X (SP 파라미터로 적용 : SP에서 SP 호출 방식 적용)

  . OLTP : Hash Match 나오는 경우 튜닝 해야 함

  . OLAP : Hash Match (대용량, Index 사용 못함)

7장. 고급 쿼리 튜닝

. JOIN

  - Nested Loop Join (OLTP성 조인)

  - Hash Match Join, Merge Join (OLAP성 조인)

  - 외부 입력이 먼저 실행되고 내부 입력이 다음에 실행 된다

  - 2개의 테이블 중 선택도가 좋은 지에 따라 "외부 입력" 지정 (인덱스 사용 가능, select 되는 행 수가 적은 테이블)

  - "내부 입력"은 반드시 인덱스 사용 가능 (Index Seek)

  - "외부 입력" --> "검색 테이블" (선택도 좋아야 함)

  - "내부 입력" --> "조인 테이블" (인덱스 조인)

  - 조인 시 "내부 입력" 테이블의 컬럼은 변형하지 말아야 한다 (반드시 Index Seek 해야 성능이 좋아진다)

  - 집합 연산자는 거의 대부분이 중복 제거 된다고 생각하면 된다

  - JOIN 시 한번에 2 테이블 씩 처리 (예 10개 테이블 10! = 3,628,800 가지) 

    . join 테이블이 많을 수록 성능 저하의 요인 (실행 계획 수립 과정 적정?한 선택)

  * (핵심) 초기 테이블의 외부, 내부 입력 선정

  * 조인 분리 : 임시 테이블 사용 고려

  * 비정규화 고려

. OUTER JOIN

  - 불필요한 OUTER JOIN 사용하지 말자

. Hash Match Join ("외부 입력", "내부 입력" 그대로 유지)

  - 인덱스 유무 확인 필요

. Merge Join

  - 데이터가 "정렬"되어 있어야 한다

  - 클러스터 인덱스가 있어야 한다 또는 커버링 인덱스가 있어야 한다

  - Sort 하고 Merge 처리 : Sort Merge (가끔씩 발생) 

. Adaptive Join (적응형 조인)

  - 실행 계획에 "논리적 연산 : 내부 조인" 표시

  - 실행 계획에 재사용 부작용 --> 쿼리 분리 처리 (옵티마이저에서 자동 처리)

  - OPTION(USE HINT(...))

. Semi Join 과 Anti Semi Join

  - Semi Join (교집합)

    . WHERE aaa in (selesct aaa from ...)

  - Anti Semi Join (차집합)

    . WHERE aaa not in (selesct aaa from ...)

    . 모델링에서 고려해야 함

    . 가장 어려운 튜닝 조건

8장. Hint

. Join Hint

  - INNER {...} JOIN : 왼쪽 테이블이 "외부 입력"

  - OPTION (... JOIN)

  - 반드시 필요한 경우 사용

  - 사용 시 왜 사용하는 지에 대한 주석으로 설명을 추가 한다

9장. Subquery

(예)

  - and exists (select * from ....)

  - and 중분류 = (select top 1 중분류 from ....)

. Join <--> Group by

  - Group by 먼저 해야 할 경우는 조인(집계)열의 밀도가 높은 경우

  - join (....) b on a.aa = b.bb : (....) 안에 있는 쿼리는  a.aa = b.bb 조건을 내부적으로 미리 적용된다

. ROW_NUMBER()

  - 페이징 쿼리에 많이 사용

  - ver 2012 이상 : ORDER BY ... DESC OFFSET ... ROWS FETCH NEXT .. ROWS ONLY (사용 권장)

  - 검색 조건별 인덱스 생성해서 사용하는 것이 좋음

  - 첫번째 페이징 정적 쿼리문 작성, 이후 페이지는 동적 쿼리문 작성 (예)

. 차집합 구하기 (참조)

  - nullif() 함수 : null 값 활용 : select 1 / 0 <-- nullif()

   (예)

    . ISNULL(E_PLAYER_NAME,'HELLOWORLD') ISNULL값,      /*E_PLAYER_NAME이 널이면 HELLOWORLD 출력*/

    . NULLIF(TEAM_ID,'K10') K10이면NULL,                /*TEAM_ID가 K10이면 NULL*/

    . COALESCE(E_PLAYER_NAME,NICKNAME)           /*널 아닌 최초값 출력*/

. OLTP 기분 병렬 쿼리

  - 병렬 처리 퀴리는 OLAP성 쿼리

  - 병렬 처리 퀴리 2개 이상의 CPU를 사용한다

  - OPTION (MAXDOP 0) : CPU 사용수 (0 --> 병렬 전체 사용, 1 --> 직렬 처리, 4 --> 병렬 최대 4개)

. STRING_AGG (...) : "1,2,4,7,4,5,7,,...." 문자열 만드는 함수

  - ver 2017 이상

. 함수

  - 실제 실행 계획에서 "QueryTimeStats" 속성 아래 "UdfCpuTime", "UdfElapsedTime" 속성 참조

  - ver 2019 이상 함수 쿼리가 자동으로 튜닝 처리 해준다. (Scalar UDF InLine 옵션 적용 시)

  - "인라인 스칼라 Tsql Udf 포험 : True"

  - STRING_SPLIT() : ver 2016 이상

10장. 저장 프로시저 튜닝

. 권장 세션 옵션

  - SET LOCK_TIMEOUT

  - SET NOCOUNT ON

  - SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

. sp_ 접두사는 금기

11장. 성능 문제 및 튜닝 방안

. OPTION (RECOMPILE)

. OPTION (OPTIMIZE FOR (@aaa {'값'})) 

  

MSSQL - 중급교육 #2

 

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 ...

MSSQL - 중급교육 #1

 

. 인프런 온라인 교육 : https://www.inflearn.com

. 행 수 추정 (Cardinality Estimation)

  - 집합의 원수 (데이터 레코드)

  - 처리 예상 행 수 추정 (잘 할 수 있도록 하는 것이 쿼리 튜닝이다)

  - 밀도 : 열의 유일 값 

    . 1 / 열의 유일 값 개수 = ? (값이 1에 가까울 수록 중복 데이터가 많은 것)

  - 선택도 : 

    . 결과 행 수 / 테이블 행 수 = ? (값이 작을 수록 선택도가 좋다)

. OLTP (트랜젹션용 작업용 쿼리) : 빠른 응답 속도 목표, 단일 CPU 사용 충분, Index Seek

. OLAP (통계 및 대용량 작업 쿼리) : 전체 처리 성능 목표, 병렬 처리 필요, 하드웨어 성능, Index Scan

. 2014 버전 이상 사용 권장 (미만 버전 성능 버그가 많음)

. 내부 함수 사용 시 행 수 추정이 좋지 않은 경우가 많음

. 쿼리 튜닝 : 인덱스 튜닝, 쿼리 자체 튜닝, 리소스 대기 튜닝 (CUP, RAM, 네트워크, ...)

. SET STATISTICS PROFILE ON

. SET STATISTICS XML ON

. SET SHOWPLAN_XML ON

. 물리적(논리적) 연산자 : 논리적 연산자 --> (내부 조인), (Aggregate), ...

. 논리적 연산자는 최종 적용된 연산자 정보 표시

. "예상" 비용 : 좋은 쿼리 나쁜 쿼리라는 판단하는 것은 문제가 있다. 성능과는 무관하다.

. 예상 행 수가 다르면 예상 비용도 다르게 된다.

*** 실행 계획 (튜닝) ***

. 실제 행 수 <--> 예상 행 수 : 차이가 적어야 한다 (OLTP or OLAP 선택 조건)

  - 복합 인덱스 튜닝 부족

  - 열 통계 관련 문제

  - SARG 위반 등의 SQL 쿼리 자체 문제

  - 기타

. (예) IO 용량 = 논리적 일기 수 6 * 8K

. 쿼리 옵티마이저가 사용하는 내부 임시 테이블 : Worktable, Workfile (사용되지 않도록 해야 함)

. RID Lookup + Join (** 중요)

. 실제 읽기 --> 논리적 읽기로 생각하면 됨

. 인덱스 쓰는 손익분기 점 기준은 1% 내외 (예) 1.325% 이하 (고정 아님) 

  (Table Scan (Index Scan) --> Index Seek (Lookup))

. 클러스터 인덱스 컬럼의 필드 업데이트 할 경우 insert, delete 함께 발생

. 클러스터 인덱스 선택 기준 - 트랜잭션 성능을 위한 후보 열 (OLTP성 테이블의 열)

  - 등록, 수정, 삭제 등의 작업을 빠르게 하는 작업

. 주문일자

. 주문일자 + 순번

. Covering Index (Key Lookup이 발생하지 않는 경우)

. 넌클러스터 인덱스 생성 할 경우 (넌클러스터 인덱스 , 클러스터 인덱스 로 생성된다)

  (UnitPrice --> UnitPrice, OrderID, ProductID)

. Key Lookup + Join (넌클러스터 인덱스의 성능을 좌우한다)

. Index 또한 테이블이다 (일반 테이블의 종속 데이블이 된다)

. Index에 필요한 필드가 포함되면 Key Lookup이 발생하지 않는다.

. key columns : 정렬 대상

. non-key columns : 정렬 대상 아님, 필더링 대상 아님 : INCLUDE (non-key column1, ...)

  - 단순 Select만 하는 필드

. WHERE : 첫번째 = 조건이 오도록

. WHERE 사업장 LIKE @a  --> 사업장 = @a   or 사업장 in (@a)

. 조건자

. Seek 조건자 : 필드명이 보여야 한다

. select + update : 인덱스 적용된다.

. select + delete : 인덱스 적용된다.

SQL - 초급 교육

 

. (주)씨퀄로 : http://www.sqlroad.com

. SW개발 : http://www.sqlroad.com/080819-2/ (SQLBigEyesHammer V3 Freeware)

. 은행쿼리 : 22GB -> 0.1MB (218,000배 향상) : where : 함수 X

1. 실행계획 (인덱스 사용 여부)

2. 세션옵션 (IO) (테이블 단위 페이지 IO량 확인)

. 실제 실행계획 : 검색, seek (OK)

. 논리적 읽기, lob 논리적 읽기

. lob (Large Object) : text, image, ....

. ANSI 표준 사용하기

. WHERE 절 작성 순서는 성능과 연관?

   - 옵티마이저가 변경하기 때문에 순서는 성능과 무관하다.

   - inner join 일 경우에 해당

. 쿼리문 작성 시 테이블 이름 선언할 때 전체 이름으로 (예 db.schema.object)

   - schema 생략 시 옵티마이저가 ID 찾아 사용

. 날짜 그 날 마지막 시간 20211010 23:59:59.997

. between '20211010' and '20211010 23:59:59.99?'

. >= '20211010' and < '20211011'

. 문자열 비교 시 오른쪽 공백은 고려하지 않는다 (rtrim 안해도 된다)

  - @varchar, @char

. MSSQL 다른 DB와 다른 것은 잠금(Lock) 부분이 다르게 적용됨

. 의미 오류 (Semantic Error) : 불필요한 조건

  - WHERE PK_Col IS NULL

  - WHERE ShipVia > 4 OR ShipVia > 2  (false)

  - DISTINCT PK_Col, ....

  - LIKE 'aaa'

  - EXISTS (SELECT DISTINCT.... FORM ...   무조건 1개만 있으면 작업 끝난다)  

  - ORDER BY PK_Col 

  - LIKE '%'

  - HAVING (WHERE에 있어야 할 조건 경우)

  - select 50 orderid from orders (별칭 붙이는 습관이 있어야 함)

  

2. SARG (검색인수)

. WHERE, FROM, HAVING : TRUE, FALSE, UNKNOWN

. 검색 대상 및 범위를 제한할 수 있는 식

. 인덱스 사용 및 쿼리 최적화를 위한 필요 조건

. 반대적인 조건 사항 : Non-SARG (비 검색인수)

  - 불필요한 데이터 (열,행) 요청

  - 열 간 비교 (컬럼 대 컬럼)

  - 부정문은 긍정문으로 자동 변환 (범위 검색이 된다)

  - ID IN (2,4,6) --> ID = 2 or ID = 4 or ID = 6

  - NOT IN (가능한 사용하지 말자)

  - 복합 Index 선행 열 ( "=" 조건이 제일 먼저 나오게 해야 한다)

     ex. PK = (사업장 + 주문일자 + 주문번호)

          사업장 LIKE '%' and 주문일자 = '20211011'     

(예)

PK (사업자, 일자, 번호)

WHERE 일자 = ?

         사업자 LIKE ?

         번호 = ?

사업장 만 Seek, 나머지는 스캔 (인덱스 사용 못 함)

*** 쿼리 스토어 기능 권장 (성능관리) ver 2017 이상

 

. WHERE 함수 (가급적 사용 X)

. WHERE SUBSTRING(CustomerID, 1, 3) = 'CEN' --> WHERE CustomerID LIKE 'CEN%'

. WHERE CONVERT(varchar(8), OrderDate, 112) = '19960704' --> WHERE OrderDate >= '19960704' AND OrderDate < '19960705'

. WHERE DateDiff(dd, ShippedDate, '19980506') <= 1 --> WHERE ShippedDate >= DATEADD(dd, -1, '19980506')

. MSSQL은 NULL 값 인덱스 값으로 저장된다. 

. WHERE ISNULL(OrderDate, '19970702') = '19970702' --> WHERE (OrderDate = '19970702' OR OrderDate IS NULL)

*** ORM 파라미터 : WHERE col = @id 대부분 유니코드('N')로 인식 함.  (예) NVARCHAR 

. LIKE : 문자열에만 사용, %???% (앞에 % 있으면 안된다)

. 열 간 비교

   - select emlid, ship from orders where emlid = ship and ship = 2

   - where emlid = case @ship is null then emlid else @ship end

. OR 인덱스 안 걸리면  --> UNION 으로 변경

3. 조건절 상수화 이슈

. WHERE OrderID > @ID 

  - 상수화 할 수 있어야 옵티마이저가 실행계획을 예측 가능하게 된다.

  - 쿼리 컴파일 시점에 @ID 값을 알아야 하기 때문

  - 문제되는 조건

     . 로컬변수 declare @id int .... where OrderId > @ID

     . 사용자 정의 함수 where OrderId > dbo.uf_OrderNo()

     . 테이블 변수 성능 문제 (@table --> #Table 로 사용)

  - MSSQL 2019 이상 EE "Scalar UDF InLine" 자동 튜닝 지원 (아직 시작 단계. 안정화 진행 중)

  

  - 집합연상 WHERE aaa in (select distinct.....) distinct 할 필요 없다. 중복 자동 제거 됨.

  - where a.ordrid = dbo.uf_split('1,2,3,4,..') -->  join order as a on a.ordrid = dbo.uf_split('1,2,3,4,..')

  - NOLOCK 사용 시 ORDER BY 생략 시 문제 발생할 수 있음 (데이터 정합성 고려)

  - ORDER BY 작성 시 유니크한 값이 나오도록 조건을 만들어야 한다.

     . select top 5 .... ORDER BY aaa, bbb, ccc....

  - count(*) vs count(열) --> null 값이 허용된 경우 count(열)

  - count(*) vs exists --> 데이터 존재 여부 경우 exists

  - 집계 시 대량의 null 이 있을 경우 is not null 처리

  - union --> 자동으로 distinct 처리하는 결과로 처리 (옵티마이저 판단)

  - union all --> 전체 데이터 처리

  - 채번을 원 쿼리로 : update 채번 set @일련번호 = 일련번호 = 일련번호 + 1 where ....

  - OUTPUT inserted.*, deleted.*

4. JOIN

(1) 네스티드 루프 조인

(2) 해쉬 조인

(3) 머지 조인

- 네스티드 루프 조인

  . 첫번째 검색 테이블 데이터가 적어야 함. (inner join 인 경우)

  . 데이터가 많은 FK에 값 convert 할 경우 --> 첫번째 PK 값을 convert 하는 것이 좋다.

  . outer join 시 옵티마이지 처리에 어려울 수 있다. 테이블 순서는 상관없다.

  . 나름 기준을 잡아서 테이블 순서를 정하자. (필수 아님)

5. Subquery 이해

. 파생 테이블 : 외곽 테이블의 key 값이 자동으로 안쪽 파생 테이블 필터링 조건에 적용 된다.

. case select... (X) --> select case (O) 

. null 값이 없도록 : where customerID not in (select customerID from orders where customerID is not null)

. CTE

  - 성능 튜닝 어렵다

. 쿼리 힌트

. 잠금 튜닝

  - 조회 전용 쿼리 시 잠금 차단 회피

     . table WITH (NOLOCK) : 수정 중인 데이터 값을 가지고 온다. (권장 안함 X. 수정후 데이터를 넘긴다)

     . Read Committed Snapshot 사용 권장 O (내부적으로 데이터를 스냅샷 하여 그 데이터를 넘겨준다)

         - TempDB 사용할 시 때문에 해당 TempDB 관리 필요.

6. Cursor : 되도록 운영을 위한 사용 하시고 서비스용은 최대한 사용하지 말 것

   - 사용한다면

     . LOCAL

     . FAST_FORWARD or STATIC READONLY FORWARD_ONLY

7. 함수

  - InLine Table-Values Function (권장)