• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

SQL Server 2016 New Features – Live Query Statistics

May 26, 2016 by Andy Hayes Leave a Comment

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{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de} 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! 🙂

Filed Under: All Articles, SQL Server Performance Tagged With: performance, sql server, sql server 2016

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

Primary Sidebar

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©