Tempdb – Memory Uptake – Real Life Samples – Day 01

Background

Want to start identifying queries that can cause your Tempdb to grow.

When possible, we will delineate as to which Tempdb component ( Internal or User ) is more impacted.

 

Lab

Stored Procedure

dbo.sp_IdentifyQueriesWithMultipleQueryPlans

Intro

There is a Stored Procedure, dbo.sp_IdentifyQueriesWithMultipleQueryPlans, that we developed to identify queries that have multiple Query Plan entries.

We spoke about it here.

Ran it

We ran it a few times and noticed it was eating up Tempdb.

Metering

Code


use master
go

if object_id('[dbo].[sp_SessionResourceUptake]') is null
begin

	exec ('create procedure [dbo].[sp_SessionResourceUptake] as ')
end
go

alter procedure [dbo].[sp_SessionResourceUptake]
as

begin

	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)
					)


			, [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)
					)
 

			, 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


end
go

 

Clean up

Free Procedure Cache

DBCC FREEPROCCACHE with no_infomsgs;

 

Drop CleanBuffers

DBCC DROPCLEANBUFFERS with no_infomsgs;

 

 

Free System Cache

DBCC FREESYSTEMCACHE ('ALL') 
		WITH MARK_IN_USE_FOR_REMOVAL, no_infomsgs
		;  

 

Measuring

Each time we ran the  dbo.sp_IdentifyQueriesWithMultipleQueryPlans procedure, using [dbo].[sp_SessionResourceUptake] we measured the session’s memory uptake.

And, here is what we noticed.

 

Stage Session Internal Object Allocated Session Internal Object De-Allocated Session User Object Allocated Session User Object De-Allocated
After clearing cache
 Run – 01  93.75  93.75  21.31  2.25
 Run – 02  187.63  187.56  42.38  4.50
 Run – 03  281.38  281.31  63.44  6.75
 Run- 04  375.19  375.19  84.50  9.0
 Run – 05  468.94  468.88  105.56  11.25

 

 

Source Control

GitHub

DanielAdeniji/SQLServerIdentifyQueriesWithMultipleQueryPlans

Link

 

Connect Items

Opened a connect item this morning.

  1. Tempdb acquires memory yet GBs of unallocated memory is left unused
    Bug ID :- 3119422
    Date Opened :- 2017-Jan-23rd
    Link
  2. Steve Hood – TempDB holds excessive unallocated pages in memory
    Bug ID :- 2215297
    Date Opened :- 2016-Jan-7th
    Status :- Closed ( as not Reproducible )
    Link

 

Summary

As for internal objects they are being acquired and released.

On the other hand, user objects are being acquired at a quarter of the rate of Internal Objects.

But, unfortunately very little of the user objects are consequently released.

 

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s