Blog

What’s New in SQL Server 2025

Last Updated November 19, 2024

SQL Server 2025

Today at Microsoft Ignite, Microsoft announced SQL Server 2025. The biggest new features focus on AI and bringing the latest Azure SQL DB features down to your own servers. Here are the top features:

Call AI services like ChatGPT directly from T-SQL – using T-SQL commands and sp_invoke_external_rest_endpoint
Vector searches, a native vector data type, and vector indexes with DiskANN – more information in the documentation
Fabric mirroring – so your data warehouse in Fabric can more easily have a near-real-time copy of your OLTP data sources – similar to how it works in Azure SQL DB
Regular expression support in T-SQL – to learn more about that, read how it works in Azure
Optimized locking – like how it works in Azure SQL DB, and you might have noticed a theme here

As you run your dirty finger down your screen, reading my words aloud, you’re probably thinking to yourself, “Wait a minute – this is all stuff that’s currently in preview in Azure SQL DB.” That’s true – there aren’t any surprises here, and I think that’s fine! Microsoft can and should use Azure SQL DB as a testing ground for new things they wanna build into the boxed SQL Server product. Work the kinks out first while it’s all still managed by Microsoft’s own staff before they deploy it down on premises.

You’re gonna laugh at this, but I’m actually excited for the ability to call ChatGPT from T-SQL. I kid you not, I am gonna build support for it into the First Responder Kit! Why not add a parameter for @AskAI = 1 where your metadata is sent up to ChatGPT and ask for advice on particular problems? (You would have to have your OpenAI or Azure account already set up – I’m not paying for your advice myself, hahaha – and it’s up to you to decide whether you want to send that metadata up to the cloud. But if you wanna do it, go for it.)

You can read more on Microsoft’s blog post, Bob Ward’s post on LinkedIn, and Ignite attendees can watch this session tomorrow.

There’s no release date yet, and it’s not even in public preview yet, but you can sign up for the private preview here. Be aware that private previews typically involve a commitment to running the new version in production and having regular calls with Microsoft to talk about how it’s working in your environment.

Update: I had to laugh that 2025 also includes a rename for the feature formerly known as PSPO (pronounced piss-poor.) It’s now named Optional Parameter Plan Optimization, OPPO.

SQL ConstantCare® Population Report: Fall 2024

Last Updated November 19, 2024

Brent Ozar

SQL Server Population Report

Every quarter, we publish adoption rate data showing how quickly people are adopting new versions of SQL Server. Today it’s time for the fall 2024 version of our SQL ConstantCare® population report.

SQL Server 2019 is still the king of the hill with almost 3X more market share than any other version!

Here’s how adoption is trending over time, with the most recent data at the right:

While 2019 is still completely dominating the market, SQL Server 2022 did grow 3% – at the expense of 2012, 2014, and 2016:

SQL Server 2022: 13%, up from 10% last quarter
SQL Server 2019: 48%, holding steady
SQL Server 2017: 13%, steady
SQL Server 2016: 18%, down ~1%
SQL Server 2014: 5%, down ~1%
SQL Server 2012 & prior: 2%, down 1%
Azure SQL DB and Managed Instances: 1%, steady

SQL Server 2016 will be unsupported in less than 2 years. If everyone replaced 2012, 2014, and 2016 with SQL Server 2022, 2022 still wouldn’t dethrone 2019! 2019’s dominance is wild.

What’s more popular: Standard or Enterprise?

SQL Server Standard Edition has 50% of the market and Enterprise Edition has 35%, making Standard more popular by server count. However, in terms of licensed cores, it’s much closer: Standard has 13,862 cores, and Enterprise has 12,871.

That means in terms of licensing, we’re talking about $90 million worth of Enterprise versus $28 million worth of Standard. You can see why Microsoft wants to preserve that sweet, sweet Enterprise licensing money!

Help Me Help You.

Last Updated May 14, 2026

Brent Ozar

Company News

In my PollGab room where y’all can ask questions, Chocolate Thunder wrote:

I do genuinely wish that I could do two things at the same time:

Give y’all everything for free, and
Pay for the roof over my head and put food on my table

I haven’t figured out how to do both of those things at the same time. I know how to do one of them, but not both. So I try to strike a balance: I try to give away as much as I possibly can, for free. Here’s a quick rundown of what I provide y’all at absolutely no charge:

The First Responder Kit: tools like sp_Blitz, sp_BlitzFirst, and more so you (and consultants all over the world) can solve problems for free
Training classes: like How to Use the First Responder Kit, How to Think Like the Engine
SQL ConstantCare®: so you can get free monitoring for the most urgent issues like database corruption and broken backups
YouTube videos: in the last year, people have watched 85,200 hours of videos – on average, at any given time, 10 people are watching one of my YouTube videos
Short videos on my LinkedIn, TikTok, and Facebook channels

Every year, I challenge myself to give away more for free than I’ve ever given away before. 2024’s new addition was the short videos on LinkedIn, TikTok, and Facebook. This actually costs me money, because I have a social media person chopping up my Office Hours videos into bite-sized chunks and adding transcripts to make them easier to follow. (I can’t do this myself – I have zero free time left.)

Short videos don’t work for everything, of course. Some questions require longer, bigger answers. For example, in a 60-90 second free video, I can’t teach you how to rewrite a stored procedure to solve deadlocking problems. That takes explanation, a complex demo query, a cleanly rewritten one, and a long session showing the before and after effects.

So I do charge for the long, complex stuff.

I try to give away everything that folks need at the beginning of their careers. However, as they progress to the senior level and they start to face complex challenges – things that are costing the business real money – then that’s where I build more complex training classes to make those issues easier.

That means, Chocolate Thunder, that I’ve got good news and great news.

The good news is that you’ve found an authority online that you trust, whose opinion you value, and who you want to learn more complex things from.

Office Hours: Guess the Theme Edition

Last Updated October 28, 2024

Brent Ozar

Videos

There’s a secret theme to the questions I hand-picked from the queue at https://pollgab.com/room/brento. Normally I just take the top-voted ones, but today, see if you can spot the thing they all have in common:

Office Hours: See if You Can Spot the Theme

Watch this video on YouTube

Here’s what we covered:

00:00 Start
01:51 Tivan: Hi Brent, I’m facing a deadlock in MS SQL Server with “ORDER BY DESC” on a primary key (clustered index) during a read, conflicting with an insert on the same table. Is this related to descending scans? I couldn’t find a clear explanation. Could you share some thoughts? Thanks!
03:19 ddev: Sometimes it happens that in a blocked process report I can see a frame that I know cannot be in a stacktrace (I know it is not called from that procedure). What does it mean ? It is bug I can fix ?
04:38 Asking2much: Occasionally when tempdb is heavily hammered, sp_blitzwho gets an error of Lock request time out period exceeded (1second) due to tempdb.sys.sysschobjs.clst index being blocked. Would you be ok with setting the max lock_timeout value to 15 seconds? No issue with blocking liveplan
07:14 Gary Numan: When tasked with fixing overall SQL VM performance with cloud hardware, what are your pros and cons of increasing server memory vs upgrading disk SKU? Which do you see more of?
08:23 Vahid: Hi Brent I’ve been learning SQL DBA for about a year, mainly through tutorials. I feel I lack hands-on experience. How proficient should I be in T-SQL for this role? What’s the best learning path to improve my admin skills and problem-solving? Any advice is appreciated!
09:24 Call Me Ishmael: Over a decade ago, when I “cornered” you at a SQL Saturday in Washington, DC, in-between sessions, I asked you about SCHEMAs, which at that time you were adamantly against, albeit humbly, belying your rock-star status. Are you still against the use of schemas?
10:58 Jersey DBA: My company has a 3-node HA cluster. The third node is read-only for reporting. Why do we still sometimes see latency and blocking on that node with queries using the “with nolock” hint.
13:30 KyleDevDBA: Hi Brent, What would you recommend when one DBA thinks another DBA isn’t pulling their weight? Is there a certain thing you look at to either prove or disprove this?
15:58 CuriousDBA: Hello Brent. I see that you can use sp_blitz output to a table locally. Is there a way to store all that information centrally by running sp_blitz command against a list of all servers so that I can create a report for all critical issues across all my servers?
17:56 Tim: If sql server becomes overwhelmed with locking / blocking issues, is there a way to give the server a break and allow it to catch up ?
18:50 RacerX: What’s your opinion of NVME ultra disk when you are stuck with low memory (128gb) for multi TB database in Azure SQL VM?
19:36 Milind: Hi Brent, Will there be any difference in join where column with regular index versus filtered index (where table has size and good number of rows)?

SSMS v21: Dark Mode, Git, Vertical Tabs, and More

Last Updated February 2, 2025

Brent Ozar

Tools

SQL Server Management Studio v21 Preview 1 is out and let’s give it a quick whirl. It’s installed with the Visual Studio installer. That doesn’t mean Visual Studio is installed or required – it’s not – it’s just that the SSMS team is leveraging the work that the VS folks have put in over the years.

After installing, you’re prompted to sign in to sync your SSMS settings across devices, but there’s a Skip link. I do wish there was a “skip this forever” link, but whatever:

If you log in with Github, there’s a rather spooky-looking list of permissions that SSMS gets:

I’m a little suspicious as to why SSMS needs all that. Insert joke about least-privileged-permissions here. Anyhoo, after SSMS starts, to enable dark mode, click Tools, Options, put theme in the search box, and under Environment, General, choose Dark in the Color Theme:

Presto! Dark mode:

Eagle-eyed readers will notice that is not exactly dark mode. Erin Stellato (the SSMS PM) asked y’all for patience as they gradually work through the screens, converting them to dark mode. Once you’re connected, things look better:

The thing over at the far right was in the old SSMS too, it was just disabled by default. It lets you navigate through long stored procedures easier. At the bottom right, there’s an “Add to Source Control” link, which doesn’t do anything for me despite being logged in with Github. I’m guessing I have to organize a project or something first – I’ll dig into that later.

Query plans are not in dark mode yet:

To get into source control, click Git, clone a repository. I’ll take the First Responder Kit for example:

And a few seconds later:

Emotional damage. Okay, maybe it’s because I just copy/pasted the URL straight from Github. I edited out the /tree/main part at the end of the URL, and the second time it appeared to go through, although the SSMS UI didn’t change other than now the title bar says ViewPickerAutoload:

Which is honestly awesome, because I loved the pickers, and anything that SSMS can do to help Mike, Danielle, and Frank load the truck, the better:

I’m just kidding, of course. Frank passed away a couple of months ago. If only he’d been alive to see the show grace the title bar of SQL Server Management Studio.

Look, the point is that SSMS v21 is still very, very much in preview. It will install side-by-side with the legacy version (ha ha ho ho), so you can use both. v21 doesn’t support Analysis Services, Integration Services, or maintenance plans, so if you rely on any of that, you’ll still need the old v20. There are also a list of known issues with the preview, and right now I’d say it’s still for us hard-core early adopters.

Copilot in SSMS is Kinda Like IE in SSMS

Last Updated November 11, 2024

Brent Ozar

Production Database Administration

You are not ready for how weird my take is.

Last week at the PASS Summit conference in Seattle, Microsoft showed off the upcoming SQL Server Management Studio v21.

One of the most intriguing features is that it has Copilot built in.

No, not Github Copilot, that’s a different thing. No, not the Copilot in Visual Studio, either. No, not the Copilot that’s in the Azure portal either. SSMS Copilot is yet another version of the same basic concept: a text box where you can ask questions, and AI uses contextual awareness of your database and your query window in order to answer those questions. I don’t just like this feature, I adore it, because I already use AI every single day to get my job done. (This blog post is AI-free, as most of my posts are, but I use it for all kinds of stuff like T-SQL code review and refactoring.)

The Microsoft staff demoing SSMS Copilot at Summit (Erin Stellato, Bob Ward, Anna Hoffman) were very quick to point out its limitations:

