Using SQL Server Activity Monitor to Capture Details for M-Files Support
Posted by Matti Ketonen (M-Files) on 04 July 2019 12:13 AM
This is a brief instruction how to use Microsoft SQL Server Activity Monitor to analyze possible issues in SQL server instance. The goal is to be able to capture screenshots and get some of the queries to M-Files support for more detail analysis.
Typical scenario is that that M-Files technical support or dev support has already done this sort of analysis with the customer, and these are more like reminder steps how to get the same details for the support to do next set of assessment.
Connect to the SQL Server instance with Microsoft SQL Server Management Studio. If possible, use remote desktop connection to get MSSMS running directly on the target server.
Launch Activity Monitor from context menu of the SQL instance.
Take screen captures of the following looking way, please do note that some columns need to be made wider and sorting can be changed to show some relevant.
Processes, note that you may extend some fields, as well sort by different order.
Data File I/O, note that you may need to sort by some read/write fields.
Recent Expensive Queries, if a high value is found, open the query's execution plan.
Open Edit Query Text.
Select all CTRL+A, copy that to clip board CTRL+C, and paste to email CTRL+V for example:
Close the query profile and query editor without executing the queries. Use the small x in the tabbing.
Do the same with Active Expensive Queries, take a full screen capture and try looking for the ones that have been running longest and take most CPU, open the queries and copy them to email along with screen capture.