TempDB – Allocation & Deallocation Tracking – Internal & User Objects

Background

After quite  a long lapse, took this entire weekend to dig more into tempdb hyper growth.

We noticed that tempdb was using quite a bit of memory on a couple of boxes.

 

Earlier Work

  1. SQL Server – Query Plans with DesiredMemory
    Published :- 2016-12-02
    Link
  2. SQL Server – Memory Allocated/Unallocated per Database
    Published :- 2016-12-05
    Link
  3. SQL Server – Tempdb – v2012
    Published :- 2016-12-05
    Link

 

TroubleShooting

Instrumentation

Glossary

Here are the Dynamic Management Views (DMVs) that we will employ:

DMVInfo Information Link
 sys.dm_db_file_space_usage Returns space usage information for each file in the database.  Link
sys.dm_os_buffer_descriptions  Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.  Link
sys.dm_db_session_space_usage Returns the number of pages allocated and deallocated by each session for the database.  Link
 sys.dm_db_task_space_usage  Returns page allocation and deallocation activity by task for the database.  Link

 

 

 

tempdb.sys.dm_db_file_space_usage

Code


set nocount on
go

use [tempdb]
go

select
 
          [runtime]
			= convert( varchar(30), getdate(), 100) 
 
         , [totalspaceMB]
			= ( SUM (total_page_count)*8 / 1024 ) 

         , [unallocatedSpaceMB]
			= ( SUM (unallocated_extent_page_count)*8 / 1024 ) 

        , [versionStoreMB]
			= ( SUM (version_store_reserved_page_count)*8  / 1024 )
 
        , [userObjectMB]
			= ( SUM (user_object_reserved_page_count)*8 / 1024 ) 
 
        , [internalObjectMB]
			= ( SUM (internal_object_reserved_page_count)*8 / 1024 )

        , [mixedextentMB]
			= ( SUM (mixed_extent_page_count)*8 / 1024 ) 
 
FROM [tempdb].[sys].[dm_db_file_space_usage]

 

Output

QA – 2017.01.22 9:14 PM

sys__dm_db_file_space_usage__20170122_0915pm

 

sys.dm_os_buffer_descriptions

Code


; with cte
( 
    [count]
)
as
(
  
    select [count] = count(*)
    FROM   sys.dm_os_buffer_descriptors
  
)
  
SELECT
        [Database]
            = case
                when (database_id = 32767) then 'ResourceDB'
                else DB_NAME(database_id)
              end  
  
        , [CachedSizeMB]
  
            = 
                cast
                    (
                        (
                            COUNT(*) * 8
                        ) 
                        / 
                        ( 
                            1024.0 * 1
                        )
                        as decimal (18, 2)
                    )
  
        , [CachedSizeGB]
            = 
                cast
                    (
                        (
                            COUNT(*) * 8
                        ) 
                        / 
                        ( 
                            1024.0 * 1000
                        )
                        as decimal (18, 2)
                    )
  
  
    
        , [UnusedSizeMB]
  
            = SUM 
				(
					CAST ([free_space_in_bytes] AS BIGINT)
				) 
				/ (1024 * 1024) 

        , [%]
            = cast
              (
                    (count(*) * 100.00)
                        / cte.[count] 
                    as decimal(10, 2)
              )
  
FROM sys.dm_os_buffer_descriptors
  
cross apply cte
  
GROUP BY
             case
                when (database_id = 32767) then 'ResourceDB'
                else DB_NAME(database_id)
             end   
 
            , [cte].[count]
  
ORDER BY
        count(*) desc
           
OPTION (MAXDOP 1, RECOMPILE)

Output

QA – 2017.01.22 9:56 PM

sys_dm_os_buffer_descriptors__20170122_0956pm

 

 

sys.dm_db_session_space_usage & sys.dm_db_task_space_usage

Code


use [tempdb]
go

