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
3 comments:
Whenever I synchronize the database either from the AOT or from the Administration -> Periodic -> SQL Administration form any index that I create directly in the database are being dropped.
You said that yours did not. -What AX version are you using?
I have tested with AX 4.0 SP1 and SP2.
From the "trcAxaptaSync.log" log file on the AOS server I get:
User ID: msr
Time: 00:19:34 02-03-2008
Version: Microsoft Dynamics 4.0 (Build number 2503.56)
Database: Microsoft SQL Server
Operation: DROP INDEX CUSTTABLE.DataAreaIdx
User ID: msr
Time: 00:19:34 02-03-2008
Version: Microsoft Dynamics 4.0 (Build number 2503.56)
Database: Microsoft SQL Server
Operation: 'CUSTTABLE' (ID #77): INDEX(DROP)+INDEX(CREATE)
Duration in Ticks: 125.,
This clearly shows that the index is being dropped.
By the way, my result from the 'Missing Indexes' query is much like yours, so being able to add the DataAreaId indexes and keep them during synchronization would be a huge performance gain for us.
Best regards
Mikael Sorensen
I'm really not sure that this will have any performance impact. You may have access to queries that will tell you what indexes are being used and I really doubt that these indexes will show up.
If you take a look at the Include columns, these are columns that are recommended as columns to include in the index and typically cover all the indexes - which resolves to a covered index scan, not going to the table itself - may also be a good indication of tables with a missing clustered index. So to comply with the suggestions, you'd have to create an index on DataAreaId and have all the columns in "Include Columns" as included columns in the index, thereby creating a new sorted version of the table.
The fact that you get less total cost from this query, probably stems from the fact that this is only a partial view of performance problems in your database. You still have this cost, you just don't see it in these DMVs.
The more interesting statements are those that have NULL in "Include columns" (or very few). There you can create an index with these values and that should indeed speed up your queries (be aware though, that index maintenance is relatively expensive - especially on large tables).
Just to be clear - you will see improved select query performance if you create the index WITH all the columns in the "Include Columns" field as included columns. Then you are, however, bloating your database and keeping a second copy of your table.
Best regards,
Eyvindur
Hello Dave
I tried your query against our Axapta SQL and it returns a lot og optimizing indexes...
i then tried against another SQL server but it returns an empty set ?
how do i enable the SQL server to save those statistics ?
thanks
Post a Comment