Talking T-SQL (among Many Other Enterprise SQL Server Topics)

I’ve been producing Tech Outbound SQL Cruise events since 2010. These are week-long, immersive training events covering the complete Microsoft Data Platform: Azure, Machine Learning, AI, Power BI and, of course, enterprise SQL Server. The model is simple: instructor-led training while at sea; exploration and fun while in port. We sprinkle in a healthy dose of free consulting by those joining us as Technical Leads during “Office Hours.” We structure the week so that there are no lines between “attendee” and “trainer.” That extends to class time. The sessions are instructor-led, but they are treated as a discussion because one thing I’ve learned over my 20 years as a data professional (and 20 years as a parent) is that no one has all the answers, and everyone brings something to the table. 

We had such a great group of Technical Leads aboard the last SQL Cruise that I wanted to invite them back together to catch up and also get their opinions on the state of cloud adoption, T-SQL enhancements, the evolution of enterprise SQL Server performance tuning and mentorship, among other topics. Joining me in this discussion were Jes Borland, Premier Field Engineer at Microsoft; Itzik Ben-Gan, T-SQL Trainer at SolidQ; Kevin Kline, Principal Program Manager at SentryOne; and Jason Hall, Vice President of Product at SentryOne. (Buck Woody, Applied Data Scientist (among other responsibilities) at Microsoft was unavailable for our discussion, but I will be sitting down with him soon to discuss data science, AI and the importance of mentoring for a separate article.)

Tim Ford: Thank you for joining me today for this talk. Can you each take a second to tell readers a bit about yourself? 

Itzik Ben-Gan: I’m with SolidQ. I spend most of my time traveling around the world teaching about T-SQL. I’ve been working with SQL and databases since the early '90s.  

Jes Borland: I've been working with SQL Server for over 10 years. I started as a report writer, working with SSRS in SQL Server 2005. I transitioned to DBA work, then got into consulting, and now I'm a Premier Field Engineer for Microsoft. I love working with a variety of customers in different industries, on different environments, with different challenges. My main focus area is performance tuning, in both on-premises and Azure SQL Database. I've enjoyed watching the SQL database mature over the last five years, from v11 with limited functionality to the recently introduced Managed Instances. 

Kevin Kline: I started my IT career back in the ‘80s working on Oracle running on VAX/VMS and mainframe hardware using character-mode front-ends. In the early ‘90s I wrote my first book on Oracle development products for the first generation of OSes that had GUIs. I was part of the IT team that did beta testing on SQL Server when it was being ported to Windows NT. Since NT wasn’t GA, we had to do our beta testing on servers using OS/2 Warp, an IBM operating system. I’ve been intensely focused on SQL Server ever since then.  

Jason Hall: I have a long history with SQL Server. It started with SQL Server 6.5, when I was working as a systems admin and I inherited a financial application using SQL Server as a data store. It did not take long to reach the conclusion that the database was the backbone of the system. If the experience was poor for end users, it could usually be traced back to database performance. That was in 1998, and I've been on just about every side of the database server in some capacity ever since, until I landed at SentryOne (then SQL Sentry) in 2006.

Ford: Let’s kick this off with talking about performance monitoring. Are you seeing differences between the problems that used to be most prevalent versus what the landscape looks like today? Would you agree that solid-state drives, virtualization, hardware costs and cloud have made a considerable impact on alleviating some problems but surfacing others?

Kline: In the old pre-cloud days, the first emphasis of tuning in enterprise-class estates was heaviest on tuning server hardware and the I/O subsystem. Once hardware and I/O was ruled out, we’d then move on to diving deeper on database design, indexing and application code, including the SQL code. Hardware and I/O tuning have diminished in the last several years as virtualization, SANs and SSDs have grown increasingly common, and that doesn’t even touch on cloud providers. But a consistent set of bottlenecks in any scenario is the quality of database design, indexing and application code, like SQL tuning. In fact, with Azure and AWS, poorly performing SQL code and poor design cost you more money, since you pay for CPU, storage and data ingress/egress.

