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:
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
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
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%'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.