Disk I/O Query

[오류 사항]

• 오류 : 이 오류 메시지는 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

• 담당자: 김석수 책임
• 시간: 2시간

 

 

[추후 동일 증상 발생 시 확인 사항]

1. 현재 대기 보류 중인 I/O를 확인 합니다.

SELECT SUM(pending_disk_io_count) AS [Number of pending I/Os] FROM sys.dm_os_schedulers

 

2. 1번 쿼리에서 보고 된 I/O 카운터에 대하여 세부 사항을 확인 합니다.

• IO_Pending 열 값이 0 이면 SQL Server 는 아직 I/O 를 처리 하지 않은 것 입니다.
• IO_Pending 열 값이 1 이면 Windows 에서 아직 I/O 를 처리 하지 않은 것 입니다.

SELECT *  FROM sys.dm_io_pending_io_requests

 

3. 보류 중인 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 ;

 

4. 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

 

5. 테이블 당 인덱스 사용 통계 / 테이블 당 가장 많은 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

 

• Tempdb 증가 원인 추적

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 을 설정 합니다.

• DBCC traceon(1118, -1)