TEMPDB Usage

  • Hi All

    I'm trying to test the affect on tempdb of a query with a large sort operation.

    I'm using the below scripts to check tempdb

    --script 1

    select top 10

    t1.session_id,

    t1.request_id,

    t1.task_alloc,

    t1.task_dealloc,

    (SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,

    (CASE WHEN statement_end_offset = -1

    THEN LEN(CONVERT(nvarchar(max),text)) * 2

    ELSE statement_end_offset

    END - t2.statement_start_offset)/2)

    FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,

    (SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan

    from (Select session_id, request_id,

    sum(internal_objects_alloc_page_count + user_objects_alloc_page_count) as task_alloc,

    sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc

    from sys.dm_db_task_space_usage

    group by session_id, request_id) as t1,

    sys.dm_exec_requests as t2

    where t1.session_id = t2.session_id and

    (t1.request_id = t2.request_id) and

    t1.session_id > 50

    order by t1.task_alloc DESC

    And

    --script 2

    select ES.session_id

    , ES.login_time

    , DB_NAME(SU.database_id)

    , ES.host_name

    , user_objects_alloc_page_count

    , user_objects_dealloc_page_count

    , internal_objects_alloc_page_count

    , internal_objects_alloc_page_count

    from sys.dm_db_session_space_usage SU

    inner join sys.dm_exec_sessions ES

    on SU.session_id = ES.session_id

    where DB_NAME(database_id) = 'tempdb'

    and ES.is_user_process <> 0

    The problem is that for the first script, I'm seeing significant amounts in the t1.task_alloc column but in the second script, is shows zero for internal_objects_alloc_page_count

    and user_objects_alloc_page_count for the same session_id

    Am I missing something here?

    Thanks

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply