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.
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.
We ran it a few times and noticed it was eating up Tempdb.
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
Free Procedure Cache
DBCC FREEPROCCACHE with no_infomsgs;
DBCC DROPCLEANBUFFERS with no_infomsgs;
Free System Cache
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL, no_infomsgs ;
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.
Opened a connect item this morning.
- Tempdb acquires memory yet GBs of unallocated memory is left unused
Bug ID :- 3119422
Date Opened :- 2017-Jan-23rd
- Steve Hood – TempDB holds excessive unallocated pages in memory
Bug ID :- 2215297
Date Opened :- 2016-Jan-7th
Status :- Closed ( as not Reproducible )
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.