SQL Server – What is in your TempDB?

Background

The last week or so, we have been digging into I/O and trying to measure IOPS requirements.

Came back into MS SQL Server and wanted to see how each database is laid out in terms of physical drive mappings.

But, again I am getting a bit ahead.

In this prerequisite post, let us see how MS SQL Server uses Tempdb and relate that to one of our SQL Instance.

 

Types of Objects

What types of objects are stored in Tempdb

 

Option Meaning SQL Server  Usage Habit
Internal Objects Objects internally created by SQL Server Applications cannot directly insert into or delete rows from internal objects.
Internal object metadata is stored in memory.
The metadata does not appear in system catalog views such as sys.all_objects. Internal objects are considered to be hidden objects.
Intermediate runs for Sorts

Intermediate results for Hash Joins

XML & LOB Variables

Temporary Objects for Spool Operators

Objects used in Keyset and Static cursors

Service Brokers

Instead of Triggers

Version Store Store row versions They do not appear in catalog views, such as sys.all_objects.
Inserts into the version store do not generate log records.
Snapshot isolation

Triggers

MARS (multiple active
result sets)

Online index build.

 

 

Improvements

v2005

From “Working with tempdb in SQL Server 2005” available here.

  1. Instant Data file Initialization
  2. Object Drop time efficiencies
    • There is now deferred drop in tempdb. This means, for example, that when a large temporary table is dropped by an application, it is handled by a background task and the application does not have to wait. This results in faster response time to applications.
    • Worktable caching is improved. When a query execution plan is cached, the work tables needed by the plan are not dropped across multiple executions of the plan but merely truncated.
  3. Object Creation and Drop Timing
    • SQL Server 2005 caches temporary objects. When table-valued functions, table variables, or local temporary tables are used in a stored procedure, function, or trigger, the frequent drop and create of these temporary objects can be time consuming. This can cause contentions on tempdb system catalog tables and allocation pages. In SQL Server 2005, these are cached. That means that dropping and creating temporary objects is very fast. When SQL Server drops a temporary object, it does not remove the catalog entry for the object
  4. Proportional Fill
    • Proportional fill has been optimized to reduce UP latch contention. Proportional fill means that, when there are multiple data files in tempdb, each file is filled in proportion to the free space that is available in the file so that all of the files fill up at about the same time. This was accomplished by removing a latch that was taken during proportional fill

 

Utilization

File Utilization

dbcc showfilestats


set nocount on;
go

use [tempdb]
go

declare @FileDetails TABLE
(     
	  FileId		int
	, FileGroupId	int
	, TotalExtents  int
	, UsedExtents	int
	, Name			nvarchar( 128 )
	, [FileName]	nvarchar( 500 )
	, TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) 
	, UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )
)   

insert into @FileDetails
(
	  FileId
	, FileGroupId
	, TotalExtents
	, UsedExtents
	, [Name]
	, [FileName]
)
exec ('use [tempdb]; dbcc showfilestats with no_infomsgs')

select 
		  FileId
		, FileGroupId
		, TotalExtents
		, UsedExtents
		, [Name]
		, [FileName]
		, [TotalSizeInMB]
			= [TotalSize]
		, [UsedSizeInMB]
			= [UsedSize]

from   @FileDetails 


Output

FileLayout

 

sys.sysfiles


use [tempdb]
go

Select 
		  
			= 'sys.sysfiles'

        , [name]

		, [filename]

        , [SizeInMB]
            = [size] / 128.0 

        , [UsedInMB]
            = Fileproperty(name, 'SpaceUsed') / 128.0 

        , [AvailableSpaceInMB]
            = (size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int))
                /128.0 
 
        , PercentFull
            = Cast((Fileproperty(name, 'SpaceUsed') * 100.0) / size As Int)

from sys.sysfiles sd


 

sys.master_files


Select 
		  
			= 'tempdb.sys.master_files'

        , [name]
 
        , physical_name
 
        , [SizeInMB]
            = [size] / 128.0 
 
        , [UsedInMB]
            = Fileproperty(name, 'SpaceUsed') / 128.0 
 
        , [AvailableSpaceInMB]
            = (size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int))
                /128.0 
 
        , PercentFull
            = Cast((Fileproperty(name, 'SpaceUsed') * 100.0) / size As Int)
 
        , is_percent_growth
 
        , [growth]
            = case
                    when is_percent_growth=0 then
                        cast(   sd.growth/256 as varchar(30)) + ' MB'
                    else cast(sd.growth as varchar(30)) + '%'
              end  
 
from sys.master_files sd
 
where  sd.database_id = db_id('tempdb')



Output:

Pre SQL Instance Reboot

IncorrectTempdbSizeInMaster_files

Post SQL Instance Reboot

TempdbSizePostSQLInstanceReboot

Explanation:

