SQL Server – Tempdb – v2012

Background

In this post we will narrow further into TempDB.  And, review allocated objects.

 

Objective

Our digging will lead us to consider

  1. Objects that are currently allocated ( Memory Allocation per Object )
  2. Allocated & Deallocated Page Count per Session
  3. Currently executing queries that are using tempdb among the other databases they are using

 

 

Glossary

Here is a quick outline of terms that we will cover.

DMV Description Note
 sys.dm_os_buffer_descriptors 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.
sys.allocation_units Contains a row for each allocation unit in the database When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server. sys.dm_os_buffer_descriptors returns cached pages for all user and system databases.
sys.dm_db_session_space_usage Returns the number of pages allocated and deallocated by each session for the database.
sys.dm_db_task_space_usage Returns page allocation and

 

 

 

Code

Outline

Here is the list of code snippets

  1. Memory Allocation per Database
    • List the memory in-use by each Database
  2. Memory allocated to each Table in Tempdb
    • For each object in the Tempdb, list the uptake of memory
  3. Allocated & Deallocated Page Count in Tempdb to each Session
    • Memory allocated in Tempdb to each Session
  4. List Currently Executing queries using tempdb
    • List detailed query resource usage for currently executing queries

 

Memory Allocation per Database

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)
                    )
 
 
        , [%]
            = 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
        2 desc
          
OPTION (MAXDOP 1, RECOMPILE)

Output

Output – Prod

sys_dm_os_buffer_descriptors_prod_20161201_0905pm

 

Output – QA

sys_dm_os_buffer_descriptors_qa_20161201_0907pm

 

 

 

Memory Allocation per Object

Code



use [tempdb]
go

/*
    -- Breaks down buffers used by current database by
    -- object (table, index) in the buffer cache
*/
SELECT
 
	   [objectID]
		= p.[object_id]

     , [object]
            = OBJECT_SCHEMA_NAME(p.[object_id])
                + '.'
                + OBJECT_NAME(p.[object_id]) 

	 , tblObject.[type]
	 	 
     , [index]
		= tblIndex.name

     --, p.index_id
     
	 , IndexType
        = max(tblIndex.type_desc)
     
	 , [BufferSizeMB]
		= COUNT_BIG(*)/128 
     
	 /*
		[BufferCount]
	 	= COUNT_BIG(*) 
     */
 
FROM sys.allocation_units AS a
 
INNER JOIN sys.dm_os_buffer_descriptors AS b
 
     ON a.allocation_unit_id = b.allocation_unit_id
 
INNER JOIN sys.partitions AS p
     ON a.container_id = p.hobt_id

INNER JOIN sys.objects as tblObject
     ON  p.object_id = tblObject.object_id
 
INNER JOIN sys.indexes as tblIndex
     ON  p.object_id = tblIndex.object_id
     AND p.index_id = tblIndex.index_id
 
WHERE b.database_id = DB_ID()
 
 -- Not System Object
AND   tblObject.[type] != 'S'
 
GROUP BY
           p.[object_id]
		 , tblObject.[type]
         , tblIndex.name
         , p.index_id
 
ORDER BY 
		COUNT_BIG(*) DESC

 

Output

Prod

tempdb_objects_prod_20161201_0927pm

 

Allocated & Deallocated Page Count per Session

Code

 


;WITH cteSU
(
	  [session_id]
	, [pagesUser] 
	, [pagesInternal] 
)
as
(

    SELECT 
          s.session_id

		, [pagesUser] 
			= sum(s.user_objects_alloc_page_count)

		, [pagesInternal] 
			= sum(s.internal_objects_alloc_page_count)
  
    
	FROM sys.dm_db_session_space_usage AS s
    
	GROUP BY s.session_id
    
	HAVING 
			SUM
			(
				s.user_objects_alloc_page_count 
					+ s.internal_objects_alloc_page_count
			) 
			> 0

)
SELECT 

		  cteSU.session_id

		, tblSP.loginame

		, [pagesUserInMB]
			= (
				cast
					(
						cteSU.[pagesUser]*1.0/128
							as float
					)
			   )

		, [pagesInternalInMB]
			= (
				cast
					(
						cteSU.[pagesInternal]*1.0/128
							as float
					)
			   )

		, [physicalIO]
			= tblSP.[physical_io]

		, tblSP.[cpu]
		
		, t.[text]
		
		, [statement] 
			= 
				COALESCE
				(
					NULLIF
					(
						SUBSTRING
						(
							   t.[text] 
							 , r.statement_start_offset / 2
							 , CASE 
									WHEN r.statement_end_offset < r.statement_start_offset 
											THEN 0 
									ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 
								END
						)
						, ''
					)
					, t.[text]
				)

		, r.[session_id]

		, tblSP.[dbid]
		
		, [db]
			= db_name(tblSP.[dbid])

		, tblSP.[hostname]

		, tblSP.[program_name]

	

