SQL Server Can a page be anything other than allocated or deallocated?

8e2ybdfx 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(90)

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?

sql-server

来源：https://stackoverflow.com/questions/77167715/can-a-page-be-anything-other-than-allocated-or-deallocated

1条答案

按热度按时间

izkcnapc1#

The data is coming from here

SELECT TOP(@i)
    tsu.session_id,
    tsu.request_id,
    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

sys.dm_db_task_space_usage

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.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Can a page be anything other than allocated or deallocated?

1条答案

相关问题

热门标签

最新问答