Monitoring

DYNAMIC MANAGEMENT OBJECT
SYS.DM_EXEC_SESSIONS
SYS.DM_TRAN_LOCKS
SYS.DM_OS_THREADS

SYSTEM MONITOR
: 운영체제 관리도구

SQL Server & 시스템 성능 모니터링
분류	카운터	권장범위 및 설명
Processor	%Processor Time	CPU 자원의 일반적인 사용량을 의미
System	Processor Queue Length	CPU 개수 * 2이하
Memory	Page/Sec	SQL Server 전용인 경우 20이하 권장
	Available Mbyte	이 값은 최소 5MB 보다 커야 한다.
PhysicalDisk	%Disk Time	읽기/쓰기 작업에 사용 중인 디스크 시간의 비율을 모니터링. 90%이상이면 아래 항목과 함께 검토
	Avg. Disk Queue Length	디스크를 액세스하기 위해 기다리고 있는 평균 시스템 요청 수. 하드 디스크 개수 * 2 이하
SQL Server	Buffer Manager : Buffer Cache Hit Ratio	97%이상 권장. Record size 줄임
	Databases : Transaction/Sec	초당 트랜잭션 발생 수
	General Statics : User Connections	현재 SQL Server에 연결한 사용자 수입니다.

Profiler
SQL Server에서 일어나는 모든 작업을 추적

DDL Trigger
        └ 입력, 수정, 삭제시 사용
            다른 데이터와 무결성
: Server, DB 레벨에서 사용 가능, 사용자 작업 제어(=EventData: 현재 진행중 데이터)

Disable trigger tr_test2 on all server
Enable trigger tr_test2 on all server

1. 데이터베이스 수준의 트리거
use TestDB
go

-- 트리거 관련 데이터를 저장할 테이블
CREATE TABLE DDL_Log (
 ID      int IDENTITY(1, 1)
  PRIMARY KEY
, TrData xml
)
GO

-- 테스트용으로 DROP, ALTER를 수행 할 테이블
CREATE TABLE TEST01 ( id int )
GO

-- DDL 트리거
CREATE TRIGGER tr_Test1
 ON DATABASE
 FOR DROP_TABLE, ALTER_TABLE
AS
 DECLARE @trData XML
    -- EVENTDATA() 함수 호출 결과를 XML로 저장하거나, XQuery로 검색
 SET @trData = EVENTDATA()

    -- DDL 트리거는 ROLLBACK 처리가 가능하다
 ROLLBACK

    -- Log 기록
 SET NOCOUNT ON
 INSERT INTO DDL_Log (TrData) VALUES (@trData)
GO

/*
   DDL 트리거 호출 테스트
*/

-- 1) DROP TABLE 이벤트는 직접 수행
DROP TABLE TEST01
SELECT * FROM DDL_Log
GO

-- 2) ALTER TABLE 이벤트는 TRY ... CATCH 내에서 수행해 봄
BEGIN TRY
 ALTER TABLE TEST01 ADD b int
END TRY
BEGIN CATCH
 SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
SELECT * FROM DDL_Log

2. 서버 수준의 트리거

-- SQL 로그인 생성을 하지 못하도록 하는 트리거 예.

-- CREATE LOGIN 에 대한 DDL 트리거
CREATE TRIGGER tr_Test2
ON ALL SERVER
FOR CREATE_LOGIN
AS
SET NOCOUNT ON

   -- XQuery 의 value() 메서드를 사용, 특정 요소 값을 검색하고 비교
IF (EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(100)')
   = 'SQL Login')
BEGIN
ROLLBACK
PRINT 'SQL 표준 사용자는 생성할 수 없습니다!'
END
ELSE
BEGIN
PRINT 'Windows 사용자는 생성 가능합니다!'
END
GO

-- SQL 표준 사용자 생성 테스트 => 실패
CREATE LOGIN SqlUser WITH PASSWORD = 'p@ssw0rd'
GO

-- Windows 표준 사용자 생성 테스트
CREATE LOGIN [SQL2005PC\Guest] FROM WINDOWS
GO
DROP LOGIN [SQL2005PC\Guest]
GO