This article is contributed. See the original author and article here.

Working on a case I got to scenario where customer may would like to better understand the TEMPDB usage and isolation between databases in an elastic pool.

 

First speaking about isolation. Each DB even though they are below one logical instance, behind the scenes each Azure DB will leave on different node. So, each DB will have one isolated DB, except for Elastic Pool that share same hardware and share same TEMPDB. Even though there are some considerations to check reg isolation of data. More information below:

 

2020-08-07 15_47_51-Clipboard.png

 

https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database

 

Azure SQL Database single databases and elastic pools support global temporary tables and global temporary stored procedures that are stored in tempdb and are scoped to the database level. Global temporary tables and global temporary stored procedures are shared for all users’ sessions within the same Azure SQL database. User sessions from other Azure SQL databases cannot access global temporary tables. For more information, see Database scoped global temporary tables (Azure SQL Database)Azure SQL Managed Instance) supports the same temporary objects as does SQL Server. For Azure SQL Database single databases and elastic pools, only master database and tempdb database apply. For more information, see What is an Azure SQL Database server. For a discussion of tempdb in the context of Azure SQL Database single databases and elastic pools, see tempdb Database in Azure SQL Database single databases and elastic pools. For Azure SQL Managed Instance, all system databases apply.

 

One way to test the isolation you can create a global temp table, like sample below.

DROP TABLE IF EXISTS ##TEMP_COLUMNS 
GO
SELECT * INTO ##TEMP_COLUMNS
FROM sys.columns

When trying to select from the global temp connected to another database you should get

SELECT * FROM ##TEMP_COLUMNS 

Msg 208, Level 16, State 0, Line 1
Invalid object name '##TEMP_COLUMNS'.

On Elastic pool also same rule applies. Even though they are sharing the same space used, global temp tables are scoped to database level.

 

TEMPDB Space monitoring

First you need to check what is your database SLO. The max space for each DB or Pool will depends on SLO for DB

SELECT * FROM [sys].[database_service_objectives] DSO
database_id edition service_objective elastic_pool_name
----------- ------- ----------------- -----------------
8 Basic ElasticPool fonsecanetPool

 

DTU Model

vCore

 

Then you can use queries like sample below to monitor the TEMPDB usage

 

 

 

 

-- Determining the Amount of Space Used  / free
SELECT 
	 [Source] = 'database_files'
	,[TEMPDB_max_size_MB] = SUM(max_size) * 8 / 1027.0
	,[TEMPDB_current_size_MB] = SUM(size) * 8 / 1027.0
	,[FileCount] = COUNT(FILE_ID)
FROM tempdb.sys.database_files
WHERE type = 0 --ROWS

SELECT 
	 [Source] = 'dm_db_file_space_usage'
	,[free_space_MB] = SUM(U.unallocated_extent_page_count) * 8 / 1024.0
	,[used_space_MB] = SUM(U.internal_object_reserved_page_count + U.user_object_reserved_page_count + U.version_store_reserved_page_count) * 8 / 1024.0
    ,[internal_object_space_MB] = SUM(U.internal_object_reserved_page_count) * 8 / 1024.0
    ,[user_object_space_MB] = SUM(U.user_object_reserved_page_count) * 8 / 1024.0
    ,[version_store_space_MB] = SUM(U.version_store_reserved_page_count) * 8 / 1024.0
FROM tempdb.sys.dm_db_file_space_usage U

-- Obtaining the space consumed currently in each session
SELECT 
	 [Source] = 'dm_db_session_space_usage'
	,[session_id] = Su.session_id
	,[login_name] = MAX(S.login_name)
	,[database_id] = MAX(S.database_id)
	,[database_name] = MAX(D.name)
	,[elastic_pool_name] = MAX(DSO.elastic_pool_name)
	,[internal_objects_alloc_page_count_MB] = SUM(internal_objects_alloc_page_count) * 8 / 1024.0
	,[user_objects_alloc_page_count_MB] = SUM(user_objects_alloc_page_count) * 8 / 1024.0
FROM tempdb.sys.dm_db_session_space_usage SU
LEFT JOIN sys.dm_exec_sessions S
        ON SU.session_id = S.session_id
LEFT JOIN sys.database_service_objectives DSO
        ON S.database_id = DSO.database_id
LEFT JOIN sys.databases D
	ON S.database_id = D.database_id
WHERE internal_objects_alloc_page_count + user_objects_alloc_page_count > 0
GROUP BY Su.session_id
ORDER BY [user_objects_alloc_page_count_MB] desc, Su.session_id;


-- Obtaining the space consumed in all currently running tasks in each session
SELECT 
	 [Source] = 'dm_db_task_space_usage'
	,[session_id] = SU.session_id
	,[login_name] = MAX(S.login_name)
	,[database_id] = MAX(S.database_id)
	,[database_name] = MAX(D.name)
	,[elastic_pool_name] = MAX(DSO.elastic_pool_name)
	,[internal_objects_alloc_page_count_MB] = SUM(SU.internal_objects_alloc_page_count) * 8 / 1024.0
	,[user_objects_alloc_page_count_MB] = SUM(SU.user_objects_alloc_page_count) * 8 / 1024.0
