mssql 2일차 교육

4장

테이블에 UI로 열쇠표시로 primary key를 생성하면 클러스터형 인덱스가 생김.

테이블에 클러스터형 인덱스는 한 개밖에 못 만든다.

튜닝패턴1 : 시퀀스넘버가 primary key로 되어 있는 경우는 보통 primary key를 넌클러스터형으로 변경하고 자주 실행되는 컬럼을 클러스터형으로 변경한다.

 

Alter table에 스크립트 방식으로 만들 때는 클러스터형 아님

 

Bulk insert 할 때는 heap 상태가 젤 빠르다.

(active하게 bulk insert를 받는 dummy table을 만들고 인덱스를 잡고 가공을 해서 필요한 데이터만 사용하는 테이블로 옮기도록 가이드를 한다.)

 

 

통계 정보 갱신 : 플랜을 새로 만들어 주니까 문제가 되는 플랜이 사라짐.

인덱스 리빌드 : 인덱스 리빌드할때도 통계 정보 갱신함.

디스크 수준에서 단편화 자체가 성능 저하의 원인일 수는 없을 것 같다.

인덱스 구조에 따라서 전체는 아니고 80%선에서…

 

 

쿼리에 블록걸고 Ctrl + L 하면 수행전에 실행계획 볼 수 있다.

 

쿼리 실행시점의 실행계획을 직접 보고 문제가 되는 쿼리를 직접 볼 수도 있다.

각 연산자마다 우클릭 속성 해서 자세한 내용 볼 수 있다. 실제 사용된 cost와 어떤 필드들이 나열되는지를 자세히 볼 수 있다.

라인마다 찍어보면 몇 건의 데이터가 연산이 되는지에 대한 로드도 볼 수 있다.

얇은 선보다는 두꺼운 선이 데이터양(access 껀수. 데이터양이라기보다)이 많은 것.

조인 연산이 잘못된 걸수도 있고 인덱스 연산이 잘 안된경우 두껍게…

access되는 페이지의 개수로 보는게 정확. (데이터 건수로 보기보다)

 

전체 cost 100 중에 각 연산자가 사용하는 cost들도 표시가 된다..

높은 부분의 연산자에서 인덱스를 좀 보거나 쿼리 최적화등을 고려해야 한다.

Table spool? 이라는게 발생하면 Working data가 좀 많으면 tempdb에 쌓기도 한다.

되도록이면 발생 안시키게 쿼리를 짤 짜야..아니면 데이터양을 좀 적절히 줄여서 넣거나…

Warning 정보도 볼 수 있다.

 

 

 

 

 

5장 보안

 

Ad 조인 : 커버로스 인증

단독 : 윈도우 인증

Sql 서버 설치할 때 기본은 윈도우 인증.

 

Principal : 로그인 계정. (보안 주체)

윈도우 인스턴스 수준에서 관리할 수 잇고 서버에 접속하는 단계

 

사용자 매핑 : grant access (DB에 접속할 수 있는 수준)

 

스키마 개념 :

일반 sql에서는 dbo (디폴트 스키마)

디폴트 스키마를 먼저 뒤지고 그게 없다라고 하면 사용자계정에 매핑되어 있는 스키마를 뒤진다.

오라클의 경우 테이블스페이스의 이름이 스키마에 매핑이 되어서, user1이라는 테이블스페이스에 있는 테이블을 SSMA로 마이그레이션하는 상황이면 dbo.테이블명이 아니라 user1.테이블명으로 마이그레이션된다.

 

 

 

개별적인 DML 수준의 권한관리 오브젝트별로 가능하다.

개발자들이 batch job을 실행하면 안 되는 경우가 있다. (보안상)

Job에 대한 성공/실패 여부를 모니터링이 불가능한 경우면 agent도 open해줘야 한다.

Role을 조정하면 agent에 대해 읽기전용 role을 만들어서 agent에서 읽기만 가능하게…

 

 

No default schema인 사용자가 조회시

스키마 이름 명시하지 않으면 dbo 스키마부터 찾는다.

Dbo가 없으면 그때 다른 스키마를 가지고 찾게 된다.

 

디폴트 스키마가 sales인 사용자가 조회시

스키마 이름 명시하지 않으면 sales 스키마부터 찾는다.

Sales 스키마가 없으면 그 때 dbo 스키마를 가지고 찾게 된다.

 

 

