Introduction to D365 Environment Monitoring and Performance troubleshooting using tools in Lifecycle Services (LCS)

This article serves as an introduction to the available Lifecycle Services tools available for troubleshooting performance issues in Microsoft Dynamics 365 Finance and Operations cloud application.

The tools available in LCS can be used to diagnose performance issues and also collect performance metrics on demand. With these LCS tools, you can easily mitigate performance issues in your User Acceptance Test (UAT) or Production environment.

 in near real-time. These tools are essentially monitoring tools that enabled real-time monitoring and auditing of your activities in your environment.

Fig.1: LCS Environment Monitoring page

Lifecycle Services Environment Monitoring:

SQL performance monitoring and troubleshooting tools are found in the SQL Insight tab under the Environment Monitoring Page on Lifecycle Services.

Fig.2: SQL Insights tab

Live View – Shows currently executing statements and blocking statements.

Fig.3: Live View - SQL Insights tab

Queries – This shows a list of predefined queries that can be used to retrieve metrics on demand. Examples of queries include a current blocking tree, a list of active plan guides, and a list of the most expensive queries.

Fig.4: Queries - SQL Insights

Actions – Shows a list of predefined actions that should be taken to mitigate issues in the sandbox and production environments. Examples of actions include terminating a blocking statement. Any time that an action is performed, the environment history for an environment will show a record of the action performed. A historical record is created only for actions and not when queries are executed.

Fig.5: Actions - SQL Insights tab

Performance Metrics – This shows the most expensive queries that were run in the system during the selected period, based on logical I/O, execution count, duration, CPU time, and wait count. This data is queried from the SQL query store. The data is retained for 30 days, and the tool runs its data collection every day at a random time between midnight and 4 AM in the time zone in which your environment is hosted. The last run date and time is visible from your environment details page in Lifecycle Services, under the Monitoring tab in the Last run field. To use the tool, select a period during the last 30 days. When the query results appear, select the bar in the duration chart to highlight where the query falls based on other metrics. On the Statement tab, you can either view the query or download the query execution plan. This feature is not available in self-service environments.

Fig.6: System Metrics

How to use the LCS performance troubleshooting tools

Go to your project in LCS and open the environment details page. Select the Environment Monitoring link in the Monitoring section. Select the SQL Insights tab to access this feature.

1. You can navigate to each of the tabs (Live View, Queries, Actions) to view or query for more information.
2. You have the option to search or export to Excel any of the results from the query execution.
3. After you have narrowed down the reason for the performance issue, you can use a predefined action to mitigate the issue.
4. After an action is performed, an entry is made on the Environment History page, which shows the details of the action, the parameters that were passed in, a timestamp, and who triggered the action.

Dynamics 365 F&O Performance Case Scenario

Users report slow performance when using the system. One issue could be a blocking statement. Kindly note that “Blocking by itself is typical in a healthy system and is only a problem when it becomes excessive or starts degrading business activities.”

Steps to take:

1.   In your environment monitoring page, navigate to the Live View tab and check if there are any blocking statements. If there is a blocking statement, copy the blocking query ID.
2. Open the Queries tab and select the Current Blocking Tree query. This will return the root blocker that is blocking the SQL operation.
3. To resolve the issue, you can either let it run and clear naturally, or end the process for the lead blocker, which will roll work back. Typically, you should only end the lead blocker process if you think that it will not clear naturally (such as a bad query plan), or in situations where a critical process is unable to run and needs to be completed immediately.
4. Confirm that it is okay to terminate the statements that are currently being executed.
5. Open the Actions tab and select the End SQL Process action and pass in the root blocker query ID. This will execute a query against the SQL database to terminate the blocking statement.
6. Go to the Queries tab and run Current blocking query to verify if the blocking statement was terminated.
7. You can also check the Environment History page to see details on what process was terminated.

See below for key terms to note:

Current blocking

Lists any currently blocked queries, the SPID that is blocking them, how long they have been blocked, and what resource they are waiting on. This can be used in conjunction with the query to see the blocking tree, which provides a graphical overview of some of the same information. Blocking by itself is normal in a healthy system and is only a problem when it becomes excessive or starts degrading business activities.

SPID: Server Process ID

A SPID in SQL Server is a Server Process ID. These process IDs are essentially sessions in SQL Server. Every time an application connects to SQL Server, a new connection (or SPID) is created. 

Current blocking tree

Provides a graphical view of the SPIDs and statements that are currently causing blocking or being blocked. This can be used in conjunction with the current blocking query to see more detailed information.

Currently running queries

Provides a list of all queries that are currently in a state of being executed or blocked on this database, and the total execution and wait times of each query. Queries that have high execution time and low wait time are often indicative of bad query plans. Queries with high wait time and low execution time are indicative of blocking. If relatively fast operations are being run many times, sometimes they can be found by running this query multiple times in a row and looking for commonly occurring queries with fast execution time.

 

End SQL process

If a SPID is consuming too many resources and degrading the operation of other processes, it might be beneficial to end the SPID process. This will cause the open transaction to roll back, meaning that data should not be lost, but the process might need to be manually restarted. Note that rollback can also take a long time and consume a lot of resources if the transaction has already performed a lot of work. Therefore, this action should be used with caution.