AX 2009 SP1, Parameter Sniffing finally addressed by Microsoft

Most of you have probably struggeled with Parameter Sniffing issues in AX solutions and it finally seems like Microsoft has been able to address this design issue also for AX 2009 SP1! The fix introduced in AX 2012 R2 CU6 has been backported to AX 2012 R0/R1 and now also AX 2009 SP1.Please see AX 2012 R2 CU6, Feature to reduce the effect of Parameter Sniffing.

Please have a look at SQL Server Parameter Sniffing with Dynamics AX, just plain evil. (Redux 5-19-2014) and note that kernel builds after 5.0.1600.2553 (KB2934938) will include a fix that changes the way SQL statements are generated from the AOS by always sending the value for the column DATAAREAID as a literal instead of a parameter (the x-factor).

Also please note that the instructions for updating the value in the table SQLSYSTEMVARIABLES are wrong - the value for the column PARM in the row added to SQLSYSTEMVARIABLES as part of implementing this kernel build, is DATAAREALITERAL (NOT DATAAREAIDLITERAL).

The correct SQL Update Statement to enable literals for DATAAREAID is

UPDATE SQLSYSTEMVARIABLES SET VALUE = '1' WHERE PARM = 'DATAAREALITERAL'

It took me a couple of minutes to realize this when first enabling the DATAAREALITERAL in an AX 2009 SP1 solution for the first time, but a quick SELECT aginst SQLSYSTEMVARIABLES, returned a row containing the value DATAAREALITERAL.

This fix should be evaluated and implemented in every AX 2009 SP1 solution having one company contributing to most rows in shared tables to avoid "Plain Evil", frustration and ad hoc work.

Verification:

Actual value for DATAAREAID is replaced by 'xxx'.

SELECT SUM(A.QTY) FROM PROJINVOICEITEM A,PROJITEMTRANS B WHERE (A.DATAAREAID=N'xxx') AND ((B.DATAAREAID=N'xxx') AND ((B.PROJTRANSID=A.PROJTRANSID) AND (B.PROJTRANSIDREF=@P1)))

SELECT A.AGREEMENT,A.ITEMCODE,A.ACCOUNTCODE,A.ITEMRELATION,A.ACCOUNTRELATION,A.QUANTITYAMOUNT,A.FROMDATE,A.TODATE,A.AMOUNT,A.CURRENCY,A.PERCENT1,A.PERCENT2,A.DELIVERYTIME,A.SEARCHAGAIN,A.PRICEUNIT,A.RELATION,A.UNITID,A.MARKUP,A.ALLOCATEMARKUP,A.MODULE,A.INVENTDIMID,A.CALENDARDAYS,A.CIT_AGREEMENT,A.RECVERSION,A.RECID FROM PRICEDISCTABLE A WHERE ((DATAAREAID=N'xxx') AND ((((((((((RELATION=@P1) AND (ITEMCODE=@P2)) AND (ITEMRELATION=@P3)) AND (ACCOUNTCODE=@P4)) AND (ACCOUNTRELATION=@P5)) AND (UNITID=@P6)) AND (CURRENCY=@P7)) AND (INVENTDIMID=@P8)) AND ((@P9>=FROMDATE) OR ((FROMDATE=@P10)))) AND ((@P11<=TODATE) OR ((TODATE=@P12))))) ORDER BY A.DATAAREAID,A.RELATION,A.ITEMCODE,A.ITEMRELATION,A.ACCOUNTCODE,A.ACCOUNTRELATION,A.CURRENCY,A.INVENTDIMID,A.UNITID,A.QUANTITYAMOUNT,A.RECID,A.FROMDATE

AX 2012 Technical Journal

Just a short message - future entries regarding AX 2012 will be published in a separate blog titled AX 2012 Technical Journal.

Stay tuned for new updates.

AX2009 SP1 secret statistics

I while ago (September 2010) I asked a question based on a simple graph. I thought it was time for an update leaving the question open.

Looking forward (read: AX 2012)

