You can analyze D365 query performance using multiple methods, such as the query store. One of the primary benefits of using the Query Store in Microsoft Dynamics 365 Finance and Supply Chain is diagnosing performance issues where processes that were once fast have now slowed down significantly. By leveraging the Query Store, we can analyze historical performance data to pinpoint the exact moment when the degradation began and identify any changes that may have contributed to the slowdown.
Additionally, the Query Store offers valuable capabilities for viewing execution plans for queries running in production. This allows us to compare these execution plans with those in other sandbox environments. Examining execution plan differences uncovers optimizations and discrepancies, offering insights into why queries perform differently across environments.
Accessing the query store
Since we don’t have direct access to the production database in Dynamics 365 Finance and Supply Chain (F&SC), viewing the Query Store for production requires an initial step: performing a database refresh from production to any Microsoft-hosted tiered environment [sandbox]. This process ensures that the refresh copies the Query Store, along with all its collected data. After completing this, we can access and analyze the D365 query performance in the Query Store, gaining insights into performance.
Within SQL Management Studio, you can find the Query Store under the database in Dynamics 365 Finance and Supply Chain (F&SC). If the Query Store is in a read-only state, it will not collect any new data. Use the following SQL query to determine the reason code for why the Query Store is in read-only mode.
SELECT * FROM sys.database_query_store_options;For more details on the different reason codes, refer to sys.database_query_store_options (Transact-SQL) – SQL Server | Microsoft Learn. The most common reason code encountered is 65536. This indicates that the Query Store has reached the size limit set based on the parameter set. Since we do not control these parameters in Dynamics 365 F&SC, we cannot make adjustments. However, if you are working with a Dynamics 2012 environment, you can increase this size limit.
Identifying the D365 query ID
To investigate a D365 query performance issue using the Query Store, the first step is to identify a query that is currently performing slowly as part of a process. I recommend taking a trace of the slow process to discover the problematic query. I will create a future blog on taking traces. Alternatively, you can review the environment monitoring logs for long-running queries. It may help to filter the list by the call stack of the slow process under review.
Once you have a query you’d like to investigate, we will need to locate its unique query ID. Below is a query you can use against the Dynamics 365 F&SC database to locate the query ID;
SELECT q.query_id, t.query_sql_text, object_name(q.object_id) AS parent_object
FROM sys.query_store_query_text t JOIN sys.query_store_query q
ON t.query_text_id = q.query_text_id
WHERE t.query_sql_text LIKE '% %'The query range for ‘sql_text’ can be difficult to search against attempting a 100% match. This is because different tools, such as trace parser, may alter the spacing slightly in their output. spacing. It’s advisable to use a wildcard search instead of attempting a direct match. I recommend using multiple wildcards to simplify the query being searched and reduce potential results.
For a query such as:
SELECT SUM(T1.POSTEDQTY),SUM(T1.POSTEDVALUE),SUM(T1.PHYSICALVALUE),
SUM(T1.DEDUCTED),SUM(T1.RECEIVED),SUM(T1.RESERVPHYSICAL),
SUM(T1.RESERVORDERED),SUM(T1.REGISTERED),SUM(T1.PICKED),
SUM(T1.ONORDER),SUM(T1.ORDERED),SUM(T1.ARRIVED)
FROM INVENTSUM T1
WHERE (((T1.PARTITION=5637144576)
AND (T1.DATAAREAID=N'DAT'))
AND ((T1.ITEMID=@P1)
AND (T1.CLOSED=@P2)))
AND EXISTS
(SELECT 'x' FROM INVENTDIM T2
WHERE (((T2.PARTITION=5637144576)
AND (T2.DATAAREAID=N'DAT'))
AND ((T2.INVENTDIMID=T1.INVENTDIMID)
AND (T2.INVENTSITEID=@P3))))This is the wild card filter I would utilize (notice the multiple percent symbol wildcards):
%SELECT SUM(T1.POSTEDQTY)%FROM INVENTSUM T1%FROM INVENTDIM T2%T2.INVENTDIMID=T1.INENTDIMID%
Depending on the number of records returned by the query, compare each until you find an exact match. I typically use the number of parameters in the query or the DataAreaId to help distinguish between similar queries. This approach can streamline the process and make it easier to identify the correct query efficiently.
Tracked queries
Now that we have the D365 performance query ID to research, we can utilize the tracked queries option from the query store:
First, open the tracked queries form by double-clicking the item in the query store. Then, from the Configure button in the top right corner, you can enter the query ID you wish to track.
I usually start with the duration as the tracking condition. Next, select a time interval that makes sense depending on your issue. For example, if the slowness began occurring a week ago, I would set the time interval to two weeks. This allows you to compare the query’s performance before and after users reported the slowness.
Reviewing D365 query performance details
The top view of the resulting page shows dots representing when SQL utilized an execution plan. SQL uses different colored dots to represent unique query plans it has chosen. For more complicated queries, there could be several different plan IDs. This indicates that SQL has used multiple execution plans for the same query. If there is only a single plan ID, it suggests that the query has consistently used the same execution plan. In such cases, the performance issue may not be related to the execution plan itself. We will then need to explore other potential reasons for the slow performance. The above example had two different execution plans making it easy to compare.
When hovering over any of the dots/execution plans, you can see some details about the D365 performance query. Key details to note include the query’s minimum and maximum duration, as well as the execution count, which shows how many times the system used this plan within a given time frame.
Note: If the duration tracking view is not providing sufficient information on why the process is slow, try switching to the wait time view from the configure menu. This view can indicate whether something is blocking or delaying the query from execution.
The bottom of the screen shows the exact execution plan SQL uses for the selected plan ID. In a future blog, I can dive deeper into key items I typically look for when reviewing execution plans. Noted items are how the system processes the tables, indexes utilized, and applied seek predicates.
Comparing execution plans
You can also select two plan IDs and use the Compare Execution Plans button on the top ribbon bar. This feature provides a side-by-side view of the different plans, highlighting the differences.
In this specific situation, the seek predicate against the InventDim table was identified as the cause of the performance issue with one of the execution plans. [Note: A seek predicate is the conditions and fields utilized in the index seek]
At times, the execution plan may show that SQL is not utilizing a proper index. SQL may even recommend an index to add. I’ll discuss key considerations for adding a recommended index in another article.
Resolving query issues
Viewing the query execution plan can present several options for addressing the D365 query performance issue:
- Adding an Index: An index may be needed to improve query performance.
- Addressing Data Issues: The query may suggest a data issue thus requiring more ranges or data cleanup.
- Creating a Plan Guide (Dynamics 2012): In on-premise versions, we can use the query plan to create a plan guide for a specific query.
- Forcing a Plan ID: We can use the button to force a plan ID. This will cause SQL to use the plan ID each time it encounters the query ID.
There are a couple of disclaimers for the latter two options:
- Forcing a Query Plan: Forcing a query plan may not always be the correct answer. If the query is heavily parameterized, some parameters may perform better with one query plan over another. For example, in Dynamics 2012, an InventSum query with a range of ItemID joined to InventDim on a range of license plate ID might perform differently depending on whether a license plate parameter is passed. If a license plate parameter is provided, starting with InventDim could be faster. However, if the license plate is blank, starting with InventSum could be more efficient.
- Unique Query IDs: Query IDs are unique, so we may need to repeat the operation for each query iteration. For instance, if there are multiple legal entities, there will be a unique query ID for each company.
- Plan Guide Access: We do not have access to force a plan guide in production. However, we can test this in a tiered environment and then open a Microsoft ticket. Letting them know which plan to force will help expedite a resolution.
