[Note from Pinal]: In this episode of the Notes from the Field series database expert Mike Lawell explains about Live Query Statistics in SQL Server 2016. Statistics are heart of SQL Server. They play a very powerful role when SQL Server Engine needs help to build a new execution plan for a query. In earlier version of SQL Server we had the only privilege to see statistics after the query is executed. However, with the advancement of SQL Server 2016, the scenario has been changed. Here is a very interesting new feature of SQL Server 2016 which can help every performance tuning expert. Read the experience of Mike in his own words.
Your boss has asked you to implement SQL Server AlwaysOn Availability Groups in your environment. Exciting as it may, you’re an accidental DBA with little to no experience implementing any high availability solution, let alone, a fairly new availability group.
The first thing you say is, fantastic! Then, hmmm, fantastic. You realize this is above your head, but it is a great opportunity to learn.
This happened to one of my clients, where the DBA barely had SQL Server Database Mirroring experience. The product had a required up time of 99.999% (yeah, right), that they were having problems maintaining. They wanted to improve their availability and business continuity.
The DBA had a lot of anxiety with implementing AGs in this environment without experiencing considerable downtime.
Fortunately they were able to engage our servic
Welcome to 2016! This is going to be an exciting year for SQL Server professionals with the upcoming release of SQL Server 2016. With this in mind I’m going to talk a little bit about one of the cool new features in SQL Server 2016.
Many of you have had the opportunity to see me talk about one of my favorite topics, Execution Plans. I had the privilege to talk about Execution Plans at PASS Summit 2015, and many SQL Saturdays across the US. You can find my presentation here on my blog site at SQL Server Associates.
Keeping in step with my love of Execution Plans, Live Query Statistics is one of the new features in SQL Server 2016 that gives me a smile.
Live Query Statistics? What’s the benefit in that? I’ve already heard it from some senior DBAs I know. Have you ever had someone ask you to performance tune a query that never completes? It just takes forever and you know you’re not about to wait for it to complete before you can see the actual execution plan? Well, here’s your chance to look at the operators in progress and as they complete.
Live Query Stats even gives you the ability to see a percentage of completion as the operator is in progress. More about that later.
Here is how you enable Live Query Statistics from Management Studio. Select Query > Include Live Query Statistics.
Voila, there you have it. Now it is as simple as running your query.
This screenshot shows the query in process. Notice the first operator “Clustered Index Scan” has completed (100%). The other operators are at 8% or 0%.
Now here is the best part, hover over the operator that is currently in process, the one you think is the issue. Even though it is “in process” you can see the details about the operator. Notice the Actual Number of Rows information? There is a lot of information here to give you a start on identifying where and what the issue might be with the query.
Once the query has completed you get an execution plan that looks like the screenshot below.
And there you go. So simple my mom could take advantage of it. Of course mom would be saying, “Michael Mark, you had better not be lying to me. It can’t be that easy!”
Really mom, it’s that simple and it is going to help you performance tune like a pro…oh, wait, sorry mom, performance tuning is a little more complicated.
Wait, but there’s more…. In what versions does this work? Well, of course SQL Server 2016…duh… but wait there’s more!
Yep, that’s right! I just ran Live Query Statistics against a SQL Server 2014 server. Isn’t that awesome?
I’ve been able to use this new feature with several of the Linchpin People clients (even though SQL Server 2016 isn’t available yet) because it works on SQL Server 2014 installations. It just doesn’t get any better than that.
If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
