A Fine Slice Of SQL Server
Brent Ozar checks out a new DMV:
SQL Server 2025 introduces a new sys.dm_os_memory_health_history view to make it easier for meatbags like you and robots like Copilot to know if the SQL Server has been under memory pressure recently.
To show how it works, let’s run a bunch of simultaneous high-memory-grant queries in the Stack Overflow database:
Read on for a demonstration of how it works.
Leave a CommentChristoph Berg performs some tests:
It’s been a while since the last performance check of Transparent Data Encryption (TDE) in Cybertec’s PGEE distribution – that was in 2016. Of course, the question is still interesting, so I did some benchmarks.
Since the difference is really small between running without any extras, with data checksums turned on, and with both encryption and checksums turned on, we need to pick a configuration that will stress-test these features the most.
Read on to see the test setup and how things perform. I’m a bit surprised that there’s so little throughput difference here.
Leave a CommentBrent Ozar has a new episode of Customers Say the Darndest Things:
I have never seen a T-SQL feature that people love as much as NOLOCK.
I keep thinking I’ve written enough blog posts about it, but a client came up with a new one:
We use SQL Server 2022’s Accelerated Database Recovery, which keeps copies of versions inside the table. Plus, we don’t use transactions – our inserts, updates, and deletes are done one table at a time, and your demos always have transactions in them, so we’re not affected.
That’s not how this works. That’s not how any of this works.
It might be because it’s early in the morning when I type this out, but I’m having a hard time even conceptualizing what the customer could be thinking here. Brent does lay it out in the comments below, however, and yeah, I’d still raise an eyebrow if someone said that out loud to me.
But the moral of the story is, find someone who loves you like T-SQL developers love NOLOCK.
Leave a CommentDavid Seis digs into scripted SQL Server installation:
In this and the next two blog posts I will be bringing diverse dbatools commands into scripts that can handle a complete deployment, do a checkup of major health and configuration metrics, and do a true up of a pre-existing instance. This post will cover the complete deployment, which if you have been reading the audit series will be much more than just the SQL install of last post. This time we are aiming for the whole thing. Install, update, configure host, configure SQL, Deploy maintenance. Everything I can think of!!
Clicking next-next-next one or two times for SQL Server installation is fine—it gives you an idea of what capabilities are available and what you need to know about. By the time you’ve installed SQL Server 5-10 times, you should familiarize yourself with the configuration files (especially because they get auto-generated for you after you use the GUI—SQL Server itself uses these to install!), and should be looking for ways to automate this process and avoid misclicks or wasting time that you could otherwise be using by reading Curated SQL.
Leave a CommentKoen Verbeeck performs an installation:
I already had a virtual machine in Azure, running SQL Server 2025 CTP 2.0 (which uses a pre-made image). I explain how to set that one up in the article Install SQL Server 2025 Demo Environment in Azure. But I wanted to use the latest preview, which is Release Candidate 0 at the time of writing. Unfortunately, there’s no image available (yet?), so I had to do it the old-school way: installing SQL Server manually.
Read on to see how to do it, as well as a few extra things necessary to make everything work well in Azure.
Leave a CommentJoey D’Antoni defends the defensible:
I’ve seen a couple of posts (of course they were chock full of AI slop images) on LinkedIn in the last couple of weeks, talking about how challenging it is to implement Kubernetes. In the most recent post I saw, it stated that “it took 5 months for our CEO to implement Kubernetes for our app”, to which I would ask, why the hell is your CEO configuring your clusters. I designed, and implemented the Kubernetes infrastructure on my current project, and I’ve worked on for a while, so of course, I felt the need to share my opinions on the matter.
As far as Kubernetes on-premises goes, there are quite valid reasons to run it on-prem. Yeah, it’s easier to host in AKS or EKS, but that’s not always possible. But regardless of whether you’re hosting on-prem or in a cloud provider, Kubernetes requires solid knowledge across several areas, including networking, storage administration, systems administration, and CI/CD, not to mention the development skills needed for containerization.
I think Joey downplays the skill level required, but I don’t want to err in the opposite direction by overstating the challenge. But if you want anything beyond the bog-standard deployment of AKS/EKS, the “You must be this tall to ride the ride” sign is significantly higher than using other containerized solutions like Azure Application Services/Container Apps or Elastic Container Service.
Leave a CommentStephen Planck recommends taking the plunge:
Cardinality estimation (CE) is how the optimizer predicts the number of rows that will flow through each operator in a plan. Those estimates drive cost, join choices, memory grants, and ultimately latency and resource usage. SQL Server has shipped multiple CE models over time. The pre-2014 model—commonly called the legacy CE—dates back to SQL Server 7.0. Starting in SQL Server 2014, Microsoft introduced a new CE and has continued refining it in later releases, including SQL Server 2022. Keeping the legacy CE turned on in SQL Server 2022 is usually the wrong long-term choice.
One thing to note is that the “new” cardinality estimator has been out for a decade. It’s not really that new anymore, and it’s not going anywhere soon. Yeah, there are still trade-offs where some queries are better on the legacy estimator, but for the people who use that as their reason for not using the new estimator, what have you done in the past decade to address this and tune those queries to work better? If the answer is “nothing,” it’s not the cardinality estimator’s fault here.
1 CommentEitan Blumin troubleshoots an odd issue:
A customer reported that running
DBCC CHECKTABLEon several different tables kept failing with the exact same error:Msg 845, Sev 17: Time-out occurred while waiting for buffer latch type 4 for page (1:27527325), database ID 10. Msg 1823, Sev 17: A database snapshot cannot be created because it failed to start. Msg 7928, Sev 17: The database snapshot for online checks could not be created.
Read on to learn more about Eitan’s troubleshooting process, what the cause of the issue was, and the fixes (both the immediate and complete ones) needed to resolve the issue.
Leave a CommentK. Brian Kelley doesn’t need that George Jetson button-clicking finger:
I am frequently building up my SQL Server environments, especially my test lab. However, manually performing SQL Server backups, running scripts to set up security, and the rest of the tasks are time-consuming. What can I do to automate things?
Read on for the answer. If you are administering a larger number of SQL Server instances than you can count with, let’s say, one hand, it’s a great idea to script out your server setup and configuration process.
Leave a CommentGetting locked out of a SQL Server instance can happen due to a number of situations. The most common scenario I’ve encountered is when a SQL Server is moved from one domain to another without the domain being trusted or having a local SQL admin account. I recently encountered another incident where a DBA was attempting to fail over a log-shipped instance to another instance. Part of their run book included a script to disable a set of users to block production access to the instance. The problem was, the production instance was on a cluster, unlike the development and QA systems where the script had been tested. Disabling the users in production took down the instance preventing the tail log backups from occurring. We had to get the instance back up in order to take those final backups.
What can you do if you find that you’re locked out of a SQL Server instance?
Read on for that answer.
1 Comment