sql 1일차

오라클의 테이블스페이스는 mssql의 Database에 매핑된다.
(궁극적으로는 파일그룹에 해당된다.)

마스터db에 있는 로그인정보를 확인해서 인스턴스에 접속

 master db : 오라클의 system 테이블스페이스
  인스턴스의 구동에 필요한 데이터 포함

alwayson high abailability : 2012 부터 포함.

ssms : 2016부터는 설치미디어에 빌트인 안 되어 있다.

server collation :
korean_wansung_ci_as
ci_as : 대소문자 구분 하지 않는다.
-> 일반적으로 사용하는 모드

modeldb : 템플릿 db
 -> collation, compatbility level (호환성 모드)
msdb : 배치잡, 스케줄을 관리하는 sql agent에 대한 내용이 기록. 실행이력들이 기록
오브젝트 db들을 관리
tempdb ; temp tablespace와 동일한 역할
-> 엔진을 구동하는데 필요한 system database
-> model db의 속성을 그대로 따라간다. db가 구동될때마다 새로 생성되는 구조

사용자 db에서 collation을 바꿀수 있는것처럼 보이는데
데이터가 일부라도 들어가 있는 상황이면 collation은 바꾸기가 불가능하다.

권장 : system collation과 사용자db의 collation은 되도록 맞추어라.
왜냐하면 system collation에 tempdb collation이 따라가는데, 쿼리등 join을 할 때 쿼리가 실행이 안 될 수도 있다.
collation은 저장이 아닌 정렬도 포함되므로? 무슨 말이징

전체메모리가 부족한 상황에
사용자가 사용하는 양이 많은지 아니면 엔진이 사용하는 양이 많은지 등의 dmv 제공

※ NUMA에서 성능저하 요소
foreign node의 자원을 사용하는 경우
-> 메모리 할당에 이점을 가져가는 아키텍처

클러스터 타입의 인덱스가 없는 데이터 타입을 heap이라고 한다.
sys.dm_os_memory_objects : 실제 메모리에 할당된 오브젝트를 보는거랑
sys.dm_os_wait_stats : 리소스 상태에서 대기가 걸린 상태를 확인

메모리(버퍼풀)에 있는 페이지를 읽어가는 것 ; logical read
디스크에 있는 페이지를 버퍼풀에 올리고 일겅가는 것 ; physical read

-> cache를 완전히 날린 상태에서 최초 실행될 때에는 physical read 발생

aging system
버퍼풀에 머무르는동안 10분 이내에 사용되지 않으면 다시 해당 페이지를 내린다.

쿼리를 실행하는 동안 발생하는 logical read (cache heat) / physical read (cache miss) 수 셀 수 있다.

db백업 같은 거 받을 때 시스템캐쉬도 사용하는데..

일반적으로 mssql 입장에서 os 메모리 1GB 여유가 있으면 괜찮다고 한다.

파일그룹 : 데이터파일 묶어주는 단위
temp 테이블스페이스를 제외한 나머지는 물리적인 파일들을 관리하는 구조
오라클은 세그먼트 단위로 관리

인덱스형에 따라...
heap (비클러스터형) / index (클러스터형) 형태로 관리되는 데이터 오브젝트들

사용자 db 하나로 관리
초기에 primary 파일그룹 하나 생성됨.
mdf : primary data file
ldf : log data file

추가적으로 DATA_FILE 그룹
ndf 파일 여러개..

INDEX_FILE그룹

이런식으로 파일그룹별로 관리

만약 내가 data file 그룹을 만들어서 ndf 파일을 10G, 5G, 1G 이렇게 만들었다. 치면
균등분할 방식으로 해서 10G짜리부터 사용하게 된다.
남아있는 공간이 많은쪽부터 쓴다.
동일한 크기로 생성해야 동일하게 균등하게 쓴다.

데이터파일 하나는 1TB 미만이 BP지만
운영 경험상 500G 정도가 적정하더라..

일반적으로는 자동증가 크기는 100mb ~ 1GB 정도로 관리 (데이터파일 경우)
자동증가할 때 파일 블럭킹이 발생해서 너무 자주 발생하면 또 안 좋음..
ldf의 경우 10%로 되어 있어서.. 이것도 fixed value로 지정이 좋다..
어느 정도인지는 말 안해주시넹ㅋㅋ

sql은 auto commit !
insert into table -> auto commit 됨.

내가 트랜잭션을 발생시키면
set implicity transation (암시적으로 트랜잭션이 자동으로 열리고 자동으로 커밋이 되는 구조)

tools > options > query execution > sql server > ANSI > set implicit transactions가 꺼지면 자동커밋 안됨.
(commit 안 때리면 계속 블락킹 상태로 계속되므로 비권고!!.
열려있는 transaction이 select가 안됨.)

데이터베이스가 재구동될때 redo / undo 결정함
chkpt는 버퍼풀에 있는 더티페이지를 디스크로 내려주는 역할
트랜잭션의 커밋 상태와 체크포인트 발생여부에 따라서 chkpt 결정됨.
recovery 모드시 아래 두가지 이루어짐
undo -> rollback
redo -> roll forward

 데이터베이스 스냅샷은 db통째로만 됨.
