Activity Monitor can come handy when you are in trouble as well. In this video, we will see how we can take the help of Activity Monitor to track Actively Running Query. If you are using SQL Server 2019, the feature described in this blog is by default enabled for you. However, if you are using any of the previous versions of the SQL Server (2016, 2017), you will have to use the trace flag to enable the feature discussed today.
In the previous blog post, I discussed how Live Query Statistics can be handy and helpful. I got lots of emails in response to the previous blog post and video. Lots of people want to know about what query is currently running in their server and their status. I totally understand their request. If you know which query you want to debug, you can enable the debug operation and can debug. However, if you are on the production server and noticed that there are plenty of the query going on at any point and if you want to know the current status of any query, it was not possible to know it earlier.
However, in SQL Server 2019, you can easily know this via Activity Monitor. I have described the method in this short SQL in the Sixty Seconds Video.
Activity Monitor is not my preferred way to query tuning. I usually depend on the DMVs but as a SQL Server Performance Troubleshooter, we should have no prejudice. We should keep on all the options open. Once you are done with the usage of the activity monitor, I suggest you close the activity monitor as it can be very expensive to leave that open for your production environment.
If you have any SQL Server tip and want me to make a video on it, please leave a comment and I will do my best.
Reference: Pinal Dave (https://blog.sqlauthority.com)