It’s in very, very early preview
The functionality is incredibly limited so far – it can’t even read real-world query plans due to context size issues
The output, like any LLM output, is prone to errors and hallucinations – the output of all of the demos I saw had serious issues for production usage
It sends your database schema/config/queries to the cloud – but they’re very clear about not keeping or using any of it for training, and in the future, you may be able to use your own LLM endpoints on-premises
It’s going to take at least a couple previews before it’s publicly accessible, which means at least 6 months away
It doesn’t actually read the error messages and results that come back from the queries it generates

And that last part is where the wheels come off.

To understand the problem,
we gotta revisit history.

Remember when Books Online first came out? Of course you don’t, dear reader, because you’re young and attractive. But being one of the olds, let me tell you how it went.

We used to get the documentation in printed format, and then Microsoft began distributing it in electronic form as part of the installer. You could just hit F1, and browse the documentation in SSMS. Later, the documentation moved into HTML, and later still, the primary home for the documentation became Microsoft’s web site. Some users were horrified when they’d hit F1, and a browser built into SSMS would take them to Microsoft’s web site to see the most recent version of the documentation.

Eventually, people just stopped using F1, and they used Google. The limited browser built into SSMS was garbage, and it didn’t support the kinds of stuff modern users wanted to do, like view documentation sites that require Javascript, like Stack Overflow.

The web browser is still there in SSMS, but I can’t remember the last time I used it. It’s just too limited. And that’s what the problem is going to be with Copilot in SSMS.

The SSMS Copilot demos made it glaringly obvious.

The demos consisted of:

Open the Copilot window in SSMS
Type something into it
Copy a query from the Copilot window into the SSMS editor
Run the query
Get an error or results
Go back to step 2, copying the error, results, or clarifications back over into the Copilot window, and keep the cycle going

Which, uh, is exactly what you can do with ChatGPT and other LLMs today, and I’ve been doing for months. You don’t have to wait. Go get started now. You can pick other LLMs if you like, including local ones if you’re paranoid about sending your company’s database schema and queries up to the cloud. You can use the latest and greatest cutting edge LLM models, way beyond what SSMS Copilot supports. You can send query plans up there for analysis.

Copilot in SSMS, when it eventually ships, is going to lag far behind – just like SSMS’s web browser does.

Oh sure, SSMS Copilot absolutely does have some advantages over copy/pasting stuff into ChatGPT, like automatically picking out which tables & indexes are relevant to your question, and running diagnostic queries on your server to fetch the metadata on that table or index. But those advantages seemed so small to me, especially given how long it’s going to take to get that product live – compared to just getting started right now with ChatGPT or your LLM of choice.

Don’t get me wrong: Copilot in SSMS is still a good idea, Just like Books Online distributed via local HTML files was a good idea way back when. It was a stepping stone, and Microsoft had to do it at the time, just like they have to put AI in all their developer tooling today. I just don’t think it’s the final AI product that we’re all going to settle on.

So what could be better?

Here’s my prototype for a version of SSMS that includes AI:

I can hear your confused voice from here: “Wait, Brent, that looks just like today’s version of SSMS. You type what you want in the top pane, hit execute, and what you want comes out of the bottom pane.”

Exactly!

Why should users have to jump back and forth between two different input areas? Just type what you want!

If you type T-SQL, SSMS executes it against the database in question. If your query has an error, the AI reads the error, and tries to suggest changes to your query to get it right. If your query takes too long, AI suggests improvements to make it go faster.
If you type plain English that asks for data, the AI’s natural language to SQL capabilities fire up and write a query for you. This isn’t new: Microsoft’s demoed it already, but in the Azure Portal, a tool that makes no sense for data analysts to use.
If you ask for advice on your database, the AI runs diagnostic queries on your database, just like the Copilot demos we saw at Summit, and the results and advice appear in the Messages tab, with supporting evidence in the Results tab.

That simple, intuitive approach is harder to build in 2025. It’s much easier for Microsoft to ship Copilot as a separate SSMS extension, duct taped into a separate tab, and force users to copy/paste stuff back and forth between different text boxes and result sets. Copilot will even catch on temporarily in 2025-2026, just as the SSMS browser did, and you will definitely see me using it.

I’m just excited and looking forward to whatever comes next to replace it.

[Video] Office Hours: SQL Server Questions and Answers

Last Updated November 6, 2024

Brent Ozar

Videos

That face I’m making in the video thumbnail is priceless. YouTube randomly picks these, and I laughed out loud at this one. Anyhoo, let’s get to answering your top-voted questions from https://pollgab.com/room/brento:

Office Hours: SQL Server Questions & Answers

Watch this video on YouTube

Here’s what we covered:

00:00 Start
00:59 Mohit: Hello Brent, What options does a DBA have in terms of performance tuning when managing third party application databases which have lots of crappy SQL queries and no lock hints? Last but not least, thanks for your service to the community.
02:11 MyTeaGotCold: Page fullness is very important, but sp_BlitzIndex never touches sys.dm_db_index_physical_stats and Ola only touches it in LIMITED mode. What am I missing?
04:19 Tony Feuz: Lot’s of talk about linked servers and how we should not do that. One of your recent posts you mentioned to move the data to the same server and I want to confirm that cross server queries = bad and cross database queries on the same server = acceptable. Do I have that correct?
04:48 Trudging Through A SQL Swamp: I swear that I watched a video of you demonstrating how using NOLOCK could return incorrect data. I have looked everywhere and can not find that video. I did find a very short blog post by you about it, but would like to see the video again. Is it still available?
05:35 Crazy Harry: Who is the Itzik Ben-Gan of PostgreSQL?
05:49 Paul Hunter: How do you properly setup Index maintenance using Ola Hallengren on an AG? My environment has one active Server and one inactive server for fail over. (You will most likely ask why I used an AG for this, I didn’t. It is just something I have to deal with)
06:43 mailbox: What are the advantages & disadvantages to housing your Data Lake in SQL Server? I’ve seen many sites push towards housing data lake on some NOSQL DB.
07:56 Juan Pablo Gallardo: Is it correct that performance is greatly impacted by the cluster size of the partition, ie. 8k cluster size is ideal?
08:53 MuSQL: Hi Brent, Recently involved in a debate on LI with a .Net dev. who claims that Stored procedures are legacy and that new projects never use them. I feel the pain when migrating between DBRMs but also the benefit of not shuffling data back and forth. Whats your opinion on this?
10:00 Dopinder: Does SQL row / page compression make up for the low drive performance in Azure? Has it got you over the finish line?
10:54 ScenarioFromRealWorld: How can I let coworkers stop using Activity Monitor. Are there any new articles regarding this? Because I cannot see that AM is getting any better than before
12:07 OracleIsDiffrent: After downgrade MSSQL2019 from Ent. licence to Sta.we noticed small perform. issues.Queries who do index seeks and scans are running slower.The execution plans are same, major diff. is execution mode on index operations.On Ent. where running in batch mode now I can see row mode.
12:51 Yavuz: Hello Brent. I’ve been a DBA for over a year now and noticed that DBA’s don’t really write too many queries. How did you get good at writing long @$$ sprocs and T-SQL while being on the data administration side? It’s mostly developers and BI folks writing the queries, not the DBA
14:39 Bruno: What do you think of transactional replication? Would it be better to use Log Shipping instead? We have a publisher distributing a lot of publications and we experience some performance issues on a regular basis. Thanks Brento and Cheers to the DBA great family.

