This is the third in a 3 part post on measuring query runtimes (previous posts Part 1 Part 2) This post covers using live query plans. This feature is available on SQL Server 2016 or later.
Live query statistics allow you to see where time is being spent as a query executes. It’s similar to Actual plans; however, it shows information in real time as a query executes. This is useful for longer running queries that take multiple seconds to run. This won’t be too useful if you’re tuning a query that completes in under a second.
Live query statistics are displayed in SSMS via the execution plan. You can enable this by selecting the “Live query statistics” button on the toolbar:
When you execute the query, you’ll see the active notes in the plan producing rows. Look for the marching ants. The marching ants show where work is occurring. Usually, you need to follow them through the plan tree to the source of where they’re marching from. This will help you see where time is actively being spent as rows are produced. Below is an animated gif illustrating this (animation is in loop). As a step in the plan completes you’ll see the arrows turn solid rather than marching ants. This also shows the number of rows produced at each step (vs estimated number of rows). Time in seconds is also displayed.
You can see the scan of ExecQueryWaitStats completes fairly quickly (marching ants turn to solid arrow). This is followed by a sort with a merge join to the scan output of ExecQuerySQLHash. The estimated progress is displayed in the top left corner. I cut the animated gif short for the sake of brevity, but this is running long enough to illustrate how live query statistics work.
This feature is available in SSMS; however, the raw data can be collected on a production server on SQL 2016 SP1 or later with the sys.dm_exec_query_profiles DMV – this needs to be manually enabled on 2016 but is on by default on 2019 and later. Shameless plug, SQLGrease collects from this DMV. That goes beyond the scope of this post. Maybe I’ll write a post up on that at a later point. Happy tuning!
Thanks for the series and the many various posts. On this topic, readers wanting to find parts 1 and 2 may eventually find they are not readily available on your right nav bar. Would you consider adding a link (in the text or as a comment) to the two previous parts? I fear if I did that here, the comment might get blocked.
Also, I don’t find that you have a search feature for the blog. Am I missing it, if it’s there? I do realize smart people can use keywords in search engines to focus solely on a given site (site:blog.sqlgrease.com somesearchterm), but not everyone will know that (or see the tip I just offered). Would you consider adding a search capability, whether one of your own making (on your server) or one that just leverages such a search engine’s capability?
Just trying to help, not complaining. 🙂
I’ll add links to the other two posts. Feedback is appreciated. Thank you.