Update - 3/3/2008...
I've received a couple of comments regarding this post. Let me add two points to my original post. First, this was more informational than an actual suggestion for your own system. As I stated below, if you have an index with only the DATAAREAID column selected as a 'Non-Clustered' index, this will work. If you add any other additional columns, then AX will remove it when a synchronization is ran. One caveat to this - I am doing this in RTM, so there is always the possibility of this not working in a later release of AX like 4.0 SP1 or SP2. While I'm not expecting to get any performance gain, I am expecting to silence the Query Tuning Wizard on requesting these indexes. As with any performance-related change, I'll monitor how this change affects our installation and adjust accordingly. If I find no harm in their existance, I'll probably leave them there.
I appreciate everyone's feedback on this posting...
DB
Below is the original post:
As I get further from our Implementation Date, I'm transitioning from my original job of developer to more of a DBA/Business Analyst role. The primary reason for the DBA role is due to performance becoming increasingly more of an issue as our database grows and the once amateur users maturing into more novice users demanding even more resources out of the system.
As I work towards optimizing indexes, monitoring resources, and optimizing X++ code, I notice that SQL balks a lot at tables missing indexes with only the 'dataareaid' field within it. As I've always known you cannot add an index to the AX database directly, likewise, you cannot add an index to AX's AOT with only the dataareaid field. Today, I stumbled upon a way to actually accomplish this. Most of you may be aware of this already but I've personally never seen a blog entry or MSDN entry on how doing this to satisfy SQL statistics. While this may not optimize the system at all, I do get asked by my boss a lot why I haven't created these indexes to satisfy what SQL believes it should have. I do know I haven't seen any issues from implementing these indexes.
If you'd like to see if this will help in your server's performance, please try this first in your test/dev environment before even considering making this modification in your production environment. I'm sure this 'hack' is not supported nor advised by Microsoft.
If you are unfamiliar with the 'Missing Indexes' query, try running the following query against your SQL database:
SELECT TOP 20
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0), avg_user_impact, TableName = statement,
[EqualityUsage] = equality_columns, [InequalityUsage] = inequality_columns,
[Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
This query will give you what SQL believes are the top 20 missing index structures in your database.
If your system is anything like ours, at least 1/5 to 1/10 of these will be Indexes with only the 'DataAreaId' column specified. To add this index in, just go right to the Indexes node within the SQL Table and create an index. Only index on the 'DataAreaId' column and set the index type to 'Non-Clustered'. Click Ok. If you run a synchronization against the table in the AOT, it will not delete the index. If you now rerun the query above you will see the record no longer appears.
You may ask why I even bothered doing this. Well the answer is while the Total Cost and Average User Impact are all relative numbers, over the last several months I have lowered the highest Total Cost to around 10 billion and all of the records between 1 and 10 billion were all missing 'DataAreaId' indexes. After implementing four indexes the SQL statistics said I needed, my highest Total Cost is now only 28 million.
I hope this advice may prove to be helpful or if nothing else serve as a 'Nice to Know'.
DB