Office Hours in the Valley of Fire

Last Updated November 6, 2024

Brent Ozar

Videos

I took the Graffiti Gulf 356 out to the Valley of Fire State Park to exercise it, and took your top-voted questions from https://pollgab.com/room/brento.

Office Hours: Valley of Fire State Park

Watch this video on YouTube

Here’s what we covered:

00:00 Start
01:39 mailbox: Hey Brent! I’m really enjoying your prerecorded training classes. Quick question,I’m running sp_BltizCache @SortOrder = ‘reads’ on our DW server and receiving a priority 1 warning of Plan Cache Instability. How meaningful is this warning on a DW server?
02:49 Briggers: Hi Brent. I have inherited an intense AG with 1 database, utilising an async read-only secondary. Is there a way to help reduce the size of a redo queue? For example reducing the number of checkpoints (Automatic) on the primary?
04:00 DoesTimeReallyExist: Hi Brent! I prefer to learn SQL Server in depth instead of learning no-SQL or PostgreSQL and TimescaleDB. What do you think?
04:31 Aksel: Duplicate Index How to explain a situation where there are indexes A (col1, col2) and B (col1, col2, col3, col4), and there is a query that checks the values of col1 and col2. If the query uses index A, no memory grant occurs. If the query uses index B, a memory grant occurs.
05:31 Cameo: What are your pros / cons of using local time vs UTC time for OS clock running SQL Server? Which do you see more of out in the field?
06:51 mailbox: My friend’s company pays only to license 4 cores enterprise(SA) on a reporting server. Best I can tell, we don’t need enterprise as of now. However, we might need it in the future. Is it a good idea to switch to Std edition on same budget, thus increasing core count?
08:00 adba: I am seeing high CPU on a SQL server VM,I have added more cpu and tuned the query, but we still see the issue. What parameters do I need to monitor to show it is a problem on the host side.
08:59 GenXerTiredOfTheBabyBoomers: You have probably got this question often, but getting up there in age/end-of-corporate-life, how does one become a consultant? I have decades of experience with MS SQL, Windows OS, and networks, yet have trouble crossing the great divide of corp to cnslt.
10:18 SportsFan101: What are your thoughts on the new JSON data type in Azure SQL? Will we see this feature in the new on-prem version, SQL Server 2025?
11:28 Bandhu: When doing row vs page compression for canned SQL, do most of your clients do all row compression or all page compression or some combination?
11:48 mailbox: In your experience, when is it time to upgrade server hardware? Maybe I should ask, how often should we try to upgrade the server hardware of our SQL Servers? My friend says that they have VMs on hosts that are 8 years old.
12:53 Juan Pablo Gallardo: In pure ERP environments, with no user queries or tasks outside the ERP, is it safe to say that any deadlock is responsibility of the ERP vendor to solve?
14:06 Dopinder: What criteria do you use when evaluating standup desks? What is your favorite brand and model?
14:44 Vasilis Hadjiloucas: Should I exclude my antivirus software from scanning my FILESTREAM container in SQL Server? What are the potential risks or benefits of doing so?
15:17 CryingInTheCorner: Hi Brent, you still didn’t update most of your Mastering classes since Sql Server 2022 came out… :'(
16:41 Dumb dude: If you had a server that was having a lot of resource contention and you were only allowed to add CPUs or Memory, but not both, without cost being factored into it, which would you pick.
17:21 About the 356’s Graffiti Gulf paint job
18:40 Dopinder: Can a single DBA manage a new SQL AG or is a team of DBAs recommended for AG administration? What do you see in the field?
19:31 mailbox: What is it like to work as a DBA for a consulting firm? Is it just non-stop performance tuning fun? Or are their lots of nights and weekend work?
20:50 Eddy Grant: When upgrading Azure SQL VM from 2019 to 2022, is it ok to do in place migration since we have snapshot backups or should we logship to new VM hardware?

Office Hours at the PASS Summit in Seattle

Last Updated November 6, 2024

Brent Ozar

SQL Server

I’m in Seattle this week for the PASS Data Community Summit. Before the pre-conference sessions started, I sat down to take your top-voted questions from https://pollgab.com/room/brento.

Office Hours at the PASS Summit in Seattle

Watch this video on YouTube

Here’s what we covered:

