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.

Patching Remote Desktop Connection Manager for CVE-2020-0765

On march 10 2020 a security vulnerability was published for Remote Desktop Connection Manager (RDCMan). At the same time Microsoft removed the download from their site pushing people towards the use of MSTSC or Universal Remote Desktop client. If you like having multiple connections with group based inheritance (as RDCMan does), you’re out of luck with those alternatives.

The security vulnerabilty in RDCMan isn’t all that bad actually. You need to open a .RDG file that was modified by an attacker which could then expose data via an XML external entity attack.
Since most of us use a local .RDG configuration file this sort of attack is pointless as someone who can modify that file already has access to the filesystem.

That being said; If you want to use RDCMan but can’t because of the vulnerability then you still have the option to fix the issue yourself.
In .NET applications this can be achieved by setting the XmlResolver on the XmlDocument type to null.

Luckily RDCMan was written in .NET which makes it rather easy to make this modification with a tool like dnSpy

Open the treeview to RDCMan\RDCMan.exe\RdcMan\RdgFile\OpenFile(string) : FileGroup
Right click the OpenFile(string) method and choose Edit Method (C#) from the popup menu
The original unmodified code

And make the following changes

Add XmlResolver = null to new XmlDocument and new XmlTextReader

After adding the two XmlResolvers hit the Compile button. After which the changes should show up in the main window of dnSpy.

The changes in the main dnSpy window

Now we just need to save the changes to RDCMan.exe

To save the changes to the RDCMan.exe go to the File menu and click Save Moduleā€¦
Leave options as is and hit OK

Thats it, you should* (see note 4) now have a CVE-2020-0765 patched RDCMan.exe

Some notes:
1) You should still be on the lookout for a RDCMan alternative as this software is no longer maintained and might possibly no longer work with newer versions of RDP or new exploits could emerge.
2) This modification is purely for educational purposes, just to see if it could be done easily.
3) Do this at your own risk, no guarantee is given.
4) It’s a theoretical patch; No testing with a malicious RDG file was done.
5) Although the Remote Desktop Connection Manager dowload was removed from the Microsoft site, The Wayback Machine still has it.

Reduce long synchronization times

When deploying a modelstore one of the steps is to synchronize the database. If the modelstore contains new or altered indexes synchronization times can rapidly increase causing long wait times before the system is up and running again. Synchronization times can drastically be reduced by increasing the max degree of parallelism option (maxdop) of SQL server, this enables SQL server to use multiple cores / processors for one task. On a SQL server that hosts the AX database however this value is usually set to 1 as that’s the best practice.


18 minutes might not seem too long, just wait and see what happens when an index is altered on a table that contains a couple of 100 million rows.

Standard AX creates indexes as shown below (I’ve never seen AX alter an index, it just seems to drop and create a new index):

With the create index statement above you get a SQL Server cpu utilization like this:

Index creation can benefit from parallelism by adding the maxdop option like below:

The creation time is significantly shorter at the cost of higher cpu utilization:

How to increase maxdop just for synchronization purposes

AX 2012 has some index configuration options available in the System administration / Periodic / Database / SQL Administration form.

This would be the place to expect the option to increase MAXDOP, unfortunately this option is not available in standard AX (2012 RTM). Luckily it can be added by customizing the following objects.

Download the xpo here: SysSqlSetup_MaxDop

After importing the xpo, the table and index options screen has the additional MaxDop option

Enabled options in this form are used by the standard AX synchronization process. Next time when deploying a modelstore you should notice an improvement in the synchronization process (if any indexes were added or modified).

Dynamics AX 2012 RTM on SQL Server 2016

In case you were wondering whether AX 2012 RTM will run on SQL Server 2016, have a look at this video:

The AX kernel version used is 6.0.1108.8690. SQL Server 2016 was installed with all default options. The database was restored from a SQL Server 2008r2 AX database. This database has compatibility level 100.
While this seems to work fine, at the moment this is probably not a supported configuration.