-- 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'
'연구개발 > DBA' 카테고리의 다른 글
페이징 (0) | 2013.07.22 |
---|---|
SQL Server 2012에서 새로워진 T-SQL 프로그램 기능 (0) | 2013.07.22 |
병목현상 및 부하 쿼리 (0) | 2013.07.09 |
tempdb 이동, 분할 스크립트 (0) | 2013.07.02 |
데드락 deadlock (0) | 2013.06.21 |