00:00 Start
02:29 MyTeaGotCold: How are you finding DbBeaver for Microsoft SQL Server? Like you, I find ADS buggy.
03:22 DBA_Mufassa: We have a SQL 2019 std VM that was already provisioned with 4 cores. We noticed antivirus is using CPU a lot. We want to add 4 more cores just for OS processes to use. VM total now 8 cores. Can CPU affinity in SQL be set to 4 cores so we stay compliant with Microsoft licensing?
04:33 SportsFan101: Do you have a list of DBCC commands that you still use? At a recent job interview, I was asked to name all the DBCC comamnds that I know or use. What do you think of this question?
06:34 VegasDBA: Hi Brent! I have 3 AGs. I’d like to put a copy of each on another SSRS instance. I inherited hundreds of SPs using linked servers for reporting and it’s been an issue with failovers and upgrades. When hosts become unavailable or change names, reports all fail. Recommendations?
07:53 Vishnu: Is AWS storage for SQL Server glacial slow like Azure storage for SQL Server?
08:52 Banhdu: What is your opinion of Elastic Query in Azure SQL DB for cross server searching? Is it as bad as linked server queries?
09:09 Jökull: SQL best practices assessment for Azure SQL VM, shows fragmentation warnings like so Medium – Remove fragmentation of dbo.Acme.IDX_Foo_DefID index. Current fragmentation level is 25.93% What is your opinion of these best practice suggestions? Is medium too high?
09:51 Rockwell: What is your opinion of Azure PostgreSQL? Does it compare favorably with Amazon’s offering?
10:45 Bandhu: What are your pros / cons of chat gpt vs copilot? Which commercial version has more bang for the buck?
11:32 Philippe Addelia: Hello Brent, I am wondering what the best platform would be to automate tests, such as unit testing, regression testing, post deployment testing and functionality testing. Would tSQLt be a good choice?
13:04 Oli the dba: Is AWS as frustrating as Azure ? It constantly feels like one step forward two steps back ! I find this non stop relating to performance and architectural designs. Need more perf? Sure turn the dial. Btw now your HA is gone. Sigh.
14:17 Håkan: Do you have, or do you know of a course that could work as an in-house certification for basic querying skills for DW purposes? Looking for something like the old Exam 70-761 (Querying Data with Transact-SQL), but could be a bit simpler.
14:56 ChompingBits: This is an entirely academic question but I was curious if the order of the ON clause in an INNER JOIN matters. I.E. If FromTable.Column = JoinedTable.Column performs better than JoinedTable.Column = FromTable.Column. I wouldn’t think it makes any difference, but was curious.
16:13 Nom: Have you seen an Azure SQL Database/Managed instance that was about to exceed the maximum storage space allowed by Azure? How would you go about resolving issues where the cloud storage limits are going to be exceeded sooner or later?
17:52 Crazy Harry: What’s your recommended way to periodically export data out of Red Shift into Azure SQL VM?
18:12 J.T.: I’ve been a SQL DBA for 9 years now and have not used A.I. tools at all professionally or personally. In a previous episode, you’ve mentioned that the money is well spent. What is one example of how you use the tools and/or do you have any suggestions on where to begin?
20:00 Ricardo: I need to improve a function that is already fast as executions/minute = 7350. What is the best approach to go from a blink to half-a-blink please?
20:56 SQLFumbler: A developer asked if I can help extend the message column in error logs. Deadlock entries only show 300 characters, truncating details needed to identify the SP causing the locks. E.g., Backup logs show over 400 characters. Is it possible?
22:05 Renzi: Do you have any helpful scripts / tools to find permissions granted in SQL for AD users that no longer exist?
22:40 Ricardo: Hi Brent. I was going to ask about my nervousness around changing hundreds of untrusted foreign keys and constraints (as detailed my sp_blitz). But as I was typing “Brent-in-my-head” piped up with “test in a test system” and “discus with the team first”. Thanks again Brent 🙂
23:36 SteveE: Hi Brent, What are the reasons that would cause long plan compilation times on a particular query
24:14 Q-Ent: Hi Brent. I was wondering how you create maintain and monitor all the links of type brentozar.com\go\***
24:34 Garry B: Are you going to PASS Summit?

Who’s Hiring in the Microsoft Data Platform Community? November 2024 Edition

Last Updated October 28, 2024

Brent Ozar

Who's Hiring

Is your company hiring for a database position as of November 2024? Do you wanna work with the kinds of people who read this blog? Let’s set up some rapid networking here.

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.

Watch Me Stream at the PASS Summit for Free!

Last Updated October 1, 2024

Brent Ozar

#SQLPass

This week at the PASS Data Community Summit in Seattle, 15 sessions are getting streamed online for free! I’m proud to be one of ’em – my session’s on Friday. Here’s the lineup, all times Pacific:

Wednesday:

8:15 AM – 9:30 AM – MICROSOFT KEYNOTE: Fuel AI innovation with Azure Databases
9:45 AM – 10:45 AM – GENERAL SESSION: What’s new in SQL Server and Azure SQL | Data Exposed Live
11:15 AM – 12:15 PM – GENERAL SESSION: Understanding SQL Server Backup and Restore
2:00 PM – 3:15 PM – GENERAL SESSION: Inside the SQL Server Transaction Log
3:30 PM – 4:45 PM – GENERAL SESSION: Fabric Architecture: Data Warehouse or Lakehouse, Which is the Right Choice?
5:00 PM – 5:45 PM – Query Store: The Simplest Way to Troubleshooting Performance

Thursday:

8:15 AM – 9:30 AM – REDGATE KEYNOTE: Simplifying Complexity: Making Database DevOps Work in the Real World
9:45 AM – 11:00 AM – GENERAL SESSION: AI Impacting Ethics: Harnessing Toolkits for AI and Data Science
11:15 AM – 12:30 PM – GENERAL SESSION: Leveraging Copilot in Fabric: Maximizing the Power of Your Data Enterprise
2:00 PM – 3:15 PM – GENERAL SESSION: A Practical Deep Dive into I/O for T-SQL Performance Tuners
3:45 PM – 5:00 PM – GENERAL SESSION: Everything You Know About Isolation Levels Is Wrong

Friday:

8:30 AM – 9:45 AM – COMMUNITY KEYNOTE: Generating Innovation: Change Is Not Artificial, It Is Intelligent
10:30 AM – 11:45 AM – GENERAL SESSION: Watch Brent Tune a Query in SQL Server 2022
1:00 PM – 2:15 PM – GENERAL SESSION: One Year in Fabric: Lessons Learned from Implementing Real-World Projects
2:30 PM – 3:45 PM – GENERAL SESSION: Perf Tuning in Prod: Plan Forcing, Plan Guides, and Plan Correction