Hall: “I've seen this in three primary ways, and in this order:

Ford: That ties into my next question: What tends to be the differences you’re hearing about from clients or peers when it comes to performance tuning issues in cloud environments versus those on-premise?

Hall: In the cloud, we have less control over available resources and how they are used. The focus shifts largely to queries and indexing. In addition, with migration being such a big factor, for the next few years, we'll see an increasing need to understand what to migrate to and how to migrate and manage the spend once we're there. 

Borland: The biggest differences I see are in automation and monitoring. The tools for automation at many enterprises are very ingrained and automate across a wide variety of products. In Azure. There is a new set of tools, and there can be a learning curve. There also can be challenges getting existing monitoring tools to work or finding new ones to provide the same functionality. Both of these items involve change, which is difficult, but can be overcome.

Kline: There is definitely a paradigm shift occurring. However, the thing I always encounter is that the basics of our business as data professionals are still hard. So many new people come into our industry with little or no practical training or experiences. As a consequence, we never run out of poor database designs to amend, bad indexing to adjust and oh-so-much SQL code to tune. These aspects of performance tuning will always be true in the cloud, as well as in on-premise applications.

Ford: Let’s talk cloud adoption since the conversation has moved that way. Do you think the shift toward cloud is finally accelerating? Have we hit the tipping point?

Borland: I think more companies are seeing the benefits of cloud services and understanding that the infrastructure and security are solid. I don't see companies going all-in to cloud services, but many aspects of it can solve business problems. A hybrid approach is best for most organizations. There are more tools to transition existing infrastructure and applications to the cloud than ever before. By taking away the need to manage infrastructure, we're enabling companies to focus on solving problems instead.

Hall: 2018 is the first year that I have first-hand knowledge of large enterprises handing down an edict to be all cloud by EoY 2019.

Ford: One last question about cloud before we move on: Do you have any recommendations for readers who may be looking at (or tasked with) a lift-and-shift to the cloud?

Borland: My general guidance is that a lift and shift of existing applications is going to work best if you use IaaS options, and new applications that can be designed within the parameters set are going to succeed with PaaS. Existing applications are often written to use the full feature set of SQL Server and have been tuned to perform well using the traditional physical or virtual hardware. These same options are available when using SQL Server in a VM. However, PaaS options like SQL Database offer a limited set of features because they are designed to be data stores, not full servers.

What's really exciting is new features that blend these options together, like SQL Database Managed Instances. Managed Instances allow a SQL Server database to be restored into it and offer almost 100 percent compatibility with features. On top of that, there are the PaaS benefits such as automatic backups, fully integrated high availability, easy scaling and cloud-first development. It's the best of both worlds!

Hall: What I've heard from many is that the financial benefits of moving to cloud are not truly realized until you are able to take advantage of more specialized PaaS offerings. If cost is a big factor in your move, don't stop with a simple lift and shift. If you do you might be unpleasantly surprised. Make sure you right-size for the workloads, and plan to architect applications toward those more specialized services in order to gain the most benefit.

Ford: We haven't talked yet about the language of Microsoft SQL Server: T-SQL. Itzik, you’re recognized as the pre-eminent expert on T-SQL. What is it about the language that keeps it interesting for you?

Ben-Gan: I love logic puzzles, and T-SQL querying tasks are in essence logic puzzles. Add to this the fact that a T-SQL challenge is not just about producing the correct result, but there’s also a performance dimension. Tt makes the challenge even greater. You can keep improving and polishing your solutions over the years. It is an area where constant practice pays off. There’s also a lot of room for creativity and self-expression.

As for a specific enhancement that really stands out to me, it is window functions. They are much more profound than many people realize. They’re not just about data analysis calculations; rather, they are a much more general-purpose tool to solve querying tasks, as an alternative to the traditional predicate-based solutions. Nowadays I use window functions in many of my more optimal solutions. If you’re not already using window functions, make sure you get to know them well. After years of working with them, I keep finding new, efficient and creative ways of using them.

