MS SQL Monitoring ( 출처 SQLTAG 1,2권 )

MS SQL Monitoring 

( 출처 SQLTAG 1,2권 )

출처 : SQLTAG BOOK 2권 

스크립트 : http://cafe.naver.com/sqlmvp/4710

내용 : MS SQL SNAPSHOT 작성  ( Oracle AWR 같은 것 ) 

-- !!! 주의 
-- 1. ADhoc 쿼리가 많을 경우, 수집에 따른 부하가 매우 큼
-- 2. sql_handle 갯수가 많은 경우, 마찬가지로 수집에 따른 부하가 매우 큼 
-- 대안 : SQL TOP 100 개만 저장 
--------------------------------------------------------------------------------------------------------
--  현재 실행중인 쿼리 조회 스크립트
--------------------------------------------------------------------------------------------------------
SELECT     db_name(database_id) as db_info, session_id,    command, start_time, blocking_session_id, wait_type, wait_time, language,   y.*
FROM    sys.dm_exec_requests AS x
        CROSS APPLY
        master.sys.dm_exec_sql_text( x.sql_handle ) AS y
WHERE    session_id > 50
GO
 
-- 출처 : SQLTAG BOOK 2권 
-- 스크립트 : http://cafe.naver.com/sqlmvp/4710
-- SNPASHOT META 정보 조회 ( SNAPSHOT LIST UP ) 
EXEC dbo.SP_GET_SNAPSHOT_META
-- SNAPSHOT 저장 
-- 주의) SNAPSHOT INTERVAL 을 줄이면 줄일수록 SNAPSHOT 수집에 따른 부하, 사이즈 증가가 일어날수 있음 
EXEC DBO.SP_SET_SNAPSHOT 
-- SNAPSHOT 간 REPORT 수행 
-- 1)Top Query 2)Wait Event 3)File I/O 4)Object I/O 5)Object Size
EXEC DBO.SP_GET_SNAPSHOT 5,6
 
--------------------------------------------------------------------------------------------------------
-- 1. sys.dm_exec_query_stats ( Top Query )
--------------------------------------------------------------------------------------------------------
-- 지금까지 서버에서 실행된 쿼리에 대한 누적 통계 제공 V$SQL - DBA_HIST_SQLSTAT 
-- 현재 수행 중인 쿼리는 반영 되지 않는다 ( V$SQL 은 현재 수행 중인 쿼리도 반영됨  완료 후, V$SQL.EXECUTION = 1 로 업데이트 됨 )
-- worker_time  - CPU 
-- Elpased Time - 수행 시간 
-- physical read - 디스크에서 읽은 페이지 수
-- logical read - 버퍼 풀에서 읽은 페이지 수
select * from master.sys.dm_exec_query_stats ;
 
--------------------------------------------------------------------------------------------------------
-- 2. sys.dm_os_wait_stats ( Waits Event ) 
--------------------------------------------------------------------------------------------------------
-- 인스턴스 범위 지금까지 발생한 대기에 대한 누적 정보 제공 View 
-- 대기에는 리소스 대기, 시그널 대기 두가지 유형이 있다.
-- 시그널 대기 - CPU 대기 
-- 리소스 대기 - CPU 외 대기
-- wait_time_ms  - 시그널 대기 포함 
-- wait_time_ms - signal_wait_time_ms = 리소스 대기 
-- wait_time = resource_wait_time + singal_wait_time 
-- sys.dm_exec_request.status 1)Background 2)Running 3)Runnable ( 시그널 대기 ) 
--                            4) Sleeping  ( SQL Server 가 다음 명령어 오기를 대기 )
--                            5) Suspended ( 리소스 대기 , 요청된 리소스가 사용 가능한 상태가 되도록 대기 )
-- 
select * from sys.dm_os_wait_stats 
 
-- 해당 DMV 초기화
DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR)
GO
 
CREATE DATABASE SQLTAGTESTDB ON PRIMARY 
( NAME = N'SQLTAGTESTDB'
,FILENAME = N'C:\SQL_DATA\SQLTAGTESTDB.MDF'
,SIZE = 1000000KB
,MAXSIZE = UNLIMITED
,FILEGROWTH =  1000000KB  )
LOG ON 
( NAME = N'TEST_LOG'
,FILENAME = N'C:\SQL_LOG\SQLTESTDB_LOG.LDF'
,SIZE = 10000KB
,MAXSIZE= 2048 GB
,FILEGROWTH = 100000KB );
GO
 