To join in, just go to the PASS Data Community Summit home page, put in your email, and watch the videos. See you there!

[Video] Office Hours: Let’s Have a Drink

Last Updated October 28, 2024

Brent Ozar

Videos

I was making myself a drink to film Office Hours and I realized I should just film it in my home bar. I mixed myself a gin & tonic and took your top-voted questions from https://pollgab.com/room/brento:

Office Hours: Let's Have a Drink Together

Watch this video on YouTube

Here’s what we discussed:

00:00 Start
00:50 MyTeaGotCold: Can you name anyone who radically disagrees with you? After nearly 50 questions, I trust you too much and want to hear from the dark side.
02:02 DataGuy: What are your thoughts on the Azure offerings of PostgreSQL vs AWS? Obviously you prefer AWS, but is Microsoft really that far behind? I ask because we are locked into Azure and are looking for opportunities to save on licensing for very specific apps.
03:13 DoesTimeReallyExist: Hi Brent! Do you have experience working with TimescaleDB? If so, how would you rate its performance?
03:21 Yusuf: Do you see foresee any issues granting the SHOW PLAN permission to a dev user so they can tune their queries for production SQL VM?
04:11 Dopinder: Will DBAs and software developers be replaced by AI in the next 5 years? How should they upskill to avoid obsolescence?
06:06 SportsFan101: Hey Brent, You have previously mentioned that the issue with open source is that when something breaks there is no one to call (no official support). I’ve never worked with open source. What do IT teams do when they encounter an issue with Open Source RDMS?
07:27 Mola Ram: How should we handle sending of emails from SQL Server so that recipients know why they are receiving an email and can opt out?
08:10 Mr. SqlSeeks: Perf issue caused by a new plan being generated. No idea why a different plan was built,. Days of research. Restructured the code. Now performs in all scenarios. Is it ever OK to stop looking for the root cause because you were able to fix it and spent days on research already?
09:43 Buttercup: Azure marketplace image installed SQL Server with master and msdb on C drive. Do you have recommended way to relocate or do we just need to suck it up?
10:17 nick: What should you be aware of when creating a database with enterprise level amount of tables
11:01 Rob DBA: Hi Brent! Working as more of an “Application DBA” wondering any points/methods you apply to determine a query is too far gone? Eg, slow with 88 joins to some very large tables where its doing heavy logic in the db that cant change.How to word rewrite to the business / devs?
13:49 Midlife DBA: Hi Brent, Have you had any experience with customers that have backup/recovery solutions like Rubrik or Cohesity? Any pros/cons you would be willing to share?
15:09 mailbox: Is SQL Server a good place to build a data lake? Any recommendations on DBMS that are designed for data lakes and offer good product support?
17:11 Vishnu: How good is your Internet when on the ship? Good enough to do day to day work?
18:04 Garthe Knight: What are your favorite 80’s TV shows?
18:41 Thomas Dolby: What’s your opinion of the azure feedback site used for SQL Server enhancements? Microsoft seems to advocate it on linked-in.

Launch Day Bonus: Free Stuff on Friday

Last Updated October 28, 2024

Brent Ozar

Company News

Our annual Black Friday sale launches this Friday and runs all month.

For those of you who are early birds, the folks who move quickly when the sales open on Friday, I’ve got a special bonus for you.

The first 100 buyers can use coupon code EarlyBird11 to save an additional 11% off the already-discounted Black Friday prices. When you go to put the coupon code in during checkout, it’ll show you how many are left:

Don’t use the coupon today! You wouldn’t wanna pay full price, not when the Black Friday deals are only a couple days away. See you on Friday!

Who Actually Buys BrentOzar.com Training?

Last Updated November 24, 2024

Brent Ozar

Company News

So you’re thinking about picking up some of my training & online services, and you’re wondering – do people actually buy this stuff?

Here are just a small sampling of some of the companies who’ve bought recently from me – forgive the sloppy logo arrangement:

If your manager isn’t springing for our training, maybe you should consider working for one of these companies instead. (Did I say that out loud? I might have said that out loud.)

[Video] Office Hours: Salem Witchcraft Edition

Last Updated October 27, 2024

Brent Ozar

Videos

It’s spooky time again! I visited Salem, Massachusetts again this year, this time for a friend’s wedding. I ended up in the same spot next to the Charter Street Cemetery that I filmed at last year! I went through your top-voted questions from https://pollgab.com/room/brento.

Office Hours: The Warlock of Databases

Watch this video on YouTube

Here’s what we covered:

00:00 Start
01:28 Dumped_on_DBA: How would you recommend storing data like zip codes that have spaces (like in Canada/UK), with or without the spaces? The issue happens when you want to search for a customer by zip code and the search input may or may not have a space and thus not match what’s stored in the DB.
02:40 DoesTimeReallyExists: Hi Brent! Should I use Cassandra or SQL Server for storing 500 million records daily? Each record contains 10 columns, with data types being datetime and numeric.
03:52 Frozt: What can you say about GMSA. I am using NTLogin again since they rollout cyberark and require us to change password every month which needs restart because other jobs are failing if you don’t restart the service. Do you have any opinions or gatcha on GMSA is it better than NTLogi
04:10 Otter 841: What was your opinion of Otter tune for PostgreSQL? Why do you think it failed while Constant Care succeeded?
05:43 KondzioSSJ4: Why MSSQL sometimes wants a index that have only changed order of fields in INCLUDE index creation? The index columns are the same. I have index like (X) INCLUDE (Y,Z) and query processor want to add (X) INCLUDE (Z, Y). What is the difference?
06:25 Tarzan Boy: What are your thoughts on the reserved instance trap for Azure SQL VM where your VM soon becomes antiquated but you are stuck with it due to reserved instance?
07:52 Az: Archiving needed for 10TB table with frequent updates, image column & 30M+ records. Seeking expert advice on optimized strategies for performance, storage efficiency & data consistency. Help with tools, technologies & step-by-step approach.”
10:20 Zoltan: What is your opinion of Idera SQL defrag manager for managing index fragmentation? Worth the money?
10:51 chandwich: Hey Brent! In the last Office Hours, you mentioned one skill many DBAs lack is their ability to communicate effectively. Have you considered offering a class on this specific topic? If not, are there any courses you recommend?
11:58 FutureBlogger: Hi Brent, I’m looking at starting a blog on databases and SQL Server to document my day to day learning. Do you have any tips or advice and what pitfalls should I try and avoid?
12:50 Hal: Do you foresee a day in the near future where certain ChatGPT requests for technical support will require a credit card?

