Database Engine Tuning Advisor (DTA) gotcha with AX and indexes

Performance tuning AX SQL queries seems to be the norm these days since the release of AX 2012 and today I was bitten by my efforts to reduce a query's execution time.

I often use Microsoft SQL Server Profiler to collect information about database activity, in order to detect performance bottlenecks. Sometimes I discover something blatantly obvious that only takes a quick tweak of the query or new index to make right. Other times when the answer is less obvious or there are far too many results to analyse manually, I opt to see what recommendations the Database Engine Tuning Advisor (DTA) will come up with. To date, this has served me well and I haven't had any issues, until today.

At some point after finishing my analysis on a test environment under load, a full database synchronisation was performed as part of a deployment, and it failed with the following error message.

SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the index 'CUSTTABLE._dta_index_CUSTTABLE_6_1915921947__K94_K93_K83_K96_K152_K1_K149_2_3_4_5_6_7_8_9_10_11_12', because it does not exist or you do not have permission.

SQL statement: DROP INDEX CUSTTABLE._dta_index_CUSTTABLE_6_1915921947__K94_K93_K83_K96_K152_K1_K149_2_3_4_5_6_7_8_9_10_11_12

Here's the screenshot.

So from a little research, it seems that if the DTA fails it does not clean up hypothetical indexes, and leaves them in the sys.indexes table of the database you are tuning.

As the code that generates the errors is in the AX kernel, I'm not sure exactly why it detects these indexes however I hypothesise that AX is doing a look up on the SQL system table indexes to get a list of indexes, that are then compared to those on the AOT. AX will then attempt to generate DROP INDEX SQL statements to clean them up. So why then wouldn't it just drop the indexes and be done. It looks to me like there is a size limit on the number of characters for either the SQL statement that AX generates or part there of. As such, the complete index name is truncated and the index that AX is trying to drop cannot be dropped as it truly does not exist.

As it turns out, this is a known 'feature' of the DTA and the deletion these also documented by Microsoft.

To fix the issue, I wrote the following SQL query to generate my DROP INDEX statements:

SELECT 'DROP INDEX ' + i.name + ' on [' + o.name + ']'
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE i.is_hypothetical = 1
  AND o.type = 'u'

You may find also that the DTA has also left behind some hypothetical statistics that need cleaning up. Here is the SQL query to generate the DROP STATISTICS statements:

SELECT 'DROP STATISTICS [' + object_name(i.[object_id]) 
        + '].['+ i.[name] + ']'   
FROM sys.stats i 
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 
  AND i.[name] LIKE '_dta%' 
  AND user_created = 0

Run these queries in SQL Server Management Studio (SSMS) against your AX database, copy the output into a query window in SSMS and execute. You may wish to review these before actually executing, that is why I prefer to generate the code first and then run, rather than run a script that runs dynamically generated code.

Now run the database synchronize in AX and you should no longer get errors.

AxBuild.exe: A new build tool for AX 2012 R2

With the release of cumulative update 7 (CU7) for AX 2012 R2 comes a neat little build tool, AxBuild.exe, which has the potential to significantly improve the performance of the X++ compilation.

Located at %Program Files%\Microsoft Dynamics AX\60\Server\<AXAOSName>\Bin\ AXBuild.exe is a command line tool with just one command, xppcompileall that runs a full X++ compile for the Dynamics AX model store.

Compiling on the server tier, AxBuild starts several temporary AOS processes (called workers) that run concurrently and complete parts of the full compile. By default the number of workers is based on the number of available processors.

What excites me most about this advancement for AX is the opportunity it gives for automating builds (and even deployments). While this is not new for AX, a significant decrease in compile time makes it more practical for deploying built code quickly and provides a more compelling reason to use tools such as Team Foundation Server (TFS) as a means to control the release of code changes across environments. Another step further away to the sometimes tedious and error prone task of xpo deployment.

For a complete overview of AxBuild.exe, visit http://msdn.microsoft.com/library/d6da631b-6a9d-42c0-9ffe-26c5bfb488e3.aspx