개기획자

1. 트랜잭션이란
    1. 데이터베이스 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 또는 한꺼번에 모두 수행되어야할 일련의 연산들
2. 특징
    1. 데이터베이스 시스템에서 병행 제어 및 회복 작업 시 처리되는 작업의 논리적 단위
    2. 사용자가 시스템에 대한 서비스 요구 시 시스템이 응답하기 위한 상태 변환 과정
    3. 하나의 트랜잭션은 commit 되거나 rollback 됨
3. 트랜잭션의 성질(ACID)
    1. Atomicity 원자성
        1. 트랜잭션의 연산은 데이터베이스에 모두 반영되던지 아니면 전혀 반영되지 않는다
        2. 트랜잭션 내 모든 명령은 반드시 완벽히 수행되어야 하며, 수행 전 후의 상태가 같아야 한다
    2. Consistencty 일관성
        1. 트랜잭션이 성공하면 언제나 일관성 있는 상태로 유지해야한다
        2. 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전 후의 상태가 같아야한다 
    3. Isolation 독립성
        1. 둘 이상의 드랜잭션이 동시에 병행되는 경우 어느 하나의 트랜잭션 중 다른 트랜잭션의 연산이 끼어 들 수 없다
        2. 수행중인 트랜잭션은 완전히 끝날때 까지 다른 트랜잭션에서 수행 결과를 참조할 수 없다
    4. Durability 영속성
        1. 완료된 트랜잭션의 결과는 영구적으로 반영되어야한다
4. 격리수준
    1. Read uncommitted
        1. 각 트랜잭션에서 변경된 내용의 commit 이나 rollback에 관계 없이 다른 트랜잭션의 값을 읽을수 있음
        2. 데이터 정합성에 문제가 많은 격리 수준
        3. Commit 되지 않은 상태지만 update 된 값을 다른 트랜잭션에서 읽을 수 있음
    2. Read committed
        1. Dirty read 발생하지 않음
        2. 실제 테이블 값이 아닌 undo로그 영역에 백업된 레코드에서 값을 가져옴
        3. 입급,출금 처리 시 데이터 정합성 문제 발생
    3. Reperatable read
        1. 트랜잭션마다 ID를 부여하여 트랜잭션 id보다 작은 트랜잭션 번호에서 변경된 것 사항만 읽어 같은 데이터를 두번 조회 했을 때 일관성 있는 결과를 리턴함
        2. Phantom read현상은 여전히 반복
    4. Serializable
        1. 트랜잭션이 완료될 때 까지 모든 데이터의 share lock 발생
        2. 격리수준이 가장 강한

• 조인이란: 두개 이상의 테이블에 대해서 결합하여 나타낼 때 조인을 사용

1. 방식(물리적)
   1. Inner
      1. 두 개의 테이블에서 공통된 요소들을 통해 결합하는 조인(교집합)
   2. outer
      1. Left
         1. 왼쪽 테이블 기준으로 공통영역을 포함하여 조인
      2. Right
         1. 오른쪽 테이블 기준으로 공통영역을 포함하여 조인
      3. Full
   3. Cross
      1. 카티션 프로덕트라고 불리며 조인하는 테이블의 모든 경우의 수를 산출한 조인
   4. Self
      1. 동일한 테이블을 대상으로 조인
