New 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:
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”
2. Right click on the query area and choose “Include Live Query Statistics”
3. Use SSMS Menu, choose Query and select “Include Live Query Statistics”
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.
use AdventureWorks2016CTP3 SELECT * FROM Sales.SalesOrderDetail d ,Sales.SalesOrderHeader h WHERE h.SalesOrderID = d.SalesOrderID
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”.
If you want to look at longer query execution, then you can use this:
SELECT ProductKey ,de.FirstName ,dd.CalendarYear ,dd.CalendarQuarter FROM FactResellerSalesXL_PageCompressed f ,DimEmployee de ,DimDate dd WHERE f.EmployeeKey = de.EmployeeKey AND f.DueDateKey = dd.DateKey
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.
SELECT * FROM sys.dm_exec_query_profiles WHERE plan_handle = 0x06000800AC4A452FA0D8B99D79000000010000000000000000000000000000000000000000000000000000 AND session_id = 63
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.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.