In some cases, the figures reported in tempdb.sys.master_files will be incorrect.  Please refer to the numbers in sys.sysfiles instead.

Restarting the SQL Instance fixed this problem for us.

Details listed in the Connect Section of this post.

Btw, the differences between sys.master_files are sys.sysfiles are:

  • Master_files is database specific, while sys.sysfiles applies to all databases

 

Object Type Utilization

Object Type Utilization – Page Count

Storage allocations are stored in the system tables in increments of page counts.

Here we query sys.dm_db_file_space_usage in the tempdb database for the page counts



SELECT
		  [InternalObjectPageCount] = SUM ([internal_object_reserved_page_count])
		, [VersionStorePageCount] = SUM ([version_store_reserved_page_count])
		, [UserObjectPageCount] = SUM ([user_object_reserved_page_count])
		, [MixedExtentPageCount] = SUM ([mixed_extent_page_count])
		, [TotalPageCount]
			= SUM 
				(
					  [internal_object_reserved_page_count]
					+ [version_store_reserved_page_count]
					+ [user_object_reserved_page_count]
					+ [mixed_extent_page_count]
				)

FROM sys.dm_db_file_space_usage

ObjectTypeUtilization

Object Type Utilization – Readable

In SQL Server, the page size is 8 KB.  And, so we multiply the page count by 8 to get Kilobytes.

 


SELECT
		  [InternalObjectKB]
			 = SUM ([internal_object_reserved_page_count]) * 8

		, [VersionStoreKB] 
			= SUM ([version_store_reserved_page_count]) * 8

		, [UserObjectKB]
			 = SUM ([user_object_reserved_page_count]) * 8

		, [MixedExtentKB]
			= SUM( [mixed_extent_page_count]) * 8

		, [TotalKB]
			= sum(
					  [internal_object_reserved_page_count]
					+ [version_store_reserved_page_count]
					+ [user_object_reserved_page_count]
					+ [mixed_extent_page_count]
				 ) * 8

FROM sys.dm_db_file_space_usage


Output:

ObjectTypeUtilization-Readable

Object Type Utilization – Percentile

Here we get percentile.


;with cteTotal
(
	  [user_object]

	, [internal_object]

	, [version_store]

	, [mixed_extent]

	, [total]
)
as
(
	select

		  sum([user_object_reserved_page_count])

		, sum([internal_object_reserved_page_count])

		, sum([version_store_reserved_page_count])

		, sum([mixed_extent_page_count])

		, [total]
			=  sum
				(
					  [user_object_reserved_page_count]
					+ [internal_object_reserved_page_count]
					+ [version_store_reserved_page_count]
					+ [mixed_extent_page_count]
				)

	FROM sys.dm_db_file_space_usage		

)
SELECT
		  [UserObject%] 
			= cast
					(
						[user_object] * 100.00
								/
							total
						as decimal(10, 2)
					)

		  , [InternalObject%] 
			= cast
					(
						([internal_object] * 100.00 )
								/
							[total]
						as decimal(10, 2)
					)

		  , [VersionStore%] 
			= cast
					(
						([version_store] * 100.00 )
								/
							[total]
						as decimal(10, 2)
					)

		  , [MixedExtent%] 
			= cast
					(
						([mixed_extent] * 100.00 )
								/
							[total]
						as decimal(10, 2)
					)

FROM cteTotal


Output:

ObjectTypeUtilization-Percentile

 

Explanation

In our case, mixed extends and internal objects are the biggest users of Tempdb.

 

File Usage

Map Objects to Individual Files

DBCC Extent Info


DBCC Extent Info

 

Output

mapFileToIndividualObjects

Explanation

Above is a good representation of how each object is stored in each File.

 

Map Objects to File Groups

sys.allocation_units


use [tempdb]
go

; with cteObject
(
	  [object_id]
	, [object]
	, type_desc
	, [create_date]

)
as
(
	select 

			  tblO.object_id

			, [object]
				= object_schema_name
				(
					  tblO.object_id
				)
				+ '.'
				+ object_name
				(
					  tblO.object_id
				)

			, tblO.type_desc

			, tblO.[create_date]

	from   Sys.Objects tblO
)
select 

		  [fileGroup]
			= tblFG.type_desc
		, tblAU.type_desc
		, tblAU.total_pages
		, tblAU.used_pages
		--, tblP.object_id
		, tblO.[object]
		, [objectType]
			= tblO.type_desc
		, tblO.[create_date]
		, tblP.index_id

		, [index]
			= CASE
					WHEN (tblP.index_id = 0) then 'Heap'
					else tblI.name
			  END

		, [rows]
			= tblP.[rows]


from   sys.allocation_units tblAU

inner join sys.filegroups tblFG

	on tblAU.data_space_id = tblFG.data_space_id

Join Sys.Partitions tblP

	On tblAU.Container_Id = tblP.Partition_Id

