none
Tempdb Deallocation (dm_db_session_space_usage)

    Question

  • Hi,

    I'm having a little trouble understanding how the sys.dm_db_session_space_usage view works, more specifically how and when the user_objects_dealloc_page_count column is updated.  To help demonstrate my question, I've included two simple demos.

    Demo 1:

    1. create table #temp(id int identity, name varchar(100) default 'DontPageMeBro')

    2. insert into #temp default values;

    go 10000

    3. select * from sys.dm_db_session_space_usage where session_id = @@SPID

    - note: at this point it's pretty clear the view is tracking the pages allocated in tempdb for the #temp table (40 pages)

    4. drop table #temp

    5. select * from sys.dm_db_session_space_usage where session_id = @@SPID

    - note: at this point it's also pretty clear the view is tracking the pages deallocated (40 pages)

    DEMO2:

    ***reset session***

    1. create table #temp(id int identity, name varchar(100) default 'DontPageMeBro')

    2. insert into #temp default values;

    go 1000000

    3. select * from sys.dm_db_session_space_usage where session_id = @@SPID

    - note: should show 3896 pages allocated

    4. drop table #temp

    5. select * from sys.dm_db_session_space_usage where session_id = @@SPID

    - note: the pages do not get deallocated after the table is dropped

    So I am familiar with sql server's delayed deallocation (separate logical & physical process); however querying the tempdb.sys.allocation_units table using the allocation_unit_id of the temp table, returns 0 results after the drop.  And as far as I can tell, none of the system views show any sign of the table still existing, except for sys.dm_db_session_space_usage.  

    The result is that the deallocation count never gets updated, making it difficult to tell how much tempdb space a session is actually consuming.  I've actually tracked sessions with page allocation count in excess of 400GB, when my tempdb is only 300GB, so it does get a little confusing.

    If anyone has experienced the same issue or has any input, I'd greatly appreciate it. Thanks in advance!

    Friday, February 03, 2012 9:42 PM

Answers

  • Hi DontPageMeBro,

    I have reproduced two scenarios on your original post. In addition, for the second scenario, I noticed that there is an additional process which deallocated the pages. Please see the scream shots:

    1. Create the table


    2. Drop the table


    Meanwhile, I find the comment on this behavior on BOL:

    There is now deferred drop in tempdb. This means, for example, that when a large temporary table is dropped by an application, it is handled by a background task and the application does not have to wait. This results in faster response time to applications.

    This is an enhacement begin from SQL Server 2005. It can be a normal behavior if the SQL Server version is later than SQL Server 2000. For more information: Working with tempdb in SQL Server 2005.

    As for your requirement, taking use of these DMVs might be not precise to addressing the space usage problem. You may condiser restricking the duration of application connections which are inactive.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Stephanie Lv

    TechNet Community Support


    • Edited by Stephanie Lv Monday, February 06, 2012 3:26 AM
    • Marked as answer by Stephanie Lv Tuesday, February 14, 2012 9:43 AM
    Monday, February 06, 2012 3:23 AM

