모니터링 DMV

-- I/O측정량

select * from sys.dm_io_virtual_file_stats (DB_ID('Petz_Test'),NULL)

go

-- I/O 측정량

select db_name(database_id), file_id ,io_stall_read_ms ,num_of_reads 

,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms' 

,io_stall_write_ms ,num_of_writes 

,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms' 

,io_stall_read_ms + io_stall_write_ms as io_stalls 

,num_of_reads + num_of_writes as total_io 

,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms' 

from sys.dm_io_virtual_file_stats(null,null) order by avg_io_stall_ms desc

go

-- 잠재적으로 유용한 인덱스

select db_name(d.database_id), d.* , s.avg_total_user_cost , s.avg_user_impact , s.last_user_seek ,s.unique_compiles 

from sys.dm_db_missing_index_group_stats s 

,sys.dm_db_missing_index_groups g 

,sys.dm_db_missing_index_details d

where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle 

order by s.avg_user_impact desc 

go

-- 인덱스에 포함되어야 할 칼럼과 사용량

declare @handle int 

select @handle = d.index_handle 

from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g ,sys.dm_db_missing_index_details d 

where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle 

select * from sys.dm_db_missing_index_columns(@handle) 

order by column_id

go

-- IO 기준 상위 50위 쿼리

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

, db_name(qt.dbid)

, object_name(qt.objectid)

FROM sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt 

WHERE db_name(dbid) = N'Petz_Test'

ORDER BY [Avg IO] DESC

-- 가장 낮은 실행계획 재사용률을 나타내는 쿼리

SELECT TOP 50 

qs.sql_handle 

,qs.plan_handle 

,cp.cacheobjtype 

,cp.usecounts 

,(cp.size_in_bytes / 1024.0 / 1024.0) as size_in_MB

,qs.statement_start_offset 

,qs.statement_end_offset 

,db_name(qt.dbid )

,object_name(qt.objectid )

,qt.text 

,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 statement 

FROM sys.dm_exec_query_stats qs 

cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt 

inner join sys.dm_exec_cached_plans as cp 

on qs.plan_handle=cp.plan_handle 

WHERE cp.plan_handle=qs.plan_handle 

and qt.dbid = db_id('Petz_Test') ----- 조회 대상 database ID 를 지정 

ORDER BY [Usecounts] ASC

-- 실행 Statement 단위의 성능 순 조회

select  top (100)

        case

            when c.dbid = 32767 then 'ResourceDB'

            when c.dbid is null then 'NONE'

        else db_name(c.dbid) end as 'DBName'

,       isnull(object_name(c.objectid, c.dbid), c.text) as 'PlanQuery'

,       b.execution_count as 'Total Execute'

,       b.execution_count * 1.0 / datediff(ss, b.creation_time, dateadd(ss, 1, b.last_execution_time)) as 'ExecCount/sec'

,       b.total_worker_time * 1.0 / b.execution_count / 1000 as 'CPUTimes(ms)/exec'

,       b.max_worker_time * 1.0 / 1000 as 'MaxCPUTimes(ms)'

,       b.total_physical_reads / b.execution_count as 'PhysicalReads/exec'

,       b.max_physical_reads as 'MaxPhysicalReads'

,       (b.total_logical_writes + total_logical_reads) / b.execution_count as 'LogicalIO/exec'

,       b.max_logical_writes + b.max_logical_reads as 'MaxLogicalIO'

,       b.total_elapsed_time * 1.0 / b.execution_count / 1000 as 'Duration(ms)/exec'

,       b.max_elapsed_time * 1.0 / 1000 as 'MaxDuration(ms)'

,       substring(c.text, (b.statement_start_offset / 2) + 1, ((case b.statement_end_offset when -1 then datalength(c.text) else b.statement_end_offset end - b.statement_start_offset)/2) + 1) as 'StatementQuery'

,       d.query_plan

, (SELECT CAST(qp.query_plan AS XML) 

FROM sys.dm_exec_text_query_plan(b.plan_handle, b.statement_start_offset, b.statement_end_offset) AS qp)

from              sys.dm_exec_query_stats b with(nolock)

    cross apply   sys.dm_exec_sql_text(b.sql_handle) c

    cross apply   sys.dm_exec_text_query_plan(b.plan_handle, b.statement_start_offset, b.statement_end_offset) d

where c.dbid = db_id('Petz_Test')

--order by 'LogicalIO/exec' desc

--order by 'MaxLogicalIO' desc

--order by 'ExecCount/sec' desc

