Today, I worked on a service request that our customer asked about the tempdb configuration in Azure SQL Database and Managed Instance and if we could have any type of contention in tempdb.
In terms of the TempDB configuration, you could see this configuration if you are using:
- Azure SQL Database you could execute:
SELECT * FROM SYS.dm_io_virtual_file_stats(2,null) A INNER JOIN TEMPDB.sys.database_files B ON A.file_id = B.FILE_ID
- Azure SQL Managed Instance you could execute:
SELECT files.physical_name, files.name, stats.num_of_writes,
(1.0*stats.io_stall_write_ms/stats.num_of_writes) AS avg_write_stall_ms,
stats.num_of_reads,
(1.0*stats.io_stall_read_ms/stats.num_of_reads) as avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'
In another hand, in terms of the TempDB contention, if you want to test either Azure SQL Database or Azure SQL Managed Instance, basically downloading the file ostress.exe and run, for example, this example:
"C:\Program Files\Microsoft Corporation\RMLUtils\ostress.exe" -Syourservername.database.windows.net -Uusername -PPassword -ddatabasename -Q"SELECT TOP (5000) * INTO #t FROM dbo.AnyTable; DROP TABLE #t;" -n100 -r30
- So, depending on the TEMPDB capacity based on your database tier or Azure SQL Managed instance tier, you could try to reproduce contention. But, let me mention that Azure SQL Database and Managed Instance has already configure internally the trace flag 1117 AND 1118.
- Also, for Azure SQL Managed Instance review this article about performance monitoring.
Finally, I found that we have more information available for both model of databases based on the DMV: sys.dm_os_performance_counters to review temporal tables created.
select *
from sys.dm_os_performance_counters
where counter_name like '%temp%'
Enjoy!
