[오류 사항]
- 오류 : 이 오류 메시지는 SQL Server I/O 에 대한 병목 현상을 나타냅니다.
- sql server has encountered 1
occurrence(s) of i/o requests taking longer than 15 seconds to complete
- autogrow of file 'templog' in
database 'tempdb' was cancelled by user or timed out after 117437ms
[추후 동일 증상 발생 시 확인 사항]
- 현재 대기 보류 중인 I/O를 확인 합니다.
SELECT SUM(pending_disk_io_count) AS [Number of pending I/Os] FROM
sys.dm_os_schedulers
- 1번 쿼리에서 보고 된 I/O 카운터에 대하여 세부 사항을 확인 합니다.
- IO_Pending 열 값이 0 이면 SQL Server 는 아직 I/O 를 처리 하지 않은 것 입니다.
- IO_Pending 열 값이 1 이면 Windows 에서 아직 I/O 를 처리 하지 않은 것 입니다.
SELECT *
FROM sys.dm_io_pending_io_requests
- 보류 중인 I/O 를 통해서 디스크 병목 현상을 확인 합니다.
SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending , r.io_pending_ms_ticks
, r.io_type , fs.num_of_reads , fs.num_of_writes
FROM sys.dm_io_pending_io_requests AS
r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL)
AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files
AS mf ON fs.database_id = mf.database_id
AND fs.file_id = mf.file_id ORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;
- I/O 유발 쿼리를 찾습니다.
select top 5 (total_logical_reads/execution_count) as
avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_physical_reads,
Execution_count, statement_start_offset, p.query_plan, q.text
from sys.dm_exec_query_stats
cross apply sys.dm_exec_query_plan(plan_handle) p
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by (total_logical_reads
+ total_logical_writes)/execution_count
Desc
- 테이블 당 인덱스 사용 통계 / 테이블 당 가장 많은 I/O 작업을 유발하는 테이블을 확인 합니다.
SELECT d.name
as [Database],
OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS
[INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS
S
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID
= S.INDEX_ID
Join sys.Databases d on s.database_id = d.database_id
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
Order by USER_SEEKS + USER_SCANS +
USER_LOOKUPS + USER_UPDATES desc
SELECT d.name, t.name, OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
A.LEAF_INSERT_COUNT,
A.LEAF_UPDATE_COUNT,
A.LEAF_DELETE_COUNT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS
I ON I.[OBJECT_ID] = A.[OBJECT_ID]
join sys.tables t on i.object_id = t.object_id
join sys.databases d on a.database_id = d.database_id
AND I.INDEX_ID
= A.INDEX_ID WHERE OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
order by A.LEAF_INSERT_COUNT
+ A.LEAF_UPDATE_COUNT
+ A.LEAF_DELETE_COUNT
desc
USE TEMPDB;
-- TEMPDB allocated by session:
select *, (allocated_mb
- deallocated_mb)reserved_mb
from
(
select session_id
,(((sum(internal_objects_alloc_page_count
+ user_objects_alloc_page_count)*8024)/1024)/1024) as allocated_mb
,(((sum(internal_objects_dealloc_page_count
+ user_objects_dealloc_page_count)*8024)/1024)/1024) as deallocated_mb
from sys.dm_db_task_space_usage
group by session_id
) x where
allocated_mb > 0;
-- TEMPDB allocated by object:
select o.type_desc
, substring(o.name,1,charindex('__',o.name)+1)+right(o.name,12) table_shortname
, si.name
index_name
, case
si.index_id when
0 then 'HEAP' when 1 then 'CLUSTERED' else 'NONCLUSTERED' end
index_type
, row_count
, ((reserved_page_count * 8024) / 1024 / 1024)reserved_mb
, create_date
from sys.dm_db_partition_stats
ps
left join sys.objects o on ps.object_id = o.object_id
left join sys.indexes si on si.object_id = o.object_id and si.index_id = ps.index_id
where is_ms_shipped = 0
order by reserved_page_count desc;
-- TEMPDB Data/Log File Auto Growth events:
DECLARE @filename VARCHAR(500)
SELECT @filename = SUBSTRING(path, 0,LEN(path) - CHARINDEX('\',REVERSE(path)) + 1)+ '\Log.trc'
FROM sys.traces
WHERE
is_default = 1 ;
SELECT TOP 100
te.Name AS EventName
,StartTime
,NTDomainName
,NTUserName
,LoginName
,ApplicationName
,HostName
,DatabaseName
,Filename
,IntegerData/128 [Size MB]
,CAST(Duration/1000000. AS DECIMAL(20,2)) AS [Duration sec]
,EndTime
,SPID
,SessionLoginName
,Error
,Success
,IsSystem
FROM fn_trace_gettable(@fileName, DEFAULT) gt
INNER JOIN sys.trace_events te ON
EventClass = te.trace_event_id
WHERE EventClass IN(92, 93) -- Data/Log File Auto
Grow
AND DatabaseName = 'tempdb'
ORDER BY StartTime DESC;
- Tempdb 에 대한 추적 플래그 –T1118 을 설정 합니다.