Back in November 2008 I blogged about AX 2009 after attending the Convergence conference in Copenhagen and I was pretty excited about the possibilities. After working with AX 2009 since it was released in 2008, I'm still excited but also more realistic about several aspects of implementing and operating AX. After gaining hands on experience, you discover all the rough edges and you get a feel for the quality of the software with regards to things not woring as expected for instance measured as the number of hotfixes released. All in all it seems like AX 2009 has succeeded in the market.

So whats next? AX 2012 was officially published as the name of the next version of AX (aka AX 6) at the Microsoft Dynamics Technical Conference taking place in Redmund in January. I wasn't able to participate, but based on the information published after the conference, it certainly seems like a huge (giant?) shift both with regards to added and extended functionalty, and technical with some architectural changes as well (like the possibility to replace the classic AOD files in the file system with storage in the database).

The post by Jake at AxaptaSource gives a good summary of what was presented at the conference and it should be pretty clear to everyone that we are talking about the biggest shift in the history of AX and a shift that will have an impact on everyone involved in the value chain. This is of cource in the category of un official information, but still quite trustworthy as far as I can understand.

Modelling is one example that will influence developers and people involved in deploying changes. Much tighter integration to Visual Studio is another. The security system is re designed with roles becoming a real part of the security system (a role in AX 2009 is the same as the Role Center assigned to a user at least within AX) and the requirement of defining a user in Active Directory, seems not to be a requirement anymore (replaced by Claims based security in SharePoint for non AD users). Setup is made easier and system management is prioritzed with a new add in for System Center Operation Manager. The BizTalk adapter is gone and replaced by the WCF adapter (Web Services). The (data) caching machanisms are re designed (much needed in my mind). Include columns are finally supported for SQL Server 2008 togheter with support for full text indexes. Huge performance improvements mainly in the area of CIL execution (Common Intermediate Language). Drag and drop transformation of forms to web pages in Enterprise Portal (indicates big changes in the area of forms in general). Workflow executing as part of the AOS service and shared workflow editor (designer) withVisual Studio 2010. Management through PowerShell. AIF service contracts have changed (possibly influencing the upgrade effort) and less complexity (hey, that sounds like a step in the right direction). Very thigh integration to Excel supporting data entry and synchronization from Excel (big time mental change for a lot of people I think). Big improvements in the area of debugging and batch execution in addition to AOS beeing able to host WCF processes (a real enabler).

Add a module for Public Sector (be aware Agresso and Visma :-)) and 1200 new features in addition to some major rewrites (rumors), and you probably start to see the big picture. I think the AX Community must start preparing for AX 2012 on the mental level since this release will require a shift in most of the ways we have been used to treat new releases of AX.

Virtualization of AOS - VMWare versus Hyper-V

A very interesting thread on the Dynamics AX Community forum regarding virtualization of AOS.

As stated, the only important information missing right now, is a description of the differences in the underlying infrastructure between the VMWare and Hyper-V implementations.

Update:
Te original poster has published information regarding the infrastructure and this information only increases the value of this subject - he has managed to test virtualization of the AOS role under VMWare and Hyper-V on the same (identical) infrastructure! This makes his findings extremely interesting (at least to me) partly because it leaves room for possible reasons/explanations/theories and maybe the biggest paradox - if his findings are true in general, a lot of customers running AOS under VMWare are possibly influenced by decreased performance caused by differences in how the AOS behaves on VMWare. Since most customers most likely are using VMWare, the scope of this subject with regards to the number of customers possibly affected, makes it one of the most interesting subjects in a long time.

It could of course be tightly coupled to the OS and how the OS performs under the two hypervisors (as mentioned as a possible reason in the thread) and I will be light on this until some more research has been done.

Comments are welcome as usual.

AX 2009 and edition of SQL Server Analysis Services

I recently came across an issue when trying to build a SSAS project containing modifications to the standard AX Cubes.

When building or rebuilding the project, I got 1 047 (!) errors with the description "Dimension [x] : Only dimension attributes and attribute relationships can have translations in Standard Edition."

The only solution to this is to (try to) modify the SSAS database in SQL Server Management Studio by some heavy scripting. Or of course upgrade SSAS to Enterprise Edition which I really don't know is legal without licensing Enterprise Edition (I'm trying to clarify this). Anyway it's a valid upgrade path regarding to the information given by Microsoft for SQL Server 2008 R2 (Version and Edition Upgrades) on MSDN.

