Hello!
In a series of posts, we would like to share our experience in the development and operation of systems of the MS Dynamics AX family (formerly Axapta).
About Us
We are a relatively young retail chain of grocery supermarkets "Da!" At the time of this writing, we have just over 100 stores. The main processes of the company's operating activities are automated in a combination of MS Dynamics Ax 2012 + MS Dynamics 365 FO systems. The systems operate 24/7. On average, about a million receipt lines and about 70,000 assembly order lines go through the system per day.
Plan guides
Performance tuning Axapts can be done in different ways. The most efficient way is, of course, to optimize the application source code. But there are situations when this is problematic. Then you can use the tool provided by MS SQL Server DBMS. It is called - Plan guide. Appeared in the version of SQL Server 2005. But according to our feelings in the Axapters community (especially if the company does not have a professional DBA), he is not always known and used. Although, in some cases, its use can be very effective.
What for?
Here are the main reasons when you should look towards this tool:
1. Inconsistent data in query parameters. The sampling result (number of records) for the same sampling criterion (set of fields) differs depending on the values of the variables in the sampling criteria. Or, in a simpler way, when the same query for different input parameters results in a radically different number of records (sometimes one, sometimes ten thousand). This is due to the so-called “sniffing” of request parameters. When a query plan is created once under the query mask, and then taken from the cache. Not looking at the values of these same parameters.
This is often the case with queries that involve InventSum and InventDim tables. For example, when in analytics there is only one party for some nomenklatura, and for others - several thousand. The first request to the database can go through for an item for which batch accounting is disabled. The optimizer will build a query plan for it. And put it in the cache. The next request to the database can go through for an item that has batch accounting enabled. And give out several thousand records in the selection for InventSum and InventDim. And for such a sample, the plan from the cache will not be optimal.
One way to solve this problem is to use the forceLiterals hint in the request body. This signals the SQL engine to generate a new query plan each time. But this gives an additional tangible load on the CPU. And with the same leftover requests using InventDim is not an acceptable option. Well, you need to understand that the SQL Server optimizer is not perfect and sometimes, even with complete statistics, generates strange plans.
And in this case, the Plan Guide comes to the rescue, with the help of which you can choose a query plan that gives an acceptable execution speed for any query input parameters. And attach this plan to the query mask using the Plan Guide.
2. The optimizer chooses an index that results in long locks. Using the Plan Guide, you can “nail down” the use of a specific index, which will narrow the sample and reduce the number of locks.
3. The source (place in the code) of the problematic request cannot be quickly identified, and the problem of the database performance drop must be promptly solved.
4. The application source code cannot be changed for some reason (partner solution, requests from the kernel, etc.). This is especially true for D365, which prohibits overlays.
How?
I will not describe in detail a step-by-step guide to creating a Plan Guide. There is a good description on the vendor's website (we are interested in the type of plan - SQL) And the network has a sea of tutorials.
But it's important to know that there is another SQL Server tool that will be of great help to you if you need to create a new Plan Guide. It is called the Query Store. Appeared in 2016. A detailed description of it here .
The main idea of the tool is that, in addition to the current query plan in the cache, it stores the entire history of plans that the optimizer has formed over a given time. If you know that the problematic functionality worked "normally" before. Didn't slow down. You just have to find the plan you need in the repository and make a Plan Guide based on it. Unfortunately, due to the peculiarities of Axapta, it is impossible to create a Plan Guide with one “force plan” button. You will have to copy the query plan from the repository and create the Plan Guide manually. But this still greatly simplifies the task.
It should also be borne in mind that the use of Query Store gives a small overhead on the computational resources of the DBMS server used. But in our practice, they are insignificant, and this can be neglected.
Examples of
Here are a couple of Plan Guide examples from our real battle base. Please note that these are just examples that are relevant to our specific business processes. They may not be applicable or even harmful to your installation.
1. InventSum
This Plan guide solves the problem of suboptimal plans in the case of queries for items with a small number of records in the InventDim table. Using this guide, you can always use the optimal plan for sampling with a large number of InventDim SKU combinations. Queries for items with few SKUs will be slightly slower. But this is not a big price to pay for a stable and predictable speed for any combination of input parameters.
These queries are mainly generated by the InventSum :: findSum () method. And depending on the grouping, the query patterns may differ slightly. So in reality we have more similar Plan Guide, adapted for different variants of groupings.
2. InventSumDelta
This Plan Guide allows you to build an optimal query plan for the InventSumDelta table, avoiding unnecessary locks on this table. The specificity of this table is such that data is not stored in it. But they are very intensively added / removed. It is essentially a semaphore table. In this regard, normal statistics cannot be collected on this table. Therefore, the optimizer sometimes generated suboptimal plans resulting in blocking.
A bit offtopic - also for this table you need to disable page locks on indexes. Since the selection from this table is always done by a unique ID, escalating locks to the page level is meaningless and even harmful here.
conclusions
But in the general case, let me draw your attention once again, you should not abuse this tool. If the code is written optimally, statistics are regularly updated, the indexes are not very fragmented - the optimizer in most cases will select the correct plan itself. But if the plan guide is configured, the input criteria of the request may fall out such that the plan guide will only harm.