A Fine Slice Of SQL Server
Andy Brownsword tells his origin story:
I enjoy a good war story.
But no, this isn’t the time Azure blindsided me, when service accounts were locked, or when I left a transaction open and halted a production system (easily fixed by closing SSMS 😅). Mine is a pivotal personal moment.
Andy humbly leaves out the part where he was hand-to-hand kung fu fighting a series of ninjas while doing all of this, but you can just assume that in there.
Comments closedChad Callihan gives us a rundown:
I’ve written about a few outages and issues in the past and wanted to write about a different one for this post. One that came to mind was from awhile ago when a code deployment appeared to be successful on the surface, but in reality turned into a time consuming mess.
Click through for the story. This is why I strongly recommend always storing dates and times in UTC format in SQL Server, so using GETUTCDATE() and its brethren versus GETDATE() and co. You can always translate from UTC into the relevant time zone, you don’t have to worry about daylight savings time fouling things up twice a year, and you always have a known starting point, regardless of where your server is located. This ties in with DR scenarios: if your main server is on the US east coast and your DR site is on the US west coast and both are using GETDATE() to store times, you’ll see problems after a failover.
Naturally when there’s a failure, there are questions about High Availability solutions. If the customer has an Availability Group, maybe they’ve avoided an outage completely – the primary fails over to the secondary, and the race starts to get the original machine back in case another failure (on the new primary) happens. Those situations aren’t the ones that keep coming back to mind, but are still stressful for the temporary loss of the safety net.
The bigger issues are when there is no quick failover available. About thirty years ago, I was dealing with a SQL Server 6.0 environment, where the customer’s DBA was cycling tapes (yes, tapes) for differential backups, except that he’d reused the tape that the full backup was on. When that system went down, I had a quick and harsh introduction to salvaging files off the disks from a failed computer. Fun times.
Also, it sounds like Rob’s surgery from last month has gone well, so hopefully he remains on the road to recovery.
Comments closedThis is about the outage caused by a ransomware attack in October 2020 on my then employer, Steelcase, and the 2+ weeks required to get everything back online.
You can read more about the two-week halt of global order management, manufacturing, and distribution, as well as the fact that forensic investigation found no evidence of exfiltration, in this BleepingComputer article.
Disclaimer: I mention and link some products and companies in this blog post. This isn’t a sponsored post, and none of these companies have had a say in what I’m writing here.
Click through for Vlad’s story.
Comments closedAlexander Arvidsson tells a story:
The patient was a misbehaving SQL Server 2014 running in a two-node cluster. It was your garden-variety cluster with a shared disk and a remote witness. In my experience, as long as the customer has enough know-how to maintain a cluster like this, it’s essentially bulletproof.
This specific cluster was not.
Click through for the story, as well as a reminder of why people in mission-critical situations tend to follow checklists and say the items aloud.
Comments closedAaron Bertrand hits an outage in both directions:
The Stack Overflow database has a long lineage, and a lot more warts than what’s exposed in Stack Exchange Data Explorer (SEDE). Many of the core tables were created a decade before I joined the staff in 2021, and they grew far larger than could have been envisioned back then.
One of those tables is
UserHistory. This table uses an identity column as the clustered index and primary key, and records all kinds of information about each user’s activity on the site. From changing your profile picture, to earning a new privilege, to changing a preference, to something as simple as logging in or out. Each event generates at least one new row inUserHistory. At peak popularity, this table would grow quite quickly and, as the application became more complex, more and more categories of activity and state would get written there.
Click through for the story of an outage, and then another outage with a very similar shape.
Comments closedJeff Taylor tells a pair of stories:
It was a normal day at the start, checking on the servers, responding to email, then all of a sudden the office went dark and silent…we had just lost power!
Everyone started stirring and then getting up, checking that our phone system was on backup power, and someone called the power company to report it and get a status of when the power would be back on.
Extreme heat, smoke, and servers are not a great combination.
Comments closedJeff Taylor gives us the low-down:
A couple of weeks ago, I asked a simple question with a loaded answer: are temp tables a friend or a foe? The responses did not disappoint. They ran from full-throated defense to a measured “it depends,” and one of you built a lab. That’s what I was looking for.
If there is a consensus, it is this: the reflex is the problem, not the tool. Almost everyone agreed that dumping data into a #temptable out of habit is a mistake. Almost everyone also had a case where a temp table was the right answer, and sometimes the only one. So let’s get into it, in no particular order.
Click through for the cast of characters and what everyone came up with.
Comments closedAs with almost everything in SQL Server, there is no one-size-fits-all answer when it comes to temp tables. There are times where temp tables have rescued a query, and others where they’ve just caused trouble. And then there’s the times where you’re just scratching your head.
Can you use a CTE or a derived table in place of a temp table? In many cases, yes! But it doesn’t always work out well. When things get particularly complex, the SQL Server optimizer can struggle to find a good plan that doesn’t involve lots of table scans.
Read on for Andy’s thoughts.
Comments closedDeborah Melkin covers the gamut:
My stance is that temp tables are a useful tool when properly used, but they’re very easy to misuse to the point of creating problems. Table variables are even more misused for similar reasons; that could be a whole other blog post that almost starts and ends with “they’re not variables stored in memory but just regular temp tables without stats.”
Click through for examples in which temp tables have been quite helpful, and cases in which temp tables have been actively harmful.
Comments closed