SQL Server 2016 Public Preview (CTP2) – Live Query Statistics

Posted on June 30, 2015 by Amit Banerjee

I have spent a lot of time at customer locations working on resolving SQL Server performance issues on business critical environments. This also involves helping customers understand how to track down the performance bottlenecks and the remediation steps that need to be taken to remove identified performance bottlenecks. This involves two kinds of troubleshooting: post-mortem analysis and troubleshooting the issue as and when it is happening! Yes, I am talking about live troubleshooting which is a scary thing to do on a production server.

So if you share my deep rooted passion for working on SQL Server performance issues, the Live Query Statistics feature in SQL Server 2016 CTP2 is definitely worth knowing more about!

The Live Query Statistics can be enabled in one of the following ways:

1. Using Management Studio

The screenshot below shows the button which enables the Live Query Statistics. This can be a very powerful tool to troubleshoot bad query performance while the query is actually executing. You actually get insights into the plan and which part of the plan is actually taking time while the query executes.

Live Query Stats button on toolbar

You get a host of information from the Live Query Statistics as seen in the screenshot below. You will be able to pin point the part of the plan which is the culprit because you will have the completion percentage statistics for each and every operator in the plan. The completed operators show you the efficient parts of the plan. Additionally, you also get the time spent in each operator which gives you statistics for identifying the most time consuming part of the plan. And what’s more, you even know how much of the query is completed. This is one of the most common questions that I used to receive from customers while troubleshooting long running queries: “How long will the query take to complete“? Well now, there is an answer!

2. Use Activity Monitor

A new grid has been added in Activity Monitor named “Active Expensive Queries” which allows you to right-click a query and click on the “Show Live Execution Plan” option. Live Query Stats button in Activity Monitor

However, the “Show Live Execution Plan” option will only be enabled if the application or user:

Executes SET STATISTICS XML ON; or SET STATISTICS PROFILE ON; in the target session.
The query_post_execution_showplan extended event has been enabled. This is a server wide setting that enable live query statistics on all sessions

And if you are developer, then you can use this feature in conjunction with the Transact-SQL debugger and pin point slow parts of the execution plan while the query is running. A truly powerful way to write and optimize queries! The debugging experience is now enhanced as the live query plan can be used along with breakpoints! The screenshot below shows what the debugging experience would look like.

Do keep in mind that this feature is provided for troubleshooting/debugging slow running query scenarios and should not be kept enabled in production environments. There is a moderate performance impact for collecting and displaying the above mentioned statistics.

Reference:

Live Query Statistics
https://msdn.microsoft.com/en-us/library/dn831878.aspx

Disclaimer: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

SQL Server 2016 Public Preview (CTP2) – Deploying to Azure VM

Posted on June 10, 2015 by Amit Banerjee

I had written a post earlier on deploying a SQL Server instance on a Azure Virtual Machine. Now that SQL Server 2016 CTP2 is out, let’s see how that looks on Azure. The wizard is the same as before but a new gallery option exists for deploying SQL Server 2016 CTP2. The catch is that any virtual machine created with this gallery image will expire on June 30th, 2016. The locations where this image can be deployed are East Asia, Southeast Asia, North Europe, West Europe, Central US, East US, East US 2 and South Central US. The gallery image gets provisioned with a single disk.

After the deployment is complete, you will need to enable connectivity for your SQL Server database engine as outlined in an earlier post of mine. What you get is the default instance of Database Engine, Analysis Services, Integration Services and Reporting Services. The deployment will not have the “PolyBase Query Service for External Data”. So if you are planning to test the PolyBase options in SQL Server 2016, then you will need to run the installation from the C:\SQLServer_13.0_Full folder. The other feature that is not available is the Distributed Replay. So, if you are planning to play around with these two features, then you would need to run the installer again.

Another feature which the gallery image does not use is the tempdb multiple file option setup parmater, “SQLTEMPDBFILECOUNT“. This is left at 1 so you will end up with the default tempdb configuration which you saw in the older releases. I would recommend using a virtual machine instance which has a SSD drive as the temporary drive so that you can use a SSD for testing out any intensive workload which requires either high tempdb usage or a local disk which supports high IOPs.

So now you have any option to play around with SQL Server 2016 CTP2 without having to hunt down a separate virtual machine or physical box in your environment.

SQL Server 2016 Public Preview (CTP2) – sys.dm_exec_query_stats

Posted on June 5, 2015 by Amit Banerjee

In any new release of SQL Server, one of the features that I first explore is the DMV enhancements. In SQL Server 2016 CTP 2, 24 new columns are added to sys.dm_exec_query_stats (Transact-SQL) provide information about memory grants and parallel thread usage!

I have documented in the table below the equivalent XML nodes from the execution plan which are now available in the DMV output. This saves you a lot of time from tracking down each and every plan and then shredding the XML to get the relevant information! I had documented how to parse the XML plans in an older post of mine.

Column Name	Comments	Equivalent XML Execution Plan Node
total_dop	This information is available in the XML plan and now without having to parse XML, you will be able to get this information using direct SELECTs to a DMV.	<QueryPlan DegreeOfParallelism=”4″ MemoryGrant=”28968″ CachedPlanSize=”128″ CompileTime=”9″ CompileCPU=”9″ CompileMemory=”832″>
last_dop
min_dop
max_dop
total_grant_kb	Again you are saved from XML parsing and this information lets you get statistical averages of the memory grants per execution.	<MemoryGrantInfo SerialRequiredMemory=”7168″ SerialDesiredMemory=”7392″ RequiredMemory=”28744″ DesiredMemory=”28968″ RequestedMemory=”28968″ GrantWaitTime=”0″ GrantedMemory=”28968″ MaxUsedMemory=”4384″ />
last_grant_kb
min_grant_kb
max_grant_kb
total_used_grant_kb	Again this is something that you can fetch from the XML execution plan but it is available in the DMV now. This lets you check how much of the granted memory is being used during execution.
last_used_grant_kb
min_used_grant_kb
max_used_grant_kb
total_ideal_grant_kb	This is the a good field to look at to check if there was a difference in the ideal and the actual granted values. If this is an abnormal value, then this warrants investigation.
last_ideal_grant_kb
min_ideal_grant_kb
max_ideal_grant_kb
total_reserved_threads	This is quite useful when tracking down queries which have a high number of worker threads usage	<ThreadStat Branches=”1″ UsedThreads=”4″> <ThreadReservation NodeId=”0″ ReservedThreads=”5″ /> </ThreadStat>
last_reserved_threads
min_reserved_threads
max_reserved_threads
total_used_threads
last_used_threads
min_used_threads
max_used_threads