Performance Improvements with Live Query with SQL Server 2016

By | In SQL Server | April 26th, 2016

Performance ImprovementsNew capabilities get added with every single version that get introduced in every software. SQL Server 2016 is no brainer in this challenge. There are tons of great capabilities that get added in the area of performance tuning. Let me share a story about the same. Whenever there is a query which is executing for long time in SQL Server, one of the common question would be “where exactly the query is stuck”. In such situations, DBA used to check if IO or CPU is changing by querying various DMV. If they are, then all they can say is “things are moving”. With an enterprise class of software, this kind of question should be answered without any problem. Microsoft has heard this feedback and added an enhancement in SQL Server 2016 called as “Live Query Statistics”

As the name suggests, this feature would allow DBA/developers to have a look at current state of the query execution. If you have seen query execution plan in SQL Server Management Studio (SSMS) then you can easily imagine this feature as “live” query plan where progress / run-time statistics is shown for each operator in the query plan. This includes percentage complete, number of rows processed, elapsed time etc. This feature would help in doing performance tuning.

Here is a quick animation of what you would see:

Live Stats

As we can see above, we can view a live execution of the query plan. The operator values are showing current progress and continuously changing values until the execution of the query.

Enabling this is very simple. In SSMS, we can do the following

1. Click on the icon for “Live Query Statistics”

Live Query 1

2. Right click on the query area and choose “Include Live Query Statistics”

Live Query 2

3. Use SSMS Menu, choose Query and select “Include Live Query Statistics”

Live Query 3

For demonstration purpose, we have downloaded “AdventureWorks”, a Sample database from Microsoft download site https://www.microsoft.com/en-us/download/details.aspx?id=49502

Here is the query which takes around four seconds but gives you a good sense of how the feature works.

You will notice that while the query was running, SQL Server Management Studio immediately opened a new tab in the Results window called “Live Query Statistics”.

Live Query 4

If you want to look at longer query execution, then you can use this:

If you want to know how it’s working, then you can read about documentation of DMV dm_exec_query_profiles. We have captured the profiler and found below getting executed to get runtime information of a query.

Plan_handle can get fetched from sys.dm_exec_requests for corresponding session_id.

To summarize, the Live Query Statistics feature is a very useful addition to a developer’s toolbox. A live execution plan allows us to have a direct view of how the query is executing and to find out where the bottlenecks are. In the current release of SQL, there are some limitations, but hopefully they will be removed in the future. In the meantime, a DBA can now answer the question “where exactly is the query stuck?” with Microsoft’s new feature.

Contact Us
Eric Russo
Senior Vice President of Database Services
Eric Russo is SVP of Database Services overseeing all of Datavail’s database practices including project and managed services for MS SQL, Oracle, Oracle EBS, MySQL, MongoDB, SharePoint and DB2. He is also the Product Owner for Datavail Delta, a database monitoring tool. He has 21 years’ experience in technology including 16 years in database management. His management success and style has attracted top DBAs from around the world to create one of the most talented and largest SQL Server teams. He has been with Datavail since 2008: previous to that his work experiences include DBA Manager at StrataVia, Senior Web Developer at Manifest Information Systems and SQL Server DBA at Clark County, Nevada.

Leave a Reply

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