SELECT * FROM SYS.DM_OS_WAIT_STATS WHERE WAIT_TYPE ='PREEMPTIVE_OS_WRITEFILEGATHER';
 
-- SQLTAGTESTDB DB 삭제 
-- gpedit.msc 에서 해당 권한에 SQL Server 시작 계정 추가
-- 링크 - https://docs.microsoft.com/ko-kr/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-2017
-- SQLTAGTESTDB DB 다시 생성
-- PREEMPTIVE_OS_WRITEFILEGATHER 대기 다시 확인 
 
-- wait_type                        waiting_tasks_count    wait_time_ms    max_wait_time_ms    signal_wait_time_ms
-- PREEMPTIVE_OS_WRITEFILEGATHER    2                    128                116                    0
-- 반영 후 ( 개인 테스트로는 반영 전/후 WAIT(대기) 차이가 없음 ( 파일 사이즈를 더 크게 할걸... ) 
-- PREEMPTIVE_OS_WRITEFILEGATHER    2                    131                120                    0
 
--------------------------------------------------------------------------------------------------------
-- 3. sys.dm_io_virtual_file_stats ( File I/O ) 
--------------------------------------------------------------------------------------------------------
-- 누적값으로 데이터 및 로그 파일에 대한 I/O 통계
-- num_of_reads 읽기 횟수
-- num_of_bytes_read 읽은 총 바이트 수
-- io_stall_read_ms 읽은 시간( ms ) 
-- num_of_writes 쓰기 횟수
-- num_of_bytes_written 쓰기 총 바이트 수
-- io_stall_wirte_ms 쓰기 시간( ms )
-- io_stall i/o 총 시간( ms )
SELECT * FROM sys.dm_io_virtual_file_stats(null,null)  
 
 
 
--/**
-- * 쿼리 13. File I/O 조회 스크립트
--**/
 
 
USE [master]
GO
drop table dbo.TBL_TEST
go
CREATE TABLE dbo.TBL_TEST
(    A bigint identity(1,1)
,     B char(8000)
)
GO
 
INSERT INTO dbo.TBL_TEST ( B ) VALUES ( 'A' )
GO 1000
 
-- Buffer Pool 에 존재하는 데이터 페이즈를 제거하는 구문
-- 아직 Disk에 Write 되지 않은 Dirty Block 은 남아 있음 
DBCC dropcleanbuffers
GO
 
EXEC dbo.SP_SET_SNAPSHOT
GO
 
DBCC dropcleanbuffers
GO
 
SELECT COUNT(*) FROM dbo.TBL_TEST
GO
 
EXEC dbo.SP_SET_SNAPSHOT
GO
 
EXEC dbo.SP_GET_SNAPSHOT_META
GO
-- 파일 I/O 관련 정렬 없음
EXEC dbo.SP_GET_SNAPSHOT 8,9
GO
 
--------------------------------------------------------------------------------------------------------
-- 4. sys.dm_io_virtual_file_stats ( File I/O ) 
--------------------------------------------------------------------------------------------------------
--  DB Object(Index) 의 IO, 잠금, 래치 등 통계정보를 제공하는 View
 
SELECT *
FROM    SYS.dm_db_index_operational_stats(null,null,null,null) 
GO
-- 앞 테스트 결과 다시 REPORT 만 다시 조회
-- leaf_insert_count DESC 
EXEC dbo.SP_GET_SNAPSHOT 8,9
GO
 
 
--------------------------------------------------------------------------------------------------------
-- 5. dbo.sysobject, dbo.sysindexes(Object Size)
--------------------------------------------------------------------------------------------------------
-- Database Level VIew
-- 
USE MASTER
GO
 
EXEC dbo.SP_GET_SNAPSHOT_META
GO
 
INSERT INTO TBL_TEST( B) VALUES ('A')
GO 2000
 
EXEC dbo.SP_SET_SNAPSHOT
GO
 
EXEC dbo.SP_GET_SNAPSHOT_META 
GO
-- 
-- last_reserved DESC 정렬 결과 
EXEC dbo.SP_GET_SNAPSHOT 9,10 
GO