All replies

  • Hi, 

    I may NOT address all of your issues but will add some pointers for you.

    >> I've actually tracked sessions with page allocation count in excess of 400GB, when my tempdb is only 300GB<<

    Note that the DMV sys.dm_db_session_space_usage shows historical page allocations/deallocations of the completed batches for the active sessions.

    >>making it difficult to tell how much tempdb space a session is actually consuming. <<

    To measure the currently executing tasks, should you be using dm_db_task_space_usage along with sys.dm_exec_requests ?

    Note that there are several optimizations with regards to tempdb usage and one such example is even if you do a explicit drop temp table inside a procedure, that table is NOT really dropped and still shows in some DMV's. There is some enhanced caching mechanism to avoid latch contention. And the same is NOT true if you run the exact stmt as a adhoc sql. This may be totally irrelevant to this discussion but wanted to present it to give you an idea of those internal optimizations.

    I know you highlighted one problem above, but can you elaborate on the tempdb issue you are facing?

    And FYI.

    If I run #4 & #5 as one batch, I don't see the deallocations but if I run them as 2 different batches, I can see all of the pages allocated being deallocated correctly.



    Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/
    Saturday, February 04, 2012 3:15 AM
    Moderator
  • Thanks for the response Sankar.  Here's the scenario that's at the root of my problem:

    Scenario: I have a fairly large number of users querying the system and creating temporary tables, but they don't always remember to explicitly drop the tables and/or disconnect their session.  So even though they may be finished with a temp table, their session is still active and reserving that space, which on occasionly leads to tempdb space problems.

    What I'm trying to do is detect those sessions (active or inactive) that are reserving large chunks of tempdb, but it seem like I'm not getting accurate numbers from the dm_db_session_space_usage view.

    My Hunch: Based on the scenarios I included above, it seems like the view isn't properly tracking the deallocation of pages for tables over a certain size.

    I am suprised that you were able to see the deallocation at all in scenario 2, because I've tried batching and executing them separately, but still no luck.  Either way, I would expect the view to return the same result regardless of how or when it's queried.

    I should also mention that I do also monitor dm_db_task_space_usage along with dm_exec_requests, but that's a little different scenario.

    And just in case you were wondering about version:

    Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (X64)   Feb  4 2011 11:27:06   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) 

    Thanks again for you response, hopefully I can get this sorted out ;)


    Saturday, February 04, 2012 2:30 PM
  • Hi,

    >>am suprised that you were able to see the deallocation at all in scenario 2, because I've tried batching and executing them separately, but still no luck. <<

    I didn't mean to but I lied to you unintentionally and apologize for that. When I ran your 2nd test on a laptop, I ran it with a smaller sample than you mentioned and it showed the deallocations correctly. It seems there is a cut off size to trigger this behavior. I have reproduced your issue now and asking folks within MSFT about it and let's if they have any ideas/thoughts about this.


    Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/
    Monday, February 06, 2012 2:37 AM
    Moderator
  • Hi DontPageMeBro,

    I have reproduced two scenarios on your original post. In addition, for the second scenario, I noticed that there is an additional process which deallocated the pages. Please see the scream shots:

    1. Create the table


    2. Drop the table


    Meanwhile, I find the comment on this behavior on BOL:

    There is now deferred drop in tempdb. This means, for example, that when a large temporary table is dropped by an application, it is handled by a background task and the application does not have to wait. This results in faster response time to applications.

    This is an enhacement begin from SQL Server 2005. It can be a normal behavior if the SQL Server version is later than SQL Server 2000. For more information: Working with tempdb in SQL Server 2005.

    As for your requirement, taking use of these DMVs might be not precise to addressing the space usage problem. You may condiser restricking the duration of application connections which are inactive.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Stephanie Lv

    TechNet Community Support


    • Edited by Stephanie Lv Monday, February 06, 2012 3:26 AM
    • Marked as answer by Stephanie Lv Tuesday, February 14, 2012 9:43 AM
    Monday, February 06, 2012 3:23 AM
  • Thanks for the great info Stephanie.

    The background process would definitely explain it, so I think this one is answered.  I think the only thing that's still troubling me, is that I haven't been able to capture the background process doing the deallocation.  Do you know when or how that process is triggered?  Can it be triggered manually?

    I'm assuming a delete statement would do the job, but I just have been able to duplicate behavior shown in your screenshots.

    Wednesday, February 15, 2012 9:50 PM
  • DontPageMeBro,

    To capture the background process, you may have a try to get the process with user_objects_dealloc_page_count equals to user_objects_alloc_page_count of the current process from table sys.dm_db_session_space_usage, as soon as your dropped the table.

    As for the DELETE statement, it will not trigger the background task, please see the sreenshot after deleting data from the temp table.
     


    Stephanie Lv

    TechNet Community Support

    Thursday, February 16, 2012 1:09 AM
  • I beleive this behavior is a bug. I did look into it in a little more detail here: Deferred Deallocations of Pages in TempDb. I also opened a Connect item here: https://connect.microsoft.com/SQLServer/feedback/details/725621/sys-dm-db-session-space-usage-inaccurate-after-deferred-drop-of-allocation-unit.

    If you agree with me that this is a bug, please go to connect and vote for this item.

    Sunday, February 19, 2012 4:11 PM