Hello Geeks and welcome to the Day 8 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.
With yesterday’s blog we have covered he IO related DMVs and today I am going to begin with the DMVs which will help you deal with the TempDB issues. There are three important DMVs which are specific to TempDB and are introduced only to help you troubleshoot and answer few questions when you need to restart as the final resort due to TempDB issues. 🙂 I will start with the most high level detail DMV, sys.dm_db_file_space_usage.
As the name suggests, sys.dm_db_file_space_usage will give you the information on how the space is being used inside each file in the TempDB. This is a point-in-time data which provides the current usage. From SQL Server 2012 Microsoft has introduced three new columns to give even more detailed information from the DMV.
The syntax again is straight forward and the most important columns to notice would be
filegroup_id (2012+) – Filegroup id to which the file belongs
total_page_count (2012+) – Total number of pages in the file (Allocated + Unallocated)
allocated_extent_page_count (2012+) – Allocated page count in allocated extents
unallocated_extent_page_count – count of unallocated pages only in unallocated extents
mixed_extent_page_count – Total number of pages in mixed extents
version_store_reserved_page_count – pages reserved (used an unused count in allocated extents) for version store
user_object_reserved_page_count – pages reserved for version store
internal_object_reserved_page_count – pages reserved for version store
Running sys.dm_db_file_space_usage would return the results like this. Use the first query if you are on SQL Server 2012 or above and the second query if you are on 2008 R2 and lower versions.
--SQL Server 2012 + SELECT file_id, filegroup_id, total_page_count, allocated_extent_page_count, unallocated_extent_page_count, mixed_extent_page_count, version_store_reserved_page_count, user_object_reserved_page_count, internal_object_reserved_page_count FROM sys.dm_db_file_space_usage --SQL Server 2008 R2 and lower versions till 2005 SELECT file_id, unallocated_extent_page_count, mixed_extent_page_count, version_store_reserved_page_count, user_object_reserved_page_count, internal_object_reserved_page_count FROM sys.dm_db_file_space_usage
From the above result you would know what kind of objects are occupying your tempdb space and act accordingly on the respective problem to fix any contention or space issues on your TempDB. You can say that internal objects are taking up more space and further troubleshoot. In combination with sys.dm_db_file_space_usage there are two other DMVs which will help you go into more detail about who is using up that space in TempDB. Tomorrow I will be covering one of those DMVs. 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
