Stopping an AOS takes ages due to huge number of temp tables

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));
}
One tempdb table was created after running the job above

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

No, only one tempdb table for RAssetPurchTrans

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));
}
This image has an empty alt attribute; its file name is image-6.png
Still just one tempdb table

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));
}
Bingo, 10 tempdb tables were created

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.

Leave a Reply