This topic describes how you can troubleshoot and mitigate the performance issues using the tools available in Microsoft Dynamics Life Cycle Services (LCS).
Dynamics LCS Overview
This Microsoft Dynamics Life Cycle Services (LCS) feature queries the SQL Server directly, so you get query store metrics in near real-time. Also added is an audit trail on the action performed so that you can easily determine who performed the action and when it was performed.
All SQL performance tools in MS Dynamics LCS services are available under the SQL Insights tab on the Environment Monitoring page for a specific environment. The following tabs are available:
- Live View – It shows the current DTU, blocking statements, and executing statements. The SQL Now page that shows performance issues will be replaced with Live View.
- Queries – It shows a list of pre-defined queries that can be used to retrieve metrics on demand. Examples of queries includes a blocking tree, a list of the active plan guides, and a list of most expensive queries.
- Actions – It shows a list of pre-defined actions that should be taken to mitigate issues in the sandbox and the production environments. Examples includes the actions that are used for adding or dropping an index, updating stats on a table, rebuilding indexes, and terminating the blocking statement.
- Performance Metrics – It shows the most expensive queries that were run in the system during the selected period, based on the logical I/O, duration, execution count, CPU time, and wait count. This data is queried from the SQL query store.
- Index Analysis – It shows aggregated index and table information, based on user scans, user updates, user seeks, and row count.
How Do I Use this Dynamics LCS Feature?
- Go to your project in Dynamics Life Cycle Services (LCS) and open the environment details page. Click on the Environment Monitoring link in the Monitoring section. Click on the SQL Insights tab to access this features.
- You can navigate to each of the tabs like Queries, Live View, Actions, Performance Metrics, Index and Analysis to view or query for more information.
- You have the option to export or search.
- 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 time-stamp, and who triggered the action.
Users will report slow performance when using the system. One issue in Dynamics Life Cycle Services could be a blocking statement. Blocking itself is typical in a healthy system and is only a problem when it becomes excessive or starts degrading business activities.
- Go to Live View tab and check if there is any blocking statements. If there is any blocking statement, copy the blocking query ID.
- Open Queries tab and Click on Current Blocking Tree query. This will show that the root blocker that is blocking the SQL operation.
- To resolve this major issue, you can either let it run and clear naturally, or end the process for the lead blocker, which will roll work back. And you should only end the lead blocker process if you do not think that it will not clear naturally (such as a bad query plan).
- Confirms that it’s okay to terminate the statements that are now being executed.
- Then open the Actions tab and select the End SQL Process action and pass in the root blocker query ID. This will starts execute a query against the SQL database to terminate the blocking statement.
- Click on Queries tab and run Current blocking query to verify if the blocking statement was terminated.
- Then you can also check the Environment History page to see details on what process was terminated.
- To avoid this type of issue in the future, you should use indexes or plan guides, or turn off lock escalation, or use page locks if processes are blocking each other while operating on different records.
Locus IT has a thorough knowledge of some of the well-known industries and the particular challenges that currently exist in this industry. We at Locus IT provide Microsoft Dynamics LCS support, Microsoft Dynamics LCS training, Microsoft Dynamics LCS upgradation, Microsoft Dynamics LCS migration and can help you design a solution using proven tools that meet your specific needs, operations, streamlining, and increasing profit.