반응형
/********************************************************************************************
-- Title : [2k5] DMV 이용한 모니터링
-- Reference : mcpworld.com 하성희
-- Key word : dynamic managent view 동적 관리 뷰 dmv
********************************************************************************************/


/******************************************************************
▣ DMV (Dynamic Management View)를 활용한 리소스 병목 점검
*******************************************************************/
------------------------------------------------------------------
-- ▶ 현재 실행 중인 SQL 문 확인
------------------------------------------------------------------
SELECT r.session_id
  ,status
  ,substring(qt.text,r.statement_start_offset/2,
   (CASE WHEN r.statement_end_offset = -1
   THEN len(convert(nvarchar(max), qt.text)) * 2
   ELSE r.statement_end_offset END - r.statement_start_offset)/2)
  AS query_text  -- 이 SELECT 문을 실행하는 그 시점에 실행중인 SQL문
  ,qt.dbid
  ,qt.objectid
  ,r.cpu_time
  ,r.total_elapsed_time
  ,r.reads
  ,r.writes
  ,r.logical_reads
  ,r.scheduler_id
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
ORDER BY r.scheduler_id, r.status, r.session_id;
GO

/*
다른 세션들에서는 SQL Server에 연결은 해 놓은 상황이지만
실제로 위의 DMV SELECT 문을 실행하는 바로 그 시점에는
이미 작업이 완료되어 어떤 작업을 수행하는 상태가 아니라면,
위의 DMV SELECT 문은 이 세션에 대한 정보만 반환한다.
(이 DMV SELECT문을 실행하는 세션 때문에, 최소 1개 행은 반환된다)
-->
만약, 위의 DMV SELECT 문이 1개 행을 반환한다면
이 세션의 SPID를 조회하여 위의 SELECT 문이 반환한 1개 행의 SPID 와 대조해 보자.
이 세션의 SPID 는 Global Variable @@SPID 를 조회하면 얻을 수 있다.
*/
-- 현재 사용자 프로세스의 서버 프로세스 식별자(ID)가 반환된다.
SELECT @@SPID;
GO
-- 현재 SQL Server에 연결 중인 모든 사용자와 프로세스에 대한 정보를 반환합니다.
EXEC sp_who;
GO
-- 사용자가 다음 명령어를 실행하기를 기다리는 프로세스들은 제외합니다.
EXEC sp_who 'active';
GO
-- DBCC INPUTBUFFER 명령어를 사용하면
-- 각 SPID에서 마지막으로 실행한 SQL 문을 확인할 수 있습니다.
DBCC INPUTBUFFER (@@SPID);
GO
------------------------------------------------------------------
-- ▶ CPU를 많이 사용하는 상위 프로시저 및 일괄 처리(Batch)
------------------------------------------------------------------
SELECT TOP 50
 sum(qs.total_worker_time) AS total_cpu_time,
 sum(qs.execution_count) AS total_execution_count,
 count(*) AS '#_statements',
 qt.dbid,
 qt.objectid,
 qs.sql_handle,
 qt.[text]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
GROUP BY qt.dbid,qt.objectid, qs.sql_handle,qt.[text]
ORDER BY sum(qs.total_worker_time) DESC, qs.sql_handle;
GO

------------------------------------------------------------------
-- ▶ AVG CPU TIme 순 상위 50개 SQL 문
------------------------------------------------------------------
SELECT TOP 50
        qs.total_worker_time/qs.execution_count as [Avg CPU Time],
        SUBSTRING(qt.text,qs.statement_start_offset/2,
   (case when qs.statement_end_offset = -1
   then len(convert(nvarchar(max), qt.text)) * 2
   else qs.statement_end_offset end -qs.statement_start_offset)/2)
  as query_text,
  qt.dbid,
  qt.objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY [Avg CPU Time] DESC;
GO
------------------------------------------------------------------
-- ▶ 병렬로 실행중인 프로세스 확인
--    셈플로 병렬처리 쿼리를 만들 수 없을까?
------------------------------------------------------------------
SELECT r.session_id,
 r.request_id,
 max(isnull(exec_context_id, 0)) AS number_of_workers,
 r.sql_handle,
 r.statement_start_offset,
 r.statement_end_offset,
 r.plan_handle
FROM sys.dm_exec_requests r
 join sys.dm_os_tasks t ON r.session_id = t.session_id
 join sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE s.is_user_process = 0x1
GROUP BY r.session_id, r.request_id, r.sql_handle, r.plan_handle,
 r.statement_start_offset, r.statement_end_offset
