If you are looking to upgrade your SQL Server replication topology, then you can look at the post that I published on the Tiger blog.
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.
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.
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.
Live Query Statistics
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).
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.