While the first day of Convergence, Partner Day, was slow for a customer like myself, it will pick up tomorrow. This has been my first Convergence attendance so of course like all first-timers, I've been wowed my the amount of cash and effort Microsoft has put into this conference. Dynamics AX 2009 is the focal point on the AX side of the product offerings and judging by the looks of the amount of Marketing material, hand-on labs, and sessions Microsoft is providing, they're releasing it the 'right' way compared to my experience with the 4.0 launch in 2006.
One bit of information that I've found a strong marketing campaign towards during Convergence is Dynamics Communities. I've been a member of the Finance Community for a while now but a 'Sales and Marketing' and 'Customer Service' community has recently launched. I encourage everyone who is currently not a member of at least one of these communities to get involved, especially if you do not have a support contract with either your partner or Microsoft.
Clicking on the link below will take you directly to the registration page. When registering, I kindly ask that you place my Dynamics Community screen name as your referral code, davidbowles.
https://www.dynamics.com/UserProfile/HaveLiveId.aspx
DB
Tuesday, March 11, 2008
Friday, March 07, 2008
Convergence 2008 - Orlando
It's Friday finally! I haven't posted anything about Convergence, but better late than never. I will be at Convergence for anyone who is interested in meeting while I'm there. To help facilitate this, I have setup a Convergence Connect profile. While a portion of my time will be working at the technical services area of Convergence, I'll have other time to attend sessions and socialize as well.
My main focus at Convergence is SQL Performance and PerformancePoint Server. Those are the two areas I'm hoping to make great strides in by mid-2008.
While I have had many emails and comments asking about the results of the poll I was attempting to take on AIF usage, I have never received any responses on usage. I'm not sure if this was due to not having time or literally no one who reads my blog using it, but if you're of the earlier persuasion, please come by and talk to me for a few minutes. I'd really love to hear your success stories or otherwise in AIF.
Look forward to seeing everyone there.
David Bowles
My main focus at Convergence is SQL Performance and PerformancePoint Server. Those are the two areas I'm hoping to make great strides in by mid-2008.
While I have had many emails and comments asking about the results of the poll I was attempting to take on AIF usage, I have never received any responses on usage. I'm not sure if this was due to not having time or literally no one who reads my blog using it, but if you're of the earlier persuasion, please come by and talk to me for a few minutes. I'd really love to hear your success stories or otherwise in AIF.
Look forward to seeing everyone there.
David Bowles
Monday, March 03, 2008
Do and Don't Lists
As an added service to you the developer or administrator, I have created two lists named "Do" and "Don't", respectively. This is a quick hit list of things that you should at bare minimum entertain the idea of researching the possibility of implementing or definitely not doing at all.
You will see these lists in the right margin at the end of the 'widgets'. If you have a personal lesson learned that you think should be in the list, please send me an email.
DB
You will see these lists in the right margin at the end of the 'widgets'. If you have a personal lesson learned that you think should be in the list, please send me an email.
DB
Thursday, February 28, 2008
Creating 'Permanent' Indexes on DataAreaId in Dynamics AX
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
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
Tuesday, January 01, 2008
Happy New Year!
Along with a new year comes new opportunities, new resolutions, and new challenges for everyone to realize.
My challenge, opportunity, and resolution for the new year is to update this blog more often. Let's see if I can actually follow through...
I have some new information to post regarding AIF queue management that I've known for a few months now but just have not made the time to formally post.
As with most documentation that you see for setting up a batch server, the instructions either state or imply to run both the AOS service as well as the batch client on the same dedicated server. This may at first seem like a common-sensical notion since the Batch Client, the AOS Service and the data storage all reside on the same system and will ultimately aide in reducing any network bottlenecks of large amounts of batchable transactions running across the network. However, this is not the case because the bottleneck of having both components running on the same system is the memory. Due to the 1.7GB limitation of 32-bit applications, the OS, the AOS, and the client are all contending for the available memory.
Depending on the amount of transactions that you have processing in an hour, or even a day, separating the processes out onto separate servers may not be necessary for you. In our case, we have approximately 1-500 messages measuring between 30-50KB that pass through our AX system via AIF every hour. As a result of the number of messages we process, our 'All-In-One' setup proved to be quite the bottleneck and resulted with us constantly getting 'Out of Memory' errors in our AIF queue. At one point, we had to filter out all messages larger than 200KB simply because even after freeing up all memory available, we still were unable to process those messages and import them through custom file I/O operations. After testing and working Microsoft, we found that separating the AOS Service on a dedicated server and running the client(s) on a separate dedicated server gave us our best potential performance and freed up the client to contend for memory only against the OS and the same with the AOS service.
To further increase our AIF performance, we found that instead of running all four AIF batch processes together in a single client session, separating each of the four out into separate client sessions increased performance. As a result, you are not limited to the four processes being executed in a sequential order but instead they can all three execute independent and even concurrently. Although the issue still exists that the four processes are related and dependent of each other, however, messages that are ready to be processed at the next execution of the batch are not held back from being processed.
For example, the four AIF processing classes are:
My challenge, opportunity, and resolution for the new year is to update this blog more often. Let's see if I can actually follow through...
I have some new information to post regarding AIF queue management that I've known for a few months now but just have not made the time to formally post.
As with most documentation that you see for setting up a batch server, the instructions either state or imply to run both the AOS service as well as the batch client on the same dedicated server. This may at first seem like a common-sensical notion since the Batch Client, the AOS Service and the data storage all reside on the same system and will ultimately aide in reducing any network bottlenecks of large amounts of batchable transactions running across the network. However, this is not the case because the bottleneck of having both components running on the same system is the memory. Due to the 1.7GB limitation of 32-bit applications, the OS, the AOS, and the client are all contending for the available memory.
Depending on the amount of transactions that you have processing in an hour, or even a day, separating the processes out onto separate servers may not be necessary for you. In our case, we have approximately 1-500 messages measuring between 30-50KB that pass through our AX system via AIF every hour. As a result of the number of messages we process, our 'All-In-One' setup proved to be quite the bottleneck and resulted with us constantly getting 'Out of Memory' errors in our AIF queue. At one point, we had to filter out all messages larger than 200KB simply because even after freeing up all memory available, we still were unable to process those messages and import them through custom file I/O operations. After testing and working Microsoft, we found that separating the AOS Service on a dedicated server and running the client(s) on a separate dedicated server gave us our best potential performance and freed up the client to contend for memory only against the OS and the same with the AOS service.
To further increase our AIF performance, we found that instead of running all four AIF batch processes together in a single client session, separating each of the four out into separate client sessions increased performance. As a result, you are not limited to the four processes being executed in a sequential order but instead they can all three execute independent and even concurrently. Although the issue still exists that the four processes are related and dependent of each other, however, messages that are ready to be processed at the next execution of the batch are not held back from being processed.
For example, the four AIF processing classes are:
- AIFSendGateway
- AIFReceiveGateway
- AIFInboundProcessingService
- AIFOutboundProcessingService
In order to import a message into AX, AIFReceiveGateway must execute to pull the message into the AIF Queue and the AIFInboundProcessingService must execute to process and persist the data to the database. Likewise, to export a message, AIFOutboundProcessingService must retrieve the data from AX package it in the form of a message and write it to the queue before the AIFSendGateway can pick it up and write it to a message. As a result, spliting up the jobs into different batch clients will allow you to make the AIF message passing process closer to a 'real-time' process.
Finally on a separate note, I have just completed reviewing a new book on Dynamics AX that will soon be published. Once the book is officially published, I will post a link to it.
DB
Subscribe to:
Posts (Atom)