SQL Server 2016 is currently out in Community Technology Preview (CTP) and is due for release sometime between probably March – May 2016 at this stage. The actual release date will be made available by Microsoft accordingly. Like all new releases the product ships with new features that can assist us with our jobs managing our SQL Server environments. This release is not going to be any different. For this article we are having a look at the new feature Live Query Statistics (LQS).
In previous versions of SQL Server we have been able to investigate poorly performing queries using SSMS and including the Set Statistics IO On as well as Set Statistics Time On and turning them off at the end of the query being investigated. The outputs from these gave us an indication on the resource usage of each part of the query that we are investigating to assist along with looking at the execution plan to identify the parts of the query that are not optimal and could do with some work.
With the release of SQL Server 2016, Live Query Statistics has been included as one of the new features available to us. This new feature allows us to enable LQS inside of SSMS for a statement that we are wishing to investigate and it will show us in real time the % complete of each of the parts of the statement to allow us to quickly and easily see the most expensive part of the statement that we are investigating.
Talking recently with Rob Farley (Blog | Twitter) who has put out a post around investigating how you can get even more information around a statement that LQS is not able to provide, which you can read here … gave me the idea of looking at can this new feature work on previous versions of SQL Server. The result is yes it can with some caveats:
- SQL Server 2014 SP1
- SSMS 2016
If you are not able to upgrade your SQL Server 2014 environment to SQL Server 2016 once it has been released, you can still take advantage of this new feature that is available within SSMS to assist you in your performance investigations of potentially poorly performing queries to give you a quick insight into what parts of your query are not performing well and are in need of some attention. Have a look at the video showing you this working against an instance of SQL Server 2014 SP1.
Leave a Reply