SQL Server 2016 New Features – Live Query Statistics

SQL Server 2016 Live Query Statistics provide a way to look into the execution plan to see what parts of the query are currently running.

The advantage of this feature is that for a slow query, instead of having to wait for it to complete before the execution plan is available, it’s now possible to view the execution plan in real time.

Sometimes execution plans can be huge, increasing the effort needed to work out which parts of the query are performing badly. With Live Query Statistics enabled, seeing this data in real time helps the DBA or developer find the root causes of poor performance faster.

How to Enable Live Query Statistics in SQL Server 2016

There is a new button on the SQL Editor toolbar which is responsible to activating this feature when inside a new query window.

 enable sql server 2016 new features live query statistics

Then simply execute the query and the results will appear on screen. In the following screenshot, the query is still in progress and all operators have not finished.

sql server 2016 new features live query statistics in action

I have circled two of them and you can see that they are at different stages according to the percentages listed. Overall, the query is 40% complete as seen highlighted in yellow. There is data for the number of rows passing through the operator and for the time lapsed on that part of the query.

When the query finishes, you can compare this visualization with the actual execution plan to understand the cost of the components relative to the total cost of the query.

This feature can also be accessed via activity monitor under the “Active Expensive Queries”. Right click the query you are interested in and click “Show Live Execution Plan”

A very nice feature indeed! 🙂

SQL Server 2016 New Features – Query Store

enable sql server 2016 new features query store

In this series of posts covering the new features in SQL Server 2016, I'm moving on to looking at the Query Store. This tool provides a way to track query execution plan performance over time to help assist troubleshooting efforts. Figuring out … [Continue reading]

SQL Server 2016 New Features – Always Encrypted

always encrypted encrypt columns master key configuration

In my previous post, I listed a number of the SQL Server 2016 new features. I intend on writing a post on all of these and so from top to bottom, let's look at the first one in that list - Always Encrypted.   With Always Encrypted the key to … [Continue reading]

SQL Server 2016 – What Are The New Features?

SQL Server 2016 New Features

SQL Server 2016 will be made available to purchase by the public on 1st June 2016 and if we compare this schedule with Microsoft's plans of a major release every 2 years, this one comes 26 months after SQL Server 2014 which arrived on 1st April … [Continue reading]

How to Find I/O Usage Per Database in SQL Server

find io usage per database in sql server

If you're looking at performance and trying to understand I/O on a per database level in SQL Server, sadly there is not a report in Management Studio that you can run from those available. It will need some T-SQL to pull out this information from … [Continue reading]