Ever faced performance issues due to parameter sniffing on other columns than DataAreaId or PartitionId?
What if we could have more control over the fact which column is handled as a literal (and not as a parameter) in the query?
Microsoft Dynamics 365 Finance and Operations, Enterprise edition has the functionality to change this for the entire query using forceLiterals, this will overload the queryplan cash with an individual queryplan+compile overhead for each individual query execution.
OR the DataAreaIDLiteral and PartitionIDLiteral than solves the problem for these columns in the application.
What if we would have a table field property to control this behavior? It would avoid coding for each individual query affected by that field.
For example: A customer who has 10 warehouses and uses serial numbers hence high volume of records in the InventDim table, 75% of all InventDim records is in only one warehouse. So if a query plan gets optimized for a small warehouse we create significant performance issues with parameterized query’s. If we could specify a literal in a query only on the warehouse this would generate 10 query plans which are far more optimized.
(Special thanks go to KevinRoos)
Comments
I started working on AX 2.5 in 2002. In the last 20+ years in numerous past AX implementation projects, I was the witness of performance issues caused by parameter placeholders. Some of the most problematic queries are the ones launched by InventSum::findSum, InventSum::findSumQty and BOMVersion::selectBomVersion methods. There are scenarios where the number of records can be unevenly distributed in BOMVersion, InventSum and InventDim.For example when product configurator is heavily used, it can lead to some items being linked to millions of records because of high number of configured variants. Compared to a few hundred/thousand records for items not using configuration dimension.Another example is when warehouse management is set up using heavyweight sites/warehouses combined to some other lightweight sites/warehouses linked to small subsidiaries or secondary stocking locations. Again, million of records can be found in InventSum and InventDim for one specific main warehouse. Compared to a few thousand (or less) records linked to smaller warehouses.When data is unevenly distributed, native queries over BOMVersion/InventSum/InventDim are causing intermittent performance issues and/or fluctuations. A same query can be attached to multiple execution plans when doing analysis in SQL Server Management Studio. Average query execution time can vary from 50 ms (or below) but sometimes up to more than 500 ms.Currently, this is not possible to use CoC (chain of command) to replace the native logic in above methods but also in a lot of other place across the entire application.There is a 'Literals' property on extended data types. But as of application version 10.0.44, modifying the value of this property using edt extension doesn't seems to have any effect.
Category: Development
+1 For this idea.
My aproach here would be to add an extra property for the EDTs. So in all tables where a certain EDT is used we could force literals.
Based on our experiance it could be very usefull for enum type fields. We frequently have uneven data distribution.
We would like if this property would be extendable also :)
Category: Development
This is very relevant suggestion. A property would be very helpful to handle this, where we can define whether to include as parameter or literal.
Currently one our customer has, set up some kind of SQL job that's checking for degraded query plans and rejecting them every 2 mins. However this would not be possible for D365 F&O in cloud.
Category: Development
I think this becoming even more relevant with overlayering coming in where we won't be able to use the forceliterals keyword as a workaround so easily, for parameter sniffing on columns other than partition and dataareaid.
Category: Development
I would like to support Markus on his idea.
The normal approach for a Dynamics AX has always been that you have to find the needle in the haystack regarding what queries that needs tuning. This can be very cumbersome and often requires code changes as Markus describes.
The idea of being able to switch selected fields on a table and force only those to be literals would be a great step towards platform that actually scales with the customer and to be as close to standard as possible.
On top of that if you think about it that we have a cloud first strategy for Dynamics AX and the database server is off limits for the customer it makes even more sense that we get something that will enable us to fine tune the application within the AOT with configurations rather than code changes.
Category: Development