28 January 2023

SQL Reloaded: Temporary Tables and Tempdb in Serverless SQL Pool

In SQL Server, temporary tables are stored in tempdb database and the front end (FE) SQL Server from a serverless SQL Server pool makes no exception from it, however there's no such database listed in the list of databases available. Watching today Brian Bønk's session on "Azure Synapse serverless - CETAS vs Views vs Parquet" at Data Toboggan winter edition 2023, the speaker pointed out that the tables available in tempdb database can be actually queried:

-- Azure Serverless SQL pool: tempdb tables
SELECT top 10 *
FROM tempdb.information_schema.tables
TABLE_CATALOGTABLE_SCHEMATABLE_NAMETABLE_TYPE
tempdbdbo#dm_continuous_copy_status_..._0000000029C7BASE TABLE
tempdbdbodiff_MonDmSbsConnectionsBASE TABLE
tempdbdbodiff_MonTceMasterKeysBASE TABLE
tempdbdbodiff_MonTceColEncryptionKeyBASE TABLE
tempdbdbodiff_MonAutomaticTuningStateBASE TABLE
tempdbdbodiff_MonDmTranActiveTransactionsBASE TABLE
tempdbdbodiff_MonTceEnclaveUsageInfoBASE TABLE
tempdbdbodiff_MonTceEnclaveColEncryptionKeyBASE TABLE
tempdbdbodiff_MonTceEnclaveMasterKeysBASE TABLE
tempdbdbodiff_MonTceColumnInfoBASE TABLE

Moreover, the content of the system tables available can be queried, even if some of the tables might have no data:
 
-- Azure Serverless SQL pool: checking a table's content
SELECT top 10 *
FROM tempdb.dbo.dmv_view_run_history

How about the temporary tables created? To check this, let's reuse a temp table created recently for listing the DMVs available in the serverlss SQL pool:

-- dropping the temp table
DROP TABLE IF EXISTS dbo.#views;

-- create the temp table
CREATE TABLE dbo.#views (
  ranking int NOT NULL
, view_name nvarchar(150) NOT NULL
)

-- inserting a few records
INSERT INTO #views
SELECT row_number() OVER(ORDER BY object_id) ranking
, concat(schema_name(schema_id),'.', name) view_name
FROM sys.all_views obj
WHERE obj.Type = 'V'
  AND obj.is_ms_shipped = 1
  AND obj.name LIKE 'dm_exec_requests%'

-- checking temp table's content
SELECT *
FROM dbo.#views
rankingview_name
1sys.dm_exec_requests
2sys.dm_exec_requests_history

Here's temp table's metadata:
 
-- checking temp table's metadata
SELECT *
FROM tempdb.information_schema.tables
WHERE table_name like '#views%'
TABLE_CATALOGTABLE_SCHEMATABLE_NAMETABLE_TYPE
tempdbdbo#views_____..._____00000000295BBASE TABLE

Of course, you can query also the tempdb.sys.all_objects: 

-- checking temp table's metadata
SELECT *
FROM tempdb.sys.all_objects
WHERE name like '#views%'

You can use now the table name returned by any of the two queries to call the table (just replace the name accordingly):

-- querying the table via tempdb (name will differ)
SELECT *
FROM tempdb.dbo.[#views______________________________________________________________________________________________________________00000000295B]
rankingview_name
1sys.dm_exec_requests
2sys.dm_exec_requests_history

The benefit of knowing that is neglectable, however the topic is interesting more for the ones who want to know how the pools in Azure Synapse work, what was kept or removed compared with the standard editions of SQL Server. 

Thus, another topic interesting for DBAs would be how many files are created for the tempdb, given that the database is used to store intermediate data for the various operations. Finding the optimal number of tempdb files and configuring them was and still is one of the main concerns when configuring an SQL Server instance for optimal performance.

The old query developed for standard SQL Server seems to work as well:
 
-- Azure Serverless SQL pool: tempdb files 
SELECT dbf.file_id
, dbf.name file_name
--, dbf.physical_name
, dsp.name file_group
--, type 
, dbf.type_desc file_type
--, dbf.growth growth_kb
, Cast(dbf.growth/128.0  as decimal(18,2)) growth_mb
, dbf.is_percent_growth
--, dbf.max_size max_size_kb
, Cast(NullIf(dbf.max_size, -1)/128.0  as decimal(18,2)) max_size_mb
--, dbf.size file_size_kb
, Cast(dbf.size/128.0 as decimal(18,2)) file_size_mb
, dbf.state_desc 
, dbf.is_read_only 
FROM tempdb.sys.database_files dbf
     LEFT JOIN tempdb.sys.data_spaces dsp
       ON dbf.data_space_id = dsp.data_space_id
ORDER BY dbf.Name
file_idfile_namefile_groupfile_typegrowth_mbis_percent_growthmax_size_mbfile_size_mbstate_descis_read_only
1tempdevPRIMARYROWS32010267016ONLINE0
3tempdev2PRIMARYROWS32010267016ONLINE0
4tempdev3PRIMARYROWS32010267016ONLINE0
5tempdev4PRIMARYROWS32010267016ONLINE0
2templogNULLLOG64016384255ONLINE0

So, there were created 4 data files of 16 MB each, with a fix growth of 32 MB, and the files can grow up to about 100 GB. What would be interesting to check is how the files grow under heavy workload, what kind of issues this raises, etc. At least in serverless SQL pools many of the views one used to use for troubleshooting aren't accessible. Maybe one doesn't have to go that far, given that the resources are managed automatically. 

Happy coding!

No comments:

Related Posts Plugin for WordPress, Blogger...

About Me

My photo
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.