Coming Soon SQL 2016 Live Query Statistics (LQS)

I recently stumbled onto a feature in the upcoming release of SQL Server 2016 that at first seemed really intriguing.  Live Query Statistics – a way to see the query plan and query statistics as a query runs. Digging a little deeper – seeing it in action – was I still as excited?  Somewhat.

Gaining access to a query plan, both the estimated and actual, is an incredibly helpful feature for the query tuner, database developer, and DBA.  For the developer it can help them refine their approach to a specific workload and put a scientifically, relationally sound query together to tackle a given problem.  For the DBA it can help identify problems in production after they have occurred by showing actionable areas for focus and refactoring.

What if you could take a query that is running right now and look at the actual query plan (not the estimated plan) as it unfolds.  Sounds kinda cool don’t it.  When I first looked into this “’16 feature” I immediately started day dreaming about watching a query run visually kind of like a visual SSIS package does.  I imagined watching row counts build, watching merges, loops, and joins happen real-time.  I pictured being able to look at reads and memory allocations with a visual plan as it all comes together.

For many of us, seeing something is believing, and reading numbers from a DMV smacks of effort and math.  I absolutely love Machanic’s sp_WhoIsActive, but I mean, come on, visually, you could really gain perspective on bottlenecks, weights, and true latency of areas that would far exceed “gut feel”.  In reality, my vision of LQS isn’t far off – as long as you don’t mind mouse hovering – like always however, there is a catch.

Live query statistics isn’t something that will just be available.  It has to be enabled prior to the query running.

Within SSMS LQS can be turned on just like estimated or actual query plans are now – on a query by query (session) basis.  Behind the scenes if you want this info you have to SET STATISTICS XML ON or SET STATISTICS PROFILE ON for individual sessions.  Or, for the entire instance, by enabling the extended event query_post_execution_showplan .

Per TechNet, this is likely something you’ll want to carefully consider as there is a lot of added overhead in making this insight possible server-wide.  The performance impact of turning this on, on a production server, could be significant.  So, my day dream of being a production DBA and logging onto a server with a current problem and bringing up it’s ongoing query plan probably isn’t a reality in most situations.

Is this still a cool feature ?? For the database developer, query tuner, absolutely.  If you’re lucky enough to have a good dev/test prod like environment, this could definitely be a very cool feature.  It has most of what my initial day dream concocted.  If you have a query that always rolls over and plays dead, then you can also use it there – but for those spur of the moment, what the heck is going on with query “A” type questions, where the dog will never do the same trick twice – it’s probably not going to come to your rescue the way existing tools, like sp_WhoIsActive for example, do.

You can see this feature in action right now by going over to the TechNet Virtual Labs.  This is where I started playing with it.  The TechNet virtual labs, by the way, are super cool.  Thanks for the resource Microsoft!!

Advertisement