T-SQL Tuesday #152: Consequences

Welcome to another edition of T-SQL Tuesday!

This month’s edition is hosted Deborah Melkin (b|t). Deb asks for each of us to share our personal infamous “rant” as we database professionals all have strong opinions about many things!

What’s Your Rant Andy?

Those of you who know me might think this post will wind up being about…

  • NCHAR/NVARCHAR vs CHAR/VARCHAR – aka Unicode or non-Unicode
  • Leading or trailing commas in SELECT statements?
  • All flash or spinning rust?
  • Coffee – with creamer or black?

But it won’t be about any of those! Instead, as I often like to do with T-SQL Tuesday themes, I’m going to take a slight twist and talk about what happens after you make a potentially controversial decision.

When X, then Y…

Consequences… ramifications… or afterclaps (my new favorite thesaurus-supplied alternate)… we’ve all had to pay the price of decisions that were made long ago. But sometimes we find ourselves having to make a less-than-desirable decision with only losing options to choose from.

I once worked for an Entity Framework SaaS company that was having horrific T-SQL performance issues. We put out a major release one weekend and spent the subsequent week buried in horrible performance hell. Because all T-SQL code was generated from Entity Framework (stored procedures were banned by a development exec prior to me joining the company), there were no easy fixes that could be implemented (and a Production release typically took 12 – 36 hours, I wish I was joking).

The manager of infrastructure had heard about SQL Server’s missing index recommendations:

  • Infra Mgr: Hey Ops DBA (I was a DB Dev). Are there any missing index recommendations in Production?
  • Ops DBA: Yeah, over 200.
  • Infra Mgr: Creating indexes doesn’t require us to do a Production Release. We can apply these overnight when client use is minimal.
  • Me: NOOOOOOO!!!!!

I did my best to explain why this was an absolutely horrific idea. But the Infrastructure Manager was desperate for any kind of relief from their performance woes and did not care to listen about how this could make things even worse. I was sidelined and the Ops DBA was ordered to script and implement each and every one.

And what was the afterclap? <giggle>

A couple of our key, gigantic core tables now had over 30 non-clustered indexes on them. Many of which were redundant and/or duplicate indexes as one might expect. A few queries were improved but backup times went through the roof, nightly ETL execution times doubled, and the Production database became horrifically bloated. A month later, I was finally given authorization to go through an index consolidation and clean up effort, which took me another month of workload analysis and index prioritization/consolidation.

… Then Y… but Why?

In the end, the Infrastructure Manager made a less than ideal decision, yes. But that is not what I have a problem with. What I have a problem with is that he refused to listen and understand the ramifications of the decision. He acted in a willfully ignorant manner, choosing not to listen to his subject matter experts, and instead grasp at straws in the hopes of resolving the issues. And the cost in the end was high in terms of cleaning up the bigger mess that was left behind.

Moral of the Story

So what’s the moral of the story here?

I want to encourage you to try and listen and understand the consequences of choosing X over Y. If you really want to save a few bucks and buy spinning disk instead of an all-flash SAN, understand and acknowledge the short term and long term consequences. And sometimes you do need to choose the less-than-ideal option. But if you must, I say do so with a full understanding of the afterclap.

Thanks for reading!


And now for something (sort of) entirely different… Part 2

About a month ago, Deb (b|t) wrote a blog about our newest adventure together: building a shared home lab! And now it’s my turn to blog about what we’ve done thus far.

A Bit of Background

I’m not quite sure how old I was when I built my first PC, but it was definitely greater than 5 and less than 10 years of age. In my 20’s, I had desktop builds that I ran at home as Linux web, DNS, e-mail servers, and file servers. Admittedly, as I got into my 30’s, self-hosting became less of a joy and more of a chore, so I retired most of that. But throughout the years, I’ve built many desktop PCs both for myself and others.

In more recent years, I’ve been using VMware Workstation for my lab needs. My current laptop is a fairly hefty hexa-core X1 Extreme, with 32GB of RAM and 2TB of NVMe SSD. It’s been great for my SQL Server lab needs, but with Deb needing a SQL Server lab environment beyond her laptop, it was finally time to build anew.

For our lab needs, our priority was at 128GB of RAM and at least 8-10 CPU cores for parallelism and running multiple VMs. I also wanted NVMe storage (since I’m still too new at Pure Storage to get my hands on a decommissioned FlashArray). I am fortunate to have a Micro Center near me, so I was planning on just sourcing parts and building a desktop machine to meet our needs. I had a number of friends offer their suggestions on Twitter and one privately offered me a used DL380. I hadn’t really considered a rack server, mainly due to space and heat concerns, and it would probably be overkill. While some might run their home labs 24/7, we’d probably only be turning ours on on-demand, negating the need for 24/7/365 durability and longevity. In the end, I opted to stick with building a desktop vs the DL380, mainly because it is what I was comfortable with and I’ve known PCs my entire life.

Hardware: So What’d Y’all Buy?

  • Intel i7-12700K Alder Lake 3.6GHz Twelve-Core CPU
  • ASUS Z690M-PLUS Prime microATX motherboard
  • G.Skill Ripjaws V 128GB DDR4 RAM
  • 2x Samsung 980 Pro 2TB NVMe SSD
  • Cooler Master ML240L Water Cooling Kit
  • Thermaltake Versa H17 mini-tower case
  • G.Skill 750w power supply

Software: So What Will You Be Running?

Now that we had hardware, the next question most think about is what operating system? Many would default to Windows but because this machine is intended as a home lab, the more important question became which Hypervisor? Do we remain with Windows 10/11 and keep using VMware Workstation for our VM needs? Or embrace Windows fully and learn Hyper-V? Considering so many organizations run their SQL Servers on VMware, I decided to do some research about a full-blown VMware installation.

Let me preface this by saying that I’m a VMware novice at best. I’ve encountered it almost daily for many years, but never had the opportunity to truly manage or work with it until I got to Pure. I’m familiar with general virtualization concepts, which I’ve used with VMware Workstation. But I’ll also admit that until very recently, I had no idea what the actual difference was between vSphere, vCenter, and ESXi (platform, management software, and hypervisor). And the other key thing I never knew was that ESXi is essentially a standalone OS (on top of Linux I think?). But I knew as part of this endeavor, I wanted to use it as a gateway to learn more VMware.

I started to research vSphere costs and discovered that there’s a vSphere Essentials Kit that’s reasonably priced and geared towards small businesses. I thought that that’d be perfect for us, even though the fact that it’s good for 3 ESXi hosts meant it was a bit of overkill for us since we’d only have one ESXi host. Then I discovered that there’s a FREE version of ESXi, good for a single host! The key drawback is that vCenter is not available. I figured I’d go with that, and if we ever had a need for vCenter functionality, we’d make the investment in the Essentials Kit.

Behold: We Have a Home Lab!

And with that, we now had a machine running ESXi. In my next blog, I’ll write about some of the first things I did, to get the lab operational and some of the hiccups I ran into along the way. Until then, thanks for reading!