tempdb 사용량에 문제를 일으키는 쿼리 찾기
누군가 tempdb 가 이상해요 라고 연락이 왔다. 아래 문서와 쿼리를 이용해 문제를 해결한다.
workingwithtempdb.doc
tempdb 를 core 수만큼 만드는 것은 tempdb 유지관리 비용으로 인해 allocation contention 해결 비용보다 더 클 수 도 있다는 설명도 들어있는 좋은 문서이다. workload 에 따라서 튜닝방법은 달라져야 한다.
Optimizing_tempdb_Performance_chicago.pdf
CREATE database perf_warehouse
GO
USE perf_warehouse
GO
CREATE TABLE tempdb_space_usage (
-- This represents the time when the particular row was
-- inserted
dt datetime DEFAULT CURRENT_TIMESTAMP,
-- session id of the sessions that were active at the time
session_id int DEFAULT null,
-- this represents the source DMV of information. It can be
-- track instance, session or task based allocation information.
scope varchar(10),
-- instance level unallocated extent pages in tempdb
Instance_unallocated_extent_pages bigint,
-- tempdb pages allocated to verstion store
version_store_pages bigint,
-- tempdb pages allocated to user objects in the instance
Instance_userobj_alloc_pages bigint,
-- tempdb pages allocated to internal objects in the instance
Instance_internalobj_alloc_pages bigint,
-- tempdb pages allocated in mixed extents in the instance
Instance_mixed_extent_alloc_pages bigint,
-- tempdb pages allocated to user obejcts within this sesssion or task.
Sess_task_userobj_alloc_pages bigint,
-- tempdb user object pages deallocated within this sesssion
-- or task.
Sess_task_userobj_deallocated_pages bigint,
-- tempdb pages allocated to internal objects within this sesssion
-- or task
Sess_task_internalobj_alloc_pages bigint,
-- tempdb internal object pages deallocated within this sesssion or
-- task
Sess_task_internalobj_deallocated_pages bigint,
-- query text for the active query for the task
query_text nvarchar(max)
)
go
-- Create a clustered index on time column when the data was collected
CREATE CLUSTERED INDEX cidx ON tempdb_space_usage (dt)
go
아래 프로시저를 주기적으로 돌려 tempdb 문제를 일으키는 쿼리를 찾는다.
CREATE PROC sp_sampleTempDbSpaceUsage AS
-- Instance level tempdb File space usage for all files within
-- tempdb
INSERT tempdb_space_usage (
scope,
Instance_unallocated_extent_pages,
version_store_pages,
Instance_userobj_alloc_pages,
Instance_internalobj_alloc_pages,
Instance_mixed_extent_alloc_pages)
SELECT
'instance',
SUM(unallocated_extent_page_count),
SUM(version_store_reserved_page_count),
SUM(user_object_reserved_page_count),
SUM(internal_object_reserved_page_count),
SUM(mixed_extent_page_count)
FROM sys.dm_db_file_space_usage
-- 2. tempdb space usage per session
--
INSERT tempdb_space_usage (
scope,
session_id,
Sess_task_userobj_alloc_pages,
Sess_task_userobj_deallocated_pages,
Sess_task_internalobj_alloc_pages,
Sess_task_internalobj_deallocated_pages)
SELECT
'session',
session_id,
user_objects_alloc_page_count,
user_objects_dealloc_page_count,
internal_objects_alloc_page_count,
internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50
-- 3. tempdb space usage per active task
--
INSERT tempdb_space_usage (
scope,
session_id,
Sess_task_userobj_alloc_pages,
Sess_task_userobj_deallocated_pages,
Sess_task_internalobj_alloc_pages,
Sess_task_internalobj_deallocated_pages,
query_text)
SELECT
'task',
R1.session_id,
R1.user_objects_alloc_page_count,
R1.user_objects_dealloc_page_count,
R1.internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count,
R3.text
FROM sys.dm_db_task_space_usage AS R1
LEFT OUTER JOIN
sys.dm_exec_requests AS R2
ON R1.session_id = R2.session_id
OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
WHERE R1.session_id > 50
v_backup.exe
