Category: Administration

The Importance of Testing Received Wisdom

Published 2026-06-23 by Kevin Feasel

Life is full of “absolutes”. For example, the Star Trek: The Next Generation episode “The Measure of a Man” is often cited as the best episode of the series, and many folks will tell you that you should never adjust max worker threads. But once you take the time to dig in, you realize that “Darmok” is in FACT the best episode of ST:TNG, and you’ll also find a small cohort of folks adjusting max worker threads on all of their SQL Server instances. Are these people just abnoxious contrarians? No. They just did their own testing to validate the common wisdom.

Click through for an example from Mark around 64K allocation unit sizes for NTFS volumes. And I’ll give one on max worker threads. I had a consulting client at one point which had per-customer databases. Each customer was, in general, quite small, so they had thousands of databases on the instance. They also wanted high availability on the system, so they wanted each database mirrored to a different server.

If they didn’t spike max worker threads to extreme levels, the server would have fallen over simply from the weight of all of the open database mirroring connections. The actual server workload was fine and it could handle all of the open worker threads because the large majority were doing nothing. But if a zealous problem-solver popped in, ran a diagnostic, saw that they were violating “best practices,” and “fixed” the problem, that would have been a bad day.

Unrelated but similar story: the one time they did need to fail over due to an emergency, it was also a bad day. Because even if the instances can handle 2500+ databases, it turns out that having them all fail over at the same time on low-powered Azure hardware was not a pleasant experience.

Moving Away from Average Fragmentation for Index Maintenance

Published 2026-06-22 by Kevin Feasel

Tim Radney proffers some advice:

As a SQL Server DBA with years of experience tuning production environments, I’ve seen the same patterns repeat: nightly index maintenance jobs running for hours, consuming massive CPU and I/O, only for performance to remain inconsistent or even degrade in some cases. Many of us (myself included, early in my career) relied heavily on avg_fragmentation_in_percent from sys.dm_db_index_physical_stats to decide when to reorganize or rebuild indexes. It’s time we move past that outdated approach and stop just doing what we’ve always done.

One thing I would add on to Tim’s advice is, determine whether that index even needs rebuilt or if you’re burning resources for no practical benefit. If there’s no practical performance benefit from the operation—and with all-flash arrays that are within an order of magnitude of RAM speeds, you might already be close to that point—then the index rebuild is for naught. The index maintenance strategy that we all learned back in the day was to minimize the time we spent waiting for spinning disks to reach relevant data. When random access lookups are approximately the same speed as sequential lookups, position on the disk doesn’t matter all that much.

Automating Azure SQL DB Tasks without SQL Agent

Published 2026-06-17 by Kevin Feasel

Garry Bargsley solves a problem:

Many routine administrative tasks that have traditionally been handled by SQL Agent still need to be performed:

Scheduled stored procedures

ETL processes

Report generation

Data cleanup

Monitoring and alerting

Business process automation

However, Azure SQL Database does not include SQL Agent.

Garry provides several solutions, and I would add to it third-party job scheduling solutions. Granted, that’s usually an extra expense (whether due to fees or supporting a roll-your-own solution), but it’s on the table. And some of them are better than what SQL Agent has to offer, even if I do like the fact that there’s an okay option built-in for DBAs.

Goodbye, SQL ConstantCare and Consultant Toolkit

Published 2026-06-17 by Kevin Feasel

Brent Ozar breaks the news:

About ten years ago, I sketched out an idea for a different kind of SQL Server monitoring tool: one that gathered data just once per day, and gave you a short email with a specific list of actionable tasks to make a difference in health and performance.

Richie Rump did an amazing job of building SQL ConstantCare out in the years since, building a solution that was rock-solid and scaled well to terabytes of monitoring data. Every day, we sent thousands of emails for SQL Servers around the world.

This month, we shut off sales and began decommissioning it. Here’s why.

