The reason, I love my job is that my audience. Every single day I work with new clients on the project Comprehensive Database Performance Health Check, I also discover new aspects of SQL Server. One of DBA recently asked is there any way to capture the execution plan for the canceled query. Let us first read his note and right after that let us discuss the workaround of the situation.
“Here is the question, we have one query which usually runs for over 20 minutes, I am currently tuning the query but after every single change, we can’t let it run for 20 minutes, that would be a very expensive process and will waste time and energy.
What we want to do is that run the query, get the execution plan and cancel it. Is there any way to capture the execution plan for the canceled query?“
Capturing Execution Plan for Canceled Query
Well, the answer is very simple to let us see how we can capture the execution plan for the canceled query. Actually, there is no way we can get the execution plan for the canceled query but we can for sure get the execution plan of about to be canceled query.
So here is how we can capture the execution plan of the query which we are able to cancel.
Enable Live Query Statistics
There are two different ways to enable Live Query Statistics. This feature is available in SSMS 16 Onwards for SQL Server 2014 onwards.
Method 1: SSMS Tool Bar Menu
Method 2: SSMS Tool Bar Option
Now when you have enabled Live Query Statistics, you can run you any query and immediately there will be an execution plan table visible in the results table which will contain the execution plan of the currently running the query. You can just take a screenshot or just observe it to know if the query execution plan reflects your changes or not. Once you get the necessary information from your query execution plan, you can safely cancel your query.
While this may not be the perfect solution, this solution can be used to get the execution plan of the queries which are running very long. There is another option is to check the estimated execution plan, however, I prefer to use this feature over the estimated execution plan.
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
