This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
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:
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
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
- TEMPDB max size / current size and File count
- That depends on the DB Service level as mentioned above
- 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
- Internal objects are only in tempdb. The following objects are included in the internal object page counters:
-
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
- The following objects are included in the user object page counters:
-
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).
-
- TEMPDB usage per session
- We can see session 79 is using around 79 Mb of TEMPDB for user objects
- 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.
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
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.