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
From “Working with tempdb in SQL Server 2005” available here.
- Instant Data file Initialization
- 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.
- 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
- 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
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
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
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')
Pre SQL Instance Reboot
Post SQL Instance Reboot
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
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
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
In our case, mixed extends and internal objects are the biggest users of Tempdb.
Map Objects to Individual Files
DBCC Extent Info
DBCC Extent Info
Above is a good representation of how each object is stored in each File.
Map Objects to File Groups
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' ) )
- We filtered out
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
- 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”
- 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
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.
- Working with tempdb in SQL Server 2005
- Deepak Biswal – Monitoring tempdb Transactions and Space usage
- Kalen Delaney – SQL Server Pro – Trouble with Mixed Extents – Iron out contention-causing page allocations
- Deferred Deallocations of Pages in TempDb