SQL SERVER – Capturing Execution Plan for Canceled Query

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.

SQL SERVER - Capturing Execution Plan for Canceled Query canceled-query-800x254

“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

SQL SERVER - Capturing Execution Plan for Canceled Query livequery1

Method 2: SSMS Tool Bar Option

SQL SERVER - Capturing Execution Plan for Canceled Query livequery2

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.

SQL SERVER - Capturing Execution Plan for Canceled Query livequery3

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)

Execution Plan, SQL Scripts, SQL Server, SQL Server Management Studio, SSMS
Previous Post
SQL SERVER – Scheduler_ID with Large Number in dm_os_schedulers
Next Post
SQL SERVER – Query for CPU Pressure

Related Posts

Leave a Reply