24HOP Session: How to be a Ninja – Troubleshooting SQL PERF on Azure VMs

Posted on August 26, 2015 by Amit Banerjee

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 17^th at 8AM PST (3PM GMT)
WHERE: ONLINE
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

[UPDATE] September 29th, 2015

Thank you for the feedback that you shared after the session. It is always great to know what people liked in the session and even better to know where we need to improve. This helps ensure that our next iteration has the necessary tweaks. We received an overall 90% positive feedback and we thank everyone who attended for that!

The replies to the questions from the session are available below.

Q. Regarding the performance fixes as best practices(hotfixes/CU), do we have separate hotfixes(.msi/msp) for azure environment when compared to on premises environment?
A. The SQL Server installation bits that you would run on Azure VMs and on virtualized/physical on-premises environment are the same. So there aren’t any different set of fixes that need to run on Azure VMs.

Q. Are these Cheat Sheets available online?
A. The cheat sheets are available in the presentation PDF on the 24HOP site.

Q. Is using "Lock Pages in Memory" lead to that total allocated memory amount of SQL Server process is not seen in Windows Task Manager?
A. Task manager is not a good place to look for allocated memory when you want to find out allocations made after enabling Lock Pages in Memory privilege for the SQL Server service account. You could either look at Total Server Memory perfmon counter or the memory DMVs to track SQL Server memory usage. Additional reference: https://msdn.microsoft.com/en-us/library/ms176018.aspx

Q. Why are you disabling caching on the log file drive?
A. This is due to the IO patterns that the SQL Server transaction log file receives and how Azure storage is structured. We have seen in tests that the performance for SQL Server transaction log is best when write caching is disabled for disks which hosts transaction log files. We will talk about this in detail during our pre-con session.

Q. For Datawarehousing workloads, do you recommend lock pages in memory setting on on-premise/azure VM hosting SQL Server?
A. For on-premise workloads, we recommend you test and ascertain the needs before enabling Lock Pages in Memory (LPIM) privilege. For Azure VM workloads, the first important task is to pick the machine with the right SKU. We recommend enabling LPIM to prevent paging to the local disk on the rack which can negatively affect performance.

Q. Why are there different storage options based on Windows version? Is there any dependency on SQL versions?
A. There aren’t different storage options based on Windows version. The different storage options are based on the performance tier that you want to be on. It is Windows and SQL version and release agnostic.

Q. Can you let me know the resources on Azure Storage?
A. The Azure storage documentation is a good place to start for this. We will talk about this in detail in the IaaS introduction part of our pre-con.

If we have missed any question, please leave your question in the comment section of this post and we will answer it.

Lastly, we loved the notes that Matt Penny [t] took during our session. A screenshot of that is shown below. Thank you Matt! J The 24HOP session presentation is attached on the session page.

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

The DMV output with the new columns is shown in the screenshot below.

There are a number of views which have been added for supporting the new Query Store feature which are:

sys.database_query_store_options
sys.query_context_settings
sys.query_store_plan
sys.query_store_query
sys.query_store_query_text
sys.query_store_runtime_stats
sys.query_store_runtime_stats_interval

Additionally, two new views have been added for supporting the new row level security feature which are:

sys.security_predicates
sys.security_policies

More details about the above views in a later post!

Reference:

sys.dm_exec_query_stats (SQL Server 2016 CTP2)

SQL Server 2016 Public Preview (CTP2) – Let’s see how it looks

Posted on May 28, 2015 by Amit Banerjee

The Public Preview of SQL Server 2016 is available for download on the Microsoft downloads site. The feature highlights are available in this blog post. I thought I will give blog about what’s different in the setup of the Database Engine.

The basic screens remain the same. The screen to select the edition is the same and the there is no change in the “License Terms”, “Global Rules”, “Product Update”, “Install Setup Files”, “Install Rules” and “Setup Role” pages.

The first major change that you will notice is in the “Feature Selection” page. This is where you will notice a change. Namely, the “PolyBase Query Service for External Data“. This installs two services:

1. SQL Server Polybase Engine Service which creates, coordinates and executes the parallel query plan against external data sources.

2. SQL Server Polybase Data Movement Service which manages communication and data transfer between SQL Server and the external data sources which will be used by the instance.

Note that these services are not instance aware and like Integration Services, there can only be one service per Windows host.

The next screen where you will notice a change is the Feature Rules page which checks if Oracle JRE 7 Update 51 is available or not. This is required for the PolyBase service since I had selected that in the previous screen.

If you don’t have the requisite Oracle JRE version running, then you will be prompted with the following message:
Rule “Oracle JRE 7 Update 51 (64-bit) or higher is required” failed.

This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from http://go.microsoft.com/fwlink/?LinkId=526030.

The forwarding link will take you to a webpage where you can download the latest JRE update. Once that is done, you will be able to continue with the installation. Another interesting part was the check for KB2919355 which is the Windows 8.1 Update which contains a cumulative set of security updates, critical updates and updates. So the setup has an OS related check as well.

The Server Configuration page allows you to choose the service accounts for the two PolyBase services.

The Database Engine Configuration page comes with a nifty tempdb file configuration which has a bit of learning to do but provides the opportunity to create additional tempdb files during the installation itself.

Once the setup is complete, you can connect to the database engine using Management Studio and you will see that your version number is 13.0.200. Also, you will notice that there are three user databases: DWConfiguration, DWDiagnostics and DWQueue which is related to the PolyBase service. More on that maybe in a future post.

So that was all about the Database Engine setup for the new SQL Server 2016 Public Preview release.