2. 종류(논리적)
   1. NL
      1. 설명
         1. 랜덤 엑세스 방식(인덱스 스캔 방식)으로 데이터를 읽음
         2. 대량의 데이터를 접근하면 i/o 부하로 인해 무조건 좋은것은 아니나 온라인 프로그램에서 많이 사용됨
         3. 순서를 반복하는 구문으로 프로그래밍에서 반복문과 유사한 방식임
      2. 순서
         1. 선행 테이블에서 조건을 만족하는 첫 번째 행을 찾음
         2. 선행 테이블의 조인 키를 가지고 후행 테이블에 조인 키가 존재하는지 확인
         3. 후행 테이블의 인덱스에서 선행 테이블의 조인 키가 존재하는지 확인
         4. 인덱스에서 추출한 레코드 식별자를 이용하여 후행 테이블을 엑세스
         5. 반복
      3. 성능개선 포인트
         1. 선행 테이블(드라이빙 테이블)의 크기가 작을 수록 유리 하여 테이블 순서 조정
         2. 후행 테이블의 인덱스 여부 확인
         3. 결과 값이 많다면 다른 조인 방식으로 힌트처리
   2. Merge
      1. 설명
         1. 조회의 범위가 많을 떄 주로 사용하는 조인
         2. 양 쪽 테이블에 각각 접근 하여 그 결과를 정렬하고 정렬된 결과를 차례로 스캔하면서 연결고리의 조건으로 머지하는 방식
         3. 주로 조인조건컬럼에 인덱스가 없거나 결과값이 많을 때 사용됨
         4. 조회 범위가 좁을떄 유리한 NL조인과 장단점이 상이함
      2. 순서
         1. 각 테이블의 데이터를 동시에 독립적으로 먼저 읽음
         2. 읽혀진 테이블의 데이터를 조인을 위한 정렬
         3. 정렬된 각 테이블의 데이터를 조인
      3. 성능개선 포인트
         1. Access 속도 향상
            1. 테이블 엑세스 시 FTS, index range scan 등 테이블 접근에 대한 최적화
         2. 정렬 속도 향상
            1. 정렬된 조인 조건 컬럼 사용
         3. 양쪽의 정렬 완료 속도를 동일하게 맞춰줌
            1. 머지할 두 테이블의 데이터 양이나 속도를 최대한 맞춰줌
         4. Sort_area_size 최적화
            1. 적당한 사이즈로 설정
   3. Hash
      1. 설명
         1. 조인할 테이블에 대해 해시 버킷을 생성 하여 순서대로 결과가 출력
         2. 조인될 두 테이블 중 하나를 해시 테이블로 선정하여 조인될 테이블의 조인 키 값을 해시 알고리즘으로 비교하여 매치되는 결과 값을 얻는 방식
         3. 비용기반 옵티마이저를 사용할 때만 사용 될 수 있는 조인방식이며 = 비교를통한 조인에서만 사용
         4. 주로 많은 데이터를 조인해야할 때 사용
      2. 순서
         1. 두 집합 중 작은 집합을 읽어 해시 공간에 테이블을 생성
         2. 반대쪽 큰 집합을 읽어 해시 테이블을 탐색하면서 조인
         3. 해시 함수에서 리턴 받은 버킷 주소로 찾아가 해시 체인을 스캔하며 데이터를 찾음
      3. 성능개선 포인트
         1. 해시 테이블을 만드는 과정을 효율화
            1. 해시 테이블을 만드는 비용이 수반되므로 outer table 이 hash area에 담길 정도로 충분히 작고 중복이 없어야함
         2. cpu성능 개선
            1. 해시 버킷이 조인 집합에 구성되어 해시 함수 결과를 저장해야하는데 HASH_AREA_SIZE에 지정된 크기만큼 메모리가 할당되어 사용됨
            2. Cpu 자원이 넉넉하면 다른 조인보다 좋은 효율을 내지만 부족한 상황에선 느릴 수 있음
         3. 충분한  메모리 확보
            1. 해시 조인에 사용되는 최대 메모리 사이즈를 최적화

1. 설명
   1. 데이터베이스 테이블 검색 성능을 높여주는 자료구조
   2. 특정 컬럼에 인덱스를 생성하면 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함꼐 저장
   3. 인덱스 참조 시 인덱스에 저장되어 있는 데이터의 물리적 주소로 가서 조회해 오는 방식
   4. 책에 있는 목차라고 생각
2. 사용이유
   1. where 검색의 효율성
      1. 데이터가 쌓이면서 내부적으로 순서가 뒤죽박죽 저장되는데 인덱스가 없거나 참조하지 못한다면 FTS
      2. 인덱스 테이블은 데이터가 물리적으로 저장되어 있기 떄문에 조건에 맞는 데이터들을 빠르게 조회
   2. order by의 효율성
      1. 인덱스 생성 시 정렬하여 저장하기 때문에 부하가 큰 order by에서 효과적
   3. Min, max 처리
      1. 시작과 끝 값을 한건만 가져오기 때문에 FTS보다 효율적
3. 단점
   1. 정렬된 상태를 계속해서 유지해야하기 때문에 DML로 인한 레코드 내 데이터 값 변경 시 인덱스를 재정렬 해야하기 때문
   2. 무조건 index가 좋은 것이 아니라 전체 데이터의 10~15% 조회 시 유리하며 대용량 데이터 조회 시 FTS이 유리할 수 있음
   3. 인덱스가 저장공간을 차지하기 때문에 스토리지 관리에도 고려해야함
4. 인덱스 생성 전략
   1. 조건절에 자주 등장하는 컬럼
   2. 항상 = 으로 비교되는 컬럼
   3. 선택도가 낮은 컬럼
   4. 정렬을 자주하는 컬럼
   5. 조인 조건건으로 사용 되는 컬럼
