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.