Click through for the reasons. Brent is still maintaining the First Responder Toolkit, so things like sp_Blitz will still be around. But I will miss the quarterly graphics of who’s using which versions of SQL Server, even if I always had to give the “This is a biased sample and may not be indicative of the entire population, but it’s still an interesting and informative sample” spiel each time.

1 Comment

Things to Watch with Contained Availability Groups

Published 2026-06-16 by Kevin Feasel

John Morehouse keeps one eye on things:

Contained availability groups solve some real operational problems, especially around logins, jobs, permissions, and supporting metadata. They can reduce drift between replicas and make failover cleaner.

That does not mean they are magic.

Like most SQL Server features, contained availability groups come with details that matter. The feature can absolutely help, but it needs to be designed, tested, and operated with the right expectations.

Click through for several things you should consider before jumping into deploying contained AGs.

Comments closed

Jobs and Security Objects in Contained Availability Groups

Published 2026-06-15 by Kevin Feasel

John Morehouse moves some assets between availability group replicas:

In the first post, I introduced contained availability groups and how they bring contained versions of master and msdb along with the Availability Group. That matters because many applications depend on more than just user databases.

Two of the biggest wins are SQL Agent jobs and security objects.

Click through to see how they work.

Comments closed

Deprecated SQL Server Features since 2016

Published 2026-06-12 by Kevin Feasel

Jeff Iannucci has a list:

If you’re planning on upgrading from SQL Server 2016 to the current version of SQL Server 2025, it would be helpful to note some of the features from versions since 2016 that are no longer supported. By that I’m talking about those things classified as “deprecated” (no longer supported) or “discontinued” (removed from the product). Let’s take a look at these features and say our goodbyes, organized by versions.

There’s not a lot on here that would affect most companies. Though to one of Jeff’s points in the article, I actually have seen a company use lightweight pooling, mostly because they didn’t know what it did and saw the bit about how it could improve performance. I came in and flipped that switch off for them. That was the kind of feature that likely benefited one or two large customers 30 years ago or so and it just hung around in the product due to inertia.

Comments closed

Thoughts on a Cloudless World

Published 2026-06-10 by Kevin Feasel

Mike Donnelly has some tongue-in-cheek responses:

There are some serious angles to this topic, and I have had conversations with people at conferences who are doing a remigration from the cloud, but it feels like the exception not the rule. It is interesting to think about. I spent most of my career working with on-prem SQL Server, but there was a period of about 10 years (the consulting years) where I didn’t touch anything that wasn’t in the cloud. The past several years have been working in a hybrid environment, but most of the work has been moving things to Azure and Fabric. Koen has some prompts for what our blog posts could be about, but rather than dive deep into any one thing I’m going to go with the blog writer’s best friend – a top 10 list.

The funny thing is, in my time as an on-premises DBA, I never dealt with hardware and didn’t have access to the server room.

Comments closed

Skills for Cloud-to-On-Prem Migration

Published 2026-06-10 by Kevin Feasel

Reitse Eskens focuses on a set of skills:

This month, Koen Verbeeck invites the blogging community to write about their thoughts on returning to on-premises. What could be struggles, things we have to re-learn, etcetera.

When I read the invite, it immediately sparked inspiration, because there are increasing rumours around cloud exits. People musing about ‘what if’. Some clients reference these questions, but so far no one has directly asked me one with the intent of moving forward with it.

Click through for Reitse’s thoughts.

Comments closed

The Pain of Moving Indexes between Filegroups

Published 2026-06-09 by Kevin Feasel

Erik Darling explains a process:

At some point you’re going to want to move some indexes to a new filegroup. Maybe you’re separating data across storage, maybe you’re cleaning up after someone who put everything on PRIMARY and walked away, maybe you’ve got your reasons and they’re none of my business.

Whatever the cause, you’d think this would be a solved problem in a database that’s been around since the Clinton administration.

It is not.

Some days, I’m convinced that the only way to win is not to play at all. Erik explaining how to migrate LOB data across filegroups fits that bill perfectly.

Comments closed