I have been a bit quiet on this blog but that is partly due to the fact that I have moved into a new role and a new country. I am now a part of the SQL Server Product Group [b|t] and based out of Redmond.
I am actually really excited about this. Sourabh Agarwal [b|t] and I are going to be presenting a preview to our SQL PASS Summit 2015 pre-con session “How to be a Ninja: Troubleshooting SQL performance on Azure Virtual Machines“. Yes, we are starting the initiation program of becoming a SQL performance troubleshooting NINJA on SQL Server!
Troubleshooting is an art but the tricks of the trade changed with the advent of Azure Virtual Machines. Performance troubleshooting is different and at the same time very similar to what you have been used to for SQL Server. SQL Server performance on Azure VMs can be a sore point for many as the host troubleshooting entry points are limited and the knowledge of the internal workings scarce.
In this session, we will show you what best practices should be known for SQL Server instances running on Azure Virtual Machines! We will talk about tips on automating the implementation of all these best practices during deployment making this a single one-click deployment. This session will be a pre-cursor to our pre-con where we will go the whole nine yards and detail how to automate deployments from scratch, implement best practices automatically and analyze performance issues magically!
We hope you can join us for this session online and we do hope to see you during our pre-con! The 24Hop sessions are full of great sessions from great speakers in the SQL Family. See the full list here. I would recommend looking through the list and signing up for the ones that you are interested in. This will also give you a preview of what you can expect in the SQL PASS Summit this year.
This 24 Hours of PASS: Summit 2015 Preview event takes place over 24 hours, beginning September 17, 2015, 12:00 UTC. Featuring 24 webcasts delivered over 24 hours, this event provides a glimpse into the unparalleled content on offer at PASS Summit 2015, October 27-30, in Seattle, WA.
WHEN: September 17th at 8AM PST (3PM GMT)
Facebook Event for our session: https://www.facebook.com/events/938656286172663/
Registration link for the event: http://www.sqlpass.org/24hours/2015/summitpreview/Registration.aspx
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.