none
TempDB question - possible issue RRS feed

  • Question

  • Hi all.

    I've just had a nose around my production tempdb database and noticed something odd (in my view anyway)

    Using queries on sys.dm_db_session_space_usage and sys.dm_db_task_space_usage, I have found that there are two sessions that were created on the 10th of April that are still being used (as per Last Batch column) but do not release themselves (ie, session gets killed) after the batch is completed. It seems it is also growing with each execution, as per the highlighted allocation columns in the attachment.

    With this comes two questions:

    1. Should I be concerned about these sessions not being killed after each execution...?
    2. What is the difference between allocated and deallocated mb? These columns reference the internal_objects_alloc_page_count and internal_objects_dealloc_page_count in sys.dm_db_session_space_usage DMV

    Any questions, please let me know.

    Thanks in advance.

    Thursday, April 18, 2019 9:43 AM

All replies

  • Hi warnerrj79,

     

    >>Should I be concerned about these sessions not being killed after each execution...?

     

    Sometimes the temp table will not clean up after the session is closed. It is typically good practice to clean up these objects.

    If the session is in sleeping state you can kill the session so that the space can be reused or shrunk.

     

    >>What is the difference between allocated and deallocated mb? These columns reference the internal_objects_alloc_page_count and internal_objects_dealloc_page_count in sys.dm_db_session_space_usage DMV

     

    Internal Objects are the objects created to perform some operation internally for a particular session. 

    allocatedInternal_mb: The space reserved or allocated for internal objects by this session.

    deallocatedInternalmb: The space that is freed by this session and no longer reserved for internal objects.

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, April 19, 2019 2:49 AM
  • >>Should I be concerned about these sessions not being killed after each execution...? I stopped drop  temp tables

    IIRW since  SQL Server 2005 SQL Server use caching temporary tables.

    The main goal of temp table cache is to reduce the costs associated with 
    temp table creation. The second temp table creation is much faster. Instead 
    of dropping and creating the table we simply truncate it. All indexes and 
    statistics are truncated and identity column values are reset. 

    1. The following query will tell how many temp tables you have:

    select count(*) from tempdb.sys.objects where type='U' and name like '#%'



    2. The following query will tell you how many temp tables are cached:

    select count(*) from sys.dm_os_memory_cache_entries where 
    type='CACHESTORE_TEMPTABLES'



    3. The following query will tell you how many tables are scheduled for 
    deferred drop:

    select cntr_value from sys.dm_os_performance_counters where 
    counter_name='Temp Tables For Destruction'



    Normally the number of cached tables (2) + the number of "tables for 
    deferred drop" (3) should be approximately equal to the number of all temp 
    tables on the server (1). I'm saying approximately because temp tables which 
    are not cached are not reflected in the second or third numbers, so it is 
    normal if (1) is a little bigger. If you take your measurements when there 
    is no (or very little) activity on the server then you should get very good 
    results. Without activity (1) should be equal to (2) and (3) should be 0. 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, April 22, 2019 5:21 AM
    Answerer