Blog

[Video] Office Hours in My Backyard

Last Updated July 6, 2025

Let’s hang out, listen to the peaceful waterfall, and go through your top-voted questions from https://pollgab.com/room/brento. This is kinda the calm before the storm: in another week, I’ll be on the road again for a while, hopefully bringing you some fresh Office Hours locations from the Caribbean and Alaska!

Here’s what we covered:

00:00 Start
01:30 Simon Frazer: I’m curious to know if partitioned views are still used much. How often do you see these out in the wild?
02:37 ConfinedLake41: Do you have any words of wisdom when moving from SQL Server-On-Premise to SQL Azure? A client wants to move all services to the cloud (imposed by the group). Also any tips, tricks, hints and or material would be really helpful! Thanks!
03:00 Ess Tea DBA: I once heard about using the Resource Governor as a monitoring tool only. Have you ever seen this done in practice?
03:45 Craig: I am running sp_BlitzFirst every 15 min to collect metrics as suggested in your “Running SQL Server in AWS & Azure” course. My problem is that sometimes it runs for over 10 min and I do not want this to affect the other processes. Does sp_BlitzFirst have an impact on the server?
04:54 chris: When I was younger I had more time on my hands than ideas to use it. Now I find I’ve far more I want to do than I’ve time for. Did you have a hard time deciding to move away from production DBA work? Do you have any advice for making difficult decisions to give something up?
09:09 Evan C: Besides using more disk space are there any gotchas with increasing the retention period for CDC?
10:02 Northwind: Hi Brent, is there a way to automatically trigger a stored procedure or SQL script right after a database is restored? We have a vendor database which allows engineers to restore prod backups to test. Due to org policy, we can’t mirror prod security in test
10:50 Adrian: Are there any plans for future videos regarding working with people (especially in development environments)? I find your talks about working with customers and/or colleagues really reach the same issues or challenges I sometimes find myself in. Would really like to hear more.
12:24 neil: What will RCSI actually break in production for a third-party app? Test environment doesnt seem like enough of a test, if I beef up TempDB and turn on RCSI in Prod how do I know if its acceptable to the company? What tests/how do you decide if it’s working? Data errors? Downtime?
13:34 Craig: How can I measure busyness by database. I am looking for something like batch requests per second — but, by database.
14:17 Craig: At the moment, our webpages call multiple store procedures (one for dropdown, one for list etc). We are considering making each webpage call a single stored procedure that returns multiple sets of data. What would you use to test if this is an improvement (or not)?
15:29 VegasHeatIsNoJoke: Brent what’s the sweet spot for free space in Azure SQL DBs? The problem my bosses are trying to solve: price of storage is now our pain point. Few of our large databases have free space over a TB while the % free is in the 15-20% range. I worry about performance.
17:01 MyTeaGotCold: Do you often find that Basic Availability Groups meet the HA/DR needs of your clients? I struggle to picture picking them instead of FCI or log shipping.
18:02 Dfens: Hi Brent, I need to prevert that 1 Sp of a DB takes all the recources of my full sql instance. Is there a way in Standart Edition to limit cpu, ram or disk usage somehow? This is one main reason that we do not put all DBs on one server but we have several sql servers.
19:02 neil: Are storage snapshot backups available for SQL VMs in Azure ?

SQL ConstantCare® Population Report: Summer 2025

Last Updated July 16, 2025

Brent Ozar

SQL Server Population Report

In this quarter’s update of our SQL ConstantCare® population report, showing how quickly (or slowly) folks adopt new versions of SQL Server, the data is very similar to last quarter. SQL Server 2019 still rules the market:

SQL Server 2022: 24%, up from 21% last quarter
SQL Server 2019: 45%, was 44%
SQL Server 2017: 11%, was 12%
SQL Server 2016: 12%, was 15%
SQL Server 2014: 5%, was 4%
SQL Server 2012 & prior: 1%, same
Azure SQL DB and Managed Instances: 2%, same

We had exactly one brave soul monitoring a new SQL Server 2025 instance as well! Here’s how adoption is trending over time, with the most recent data at the right:

In last quarter’s update, I said I expected to see a big jump in SQL Server 2022’s adoption rate as folks prepared for next year when SQL Server 2016 goes out of support. Sure enough, we saw about a 20% drop in SQL Server 2016 deployments, and the bulk of those numbers moved to SQL Server 2022’s market share.

Right now, a stunning 96% of the audience are running supported major builds – aka, SQL Server 2016 or newer! That’s great! I’m so happy with y’all. (Not to say the entire audience is running supported builds, though – a lot of folks are behind on their patching.)

Updated First Responder Kit and Consultant Toolkit for July 2025: Independence Release

Last Updated July 14, 2025

Brent Ozar

First Responder Kit Updates

I’mma wax philosophical for a second: I love the open source nature of the First Responder Kit because it lets anybody, anywhere, diagnose database problems faster. If you’re a full time employee somewhere today, you owe it to yourself to use the FRK scripts because if you ever switch from one company to another, your employment agreement will probably require that you leave your old scripts behind. (I’ve actually been involved in a lawsuit where a DBA took his T-SQL scripts to his next job, and got sued for it.)

Someday, I hope you celebrate your own personal independence and get to define your own success, and I hope the First Responder Kit scripts help in some small way.

Anyhoo, if you do live performance tuning of workloads under stress, you’ll find a lot of little quality-of-life enhancements in this quarter’s release.

How I Use the First Responder Kit — Wanna watch me use it? Take the free class.

To get the new version:

Download the updated FirstResponderKit.zip
Get The Consultant Toolkit to quickly export the First Responder Kit results into an easy-to-share spreadsheet
PowerShell users: run Install-DbaFirstResponderKit from dbatools

Consultant Toolkit Changes

Updated to this month’s First Responder Kit, but no changes to the spreadsheet template.

sp_Blitz Changes