order by 'CPUTimes(ms)/exec' desc

--order by 'MaxCPUTimes(ms)' desc

--order by 'PhysicalReads/exec' desc

--order by 'MaxPhysicalReads' desc

--order by 'Duration(ms)/exec' desc

--order by 'MaxDuration(ms)' desc

-- 현재 실행되고 있는 쿼리 확인

CREATE FUNCTION dbo.AFN_THREEPARTNAME(

    @object_id INT,

    @database_id INT

)

RETURNS NVARCHAR(1000)

AS

BEGIN

    RETURN (QUOTENAME(DB_NAME(@database_id)) + '.'

          + QUOTENAME(OBJECT_SCHEMA_NAME(@object_id, @database_id))

          + '.' + QUOTENAME(OBJECT_NAME(@object_id, @database_id)));

END

SELECT  r.session_id AS [세션ID] ,

        r.[status] AS [상태],

        r.wait_type AS [대기상태],

        r.scheduler_id AS [SchedulerID],

        CASE WHEN qt.objectid IS NULL THEN 'AD-HOC'

             ELSE dbo.AFN_THREEPARTNAME(qt.objectid, qt.dbid)

        END AS [SP이름] ,

        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 [statement_executing] ,

        DB_NAME(qt.[dbid])AS [DatabaseName] ,

        OBJECT_NAME(qt.objectid) AS [ObjectName] ,

        r.cpu_time / 1000. AS [CPU시간(s)],

        r.logical_reads / 128. AS [논리적읽기(MB)] ,

        r.granted_query_memory / 128. AS [쿼리메모리(MB)],

        r.start_time ,

        r.total_elapsed_time / 1000. AS [실행되고있는시간(s)] ,

        ( r.total_elapsed_time / 1000. ) / 60 AS [실행되고있는시간(m)] ,

        r.reads ,

        r.writes ,

        r.plan_handle ,

        (SELECT CAST(qp.query_plan AS XML) 

FROM sys.dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) AS qp) AS [실행계획보기],

        sp.*

FROM    sys.dm_exec_requests AS r

        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt

        INNER JOIN sys.sysprocesses AS sp

            ON r.session_id = sp.spid

WHERE   r.session_id > 50

       --AND r.session_id != @@SPID

ORDER BY r.total_elapsed_time DESC ,

        r.scheduler_id ,

        r.[status] ,

        r.session_id ;

-- 재컴파일 

select top 25 

sql_text.text, 

sql_handle, 

plan_generation_num, --문이 재컴파일이 발생할 때마다, plan_generation_num 칼럼의 값이 증가

substring(text,qs.statement_start_offset/2

, (case when qs.statement_end_offset = -1 then datalength(convert(nvarchar(max), text)) * 2 

else qs.statement_end_offset 

end - qs.statement_start_offset)/2) as stmt_executing, 

execution_count, 

dbid,

objectid,

db_name(sql_text.dbid), 

object_name(sql_text.objectid )

from sys.dm_exec_query_stats as qs 

Cross apply sys.dm_exec_sql_text(sql_handle) sql_text 

where plan_generation_num >1 

order by sql_handle, plan_generation_num

-- 행 잠금 대기를 검색

-- sp_configure : blocked process threshold

declare @dbid int 

select @dbid = db_id() 

Select dbid=db_name(database_id)

, objectname=object_name(s.object_id)

, indexname=i.name, i.index_id --, partition_number 

, row_lock_count

, row_lock_wait_count 

, [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2)) 

, row_lock_wait_in_ms 

, [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2)) 

from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s, sys.indexes i 

where objectproperty(s.object_id,'IsUserTable') = 1 

and i.object_id = s.object_id 

and i.index_id = s.index_id 

order by row_lock_wait_count desc

-- 대기자 리스트에 있는 SQL문

create proc get_statements_from_waiter_list (@wait_type nvarchar(60)=NULL) 

as 

select  

        r.wait_type 

        ,r.wait_time 

        ,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 

        ,qt.dbid, dbname=db_name(qt.dbid) 

        ,qt.objectid 

        ,r.sql_handle 

        ,r.plan_handle 

FROM sys.dm_exec_requests r 

cross apply sys.dm_exec_sql_text(r.sql_handle) as qt 

where r.session_id > 50 

  and r.wait_type = isnull(upper(@wait_type),r.wait_type) 

go 

 

exec get_statements_from_waiter_list 

--exec get_statements_in_waiter_list @wait_type = 'CXPACKET'