select 

		  tblSP.[spid]

		, tblSP.[loginame]

		, tblSP.[hostname]

		, tblSP.[program_name]

		--, tblSS.memory_usage

		--, tblSS.[status]

		, tblSS.open_transaction_count

		, [sessionInternalObjectsAllocationInMB]
			= cast
				(
					(tblSBSSU.[internal_objects_alloc_page_count] *1.0 ) / ( 128)
					as decimal(30, 2)
				)

		, [sessionInternalObjectsDeAllocationInMB]
			= cast
				(
					(tblSBSSU.[internal_objects_dealloc_page_count] *1.0 ) / ( 128)
					as decimal(30, 2)
				)

/*
		, [sessionInternalObjectsAllocationInGB]
			= cast
				(
					(tblSBSSU.[internal_objects_alloc_page_count] *1.0 ) / ( 128 * 1000)
					as decimal(30, 2)
				)
*/

		, [sessionUserObjectsAllocationInMB]
			= cast
				(
					 (tblSBSSU.user_objects_alloc_page_count *1.0 ) / ( 128)
					as decimal(30, 2)
				)


		, [sessionUserObjectsDeAllocationInMB]
			= cast
				(
					 (tblSBSSU.user_objects_dealloc_page_count *1.0 ) / ( 128)
					as decimal(30, 2)
				)


		, [taskInternalObjectsAllocationInMB]
			= cast
				(
					([tblSSBTSU].[internal_objects_alloc_page_count] *1.0 ) / ( 128)
					as decimal(30, 2)
				)

		, [taskInternalObjectsAllocationInGB]
			= cast
				(
					([tblSSBTSU].[internal_objects_alloc_page_count] *1.0 ) / ( 128 * 1000)
					as decimal(30, 2)
				)

/*
		, [taskUserObjectsAllocationInMB]
			= cast
				(
					 ([tblSSBTSU].user_objects_alloc_page_count *1.0 ) / ( 128)
					as decimal(30, 2)
				)


		, [taskUserObjectsAllocationInGB]
			= cast
				(
					([tblSSBTSU].[user_objects_alloc_page_count] *1.0 ) / ( 128 * 1000)
						as decimal(30, 2)
				)

*/
		, tblSP.[cmd]

from   [tempdb].[sys].[dm_db_session_space_usage] tblSBSSU

inner join [tempdb].[sys].[dm_db_task_space_usage] tblSSBTSU

		on tblSBSSU.[session_id] = tblSSBTSU.[session_id]

inner join [sys].[sysprocesses] tblSP

		on tblSBSSU.[session_id] = tblSP.[spid]

inner join sys.dm_exec_sessions tblSS

		on tblSBSSU.[session_id] = tblSS.[session_id]

where

	(

		(
			tblSBSSU.internal_objects_alloc_page_count
			+ tblSBSSU.user_objects_alloc_page_count
		) > 0


	)

order by

		(
			tblSBSSU.internal_objects_alloc_page_count
			+ tblSBSSU.user_objects_alloc_page_count
		) desc



 

Output

QA – 2017.01.22 9:14 PM

sys__dm_db_session_space_usage__20170122_0933am

Explanation
  1. We see that sessions 61 & 63 are the highest users of tempdb
    • In this case they are using internal Objects as compared to user Objects
    • We also see that just about all the memory allocated are subsequent deallocated

 

Summary

In a nutshell, both tempdb.sys.dm_db_file_space_usage ( durable file allocation ) and  sys.dm_os_buffer_descriptors ( online memory ) have high values for tempdb uptake.

But,  sys.dm_db_session_space_usage & sys.dm_db_task_space_usage ( currently running session indicators ) agree with what we see in sys.dm_db_file_space_usage DMV that the storage is indeed unallocated.

It is just that SQL Server is reluctant to free up that space from memory and give is back to the system.

Admittedly, we got lucky here; lucky in the sense that the sessions that are using tempdb are still connected to the SQL Instance; otherwise, we will mistake them for system or phantom processes.

 

References

  1. Microsoft Developer
    • Arvind Shyamsundar
      • Tracking TEMPDB internal object space usage in SQL 2012
        Link
  2.  SqlSkills
    • Paul Randall
      • Performance issues from wasted buffer pool memory
        Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s