Daily Study

13. DMV - 동적관리뷰 

동적 관리 뷰 및 함수는 서버 인스턴스 상태 모니터링, 문제 진단 및 성능 튜닝에 사용할 수 있는 서버 상태 정보를 반환합니다.

동적 관리 뷰 또는 함수를 쿼리하려면 개체에 대한 SELECT 권한과 VIEW SERVER STATE 또는 VIEW DATABASE STATE 권한이 필요합니다. 

- 동적관리뷰 개수는? : 아래 쿼리를 활용하여 확인해보니 총 273개입니다.

SELECT N'SYS.' + NAME AS NAME, TYPE_DESC 
  FROM SYS.SYSTEM_OBJECTS 
 WHERE NAME LIKE N'DM[_]%' 
 ORDER BY NAME
 

모두를 알기는 불가능하니, 자주 쓰는 DMV에 대해서 알아보겠습니다.

(1) sys.dm_exec_query_stats - 캐시된 쿼리 계획에 대한 집계 성능 통계

ex - 평균 CPU 시간별로 상위 5개의 쿼리 찾기

SELECT TOP 5 query_stats.query_hash AS "Query Hash",   
    		 SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",  
             MIN(query_stats.statement_text) AS "Statement Text"  
  FROM  (SELECT QS.*,  SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,  
		((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)  
         	 ELSE QS.statement_end_offset END   
                    - QS.statement_start_offset)/2) + 1) AS statement_text  
     	   FROM sys.dm_exec_query_stats AS QS  
     	  CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats  
 GROUP BY query_stats.query_hash  
 ORDER BY 2 DESC;  

(2) sys.dm_exec_sql_text(sql_handle)  - 지정 된 sql_handle 에 의해 식별 되는 SQL 일괄 처리의 텍스트를 반환

- text 컬럼에 SQL 쿼리 실행 텍스트가 나옴.

 SELECT B.text,* 
  FROM sys.dm_exec_query_stats AS A
  CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) AS B  
 ORDER BY A.creation_time DESC

(3) sys.dm_exec_procedure_stats - 저장 프로시저에 대한 집계 성능 통계

- 평균 경과 시간으로 식별된 상위 10개의 저장 프로시저 찾기 예제

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',   
       d.cached_time, d.last_execution_time, d.total_elapsed_time,  
       d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],  
       d.last_elapsed_time, d.execution_count  
  FROM sys.dm_exec_procedure_stats AS d  
 ORDER BY [total_worker_time] DESC;  

(4) sys.dm_exec_requests - 실행 되는 각 요청에 대한 정보를 반환

(5) sys.dm_exec_query_plan - 계획 핸들로 지정한 일괄 처리에 대한 XML 형식의 실행 계획을 반환

테스트를 위해 VMWare에 Windows SERVER 2016을 설치해보겠습니다.

먼저 아래 VMWare 사이트에 들어가서 무료판을 다운로드합니다.

(1) www.vmware.com/kr.html

(2) 설치를 하고

(3) 실행을 합니다

(4) 그 다음 아래 사이트에서 SQL Server 2016 ISO를 받습니다.

동의하고 정보를 입력하고 또 동의~

언어는 영어로

다운로드중........

(5) 설치 시작

3번째를 선택하고 다음

Windows Server 2016를 찾아서 선택해줍니다.

원하는대로 용량을 설정해주세요.

Finish

(6) ISO 설정

CD/DVD 에 다운받은 ISO를 선택합니다

그 다음 Windows Server를 실행해 주세요

실행되면 엔터를 연타!! 

아래와 같이 나옵니다. 

Next - Install Now 선택

2번째 데스크탑 환경 선택

Custom 선택

Next - 설치중..

설치 완료

10. 백업 종류
 (1) 전체 백업 : DB의 모든 데이터를 백업. 백업이 진행되는 동안 발생한 트랜잭션 로그도 백업.

 (2) 차등 백업 : 전체 백업 이후 백업되지 않은 변경된 데이터만 백업.

 (3) 트랜잭션 로그 백업 : 트랜잭션 로그 파일을 백업하고 로그를 지움. 설정으로 안지울수 있다. FULL, BULK_LOGGED    복구모델만 가능

 (4) 로그 꼬리 백업 : 백업되지 않고 남아있는 로그를 백업. 마지막 로그 백업

 (5) 파일/파일 그룹 백업 : 데이터베이스 크기가 커서 파일 또는 파일그룹을 백업.

 (6) 부분 백업 : 파일 그룹 중 PRIMARY 파일 그룹과 읽기/쓰기가 가능한 파일 그룹을 백업. (OR 명시적으로 읽기 전용 파일 그룹도 포함해서 가능)

 (7) 복사 전용 백업  : 다른 백업에 영향을 끼치지 않고 전체 백업이나 트랜잭션 로그 백업을 할 수 있다.

11. 복구 모델

 (1) 전체(FULL) : 모든 데이터 변경에 대한 로그를 트랜잭션 로그 파일에 기록. 특정시점 복원이 가능. 로그 파일 용량이계속 커지므로 관리 필요.

 (2) 대량로그(BULK_LOGGED) : 대량 로그 작업으로 발생하는 트랜잭션 로그를 최소화하여 작업을 빠르게. BCP, BULK INSERT, INSERT~ SELECT, CREATE INDEX, ALTER INDEX REBUILD 등.  대량로그 작업이 발생하면 특정시점으로 복원 할 수 없다. 디스크 성능이 좋아짐에 따라 잘 쓰지 않음.

 (3) 단순(SIMPLE)  : 트랜잭션이 완료되면 지워짐. 다양한 복원 작업 불가.