FROM cteSU cteSU

LEFT OUTER JOIN sys.dm_exec_requests AS r

		ON cteSU.session_id = r.session_id

OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t

LEFT OUTER JOIN master.dbo.sysprocesses tblSP

		ON cteSU.session_id= tblSP.[spid]

ORDER BY 
		( 
			cteSU.[pagesUser] 
				+ cteSU.[pagesInternal] 
		)
			DESC

;

 

Output

unallocatedtempdb-objects-20161205-0528pm

 

Explanation

  1. We have one person that is heavily using TempDB
    1. He/she is at 775 MB

 

 

List Currently Executing queries using tempdb

Code






/*
	Kendra Little
	Who’s Using All that Space in tempdb, and What’s their Plan?
	August 27, 2009
	
Who’s Using All that Space in tempdb, and What’s their Plan?
*/ ;with cteTaskSpaceUsage ( session_id , request_id , task_alloc_pages , task_dealloc_pages ) as ( Select session_id , request_id , task_alloc_pages=sum(internal_objects_alloc_page_count + user_objects_alloc_page_count) , task_dealloc_pages = sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) from sys.dm_db_task_space_usage group by session_id , request_id ) --Modified from http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/12/tempdb-monitoring-and-troubleshooting-out-of-space.aspx select t1.session_id , t1.request_id , task_alloc_GB = cast((t1.task_alloc_pages * 8./1024./1024.) as numeric(10,1)) , task_dealloc_GB = cast((t1.task_dealloc_pages * 8./1024./1024.) as numeric(10,1)) , task_alloc_KB = cast((t1.task_alloc_pages * 8./1./1.) as numeric(10,1)) , task_dealloc_KB = cast((t1.task_dealloc_pages * 8./1./1.) as numeric(10,1)) , host = case when t1.session_id <= 50 then '[SYS]' else s1.host_name end , s1.login_name , s1.[status] , s1.last_request_start_time , s1.last_request_end_time , s1.row_count , s1.transaction_isolation_level , queryPlan = qp.query_plan , [queryText] = [sqlText].[text] , [statement] = COALESCE ( NULLIF ( SUBSTRING ( [sqlText].[text] , [t2].statement_start_offset / 2 , CASE WHEN [t2].statement_end_offset < [t2].statement_start_offset THEN 0 ELSE( [t2].statement_end_offset - [t2].statement_start_offset ) / 2 END ) , '' ) , [sqlText].[text] ) , query_plan =( SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle) ) from cteTaskSpaceUsage as t1 left join sys.dm_exec_requests as t2 on t1.session_id = t2.session_id and t1.request_id = t2.request_id left join sys.dm_exec_sessions as s1 on t1.session_id=s1.session_id OUTER APPLY sys.dm_exec_query_plan(t2.plan_handle) as qp OUTER APPLY sys.dm_exec_sql_text(t2.plan_handle) AS [sqlText] where t1.session_id <> @@SPID -- ignore this request itself order by t1.task_alloc_pages DESC option (recompile) GO

What is stored in Tempdb?

From  “sys.dm_db_session_space_usage (Transact-SQL)” ( Link ), here is what is stored in tempdb.

DMV Description
 User Objects
User Defined Objects
System tables and indexes
Global temporary tables and indexes
Local temporary tables and indexes
Table variables
 Internal Objects
Work tables for cursor or spool operations and temporary large object (LOB) storage
Work files for operations such as a hash join
Sort Runs

 

 

Fess Up

When we first saw that Tempdb was using a lot of Memory we took to the Net and tried to catch up with what credible Sources, bloggers, have posted.

  1. Steve Hood
    • TempDB memory leak?
      Date Posted :- 2016-01-05
      Link
    • TempDB Excessive Memory Usage Example
      Date Posted :- 2016-02-01
      Link

 

MS Connect Items

Dedicated

Dedicating to Steve Hood for the detailed work he did.

And, his boldness in Opening a Connect Item to root that work in the Public Domain.

 

Summary

In a follow-up post, we will look more at what is in Tempdb, whichever of the aforementioned objects and processes are most prevalent in our environment, and how we can best measure them to determine “true” causation.

One thought on “SQL Server – Tempdb – v2012

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