Recently we had an AOS (AX 2012 R3) which took more than 20 minutes to shut down while it normally should take a minute or two. When checking SQL we saw that the AOS was dropping a large number of tempdb tables (about 450000).
While we do have a reasonably large database and do millions of transactions each day, having almost half a million tempdb tables seems a bit excessive.

So, where do these temp tables come from?

Looking at the tables in tempdb reveals the following:

• they start with t
• then a number
• followed by an _
• and a guid like string
  e.g. t7461_15722EEBE3404D5B80A6056D701084FD

After comparing some table schemas we determined that the number between the “t” and the “_” is actually the AX table ID. Cool, so now we can run the following query to to get some more insight on the tables used (replace development_model with your model database name)

select 
	ttables.name,
	cast(SUBSTRING(ttables.name, 2, CHARINDEX('_', ttables.name) - 2) as int) tableid,
	me.name
	from tempdb.sys.tables ttables with (nolock) 
	left join development_model.dbo.ModelElement me on me.AxId = cast(SUBSTRING(ttables.name, 2, CHARINDEX('_', ttables.name) - 2) as int) and me.ElementType = 44 
		where ttables.name like 't%[_]%' 
		order by ttables.create_date desc

Some tables have multiple tempdb copies, use the following query to find the most frequent occurrence of a table in tempdb (replace development_model with your model database name).

select 
	me.name, 
	count(1) as occurrences
	from tempdb.sys.tables ttables with (nolock) 
	left join development_model.dbo.ModelElement me on me.AxId = cast(SUBSTRING(ttables.name, 2, CHARINDEX('_', ttables.name) - 2) as int) and me.ElementType = 44 
		where ttables.name like 't%[_]%' 
		group by me.name order by count(1) desc

Now, why did AX create 2710 temp table copies of the same table?

Lets see what happens when we run a job that selects a single record from the RAssetPurchTrans table (after dropping all tempdb tables).

static void testTempdb(Args _args)
{
    boolean     testTempdb;
    
    testTempdb = (select firstonly RAssetPurchTrans).RecId != 0;
    
    info(strFmt("test is %1", testTempdb));
}

What if we run that job a second time? Will it create another tempdb table?

What if we run it 10 times in a for loop?

static void testTempdb(Args _args)
{
    boolean     testTempdb;
    int         i;
    
    for (i = 1; i <= 10; i++)
    {
        testTempdb = (select firstonly RAssetPurchTrans).RecId != 0;
    }
    
    info(strFmt("test is %1", testTempdb));
}

Well, what if we run that job within a transaction?

static void testTempdb(Args _args)
{
    boolean     testTempdb;
    int         i;
    
    ttsBegin;
    for (i = 1; i <= 10; i++)
    {
        testTempdb = (select firstonly RAssetPurchTrans).RecId != 0;
    }
    ttsCommit;
    
    info(strFmt("test is %1", testTempdb));
}

So, if we’re running inside a transaction AX will create a new tempdb table each time we select a record from the RAssetPurchTrans table.

But why is a tempdb table even created?

While testing this with a new table I created myself, no tempdb tables were created at all. Well, the table I created doesn’t have a configuration key associated with it, while the RAssetPurchTrans table has the RAsset configuration key associated which is disabled.

According to Microsoft docs this is normal/expected behaviour

Avoiding / solving the issue

Since we’re running a query on a table that is disabled, we might as well check whether the configuration key is enabled so we can skip the actual query.

static void testTempdb(Args _args)
{
    boolean     testTempdb;
    int         i;
    
    ttsBegin;
    for (i = 1; i <= 10; i++)
    {
        if (isConfigurationKeyEnabled(configurationkeynum(RAsset)))
        {
            testTempdb = (select firstonly RAssetPurchTrans).RecId != 0;
        }
    }
    ttsCommit;
    
    info(strFmt("test is %1", testTempdb));
}

By doing it this way no tempdb table will be created for RAssetPurchTrans.