일반적인 가이드는 dbo 스키마를 그냥 써라~~~

 

 

 

2016에서부터는 Active Directory Password Authentication 이 있다.

 

 

 

use [msdb]

GO

EXEC sp_addrole 'SQLAgent_Viewer'

EXEC sp_addrolemember 'SQLAgentUserRole', 'SQLAgent_Viewer'

EXEC sp_addrolemember 'SQLAgentReaderRole', 'SQLAgent_Viewer'

EXEC sp_grantdbaccess 'dev' , 'dev'

EXEC sp_addrolemember 'SQLAgent_Viewer', 'dev'

-- 글로벌하게 만든게 아니라, msdb에 만든거라서 server role에 보이는게 아니라,

-- msdb 안의 security > Roles > database Roles 에서 SQLAgent_Viewer 라는 롤이 생긴 것.

 

 

 

 

 

 

 

 

 

Server roles에 생기는 것이 아니다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

누가 설정을 바꿨는지 기록하는 기능 (sp_configure에 대해서 audit)

1. Audit 파일 생성

 

1. Audit 기록 넣기

   (audit 기록이 아래 server 어쩌구 아니고 다른 거 같다고 하신다. 그건 알려주신다고 한다.)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Alwayson이 로그인계정까지 복제되진 않는다.

그래서 contained database authentication을 넣으면 로그인 계정까지? 복사해서 보낸다.

 

Sql SE / EE 모두 필드 암호화 가능

sql에서 제공하는 거 말고 db에서 따로 암호화 키도 만들 수 있당…(맞지?)

TDE(Transparent Data Encryption) : 백업된 데이터베이스파일을 다른데서 복구하지 못하도록 인증서를 매핑하는 것

 

암호 : 단방향 (SHA-1/2)

TDS패킷 : 암호화.. tcp프로토콜에 인증서를 붙여서 하면 로그인정보에 대한 암호화 가능함

• 디폴트로 해줘야 하는 것 아님…????

 

db에서 create certificate하면 유에 인증서를 만들어서 자체 암호화 가능.

윈도우에서 make cert 해서 하는 방법도 있고 db에서 인증서 만드는 방법도 있다.

 

 

 

 

6장 데이터베이스 백업/복구

 

일반적인 statement failure(구문상 오류) 는 exception으로 처리.

프로세스 실패 : 대비해서 결국에는 ha 구성하라..이런 리커버리 전략

인스턴스 실패 : 인스턴스 수준의 redundancy 보장은 failover cluster .

사용자 수준에서 데이터를 잘못 업데이트 치거나 하는 경우

• Temporary …기능 (오라클 flashback 기능)

Bcp 솔루션. 커맨드라인으로 데이터 내리는거… 대량 작업 전에는 read committed snapshot 이기 때문에 트랜잭션 걸고 해야…

 

 

백업방법 : sql 백업은 온라인상에서 수행.

2014까지는 플래시백이 없기 때문에 대량변경작업 전에는 SSIS로 데이터백업하는 (BCP) 별도의 사본을 만들어 두는게 좋다.

 

 

복원모델

Full : 백업 받은 시점의 lsn 범위에 따라서 특정 시간으로 rewind하는 기능 쓸 수 있다.

(풀백업시 데이터/로그파일 모두 받음.)

트랜잭션로그 받지 않으면 ldf가 계속 쌓임

Bulk logged : 차등/트랜잭션 가능. Bulk 형태의 로그들을 덜 쌓는다. (DW시스템에서 설정)

Simple : 트랜잭션로그가 자동으로 관리가 됨. 70% 차면 자동으로 로그가 지워짐.

 

트랜잭션 로그가 계속 증가할 때 재사용이 안 되는 이유를 봐야 한다.

Select * from sys.database

• Log reuse wait desc

• 로그백업을 받지 않아서 계속 커지고 있다.

• 이런 내용을 볼 수 있다.

 

 

백업을 결정할 때, sql 자체적으로 받는 백업을 native 백업이라고 한다..

장애 수준에 따라 어떻게 조치를 하면 되느냐

관례에 따라서 failure 수준을 어느 정도로 관리할 거냐

그런 얘기를 하고 있습니다.허허

 

 

블록커피는 데이터 연속성을 보장해 주는듯.

 

 