HAVING max(isnull(exec_context_id, 0)) > 0;
GO
------------------------------------------------------------------
-- ▶ CPU > Duration인 쿼리 확인
------------------------------------------------------------------
SELECT
 qs.sql_handle,
 qs.statement_start_offset,
 qs.statement_end_offset,
 q.dbid,
 q.objectid,
 q.number,
 q.encrypted,
 q.text,
 qs.total_worker_time,
 qs.total_elapsed_time,
 qs.total_elapsed_time - qs.total_worker_time AS Gap
FROM sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS q
WHERE qs.total_worker_time > qs.total_elapsed_time
GO
------------------------------------------------------------------
-- ▶ CPU : 컴파일 시간
------------------------------------------------------------------
SELECT * FROM sys.dm_exec_query_optimizer_info
GO
------------------------------------------------------------------
-- ▶ 재컴파일
------------------------------------------------------------------
SELECT TOP 25
 sql_text.text,
 sql_handle,
 plan_generation_num,
 execution_count,
 dbid,
 objectid
FROM sys.dm_exec_query_stats a
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
WHERE plan_generation_num >1
ORDER BY plan_generation_num DESC;
GO
------------------------------------------------------------------
-- ▶ 메모리 버퍼를 오브젝트를 기준으로 분류 (테이블,인덱스)
------------------------------------------------------------------
SELECT b.database_id
  ,p.object_id
  ,object_name(p.object_id) as objname
  ,p.index_id
  ,buffer_count=count(*)
FROM sys.allocation_units a,
  sys.dm_os_buffer_descriptors b,
  sys.partitions p
WHERE a.allocation_unit_id = b.allocation_unit_id
AND a.container_id = p.hobt_id
GROUP BY b.database_id, p.object_id, p.index_id
ORDER BY buffer_count DESC;
GO
------------------------------------------------------------------
-- ▶ I/O 기준 상위 50개 SQL 문
------------------------------------------------------------------
SELECT TOP 50
    (qs.total_logical_reads+qs.total_logical_writes)/qs.execution_count AS [Avg IO],
 SUBSTRING(qt.text,qs.statement_start_offset/2,    
 (case when qs.statement_end_offset = -1
    then len(convert(nvarchar(max), qt.text)) * 2
    else qs.statement_end_offset end -qs.statement_start_offset)/2)
 AS query_text,
 qt.dbid,
 qt.objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY [Avg IO] DESC;
GO
------------------------------------------------------------------
-- ▶ 파일 별 IO 대기
------------------------------------------------------------------
SELECT database_id
  , file_id
  , io_stall,io_pending_ms_ticks
  , scheduler_address
FROM sys.dm_io_virtual_file_stats (NULL, NULL) AS t1,
     sys.dm_io_pending_io_requests AS t2
WHERE t1.file_handle = t2.io_handle;
GO
SELECT * FROM sys.dm_io_virtual_file_stats (NULL, NULL);
SELECT * FROM sys.dm_io_pending_io_requests;

------------------------------------------------------------------
-- ▶ I/O 할당 대기
------------------------------------------------------------------
-- Tempdb (DBID=2)
SELECT session_id, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGE%LATCH_%'
AND resource_description LIKE '2:%';
GO
-- 사용자 DB (다음에서 dbid 부분을 db_id() 값을 입력하여 실행하면 됨 )
SELECT session_id, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGE%LATCH_%'
AND resource_description LIKE '5:%';
GO
------------------------------------------------------------------
-- ▶ tempdb 공간 사용 현황 조회
------------------------------------------------------------------
SELECT  
 SUM (user_object_reserved_page_count)*8 AS user_objects_kb,
 SUM (internal_object_reserved_page_count)*8 AS internal_objects_kb,
 SUM (version_store_reserved_page_count)*8 AS version_store_kb,
 SUM (unallocated_extent_page_count)*8 AS freespace_kb
FROM sys.dm_db_file_space_usage
WHERE database_id = 2;
GO
------------------------------------------------------------------
-- ▶ BLOCKING 점검 SP
-- master DB에 생성해 두고 활용하면 편리합니다.
------------------------------------------------------------------
create proc dbo.sp_block (@spid bigint=NULL)
as
-- This stored procedure is provided "AS IS" with no warranties, and confers no rights.
-- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
-- This proc reports blocks
-- 1. optional parameter @spid
select t1.resource_type
 , 'database'=db_name(resource_database_id)
 , 'blk object' =isnull(object_name(t1.resource_associated_entity_id) ,t1.resource_associated_entity_id)
 , t1.request_mode
 , t1.request_session_id   -- spid 
 , t2.blocking_session_id  -- spid 
