SQL PASS Summit 2015 | TroubleshootingSQL

Sourabh and I completed our 24HOP session in the morning (or at least my morning… night, afternoon or evening, depending on which part of the world) you live in. A big THANK YOU to everyone that attended the session and we had a ball presenting the session. We have a little over 200 people attending the session and we hope see a good number of these during our pre-conference session at PASS.

The Summit gives you an opportunity to attend sessions from technical experts across the world and is the largest gathering of Data Platform Professionals in the world. To me SQL PASS Summit is a great way to connect with the SQL Family with whom I only have virtual conversations throughout the year. If you want to do deep-dive of sessions on various things in the SQL Server space, then you have a line-up of pre-con sessions.

There will be a contingent of engineers, program managers and Microsoft Services folks at the SQL Server Clinic to help answer your queries regarding SQL Server. So, don’t forget to stop by the SQL Clinic in case you have a question that you have been meaning to ask for a while!

Apart from all the nerdy and geeky overdose, you have the option of painting the town red during the week that you are in town and attend social gatherings after the sessions. There is always more than one happening and you get to take your pick! It was definitely fun when I attended it last time and it’s going to be fun this time as well. If you are attending for the first time, then brace yourself for an action-packed, geeky overdose and fun-filled SQL adventure! And don’t forget to check out the first-timers page on the Summit website. If you are returning attendee, then you know what I am talking about! J

Sourabh Agarwal [b|t] and I are going to be presenting on How to be a Ninja: Troubleshooting SQL performance on Azure Virtual Machines [DBA-399-PM]. This is a demo packed full day session which will cover a plethora of stuff related to SQL Server. To name a few:
1. Automation deployment on Azure Virtual Machines for SQL Server instances
2. Best practices for using SQL Server on Azure Virtual Machines and some of these apply to on-premise SQL Server instances as well. We will document the differences between the two based on our vast experience working on issues with Microsoft customers. We have gathered working for different teams within Microsoft specializing in SQL Server.
3. Troubleshooting performance issues. This is where you will gain your Ninja status! We have made a career within Microsoft on troubleshooting complex SQL issues. We will show you how to troubleshoot performance problems on SQL Server instances which applies to Azure VMs, on-premise SQL Server instances and virtualized environments.
4. We will also give you a preview of some of the enhancements done for SQL Server deployments on Azure by the SQL Server product group at Microsoft.
5. And it is about having fun…. We will definitely give you a thing or two to laugh about as well.

A screenshot of our agenda slide for the pre-con is available below. We also have a bunch of Easter eggs that we will crack open in the session. Psssst.. Some of the stuff is still under works and should be out of the door when we present!

When: 27^th October

Time: 8:30am – 4:30pm PST

Where: Washington State Convention Center

Session URL: http://www.sqlpass.org/summit/2015/Sessions/Details.aspx?sid=38085

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.

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.