This is how TempDB tables implemented in MsDyn365FO

I think almost every Microsoft Dynamics 365 for Finance and Operation developer knows how to create and use a TempDB tables via metadata or code. But I also think that only a few of them know how these tables implemented from a platform point of view. In this post I want to shade some light into this topic especially for environments running with Azure SQL.

My research and this post has been initiated by the following long running query which I found via LCS during one of our load tests: ALTER TABLE DBO.t9294BatchSASSB4AOS2246116_690AB03A15E646258EB6205137807460 ADD CONSTRAINT i9294_I_9294RECID_690AB03A15E646258EB6205137807460 PRIMARY KEY CLUSTERED (RECID)

What I was wondering after I found this query is: Why is this statement slow and why is this statement happening in the AxDB? The first question needs more analysis but the second question is an easy one. There is a setup in table SysGlobalConfiguration with name TEMPTABLEINAXDB which is set to 1 for environments using Azure SQL. This means all “temp tables” get created in the AxDB. And as far as I know there is no clean up routine for sandbox environments (but hopefully for production environments). As you can see in the banner of this post, you can also filter/find them using SQL Server Management Studio.

If you want to analyze the behavior on an one box (Tier1) environment you can simply insert a temp table via X++ code and try to find the created table in the tempdb. If you want the same behavior as in Azure SQL environments you can simply set the value for TEMPTABLEINAXDB to 1.

In case you found some if these tables and wonder how the naming works here is an example: t69329IISAXDEVHKA8016696528_8403528D2F314601966541DDA2BA3A8A

  • t
  • TableId: Id of the temp table which can be mapped to the right name via the table tableIdTable or view SysTableIdView
  • IIS/Batch: IIS in case in IIS session has created the table or batch when batch created the table
  • ServerId: The id/name of the server that crated the table
  • Guid: Unique identifier

Now I´m interested in your comments because I was a bit shocked that all those temp tables get created in AxDB for environments using Azure SQL.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s