Understanding Temp Storage Cleanup and IOPS Utilization in Azure SQL Managed Instance

Hi @Swaroop Gowda D
In addition to the information already shared, here are a few recommendations that may help with ongoing monitoring and troubleshooting.

For TempDB, it's a good practice to monitor usage trends rather than checking only when an issue occurs. You can periodically query sys.dm_db_file_space_usage and sys.dm_db_session_space_usage to identify workloads or sessions that are consistently consuming large amounts of TempDB. If you notice frequent growth or high usage, reviewing execution plans for operations such as sorts, hash joins, hash aggregates, index rebuilds, or spills to TempDB may help identify opportunities for query or index optimization. If your workload uses snapshot isolation or Read Committed Snapshot Isolation (RCSI), it's also worth monitoring the version store, as long-running transactions can delay cleanup and increase TempDB usage.

For IOPS monitoring, Azure SQL Managed Instance does not expose a direct metric showing the current IOPS consumed versus the provisioned limit. Microsoft generally recommends monitoring Data IO percentage and Log IO percentage in Azure Monitor, together with SQL Server DMVs such as sys.dm_io_virtual_file_stats, to understand storage activity. If you observe consistently high Data IO or Log IO percentages, along with increasing I/O-related wait types such as PAGEIOLATCH_*, WRITELOG, or IO_COMPLETION, it may indicate that the workload is approaching the available storage throughput.

If you're troubleshooting a performance issue, collecting Azure Monitor metrics (Data IO %, Log IO %, CPU, and Storage), along with outputs from sys.dm_io_virtual_file_stats, wait statistics (sys.dm_os_wait_stats), and the top resource-consuming queries from Query Store, can provide a comprehensive view of whether storage I/O is the bottleneck or if query optimization is needed.

In addition to Alex's post:

Is this 1.1 TB preallocated space or actual TempDB usage?

A database has two or more files allocated to it. These files have a certain size. The actual space used in these files can be a lot lower. In a user database, the used space typically grows over time. In tempdb, the allocated spaces goes up and down, depending on the usage.

To see the current usage you can run

EXEC tempdb..sp_spaceused

If TempDB becomes full, what is the behavior of Azure SQL Managed Instance?

When a file gets full, SQL Server's reaction is to grow the file. However, this assumes a few things:

• Autogrow is enabled.
• The max size configured for the file is bigger than the current allocation.
• There is actually space on the disk for the database to grow.

I don't know for sure for Azure SQL MI, but I suspect that the possibility for tempdb to grow is limited. So if you perform an operation that fills up tempdb, that operation will error out. The effect on the rest of the instance may be limited, though. I recall that I once ran an operation on a production server (on-prem not MI), which terminated with "tempdb full". As I was only a temporary guest on this server, I got a bit nervous, but when I ran sp_spaceused, just a few per cent of the space was in use. The big temporary object I had accidentally created was already gone.

That said, if you fill up a gargantuan temp table bit by bit, only the last few rows will be rolled back. So, yes, you can cause situations where everyone on the server faces the situation that tempdb is full, which makes the instance quite difficult to use.

A bit of good news here: Microsoft recently introduced the possibility to control tempdb usage through Resource Governor, so that not just any one can fill up tempdb and cause a outage.

Does SQL Server automatically clear TempDB contents, and under what conditions?

A lot of the objects in tempdb are temporary are dropped automatically at some point. Worktables for spools and sort and hash spills go away when the query completes. Temp tables created in a stored procedure are dropped when the procedure exits. But you can create a temp table on session level, and it will exist until you explicitly drop it or you disconnect. And there is nothing to stop you from creating permanent objects in tempdb. They will be there until the instance is restarted, unless you drop them yourselves.

hi & thx for sharing urs issue here at Q&A portal,

For TempDB, that 1.1 TB is usually allocated/available TempDB capacity for the MI, not proof that TempDB is actually using 1.1 TB right now. TempDB is recreated when SQL Server starts, and objects inside it are temporary. But it doesn’t mean space is instantly ‘shrunk’ after every query. SQL Server can keep TempDB files grown/allocated for reuse. So allocated size and current used size are diff things.

To see real TempDB usage, use DMVs, not just the portal size number

SELECT

SUM(unallocated_extent_page_count) * 8 / 1024 AS free_mb,

SUM(user_object_reserved_page_count) * 8 / 1024 AS user_objects_mb,

SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,

SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,

SUM(mixed_extent_page_count) * 8 / 1024 AS mixed_extent_mb

FROM sys.dm_db_file_space_usage;

To find sessions using TempDB

SELECT

session_id,

user_objects_alloc_page_count * 8 / 1024 AS user_objects_mb,

internal_objects_alloc_page_count * 8 / 1024 AS internal_objects_mb

FROM sys.dm_db_session_space_usage

ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC;

If TempDB fills up, queries that need TempDB can fail, spill, or throw errors. It can affect sorting, hash joins, row versioning, temp tables, index rebuilds, etc. SQL won’t magically clear active TempDB usage while sessions still need it. Space is released when temp objects are dropped, transactions finish, row versions age out, or sessions end.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits

For IOPS, don’t look only at the provisioned 64,000. That’s the limit/capacity, not current usage.

In SQL, u can use file-level IO DMVs

SELECT

DB_NAME(vfs.database_id) AS database_name,

mf.name AS file_name,

vfs.num_of_reads,

vfs.num_of_writes,

vfs.io_stall_read_ms,

vfs.io_stall_write_ms

FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs

JOIN sys.master_files mf

ON vfs.database_id = mf.database_id

AND vfs.file_id = mf.file_id;

That gives cumulative reads/writes since last restart/failover. To get IOPS, sample it every minute and calculate delta reads+writes / seconds. Kinda annoying, but that’s the normal way.

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/monitoring-sql-managed-instance-azure-monitor

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql

TempDB allocated size isn’t the same as current usage. For IOPS, use Azure Monitor where available + DMV deltas from sys.dm_io_virtual_file_stats. If latency/stall time is rising while IOPS is high, that’s a better sign u are approaching the limit than the raw provisioned number alone.

rgds,

Alex

&

If my answer was helpful pls mark it and additional thx if u follow me at Q&A portal

and at my blog https://ctrlaltdel.blog/