Enhancement: add warning for AG replica more than 60 seconds behind. (#3635)
Enhancement: add warnings for SQL Server 2025’s new database scoped configurations and sys.configurations. (#3646 and #3657)
Enhancement: clarify warning text about AD groups in case they’re empty. (#3648, thanks Jane Palmer.)
Fix: was returning multiple warnings if trace flag 7745 wasn’t enabled and Query Store was enabled. (#3627)
Fix: exclude model database from Query Store checks. (#3654)

sp_BlitzCache Changes

Enhancement: can now do plan cache analysis on read-only secondaries. (#3632)

sp_BlitzFirst Changes

Enhancement: add warning for deadlocks happening during the live sample. (#3637)
Enhancement: add total thread time to headline news result set. (#3631)
Enhancement: move “Avg ms Per Wait” to the left in result sets because it’s so useful for troubleshooting storage, CPU, and blocking waits. (#3629)
Fix: warnings about restores happening now weren’t firing on Amazon RDS. (#3643)

sp_BlitzLock

Fix: new output-to-table mode broke other stuff. (#3640, thanks Erik Darling)

sp_ineachdb

Enhancement: adds new parameters for @is_ag_writeable_copy and @is_query_store_on. (#3651, thanks bwiggin10.)

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. Be patient: it’s staffed by volunteers with day jobs. If it’s your first time in the community Slack, get started here.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes me!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

[Video] Office Hours: Finally Back in the Home Office

Last Updated July 9, 2025

Brent Ozar

Videos

I’m finally back in the home office after running around Europe for a month. Let’s talk through your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
01:52 World Peace: Hey Brent, I am working in AZURE currently and the AZURE architect is placing all the mdf and ldf files on one drive and indicating it as a much better setup in AZURE then setting up a data drive and log drive. Are the previous ways of creating a DB server no longer valid.
03:59 Bishal: I have Azure VM hosting SQL standard edition with multiple DBs. We reduce server specs during weekend & upgrade back on Monday to save costs as there would be very little usage during weekend but this has downtime of around 10 mins. Is there any option for this with 0 downtime?
05:07 Kurama: My boss says you do not need to run index maintenance anymore outside of stats updates as fragmentation is not a concern anymore with all flash storage. We haven’t had any issues with it being used in this case (main db is 10tb~ with many billion row tables) What do you think?
05:31 SQLHeinrich: Hi Brent, I didn’t have the budget for Data Saturday Croatia this year. How was it from your perspective? How is the SQL Server community there? And do you plan on being there next year too?
07:19 So very tired: Neither transactional nor merge replication are in my toolbox. Do they take long to learn? Are they worth learning?
08:19 Flash Sentry: Very specifically for AGs, what should I look for in a monitoring tool? Most of them just regurgitate sys.dm_hadr_database_replica_states.
09:49 Tigger: What model and size do you use in your Local LLM?
12:06 Jerry: Hey Brent! Could you explain data partitioning in SQL Server to someone new to it? When should we consider it, and what are the main things to watch out for?
13:09 MyTeaGotCold: Have you found a use case for incremental statistics? As far as I can tell, nothing has changed since Erin Stellato was complaining about them in SQL Server 2014.
13:25 marcus-the-german: Hi Brent, I have a simple select * from table, no filtering. The query runs for 8 sec (statistics io, time is on), but execution plan says just 900 ms. Any hints? The query is executed in SSMS 21.
14:00 Dopinder: What’s the max DB’s you recommend for a single SQL availability group? What are the warnings for exceeding this number?
15:17 Mirza: What is the technology in Azure that you recommend we focus on if we only had on premises experience as a DBA. Is it just Azure SQL database or do we need to have a working knowledge of other tools such as Azure data factory?
15:56 OneCTEtoRuleThemAll: To CTE or not to CTE? Help me be kind to myself and others – other than recursion, are CTEs only a matter of preference and style? Or can you gain performance under the hood using them?

Should Someone Try to Get a DBA Job?

Last Updated August 11, 2025

Brent Ozar

Production Database Administration

There was an interesting post on Reddit yesterday asking what DBA jobs are like these days. Here’s a snippet of the most important part:

Our friend works for maybe 15 hours a week, on call for maybe 40 but is always running around not in front of his computer. She works odd hours, takes the shifts like Saturdays and Friday nights…And the rest of the time she watches YouTube/does other things while on call.

Is this true of all Server DBA jobs? I’m genuinely curious, and surprised how someone can be paid 140k a year for working so little. Is it a dying position that can easily be off shored? Enlighten me more, I’m so curious whether all SQL Server DBA roles are like this, or is this a rare occurrence.

There are actually several great questions in that post, and I’m going to reword them and talk through ’em individually.

Do some DBAs only work 15 hours per week?

Yes, and I know a few, but they’re the exception rather than the typical DBA job. In these cases, the DBA has been there for several years, they’re experienced with building their own automation, and they’re the only DBA at a particular stable company (or in a department/division.)

See, good DBAs strive to automate their work, and after a few years in a stable company, the DBA has put enough proactive automation into place to deal with regular issues. The company isn’t adding a bunch of new servers or users per week, so the DBA is just efficiently keeping the lights on.

In these cases, the company is paying the DBA for two benefits:

The DBA keeps the lights on, reliably
When things break, the DBA is on call 24/7 to react, and to troubleshoot efficiently using their in-depth knowledge of the company, applications, and SQL Server itself

Can a DBA do the same work for 25 years?

The Reddit post mentioned that the DBA in question got a certification 25 years ago, and they’ve been doing the same work for 25 years.

I can see how a DBA would feel that they’re still doing the same stuff they did 25 years ago. After all, we’re still setting up backups, checking for corruption, setting Cost Threshold for Parallelism, troubleshooting Agent job failures, explaining why SQL Server is slow right now, tuning indexes and queries to make them go faster, filling out reports for security auditors, and answering licensing questions.

However, the way we do those things has often shifted over time.

If you took a DBA from 25 years ago, certified with SQL Server 2000, and asked them why SQL Server is slow right now, they could probably give you an answer – but their answer would likely be different than the one a current DBA would give you. There have been so many slow evolutions over time in T-SQL, wait stats, hardware, diagnostic scripts, you name it.

In the case of the 15-hour-per-week DBA, the way she’d automate her work in the year 2000 is very different than how she’d automate it today. Over the years, she probably dabbled with T-SQL, then batch files, then PowerShell, then the Azure portal, and maybe today she’s learning Python to stay ahead of the game and work with her cloud services.

So yes, you can do the same work for 25 years – but the tools are constantly evolving, and you still have to keep learning.

How do I find those 15-hour-per-week jobs?

Those jobs are not advertised as “work 15 hours per week.” They’re advertised as regular hard DBA jobs with a lot of complex systems that break frequently. The difference isn’t the job: it’s the DBA. The DBA is the one who works hard for a few years to catch up, right the sinking ship, and then put in automation to avoid future problems. (And for those first few years, the job can involve a lot more than 40 hours per week, depending on how quickly the DBA wants to catch up and get ahead.)

That opportunity – to automate most of your work – isn’t restricted to just DBA jobs. A lot of tech administration jobs have that same potential.

Your best bet will be to focus on large, stable companies though. Small and fast-moving companies have too much change, and you’ll never be able to catch up and completely automate your work. There will be too many new incoming servers, too many application changes, too many architecture projects, etc.

Is DBA work easily done remotely or offshore?

The technical work: yes. For years, long before COVID, DBAs fought for the ability to work remotely. We spend a lot of time administering servers, and it’s not like we need to physically touch the servers.

The political work: not as much. Because DBAs play a central role connecting many teams (developers, systems administrators, network admins, project managers, end users, help desk, etc), we often get roped into meetings. Many of those meetings have very high visibility because we work on critical systems, so managers often want people in person for those meetings.

That’s still true for me even today, long after COVID. I specialize in a 2-day emergency consulting engagement, and while most of my clients would rather have me jump in quickly remotely, some require me to fly to their offices to do the engagement in person. It often costs them more than double to have me onsite, and those companies don’t care because the database is so critical to their company.

Is DBA work easily outsourced to another company?

No, but not because of technical problems – it’s because of political problems.

Bad remote DBA companies don’t automate the DBA work to reduce it – because they’re interested in maximizing their revenue. The less work they do, the less they get paid – so it’s not in their best interest to put in proactive automation. Instead, they simply use runbooks to respond to each problem as it happens, generating revenue each time.

Good remote DBA companies want to put in automation to reduce the ongoing work – but bad clients may not let them. After all, the company still probably has their own internal development and customer teams who wanna do stuff to the SQL Server, and they want the permissions to do anything they want, at any time, without interference from the DBA. In that case, the remote DBA company is put in a tough position: restrict the customer’s employees’ capabilities to preserve system reliability, or let the customers’ employees do what they want to keep them happy.

In addition, some managers and company owners are just more comfortable having a DBA on their payroll so that when something breaks, the manager can walk into the DBA’s cube and say, “I know you, I trust you, and I’m making you accountable for this problem. I know you’re going to work on solving this problem, and I know you’re not going to stop until it’s fixed, no matter what it takes, and it’s not going to cost me extra money.”

Is the DBA position dying?

There are two kinds of DBA specialists: production DBAs and development DBAs:

Production DBA jobs evolved rapidly over the last 5-10 years due to cloud database services
Development DBA jobs are about to evolve rapidly over the next 5-10 years due to AI

Production DBAs focus on installation, configuration, monitoring, and troubleshooting. Platform-as-a-service databases like Azure SQL DB and Amazon RDS automated some of the production DBA work, like backups and outage troubleshooting. In some companies, the remainder of production DBA work has been merged into systems administration jobs or developer teams.

Development DBAs focus on performance monitoring, tuning indexes, and tuning code. The need for them actually grew with the advent of cloud database services because companies suddenly faced much higher, faster-growing bills. I’ve had plenty of clients who said, “OMG, we thought the cloud was going to save us money, but performance is worse and the cost is higher – halp!”

However, dev DBA work will evolve because:

Developer tooling will start to integrate good database advice, earlier in the application development process
Performance monitor tooling will start to integrate good database advice after deployment, recommending code & index changes to help

Right now, as we speak, commenters are frantically typing:

But they missed two important words in the above bullet points:

Integrate – because the advice needs to come naturally during development and monitoring. I’m looking forward to the day when the user doesn’t have to point at something and ask the AI for help, but instead the AI can simply, politely intervene at the right time and say, “Hey, this thing that’s happening right here, this is a problem and here’s how to fix it.” Microsoft tried it decades ago with Clippy, and it completely bombed, so I know older folks are going to rebel at that solution – but we need that approach, combined with…
Good – not just advice, but good advice. Today’s LLMs put an awful lot of noise in with the signal, and too much prompting work is required on the user’s part. I regularly use tools like Copilot, and I chuckle and point out that the only reason the advice seems good is that it’s being read through my eyes, and I know which parts to scoff at and which parts to take seriously.

We’re nowhere near either of those today. I hope we will be in 5-10 years.

Should university students aim for DBA roles?

This was really the Reddit questioner’s main challenge, but I couldn’t do it justice without typing all of the above.

Having said all of that, no, I don’t think college students should aim for DBA roles. Those roles are amongst the hardest to get straight out of college because companies prefer hiring DBAs with experience.

I know, I know: “How am I supposed to get experience without getting the job first?” The answer is to get a different job, not DBA, but one where you’re working with data. Gradually gain seniority and confidence managing data, and then over time, where it makes sense, it’ll be much easier for your existing company to promote you into a DBA role.

And good news! Just about all tech jobs these days involve data in one way or another! But just don’t aim for DBA first.

Microsoft Introduced AI Integrations for SQL Server.

Last Updated July 9, 2025

Brent Ozar

You know how you put “https://” at the beginning of web site addresses, and your browser can magically talk to all kinds of web servers, all over the world?

Well, the Model Context Protocol (MCP) is a standard for AI stuff to talk to other things. It’s like http for AI.

It’s less than a year old, but there are already tons of MCP APIs for stuff like sending emails, manipulating data in Excel, placing Facebook ads, scraping web sites, managing Kubernetes clusters, you name it. It’s very rapidly becoming the way for developers to interact with services. And whaddya know, Microsoft introduced an MSSQL MCP Server preview!

Introducing MSSQL MCP Server
An example storyline showing how it works
The code on Github (click on the language of your choice to see the documentation for it)
Oliver Flindall’s improvements for DBAs and his Github repo for the code (he added health checks with sp_Blitz, sp_PressureDetector, wait stats analysis, Agent job health, and much more)
MCP service to analyze T-SQL for design, naming, and performance issues – hat tip to Erik Ejlskov Jenson for this

When a developer builds MCP into a tool, it lets users (and DBAs) chat with AI and:

Create, list, and drop tables
Query, insert, and update data
Create indexes

So you could literally chat with ChatGPT or Claude and say things like:

“Create a reporting table to summarize sales by year, product, and salesperson”
“Give me salaries for everyone in the company, ordered by salary descending”
“Update all salaries to give them a 10% raise”
“Drop all tables with names similar to audit or tracking”

Now, I know you, dear reader. You manage databases, so you’ve got a master’s degree in paranoia. When you read the above words, your knee jerk reaction was to say, “Hell no, not in my house!” and run to the network admins asking them to block the above URLs.

I had the complete opposite reaction: I was excited, especially when I saw how Oliver’s using it. Seriously, you should go read his post.

To me, the MCP Server is no different than any other application that talks to SQL Server. It’s gonna need a login to talk to SQL Server, and it’s up to us to define the security for that login. If we do something dumb like give it the ability to create tables, drop them, create indexes, and change data, well then, we deserve what we get – just like we deserve what we get if we give ANY application the ability to do that stuff.

You should be familiar with these database roles:

db_datareader – can read from all user tables and views
db_datawriter – can insert, update, delete in any user table
db_denydatawriter – can’t insert, update, or delete anywhere in the database
db_denydatareader – can’t read anywhere in the database

In a perfect world, you’d give each login fine-grained permissions so that they can only access the specific objects agreed upon in advance. For example, you might only grant read access to particular tables, or even only grant access to read via specific views or stored procedures.

But at absolute bare minimum, I’d expect you to leverage the four above roles, and not grant the ability to be a database owner, or create/drop objects on the fly in most databases. A production application shouldn’t own databases, and should only need to select/insert/update/delete from tables. If an application really needs to create and drop tables, that activity should be confined to a separate playground database.

So before you have a knee-jerk reaction to ban MCP services in your environment, take a deep breath, and do a security review of your existing application logins. Odds are, the existing logins already have too much permissions. I’m not asking you to lock those down – I’m just asking you to learn more about database-level roles so that when the executives tell you that you will be setting up a login for MCP, at least you’ll be better equipped to give it the right permissions.

[Video] Job Options: Zooming Out to See the Big Picture

Last Updated June 24, 2025

Brent Ozar

Professional Development, Videos

One of my readers asked me to give a 30-minute talk to their company’s internal employees discussing the job market at large. The target audience was technical people of all disciplines – not just database administrators, but all kinds of people who worked with technology.

My goal with the talk was to help people think about lots of possible next-steps in their career journey. I wanted to show how the skills they’ve already developed might be useful in jobs they hadn’t thought about before.

When I rehearsed it, I thought, “Ooo, I should publish this on my YouTube channel,” so… here it is! Keep in mind that this is just a quick & dirty recording, so the video quality isn’t up to my usual high standards, but hopefully the material is worth your while.

What’s New (And Undocumented) in SQL Server 2025 CTP 2.1

Last Updated June 26, 2025

Brent Ozar

SQL Server 2025

As the SQL Server 2025 previews continue to come out, Microsoft is making changes that they’re not telling you about. Sometimes these changes never actually go into the final production version, other times they’re held exclusively for the cloud, but sometimes – just sometimes – we get a sneak peek of something Microsoft hasn’t announced yet.

When CTP 2.0 came out, I blogged the thousands of new feature flags, messages, and system objects in SQL Server 2025 CTP 2.0, and I haven’t had the time to dig more deeply into those yet. In that post, I discussed the new sys.dm_feature_switches table, and I mentioned that it might disappear before release. Indeed it has – last week’s new CTP 2.1 removed access to that table, meaning that was probably our one-time shot to get a glimpse into something cool and undocumented. I would have loved to have been a fly on the wall when folks at Microsoft read my blog post and realized they’d left that table unguarded and publicly visible, hahaha. I love my job.

So now CTP 2.1 is out, and it comes with a few things I think you’re going to be interested in.

New Query Store Tools

The new undocumented stored procedure sp_query_store_remove_plan_feedback will probably let you remove data for specific features, like adaptive memory grants, parallelism, or cardinality estimation. There’s currently an sp_query_store_remove_plan, but that removes the entire plan rather than specific feedback attributes.

There may be a new sys.plan_persist_ce_feedback_for_expressions table – it shows up in sys.all_objects – but I haven’t been able to query it yet.

The following new messages in sys.messages appear to be related to this new feature:

12467: The feature_id %d is invalid.
12468: Removing feedback for feature_id %d is not supported.
12469: Removing feedback for a specific plan is not supported.
12470: Removing plan feedback is disabled.

New Messages

Folks using Availability Groups should read through these carefully:

5383: Cannot reference Index on Expression column ‘%.*s’ during index creation.
8070: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter %d (“%.*ls”): vector data type is not supported in TDS on the server side.
11449: ALTER TABLE SWITCH statement failed because the table ‘%.*ls’ has %S_MSG on it.
12526: An internal error occurred while cloning the specified warehouse.
13836: For onelake container paths, only files in ‘//Files’ directory are allowed.
13837: ERRORFILE path ‘%ls’ and data path ‘%ls’ must have the same onelake endpoint, workspace, artifact and folder.
16219: The value for %.*ls is out of range. The value must be greater than or equal to %I64u and less than or equal to %I64u.
17077: Error spawning Heartbeat Monitor thread: %ls
19545: ‘ALTER AVAILABILITY GROUP’ command failed for internal distributed availability group ‘%.*ls’. Altering internal distributed availability groups is not supported.
19546: ‘ALTER DATABASE SET HADR’ command failed for database ‘%.*ls’. This command is not supported for databases participating in distributed availability group with SQL Managed Instance.
19547: The integrity check for distributed availability group ‘%.*ls’ with SQL Managed Instance failed with error cause: ‘%S_MSG’. Corrective action: ‘%S_MSG’.
19548: The integrity check for distributed availability group ‘%.*ls’ with SQL Managed Instance failed with error cause: ‘An availability database ‘%.*ls’ not participating in any internal distributed availability group.’. Corrective action: ‘Remove the availability database from the availability group and add it back again.’.
19549: Provided distributed availability group name ‘%.*ls’ is invalid. Please provide a name of a distributed availability group with SQL Managed Instance.
19550: Distributed availability group with managed instance does not support having multiple databases.
23667: Change Streams event delivery error : ‘%ls’.
23668: User table ‘%s’ has reached the max number of Change Event Streaming destinations.
24803: Encountered failure during database upgrade of internal tables.
24804: This stored procedure supports only one-part or two-part names. Please use either [table] or [schema].[table].
24805: Column ‘%s’ of type ‘%s’ could not be validated with the underlying table. Reason: %s.
24806: Unexpected JSON data during parsing attempt in column type inference. Underlying data description: ‘%ls’
24807: Cannot parse unquoted JSON value in the property ‘%ls’. Valid unquoted values can be true, false, null, and numbers. Underlying data description: ‘%ls’
24808: Cannot convert a string value found in the JSON text to binary value because it is not Base64 encoded.
24809: One or more log or checkpoint files have been deleted or overwritten. No operations can be performed on the external table. Please recreate the external table.
24810: Updating source database is not supported.
24811: SOURCE_DATABASE can only be altered only for warehouse snapshots in LIVE mode.
24812: Setting SOURCE_DATABASE failed.
24813: Specifying an explicit value for the identity column ‘%.*ls’ in table ‘%.*ls’ is not supported.
25100: Storage is unavailable.
25101: Provided lease is broken.
25102: Lease was already released.
25103: Incompatible lease mode.
25104: Invalid parameter.
27604: Your organization Microsoft Purview Information Protection label publishing policy requires a justification when the label assigned to the column is being changed to a different label with lower priority. Provide a valid justification. Learn more: https://aka.ms/SQLPurviewLabeling
27605: The label with ID ‘%.*ls’ is not a valid label in Microsoft Purview Information Protection. Provide a valid label ID. Learn more: https://aka.ms/SQLPurviewLabeling
27606: Microsoft Purview Information Protection label can only be assigned or changed by Microsoft Entra user. Use a valid Microsoft Entra user to execute this query. Learn more
27607: Microsoft Purview Information Protection labels cannot be assigned when the database has existing labels assigned via the SQL Data Discovery and Classification feature. Remove any existing labels from the database prior to assigning Microsoft Purview Information Protection labels. Learn more https://aka.ms/SQLPurviewLabeling
27608: Labels via the SQL Data Discovery and Classification feature cannot be assigned when the database has existing Microsoft Purview Information Protection labels assigned to it. Remove any existing labels from the database and then try again. Learn more
27609: Internal error occurred while running billing background task. Major error code: %d, Minor error code: %d, State: %d.
27610: Unable to access Microsoft Purview Information Protection on your behalf because your session has expired. Reconnect to SQL database and try again.
31208: A fulltext fragment table is missing.
31644: Config server option ‘allow server scoped db credentials’ is disabled.
31734: The value ‘%I64d’ is not within range for the ‘%ls’ parameter.
31735: The JSON value for the JSON key ‘%ls’ must be ‘%.*ls’ type.
31736: An error occurred during the execution of the function.
31737: The size of the provided %ls would be at least %u bytes after converting to UTF-8, which exceeds the maximum allowed size of %u bytes.
31738: Initialization of the ‘%ls’ process with session ID ‘%ls’ failed with HRESULT 0x%08x.
31739: Generating embeddings from ‘%ls’ process with session ID ‘%ls’ failed with HRESULT 0x%08x.
31740: An internal error has occurred in AI runtime with session ID ‘%ls’. Retry the operation, if the issue persists, contact support for assistance.
35541: ai_generate_chunks
35542: Drop and re-create the distributed availability group with SQL Managed Instance.
35543: Stored procedure called on invalid distributed availability group type or on non-existing distributed availability group.
35544: Empty internal distributed availability group.
35545: Internal distributed availability group naming inconsistency.
35546: An internal distributed availability group detected with more than one associated availability database.
37579: The security policy ‘%.*ls’ cannot reference tables with vector indexes. Table ‘%.*ls’ has a vector index.
42241: Input JSON contains out-of-range values for %ls.
42242: Input JSON contains %ls type which is not supported in ARM64 architecture.
42243: VECTOR_DISTANCE function does not support different base types for vector arguments.
42244: A vector index cannot be created on tables with security policies. Table ‘%.*ls’ has security policy ‘%.*ls’.
42245: Drop and recreate the incompatible vector index ID %d on object ID %d.
45943: “The long-term retention backup with the desired legal hold value already exists backup file backup. ErrorOwner: sqldataintegration
46560: External Table Location option must be a valid OneLake path.
47700: Reason: The External Provider Access Token used for authenticating to the SQL Server is Blocklisted.
49538: The dynamic space management force reset operation was aborted because the connected server is not a page server.
49539: The dynamic space management force reset operation has failed for database ID %d, mode ID %d, failure name %ls.

Who’s Hiring in the Microsoft Database Community? July 2025 Edition

Last Updated June 23, 2025

Brent Ozar

Who’s Hiring

Is your company hiring for a database position as of July 2025? Do you wanna work with the kinds of people who read this blog? Let’s make a love connection.

If your company is hiring, leave a comment. The rules:

Your comment must include the job title, and either a link to the full job description, or the text of it. It doesn’t have to be a SQL Server DBA job, but it does have to be related to databases. (We get a pretty broad readership here – it can be any database.)
An email address to send resumes, or a link to the application process – if I were you, I’d put an email address because you may want to know that applicants are readers here, because they might be more qualified than the applicants you regularly get.
Please state the location and include REMOTE and/or VISA when that sort of candidate is welcome. When remote work is not an option, include ONSITE.
Please only post if you personally are part of the hiring company—no recruiting firms or job boards. Only one post per company. If it isn’t a household name, please explain what your company does.
Commenters: please don’t reply to job posts to complain about something. It’s off topic here.
Readers: please only email if you are personally interested in the job.

If your comment isn’t relevant or smells fishy, I’ll delete it. If you have questions about why your comment got deleted, or how to maximize the effectiveness of your comment, contact me.

Each month, I publish a new post in the Who’s Hiring category here so y’all can get the latest opportunities.

Mastering Class Updates on Resource_Semaphore, The Ascending Key Problem, and The Cloud

Last Updated June 26, 2025

Brent Ozar

Development

I’ve posted a couple of updated videos to my Mastering courses.

In Mastering Server Tuning, I updated and expanded the RESOURCE_SEMAPHORE module to cover more details about query workspace grants, plus cover SQL Server 2022’s updates to Adaptive Memory Grants.

In Mastering Query Tuning, I added a new module on the Ascending Key Problem. It’s a session I first gave this weekend at the SQLBits conference in London, and I’m tickled pink with how it went. Bits records all their sessions and publishes ’em free on their site eventually, so at some point that’ll be free to the public, but I wanted to record it right away in my home studio while it was fresh in my memory.

Folks who own the Recorded Class Season Pass: Mastering will also notice new modules in their Mastering Server Tuning class. At the bottom of that class, there are new modules appearing for Faster, Cheaper Cloud Databases. It’s the training day workshop I gave at Data Saturday Croatia & SQLBits over the last couple of weeks. I’ve tweaked the content to cover more of the attendees’ questions, and I’ll be giving it as a thank-you to folks with current Mastering memberships. You’ll see the modules get added gradually over the next few days as I record ’em. Enjoy!

[Video] Office Hours in My London Hotel Room

Last Updated June 23, 2025

Brent Ozar

Videos

When I’m on the road, I try to take you along with me to glamorous or scenic locations to give you a change of pace. However, last week in London was a bit of a whirlwind between SQLBits and restaurants, so at the last minute, I recorded Office Hours in my hotel room!

I went through your top-voted questions from https://pollgab.com/room/brento, and here’s what we covered:

00:00 Start
01:07 Shane: Hi Brent, I am early in my career and I enjoy learning about SQL Server performance tuning. Is it a bad bet for me to focus on that? With how AI is developing, would it make SQL Server performance tuning less needed in the future?
03:07 andrews: Have you ever worked with databases where Clustering Key had to be different from Primary Key, e.g. Clustering Key was made INT while Primary Key was left GUID. Have you seen any performance gains with this separation?What about Key Lookups in PK/FK JOIN to other tables
04:29 myClusteredIndexSucks: Changing clustered index seems too risky on very large critical tables in a system required 24×7. When the clustered index key is useless, do you ever create a ‘surrogate clustered index’? ie one non-clustered index on a very useful key with most or all the other columns
05:43 MyTeaGotCold: How can I have multiple BAGs with automatic failover on the same instance without a licensing risk? Because the databases aren’t in the same AG, I can’t guarantee that they failover together.
06:26 Bandhu: When taking a new DBA job, what’s the max number of SQL Servers you would feel comfortable being responsible for?
07:34 Cavaliery: Hi Bren, I’ve noticed that all your examples include an ‘Id’ field (an auto-incrementing integer). Do you think it’s always always necessary to have that field?
08:15 Bandhu: How does admin of a few SQL Servers differ from admin of a hundred SQL Servers?
08:52 TractorDBA: SQL2019: A UDF was inlined resulting in a complex execution plan which produced non-yielding schedulers, hanging SQL. Only stopping the service with NOWAIT via a DAC connection would kill the query. Have you seen anything like this? Can you point me to any educational cases?
09:59 Alexei Shostakov: What is your opinion of the new vector search functionality in SQL Server 2025? Will this drive SQL Server revenue to most glorious victory?
11:36 Eduardo : The corporate dashboard tool (Tableau) takes control of the TSQL generation but makes crazy complex / poor performing queries. Any tips for performance in this type of application?
12:36 Adrian: Hi Brent, I took all your Fundamental courses and I still cannot make a MAX run fast (SELECT a, MAX(b) GROUP BY a HAVING MAX(b) …). Does it help to look for Mastery courses (which one?) or there is not a guaranteed solution for the problem? I used SQL 2019 for my attempts.

Poll Results: How Often You’re Running Maintenance Tasks

Last Updated June 23, 2025

Brent Ozar

Production Database Administration

Last week, I asked y’all how frequently you ran your full backups, corruption checks, index maintenance, and stats updates. The results after over 1,000 responses:

A few thoughts:

You’re typically taking full backups daily or weekly. I’m actually surprised by how many folks do weekly backups! That kinda scares me because if the backups take so long that you can’t do ’em overnight, then you’re probably gonna be horrified by restore times when things go wrong. You’ll have to do that big long full restore, plus the most recent diff. If you’re in that situation, you really wanna start checking out storage snapshot backups.

You’re doing index maintenance less frequently. Compare the backup and index maintenance responses, and weekly/monthly/less have higher responses than they did on the full backups answer. That’s good to hear! Fragmentation isn’t a problem that you want to tackle daily.

A lot of folks are doing daily stats updates. And right here, this is the problem that prompted this entire poll. I see a surprising number of people who do daily statistics updates, AND who complain about an unstable plan cache that constantly experiences parameter sniffing. The more you update stats, the more you’re freeing the plan cache, and you’re causing your own parameter sniffing issues by making the plan cache so unstable.

If I could give you one piece of advice, it would be to check for corruption more frequently than you do statistics updates. People don’t get fired for out-of-date stats: they get fired for losing data.

SQL Server Reporting Services is Dead. Is SSIS Next?

Last Updated June 20, 2025

Brent Ozar

SQL Server 2025

It’s official: SQL Server 2022 was the last release for SSRS.

At SQLBits this week, Microsoft announced that SQL Server 2025 won’t include SSRS.

Instead, all SQL Server 2025 customers will get free licensing for the on-premises Power BI Report Server (PBIRS.) The same license key you use to install 2025 will also work to activate PBIRS.

Microsoft’s got links on how it’ll work:

FAQ on the change – like why they’re doing it and how licensing will work
How to migrate from SSRS to PBIRS
How to migrate your RDL reports to PBIRS

Like any transition, there’s going to be manual work involved. Some data sources aren’t supported, and some SSRS features (like linked reports) aren’t supported. It reminds me of the DTS-package-to-SSIS conversion years ago, which even spawned small consulting companies that focused exclusively on that kind of work because there was so much of it. This transition will keep Microsoft partners busy for a few years.

I don’t do any reporting work, but I think the change makes complete sense for Microsoft and for folks who want better reporting. The change log for Power BI Desktop and for Power BI Report Server make it clear that Microsoft’s been investing way more in those products, whereas SSRS hasn’t gotten any love in years. I have fond memories of SSRS from 20 years ago, back when it came out and quickly decimated the market share of Crystal Reports. However, the writing’s been on the wall for quite a while. Power BI’s where the action is.

So, does this mean the gradual end of the free-in-the-box BI bundle that emerged in 2005: SQL Server Analysis Services, Integration Services, and Reporting Service? Those tools have all steadily declined in usage. Well, SSAS gets improvements in SQL Server 2025, but SSIS’s 2025 changes seem to be more about removing stuff than adding it. Might SSIS be next on the chopping block?

My guess is that SSRS’s ending was easier because Microsoft already had a fully on-premises solution in PBIRS. However, fully on-premises folks don’t have a Microsoft solution to replace SSIS yet. The closest thing they have is a self-hosted integration runtime, but the control is still dependent on the cloud. Announcing the ending of SSIS would be harder if Microsoft doesn’t have an alternative, so rather than ending it outright, they’re just stripping away stuff they don’t want to support (like Attunity and Hadoop.) Given that SSIS gets much less attention from end users (as opposed to reporting apps), Microsoft will probably let SSIS soldier on with zero development effort, like Service Broker. If it works for you, great – keep using it! But just know that the writing’s on the wall for this one, too.

[Video] Office Hours on the Cruise Ship

Last Updated June 8, 2025

Brent Ozar

Videos

I’m on a Med cruise, and I stopped the boat today in Corsica to make sure your questions from https://pollgab.com/room/brento got the answers they deserve. I started by mentioning that I’m using a 360 degree camera, but the file size on that video was so huge that I couldn’t upload it from the cruise ship, so you just get the 180 version.

Here’s what we covered:

00:00 Start
01:36 chandwich: Hey, Brent. At what point in your career did you start to feel truly confident in your ability to solve problems you have not seen before? Was there a specific “aha!” moment? Did that contribute to your decisions to move into consulting?
04:35 Erez Yaar: Hi Brent, following your course i have ran sp_blitz on a freshly recovered database. I have got around 450 messages “Leftover Fake Indexes From Wizards” but no indexes to delete (only the statistics shows the same index names. The script on the URL return nothing.
05:48 TechDB: Hi Brent could you pls brief the use case of CDC and Change data capture
07:11 Ricardo: What are your recommended questions to ask the potential employer in a DBA interview?
07:47 AG Avoider: Is it any kind of bad practice to use the same file share as an FCI Quorum witness and as your log shipping storage?
09:02 Daniel: A third party application inserts millions of rows to mssql one by one. How to optimize the speed export process if a client code cannot be changed? I’m considering using memory optimized non durable tables and then move data to standard ones.
10:26 gserdijn: Hello Brent. One of my servers has almost 20% stolen memory. Any pointers how I can find the culprit? A friendly person suspects it might have something to do with large, mostly unused, memory grants.
11:58 MyTeaGotCold: How can I keep on top of columnstore maintenance when measuring fragmentation takes too long? My columnstore indexes are partitioned and huge, so Niko’s scripts take 20+ minutes per index to report fragmentation.
12:54 Srinivas: In Query Store reports , large number of system queries (Microsoft related) shows up . For example queries with high CPU . Is there a way to bypass these in GUI . Is there any way to improve system queries by creating indexes or statistics ? Require more filtering options in QS
14:47 Ben: Hey Brent, What are your thoughts on SQL Server on Linux? Have you seen much adoption, and do you think it’s a good direction for SQL Server?
15:41 NotARealDBA: My app has an EF query that pages on a complex subquery. SQL will produce a Top N Sort operator if I inline the paging variables, but parameterized it requires a Top-Sort which spills and kills performance. Is there anything I can do that doesn’t require fighting EF? Thanks 🙂
17:15 Jonathan: Hey Brent! Just curious—do you have a college degree, or did you go a different route into tech?
19:20 chris: The company I work for blocks all of the AI providers as well as Copilot and even sites such as Notion which have a paid AI option available. Do you think their stance on these tools may change as they become more embedded with the way people work?
20:14 I’mTrying: Given that new versions of SSMS and drivers are opting for security by default, are companies moving towards using CA signed certificates on their SQL Server instances? If it is best practice how are enterprises managing hundreds of SQL Servers and expiring certs?
21:27 Potato with an e: I’m a new employee(a dev, not dba) and the DB’s have nolock hints all over the place. Eww. No RCSI. I brought up implementing RCSI and they said you can’t implement RCSI without removing all no locks first. Any techniques for continuing the conversation?

[Poll] How Often Do You Run Maintenance Tasks?

Last Updated June 18, 2025

Brent Ozar

Production Database Administration

How often do you run backups, corruption checking, index maintenance, and statistics updates?

Click here to take the anonymous poll.

After you fill it out, you’ll be able to see the average responses from other folks. I’ll circle back next week and share the answers, plus chime in with my thoughts.

Update – I’ve closed comments on the post to make it clear that we’re doing a poll here, not asking for text answers, so we can deal with data instead of feelings. WINK WINK

Free Webcast Next Thursday: Tuning Indexes to Avoid Blocking

Last Updated June 7, 2025

Brent Ozar

Conferences and Classes

Indexing can make or break your SQL Server performance. You need just the right balance: enough indexes to speed up your queries, but not so many that they drag down your inserts, updates, and deletes.

Join me as I introduce the “5 & 5 indexing guideline” — a practical approach to tuning indexes for maximum efficiency and minimum contention. Through real-world demos, I’ll will show how a single poorly chosen index can bring your system to a crawl with blocking issues.

Whether you’re troubleshooting slow transactions or proactively tuning your SQL Server for performance, this session will give you actionable strategies you can use immediately.

Who should attend? DBAs, SQL Developers, Database Engineers, and anyone responsible for SQL Server performance.

You’ll learn:

How to strike the right indexing balance
The “5 & 5” guideline and why it works
How to spot and resolve blocking caused by indexes
Real-world tuning tactics you can apply today

Register here for this free webcast sponsored by Idera. See you there!

[Video] Office Hours at the Barcelona Pavilion

Last Updated June 2, 2025

Brent Ozar

Videos

I’ve always wanted to see the Barcelona Pavilion by Mies van der Rohe, and by happy coincidence, I ended up walking right past it while visiting Barcelona! It’s one of the most important beginnings of modern architecture, and I loved it. I brought you along with me and talked through your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
02:22 James: Hi Brent, I noticed in your last Office Hours session that your shoes looked huge! Do you have big feet? Just curious—what shoe size do you wear?
03:30 Jerry: You mentioned a professional association for SQL Server during your last Office Hours. I’ve also heard about the PASS Summit—are they related?
04:40 MyTeaGotCold: Are Availability Groups the hardest thing in all of SQL Server? It’s as if you can’t debug them without a very deep knowledge of SQL Server’s internals.
07:02 Raja: Hi Brent! How do you create a nonclustered index or alter a clustered index on a large table where blocking is unacceptable to the system?
08:17 DataBarbieAdministrator: Hi Brent! What was your worst experience with a customer – and how did you handle it? Thank you for all the (great!) free work you do for the community!
12:28 Caught Brent’s Cold: Are CAL licences permanent? The official licensing guide was no help.
13:41 Q-Ent: Hi brent, during your consulting career have you ever faced a satiation where a client or a department by the client challenged your knowledge or your competency?
16:02 Dankula Flow: Should I treat the Blocked Process Report like Query Store and turn it on by default? I’m unclear on its performance costs.
17:15 BA with DBA Hobby: I have separate disks for the database, log files and TempDB. When I switch our production DB from Simple to Full recovery mode the latencies on the database disk go wild. I would expect the latencies on the log files to go up not on the database. What could this be?
18:40 World Peace: Hello dar Brent! The following scripts need xp_cmdshell: sp_allNightLog, sp_AllNightLog_Setup, sp_Blitz, sp_DatabaseRestore. xp_cmdshell is not a supported feature in AZURE Serverless and SQL Server Managed Instances. Is there a work around or a plan to fix this in the future?

Query Exercise Answers: Returning Routes in the Right Order

Last Updated June 11, 2025

Brent Ozar

Query Exercise Answers

In your most recent Query Exercise challenge, I gave you these two tables:

DROP TABLE IF EXISTS dbo.Stations;
CREATE TABLE dbo.Stations
    (StationId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
     StationName VARCHAR(50),
     StationPhysicalOrder INT);

INSERT INTO dbo.Stations (StationName, StationPhysicalOrder)
    SELECT CHAR(64 + n), n
    FROM (VALUES 
        (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
        (12),(13),(14),(15),(16),(17),(18),(19)
    ) AS Numbers(n);

DROP TABLE IF EXISTS dbo.StationRoutingOverride;
CREATE TABLE dbo.StationRoutingOverride
    (StationRoutingOverrideId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
     StationFromName VARCHAR(50),
     StationToName VARCHAR(50));

INSERT INTO dbo.StationRoutingOverride (StationFromName, StationToName)
    VALUES ('E', 'S'), ('B', 'I'), ('I', 'D');

DROP TABLE IF EXISTS dbo.Stations;

CREATE TABLE dbo.Stations

(StationId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

StationName VARCHAR(50),

StationPhysicalOrder INT);

INSERT INTO dbo.Stations (StationName, StationPhysicalOrder)

SELECT CHAR(64 + n), n

FROM (VALUES

(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),

(12),(13),(14),(15),(16),(17),(18),(19)

) AS Numbers(n);

DROP TABLE IF EXISTS dbo.StationRoutingOverride;

CREATE TABLE dbo.StationRoutingOverride

(StationRoutingOverrideId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

StationFromName VARCHAR(50),

StationToName VARCHAR(50));

INSERT INTO dbo.StationRoutingOverride (StationFromName, StationToName)

VALUES ('E', 'S'), ('B', 'I'), ('I', 'D');

And your assignment was to return a result set that started with the first station by StationPhysicalOrder, and then move through the rows in StationPhysicalOrder – unless there was a matching StationRoutingOverride row, in which case you needed to follow that routing instead. A successful result would look like this:

In the challenge, I explained that I wasn’t actually able to solve it without using procedural code: looping through the rows one by one with a while loop or cursor, I knew y’all would be able to do it because the audience includes some real T-SQL wizards. All of y’all who solved it in the comments should officially feel Smarter Than Brent Ozar™.

An Against-the-Rules (But Real-World) Answer First

Brian Boodman first posted this answer, and then apologized for not reading the instructions about not using loops. However, I wanna show his answer because it was the first kind of answer I thought about doing at the client – because after all, this kind of brute force programming works, it’s just not elegant:

declare @Result Table(StationName VarChar(50))
declare @Position VarChar(50) = 'A'

while(@Position is not null)
BEGIN
    insert into @Result values(@Position)
    declare @Override varchar(50) = (select top 1 StationToName from StationRoutingOverride where StationFromName = @position)
    declare @Failover varchar(50) =
    (select top 1 S2.StationName from Stations S1
    join Stations S2 on S2.StationPhysicalOrder = S1.StationPhysicalOrder+1
    where S1.StationName = @Position)
    set @Position = coalesce(@Override,@Failover)
END
select * from @Result

declare @Result Table(StationName VarChar(50))

declare @Position VarChar(50) = 'A'

while(@Position is not null)

BEGIN

insert into @Result values(@Position)

declare @Override varchar(50) = (select top 1 StationToName from StationRoutingOverride where StationFromName = @position)

declare @Failover varchar(50) =

(select top 1 S2.StationName from Stations S1

join Stations S2 on S2.StationPhysicalOrder = S1.StationPhysicalOrder+1

where S1.StationName = @Position)

set @Position = coalesce(@Override,@Failover)

END

select * from @Result

When I was working with the client, I said that if I had to get across the finish line in 15-30 minutes, this is the kind of solution I’d write. As it happened, this query would end up getting called millions of times per minute, and every millisecond of CPU would count, so I didn’t wanna do this. But I’m showing it here because it works, and I bet a lot of y’all went to this in your head first too – I did!

Using CTEs

The first answer was Ilya Makarov’s. Even though his was the fastest, he managed to work in cool examples of T-SQL features like LEAD, OUTER APPLY, and CASTing an integer for safety purposes:

with t as
(
    select 
        stationName s,
        stationPhysicalOrder o,
        lead(stationName) over (order by stationPhysicalOrder) ns,
        min(stationPhysicalOrder) over () mino
    from
        dbo.Stations
),
r as
(
    select cast(1 as int) o, s from t where o = mino
    union all
    select 
        r.o + 1, isnull(so.ns, t.ns)
    from
        r
        outer apply (select StationToName from dbo.StationRoutingOverride where StationFromName = r.s) so (ns)
        join t on r.s = t.s
)

select s from r 
where s is not null
order by o

with t as

(

select

stationName s,

stationPhysicalOrder o,

lead(stationName) over (order by stationPhysicalOrder) ns,

min(stationPhysicalOrder) over () mino

from

dbo.Stations

r as

(

select cast(1 as int) o, s from t where o = mino

union all

select

r.o + 1, isnull(so.ns, t.ns)

from

outer apply (select StationToName from dbo.StationRoutingOverride where StationFromName = r.s) so (ns)

join t on r.s = t.s

)

select s from r

where s is not null

order by o

Ilya used two CTEs: the first fetches the first station only, and then the second CTE joins to it to get all subsequent stations.

I got a chuckle out of Ilya’s answer because at the client, one of the developers had said, “Could we use OUTER APPLY to do this?” and my honest answer was, “Maybe, but I don’t use that a lot, and to get it right is going to take more time than we have here.” Hats off to Ilya (and the other commenters who banged out good answers.)

Michael Bogdanov’s answer came in next, managing to do it with a single CTE plus OUTER APPLY:

;WITH cte AS 
(
SELECT StationName , 
       StationPhysicalOrder
FROM Stations 
WHERE StationPhysicalOrder = (SELECT MIN(StationPhysicalOrder) FROM Stations)
UNION ALL 
SELECT COALESCE(StOverride.StationToName, Stations.StationName) AS StationName  , 
       COALESCE(StOverride.StationPhysicalOrder, Stations.StationPhysicalOrder) AS StationPhysicalOrder
FROM Stations 
JOIN cte 
ON Stations.StationPhysicalOrder = cte.StationPhysicalOrder + 1 
OUTER APPLY
    (
    SELECT StationRoutingOverride.StationToName, StationOverride.StationPhysicalOrder
    FROM  StationRoutingOverride
    JOIN Stations AS StationOverride 
    ON StationOverride.StationName = StationRoutingOverride.StationToName
    WHERE StationFromName = cte.StationName 
    ) StOverride
)
SELECT StationName
FROM cte

;WITH cte AS

(

SELECT StationName ,

StationPhysicalOrder

FROM Stations

WHERE StationPhysicalOrder = (SELECT MIN(StationPhysicalOrder) FROM Stations)

UNION ALL

SELECT COALESCE(StOverride.StationToName, Stations.StationName) AS StationName ,

COALESCE(StOverride.StationPhysicalOrder, Stations.StationPhysicalOrder) AS StationPhysicalOrder

FROM Stations

JOIN cte

ON Stations.StationPhysicalOrder = cte.StationPhysicalOrder + 1

OUTER APPLY

(

SELECT StationRoutingOverride.StationToName, StationOverride.StationPhysicalOrder

FROM StationRoutingOverride

JOIN Stations AS StationOverride

ON StationOverride.StationName = StationRoutingOverride.StationToName

WHERE StationFromName = cte.StationName

) StOverride

)

SELECT StationName

FROM cte

The first SELECT grabs the first station in the list, the one with the minimum order.

The second SELECT – still inside the CTE – queries back to the CTE itself. The first time it’s executed, it will only select the minimum row, then it’ll join to Stations again to get the next physical order. Then the OUTER APPLY also tacks on the overrides table.

Function-Based Answer

Michael J. Swart handled the recursion by using a user-defined function to fetch the next station for a route:

DROP FUNCTION IF EXISTS dbo.GetNextStationPhysicalOrder;
GO

CREATE FUNCTION dbo.GetNextStationPhysicalOrder
(
    @CurrentStationPhysicalOrder int
)
RETURNS INT
AS
BEGIN
    RETURN (
        SELECT TOP (1) StationPhysicalOrder
        FROM 
        (
            -- next one naturally
            SELECT TOP (1)
                StationPhysicalOrder,
                CAST(1 as bit) as IsNaturalNext
            FROM dbo.Stations
            WHERE StationPhysicalOrder > @CurrentStationPhysicalOrder
            ORDER BY StationPhysicalOrder ASC

            UNION ALL

            -- next one following override
            SELECT 
                NextStation.StationPhysicalOrder, 
                CAST(0 as bit) as IsNaturalNext
            FROM 
                dbo.Stations CurrentStation
            INNER JOIN 
                dbo.StationRoutingOverride O
                ON O.StationFromName = CurrentStation.StationName
            INNER JOIN 
                dbo.Stations NextStation
                ON NextStation.StationName = O.StationToName
            WHERE  
                CurrentStation.StationPhysicalOrder = @CurrentStationPhysicalOrder
        ) NextStation
        ORDER BY IsNaturalNext ASC /* prefer override --> NaturalNext == 0 */ 
    );
END
GO

WITH CTE AS
(
    SELECT TOP (1) 
        StationId,
        StationName,
        StationPhysicalOrder, 
        1 as OutputOrder
    FROM dbo.Stations
    ORDER BY StationPhysicalOrder ASC

    UNION ALL

    SELECT 
        S.StationId, 
        S.StationName,
        S.StationPhysicalOrder, 
        C.OutputOrder + 1
    FROM CTE C
    INNER JOIN 
        dbo.Stations S
        ON S.StationPhysicalOrder = dbo.GetNextStationPhysicalOrder(C.StationPhysicalOrder)
)
SELECT 
    StationId,
    StationName,
    StationPhysicalOrder
FROM 
    CTE
ORDER BY 
    OutputOrder ASC

DROP FUNCTION IF EXISTS dbo.GetNextStationPhysicalOrder;

CREATE FUNCTION dbo.GetNextStationPhysicalOrder

(

@CurrentStationPhysicalOrder int

)

RETURNS INT

BEGIN

RETURN (

SELECT TOP (1) StationPhysicalOrder

FROM

(

-- next one naturally

SELECT TOP (1)

StationPhysicalOrder,

CAST(1 as bit) as IsNaturalNext

FROM dbo.Stations

WHERE StationPhysicalOrder > @CurrentStationPhysicalOrder

ORDER BY StationPhysicalOrder ASC

UNION ALL

-- next one following override

SELECT

NextStation.StationPhysicalOrder,

CAST(0 as bit) as IsNaturalNext

FROM

dbo.Stations CurrentStation

INNER JOIN

dbo.StationRoutingOverride O

ON O.StationFromName = CurrentStation.StationName

INNER JOIN

dbo.Stations NextStation

ON NextStation.StationName = O.StationToName

WHERE

CurrentStation.StationPhysicalOrder = @CurrentStationPhysicalOrder

) NextStation

ORDER BY IsNaturalNext ASC /* prefer override --> NaturalNext == 0 */

);

END

WITH CTE AS

(

SELECT TOP (1)

StationId,

StationName,

StationPhysicalOrder,

1 as OutputOrder

FROM dbo.Stations

ORDER BY StationPhysicalOrder ASC

UNION ALL

SELECT

S.StationId,

S.StationName,

S.StationPhysicalOrder,

C.OutputOrder + 1

FROM CTE C

INNER JOIN

dbo.Stations S

ON S.StationPhysicalOrder = dbo.GetNextStationPhysicalOrder(C.StationPhysicalOrder)

)

SELECT

StationId,

StationName,

StationPhysicalOrder

FROM

CTE

ORDER BY

OutputOrder ASC

But then to put icing on the cake, Swart added another answer that uses string stuffing. I have to confess that this one was my favorite out of all of ’em because it was so out-of-left-field. I actually blurted out loud, “OH WOW!” when I walked through his code, hahaha.

All of the above answers get the job done! Good work, folks. Hope y’all had fun with that challenge – that was a little harder one than usual, for me at least!

SQLBits Added More Capacity to My Workshop! Move Quick.

Last Updated June 7, 2025

Brent Ozar

SQLBits

I’m coming to London this month for SQLBits, and my Thursday workshop “Faster, Cheaper Cloud Databases” workshop sold out right away when it was announced. There’s been a waitlist for months.

The short story: they added more capacity to my room! If you’re already registered, email contactus@sqlbits.com to join my Thursday workshop. If you haven’t registered for Bits yet, you can start a new registration here, use coupon code ozar102025 to save 10% on your registration.

The long story: my workshop is on Thursday, the same day as the Microsoft keynote. After the Microsoft keynote finishes, I’m taking over the auditorium, woohoo! That does mean that my session will run later, like 10:20-18:20. I don’t want it to go too late, so I’m also giving attendees access to a recorded version of the session, too, along with a full Recorded Class Season Pass Fundamentals.

Thursday Workshop:
Faster, Cheaper Cloud Databases

You’re managing databases that live in cloud whether it be AWS, Azure, and Google, and you’re getting a lot of questions about slow performance and crazy costs. Your users want everything faster and cheaper, and you’re not finding good answers.

Join me, Brent Ozar, for a day of real-time demos, honest war stories, and practical fixes. I’ll show how I use the First Responder Kit to assess the bottleneck, mitigate as much as I can with index and query tuning, and then write up a business case for management for better future budgeting.

Throughout the day, I’ll include real-life stories from my cloud clients, with names changed to protect the innocent. I’ll explain what worked, what didn’t, and what went up in expensive flames.

I’ll be using different flavours of SQL from across the clouds like Azure SQL DB Managed Instance and Amazon RDS SQL Server to highlight how each has nuances but also lots of similarities

You’ll learn:

How Brent uses the First Responder Kit to assess a cloud SQL Server’s wait types
Why each cloud vendor’s VM quirks make performance tuning tricky, and how to adapt
How to fix blocking, logging, and CPU issues when the hardware isn’t yours
How to write a persuasive performance report for management to fairly lay out where the problem is, and what steps to take next

By the end of the day, you’ll be armed with real-world techniques for keeping your cloud databases faster, cheaper, and more future-proof.

If you’re already registered for Bits, email contactus@sqlbits.com to add my workshop to your schedule. If you haven’t registered for Bits yet, you can start a new registration here, use coupon code ozar102025 to save 10% on your registration.

[Video] Office Hours in the Detroit Airport

Last Updated May 24, 2025

Brent Ozar

Videos

I’m in the Detroit airport today, en route home after a client gig. I’ve got some time to kill, so let’s go through your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
00:39 SQLLearner: Hi Brent! I’m curious about the two versions of SQL ConstantCare® — the Free Basics Edition and the full version. Could you explain the main differences in features and what each version is best suited for?
01:09 Harold B: Hey Brent! Is there any good reliable tool/plugin (preferably by M$) that you have used or suggest, where we can have versioning control of the schema/definition for Tables, procs, views, function onces they are created or changed.
01:48 Concern : Hi Brent! A few years ago, developers couldn’t write complex queries, so I, as a SQL Server expert, wrote them. But these days, AI does it much faster. Is it the end of my job?
03:22 CuriousDBA: I saw your post saying you no longer tweet on X. What prompted the decision?
03:38 JrDBA: Hi Brent! You often say to document planned/unplanned failovers with screenshots so any admin can do it. What’s the best way or tooling you’ve seen teams use to keep all that DBA documentation (failovers, log shipping, perf, perms) organised & versioned?
04:25 My Coffee Got COLD: I have databases in Read-Only mode, so why do the sizes of the differential backups change?
05:05 Dopinder: We are forced to use linked server queries due to architecture decisions made years ago. What are your top tips for improving linked server query performance between two SQL servers?
05:37 MyTeaGotCold: Aside from hacking in batch mode, have you ever found a use for a columnstore index on a table with less than one million rows?
06:16 NotATempDB: On a brand new DB, what are the reasons to use a collation other than the default?
06:33 AG Avoider: How is data loss possible on an FCI? Your HA/DR worksheet says it is but I can’t imagine it.
07:05 DBAInAction: Hi Brent, Trying to shrink a 25 TB DB by ~40% to reduce storage costs and improve maintenance across 6 Always On AG replicas. Main goal is to safely reclaim disk space by archiving historical data with minimal downtime and impact on availability. Appreciate your inputs, Thanks!
07:49 Eddie: Many of the SQL DBA jobs want reporting experience in SSRS / Power BI. Is this common? Not a reporting guru.
08:25 Brent’s Lungs Got Cold : Have you ever seen a BAG used for DR with an FCI for HA? I can see some benefits over log shipping, but I don’t know how well FCIs and BAGs mix.
08:47 Mike: Are there professional associations for guys working with databases ? specifically SQL Server and/or Azure SQL stack. Can anyone join ?
09:27 KyleDevDBA: In a recent office hours you mentioned that you run LLMs locally. What has been your experience with performance of the models and which do you prefer?
10:36 Mike: Got a shared system that supports multiple clients. Importing data for one client often causes us to need to re-run statistics, affecting other clients. What’s your take on partitioning tables by some kind of client id and then only running statistics on those tables by client id
11:56 Bandhu: What’s your opinion of the new native support for JSON in SQL Server 2025?
12:35 Cam: Hi Brent. My friend has a table that acts as a cache with a 1 hour lifetime. Expired data is removed by a frequent fast ordered delete job. In testing they see frequent stats updates causing bad performance (100% CPU). External cache is on the roadmap. Any tips in the meantime?
13:37 Accidental DBA: General Best Practice Question: Return one record with sorting done on Sql Server. Or return many records (less than 1000) to C# and have the app sort to get the final single record. Query plans for testing only show a very minor difference (C# sort slightly better).
14:31 Ricardo: What are the signs to look for when it’s time to find a new job?
15:24 DWKing: Hi Brent, Yourself and many others mention that Fabric isn’t production ready and has many issues. What would you recommend for a company setting up a new Dat Warehouse from scratch in 2025?
16:16 Devin: What are your pros / cons of working on a team of DBA’s vs working as the lone DBA? Which did you prefer?

Get Free SQL Stuff

"*" indicates required fields

Comments

This field is for validation purposes and should be left unchanged.

Name*

First Last

Email*

Things I want*

First Responder Kit (Scripts and Guides)

Monday Links (SQL & tech news)

Blog posts (2-4 per week)

Blog

Consultant Toolkit Changes

sp_Blitz Changes

sp_BlitzCache Changes

sp_BlitzFirst Changes

sp_BlitzLock

sp_ineachdb

For Support

Do some DBAs only work 15 hours per week?

Can a DBA do the same work for 25 years?

How do I find those 15-hour-per-week jobs?

Is DBA work easily done remotely or offshore?

Is DBA work easily outsourced to another company?

Is the DBA position dying?

Should university students aim for DBA roles?

New Query Store Tools

New Messages

An Against-the-Rules (But Real-World) Answer First

Using CTEs

Function-Based Answer

Thursday Workshop: Faster, Cheaper Cloud Databases

Hi! I’m Brent Ozar.

Get Free SQL Stuff

Thursday Workshop:
Faster, Cheaper Cloud Databases