Working with many AX solutions over the years, I have seen Parameter Sniffing in many solutions and the effect of "bad Execution Plans" in SQL Server, can be rather "not so nice" to all end users.
Most of the solutions I see have many companies or Legal Enteties (LE) as they are refered to in AX 2012, but the distribution of the data in shared tables, are very commonly skewed where one company or LE typically allocates 80 to 9x percent of the rows while 8 - 10 companies/LE contribute to the rest of the rows in individual shared tables. Microsoft introduced PARTITION as a new way of isolating companies or LE in AX 2012 R2 and this led to another column in most tables contributing to the parameter sniffing issue. AX 2012 R2 is designed to have several partitions and many LE each representing a balanced data distribution (each partition and Company/LE having roughly the same number of rows), but in real life, this is very seldom what customers end up with.
I would say that Parameter Sniffing is not a phenomen (it's in fact a SQL Server by design behaviour), but it tend to hit AX solutions very hard when the design criteria is not met. The x-factor lies in the fact that Microsoft utilizes Parameterized Queries heavily to make sure every Query is executed "the same way" (also the main reason for recommending MAXDOP = 1) each time and to reduce the number of SQL Compiles and Recompiles (has a small CPU cost). But the downside is that the actual value for the parameter when the statement reaches SQL Server for the first time (or the SQL Server is experiencing mamory pressure with flushing of execution plans), must represent "an average" partions and/or company/LE. The x-factor can simply be caused by the the first user logging on to AX the morning (early bird) after the AOS instances and/or SQL Server has been restarted and representing a small company with regards to the number of rows in shared tables, can "trick" SQL Server in generating a execution plan where SQL Server find it most efficent (based on the cost) to perform a table or index scan. When an user representing the largest company (number of rows in the underlying tables) implicitly reuses the same execution plan, all sorts of bad performance reports starts popping up. Looking at the SQL Server you will probably notice constantly high CPU utilization and digging a little further, You will probably identify one or several statements consuming much CPU time (some of then probably also beeing utilized by API Cursors).
Microsoft introduced a new feature in AX 2012 R2 CU6 to overcome this vulnerability and it's a kernel feature that's turned off by default. The feature is well described in the article Overcoming parameter sniffing issue in Microsoft Dynamics AX 2012-R2 – CU6 written by the AX Performance Team.
I have been aware of this rather important change (results in values for the columns PARTITION and DATAAREAID beeing always sent to SQL Server as literals or actual values instead of parameterized values), but I tend to await activation until I actually see (have proofe of) that a solution is impacted by Parameter Sniffing. Last week I saw proof of this happining in a busy solution focused around Sales Order processing and after activating the changes in the table SYSGLOBALCONFIGURATION followed by a stop - start sequence of the AOS instances, we imediately saw SQL Server generating execution plans using literals for the columns described which again "calmed" the solution. At the same time the number of SQL Compiles and Recompiles increased somewhat, but this was expected as the number of Execution Plans for each Parameterized Statement will increase by the product of the number of Partitions and the number of individual companies/LE utilizing a spesific module in AX.
This is perhaps one of the most important changes Microsoft has introduced and I have not seen any other solution improving (trace flags, force literals keyword etc.) or near eliminating this issue until this feature was introduced. Everyone involved in implementing AX 2012 solutions should be aware of this and be able to utilize this feature when needed. I say AX 2012 since MS stated that this change was expected to be back ported to AX 2012 (I don't know the status), but it's confirmed in AX 2012 R2 CU6 and later binary builds. Customers still utilizing AX 4.0 or AX 2009, will probably never benefit from this feature.
Many factors play a part in this story such as SQL Server Statistics, Max Degree of Parallism and API Cursors, but this is the single most efficent method of helping SQL Server generating the most representative (balanced) Execution Plans for AX based on my experience.
I saw Axapta 1.5 back in 2001 and have been working with Axapta 2.5 and Axapta 3.0 in the period 2002 to 2005, AX 4.0 and 2009 from 2007 and of course AX 2012 R0/1/2/3 from 2011.
My professional record ranges from Development (1994 + 5 years) to Implementation (1999 + 6 years) and Application Management (2005 + 2.5 years). I currently work within the Technology area in a Norwegian subsidiary of a large International IT company specialising in Business consulting, Systems Integration and IT Outsourcing Services
Axcess Nordic has delivered solutions from Verint Systems since 2011. Verint systems offer a wide range of solutions for the modern customer centre. Tools which make work more efficient (e.g. workforce management and communication centres), improve customer communication (e.g. Quality Management), deliver operational insight (e.g. Speech Analytics) and regular communication systems.
Verint Systems is a global market leader within the area typically referred to as WFO (Work Force Optimisation) and WFM (Workforce Management) and more than 10,000 companies and organisations in more than 180 countries – including 80% of Forbes Fortune 100 companies – use solutions from Verint Systems.
According to Gartner Magic Quadrant as well as Forrester Wawe Verint is among the 3 leading WFO players.
Axcess Nordic sells and implements solutions on behalf of Verint Systems in the Nordic countries and has status of Platinum Partner (highest possible partner status), which requires an extensive knowledge of the company’s solutions.
Calabrio offers en-suite within the field commonly referred to as Workforce Optimisation (WFO). The company offers solutions for small and medium-sized companies, major enterprise companies as well as public organisations.
The advantage of Calabrio’s solutions is that they can be locally installed, via cloud and as a hybrid solution.
Calabrio offers tools which help companies streamline work (e.g. Workforce Management and Performance Management), improve customer communication (e.g. Quality Management) and Compliance Recording to record customer calls.
According to Gartner Magic Quadrant as well as Forrester Wawe Calabrio is among the 3 leading WFO players and Calabrio’s solutions are characterised by being very intuitive and user-friendly.
Axcess Nordic sells and implements solutions on behalf of Calabrio in the Nordic countries and has status of Platinum PLUS Partner (highest possible partner status), which requires extensive knowledge of the company’s solutions.
Axcess Nordic has formed a partnership with Avaya to be able to offer certified consultancy. We sell, implement, support and consult on Workforce Optimisation (WFO) and Workforce Management (WFM) solutions for Avaya platforms.
Avaya forms a strategic partnership with Verint Systems. This means that if you use an Avaya platform you will be able to buy WFO and WFM solutions from Verint Systems through Avaya. This way, you will have access to best-in-class solutions and direct support.
Avaya is a global market leader within IP telephony, collaboration and contact centre solutions. Over the last 10 years, Avaya solutions for more than 4.5million customer centre employees have been implemented worldwide.
Axcess Nordic has a focused approach to supporting Cisco’s collaboration solutions which, among other things, entails a number of solutions which can work on top of a Cisco based telephony and contact centre set-up. We offer in this way, among other things, certified consultancy and we are licensed to sell, implement, support and consult on Workforce Optimisation (WFO) and Workforce Management (WFM) solutions for Cisco platforms.
Cisco has entered into a strategic partnership with Calabrio as well as Verint Systems, enabling you as a Cisco customer to buy WFO and WFM solutions from Calabrio and Verint systems via Cisco and, in this way, gain access to best-in class solutions which are compatible and offer direct support. Solutions can be bought as Cisco-OEM or via the so-called Cisco Solution Plus program.
Cisco is a global market leader within IP telephony, video, collaboration and contact centre solutions.
At Axcess Nordic, we are very much aware that Microsoft’s solutions within the area of collaboration (e.g. Skype for Business and Office 365 with Skype) are collaboration solutions which are gaining in the Nordic countries. For this reason, we also offer tools which support these platforms, e.g. Compliance Recording.
For this reason, we also offer our solutions to these platforms, e.g. Compliance Recording. Here, we can offer to record a Microsoft based communication, this could, for instance, be Skype chat, voice, text and e-mail interactions as well as screen data with a solution based on Verint Systems.
Axcess Nordic is ready to talk to Microsoft customers about your requirements and wishes for Workforce Optimisation (WFO).
Reliable communication is the key to an efficient workstation when finance houses’ and energy companies’ trading departments act on the global markets.
IPC is one of the global market leaders with information and communication technology for trading floors, and it is a company known for its ability to satisfy even the most demanding traders.
Axcess Nordic and IPC have been partners for more than 10 years and Axcess Nordic delivers and supports IPC’s solutions for several Nordic finance and energy companies using IPC as a modern and efficient communications platform.
SBFI has built a solid brand as supplier of specially-designed tables and storage solutions for trading floors and office environments. SBFI’s tables are constructed to be able to offer space, cooling as well as a set-up to satisfy even the most demanding trading workstation.
Axcess Nordic and SBFI have worked together for more than 10 years to deliver the most modern and professional solutions for the Nordic finance and control room market.
Amulet Hotkey delivers compute, performance, flexibility and scalability to workstations and desktop infrastructure of a quality accommodating even the most demanding work stations. For this reason, Amulet Hotkey offers relevant solutions for trading floors and control rooms especially, as these typically place high demands on speed, redundancy and multiple screens which all have to work optimally.
Axcess Nordic and Amulet Hotkey have worked together since 2013 to deliver solutions to the Nordic market.
We help service-oriented companies and organisations throughout the Nordic countries to optimise and develop their customer experience. At the same time, we help Nordic finance houses and energy companies with telephony and information systems.
Description Black & White photograph of Adolf Hitler talking to German Luftwaffe officers standing in front of a Junkers Ju 52 bomber, circa 1935-1945.