How to find a temporary table in tempdb?

  • Yup, that's pretty much what I was thinking about.

    Just one comment, if the session_id>50 is supposed to filter out system processes, remove it as it won't necessarily do what you intend. Rather use is_system_process = 0 (column from sys.dm_exec_sessions)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (4/16/2011)


    Yup, that's pretty much what I was thinking about.

    Just one comment, if the session_id>50 is supposed to filter out system processes, remove it as it won't necessarily do what you intend. Rather use is_system_process = 0 (column from sys.dm_exec_sessions)

    Thank you, duly noted.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • GilaMonster (4/16/2011)


    Yup, that's pretty much what I was thinking about.

    Just one comment, if the session_id>50 is supposed to filter out system processes, remove it as it won't necessarily do what you intend. Rather use is_system_process = 0 (column from sys.dm_exec_sessions)

    I know this is 2k8 forum, but for 2k5I had to use where S.is_user_process = 1

  • Ninja's_RGR'us (4/18/2011)


    GilaMonster (4/16/2011)


    Yup, that's pretty much what I was thinking about.

    Just one comment, if the session_id>50 is supposed to filter out system processes, remove it as it won't necessarily do what you intend. Rather use is_system_process = 0 (column from sys.dm_exec_sessions)

    I know this is 2k8 forum, but for 2k5I had to use where S.is_user_process = 1

    Thanks both, here is the query again with this correction:

    SELECT

    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]

    , S.[program_name]

    , S.login_name

    , S.[status]

    , S.cpu_time

    , S.memory_usage

    , S.total_scheduled_time

    , S.total_elapsed_time

    , S.last_request_start_time

    , S.last_request_end_time

    , S.reads

    , S.writes

    , S.logical_reads

    FROM

    sys.dm_db_task_space_usage AS R1

    INNER JOIN

    sys.dm_exec_sessions AS S

    ON

    R1.session_id = S.session_id

    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

    S.is_user_process = 1

    AND

    (

    R1.user_objects_alloc_page_count > 0

    OR R1.user_objects_dealloc_page_count > 0

    OR R1.internal_objects_alloc_page_count > 0

    OR R1.internal_objects_dealloc_page_count > 0

    OR R3.[text] IS NOT NULL

    );

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Ninja's_RGR'us (4/18/2011)


    GilaMonster (4/16/2011)


    Yup, that's pretty much what I was thinking about.

    Just one comment, if the session_id>50 is supposed to filter out system processes, remove it as it won't necessarily do what you intend. Rather use is_system_process = 0 (column from sys.dm_exec_sessions)

    I know this is 2k8 forum, but for 2k5I had to use where S.is_user_process = 1

    sorry, got it the wrong way around. Posting without testing again. Bad!

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (4/18/2011)


    Ninja's_RGR'us (4/18/2011)


    GilaMonster (4/16/2011)


    Yup, that's pretty much what I was thinking about.

    Just one comment, if the session_id>50 is supposed to filter out system processes, remove it as it won't necessarily do what you intend. Rather use is_system_process = 0 (column from sys.dm_exec_sessions)

    I know this is 2k8 forum, but for 2k5I had to use where S.is_user_process = 1

    sorry, got it the wrong way around. Posting without testing again. Bad!

    I'll let it slide, THIS TIME. :hehe::hehe::hehe::-P

  • Hi all, I even did not expect so many replyes over the weekend.

    Dan: Yes, I am looking at space available, and that's easy query, I use sys.database_files along with FileProperty function.

    Jack: Thanks a lot for good links, I will read them after I update with my answers.

    GilaMonster: I agree this is not perfromance but a space issue. But anyway I have to resolve it , no matter how to label it. Yes, all files are pre-sized. We have 8 files * 20000 MB each = ~150 GB. For most of the cases it is good enough, our average utilization is about 12%. However sometimes they run very huge and complex reports (this is data warehouse) with apparently large temp tables. As I mentioned in my initial post, I also have to select top 5 largest tables there. But the only problem in my case is that I cannot see local temp tables, that's because of my restricted privileges.

    Mario: Thanks for the query, I will use it too.

  • SQL Guy 1 (4/18/2011)


    I also have to select top 5 largest tables there. But the only problem in my case is that I cannot see local temp tables, that's because of my restricted privileges.

    The thing is, just finding the top 5 largest tables is useless, unless you just want to say 'there's a large table in TempDB'. You can't tie the table names back to the session.

    Hence the use of Marios' query, not to find the largest table, but to find the session (and the query) that is using the most space. Once you have that, you can do something practical, like try to fix it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • They might be better off if they used permanent tables instead of temporary tables.

Viewing 9 posts - 16 through 23 (of 23 total)

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