A Fine Slice Of SQL Server
A cyber-criminal on the run, a wingsuit jump between planes at 5,000 meters, and 24 million flight records to sift through. This is the most cinematic KDA case yet.
Click through for a rather extreme scenario and how Tom was able to solve it.
Leave a CommentJordan Boich administers a database in a distributed availability group:
I’ve seen a lot of great blog posts and content out there talking about Distributed Availability Groups (DAGs). However, I haven’t come across much of anything covering the actual administration of them. Especially for those choosing to use a DAG as their Disaster Recovery (DR) strategy. If you’re choosing to use a DAG for your DR, then you need to know how to perform basic administration. The goal of this post is to cover the gaps in knowledge out there, and cover the nuances of DAG administration as the methods and steps reveal nuances that normal Availability Groups do not necessarily present.
This post is going to cover how to remove and add a database from a Distributed Availability Group. Removing a database from an AG and having to add it back is a common maintenance or troubleshooting step that many DBAs face. But when it comes to DAGs there are some extra nuances and steps you have to keep in mind depending on if you’re working on the global primary AG or the forwarder.
Click through for the steps, as well as some of the pain points you might run into along the way.
Leave a CommentNiko Neugebauer concludes a series on in-memory OLTP:
Over the years, I have contributed with a reasonably big number of posts on the topic of In-Memory OLTP (aka Hekaton) and I did a couple of focused presentations on this wonderful feature as well. I love the idea, I love the path where the feature was going, and call me wrong* and overall I think that if the leadership team of Microsoft Data Platform understood the potential, it would have grown much bigger and more impactful.
Today I am writing the last post on Hekaton, for the foreseeable future. For me its journey has come to an end. The Odyssey has ended. With the arrival of SQL Server 2025, we have finally received a functionality of removing the In-Memory Filegroup from the database. The introduction of this functionality is closing the loop after 11 years since Hekaton introduction.
I agree with Niko. The promise was considerable but the actual implementation was frustrating, especially if you had cross-database queries. At the time this feature came out, the company I worked for could have made great use of it, except that a majority of queries we had used replicated lookup data that existed in a separate database. As a result, we were able to make good use of memory-optimized temp tables for table-valued parameters, but not much else.
Leave a CommentHugo Kornelis looks into one of the newest types of index available in SQL Server:
It’s time to continue the series on internals of storage structures. I already covered the more standard storage types in the parts about on-disk rowstore, columnstore indexes, memory-optimized storage, and memory-optimized columnstores, and in the last episode of this series I started to cover specialized storage structures by looking at XML indexes.
Microsoft introduced limited JSON support in SQL Server 2016. However, it took until SQL Server 2025 before the native json data type and JSON indexes were introduced. So let’s look at how these work under the cover.
Click through to learn more about how SQL Server stores the contents of a JSON index.
Leave a CommentVlad Drumea does some sleuthing:
In this post I dig through some data that Shodan provides for internet-exposed SQL Server instances, their versions and locations.
I occasionally check Shodan for SQL Server instances that are publicly exposed on the internet for anyone to poke at. I make a LinkedIn post about it (like this one) and then just leave it at that.
So, this time, I’ve decided to dig a bit more into that data and document it here.
Click through to see what Vlad was able to find.
Leave a CommentGarry Bargsley runs the numbers:
Today’s post comes from one of those real-world DBA situations where someone hands you a folder full of SQL Server backup files and asks what should be a simple question:
“How much disk space do we need to restore these?”
Simple question.
Not always a simple answer.
Click through for a script that calculates how much disk space you’d need to restore a given set of backups.
Leave a CommentI plan to install several SQL Server instances across different servers. On all servers, I would like to use the same accounts for the SQL Server services for easier management. I’ve heard there are Group Managed Service Accounts (gMSA) that can be used as SQL Server service accounts, for example for the database engine service and SQL Agent service. How can I configure gMSAs?
Click through for a good solution to a security problem that becomes all the more important when you need high availability.
Leave a CommentAaron Bertand only likes the right kind, by which I mean the left kind:
The headline is probably unfair and is not meant to imply that a RIGHT OUTER JOIN is wrong. But when I see a RIGHT OUTER JOIN, my first thought is, “the rest of this review will probably be harder than it needs to be.” I find that it makes queries harder to read, because most people naturally read queries left-to-right. With a left join, the “important” table is on the left, and the query is saying, “give me everything from this table, and maybe something from this related table.” With a right join, I have to mentally flip things around to understand which table is important.
Aaron is absolutely right about left-handed scissors. I extend this as well to can openers and a half-dozen other tools that fit very well in the right hand but are quite awkward for southpaws. And don’t get me started on writing.
Aaron is kinder toward RIGHT OUTER JOIN than I am. I’m not convinced there’s ever a reason that we should use RIGHT OUTER JOIN. LEFT OUTER JOIN is much easier for humans to interpret, and (save for very specific scenarios) we should optimize code for human interpretability over pretty much anything else. And yes, that includes (again, save for very specific scenarios) performance.
Leave a CommentJeff Mlakar is saying the number of NCIs is too darn high!:
I wish I could say I was exaggerating when I say that I’ve investigated query performance involving 20+ NCI on a single table on more than a few occasions. “Why not?” you may think – the more NCI the better, right? Well think on this:
Click through for the reasoning. You know it’s extra-bad when 95% of those indexes have “dta” in the name. Jeff even calls that out in the post.
Leave a CommentAndy Levy shares some thoughts:
I’ve spent a lot of time over the past 8 or years trying to “right the ship.” Systems that have been built and evolved over 10-15 years and the cracks are starting to show. Yes, there’s always the hot spot code that desperately needs attention, the stored procedure that runs in 6 hours but could be 20 minutes with the right adjustments. But I’m looking at a macro level today, more “operational” than “surgical.” When I see __ in a chunk of code, it’s a signal to me that there are overarching problems in how the whole system I’m working on was built and it’s going to take me a good, long while to undo that to deliver constant performance as data grows or improve maintainability.
Andy’s main topic is pre-populated temp tables serving as lookup tables in queries. Though if Andy wants eldrich temp table horrors, I raise him global temp tables (##table) created from a separate session and a SQL Agent job that runs every minute to create it, with people ignoring the failures because “That’s how it’s supposed to work.”
I’ve never seen that in practice, but now I kind of want to do it.
Bonus comment: leading semi-colons for CTEs. I rarely do that, but when I do, it’s because a semi-colon on the same line as a batch separator doesn’t count. In other words,
GO;
WITH records as (...)returns an error. There might be some workable variant, admittedly, but in those cases, I do put the semi-colon in front of the CTE. The rest of the time, when I know there isn’t a batch separator right before the common table expression, I of course don’t. Commas and semi-colons belong at the end, not the beginning.
Leave a Comment