Post reply

Identify tempdb usage

  • September 20, 2016 at 2:33 pm

    #317712

    Comments posted to this topic are about the item Identify tempdb usage

  • October 7, 2016 at 12:41 pm

    #1905444

    David, Thank you for Sharing

    I saw the use of master.dbo.fn_varbintohexstr

    I like your approach better, even if Microsoft doesn't support it anymore (I still see it at SQL 2014)

    Another Article Replacement for using fn_varbintohexstr or the use of the CONVERT

    http://www.sqlservercentral.com/Forums/Topic1708816-391-1.aspx

    Before, I was using this view to get details from TempDB

    SELECT *

    FROM (

    SELECT TOP 100 PERCENT

    spu.session_id

    , db_name(r.database_id) dbname

    , s.host_name host

    , s.login_name loginname

    , s.program_name [program_name]

    , ISNULL(j.name, 'N/A') AS [Job Name]

    , ( CASE WHEN ( CHARINDEX('SQLAgent', s.program_name) > 0 ) THEN REPLACE( SUBSTRING( s.program_name, 67, 10 ), ')', '')

    ELSE 'N/A' END ) step

    , r.start_time

    , r.wait_time

    , r.cpu_time

    , r.logical_reads

    , r.reads

    , r.writes

    , LTRIM( t.text ) text

    FROM sys.dm_db_session_space_usage spu

    JOIN sys.dm_exec_sessions s ON s.session_id = spu.session_id

    JOIN sys.dm_exec_requests r ON s.session_id = r.session_id

    INNER JOIN msdb.dbo.sysjobs j ON master.dbo.fn_varbintohexstr(CONVERT(varbinary(16), job_id)) = SUBSTRING(REPLACE(PROGRAM_NAME, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34)

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) t,

    ( SELECT su.session_id, su.request_id

    , SUM( su.internal_objects_alloc_page_count + su.user_objects_alloc_page_count) Task_Alloc

    , SUM( su.internal_objects_dealloc_page_count + su.user_objects_dealloc_page_count) Task_Dealloc

    FROM sys.dm_db_task_space_usage AS su

    GROUP BY session_id, request_id ) AS su

    WHERE r.status = 'running'

    AND su.session_id = r.session_id

    AND ( su.request_id = r.request_id )

    AND ( s.session_id = su.session_id )

    AND s.login_name NOT IN ('CORP\lmorales','corp\eRuhnke') -- Remove SA users

    AND s.session_id <> ( SELECT @@SPID ) -- Eliminates current user session from results

    AND su.session_id > 50 -- Sessions 50 and below are system sessions and should not be killed

    AND s.program_name NOT IN ('SQL diagnostic manager Collection Service','SQL diagnostic manager Management Service')

    ) cia

    There's still much to learn and improve.

    Thank you.

  • October 10, 2016 at 8:59 am

    #1905680

    What a cool script, thanks.

  • October 10, 2016 at 10:50 am

    #1905714

    Happy to hear you are finding it useful.

Viewing 4 posts - 1 through 3 (of 3 total)

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