[Video] Office Hours: Open Q&A About Databases

Last Updated October 28, 2024

Brent Ozar

Videos

I’m back in the home office to take your database questions from https://pollgab.com/room/brento.

Office Hours: Ask Me Anything About SQL Server and Azure SQL DB

Watch this video on YouTube

Here’s what we covered in this episode:

00:00 Start
03:15 Mattia Nocerino: I’ve always ignored database t-sql snapshots. What are good use cases, if any?
05:12 All.. Night.. Log..: Any recommendations for what to do when we hit high Tlog VLF count on our SQL AG cluster?
06:06 ProochingMan: Do you think learning R and/or Python can be useful for DBAs who function as production DBAs and who have to occasionally help with data validation for new systems?
06:54 TiredDBA: How do you feel about Multi-Factor Authentication connecting to SQL Server databases? Does this become a nuisance if a user wants to connect to multiple servers to run a query against?
07:57 InTheNameOfGod: Can you explain how to use SET XACT_ABORT ON; and should we use it with BEGIN TRY and also BEGIN TRANSACTION; ? Am I the only one confused here?
09:17 Help Help Me BrentO: We have an issue where plan age in the plan cache is less than 30 seconds. When this exists, performance is poor. When the cache cleared, the plan cache age continually grows and performance is good. This lasts for 10 min before the issue starts again. What should be looked at?
10:45 Pseudo Echo: Now that ChatGPT can answer many coding questions, will this impact the quality / difficulty of the fewer questions that actually make it to Stack overflow and DBA stack exchange?
11:44 Rebel Girl: What are your thoughts on putting the SQL system DBs on their own dedicated non C drive? Good, bad, overkill?
12:45 Mattia Nocerino: I’ve inherited a server where previous full backups are restored once a day. BI asked me to improve restore times, so I’ve left dbs in standby and restore diff + logs (basic tlog-shipping I guess). I’m noticing high variance in restore time, from 90 to 30 min. Is this expected?
14:50 MyTeaGotCold: Have you recommended DBCC CLEANTABLE at any point in the last ten years? Even if you truly need it, why not rebuild the indexes instead?
15:12 Coffee Badger: What are your thoughts on Amazon’s 5 day in office work week policy?
16:11 Queen B: Does anything bad happen if you don’t prune your backup history for boxed SQL? What job normally does this?
16:59 boersp: With Veeam and all the other SQL backup agents, do you still recommend SQL backups via maintenance plans? Or are the 3rd party backups good?
17:58 Maximus: What are your recommended resources for learning stoicism?
19:02 Hal: Will we see AI mental health counseling in the near future?
20:01 StatsDontLie: My friend is getting an error on updating statistics on a read-only copy of the database on a user-defined table. Updating stats on the read/write database doesn’t fix the issue, neither does more coffee, help!
21:06 Tommy Tutone: What are the top issues you see when migrating SQL FCI VM from single subnet to multi subnet?
23:07 DBA-Team: Are you frequently using the Vision Pro? Or was it nice at first but not so useful now?

What’s In Your Development Database? The Answer: Production Data.

Last Updated October 14, 2024

Brent Ozar

Processes and Practices

Are your developers working with live production data, completely made-up synthetic data, or something in between? I posted a poll here on the blog and on a few of my social media feeds, and here were the results:

Note that the numbers can add up to more than 100% because folks could mark multiple choices if they had multiple teams with different approaches.

I also posted it on Reddit, which has its own separate (smaller) results, but I like having samples from more audiences:

No matter which way you slice it, about half are letting developers work with data straight outta production. We’re not masking personally identifiable data before the developers get access to it.

It was the same story about 5 years ago when I asked the same question, and back then, about 2/3 of the time, developers were using production data as-is:

Someone’s gonna ask, “So Brent, what do you recommend to fix this problem?” And I just don’t have easy answers. Over a decade ago, I explained why masking data is so hard, and the commenters chimed in about the difficulties they were having.

So when vendors claim to have solved this problem, what’s the catch?

One group of products focuses on building fake data from scratch, which is fantastic in the sense that you can pre-build testing data without waiting around for customers, and you never have to worry about getting the sanitization right. However, you have to define your tables & relationships in those tools, and you’re relying on those tools to generate the same kinds of data distribution and outlier users that you end up with in production. My clients who use these tools tend to struggle with reproducing parameter sniffing issues, for example, because the created data is too evenly distributed and doesn’t account for the crazy things users do in production.

Another set of products focuses on sanitizing prod data, which is cool because you can maintain the same data distribution, but less cool because you have to define every column that needs to be sanitized, keep those definitions up to date as you add/change tables, and then when you need to refresh development, wait for the data to be sanitized. These tools make it easier to troubleshoot parameter sniffing issues, for example.

Both kinds of tools require time & attention from data modelers. You have to lay out your entire data model and define what parts of it are private. Honestly, I love this, and everyone should do it.

When Microsoft announced Purview, I thought, “This is another really good idea that very few people will ever use.” It’d be amazing if we had a central dictionary that defined every column in every table in every database. Row-level security tools could plug into that to determine who’s allowed to see PII. Data cleansing tools could plug into that whenever they needed to sanitize production data. It’d be an amazing single source of truth for all kinds of utilities.

However, back here in reality, we can’t even agree on what utilities to use, let alone the central dictionary for them all to rely on.

Back here in the reality of 2024, we’re still developing with raw production data. <sigh> We’ve got so much work to do.

[Video] SQL Server Always On Availability Groups 101

Last Updated August 21, 2024

Brent Ozar

Always On Availability Groups, Videos

I got a few closely related Availability Groups questions at https://pollgab.com/room/brento and decided to do a half-hour introduction to AGs:

SQL Server Always On Availability Groups 101

Watch this video on YouTube

Here’s what we covered:

00:00 Start
00:24 Paul Hunter: My company decided to move to AZURE. The person in charge of moving the SQL Server decided to put up an AG (I voiced my concerns over management of an AG). Now I am going to be in charge of managing an AG. Do you have any advice on how to quickly learn to manage an AG?
30:14 Bandhu: What’s the max number of DB’s you would not exceed for a single AG?
33:59 Dopinder: What is the top issue you see with clients running SQL Server AG?
35:29 Bandhu: Is there a good way to determine which databases a given app connects to (for the purpose of designing AG’s)?

Updated First Responder Kit and Consultant Toolkit for October 2024

Last Updated October 19, 2024

Brent Ozar

First Responder Kit Updates

The wording of all of sp_BlitzIndex’s warnings is now much more clear and inclusive thanks to hard work by W. Eli Hayes and VladDBA.

When Kendra Little first wrote sp_BlitzIndex about a decade ago to help you analyze your index designs, we brainstormed about how to surface warnings. Index design is not black and white: for example, for some tables and workloads, 10 nonclustered indexes isn’t a big deal, but for other tables and workloads, it’s way too many and causes blocking issues. So we needed a way to talk about warnings in a fun way that indicated some professional analysis was required, and that the situation may or may not be a big deal. We settled on psychological-sounding phrases like “Index Hoarder”, “Aggressive Indexes”, “Abnormal Psychology”, etc.

The psychological terms worked, but we had to explain ’em every time. They were more of a barrier to understanding than an enhancement. So now, your sp_BlitzIndex advice looks more like this:

Simpler, clearer, and more inclusive. Although now I gotta go re-record all my index training, hahaha! I’ll work on that after the Black Friday sales finish.

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

To get the new version:

Download the updated FirstResponderKit.zip
Azure Data Studio users with the First Responder Kit extension:
ctrl/command+shift+p, First Responder Kit: Import.
PowerShell users: run Install-DbaFirstResponderKit from dbatools
Get The Consultant Toolkit to quickly export the First Responder Kit results into an easy-to-share spreadsheet

Consultant Toolkit Changes

Updated to this month’s First Responder Kit, but no changes to the spreadsheet template. In case you haven’t upgraded in a few months, you’re probably upgrading now because your old version has expired, so here’s what we did in May because the changes were pretty big:

Supports Microsoft Entra multi-factor authentication (MFA)
Automatically retries failed connections, allowing you to more easily gather data from Azure SQL DB Serverless databases that auto-paused
No longer requires create-table permissions, so it works better in environments where you can pull diagnostic data but not see database contents
Requires .NET Desktop Runtime 7 or higher on the machine where you run the Consultant Toolkit, typically your jump box or laptop (not the SQL Server itself)

sp_Blitz Changes

Enhancement: added checks for unusual Query Store configurations and missing trace flags. (#3553, thanks Reece Goding.)
Fix: when it was run without SA permission, it would throw a false error about the number of TempDB files not matching. (#3578, thanks Tisit.)
Fix: when it was run with SA permission, the “No Failsafe Operator Configured” wasn’t being reported. (#3577, thanks Tisit.)
Fix: added some wait types to the ignorable wait type list. (#3562, thanks Reece Goding.)
Fix: when it was run with SA permission, the invalid logins check wasn’t being reported. (#3551, thanks PowerDBAKlaas.)

sp_BlitzIndex Changes

Enhancement: change index warnings from psychological terms, to more inclusive ones. (#3571, thanks W. Eli Hayes and VladDBA.)
Enhancement: faster processing of missing indexes. (#3556, thanks Matt Maddocks and Dean MacFadyen for reporting.)

sp_BlitzLock Changes

Fix: @Debug = 1 would throw an error if your plan cache was empty. (#3567, thanks VladDBA.)
Fix: the @OutputTableName parameter would sometimes throw errors depending on your database context. (#3566, thanks BilliousBT.)

If you wanna watch me troubleshooting the sp_BlitzLock errors, this is a fun one-hour live stream of me working on that last bug:

Debugging a Complex T-SQL Issue

Watch this video on YouTube

sp_DatabaseRestore Changes

Enhancement: fix backup file selection for non-split full backups used with the @StopAt command. (#3560, thanks Rob Hague.)

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: Back at Home (Briefly)

Last Updated October 1, 2024

Brent Ozar

Videos

I took a break from unpacking my SQL Saturday San Diego bags and re-packing for a Boston wedding long enough to take your top-voted questions from https://pollgab.com/room/brento.

Office Hours: Back at Home (Briefly)

Watch this video on YouTube

Here’s what we covered:

01:55 Deadlockinator: Hi Brent, Why does sp_BlitzWho return NULL in the query_text for some SPID, even when I’m running it as sysadmin? It also seems like it’s not capturing all the sessions, but SQL Profiler gets all the statements, especially the ones running with sp_executesql. Thanks!
03:46 Bruce: How prevalent is SQL Server on Linux in production environments?
04:32 Bandu: How many companies have you seen 1. Attempt a transition from MSSQL to PostgreSQL. 2. Complete a transition from MSSQL to PostgreSQL?
06:37 MyTeaGotCold: Is there a shorter way to say “Microsoft SQL Server on Amazon RDS”? None of those words seem optional! There are many SQL servers on RDS that aren’t Microsoft’s and many clouds that aren’t Amazon’s!
07:03 Ray: When Installing Cumulative Updates for SQL Server all the previous updates for the Service Pack get installed. If there are 10 CUs and CU 5 has a bug, will that bug be an issue when I install CU10?
07:52 Sean: Heyo! Inspired by your consulting video. How would you get Ops/Dev buy in on performance or optimization requests? Our infrastructure DBA team has raised tickets, standups, pushed at exec prioritization meetings, handed them all-in-one fixit scripts to test, with no luck.
09:33 Dru: Do you ever watch any of the free Carnegie Mellon database college courses on YouTube? What is your favorite?
11:04 Erzsebet: Does SQL CLR compare favorably with PostgreSQL extensions? What is your favorite PostgreSQL extension?