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
+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