The system requirements for AX 2009 doesn't contain any information saying SSAS Enterprise Edition is needed and neither does the general SQL Server reqiurements.Until further noticed and official information is given from Microsoft, it's worth having this in mind when deciding which edition of SQL Server to use together with AX 2009.

The only information from Microsoft I have found on this issue, is in a draft document titled "How to add financial dimension to Dynamics AX default cubes" (PDF) from the EMEA Dynamics Support blog on MSDN. On the bottom of page 3 and the beginning of page 4, the following information is given:

This is the only information from Microsoft I have found and despite this beeing a draft document, it clearly states that the system requirements should be updated alternatively that another soultion is provided. From this I understand that you have to backup the OLAP database and restore it to an instance of SQL Server 200x Developer Edition (equals Enterprise Edition in functionality), create a project in BIDS, build and deploy the changes to Developer Edition, and finish doing a final backup - restore sequence back to the production instance of SSAS.

In general, I think most customers will end up designing their own cubes and modifying the needed Role Centers accordingly, to avoid all the struggle with the default cubes.

Experience doing an AX 2009 Complete Install

A short update on my experience from doing a complete install of AX 2009 with dedicated servers for each server role.

First of all, the process went quite well and the only struggle was to get all the Service Principal Names (SPNs) correct including named instances and the SQL Server Browser service on the SSAS server.

Main take aways:

• Spend time on planning and account for some issues related to Kerberos authentication (hard to estimate)
• Follow the latest version of the AX Installation Guide
• Make sure you deploy the latest Cumulative Rollup package for AX (and other services)
• Bookmark the AX EMEA Support Team Blog (very valuable source of information)
• Make sure each component work as expected before you proceed the install and configuration sequence

After installing SharePoint 2010 for the first time, I also have a suggestion for Microsoft regarding AX setup - please have a look at the SharePoint 2010 Products Preparation Tool (shown below) and incorporate this idea into the next release of AX.

AX 2009 Rollup 6 and KB 2278963 (SharePoint 2010)

Finally I'm doing a complete install of Dynamics AX 2009 on a new plattform for a customer! While performing the installation of the core solution, my verified plan (verified by Microsoft) was to install in this sequence:

1. AX 2009 RTM patched with binaries from the KB
2. AX 2009 SP1
3. AX 2009 SP1 RU6
4. Initialize
5. Apply application fix for the KB

Everything went according to plan until step 5. This step failed with the following message:

This update is not applicable to your installation of Microsoft Dynamics AX because wrong version of application files is installed. This update is applicable to version 5.0.1000.52-5.0.1500.3560 of application files.

Setup could not complete the installation.

After checking my sources and discussing with Microsoft without any progress, I decided to install a second application in this sequence:

1. AX 2009 RTM
2. AX 2009 SP1
3. Initialize
4. KB 2278963
5. AX 2009 SP1 RU6

This time everything went according to plan and based on my experience, one of the following are true for this issue:

• KB2278963 application hotfix is forgotten with regards to RU6

or

• KB2278963 is included but not documented as part of RU6 (or RU5)

or

• Wrong version is used when the axupdate process verifies the application version (max is 5.0.1500.3760 - RU6 gives 3761)

The worst thing is that I had to spend extra time and effort doing a new install and 2 compilations in addition to installing a separate AOS and a Client to work on the same versionlevel.

AX 2009 64-bit AOS instance memory usage

I have seen some people asking if the 64-bit "edition" of the AOS kernel is capable of utilizing memory above 2 Gb (search microsoft.public.axapta which by the way is or is going to be retired). The snapshot below is taken from a customer site.

Working Set equal to 2 Gb and Peak Working Set equal to 4,1 Gb - say no more.

Question

A little challenge to my readers:

Can You tell me what I'm trying to illustrate in the graph below?

A couple of tips along the way

• it's closely related to Microsoft and Dynamics AX 2009 SP1...
• the average is almost 69...

Please leave your answer as a comment.