백업 명령에 stats=10 을 넣으면 10퍼마다 알려줘라 이런건가바? 아닌가 1퍼마다 알려줘라인가.. 10초마다 알려줘라 인가ㅠㅠ

 

 

Copy only 옵션을 쓰면 백업체인을 깨지 않고 백업하는 방법을 체크옵션에 넣을 수 있다.

(백업체인에 로그백업 등등이 포함됨.)

 

full백업할 때 mdf랑 ldf 모두 백업한다고 했자나용?

그러면 full 백업 완료 후에는 ldf 내용이 truncate 되는지요?

 

 

RPO ; 복구 구간

RTO ; 허용되는 다운타임

RLO : 성능까지 같이 본다(db에서 여기까지 보진 않는다)

 

 

 

MSCS ;Shared nothing (A-S)

AlwaysOn Availability Groups : 데이터베이스 readonly 가능

(dw용 readonly , r/w 용 레플리카 모두 구성 가능)

 

 

7장 모니터링 / 튜닝

 

1. Configuration 정보를 먼저 확인

2. 성능카운터를 봐서 쿼리를 점검해야 하는 수준인지 확인

   1. 쿼리를 수집해서 디테일하게 확인

   2. Wait 상태도 없다.. 이러면 하드웨어 문제

 

Baseline이 있어야 한다. (어떤 거 대비…이런식)

오랫동안 수행되는 쿼리가 남아있어서 was에 영향을 줄수도 있고..( cpu)

 

인스턴스 레벨에서는 메모리 관점에서 볼 수 있는 DMV 활용해서……………………….

리소스 측면에서 발생되는 오류인지 단순히 블락킹에 의한 문제인지를 찾아야 한다.

 

문제 접근방식에 대한 프로세스/패스/노하우에 따라…

다른데 힝 난 그런거 업쪙

 

 

대표적으로 메모리 문제일 때

1. db프로세스에 의해 메모리 사용 문제인지

2. 인스턴스에 들어가서 allocation된 쿼리에 대해 점검

3. 현재 worloard에 맞게 메모리가 사이징이 되어있는지도 확인해야 한다

4. 메모리 마니 소모하는 쿼리를 찾아라…어케…?

 

내부 프로세스 / 페이징폴트/ 스레드 / 인스턴스에 대한 점검도 필요

ㅎ ㅓ????

 

 

 

 

 

 

 

 

 

 

 

 

 

데드락 정보 확인

 

 

 

 

 

 

 

 

 

이걸 보고…어떻게 뭘 확인해야 할지 모르겠다 ㅋㅋㅋㅋ

Sate clean이면 ok인건가…

 

 

 

 

실제 데드락이 발생되는 상태면

(일단 퍼프몬에서 데드락이 발생하는지 여부부터 확인)

Dbcc traceon(-1, 1204, 1222)

이렇게 걸면 system_health에서 데드락에 대한 유발하는 쿼리 확인

 

 

성능카운터 볼 때 실제 유입되는 쿼리상태를 봐야 한다.

성능카운터만 보면 소설을 쓸 수 있다.

오라클의 AWR와 유사한 기능 : 10분간 실행됐던 쿼리에 대한 내용 주기적으로 보관….

• 뭔가 따로 하라는 거넹-_-;;

• 성능이 저하되는 쿼리를 트리거

• Sql에 대한 핸들값을 만약에 보관을 할 수 있는 상태라고 하면 실행됐던 쿼리의 sqlperfstatch ….??? 수집

• 그 시점의 플랜도 볼 수 있다.

 

 

 

 

Select top 10 으로 주셨던 쿼리가 있는데

그걸 돌리면 된다. (baseline 관리 필요)

그게 머지..ㅋㅋㅋㅋ

 

 

 

온라인으로 인덱스 만드는 기능

• 클러스터형 인덱스의 경우에는 tempdb에서 데이터인덱스를 만들어서 마지막에 스키마에 블락킹을 걸고 인덱스 추가..

• 엔터프라이즈에서만 가능.

 

대량 테이블을 switch in / switch out 할 때

60분동안은 무조건 수행하겠다 라는 거랑

다른 블락킹을 다 끊어버리겠다는 개념으로 쓸 수도 있다.

모르겠다

ㅋㅋ

 

 

 

Truncate table은 테이블이 갖던 identity를 초기화시킴.

Delte table하면 로그에 기록하면서 해서 느리다.

대상데이터를 날려도 되는 상황이면 원활하게,,,