In the recent release for LCS, Microsoft added some great new functionalities that, as a partner, we have been missing ever since the shift to the Cloud. Behold: We can now diagnose and mitigate performance issues on Sandbox and Production directly through LCS
The full release notes can be viewed here: https://blogs.msdn.microsoft.com/lcs/2018/09/20/lcs-september-2018-release-notes/
This is really exciting, as now we can directly run checks against the production database AND have powers to kill a blocking process or quickly resolve performance bottlenecks by adding new indexes to the tables.
The change should be published now across the board. Log into LCS, choose an implementation project and locate the environment that is struggling. Once there, choose ‘Environment monitoring’:
When the page loads, shift your focus to ‘SQL Insights’ tab. There are 3 new options over there:
As per Microsoft release notes:
- Live View – Shows current DTU, executing statements, and blocking statements. This allows you to observe what is currently happening in your system. Think of this as ‘Activity monitor’ in SQL Server Management Studio. You see exactly what SQL queries are executed with their running times and potential resource waits. Should we have any blocking statements we would have seen them in here, but thankfully all seems running like a dream today.
- The Batch Info column also contains the ID of the user running the statement. You can use it to check with the user what kind of operation has been triggered.
- Queries – Shows a list of pre-defined queries that can be used to retrieve metrics on demand. Examples of queries include a current blocking tree, a list of active plan guides, get XML for a plan ID, and a list of most expensive queries. This option lets you execute one of the predefined queries (I am sure Microsoft will keep adding them in the future):
- You can reuse some of the information returned from previous Live view screen to execute queries with parameters:
- Actions – Shows a list of pre-defined actions that should be taken to mitigate issues in the Sandbox and Production environments. Examples of actions include adding/dropping an index, updating stats on a table, rebuilding indexes, and terminating a blocking statement. This is actually is big – Microsoft will allow you to physically change the production database. You will not be able to execute direct SQL query against a database, but you still have access to quite a few performance-oriented actions to perform:
I will not delve into the details of every option available, however I can see this one being a life saver in critical situations: