Blog

SQL Server 2022 Finally Fixed a SQL Server 2008 Query Plan Bug!

Last Updated April 26, 2025

For yeeeeeears, when I’ve explained execution plans, part of my explanation has included the instructions, “Read the plan from right to left, top to bottom, looking for the place where the estimates vs actuals are suddenly way off.” Here’s an example:

Things seem to be going okay on the query plan until you hit the key lookup, which brought back 13 rows of an estimated 19,452. That would appear to be a pretty doggone bad estimate.

However, that’s not an estimation problem: it’s a decade-old bug in SQL Server. ENHANCE:

If a nonclustered index seek is estimated to produce 355 rows, then when we look up their keys in the clustered index, we will only find 355 matching rows, full stop. (I mean, unless there’s database corruption, ha ha ho ho.) The correct estimate for the key lookup has to be 355 rows, not 19,452.

So in my execution plan explanations, I’ve had to constantly add, “Unless it’s a key lookup, in which case, ignore it because the estimates aren’t even right. Just keep moving through the plan and look at the join operator instead.” Paul White first blogged about this back in 2012, but it’s been the case going all the way back to 2008.

Good news! I just learned last week (shout out to Tim Tanner) that compatibility level 160 (SQL Server 2022) finally fixed this bug!

You might think, “Hey, wait, that’s a new bug, because shouldn’t the key lookup produce 355 rows, not 55?” That estimate is actually accurate, because the query’s doing some filtering on the key lookup itself. To learn more about the math on that, check out Paul’s post – which he updated in October 2024 to announce the fix, but I missed it because I don’t regularly go back and re-read Paul’s posts. That’s on me, because I should probably re-read all of them every quarter or two. Not because he updates them that frequently, but because they’re complex enough that they require re-reading, hahaha.

[Video] Office Hours: Hello Kitty Porsche Edition

Last Updated April 5, 2025

Brent Ozar

Videos

On a nice sunny Saturday, I took the 911 around the Las Vegas canyons, and stopped to answer your top-voted questions from https://pollgab.com/room/brento:

Here’s what we covered:

00:00 Start
00:56 TheWorstDBA: I have a large table with 50 billion rows. It has a partition implemented on it, with a columnstore index. SP_BlitzIndex suggest I add a NC Index to improve performance, but when I do it slows down my queries. How can I make my queries more performant? Workload mostly reads
02:44 Jack: Hi Brent, how do you come up with ideas for blog posts and demo? I liked the recent post about Index Rebuilds with ADR & RCSI.
04:13 Golden : Hi Brent! Forget SQL Server. Why not try your hand at gold? Buy it when it’s cheap, sell it when it’s valuable.
04:31 JohnSteak: We have a transactional application that sends the read statements to a RDS Read Replica. This replica does not autoscale. Do you see any performance advantage when using this architecture, or have any advice on how to measure the performance gain?
05:42 Q-Ent: Hi Brent, does it make any sense to use in memory tables instead of traditional row store tables when the data pages in row store are all cached in RAM? Are there any performance differences for different query types (SELECT . INSERT , UPDATE) ?
07:24 chris: Have you ever had to deal with the fallout of a split brain event? With all of our DR environments I should prepare for such an emergency. Are there any tools or techniques you’ve found helpful to bring the data back together in to one database?
08:15 Novocaine: Are the numbers from DBCC CHECKDB … ESTIMATEONLY reliable? My VLDB’s production DBA is telling us that we can’t run CHECKDB because we can’t afford the tempdb hit, even on SAN replicas.
09:28 MyTeaGotCold: I’m dropping lots of big tables from a VLDB, compressing what is left, and then making it read only. Any tips for shrinking the database afterwards? The data is so cold that I expect post-shrink fragmentation to matter.
10:34 April 13, 2011: Can I prove that my SAN makes me do random rather than sequential reads? My SAN admin read too much Jonathan Kehayias and is blaming fragmentation for my ills.
12:08 geopolitically_confused_dba: The recent rise in tensions between the US and Denmark has sparked a discussion of what would happen if US pulled the plug on cloud services for us. Could something like this happen, or am I paranoid? Can I prepare for such “doomsday” scenario in any way?

Building AI Calls into the First Responder Kit

Last Updated April 15, 2025

Brent Ozar

First Responder Kit

No, that isn’t an April 1st joke, heh. I know a lot of vendors are shoving AI down your throat, but I can think of a few ways I actually want to get quick, easy advice from large language models (LLM) when I’m calling the sp_Blitz scripts:

sp_BlitzCache will have an @AskAI parameter that sends the query plan to an LLM to get specific, actionable advice on how to improve the query and/or execution plan.
sp_BlitzIndex will send a table’s indexes to the LLM to de-duplicate them and generate a change script for you to review.
sp_BlitzLock will send a deadlock graph to the LLM asking for index changes, query changes, or query hints that might avoid deadlocking.

This post isn’t a debate about whether I’m going to code this – I am, period, full stop, because it’s going to be useful for some folks. If it’s not useful to you, that’s totally okay – close this browser tab and go on with your life. Let’s not turn this into a discussion of whether or not we should use tooling.

But if AI in the FRK is something you’d use, keep reading.

These features are going to require SQL Server 2025 or Azure SQL DB because it’ll rely on their new abilities to call LLMs via extended stored procedures, like sp_invoke_external_rest_endpoint. The thing I’m worried about is how we’re going to store your LLM credentials, like access keys.

Tools like ChatGPT, Gemini, and Claude are called with access keys that are unique to your account. Each time you call the service, your access key is what gets billed. If your access key gets out there – or even inside your company – then people can run up huge charges on your bill.

So right now, my design thoughts are:

I don’t want you to have to copy/paste in an access key into the stored proc each time. That would make the features too much of a pain to use, and would lead people to store the access key all over the place.
I’d like the First Responder Kit to check for the existence of a table (like master.dbo.llm_authentication), and if it exists, pull the access keys from there. Only your SQL Server user account (and other people on your nice list) should have read access to that table.
I’ll add a decryption password to the First Responder Kit scripts, and if that’s passed in, use that password to decrypt the access keys stored in the table (otherwise they can be stored unencrypted if you just want to rely on SQL Server’s security to keep people out of those tables.)
We should be able to call different LLMs based on an FRK parameter. Different LLMs have different strengths in different situations, and there are going to be times you want to experiment by asking for a second opinion. The llm_authentication table should be able to have multiple rows, with one designated as the default. This way you can store keys for ChatGPT, Gemini, Claude, and other services, and switch which one you’re calling at runtime.

When I’m coding this, is there anything else you’d like me to consider? I plan to code it this summer.

It’s Time for Fundamentals Week! Register for Tomorrow’s Classes.

Last Updated March 30, 2025

Brent Ozar

Conferences and Classes

This is it! You’ve been watching my free live Spring Training classes over the last several weeks, and they’ve whetted your appetite for serious SQL Server learning. You enjoy my laid-back, humorous approach to sharing what I’ve learned over the years.

You love live classes. You’ve tried watching recordings or stepping through demos yourself, but…you just can’t block out the time, and you can’t stay motivated while you’re watching a recording. You like the fast-paced energy of seeing me live, asking me questions, and seeing me respond to your chats.

You hate free conferences that feel like Zoom meetings. You’ve tried attending a few online events, but they felt just like sitting through one meeting after another. They didn’t dive deeply into any one subject – they just hopped around from one topic to another, and many of those topics just weren’t relevant to you. They were good for free stuff, but…

You’re ready for Fundamentals Week! You’ll spend the week with me learning about indexes, query tuning, columnstore, how I use the First Responder Kit, and more.

The online classes start tomorrow at 11AM-5PM Eastern, 8AM-2PM Pacific. Here’s what we’ll cover:

Register now for $1,595. See you in class!

[Video] Office Hours in the Home Bar

Last Updated April 6, 2025

Brent Ozar

Videos

On a sunny Saturday afternoon, I made myself a highball and went through your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
00:57 jrl: Do you have any advice about communicating effectively in a fully remote workplace where all discussion is centered around a messaging platform like Slack or Teams? The company I work for has turned into a busy chatroom, and important messages are easily missed.
02:24 Care-Giver: What is the benefit of using Constant Care instead of just running sp_Blitz once a week and emailing my team with the results?
03:52 Marian: Hi Brent! What advice do you have for a SQL dev to improve when their job feels routine and too comfortable? I’m early in my career and worry my skills aren’t being challenged enough. How can I push myself to grow and stay sharp?
05:02 Steve: Hi Brent, Any plans for a run of merchandise? I’d love a “what’s the problem you’re trying to solve” T-Shirt
05:42 Planetscale Benchmarks: Ever thought about moving Constant Care to Aurora Optimized Reads? I don’t think that you’ve blogged on it.
06:11 Dopinder: Do you have any class modules on generating synthetic data for SQL Server lower environments?
07:02 Mr. SqlSeeks: When you encounter a client experiencing DB corruption, what tends to be the most common causes of that corruption?
09:03 DamastaG: Hi Brent. When troubleshooting code that does things based on time, usually out of hours, (e.g. only run this part if it is a Sunday) is there a way to spoof your SPID’s session date/time so it will run as you need it to?
11:45 My Friend Asks: I’ve been managing databases for a long time (perhaps too long), and now have a crazy notion that I might like to move towards managing a technical team instead. Dipped my toe in a little (vacation fill-in) and was ok with it. Any training you’d recommend for making that leap?
13:38 DeveshDBA: Future Impact on DBA jobs due to the introduction of agentic AI. More and more companies will introduce database agents as well. Please share your thoughts.
14:55 Sean: Hi Brent! People are sometimes overconfident because they don’t know what they don’t know (Dunning-Kreuger Effect). What do you know you don’t know about SQL Server (or in general) that you wish you knew more about?
17:31 Kulstad: what is your stance on multiple installs of SQL server to keep important business processes running vs one SQL server to manage them all. Right now, we have 20+SQL server Standard installs, and I’m looking to save $$ with licensing
18:50 Ynze: After I restart my sql server the write latency on the main DB rises slowly from 0 to 160 over the course of three days I have tried increasing IOPS but that does not seem to help. Query optimization is not an option because we run a third party ERP. Where can I look further?
19:49 Starr Spencer: Your post today was amazing! You continue to be my hero in so many ways!
21:04 Juche: What’s the worst DB security incident in your opinion?
22:30 Bandhu: What are your thoughts on using file groups to periodically migrate older data from expensive azure storage to cheaper azure storage for cost saving purposes? Is this worth the potential headache?
24:05 Bandhu: The developers want the DBA team to periodically review the TSQL generated by their ORM. What is the best tool to give to the developers so they can capture their own ORM TSQL without needing SA permission?
25:07 Philippe: Hi, in your opinion what should be a typical SQL Server DBA calendar? What should be the regular scheduled tasks, at what interval, how much time should be set aside for support/emergencies, same for training and new stuff exploration. Something like “your typical DBA calendar”.
27:58 Lost-In-TheMountains: I inherited Merge Replication. The setup involves a main lodge and ten backcountry, remote lodges. Main lodge publishes to remote lodges, and the merge runs every 4 hours. Network is ok, w\ Starlink as backup. What would you consider implementing as an alternative to Replication?
30:11 Hypervisor has 6 TB spare: If I have almost no PAGEIOLATCH waits, is there any real reason to add more RAM?

Your SQL Server Needs Spring Cleaning, Too.

Last Updated April 6, 2025

Brent Ozar

Indexing

First things first: pick up that filthy keyboard, take it over to the trash can, turn it upside down, and give it a good, vigorous shake.

Next, go to your biggest SQL Server and run:

EXEC sp_BlitzIndex @GetAllDatabases = 1,
  @Mode = 2, @SortOrder = 'rows';

1 2	EXEC sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = 'rows';

This will give you an inventory of all of the objects in your databases, ordered from biggest to smallest. Here’s what to look for:

Ask simple questions about the 10 biggest tables. Are they supposed to be this large, and contain this much history? Do we have history-deletion jobs that are supposed to be running, but aren’t? I swear, this happens at almost every client I work with! By simply cleaning out the unused, unwanted history, we can make our maintenance jobs like DBCC CHECKDB run faster.

Look for objects with names like Backup_ or Temp_ or ToBeDeleted. We’ve all done a quick backup by copying data into another table, and then accidentally forgotten to go back and delete it afterwards. Check the Reads column to make sure it’s 0, and then rename the objects with a prefix like __To_Be_Dropped_On_20250425. Over the coming days, make sure you’ve got a good copy of the backup so that you can undelete those objects if necessary, and then on April 25, do the needful. (And this time, actually drop them – set yourself a calendar reminder.) Again, the goal here is faster maintenance jobs.

