Query details about objects allocated in TEMPDB.

  • Eric M Russell

    SSC Guru

    Points: 125020

    Comments posted to this topic are about the item Query details about objects allocated in TEMPDB.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    I'm not sure how to use the results yet. But it does appear to work on my 2008 system.

  • Eric M Russell

    SSC Guru

    Points: 125020

    Iwas Bornready (1/4/2017)


    I'm not sure how to use the results yet. But it does appear to work on my 2008 system.

    How to use the results? It tells you what application, login, and objects have space allocated in TEMPDB, so if temp storage starts growing unexpectedly, you can narrow down the issue to specific stored procedures or queries.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • kast218

    SSCertifiable

    Points: 6286

    Hi, Eric nice query.

    But one pull request: replace USE TEMPDB; on USE tempdb; for case sensitive instance.

  • Eric M Russell

    SSC Guru

    Points: 125020

    kast218 (1/4/2017)


    Hi, Eric nice query.

    But one pull request: replace USE TEMPDB; on USE tempdb; for case sensitive instance.

    Ugh... I've actually never worked with a server that has case sensitivity enabled. That must be pain. What is the advantage or reasoning for this?

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • kast218

    SSCertifiable

    Points: 6286

    Case sensitive server is not real pain - it is question about discipline. I don't criticize your code (conversely, I add your script - https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/tempdb_Information.sql, to compare it with Glenn Berry alternatives - https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/SQL%20Server%202016%20Diagnostic%20Information%20Queries.sql), I just want to point out the fact that the best practices for using the name of the system database is lowercase.

  • Eric M Russell

    SSC Guru

    Points: 125020

    Based on feedback, I have submitted an updated version that uses tempdb (lowercase) to compensate for servers with case sensitivity enabled. Also I have extended it to include additional columns for containing the stored procedure name and SQL statement (if applicable) that allocated the object.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • kast218

    SSCertifiable

    Points: 6286

    Hi, Eric. Great thanks for updated script - really good work.

    But for CS instance still exist error: Msg 207, Level 16, State 1, Line 56

    Invalid column name 'spid'.

    Please, change on line 56 ON er.session_id = trace.spid on ON er.session_id = trace.SPID

  • Velveeta22

    Ten Centuries

    Points: 1289

    Your script is a military grade flashlight in what was a black box for me!  Added to favorites, thank you.

  • Velveeta22

    Ten Centuries

    Points: 1289

    For my personal version of this I have removed the first 3 columns as they were taking up visual space for information I did not need.  I have added the index usage statistics on the TempDB indexes if you want to merge into your copy:


    -- Query details about objects allocated in TEMPDB. This must be run in context of SET

    SET LOCK_TIMEOUT 10000;
    SET DEADLOCK_PRIORITY LOW;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    USE tempdb;

    SELECT * FROM
    (
    SELECT DISTINCT
    o.name AS ObjectName
    , si.name AS IndexName
    , CASE si.index_id
     WHEN 0 THEN 'HEAP'
     WHEN 1 THEN 'CLUSTERED'
     ELSE 'NONCLUSTERED'
     END AS IndexType
    , ius.user_seeks
    , ius.user_scans
    , ius.user_lookups
    , ius.user_seeks + ius.user_scans + ius.user_lookups AS user_reads
    , ius.user_updates AS user_writes
    , ius.last_user_seek
    , ius.last_user_scan
    , ius.last_user_update
    , ps.row_count AS RowsCount
    , ((ps.reserved_page_count * 8024) / 1024 / 1024) AS ReservedMB
    , trace.SPID
    , er.start_time AS RequestStartTime
    , trace.ApplicationName
    , OBJECT_NAME( qt.objectid, qt.dbid ) AS ProcedureName
    , SUBSTRING(CHAR(13) + SUBSTRING (qt.text,(er.statement_start_offset/2) + 1
     ,((CASE WHEN er.statement_end_offset = -1
     THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
     ELSE er.statement_end_offset
     END - er.statement_start_offset)/2) + 1)
     ,1,8000) AS StatementText
    , trace.HostName
    , trace.LoginName
    , o.create_date AS ObjectCreated
    FROM sys.dm_db_partition_stats ps
    JOIN sys.tables AS o ON o.object_id = ps.OBJECT_ID AND o.is_ms_shipped = 0
    LEFT JOIN sys.indexes si ON si.object_id = o.object_id AND si.index_id = ps.index_id
    LEFT JOIN sys.dm_db_index_usage_stats ius ON ius.OBJECT_ID = ps.OBJECT_ID AND ius.index_id = ps.index_id
    LEFT JOIN
    (
    SELECT HostName, LoginName, SPID, ApplicationName, DatabaseName, ObjectID
     , ROW_NUMBER() OVER (PARTITION BY ObjectID ORDER BY StartTime DESC) MostRecentObjectReference
    FROM fn_trace_gettable(
     (
     SELECT LEFT(path, LEN(path)-CHARINDEX('\', REVERSE(path))) + '\Log.trc'
     FROM  sys.traces
     WHERE is_default = 1
     ), DEFAULT)
    WHERE ObjectID IS NOT NULL
    ) trace
     ON trace.ObjectID = ps.object_id
     AND trace.DatabaseName = 'tempdb'
     AND MostRecentObjectReference = 1
    LEFT JOIN sys.dm_exec_requests AS er
        ON er.session_id = trace.spid
    OUTER APPLY sys.dm_exec_sql_text( er.sql_handle) AS qt
    ) AS T
    WHERE ReservedMB > 0
    ORDER BY 4;

Viewing 10 posts - 1 through 10 (of 10 total)

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