Follow Up on Event ID 110 and 117 (annoying database error)

I wrote about this a while ago and since this still is what I call an annoying database error (noice) and something very common to AX 4.0/AX 2009 solutions, I would like to call on my readers to vote for a change in the AOS kernels (x32/x64) to filter out this information to prevent bombing the event log with errors not beeing errors at all. The voting can be done at Microsoft Connect.

Finally I would like to quote a paragraph in Microsoft KB article 143339:

Applications need to code their error handling routines for SQL Server connections to call SQLError() until it returns SQL_NO_DATA_FOUND, and act on any messages other than the ones that return a pfNative code of 5701 or 5703.

Maybe it's impossible to change this behaviour, but as long as Microsoft don't state this in their feedback on Connect, it's still an issue that I find hard to accept...

AX 2009 - deadlocks

I recently conducted a system review at a customer site and discovered a message totally new to me beeing logged in on all AOS: "Object Server xx: Dialog issued for client-less session 1: Cannot edit a record in LastValue (SysLastValue). User ID: ,AdminUserSetup. Deadlock where one or more users have simultaneously locked the hole table or part of it."

The database at hand was not configured to use Read Committed Snapshot Isolation (RCSI) as recommended by the AX Performance Team. Enabling RCSI on the database solved the issue.

Missing information

I recently was asked about the difference between the 4 alternative values for "Cache Settings" found on the tab labeled "Performance" in the AX Configuration Utility (client) - se example below.

The different options are explained directly in the GUI, but I would like to know how this mechanism is implemented (technically). I have searched all possible sources (formal and informal) without finding any information at all. It's not unusual to hit "blind spots" like this regarding formal documentation around Dynamics AX, but it should not be necessary to open a support case every time this happens.

Anyone having a reference or are willing to share some experience regarding the effect of this setting?

Comments

I have changed the possibility to post comments from anonymous (all) to Registered Users. Reason: loads of spam comments in my mailbox.

Event ID 110 and 117 (annoying database error)

I observe the issue described below in both AX 4.0.x and AX 2009 SP1 x solutions running SQL Server 2005 and 2008 (the version of SQL Server Native Client is the same). This is also reported by others in several Communities like microsoft.public.axapta (last one beeing a post by Mike titled "AOS errors" from February 25 2010 where one suggestion is to syncronize the SYSLASTVALUE table).

Eventlog, application

#1 Event ID 110

Object Server 01: Dialog issued for client-less session 1: Cannot edit a record in LastValue (SysLastValue). User ID: , AdminUserSetup.
The SQL database has issued an error.

#2 Event ID 117

Object Server 01:
The database reported (session 1 (-AOS-)):
[Microsoft][SQL Native Client][SQL Server]Changed language setting to us_english.
The SQL statement was:
"UPDATE SYSLASTVALUE SET VALUE=?,RECVERSION=? WHERE (((((((USERID=?) AND (RECORDTYPE=?)) AND (ELEMENTNAME=?)) AND (DESIGNNAME=?)) AND (ISKERNEL=?)) AND (COMPANY=?)) AND (RECVERSION=?))"

I don't know if this has any impact on the solutions at all and I have also found some information on Partnersource describing this as "noice". And I also find it a little bit hard to understand why a syncronization of the system table SYSLASTVALUE should be needed since the real error message in fact is stating "Changed language setting to us_english.".

The reason for posting this is that I find it a little bit hard to understand why something is logget as an error situation if this is "just noice". AX is in general bad at categorizing events in event log application, but I would like to share some experience with others regarding this. Since it's reported from the ODBC layer it could be related to settings like default language for the DBO user (aka AOS Service Account) or inproper settings for Regional Settings for the same Service Account. This is a couple of my own teories left to verify, but bottom line I find it hard to be "just noice".

Hopefully we can start a good discussion around this and bring the issue forward to the system vendor instead of reporting it individually (perhaps a bigger impact).

Update March 10 2010:
For those of you not reading the comments, I would like to point out that Chris pointed to an important KB-article from Microsoft. After looking a little bit more at the details (Googled for NativeError 5703), I found another reference on MSDN. Quote:

The SQL Server ODBC driver returns SQL_SUCCESS_WITH_INFO on a successful SQLConnect, SQLDriverConnect, or SQLBrowseConnect. When an ODBC application calls SQLError after getting SQL_SUCCESS_WITH_INFO, it can receive the following messages:

5701—indicates SQL Server initially putting the user's context into the default database defined at the server for the login ID used in the connection
5703—indicates the language being used on the server

If either the ODBC data source has a default database specified or the application specified the DATABASE keyword on SQLDriverConnect or SQLBrowseConnect, there will be a second 5701 message that indicates the user's context has been switched to the database requested.

My conclusion is that the AOS kernel treat every error raised from the ODBC layer as an error. If Microsoft could implement a change to filter out NativeError 5701 and 5703, a lot of confusion could be saved and not at least a lot of time investigating this issue could also be saved.

Until further notice, this is my conclusion.

Again, thank you Chris for leaving this valuable information.

Update #2 March 11 2010:

I have today posted this as a suggestion on the MS Dynamics AX Suggestions Home page at Microsoft Connect. Please sign in at Connect and vote this up if you find this issue to be a valuable improvement.

Update #3 May 11 2010:

The EMEA Dynamics AX Support team recently released a post giving another explanation. It can be found here.

AX Load Balancing

A tree tiered architecture has been around since AX 4.0. In AX 2009 you have 3 possible AOS configurations:

1. Single AOS instance(s)
2. Load Balanced AOS instances without a dedicated load balancer
3. Load Balanced AOS instances with a dedicted load balancer

After experimenting a little bit with all of the possible configurations, I find it a little bit difficult to separate configuration 2 and 3 above. The only difference is the presence of one (or several) AOS instance(s) defined as dedicated load balancers only handling the load balancing functionality (no end user sessions) for AX clients that whish to log on to AX (handshake). So the big question is when to use a configuration with a dedicated load balancer. I can't see any reason or benefit other than limiting the need to adjust the client configurations when AOS instances move out and in of the cluster. The only benefit deploying a dedicated load balancer is that this will be the only AOS instance referenced by the clients and that this makes the client configuration more static compared to configuration 2 when every AOS instance in fact both is acting as a load balancer and handling client sessions.

And information from Microsoft states that a dedicated load balancer only is needed when you have more than 4 AOS instances in the same cluster.

All combinations in the client configurations will work (no difference wheter you only reference the dedicated load balancing instance and/or all AOS instances).

Any comments around this matter will be appreciated.

Short note on AX and database mirroring in SQL Server 2008

The last days, I have been working with a customer running AX 2009 and SQL Server 2008 SP1 Standard (x64) on Windows Server 2008 SP2. The solution suffered from general performance issues that possibly could have a lot of sources. As usual my attention was around SQL Server and this time I started looking at Wait Stats which showed a waste amount of waits related to mirroring. After some Googling, I was a little bit confused about this beeing normal or not. The nature of database mirroring could in fact result in high Wait Stat values since the processes involved, is mostly sleeping (suspended). But I choosed to consider this as a possible source since the nature of database mirroring in my oppinion, does'nt fit the nature of AX as a classic OLTP application. And since disabling database mirroring is an isolated and low risk operation, we choosed to stop mirroring for all database (around 10). After this, the overall performance increased and the customer reported this almost immediately. It's a little bit early to conclude, but the initial responce seems pretty promising.

Minor observation installing AX 2009

Some time back, I did a QA run on one installation suffering some performance issues. The consultant originally installing AX choosed to use the installer to create the database (shown in the picture below). Personally I prefer to create the database manually, both to get control over every aspect and it usually don't take more than 30 minutes to get everything in place manually. The solution at hand was running SQL Server 2008.

While looking into the database configuration (sp_helpdb), I noticed that the compatibility level was set to 90 which corresponds to SQL Server 2005. At this stage, I asked the consultant doing the installation why he had choosen to set the compatibility level to 90 and he then answered that he always used the AX installer to create the database.