Specifically, you can solve many challenges using running totals, as an indirect means to an end. For example, you can solve many temporal querying challenges involving intervals with running totals. Also make sure you understand how window functions get optimized. This area improved significantly recently with the introduction of batch-mode optimization in SQL Server 2016, and batch mode on rowstore in SQL Server 2019.”

Ford: Itzik just touched on the performance aspect of window functions, and each of you has mentioned monitoring in one way or another. Where do you see the future of performance monitoring?

Hall: It's definitely in machine learning and advanced analytics. I'm not talking about an easy button. I don't think we'll get to "run this and it goes faster" in the near future, but the next frontier is removing the manual analysis that has to be done by data professionals now. Help them surface what is normal, what normal means, and, most importantly, what was recently or is currently not normal. Data professionals have what they need to know to solve problems. The future of performance monitoring lies in leading them to the root of problems faster, or even before problems manifest.

Kline: The industry is bifurcating, splitting in two between those who understand the cost of doing business properly and those who do not. Most sensible people wouldn’t even consider starting a new and important business without insurance or buying an expensive piece of equipment without a warranty and service contract. Some people do that, and they almost always end up regretting that decision. Performance monitoring systems do the same sort of things as insurance and warranties, except for applications and databases. One thing I didn’t see much in the past, but I’m starting to see a few times per year now, is that DBAs and admins who fail to implement performance monitoring are literally being fired when their company experiences a significant outage without warning because no monitoring was in place. Companies are realizing that they are at risk of losing massive amounts of money due to an outage, in fact sometimes only after an outage. What I’m seeing in the market is that companies who want to beat their competition and protect themselves from serious risk always implement performance monitoring.

Ford: This seems like a good time to shift the conversation away from the hardware and software of our industry to focus on the most important aspect: the people that do what we do. I’m an accidental DBA myself--coming to this as a second career. What advice do you have for those perhaps just starting their own careers with the Microsoft data platform?

Ben-Gan: Data becomes more and more important and integral to the success of most businesses. Yuval Noah Harari depicts this well in his book "Homo Deus." The fact that you chose this field is already a smart move.

It’s hard for me when I’m asked to give career advice because I don’t know if what worked for me would work for others. But I do remember the advice I listened to: Pick something that you really relate to, and master it. You will enjoy what you do, and you and your work will become valuable to others. I have learned from many. But there are two mentors who stand out. My boss from my first job as a data expert right after the army, Gilad Gordon taught me a lot of technical things, but what I really appreciated was his selflessness in doing so. I also learned a lot from his positive approach to solving problems. Another mentor was Yehuda Pantanowitz. I used to practice Okinawan Goju-ryu, and he was my teacher. I admired his dedication and the clear way in which he saw things. I remember telling him how I got nervous and excited when speaking in front of large audiences, and he suggested that before I went up on stage I tell myself, "It’s just another day in the office." It sounds like a small, simple thing, but it really worked well for me.

Ford: Kevin, you’re held in high regard in the SQL Server community as a leader, teacher and mentor. Could you recommend one or two non-technical things the typical data professional should focus on improving?

Kline: Two quick thoughts here. First, invest in deliberate practice. Many of us are inquisitive, and we like to learn, so we’re often studying, reading blogs and keeping up with the latest news about SQL Server. But that’s not truly what I mean when I include the word deliberate. A better analogy is with world-class athletes. Athletes today spend a lot of time examining their strengths and weaknesses and, with their coaches, practice skills that are most important with a mind toward achieving a new level of performance. It’s a process: Assess yourself to find your strengths and weaknesses; practice one skill in each of those categories; track that practice against a specific measurable goal. Lather, rinse, repeat. The second bit of advice is find a mentor. This is a person whose career you would like to emulate, on both a professional and interpersonal level. Look for someone who is not only a technical master of the trade, but someone with political savvy and soft skills. In most career trajectories, the hard skills get you the job, but it’s the soft skills that get you the promotion. Look for someone who will help in both regards. And if you’re more advanced in your career, try to mentor others. It’s by teaching others that we are most able to clarify and internalize what we already know.