FROM tempdb.sys.dm_db_task_space_usage SU
LEFT JOIN sys.dm_exec_sessions S
        ON SU.session_id = S.session_id
LEFT JOIN sys.database_service_objectives DSO
        ON S.database_id = DSO.database_id
LEFT JOIN sys.databases D
	ON S.database_id = D.database_id
WHERE internal_objects_alloc_page_count + user_objects_alloc_page_count > 0
GROUP BY SU.session_id
ORDER BY [user_objects_alloc_page_count_MB] desc, session_id;

 

 

https://github.com/FonsecaSergio/ScriptCollection/blob/master/SQL/AzureSQL%20-%20Monitor%20tempdb%20usage.sql

 

 

In this test we can see this DB is part of Basic Elastic Pool.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15#tempdb-database-in-sql-database

SLO Max tempdb Data File Size (GBs) # of tempdb data files Max tempdb data size (GB)
Basic Elastic Pools (all DTU configurations) 13.9 12 166.7

 

FonsecaSergio_0-1598966140442.png

 

We can see in the results above

  1. TEMPDB max size / current size and File count
    • That depends on the DB Service level as mentioned above
  2. TEMPDB file space used where
    • internal_object_reserved_page_count – Total number of pages in uniform extents allocated for internal objects in the file. Unused pages from an allocated extent are included in the count.

      There is no catalog view or dynamic management object that returns the page count of each internal object.

      • Internal objects are only in tempdb. The following objects are included in the internal object page counters:
        • Work tables for cursor or spool operations and temporary large object (LOB) storage
        • Work files for operations such as a hash join
        • Sort runs
    • user_object_reserved_page_count – Total number of pages allocated from uniform extents for user objects in the database. Unused pages from an allocated extent are included in the count.

      You can use the total_pages column in the sys.allocation_units catalog view to return the reserved page count of each allocation unit in the user object. However, note that the total_pages column includes IAM pages.

      • The following objects are included in the user object page counters:
        • User-defined tables and indexes
        • System tables and indexes
        • Global temporary tables and indexes
        • Local temporary tables and indexes
        • Table variables
        • Tables returned in the table-valued functions
    • version_store_reserved_page_count – Total number of pages in the uniform extents allocated for the version store. Version store pages are never allocated from mixed extents.

      For more information, see sys.dm_tran_version_store (Transact-SQL).

  3. TEMPDB usage per session
    • We can see session 79 is using around 79 Mb of TEMPDB for user objects
  4. TEMPDB usage in running task per session
    • In this view we can see all user requests currently running that are consuming tempdb, this can be also useful to do some specific troubleshooting. In this image we can see some internal objects being currently used. Could be used for sorting / hash join / etc or some other internal operation.

 

For this second test I created a global temp table in another DB in the pool, we can monitor the usage but by design we will miss some metadata like DB name as we cannot look on master.sys.databases and local user db sys.databases only includes master + current user db. With this we can see that all databases in the same elastic pool share same tempdb database space. Even though you still cannot access global temp table from other database in same pool as temp tables are scoped to database level.

 

FonsecaSergio_0-1598965525617.png

 

We can still connect to master db and check sys.databases manually to match database id to name

 

SELECT database_id, name FROM sys.databases

FonsecaSergio_1-1598965697029.png

 

Transactions using TEMPDB

You can also connect directly to user DB and check if there is any session ID that have a open transaction using TEMPDB.

 

SELECT 
	 [Source] = 'database_transactions'
	,[session_id] = ST.session_id
	,[transaction_id] = ST.transaction_id
	,[database_id] = DT.database_id
	,[database_name] = CASE
		WHEN D.name IS NULL AND DT.database_id = 2 THEN 'TEMPDB'
		ELSE D.name
	 END
	,[database_transaction_log_used_Kb] = CONVERT(numeric(18,2), DT.database_transaction_log_bytes_used / 1024.0 )
	,[database_transaction_begin_time] = DT.database_transaction_begin_time
	,[transaction_type_desc] = CASE database_transaction_type
		WHEN 1 THEN 'Read/write transaction'
		WHEN 2 THEN 'Read-only transaction'
		WHEN 3 THEN 'System transaction'
		WHEN 4 THEN 'Distributed transaction'
	END
	,[transaction_state_desc] = CASE database_transaction_state
		WHEN 0 THEN 'The transaction has not been completely initialized yet'
		WHEN 1 THEN 'The transaction has been initialized but has not started'
		WHEN 2 THEN 'The transaction is active'
		WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions'
		WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place'
		WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
		WHEN 6 THEN 'The transaction has been committed'
		WHEN 7 THEN 'The transaction is being rolled back'
		WHEN 8 THEN 'The transaction has been rolled back'
	END
FROM sys.dm_tran_database_transactions DT
INNER JOIN sys.dm_tran_session_transactions ST
	ON DT.transaction_id = ST.transaction_id
LEFT JOIN sys.databases D
	ON DT.database_id = D.database_id
ORDER BY ST.session_id

 

 

FonsecaSergio_1-1599044174417.png

 

 

 

 

 

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.