5. B*tree 인덱스
   1. 설명
      1. DBMS에서 가장 많이 사용되는 인덱스 구조는 밸런스드 트리 인덱스
      2. 항상 정렬된 상태를 유지하기 때문에 시간복잡도가 O(logN)이므로 탐색 시간에 매우 효율적인 자료구조
      3. 항상 좌우 자식 노드 개수의 밸런스를 유지하기 때문에 최악의 경우에도 O(logN) 시간을 유지하기 떄문
   2. B tree가 DB 인덱스 용도로 가장 적합한 자료구조인 이유
      1. 항상 정렬된 상태로 특정 값보다 크고 작은 부등호 연산에 문제가 없음
      2. 참조 포인터가 적어 방대한 데이터 양에도 빠른 메모리 접근 가능
      3. 데이터 탐색 뿐만 아니라 저장, 수정, 삭제에도 항상 같은 시간 복잡도를 가짐(O(logN)
   3. 데이터 접근이 빠른 자료구조인 배열이 db 인덱스로 선택 받지 못하는 이유
      1. 배열 내에서 데이터 저장, 삭제가 일어나는 순간 B-tree보다 비효율적인 성능이 발생함
      2. 중간에 데이터 삽입 시 해당 데이터보다 큰 데이터는 한 칸씩 뒤로 물러나게 되는데 이때 발생하는 평균 시간 복잡도가 O(N+logN) = O(N)
      3. 즉 일관적으로 빠른 시간복잡도를 유지하기 위해서 B-tree로 선택

AlwaysOn DB 점검 중 이벤트 뷰어 시스템 쪽에서 failover clustering이 지속 발생하는 것을 확인했다.

이벤트 ID 는 1196 이었고 이벤트 상세 내역과 구글링 결과 DNS 등록 문제로 판단했다.

인프라 도움을 받아 AD서버 DNS 쪽 확인해보니 해당 DB이름으로 DNS 등록이 되어 있지 않은 것을 확인했다.

우선 DNS 쪽에 해당 DB에 대한 DNS 등록을 했고 차후 같은 에러가 나는지 모니터링 해봐야겠다.

차후에도 같은 에러가 발생했다.

1. event id 1196 확인 
   1. 상세 내용 : EventID 1196
2. 확인 내용
   1. 네트워크 인터페이스 ipv6 enable 확인
   2. 자동으로 받아온 DNS(ipv6) 를 포함하여 등록된 DNS 서버가 4개로 확인
3. 조치 내용
   1. 네트워크 인터페이스 ipv6 disable 조치

결국엔 사용하지 않는 ipv6 enable처리 때문이었고 disable처리 하면서 에러로그 남지 않게 되었다.

세션id로 실행 계획 보기

select B.query_plan,plan_handle, * from sys.dm_exec_requests A outer apply sys.dm_exec_query_plan(A.plan_handle) B where session_id = '753'

서비스 운영 중 갑자기 APM 쪽에서 폭포수를 그리기 시작했다.

빠르게 DPM 확인해보니 DB 쪽은 문제 없어서 DB문제는 아닌가보다 하고 있었는데,

다른 DBA 분이 넌지시 계정쪽 문제 인 것 같으니 계정 확인해보라고 하셨다.

알고보니 마스터 성으로 사용중인 계정이 잠긴 것이다.

마스터 성으로 사용하는 계정은 일반 개발자에게도 오픈된 계정으로 사실상 sysadmin 계정을 공용으로 사용하는 계정이다.

해당 계정의 이러한 사용은 사내 특수성을 고려하여 제약을 두려고 노력 중인 사항이지만 현재로서는 큰 공수를 두기가 힘들어 권고만 하던 수준이었다.

잠긴 계정을 풀고 APM을 보니 막혀있던 트랜젝션들이 해소되고 있었다.

해당 계정의 잠긴 사유를 확인해보기 위해 dberror log와 DB접근제어(chakraMax)로 확인했다.

우선은 dberror log에 계정잠김 최초시점을 보니, SSMS에서 비밀번호를 오기입한 흔적이 보였다.

5번 이상 실패하여 계정이 잠긴 것으로 추측했고 DB접근제어에서도 이렇다 할 큰 작업이나 막대한 요청은 없었다.

보안 감사 지적사항으로 나온 암호정책강제적용으로 5번 실패에 계정이 잠긴 것으로 판단할 수 있을 것 같다.

개발자들의 SA성 계정 사용을 막기 위한 방안 마련이 시급하다.

파일그룹별 object 및 object의 index 정보 조회

SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName] ,i.[index_id] AS [IndexID] ,i.[name] AS [IndexName] ,i.[type_desc] AS [IndexType] ,i.[data_space_id] AS [DatabaseSpaceID] ,f.[name] AS [FileGroup] ,d.[physical_name] AS [DatabaseFileName] FROM [sys].[indexes] i INNER JOIN [sys].[filegroups] f ON f.[data_space_id] = i.[data_space_id] INNER JOIN [sys].[database_files] d ON f.[data_space_id] = d.[data_space_id] INNER JOIN [sys].[data_spaces] s ON f.[data_space_id] = s.[data_space_id] WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1 ORDER BY OBJECT_NAME(i.[object_id]) ,f.[name] ,i.[data_space_id]