Is it Live or Is It Memorex?

If you are my age, that is a phrase you will remember.  If not, it is a slogan for Memorex from quite a few years ago for cassette tapes….here is a link to a YouTube video.

This post really isn’t about that…it is about live query execution plans that were introduced with SSMS 2016.  Basically live query execution plans allows you to view the plan live as it is executing.

Before getting into the details, let’s take a look how to turn it on.  Before starting your query, Live Query Statistics must be turned on.  There are two ways this can be done.  The first one is under the Query menu and the second method is on the tool bar.  The button is the one the arrow is pointing to.

Once this is turned on, you can now run your query.  When you do, a new tab will appear in the results pain, which will be labeled “Live Query Statistics”.

This is actually pretty cool to see.  The dotted lines represent the parts of the plan that are either currently executing or haven’t executed yet.  In addition to seeing the lines move, you will also see the number of rows increment and the number of seconds go up as well.  The time is circled below.

Regarding the number of rows, you will actually see two numbers, actual number of rows and the estimated number of rows.  You will see something like this:

As indicated, the green arrow is the actual number of rows and the brown arrow is the estimated number of rows.  If you move away from the operator and the pop up menu disappears, when you return the actual number of rows will actually increment as more rows are returned.

If you look at the properties of the operator, you will see the Actual Number of Rows and the Elapsed Time properties increment as the query executes.

You can also see Live Execution Plans by using Activity Monitor.  Once you have opened Activity Monitor, navigate to the Active Expensive Queries section and right click on the query.  You will see a pop menu that will have the “Show Live Execution Plan”.

At the bottom left of the new tab, you will see the progress.  In the case below, the progress is at 18% complete.

Brent Ozar has a few really good posts that go more in depth about Live Query Execution Plans.  I figured why reinvent the wheel.  Here are the links to his posts.

BrentO Post 1

BrentO Post 2

I would recommend that you try this if you haven’t yet.

Here is some code that you can use.

USE [AdventureWorks2014]

FROM Production.TransactionHistory th
INNER JOIN Production.TransactionHistoryArchive tha
ON th.Quantity = tha.Quantity

Thanks for visiting my blog…hope you enjoyed the post!