from sys.dm_tran_locks as t1,
 sys.dm_os_waiting_tasks as t2
where t1.lock_owner_address = t2.resource_address
and t1.request_session_id = isnull(@spid,t1.request_session_id)
GO
------------------------------------------------------------------
-- ▶ 유용하지 않은 인덱스 찾기
------------------------------------------------------------------
CREATE DATABASE idxtestdb -- 실제 운영환경에서는 크기, 확장크기 등 지정 요망
GO
USE idxtestdb
GO
SELECT db_id()
GO
-- 6
DROP TABLE t1, t2
GO
-- 동일한 구조를 가지는 테이블을 두 개 생성합니다.
CREATE TABLE t1 (c1 int, c2 char(8000) DEFAULT ('test'))
INSERT INTO t1 (c1) SELECT id FROM sysobjects
CREATE UNIQUE INDEX NX_c1 ON t1 (c1)
GO
CREATE TABLE t2 (c1 int, c2 char(8000) DEFAULT ('test'))
INSERT INTO t2 (c1) SELECT id FROM sysobjects
CREATE UNIQUE INDEX NX_c1 ON t2 (c1)-- 이해를 돕기 위하여 인덱스를 생성하였음. (실제로는 PK constraint를 설정해야 합니다)
GO
-- 사용하지 않은 인덱스 확인
SELECT object_name(i.object_id) AS Table_Name, i.name AS Index_Name
FROM sys.indexes i, sys.objects o
WHERE  i.index_id NOT IN
 (SELECT s.index_id
  FROM sys.dm_db_index_usage_stats s
  WHERE s.object_id=i.object_id and
    i.index_id=s.index_id and
   database_id = db_id() )  -- dbid : db_id() 값을 입력
AND o.type = 'U'
AND o.object_id = i.object_id
ORDER BY object_name(i.object_id) ASC
GO
/* 결과
Table_Name Index_Name
---------- ----------
t1   NX_c1
t2   NX_c1
*/
-- t1 테이블에 대하여 미리 만들어 준 인덱스를 사용하는 SQL 문을 실행
DELETE t1 WHERE c1 = 9999999
GO
-- 사용하지 않은 인덱스 재확인
SELECT object_name(i.object_id) AS Table_Name, i.name AS Index_Name
FROM sys.indexes i, sys.objects o
WHERE  i.index_id NOT IN
 (SELECT s.index_id
  FROM sys.dm_db_index_usage_stats s
  WHERE s.object_id=i.object_id and
    i.index_id=s.index_id and
   database_id = db_id() )  -- dbid : db_id() 값을 입력
AND o.type = 'U'
AND o.object_id = i.object_id
ORDER BY object_name(i.object_id) ASC;
GO
/*
Table_Name Index_Name
---------- ----------
t2   NX_c1
*/
SELECT * FROM Sys.dm_db_missing_index_group_stats;
SELECT * FROM Sys.dm_db_missing_index_groups;
SELECT * FROM Sys.dm_db_missing_index_details;
------------------------------------------------------------------
-- ▶ Default Trace
------------------------------------------------------------------
EXEC sp_configure 'show advanced option', '1'  -- 1:고급옵션, 2:기본옵션
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'default trace enabled'
GO
--> 디폴트 값이 1임
-- 임시로 변경
-- EXEC sp_configure 'xp_cmdshell', 1
-- RECONFIGURE WITH OVERRIDE
-- GO
EXEC master..xp_cmdshell
'DIR C:\"Program Files"\"Microsoft SQL Server"\MSSQL.1\MSSQL\LOG\log*.trc';
GO
--sqlcmd 모드에서
!! DIR C:\"Program Files"\"Microsoft SQL Server"\MSSQL.1\MSSQL\LOG\log*.trc
-- 파일 확인 (LOG 파일명을 탐색기에서 확인하고 로그 파일명을 다음에 입력합니다.)
SELECT t.StartTime, t.EventClass, e.name,
  t.DatabaseName, t.ObjectName, t.ObjectID, t.IndexID
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_24.trc',0) t
  JOIN sys.trace_events e
ON e.trace_event_id = t.eventclass
ORDER BY t.StartTime;
GO
-- 다시 디폴트 상태로 원복
-- EXEC sp_configure 'xp_cmdshell', 0
-- RECONFIGURE WITH OVERRIDE
-- GO
/********************************* 끝 ************************************/

반응형

+ Recent posts