테이블별로는 change tracking

2014까지는 트랜잭션까지 보관해야 복구가능
2016은 temporary?라는 기능 있어서 오라클의 flashback 기능 사용가능함

redo thread 자체가 병렬처리구조로 되어서 recovery 페이스가 빨라짐.

sql에서는 각자 데이터베이스에
sys.objects
sys.index
라는 형태로 관리됨.

프라이머리 데이터베이스 그룹 하나 만들어지면
여기에 mdf / ldf 파일이 있자나여?
여기에 dictionary가 보관됨.
이거 사이즈를 조그맣게(300MB) 관리하고
새로운 파일그룹 (DATA_FG)을 생성해서 디폴트파일그룹을 이걸로 정의.
사용자 데이터파일을 보관하는 파일그룹을 별도로 관리.
이렇게 관리하는 것이 좋다~라고 본다.

sql도 데이터의 변경이 발생하면
로그 버퍼/로그 캐쉬에 먼저 기록이 되고
로그가 flush가 되는 시점에 트랜잭션로그와 데이터파일에 기록이 된다.

live query statistics : 2016부터 볼 수 있다.
실행계획을 라이브로 볼 수 있다.
그래서 어디서 느린지 볼 수 있다.

클러스터 dtc의 경우 2005까지는 필수
2008 이상부터는 dtc가 필수로는 안 쓴다.

secpol에서 user rights에서...
perform volume maintenance tasks : 에 sql시작계정 넣으면, 자동증가 한 후 zero filling을 안해서 빨리 자동증가가 가능하다.

tempb의 경우 이제 8코어 이상이어도 템프파일을 8개 이상 만든다고 크게 성능의 향상을
주진 않다.

stored 프로시저 아니고 재사용 안되는 실행계획을 ad hoc이라고 한다.
동적 쿼리가 많은 상태면, optimize for ad hoc workloads를 구성하면 최적화 가능하다.
걸어도 특별히 영향도 없다. 걍 걸어서 써라ㅋㅋ

애초에 할 때부터 메모리캐쉬에서부터 압축해서 내리는 방식이 있고,
기본크기로 했다가 압축하는 방식도 있고.
디폴트는 기본크기로 했다가 압축하는 방식.
msdn 에 찾아봐랑. backup compress ? 로 검색해보면 트레이스플래그를 설정하는게 있는 거 같다.
백업파일을 여러가지로 slicing 할수도 있다.
write 하는 thread가 분산되는 효과가 있다.

select * from fn_trace_gettable('디폴트트레이스파일')
where TextData IS NOT NULL
-> 하면 디폴트트레이스파일에 null 아닌 거 확인 가능
보통 디폴트트레이스파일은 mssql 루트디렉토리 내 log 파일에서 확인 가능함.

닷넷프레임워크 4.0이 cpu 누수가 좀 잇다.
activity monitor 가 wmi를 쓰기 때문에 cpu를 좀 잡아먹는다.
그래서 왠만하면 꺼라.

4장
sql은 일반적인 수준에서 만드는 index는 모두 b-tree 인덱스
2012부터 columnstore 인덱스....?
heap -> 테이블 만들고 데이터 인서트 해놓으면 이게 heap 상태.
 걍 입력된 순서대로 정렬된거
클러스터형 인덱스 -> 들어간 데이터가 특정 필드를 기준으로 정렬된 형태로 바뀌면 클러스터형 데이터테이블이다 라고 한다.

sql에서의 파티션 테이블은 left / right range? 정렬만 지원한다.
대부분의 파티션 데이터가 저장되는 방식은 날짜 기준으로 ...훔

어후 어려웡
ㅠㅠ
columnstore clustered index : 디스크 줄이는데 효과적..
데이터에 대한 압축이나 배치성 쿼리로 사용되는 데이터를 핸들링하고 아카이빙할 때는 이 인덱스 사용하는 것이 좋다.
텍스트 데이터가 많은 경우

클러스터 타입의 인덱스는 데이터랑 똑같다!!!
기본적으로 ui상에서 만들면 클러스터 타입의 인덱스로 생성됨.
leaf node / inter node / root
가중치를 기반으로 움직인다.

클러스터형 타입의 인덱스는 실제 leaf node로 가면 데이터가 있다.
비클러스터형 타입의 인덱스는 key 따라서 가면 leaf노드에 데이터가 있는게 아니라 데이터에 대한 위치정보가 있다. 그 데이터를 가져오는 연산을 lookup 이라고 한다.

RID lookup : 비클러스터형.
key lookup : 클러스터형. 정렬이 된 상태. 클러스터형 인덱스에 키값이 저장됨.
-> 인덱스 자체가 데이터파일에 포함됨!

sql에서도 login triggers가 있어서 접근제어 가능하대
오잉?
언제부터?
-> 여튼... 비권고래 뭐지ㅋㅋ
로그인이 빈번하게 일어나는경우 부하를 주기 때문에 비권고랭 ㅋㅋㅋ