Join cteObject tblO

	On tblP.object_id = tblO.object_id

Join Sys.Indexes tblI

	On tblP.object_id = tblI.object_id



where tblO.type_desc not in
		(
			'SYSTEM_TABLE'
		)

and (
		tblO.[object]
			not like 
				(
					'sys.queue_messages%'
				)
	)

and (
		tblO.[object]
			not in 
				(
					  'sys.service_broker_map'
					, 'dbo.MSdistributor_access'
				)
	)




 

Output

MapIndividualObjectsToFileGroups

Explanation
  1. We filtered out
    •  sys.objects.type_desc
      • SYSTEM_TABLE
    • sys.objects.name
      • sys.queue_messages*
      • sys.service_broker_map
      • dbo.MSdistributor_access

 

User Activity

Storage Allocation per individual Session


/*
	Deepak Biswal
	Monitoring tempdb Transactions and Space usage
	https://blogs.msdn.microsoft.com/deepakbi/2010/04/13/monitoring-tempdb-transactions-and-space-usage/

*/
SELECT
          [SESSION ID] 
			= tblES.session_id 

        , [System Name]
			 = HOST_NAME

        , [Program Name] 
			= program_name

        , [USER Name]
			 = login_name

        , [status]
			= tblES.[status]

        , [USERObjectsAlloc (in KB)]
			= (tblSSU.[user_objects_alloc_page_count] * 8) 

        , [USERObjectsDealloc (in KB)]
			= (tblSSU.[user_objects_dealloc_page_count] * 8) 

        , [InternalObjectsAlloc (in KB)]
			= (tblSSU.[internal_objects_alloc_page_count] * 8)

		, [InternalObjectsDeAlloc (in KB)]
			= (tblSSU.[internal_objects_dealloc_page_count] * 8)

		, [InternalObjectsDelta (in KB)]
			= (
				 (
					tblSSU.[internal_objects_alloc_page_count]
					 - tblSSU.[internal_objects_dealloc_page_count] 
				 )
					* 8
				)

       , [SESSION Type]
			= CASE is_user_process
					WHEN 1 THEN 'user session'
					WHEN 0 THEN 'system session'
			  END 

		, [sql]
			= case

				when tblST.[objectid] is not null then

					quoteName
					(
						object_schema_name
						(
							tblST.[objectid]
						  , tblST.[dbid]
						)
					)
					+ '.'
					+ quoteName
						(
							object_name
							(
								  tblST.[objectid]
								, tblST.[dbid]
							)
						)

				else tblST.[text]

			end

	, tblDTST.[transaction_descriptor]

FROM sys.dm_db_session_space_usage tblSSU


INNER join sys.dm_exec_sessions tblES

	ON tblSSU.session_id = tblES.session_id

left outer join sys.dm_exec_requests tblER

	ON tblES.session_id = tblER.session_id

LEFT OUTER join sys.dm_tran_session_transactions tblDTST

	ON tblSSU.session_id = tblDTST.session_id

outer apply sys.dm_exec_sql_text(tblER.sql_handle) tblST

where   tblSSU.database_id = db_id('tempdb')

order by
		(
			user_objects_alloc_page_count 
				+ internal_objects_alloc_page_count
		) desc


 

Output

IndividualSessionUsage

 

Explanation

  1. You want to pay attention to individual sessions, especially the biggest users in terms of user and internal objects and see how they are using TempDB
  2. It is also worthy to note that there might be a gap between object allocation and deallocation
    • In some cases people create temporary objects, but they forget to explicitly drop them
    • Of course, the problem can also be “opened transactions”

 

Connect Items

  1. Master_files does not show accurate size information
    • Opened By :- Michael Hotek
    • Connect Item :- 377223
    • Date :- 10/22/2008 6:37:14 PM
    • Status :- Closed

Summary

Above is a pretty much a regurgitation of what is available online.

As seen from querying sys.allocation_units, Service Broker and Replication Engines are used by TempDB.

In a followup post, we will see how current utilization such as Database settings such as Snapshot Isolation, maintenance operations such as Index Rebuilding, and every day query processing such as Hash Joins and Sorts can prevent online resizing and repositioning of TempDB files.

 

References

  1. Working with tempdb in SQL Server 2005
    https://technet.microsoft.com/library/Cc966545
  2. Deepak Biswal – Monitoring tempdb Transactions and Space usage
    https://blogs.msdn.microsoft.com/deepakbi/2010/04/13/monitoring-tempdb-transactions-and-space-usage/
  3. Kalen Delaney – SQL Server Pro – Trouble with Mixed Extents – Iron out contention-causing page allocations
    http://sqlmag.com/t-sql/trouble-mixed-extents
  4. Deferred Deallocations of Pages in TempDb
    http://sqlity.net/en/671/deferred-deallocations-of-pages-in-tempdb/

 

One thought on “SQL Server – What is in your TempDB?

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