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)