Can a page be anything other than allocated or deallocated?

The documentation for sp_WhoIsActive states

The most confusing of these columns are those related to tempdb. Each of the columns reports a number of 8 KB pages. The [tempdb_allocations] column is collected directly from the tempdb-related DMVs, and indicates how many pages were allocated in tempdb due to temporary tables, LOB types, spools, or other consumers. The [tempdb_current] column is computed by subtracting the deallocated pages information reported by the tempdb DMVs from the number of allocations. Seeing a high number of allocations with a small amount of current pages means that your query may be slamming tempdb, but is not causing it to grow. Seeing a large number of current pages means that your query may be responsible for all of those auto-grows you keep noticing.

tempdb_current is the column that has me confused. My plain English understanding is that a page can only be allocated or deallocated, meaning that subtracting one number from the other should not produce anything helpful. Clearly, I am wrong. What can a page be, if neither allocated nor deallocated?


  • The data is coming from here

    SELECT TOP(@i)
        tsu.user_objects_alloc_page_count +
            tsu.internal_objects_alloc_page_count AS tempdb_allocations,
        tsu.user_objects_alloc_page_count +
            tsu.internal_objects_alloc_page_count -
            tsu.user_objects_dealloc_page_count -
            tsu.internal_objects_dealloc_page_count AS tempdb_current
    FROM sys.dm_db_task_space_usage AS tsu

    enter image description here


    This is for the session's current query. The idea is that if the query has allocated 1000 pages and deallocated 800 pages then there are currently 200 pages still allocated.