tempdb 대량 I/O 발생시키는 쿼리 확인

-- tempdb 의 I/O 관련 장애처리

CREATE DATABASE DBA

GO

USE DBA

GO

-- DROP TABLE dbo.TBL_ANALYSIS_MONITOR_TEMPDB

-- GO

CREATE TABLE dbo.TBL_ANALYSIS_MONITOR_TEMPDB

(

Event_Date datetime -- 로깅 시작

, Event_Idx int identity -- 중복방지 자동증가값

, Task_UserObj_Alloc_Cnt int -- 유저오브젝트 페이지 할당수

, Task_UserObj_Dealloc_Cnt int -- 유저오브젝트 페이지 해제수

, Task_Internal_Alloc_Cnt int -- 내부오브젝트 페이지 할당수

, Task_Internal_Dealloc_Cnt int -- 내부오브젝트 페이지 해제수

, Degree_of_Parallelism smallint -- 병렬처리수준

, Database_ID int -- DB_ID

, User_ID int -- 실행 유저 ID

, SQL_Handle varbinary(64) -- 후에 추적 가능하도록 로깅

, Plan_Handle varbinary(64) -- 후에 추적 가능하도록 로깅

, SQL_Text100 nvarchar(100) -- 핸들 조인 안될경우 대비

, CONSTRAINT CPK__TBL_ANALYSIS_MONITOR_TEMPDB__01 PRIMARY KEY CLUSTERED (Event_Date, Event_Idx)

);

GO

IF object_id('dbo.USP_TEMP_IO_GETTER') IS NULL

EXEC ('CREATE PROC dbo.USP_TEMP_IO_GETTER AS SELECT 1')

GO

ALTER PROCEDURE dbo.USP_TEMP_IO_GETTER

(

@pRoop_Cnt int = 60

)

AS

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @vDate_Time datetime, @vCnt int = 0

WHILE @vCnt < @pRoop_Cnt

BEGIN

SELECT @vDate_Time = GETDATE(), @vCnt += 1;

WITH cteSU AS (

SELECT TOP 10

session_id

, request_id

, SUM(user_objects_alloc_page_count) as [userobj_alloc_cnt]

, SUM(user_objects_dealloc_page_count) as [userobj_dealloc_cnt]

, SUM(internal_objects_alloc_page_count) as [internal_alloc_cnt]

, SUM(internal_objects_dealloc_page_count) as [internal_dealloc_cnt]

, COUNT(session_id) as [degree_of_parallelism]

FROM tempdb.sys.dm_db_task_space_usage

WHERE database_id = 2

AND (user_objects_alloc_page_count > 0 OR internal_objects_alloc_page_count > 0)

AND session_id <> @@spid

AND session_id > 50

GROUP BY session_id, request_id

ORDER BY SUM(user_objects_alloc_page_count)

+ SUM(internal_objects_alloc_page_count) DESC

)

INSERT INTO DBA.dbo.TBL_ANALYSIS_MONITOR_TEMPDB

(Event_Date

, Task_UserObj_Alloc_Cnt

, Task_UserObj_Dealloc_Cnt

, Task_Internal_Alloc_Cnt

, Task_Internal_Dealloc_Cnt

, Degree_of_Parallelism

, Database_ID

, User_ID

, SQL_Handle

, Plan_Handle

, SQL_Text100

)

SELECT GETDATE() AS event_date

, A.userobj_alloc_cnt

, A.userobj_dealloc_cnt

, A.internal_alloc_cnt

, A.internal_dealloc_cnt

, A.degree_of_parallelism

, ER.database_id

, ER.user_id

, ER.sql_handle

, ER.plan_handle

, LEFT(ST.text, 100)

FROM cteSU AS A 

INNER JOIN sys.dm_exec_requests AS ER

ON ER.session_id = A.session_id AND ER.request_id = A.request_id

OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST

WAITFOR DELAY '00:00:01'

END

END;

GO

SELECT ST.text as [SQL_Text]

, QP.query_plan as [Plan_XML]

, AMT.Max_Task_UserObj_Alloc_Cnt

, AMT.Max_Task_Internal_Alloc_Cnt

, AMT.Database_ID

, AMT.SQL_Text100

FROM (

SELECT TOP 10

SQL_Handle, Plan_Handle, Database_ID

, MAX(Task_UserObj_Alloc_Cnt) as [Max_Task_UserObj_Alloc_Cnt]

, MAX(Task_Internal_Alloc_Cnt) as [Max_Task_Internal_Alloc_Cnt]

, SQL_Text100

FROM DBA.dbo.TBL_ANALYSIS_MONITOR_TEMPDB

GROUP BY SQL_Handle, Plan_Handle, Database_ID, SQL_Text100

ORDER BY MAX(Task_UserObj_Alloc_Cnt) + MAX(Task_Internal_Alloc_Cnt) DESC

) AS AMT 

OUTER APPLY sys.dm_exec_sql_text(AMT.sql_handle) as ST

OUTER APPLY sys.dm_exec_query_plan(AMT.Plan_Handle) as QP

GO

EXEC dbo.USP_TEMP_IO_GETTER;