Next, look for indexes with Reads = 0, and Writes > 0. These are indexes that are slowing down your inserts, updates, and deletes, and they don’t have the payoff of making select queries go faster. Script out their definitions in an undo script so you can recreate ’em if necessary, and then drop ’em. (I’m not a fan of disabling them because I’ve seen too many homemade index maintenance scripts that simply rebuild indexes without checking the disabled status, and when you rebuild a disabled index, it goes back into place.)

Finally, look for tables with Reads = 0 and Writes = 0. Do we have old versions of tables lying around that used to be part of the production app, but no longer are? Do we still need those?

This work only takes an hour or two, and it reduces dead weight that’s lengthening your backups, DBCC CHECKDB, statistics updates, and more.

Updated First Responder Kit and Consultant Toolkit for April 2025

Last Updated April 14, 2025

Brent Ozar

First Responder Kit Updates

This quarter’s release contains a few advanced warnings & tools, like table input for sp_BlitzLock, new warnings about paused resumable index operations that will be deleted automatically by SQL Server 2022, and a new option to maintain carriage returns and line feeds in sp_BlitzCache’s output.

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: added warning for non-default database scoped configuration changes for SQL Server 2022’s options, like PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES. (#3620, thanks Reece Goding.)
Fix: @OutputType = ‘markdown’ would fail if the output had an ampersand in it. (#3610, thanks Roger Dawkins.)

sp_BlitzCache Changes

Enhancement: new @KeepCRLF parameter lets you retain the carriage returns & line feeds in query text if you want. (#3611, thanks Vlad Drumea.)

sp_BlitzIndex Changes

Enhancement: added warning for paused resumable index operations. (#3609, thanks Reece Goding.)
Enhancement: added warning for nonclustered indexes on temporal table history tables, which can break merge statements. (#3618, thanks Reece Goding.)

sp_BlitzLock Changes:

Enhancement: add table mode input. New parameters for @TargetDatabaseName, @TargetSchemaName, @TargetTableName, etc. (#3614, thanks Erik Darling!)

sp_DatabaseRestore Changes:

Fix: really long database names would cause the script to fail. (#3605, thanks Philippe Meunier.)

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.

The Waitlist is Open Now for My SQLBits Workshop!

Last Updated April 13, 2025

Brent Ozar

SQLBits

I’m excited to return to London for SQLBits again this year!

I’ve written about why SQLBits is my favorite conference, and it’s hard to believe it’s been over a decade since I wrote that post. I still feel the same way about Bits – it’s fun, different, and unafraid to run big experiments.

This year, they’re running a BIG experiment: the conference runs Wednesday, Thursday, Friday, and Saturday. You’re used to pre-conference and post-conference all-day workshops running before or after the conference, but that’s not how it goes this year. This year, the workshops are during the conference!

My workshop is all day Thursday. That means if you choose to attend mine, you’re going to miss the Microsoft keynote (which is usually just marketing stuff anyway), and you’re going to miss the general sessions that run on Thursday. I know that’s going to be a tough choice for folks – you’re going to have fear of missing out (FOMO) no matter what you choose.

Unfortunately, my workshop sold out as soon as registration opened – before I could even get a blog post out – but they’re running a waitlist. Register for the conference, use coupon code ozar102025 to save 10% on your registration, and after you register, email contactus@sqlbits.com with the email address you used for registration, and tell them that you want to join the waitlist for my session. They’re looking at adding my workshop on another day – and if they do, the space will instantly get sold out to the people on the waitlist.

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.

Space is limited, and you can register here. See you in London!

[Video] Office Hours in North Bay, Canada

Last Updated April 6, 2025

Brent Ozar

Videos

Every year, Aaron Bertrand runs a curling tournament up in scenic North Bay where he grew up. I joined in this year, and before the tournament, sat on the shores of Lake Napissing to answer your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
01:25 MyTeaGotCold: Kendra Little’s recent article on the decline of SQL Server scared me and it matches much of what I’ve been thinking privately. I’m in my late 20s and heavily specialised into SQL Server. Should I be worried?
04:03 sqlknitter: I have a chunky 17TB database, largest table 2TB (1TB IDX). CheckDB takes days to run (5 days in, 27% complete per sp_whoisactive), with a weekly reboot. How can I tell which tables have actually been checked, before I make the decision to refactor the job to use checktable?
06:11 Smart Python: What metrics can I use to determine if my manual failover AG can safely be promoted to automatic failover?
07:31 Always Query With TABLOCKX: I have an app that churns temp tables outside tempdb so often that schema locks are my biggest problem. I can ask devs to not create temp tables this way if I back the reason with stats—but how do I gather stats on something blocked for minutes yet only used for seconds?
08:45 SaintBear: What’s your opinion of the SQL upgrade assessment functionality in SSMS21?
09:08 Mike: Hi Brent! You mentioned you go to bed and get up very early. How and why you started doing this, were there any specific reasons or benefits for you ?
09:52 Dick_Bowen: I restored a sql 2008 db to sql 2022 and the size doubled. What would cause this to happen?
10:40 Venkat: What is your opinion of Uber’s new QueryGPT for converting natural language to SQL queries?
11:32 Vinneet: Is throwing more hardware at DB performance problems more likely in PostgreSQL than In SQL Server? What are your thoughts?
12:29 SilentKnight: What’s your take on standards other than “good to have”. What effort would you expend to enforce standards on an environment that is very inconsistent. e.g. DB locations, physical file names, table names. PROD: 30 srv 300 DBs of similar design (table structures, SPROCs, views)

Tap Tap Tap – Short, Silent SQL Server Tutorials

Last Updated April 7, 2025

Brent Ozar

Videos

One of the things I love about TikTok is that it exposes me to wildly new video ideas like this:

@tiipititap

? original sound – tiipititap

There’s a whole genre of videos in that style – people using tapping plastic pointer fingers on a grainy screen with no voiceover. One sub-genre (like the video above) likes to draw your attention to something that seems bad, only to have wild plot twists or last-minute surprises. Other sub-genres purport to “analyze” something completely obvious. I laugh way too hard at these, and the comments are usually hilarious too.

So, I thought to myself, hey, these look relatively easy to film – why not try it with SQL Server?

@brentozarultd

Why you should use TOP 100 or below, not TOP 101 or higher #sqlserver #sql #dba #database #azure #microsoft #tips #brentozar #pollgab #computerscience #computerconsulting #database #databaseadministrator #careergoals #spanishsubtitles #Postgres #Postgressql

? original sound – Brent Ozar Unlimited

@brentozarultd

Next title: Why you probably shouldn’t be using NOLOCK #sqlserver #sql #dba #database #azure #microsoft #tips #brentozar #pollgab #computerscience #computerconsulting #database #databaseadministrator #careergoals #spanishsubtitles #Postgres #Postgressql

? original sound – Brent Ozar Unlimited

I’ve put several of ’em on our LinkedIn page, TikTok channel, YouTube shorts, and Instagram reels so you can follow me wherever you like to get your social media.

My home office filming setup is pretty amusing. I put my phone on a video tripod so that it’s held in place, but I can smoothly turn it from left to right – useful for when I want to run multiple demos in the same video.

I hope y’all enjoy these as much as I love making ’em. I giggle to myself whenever I set these up!

Free Webcast: Avoiding Deadlocks by Query Tuning

Last Updated April 7, 2025

Brent Ozar

Company News

Deadlocks used to scare me.

I’d get the alert from a monitoring tool, and I’d look around to see if anyone was watching me. If the coast was clear, I’d… delete the notification.

Deadlocks were a mystery to me because I didn’t know how I was supposed to fix them. Sometimes the same queries could cooperate, and sometimes they couldn’t? The database server decided which one could pass, and which one would fail? What was I supposed to do about any of that? Besides, most of the time it wasn’t a problem – apps retried their queries, or users just ignored error messages and tried the process again.

But eventually, when I became a consultant, I had to confront my ignorance head-on and do something about it because some clients were having serious deadlock issues that they couldn’t ignore. I got help from smart people like Kendra Little, Erik Darling, and Jeremiah Peschka. I solved those client issues, claimed victory, and today deadlocks don’t scare me at all.

You can solve deadlocks in (at least) 3 ways:

Having enough indexes to make your queries go fast, but not so many that they cause problems for inserts, updates, & deletes
Using the right isolation level for your app’s needs, which typically isn’t the default – it’s RCSI
Or worst case, tuning your queries

I say worst case because tuning your queries is usually the most labor-intensive option. It requires knowing the queries involved in the deadlocks, being able to understand what about them is causing a problem, and knowing how to rewrite/reorder them in a way that’ll avoid deadlocks.

In this free session on April 29th sponsored by Idera, I’ll show you why your queries need to work through tables in a consistent order while minimizing the number of times you touch a table. I’ll use a real-world deadlock example and rewrite the query to resolve the issue. Whether you’re a DBA or developer, you’ll walk away with practical strategies you can apply immediately to make your queries deadlock-resistant and your applications more resilient.

If you can’t make it live, Idera will email registrants the recording later to watch on their own time. See you there!

Free Webcast Tomorrow: Fundamentals of Columnstore

Last Updated April 6, 2025

Brent Ozar

Company News

It’s time for SQL Server Spring Training! I’m teaching 60-90 minutes of my most popular classes for free over the next few months.

Tomorrow I’m teaching Fundamentals of Columnstore, and we’re going to be covering the internals of how SQL Server and Azure SQL DB store data in columnstore indexes. You’ll learn why people think it’s an index on every column, but in reality, it’s something wildly different.

There’s a lot more to cover in columnstore, like how columnstore indexes are rebuilt, why it’s so important to do index maintenance on columnstore indexes (unlike rowstore), the differences between clustered and nonclustered columnstore indexes, and why partitioning makes so much sense for columnstore indexes. We cover those in our full Fundamentals of Columnstore class.

If you’ve never attended a formal SQL Server training class before, let’s fix that and get you started for free tomorrow morning. Register now and I’ll see you at 11AM Eastern, 10AM Central, 8AM Pacific. The webcast will not be recorded – be there, be square, or buy the full class for $89.

Free Training Registration

"*" indicates required fields

Company

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

Name*

First Last

Email*

Things I want*

Free Training Class Invites

Subscribe to Blog Posts (3-4 per week)

[Video] Office Hours in My Vegas Backyard

Last Updated March 16, 2025

Brent Ozar

Videos

I’m doing a lot of travel this month, but I took a break in my Vegas backyard to go through the top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
00:29 Nick H: I’m a new starter at an organisation who have lots of rules, one of which is that the use of Intellisense is forbidden. I asked why and was told that this was a decision taken “a long time ago” and is due to some apparent negative impact on servers. Is there any truth in this?
01:28 Adrian: Hi Brent, have you ever recommended Capture Data Change or Change Tracking to your customers? If yes, what for? What is your opinion about these features? I have an internal team pushing for this to replicate the data to a different system and I am not sure it is a good idea.
02:18 Frozt: What can you say about SQL 2025? do you recommend upgrading from 2016 to 2025 or you recommend 2022 for stability? Any SQL 2025 gotchas we should think about and are you excited with something for SQL 2025? Thank you
03:43 Snowman: I recently learned that you can only take copy-only backups on AG secondaries. What is the use case for this? I don’t see how it reduces my need to backup the primary.
04:14 Dopinder: Is it worth it to be a dual stack DBA (MSSQL / Postgres)? How do you keep them separate in your brain?
05:18 sqlpadawan: Came here after your post about your sexuality. It takes guts to bring that subject publicly and receive the virtual stones. Q: How was “that” conversation with your ex-partner? Are you still close/friends? Have you lost clients/followers/friends after sharing that in public?
06:12 NikTrs: When corruption happens on non clustered indexes or non important tables which can be restored from backups, is it safe to drop the objects and recreating them to remove corruption instead of restoring the database from backup
07:29 Sysadmim: Is there a way to remove cached statistics on temp tables?Trying to solve an issue with skewed row-count on temp table by 1 odd-out execution resulting in massive scans, I’m not able to change the stored procedure to add an option recompile or update stats on the said temp table.
07:46 Trapped in the cloud: Do you still distribute “SQL Server Performance Tuning in Google Compute Engine”? It’s not in the First Responder Kit, your website, or Google’s.
08:33 Uncle: In terms of SQL DB Backups, is it better to rely on Enterprise Backup solution like Networker or Veritas (knowing it requires sysadmin, and the DBA does not have access to it) or have conventional (Ola’s script) backups to a network share and enterprise backup the shared drive?
09:40 MyTeaGotCold: I’ve never seen anyone talk about Contained Availability Groups since they were announced. Were they any good?
10:37 RoJo: Can you discuss your current guidelines for Antivirus on the SQL server. Some say don’t do it at all and harden access to it so nothing gets in. Agents can make SQL slow. Where is your balance. Trying to solve security vs performance. (remembering CloudStrike-shudder)
11:39 Mr. Zbd409: Is there any reason to keep data forever? With cheap storage (archive tier azure) , I feel like this is a consideration for businesses. is there any reason to delete data? Is it cheaper to store archive data in json rather than proprietary sql server backup file?
12:30 Bandhu: What are your pros / cons for implementing multi – tenancy at the server level (each tenant has their own database server)? Do you see this much in the field?
13:00 30-years-till-retirement: I’m about 10 years into my data career. I’ve heard some SQL MVPs on a podcast say it takes 3-6 months to find a new job. I’d assume it was 6 weeks at most. Is this a case of me having low standards for future employers? In case it wasn’t clear, I’m not a SQL MVP!
14:45 Ricardo: We use chromebooks instead of windows laptops. Whats the best way to administer cloud based (mostly azure) sql servers?
15:02 What’s the Problem You’re Trying to Solve?: “My friend” recently ran sp_BlitzIndex on a table with 1.5 M rows, result: 450 indexes, mostly “IN-MEMORY/HYPOTHETICAL” with no usage. Also 5,200 Statistics, mostly Last Updated 5 years ago. Both indexes and stats are named with “_dta” prefix. Should he be worried?
16:32 Mike: How do you treat people who do not use “sp_” prefix when creating their stored procedures ? Is it OK for you, or you kind of strict and think everything should be with a prefix ?

Fundamentals Week 2025 is Coming This Month! Get Your Tickets Now.

Last Updated February 28, 2025

Brent Ozar

Conferences and Classes

You’ve attended some of my free online streams, read my blog posts, and you use the First Responder Kit. You enjoy my laid-back, humorous approach to sharing what I’ve learned over the years.

You’re ready for Fundamentals Week! You’ll spend the week with me learning about indexes, query tuning, columnstore, how I use the First Responder Kit, and more.

The conference dates are April 22-25, 2025, and classes are 11AM-5PM Eastern, 8AM-2PM Pacific. Here’s what we’ll cover:

Register now for $1,595. See you in class!

What’s New in SQL Server 2025

Last Updated April 1, 2025

Brent Ozar

Humor

This year’s release of Microsoft’s enterprise-grade relational database focuses on the most highly requested features from customers. Highlights include:

Tooling support for SSMS like adding the debugger back in and executing the query where your cursor is on
Production DBA enhancements like the ability to restore a single table from backup, and automatic background corruption checks
New investments in SQL Server Reporting Services, like a report viewer for .NET Core
T-SQL enhancements like DISTINCT support in STRING_AGG and enhanced NoSQL support

Microsoft’s Corporate Vice President of Azure Data, Rohan Kumar, explained in an interview for the Data Exposed podcast, “For years, we’ve been telling folks to post their feature requests at feedback.azure.com, and to upvote the features they found most compelling. We’ve heard their requests loud and clear, and we’re proud to announce that SQL Server 2025 delivers on what customers have really been asking for.”

This approach marks quite the departure from the last few versions, in which the flagship features like Machine Learning Services, Ledger (blockchain-ish history), and Big Data Clusters have suffered from dramatic adoption problems. “We learned from our mistakes, and we know database engine competition is tougher than ever,” Rohan explained in the podcast. “We know we can’t afford to just slap AI on it and call it a day.”

When asked about pricing and feature limitations on Standard Edition, Rohan couldn’t promise anything, but he did say, “We’ve heard from customers loud and clear that SQL Server price increases have to stop, and that today’s hardware has outpaced our restrictions on Standard Edition. We’ll have information closer to release, and I think customers will be very excited about our willingness to compete.”

Free Webcast Tomorrow: Fundamentals of TempDB

Last Updated March 23, 2025

Brent Ozar

Company News

It’s time for SQL Server Spring Training! I’m teaching 60-90 minutes of my most popular classes for free over the next few months.

Tomorrow I’m teaching Fundamentals of TempDB, and we’re going to be covering temp table statistics, execution plans, and plan reuse.

There’s a lot more to cover in TempDB, like table variables, memory-optimized table variables, cursors, index builds, AG statistics, and provisioning, but tomorrow’s focus will just be temp tables.

Free Training Registration

"*" indicates required fields

Facebook

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

Name*

First Last

Email*

Things I want*

Free Training Class Invites

Subscribe to Blog Posts (3-4 per week)

[Video] Office Hours: Back in the Home Office

Last Updated March 6, 2025

Brent Ozar

Videos

I’m back home after a 2-week Iceland vacation. Let’s catch up with your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
02:04 Suresh: Hi Brent . Question about 128 GB memory limit for SQL server standard edition.If I set ‘Max SQL Server Memory’ to 228 GB on a 256 GB VM , SQL will use the additional 100 GB for anything other than buffer pool . Is that correct assumption ?
03:41 Jens Herrmann: Two 20-year-old databases without any clustered indexes. The key columns are uniqueidentifier and some int columns. A colleague of mine changed all the table key columns to clustered indexes, and everything became noticeably slower. Is it a bad Idea to have a clustered index?
04:52 PAul: A production database is approaching 16TB. Adding another datafile to the PRIMARY filegroup is a solution. Is there any (dis)advantage to creating a new Filegroup on a different drive and moving large tables/indexes to it?
07:07 TokTik: What are your top recommended books on SQL server performance tuning for someone planning to explore this field for the first time?
07:51 Gary: Hi B. We have a Prod db that periodically experiences hundreds of long LCK_M_X waits and blocking on the same proc. Oddly, every one of those waiting sessions and the lead blocker is a COMPILE wait on the proc’s objectid. No RECOMPILE used anywhere. Why is sql trying to compile?
09:22 MyTeaGotCold: My devs use temporal tables a lot. Can you tell me any horror stories about them? I hit a SQL Server bug with system-versioned tables today and I’m scared.
10:30 Bandhu: What’s your faith level in third party SQL backup software? Considering it to get table level restores since Microsoft won’t add this any time soon.
10:53 Archivist: I’m trying to make backups faster by making cold data read only. How do I decide if I should make an RO database, make archive tables in an RO filegroup, or partition tables and make part of them RO?
12:10 neil: A frequently used dashboard randomly chooses horrible plan and kills production server. I tried forcing plan with Query Store. Works for a while then fails, says the index it’s using no longer exists? But I didn’t drop any indexes that I know of. Not sure where to look next.
13:33 thetechguy23: The role of a DBA today seems very blurred, at least at my organisation. As well as “keeping the lights on” we’re often asked to develop integrations exchanging data using REST API’s and then own that process. Data comes from the database, right? Where do you stand on that?

Query Exercise Answer: Finding Email Addresses

Last Updated March 20, 2025

Brent Ozar

Query Exercise Answers

For this week’s Query Exercise, your mission had two parts:

Do a first pass search through the Users table looking for columns where people had snuck in email addresses where they didn’t belong
Write a more high-quality query to identify exactly which rows we needed to handle

I wrote the challenge that way because in database work, we often write a quick-and-dirty proof of concept query to get a rough idea of what we’re dealing with. Sure, in a perfect world, we’d write a query that could capture any email address, but first let’s just get a rough idea if there’s anything that looks like an email address in the Location column:

SELECT TOP 100 Location, Id
FROM dbo.Users
WHERE Location LIKE '%@%'
  AND Location LIKE '%.com';

SELECT TOP 100 Location, Id

FROM dbo.Users

WHERE Location LIKE '%@%'

AND Location LIKE '%.com';

That runs nearly instantly and returns results – some of which aren’t actually email addresses, like “@bloginfoguide.com”, but there are more than enough genuine email addresses in that list to confirm that we’ve got a real problem. (And that’s only .com emails!)

If we need to check all string columns, we could concatenate them together into one big long string, and check to see if that big long string has @ and .com in it. However, that won’t tell us which columns are problematic, and that’s going to be important if we need to do this kind of research at real-life scale – across not just all the string columns in one table, but all the tables in the database, too.

When we hand our analysis over to someone (because it’s usually a dev manager or compliance officer who needs it), they probably want it in a format like this:

That’s where dynamic SQL and metadata come in.

For challenges like this, it’s tough to beat dynamic SQL. (Trust me, I’ve tried to beat it repeatedly. I put on boxing gloves and punch my screen all the time when I’m working with dynamic SQL, but I’m still the one who ends up huddled on the floor, crying. Wait, where were we?)

Let’s start by taking that query we just wrote, but modifying it to produce output similar to what we’re looking for, and let’s build it with dynamic SQL – but not make it metadata-driven just yet:

DECLARE @StringToExec NVARCHAR(4000);
SET @StringToExec = N'SELECT TOP 10 ''StackOverflow'' AS DatabaseName,
    ''dbo'' AS SchemaName,
    ''Users'' AS TableName,
    ''Location'' AS ColumnName,
    Location AS PossiblePII
    FROM StackOverflow2010.dbo.Users
    WHERE Location LIKE ''%@%''
      AND Location LIKE ''%.com'';';
PRINT @StringToExec;
EXEC(@StringToExec);

DECLARE @StringToExec NVARCHAR(4000);

SET @StringToExec = N'SELECT TOP 10 ''StackOverflow'' AS DatabaseName,

''dbo'' AS SchemaName,

''Users'' AS TableName,

''Location'' AS ColumnName,

Location AS PossiblePII

FROM StackOverflow2010.dbo.Users

WHERE Location LIKE ''%@%''

AND Location LIKE ''%.com'';';

PRINT @StringToExec;

EXEC(@StringToExec);

That produces this query:

SELECT TOP 10 'StackOverflow' AS DatabaseName,
    'dbo' AS SchemaName,
    'Users' AS TableName,
    'Location' AS ColumnName,
    Location AS PossiblePII
    FROM StackOverflow2010.dbo.Users
    WHERE Location LIKE '%@%'
      AND Location LIKE '%.com';

SELECT TOP 10 'StackOverflow' AS DatabaseName,

'dbo' AS SchemaName,

'Users' AS TableName,

'Location' AS ColumnName,

Location AS PossiblePII

FROM StackOverflow2010.dbo.Users

WHERE Location LIKE '%@%'

AND Location LIKE '%.com';

Now, let’s get a little fancier and build this with metadata. We’ll source our object definitions from the system object INFORMATION_SCHEMA.COLUMNS:

For my quick-and-dirty purposes, I’m going to filter for schema <> ‘sys’, because those are typically system objects. I’m also going to filter for only string columns (varchar and nvarchar), but if you’ve gotten fancy with user-defined data types, you’ll have some additional work. I trust you’ll be smart enough to figure that part out, since you’re the one who got fancy there. Anyhoo, back in my environment, this gives me enough to metadata-drive my dynamic SQL:

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.DATA_TYPE IN ('nvarchar', 'varchar')

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS c

WHERE c.DATA_TYPE IN ('nvarchar', 'varchar')

So now let’s put those two things together – our dynamic SQL peanut butter and our metadata chocolate:

SELECT N'SELECT TOP 10 N''' + TABLE_CATALOG + N''' AS DatabaseName,
    N''' + TABLE_SCHEMA + N''' AS SchemaName,
    N''' + TABLE_NAME + N''' AS TableName,
    N''' + COLUMN_NAME + N''' AS ColumnName,
    ' + COLUMN_NAME + N' AS PossiblePII
    FROM StackOverflow2010.dbo.Users
    WHERE ' + COLUMN_NAME + N' LIKE ''%@%''
      AND ' + COLUMN_NAME + N' LIKE ''%.com'';' AS StringToExecute
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.DATA_TYPE IN ('nvarchar', 'varchar');

SELECT N'SELECT TOP 10 N''' + TABLE_CATALOG + N''' AS DatabaseName,

N''' + TABLE_SCHEMA + N''' AS SchemaName,

N''' + TABLE_NAME + N''' AS TableName,

N''' + COLUMN_NAME + N''' AS ColumnName,

' + COLUMN_NAME + N' AS PossiblePII

FROM StackOverflow2010.dbo.Users

WHERE ' + COLUMN_NAME + N' LIKE ''%@%''

AND ' + COLUMN_NAME + N' LIKE ''%.com'';' AS StringToExecute

FROM INFORMATION_SCHEMA.COLUMNS c

WHERE c.DATA_TYPE IN ('nvarchar', 'varchar');

When executed, that returns the queries we need to run against our database:

I like building my dynamic SQL gradually like this and testing it first manually to see if the queries are even correct. Given my typing skills and my use of alcohol, that’s not always the case, but here, these queries actually compile and run, woohoo! So now let’s turn them into dynamic SQL.

If I just built and executed them one by one, then our SSMS results tab would be chock full of lots of different result sets. I want one result set that I can copy/paste into Excel and hand to the project manager who will be dealing with this. (Ideally, I’d rather just give them the metadata-driven query so they can run it, and I won’t be responsible for copy/pasting PII into Excel, but managers sometimes just wave their hands and say, “Enough already, just put it in Excel and email it to me.”)

So I need it in one result set – to do that, I’m going to stage the data into a table. You could use a temp table for this, but I’d rather have it be restartable in case my dynamic SQL hits a snag, or in case I want to check its progress from another session.

So our script gets a little more complicated again:

CREATE TABLE dbo.DBA_PossiblePII
    (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    DatabaseName NVARCHAR(256),
    SchemaName NVARCHAR(256),
    TableName NVARCHAR(256),
    ColumnName NVARCHAR(256),
    PossiblePII NVARCHAR(MAX));

SELECT N'INSERT INTO dbo.DBA_PossiblePII
    (DatabaseName, SchemaName, TableName, ColumnName, PossiblePII)
    SELECT TOP 10 N''' + TABLE_CATALOG + N''' AS DatabaseName,
    N''' + TABLE_SCHEMA + N''' AS SchemaName,
    N''' + TABLE_NAME + N''' AS TableName,
    N''' + COLUMN_NAME + N''' AS ColumnName,
    ' + COLUMN_NAME + N' AS PossiblePII
    FROM StackOverflow2010.dbo.Users
    WHERE ' + COLUMN_NAME + N' LIKE ''%@%''
      AND ' + COLUMN_NAME + N' LIKE ''%.com'';' AS StringToExecute
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.DATA_TYPE IN ('nvarchar', 'varchar')
  AND c.TABLE_SCHEMA <> 'sys';

CREATE TABLE dbo.DBA_PossiblePII

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

DatabaseName NVARCHAR(256),

SchemaName NVARCHAR(256),

TableName NVARCHAR(256),

ColumnName NVARCHAR(256),

PossiblePII NVARCHAR(MAX));

SELECT N'INSERT INTO dbo.DBA_PossiblePII

(DatabaseName, SchemaName, TableName, ColumnName, PossiblePII)

SELECT TOP 10 N''' + TABLE_CATALOG + N''' AS DatabaseName,

N''' + TABLE_SCHEMA + N''' AS SchemaName,

N''' + TABLE_NAME + N''' AS TableName,

N''' + COLUMN_NAME + N''' AS ColumnName,

' + COLUMN_NAME + N' AS PossiblePII

FROM StackOverflow2010.dbo.Users

WHERE ' + COLUMN_NAME + N' LIKE ''%@%''

AND ' + COLUMN_NAME + N' LIKE ''%.com'';' AS StringToExecute

FROM INFORMATION_SCHEMA.COLUMNS c

WHERE c.DATA_TYPE IN ('nvarchar', 'varchar')

AND c.TABLE_SCHEMA <> 'sys';

Test that, and it runs. Now, it’s time to think about actually executing it.

Executing this sucks in the real world.

In small-world situations like the Users table, I wouldn’t even bother with dynamic SQL. I’d simply take the output of the above query, and run it manually in SSMS. It’d finish in a few seconds, populating the table, and I’d take that output and run with it. However, the larger your data set becomes, the more problematic this is going to be.

You could build a cursor and loop over the contents of INFORMATION_SCHEMA.COLUMNS, executing the dynamic queries one at a time, one per column. However, you don’t wanna do that because I’ve hit situations where read-only metadata queries, even with READ UNCOMMITTED and NOLOCK hints, have actually either been blocked by other queries, or been the cause of blocking outages. (My favorite was when a client had a production outage because someone was running sp_BlitzIndex at the same time someone else was creating a columnstore index.) I wouldn’t want your query to be the cause of the next production outage.

So if you want to take the cursor approach, dump the contents of INFORMATION_SCHEMA.COLUMNS into a user table or temp table first, and then iterate over that, using Aaron Bertrand’s tips on cursor tuning.

Another consideration is query performance. The way we’ve written this, we’re doing multiple passes against the same table over and over. This technique works great if you have a lot of nonclustered indexes on your string columns. However, if you don’t, or if you purposely want to do a table scan instead, AND if you have hardly any PII, this is going to suck because you’re going to do a full table scan for each column you want to check. In that situation, it’d be better to write your PII-checking query differently, more like this pseudocode:

SELECT *
FROM dbo.Users
WHERE Location LIKE '%pii%'
   OR AboutMe LIKE '%pii%'
   OR WebsiteUrl LIKE '%pii%';

SELECT *

FROM dbo.Users

WHERE Location LIKE '%pii%'

OR AboutMe LIKE '%pii%'

OR WebsiteUrl LIKE '%pii%';

That would require a different result-capture method, though, since it doesn’t identify which column is the one with the PII, and returns all columns. However, if you’ve got a lot of string columns, and no indexes, this query might execute faster (albeit be harder to build.)

That brings me to my last consideration: the goal of this Query Exercise wasn’t to give you a ready-to-go PII analysis solution, because after all, we’re only checking for email addresses here! We haven’t even gone into things like social security numbers or dates of birth or whatever.

Let’s zoom out.

Your original query challenge had two parts:

Do a first pass search through the Users table looking for columns where people had snuck in email addresses where they didn’t belong
Write a more high-quality query to identify exactly which rows we needed to handle

And we’ve already faced a lot of complexity with just working on #1, and only for email addresses! That’s why in the real world, when clients ask me about this kind of thing, I walk them through a quick-and-dirty check like this:

SELECT TOP 100 *
FROM dbo.Users
WHERE Location LIKE N'%.com';

SELECT TOP 100 *

FROM dbo.Users

WHERE Location LIKE N'%.com';

And if email addresses show up, our proof-of-concept check is done. We’ve got a problem: PII is where it isn’t supposed to be, and frankly, IT ALWAYS IS. Users are boundlessly creative in how they sneak PII into systems.

At that point, the project scope just exploded. We’re going to have to identify all of the PII types we’re looking for, and write robust queries to detect that PII hidden in various formats. This work is genuinely hard and not particularly performant, especially when we try to do it in T-SQL, as we covered in the recent regex post.

This is where a dedicated team, and perhaps buying a third party app, make sense. You and I aren’t going to write queries that do a good job detecting all the types of PII, especially when they’re cleverly hidden in columns like Location or masses of text in AboutMe. If our work needs to be legally defensible – and in PII cases, it does – then there’s no substitute for a team with the time to really do this topic justice.

Or, you know, just, uh, sweep it under the rug. Time to go build another feature for our app!

Why Your Azure SQL DB Hyperscale Bill is Higher Than You’d Expect

Last Updated March 23, 2025

Brent Ozar

Azure SQL DB, Hyperscale

tl;dr: if you use Azure SQL DB Hyperscale’s auto-scaling, it’s probably not cutting your bill down because like SQL Server, Hyperscale doesn’t automatically relinquish memory, and it’ll be up to you to manually manage your own memory if you wanna cut your bill.

Now for the long story.

Microsoft’s Azure SQL DB Hyperscale has an auto-scaling option that’s supposed to automatically scale up and down. It’s perfect for bursty workloads like Have I Been Pwned, a free online service where you can put in your email address and see if your login details have been leaked. (Mine’s been part of 44 data breaches, which is one of the many reasons why I use 1Password to create separate logins for each web site, and sync my logins across all devices, plus manage my one-time passwords.)

Have I Been Pwned’s back end architecture relies on Azure SQL DB Hyperscale for data storage, and uses autoscaling to automatically add more horsepower (like when there’s a big breach to import), and cut horsepower during low-load periods to save money.

However, Stefán J. Sigurðarson discovered why their bill wasn’t going down. It turns out that Hyperscale’s automatic cost savings only kicks in when your CPU usage is low and when your memory usage is low, too. That’s where the problem comes in.

Microsoft SQL Server uses all your available memory by default, and doesn’t give it up automatically. I’m simplifying – there are some small guardrails, and SQL Server will back down its usage when other apps fire up and demand memory. But the vast majority of users will look at the vast majority of servers and say, “Whoa, SQL Server sure is using a lot of memory, and it never seems to go down.”

That’s a good thing for well-managed database servers (as I explained in that post above, more than a decade ago) – and that design served Microsoft well for decades. However, it’s a problem for the cloud, especially for cloud services that are supposed to automatically scale up and down based on demand. Azure SQL DB Hyperscale is built with Microsoft SQL Server’s code, and inherits some of those design choices.

Azure SQL DB Hyperscale’s memory documentation says they fixed SQL Server’s problem, saying things like:

Unlike provisioned compute databases, memory from the SQL cache is reclaimed from a serverless database when CPU or active cache utilization is low.

Except as Stefán discovered and documented in his post, that doesn’t appear to be the case. Stefán’s post explains how to work around it by creating a stored procedure to manually down-scale your Hyperscale database based on low demand, and to run it in an Elastic Job. Good stuff. You should subscribe to his blog (button at the top right of his site) because it’s hard to find folks publicly sharing real-world Hyperscale knowledge, especially at scale.

I do expect Hyperscale’s behavior to change as it gains more public awareness, and just over time in general, Microsoft’s strategy for how they scale down (and scale up) will change. That’s just a normal part of any development process, and hopefully they document those changes and share ’em with customers.

[Video] Office Hours: Waiting for the Hot Tub to Fill

Last Updated March 5, 2025

Brent Ozar

Videos

While waiting for my Husavik hot tub to fill up, I took your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
01:28 1440×1080: always on with 2 nodes 1db, readable secondary. I’ve optimized both nodes to a point where the primary can withstand both workloads.i want to downgrade licensing from enterprise to standard. What is the process to switch from AOAG to BAG ?Do i just change the license ?
04:05 Compatability Level 140: Soon after auto-update stats triggers, I get single-row estimates for date ranges starting with yesterday. I thought the modern cardinality estimator was supposed to prevent this major error. What do I need to study?
05:11 Chase: When would having tempdb configured to only have a single file (mdf) be favorable, even if you more than 1 CPU? I was taught to have an mdf per CPU up to 8, then test for balance. A vendor system imports faster using temp tables using only 1 mdf, which makes no sense. Any ideas?
07:00 Manolis: Do you foresee a specific DB technology exploding in popularity due to close alignment with AI?
07:22 MySpecialHoneyPot: Hi, in various cases SQL Server chooses to use the clustered columnstore index where a suitable nonclustered index exists that delivers better performance. Besides index hints, is there a way to push Query Optimizer to not use the clustered indexes?
08:37 green tissue: What breaks when databases are owned by windows users? People always say it’s evil but I have never found a demo or specific example.
09:40 JerseyDBA: I was looking through sp_blitz on a server and found all the DBs have the legacy target recovery time set to 0 not 60. Auto checkpointing isn’t causing issues, any harm in leaving this and only flipping to 60 seconds if I start seeing I/O issues?
10:55 Austin Powers Shagadelic Box: My big server’s CPU never hits 70%, but I see lots of parallelism waits. Will adding more cores help? I’m just checking to see if I understand how SQL server uses CPU.
12:15 James: Hi Brent, In your opinion, would creating triggers to enforce cross database referential integrity be something you would consider, and not fall in the generic realm of ‘bad practice’?
14:08 chris: Do we know what Azure Managed Instance is using “under the hood” for HA/DR? If not Availability Groups is it something proprietary?
15:03 ChompingBits: What are you thoughts on how to work with vendors that don’t follow best practices. Our environment is mostly SQL isntances for vendor software we buy. We run SP_Blitz weekly and see lots of configuration warnings. How do you tell a vendor they don’t know what they’re doing?
16:44 AzureNewb: Do most of your clients use Azure Managed Instance or Azure SQL?
18:21 Contineo: Not all RDS instance types support putting tempdb on the local storage. What would you choose between: – a r5d or m5d instance with tempdb on the local storage but a EBS Bandwidth or 4750Mbps or – a r6i instance without tempdb on the local storage but an EBS Bandwidth of 10Gbps
20:05 SGU: So while I know that “Missing Indexes” are not 100% reliable as to the exactly index to be created, is there a was to determine what query “caused” that missing index recommendation to be made?
20:58 Depthcharge: Hi Brent! I’ve been trying to reduce PAGEIOLATCH_SH on a 25TB production database. Monitoring tool identified a 25TB+/hour of logical reads from 300 exec/hr “Task Manager” commands in the master DB, no associated SQL. Worth investigating, or should I focus on individual queries?

Get Free SQL Stuff

"*" indicates required fields

Name

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_BlitzIndex Changes

sp_BlitzLock Changes:

sp_DatabaseRestore Changes:

For Support

Thursday Workshop: Faster, Cheaper Cloud Databases

Free Training Registration

Free Training Registration

That’s where dynamic SQL and metadata come in.

Executing this sucks in the real world.

Let’s zoom out.

Hi! I’m Brent Ozar.

Get Free SQL Stuff

Thursday Workshop:
Faster, Cheaper Cloud Databases