Without testing this option when installing AX, it seems like Microsoft has provided a template database beeing attached to the specified SQL Server instance by the installer in the security context of the setup user. I guess the installer asks for some information like collation, file locations etc. but compatibility level keeps untouched.

Compatibility level is a database level configuration option and it is used by the database instance to decide which functionality the database instance should "activate" for each database. It's mostly used to provide backwards compatibility during migration of databases, but it could also impact the ability to utilize new features.

A good reference can be found at MSDN and compatibility level could possibly be a source for some time consuming investigations. It seems like compatibility level = 90 is OK even for an AX database running on SQL Server 2008, but I don't see any reason for not setting it to 100 to make sure all new features in SQL Server 2008 is enabled for the AX database.

Or always create the database manually using the system database model as the template (will in most situations match the version of SQL Server).

A new year approaching

With another week to go and the Cristmas hollidays coming, it's time to reflect on the past and coming year. I think 2009 was a good year for AX 2009 despite the product beeing released in 2008. The main reason for 2009 beeing a good year for AX 2009 is mainly based on the Gartner report positioning AX 2009 the number one (#1) leader in the magic quadrant for what we call SMB here in Norway. This could of course be marketing hoopla, but if you read the report, Gartner pays special attention to "Microsoft delivering on their vision". Personally I find this interesting and I suggest that every partner study the current Roadmap and Statement of Direction (SOD) to see what Microsoft is planning (visionary) for AX in the future. In my mind, AX 2009 did'nt bring a lot of news on the techology side (the new batch framework and the support for UTC is perhaps the most important improvements), but overall MS managed to position AX as a real challenger and maybe also a winner in the combat against SAP and Oracle/PeopleSoft.

Based on the number of hotfixes released for AX 2009 and SP1, MS still has some challenges regarding quality. Maybe a slowdown in the release cycle would be a good advise and maybe also broaden the early adoption program to gain some more experience from the field. Again this is really not special to AX, since we have to remember that all software is manufactured by humans and no human is free of errors. All in all I think AX 2009 was a great step forward at least with regards to technology and architecture, since the product is well positioned to compete with the biggest rivals even in the upper, right quadrant. Finally "MS got rid of most of (every?) not so industry standard implementation as expected" and lifted the product up to the MS level of integration (still some ground to cover, but yet greatly improved).

So it's exiting times generally and with regards to AX. I'm positive about the year to come and also the next decade. From the Roadmap and SOD, I read a steady growth both vertically and horizontaly with regards to functionality. We are already seeing some evidence of this with MS buying verticals from partners (like the POS vertical from a Danish world wide partner financing? a rather special twist in buing a highly specialized ERP solution called Guideix A/S). The future story of this evolution of one of my previous employers, will be very interesting to follow. Driven by market, customers or product/technology? Who knows...

Anyway I'm optimistic about the future of AX and I seriously meen that AX 2009 brought AX closer (really close) to what the product has been marketed as since the beginning (Damgaard back in 1998 - more than 10 years ago). At the same time I have to admire the Damgaard brothers for the introduction of a true 3-tired solution (was it 2.5?) that MS now has adopted and brought forward to a pure 3-tiered solution with no other choices. Add the rather annoying AOCP beeing replaced with MS RPC in AX 4.0 and you get the picture.

One final message to all x++ developers out there, is to take the time to study and understand the difference between the different caching schemes available in AX (look at the DEV III course documentation or Inside AX 4.0/2009 book). This is key knowledge to utilize the 3-tier architecture in AX 4.0 and 2009 (also valid for earlier versions when running in 3-tire thin configurations) and at the same time, rather complicated. From my experience, this is an area that get far to less attention and that has a big potential for optimizations (and performance gains).

Without digging deeper into the philosopical area, I would take the opportunity to wish my few (but valuable) readers, a happyy Christmas and an interesting new AX year (and decade)!

See you in 2010! So long...

DAS or SAN for SQL Server part II

I thought a follow up was adequate for this one. After looking for some more views, oppinions and recommendations, I found the following posts (3 part) on the blog for the Microsoft SQL Server Development Customer Advisory Team:

Deploying SQL Server with SAN #1
Deploying SQL Server with SAN #2
Deploying SQL Server with SAN #3