Hello Geeks and welcome to the Day 9 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
We have started with sys.dm_db_file_space_usage yesterday which will help you start with troubleshooting TempDB contention and usage issues. Today I will take up the next DMV in the same category, sys.dm_db_session_space_usage. Similar to the first DMV this DMV also provides usage information inside TempDB but gives more in detail information specific to each session.
The session level information in sys.dm_db_session_space_usage will give you details about the allocation and deallocation of pages with respect to user objects and internal objects. Before we go any further let me explain what are user objects and internal objects. User objects are the # tables that the user sessions create and the space allocated is counted under user object allocations and deallocations. Internal Objects are the objects created to perform some operation internally for a particular session. This space is allocated by the SQL Server itself and cannot be seen explicitly what these objects are unless you run STATISTICS IO ON in your sessions. They generally get created when you use big hash tables or spooling in worktables.
sys.dm_db_session_space_usage aggregates this information at session level for each existing session. In most of the troubleshooting cases I have observed that the TempDB space is cached after deallocations if the session does not end. You will see very high number for allocation and deallocation and when you try to release the space by shrinking it does not free up the space. In those cases you need to check if the session is still active and if it is in sleeping state you can kill the session so that the space can be reused or shrunk.
We will get the information from sys.dm_db_session_space_usage by running below query which will give you the current active space used and the space that was totally used by the session.
SELECT session_id, user_objects_alloc_page_count/128 AS user_objs_total_sizeMB, (user_objects_alloc_page_count - user_objects_dealloc_page_count)/128.0 AS user_objs_active_sizeMB, internal_objects_alloc_page_count/128 AS internal_objs_total_sizeMB, (internal_objects_alloc_page_count - internal_objects_dealloc_page_count)/128.0 AS internal_objs_active_sizeMB FROM sys.dm_db_session_space_usage ORDER BY user_objects_alloc_page_count DESC
We observe that around 2302 MB is allocated for 232 session for internal objects and only 0.3 MB is active. As I explained above we will check if the session is in sleeping state.
SP_WHO2 232
BINGO!!!. Kill it and you can reuse that space or shrink the file easily now. Some times you will see the active space is very less but the total space held up would be in GBs. This DMV helps you figure that out while troubleshooting.
Tomorrow I will be covering one more in detailed DMV related to TempDB. So, stay tuned. Till then
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
