Some queries and their individual data composition in the different companies sometimes really require specific query tuning.
Microsoft Dynamics 365 Finance and Operations, Enterprise edition has the functionality to disable parameter usage for the entire query using forceLiterals, this will overload the queryplan cash with an individual queryplan+compile overhead for each query execution.
What if we would have additional control on the SQL hints via X++? My wish list would be:
OPTIMIZE FOR UNKNOWN (formally often enabled on SQL Instance by SQL TF 4136)
RECOMPILE (formally often added by SQL Plan Guides)
MAXDOP number_of_processors
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query
Comments
It is true you can create Plan Guides for most of your queries, but it is not possible if the query you have problems with uses a TempDB table. In this case the table name changes, so you cannot use it in a Plan Guide. I have seen quite many such examples already and new hints in X++ would make it very easy to fix.
One hint that I would add to this request is QUERYTRACEON - this is especially useful when there are only couple of queries that are working better with old Cardinality Estimator in SQL and you don't want to change the whole DB (or instance) to work with legacy CE (TF9481 could then be used on query level to make it work better)
On a final note, we have some of the hints listed in this request, but I think the best would be to just allow all the SQL hints from X++
Category: Development
You already can achieve this using "EXEC sp_create_plan_guide" SQL command.(you can add OPTIMIZE FOR UNKNOWN, MAXDOP and also INDEX hints)
if has the following advantages:
-you don't need to deploy your code (current deployment time is 3 hours of downtime), it works instantly
-if Microsoft seal the application suite you can't tune the standard queries using X++
- you will see all your hints in one place
and one disadvantage - you need to update your guides if someone add new table fields.
So probably the best way to implement it - add some AX form to manage SQL plan guides, instead of new query hints in X++
Category: Development
Administrator on 5/25/2019 7:24:35 PM
We added index hints to X++ as of platform update 24 and we will be adding delete from hints in an upcoming platform (date not committed yet)