This is how TempDB tables implemented in MsDyn365FO

I have a comment regarding your statement in the original post "And as far as I know there is no clean up routine for sandbox environments (but hopefully for production environments)." Have you done any further investigation on this topic? I can share that in production environment (average sized cloud installation, 4AOSes, lots of mods, high user load during daytime ) our client runs, currently there are almost 15k t% tables, collected over a year. So apparently system isn't doing a successful cleanup. I can't believe they're still in use, therefore I'm about to create a support ticket.

Hi Paul Topic is bit old, but still might be relevant. DB administrator informed me, that database copies from production contain high number ( more than 10 thousand) of "temporary" tables in AXDB. Lot of them where empty, but not all. I might not recall exact dates and PUs, but per May 2022, there were even "temporary" tables, oldest created even back in August 2021. In our case it happened when PU43 was applied. I guess, there is some routine who drops these tables, but apparently it hadn't worked in our case. At least not all instances of tables were dropped. One possible reason for that - there happens AOS crashes couple times a week in our installation, but reason is not related to temp tables. May be after these crashes system isn't able to clean up those tables. I will check with DB admin now, to find out whether this issue is still a problem. Still - you topic was useful to understand what these table are, and to learn about existance of TEMPTABLEINAXDB. What I wanted to ask you - do you believe there are cases when system might run better when TEMPTABLEINAXDB = 0?

Hi Paul, I came across your article when I was doing an investigation to a performance issue in which we were seeing high contention in the AxDB database on temp tables when running a multi-threaded batch (500+ bundles). We're experiencing a lot of waiting tasks, up to 60 seconds on just the creation, truncation or drop of temp tables. We're able to see this in a P15 database with only 5 AOS servers (16 threads each). After spending more than a week on this issue, I found out that by default, a TIER-2 environment stores its temp tables in the AxDB but that you can override this behavior by switching a setting in the database (see below). We have seen great performance improvements by enabling the setting, especially in processes that heavily rely on Temp tables such as posting free-text invoices (factor 3x more throughput). Temp tables are now being created in the tempdb. We're now being constrained by CPU database (to be expected), but at least it now scales properly again. In the mean time, Microsoft has confirmed that we should be using this setting to remove the bottleneck on the temp tables. UPDATE sysglobalconfiguration SET VALUE = 1, RECVERSION = 2 WHERE NAME = 'VOLATILE_ISGLOBALTEMPTABLEENABLED'? https://azure.microsoft.com/en-us/blog/database-scoped-global-temporary-tables-in-public-preview-for-azure-sql-db/ One issue: heavy use of temp tables is causing the Query Store to go in read-only mode. In our case, that happens already after a few minutes of batch processing. Apparently a known issue. We're working on that with Microsoft. Hope this helps!

As usual in public SAAS cloud services, this is subject to change and is indeed different on different customers, both the implementation of temp tables and the naming of those. What is described is correct for some instances as of today, but not for all of them, and the configuration can and will change depending on the instance workload, future evolution of the platform, or changes on customer usage patterns. So, if you feel your tempdb is a problem please reach support or fast track for help :)