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:
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
SELECT * INTO ##TEMP_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.
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
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;
In this test we can see this DB is part of Basic Elastic Pool.
|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|
We can see in the results above
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.
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.
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).
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.