[Video] Office Hours: Long Answers Edition


This time on Office Hours, I let a few questions piled up at https://pollgab.com/room/brento that required in-depth answers to really do ’em justice. In particular, there was a statistics question that needed demos.

Here’s what we covered:

  • 00:00 Introductions
  • 00:35 Grogu: Did DBA Brent ever have to support a version of SQL Server that Microsoft had end of lifed? Any tips on discussing this issue with management?
  • 04:19 Ahsoka Tano: What are your favorite PASS Summit presentations from prior years?
  • 07:11 Can: I have 3000 DB per SQL Server. When I need to restart SQL Services, the service is waiting on Stopping mode.
  • 09:22 i_use_uppercase_for_SELECT: When doing filtered statistics do you basically have to use full scan? It appears the where clause for filtered statistics is applied after the tablesample system is applied so you end up sampling the same data as the main statistics which wasn’t seeing this data originally.
  • 29:02 Shaheen: Is there info in SQL Server that will tell us why it picked one index over another for a given query?
  • 31:58 muppet#1: What’s your favourite SSMS plugin (assuming you use any)?
  • 33:02 DBAInProgress: What are some of the major gotchas of using TDE?
  • 34:22 Dmitriy: How would I begin to optimize a reporting query that has no WHERE clause, but has a bunch of LEFT JOINs to a bunch of other tables via foreign keys?
  • 35:55 Columnstore newbie: I have a clustered columnstore index. The segments look good but selecting TOP 1000 order by UtcDate is still very slow. Why?

Let’s Make September Our Free Community Tools Awareness Month.

Last week, I was reading a brand new article from a Microsoft employee about how you should directly query sys.dm_exec_requests in order to find out what’s running on your system.

Brent Reading Book
“Step 1: get a stone that looks round.”

I lost my mind.

There was a lot of yelling at the monitor.

In the year 2022, nobody should be reinventing the wheel. There are plenty of free wheels available for you to choose from. You’re literally wasting your time if you start from scratch with a boulder and chisel, and then try to turn it into a wheel.

The Microsoft data platform community is amazing, and has been that way for years. There are so many free resources to help you do your job faster, easier, and more accurately.

And sure, I’ve been around for quite a while, and I take for granted that everybody in the database business knows about all this cool free stuff. I’m not talking about the First Responder Kit, either – I’m talking about a stunning list of resources so large that it’s intimidating just to get started.

That’s where you come in.
What do you rely on every week?

In September, I want you to improve community knowledge about one free tool that you rely on every week in order to get your job done.

Your first reaction is gonna be that everybody already knows it, but trust me, they do not. Just by reading this blog post, you’re already ahead of many folks out there who don’t have the time to keep up with the industry. Imagine that you’re talking to a brand new hire at your organization who needs to get up to speed on how you’re able to do your job so effectively.

Pick one of these things to share:

  • Introduce the tool to readers for the first time
  • Tell a story about how it saved your bacon
  • Share a non-default configuration option that you use, and why
  • Write a review – explain what you like about a tool and what you wish was different
  • Compare several free tools that do the same thing – explain the pros & cons of each one
  • Put together a list of learning resources for a free tool – maybe you like the tool, but it isn’t easy to use, and you want to put together a set of links to show a new user where to begin

And there are any number of ways you can share it:

  • Write a blog post (if you don’t have a blog, write on LinkedIn, SQLServerCentral, MSSQLTips)
  • Record a short video
  • Improve the tool’s documentation

You can schedule it anytime you want during September. When it goes live, leave a comment here with a link to it. I’ll post a roundup post, and I’ll set up social media re-sharing so that I can keep driving new folks to your work over time. I’ll be working on it too – most of my September blog posts will be focused on free community tools.

Let’s make sure that nobody in our industry has to reinvent the wheel again!

Who’s Hiring in the Database Community? August 2022 Edition

Who's Hiring

Is your company hiring for a database position as of August 2022? 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.

Office Hours Speed Round: Text Edition

Got questions for me? Post ’em at https://pollgab.com/room/brento and upvote the ones you’d like to see me cover. I filter out the ones that are too short for video answers, and here’s the latest batch:

Thomas Franz: When I have a very large table with no statistics (or not on a specific column in the WHERE) and want to show the estimated execution plan it takes minutes because it builds the statistics first. How can I prevent / speed up it (want the create stats asynchron as stats updates)?

There’s no such thing as asynchronous stats creation, only updates. SQL Server needs the stats first to create the plan, or else the plan would literally be based on random guesses.

Kinneret: Is table ordering (Table B appears before Table A) within a view definition ever important for query performance?

Yes, read this all the way through.

SQLSteve: I have inherited a system that has an unusual index setup. Most tables have a clustered index but also have a PK non-clustered index which is the same as the clustered. IndexUsageStats show 0 for some NCI’s but can also show usage up to 50% of the Clustered.

Cool. As long as we’re just sharing stuff we got from others, I got a metal pineapple with an unusual inside compartment:

A metal pineapple - shout out to the fine folks at é

I don’t have any questions about what I have either.

Luca: Are Azure DR nodes more prone to “replica not healthy” errors? Our nodes are in a different region & every ~2 wks we see: Always On Availability Groups connection…terminated / The local replica of availability group … is preparing to transition to the resolving role

My gut feeling is that sysadmins who use the cloud are just more reckless when it comes to network config. They’re more likely to just up and change firewalls, subnet configs, routing, etc and wing it as they go.

Midwest DBA: Hi Brent, Does a Senior Development DBA need to be better at writing T-SQL than developers? Do you need to be some sort of T-SQL Guru, or just know the most common anti-patterns. Also, any good book suggestions on T-sql Anti-patterns?

I think they need to better understand the execution plan ramifications of the T-SQL they write. For T-SQL books, get Itzik Ben-Gan’s.

WhyIsItAlwaysOnFire: Any recommendations to prevent Non Yielding Scheduler errors? Seeing occurrences of this and subsequent thread pool starvation occurring during index maintenance.

Patch SQL Server, Windows, and your hardware drivers/firmware/BIOS.

Film_Buff: Hello! I’ve heard you say that doing sorting in SQL Server is expensive since one pays for licensing, and that it should be offloaded to the app. Is this a difficult task for developers to workout? What are the things to consider in regards to design and implementation? Thanks!

Your developers should be able to sort a list or an array fairly easily. That isn’t something that requires a lot of design planning. You’re overthinking it.

Midwest DBA: Will my SAN admin try and get me fired if I make all Identity columns Big Int going forward? Or is there a case when you can trust the business and set Identity to Tiny INT or INT?

Read how row compression works.

Martin: Our company has 3rd part DBA management. They do one size fits all index maintenance( reorg >5% and rebuild >30%) for all user DBs once a week. Is it better to allow them to do this, or not? They also run a nightly update stats job against all user DBs. Is this helpful or harmful

If you don’t trust your remote DBA firm, I can help you find a better one.

Garry Kasparov: I have a query that calculates Instance wide signal wait time as a % of clock time per period. if (instance wide signal wait time / Clock time )*100 = 100, then I’m thinking the instance would benefit from an additional CPU core. Is this a easy way to determine more cpu?

Without looking at the queries using the most CPU to see if you can tune them? That seems like a pretty expensive script.

DBA douglas: Hello Brent, I’m currently in the USA. I’m wondering if I can move to Europe or New Zealand, and provide American Companies off hours DBA support? Do you think companies would find it valuable to have an American Citizen Working in a different time zone?

Why would their DBA need to be an American citizen?

I_wish_I’d_done_better_in_school: What are the 3 most common issues that clients hire you to fix?

Slow performance (#1 by far), unreliable SQL Servers, and planning for the future.

I_wish_I’d_done_better_in_school: Hey Brent, Do you see many productions DBA’s skilling up in powershell still? Or are there new tools that make this unnecessary? I’m trying to get my team to adopt DBAtools, but my manager says that all the DBAs would have to learn powershell and that is too much to ask.

Be the change you want to see in the world. Consistently solve problems with PowerShell faster than your teammates, and they’ll want to be more like you. (Assuming, that is, that you can – if you can’t, then there’s your answer too, but you don’t need them to adopt it first in order to improve your own skills.)

Greef Karga: What are your pros / cons for generating / executing dynamic SQL in the C# client app vs doing so in a stored proc?

Developers are best at the language they use the most. What language do your developers use the most?

Qaiser: Rolling upgrade or Backup Restore for database migration in AG from sql 2012 SP4 to sql 2019 Enterprise. Some experts say that Rolling upgrade is risky as no rollback option.

Read this.

sqldeo: Hi Brent,I got unusual issue for my tempdb,i have 8 tempfile for 8 core cpu but not growing all equally (initial size and growing size equal across all files)using sql 2019 so traceflag 1117 also not recommended by MS,any solution to look the problem !thanks

Grow ’em out manually.

Seriously, just do it once and be done. Grow ’em out to 25% of the total size of the databases on the server for starters, and just be done with it. Why waste a minute of your life trying to solve this problem? If they continue to grow beyond that size, take my Fundamentals of TempDB class to understand what’s using the space and what you can do about it.

Valdemar: Should stored procs that create local temp tables also drop the local temp tables upon completion?

You can if you want, but it doesn’t really matter, as Paul White discusses in these posts. SQL Server keeps the structure & statistics around for other sessions. The drop only affects your ability to access the temp table after your proc finishes.

WBurgh: Good old MDS, any word on the street on when it’s being killed off? Asking since I’m not seeing anything in the Azure pipeline from MS.

No, but I certainly wouldn’t do any new implementations of it.

LegalEagle: Good Morning Brent! I have an availability group running on physical hardware and wish to add an additional node but add it as a VM to offload reporting. Can I mix a physical SQL with a VM SQL in the same AG? Google doesn’t seem to have information on that. See you at PASS!

Yes, you can, period. See you in Seattle!

Scrootch: My friend is struggling with PAGELATCH on the tempDB, leading to GAM contention. Server spec: 2019 Enterprise, 24 CPU/tempDB files, 320GB memory, 760 batch req/s. Would adding more tempDB files than CPUs have a negative effect to address this issue? Eg adding 32 file vs 24 CPUs.

Check out my Fundamentals of TempDB class.

Maksim: Has moving lob data off row for a given table ever moved you across the performance finish line?


CakeAndEatItToo: The ‘missing index’ dmv is limited to 600 results, but for a SaaSy server with 1000+ db’s there’s a lot of cpu cycles spent cleaning up every couple of minutes. Is it possible to increase the limit or scale back the cleanup or do ANYthing without turning the whole thing off?

I’d be curious as to how you’re measuring that. Post a question on DBA.StackExchange.com with your evidence showing the cleanup of that DMV is causing overhead on your system.

Bill: I have an OLTP system with cache instability, but plenty of cpu to cover it. I had a frequently run proc that runs in <10ms., been stable for years, start blocking, and queries to timeout after 30s., seemingly due to it being Recompiled. indexstats are current. Where to start?

Run sp_BlitzCache, look at the Priority 1 warnings, and read the URLs in each of those.

Kinneret: Have you ever written an epic SQL query only to see SSMS freeze up or crash?

Click File, Save. Save your work every few minutes. No different than the advice I’d give to anyone using any computer application, ever.

Mollusk: What are your thoughts on running SQL HA technology like AG’s or a failover cluster instance in a public cloud. Issues are that VMs could be vmotioned (a lot). How will this affect auto-failovers or cluster health? Can I just set healthcheck and timeout settings higher?

I haven’t done hands-on AG work in years, sorry. I got out of production DBA work because complexity kept rising, fragility kept getting worse, and I kept hitting weird undocumented problems. I got tired of being on-call to fix that kind of thing.

J Katz: How to get a job starting out with perf tuning? Im a Senior infrastructure DBA, and I want to get experience perf tuning –I love your courses BTW, best investment ever!. Does Microsoft have positions like this? Should I expect to take a pay cut?

Use the Buddy System. Call people in your network who know you know your stuff. They’ll get you past the stack of resumes. Generally speaking, Microsoft doesn’t tune the T-SQL or indexes that runs on their customers’ servers.

DataPayload: Hello Brent, My friend understand that 1=1 help to comment out and 1=2 in create table without data in where clause. He is not sure if these helps the query logic or plan any other way. Can you please help him. Thank you.

Read this.

SQLServile: Our server is to be replaced with a newer one, which is fine. The staff doing this though, say it will be “too hard” to retain the original servername in this exercise. A new name will break countless connections (but hey, not their problem). Is there a less disruptive way?

They’re right. Instead, immediately after migrating to the new server, rename the old server as something else, like PROD_OLD. Create a DNS CNAME (alias) for the old name, and have it point to the new server’s name.

Yousef: Is it ever OK to specify an additional / redundant WHERE condition value to get a better index selection?

I would want to see an example.

Dan Griswold: Should Managed Service accounts like NT SERVICE\…. be disabled for security purposes? Why or why not? I have searched all over and can’t seem to find a clear answer.

I don’t do security work.

Raguel: Do you know of anyone that has ported the Stackoverflow or AdventureWorks DBs to other platforms (PosgreSQL, etc) for bakeoff performance testing purposes against SQL Server?

Read this.

Galina: What are your pros/cons of streaming on Youtube vs Twitch?

In my experience, Twitch pays better. When I was streaming regularly, I was getting $500-$750/month from Twitch, where as I don’t think I ever cracked $200/mo from YouTube.

Stacey Q: What is the largest DB you have seen that had enough server memory to cache the entire DB in RAM?

About 1.5TB. I’ve seen much larger databases, but they just weren’t cached in RAM.

Shaheen: How does index tuning for data warehouse queries differ from index tuning for OLTP queries?

These days, data warehouse tuning tends to start with clustered columnstore and go from there. OLTP tuning starts with multiple rowstore indexes.

Don’t Blame Anthony: How does SQL server determine row order for “Select Top” or “Delete Top” when no order by clause is specified? I see this pattern frequently.

Whatever rows the query plan pulls first, that’s what comes out. Order isn’t guaranteed without an order by. It can change based on parallelism, available indexes, merry-go-round reads, all kinds of stuff.

Stockburn: Hi Brent, company has 600K+ databases in Azure SQL DB. How would you change your perf tuning methods, investigation when faced with so many DBs. They all use a similar schema but may be used more / less heavily. We have been using query store, interested in your advice.

Read this.

Marc Spector: Sometimes when we run sp_whoisactive we see long duration sessions from our .NET apps with a sql_command of “sys.sp_reset_connection;1” and status of “dormant”. What causes this? Is this anything to be concerned with?

Read this.

TheCaptain: Hi Brent, Some developers at my organisation have written a module to encapsulate some common client parameters, making them optional and default: timeout=0, connection_timeout=0. What’s your opinion and do you know of any articles with guidance on best practices?


MikeNM: I was watching your talk about debugging stored procedures, and your laments about the death of debugging in SSMS. Have you worked with SQL debugging in VS Code? If not, what do you recommend?

I use the technique I describe in the video.

Cats_Everywhere: Brent, Do you know any DBAs who have taken advantage of the WFH movement to move to a low cost of living city/area? Any suggestions? Must have great restaurants!

Yes, but it’s typically been to move closer to family.

Tefnut: When SQL connections are re-used via .NET connection pooling, does the sproc author need to take care to reset connection level settings that were enabled in the executed stored procedures? Ex: At the end of the the sproc do SET NOCOUNT OFF, SET XACT ABORT OFF, etc.

No, but remember that the procs might be called by other things, not just .NET.

Yourbiggestfan: Hi Brent, Can you point me to any website/blogsite which has performance tuning challenges/exercises for SQL Server where we can test our knowledge? Your invisible indexes challenge is the kind I am looking for.

Yes, click Training at the top of the site.

Emelio: What is the best book on Data Warehouse Design?

I have no idea.

NotCloseEnought2RetirementToStopLearning: Hi Brent My shop has been mainly SQL 2012 and 2016 (nagging my manager to upgrade). Can you recommend key\cool features released in SQL 2017, 2019 and 2022 that I should focus on so I Iook super smart when we do upgrade? One feature per version please.

Read this. 

Taweret: What are the top rules / guidelines from older SQL Servers that you see used on new SQL Server versions but shouldn’t be?

To monitor Page Life Expectancy and to lower Fill Factor in order to reduce fragmentation.

Khonsu: What are the pros / cons of creating NC temp table indexes inline vs a separate step after table creation?

Read this and/or attend my Fundamentals of TempDB class.

Steven Grant: Do you have a recommended sample DBA calendar that shows admin functions to perform daily, weekly, monthly, yearly for SQL shops with little previous DBA structure?

No. Neat question, but no.

James: Hi Brent – With the non stop surge towards cloud, companies desiring and hiring more towards BI/Data Teams/Engineers etc, and being adamant that we are not needed, is it time to accept the inevitable that all respect and understanding of a DBA is gone and we need to move on?

Yes, as you can tell by the complete lack of database questions above, there’s no work left to be done. You should move on. You go on ahead without me. Best wishes, hugs, thoughts, and prayers.

Midwest DBA: Who is the Brent Ozar of Data Warehousing? Do you have any recommendations on training resources?

Data warehousing is being reinvented, and there are tons of approaches. For example, check out James Serra’s video on what a modern data warehouse entails:

Office Hours: Dodging Work Edition


Post your questions at https://pollgab.com/room/brento and upvote the ones you’d like to see me cover. Today, I’m dodging work, so I went through your questions while I waited for the coffee shop to open:

Here’s what we covered:

  • 00:00 Introductions
  • 03:28 Ivan: What are the top signs that a table has a poor clustered index?
  • 05:39 NotCloseEnough2RetirementToStopLearning: Hi Brent, it seems the job market wants and is paying more for data generalists (wider but less deep skillset) than data specialists (DBA, deep but less wide skillset). This seems to be limiting salaries and the number of Senior positions. Any thoughts or comments?
  • 07:52 Gennady: When should you use a table per year of data (and union them together in a view) vs using formal sql table partitioning?
  • 12:32 Mr. Ed: How would I determine if my IT team needs a DBA?
  • 14:28 Tom: Can you recommend a live T-SQL class?
  • 15:23 Jorriss: If you were a woodchuck, would you prefer brick and mortar structures?
  • 17:18 J.P.: Why should I learn noSQL if I know SQL? Is it only because of the speed?
  • 19:06 Rob: We have a 4.8 tb SharePoint database that is causing a lot of issues like backups due to the file size. Has anyone run across this issue?

[Video] Fundamentals of Stored Procedures at SQLBits

T-SQL, Videos

Anybody can write a stored procedure with a little help from Google. This session is about how to write stored procedures that have a high likelihood of performing well and are easy to troubleshoot.

This fast-paced, all-demo session from SQLBits will NOT cover how to write a query, syntax, or performance tuning. This is about good best practices after you’ve written the first one – things like how to catch errors, how to pass in multiple values, how to debug without the debugger, and more.

If you enjoyed this session, check out SQLBits 2022’s free video library with the other sessions from this year, and for all years.

Why Adding Some Memory Doesn’t Fix All Caching Problems

Wait Stats

It seems obvious: add some memory, and SQL Server should be able to cache data, thereby making queries run faster … right?

Well, if you can’t cache everything you need for a query, you might be surprised to find that SQL Server may still read the entire table up from disk, regardless of what parts of the table are currently cached in memory.

Let’s start with a little setup. I’m going to set the server’s max memory at about 8GB, which isn’t enough to cache the entire Comments table from the Stack Overflow database. (I’m using the 2018-06 version of the database, in which the Comments table is about 21GB. I’m also going to drop all of my nonclustered indexes to force SQL Server to do a table scan.

Then in order to demo the problem as quickly and easily as possible, I’m going to write a query that you probably wouldn’t normally write. I’ll take the Comments table of the Stack Overflow database, which has a clustered primary key on the Id column, and query it to find all the comments Jon Skeet has ever made:

A couple of things to recognize:

  • There’s no index on UserId, so SQL Server will need to scan the entire clustered index
  • The ORDER BY Id actually pays off a little for the clustered index scan – because we asked for the data sorted by Id anyway

The first time the query runs on 8GB RAM…

If we look at the actual execution plan, we spent 48 seconds waiting on PAGEIOLATCH (storage reads) in a 22-second query:

During that 22-second query, we read the entirety of the Comments table up from storage. We can prove that by checking sys.dm_io_virtual_file_stats before & after the query runs. That’s where SQL Server tracks how much it’s read & written from the various data & log files:

SQL Server read 21GB of data from the Stack Overflow database, which is spread across 4 data files. That’s the size of the Comments table. Okay, so far so good.

The second time the query runs on 8GB RAM…

We’ve already got at least some of the table cached in memory, right? If we rerun the table again, surely our PAGEIOLATCH waits will drop a little because the buffer pool is warmed up, right? Right?

And we read the same 21GB of data up from disk again:

The problem is that this query needs the data sorted in the order of the clustered index’s Id, so it’s going to do a scan from the beginning of the clustered index to the end. When the query completes, sure, some of the Comments table is cached in memory – but only the end of it, the highest Ids. So when our query starts again, it’s like Groundhog Day – we begin reading the table from the beginning again.

Adding just some memory doesn’t fix this.

Let’s amp our server’s memory up to 16GB and try the query again:

I’m also running the query a couple of times just to make doggone sure the buffer pool is warmed up and that SQL Server’s total memory has risen up to match its target. Then, run it a third time, and check wait stats and storage reads:

The query still takes 22 seconds, and we still wait about a minute on storage because we’re reading it all back up from disk again:

But cache the whole table, and magic happens.

Raise the server’s memory up to 24GB, enough to cache the 21GB Comments table, and run the query a couple times to prime the pump:

Suddenly the query finishes in 3 seconds instead of 22, and we don’t spend any time waiting on storage whatsoever:

Because we did no physical reads at all – the only activity during this span was a little writing to TempDB by sp_BlitzFirst itself:

Keep in mind that this is a simplified demo to illustrate a complex problem:

  • Real-life queries are more complex than this, especially in how we have to dig into the plan to understand why they’re scanning an index in order.
  • Adding an index to get a seek is a great solution – but only if you’re reading a relatively small portion of the table. If you seek in and read a range of rows, especially on real-world-sized data sets, you can still hit this problem.
  • Real-life servers serve multiple databases at a time, each running multiple queries. It’s hard as hell to cache everything you need.
  • SQL Server uses memory for lots of things, not just caching data, and they interact in tricky ways.
  • This is especially tricky in cloud VMs where you might want to make a giant leap up in memory, but you’re required to buy way more CPUs than you need in order to get more memory.

The point of the story here was to explain why adding memory – even doubling or tripling it – might not be enough to put a dent in PAGEIOLATCH waits. If SQL Server wants to read pages in a specific order, and the pages it wants aren’t in memory, it will throw out cached pages on the very same object – only to read those pages back up from disk again later while executing the query.

How to Add Invisible Indexes in SQL Server Enterprise Edition


Every now and then, a client says, “We want to add indexes to this third party application, but if we do, we’ll lose support.”

No problem – enter indexed views.

Say our vendor app is the Stack Overflow database, and the app constantly queries Users by display name:

That query plan scans the Users table because there’s no index on DisplayName:

And our mean ol’ nasty vendor won’t let us add any indexes. No problem – let’s add a view:

A regular view like that is just syntactic sugar that makes it easier to write queries. You hide the complexity in the view, and then people can easily select stuff from the view without understanding everything that the view is pulling together. A view is just a T-SQL shortcut, but it doesn’t change the database structure.

However, you can turn a regular view into an indexed view (or a materialized view, different terms, same thing) by creating a unique clustered index atop the view.

Normally, when we think about clustered indexes, we would assume that the Id column is the right one to use here, since it’s unique. However, the entire purpose of what we’re about to do is just to give ourselves an index on DisplayName – so let’s cluster on that:

Then run our query again and check out its new execution plan:

SQL Server Enterprise Edition says, “Hey, you’re looking for Users with a specific DisplayName – it just so happens I’ve got a copy of that over here, sorted in order!” (Standard Edition doesn’t know this trick.) It produces a nifty execution plan with a key lookup – without actually having an index operation on the Users table itself.

The real beauty, though, is that when our mean ol’ nasty vendor looks at the indexes on the Users table, they have no idea that we’ve done anything. If we need to open a support case with the vendor or upgrade the software, we can temporarily drop our indexed views, and then recreate ’em after the support call is over. (That’s actually important to do, too – if the vendor tries to alter the table, their scripts can fail due to the presence of the indexed view.)

Indexed views have tons of gotchas, and explaining ’em all is way outside of the scope of this blog post. I demo ’em in the live Mastering Index Tuning class next month, or if you’ve got the recordings, head over here now.

Speaking of which, you know who else has the recordings? Paul White and Michael J. Swart, both of whom had good answers to the challenge in Monday’s blog post. Paul used the indexed view, whereas Michael had a more, shall we say, “creative” solution.

Updated First Responder Kit and Consultant Toolkit for July 2022

Summer has turned the corner. Sure, right now the First Responder Kit is wearing its swimsuit, basking in the sun, but already as we speak, the days are getting shorter. It’s only going to be a matter of time before the Pumpkin Spice release of the First Responder Kit. Go outside and take a walk, work on your tan while you still can.

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

To get the new version:

Consultant Toolkit Changes

I updated it to this month’s First Responder Kit, but no changes to querymanifest.json or the spreadsheet. If you’ve customized those, no changes are necessary this month: just copy your spreadsheet and querymanifest.json into the new release’s folder.

sp_Blitz Changes

  • Enhancement: ignore SQL Server 2022’s default In-Memory OLTP usage for TempDB if it’s low. (#3110)
  • Enhancement: ignore SQL Server 2022’s idle POPULATE_LOCK_ORDINALS wait. (#3105)
  • Enhancement: ignore queries in system databases that have recompile hints. (#3119, thanks Erik Darling.)
  • Fix: typo on a check. (#3116, thanks Andreas Jordan.)

sp_BlitzCache Changes

  • Fix: should no longer get arithmetic overflow errors on servers with huge numbers of reads and writes that overflowed bigints. (#2980, thanks sunsickteck and RihoA.)
  • Fix: the Average Max Memory Grant column contents formula was wrong. (#3120, thanks MrTCS.)
  • Fix: make warning capitalization more consistent. (#3096)

sp_BlitzFirst Changes

  • Enhancement: the file stats output section now shows the database name at the far right. (#3118)
  • Fix: only repopulate the ##WaitCategories table if its contents are older, not newer. (#3092)

sp_BlitzIndex Changes

  • Fix: lower memory usage on SQL Server 2019 instances that are facing a bug in an out-of-control number of entries in sys.dm_db_missing_index_group_stats_query. This DMV is only supposed to have 600 rows, but it looks like in some older 2019 CUs, the number of recommended index plans wasn’t capped the way BOL says it should be. (#3085, thanks Paul Neering.)

sp_BlitzLock Changes

  • Enhancement: add spid and wait_resource columns to output. (#3101, thanks David Hooey.)

sp_DatabaseRestore Changes

  • Enhancement: look for Ola’s CommandExecute proc in the current database, so it should be supported in more non-system-database scenarios. (#3094 and #3095, thanks Ben Wiggins.)
  • Fix: case sensitivity typo involving BackUpFile. (#3100, thanks Maarten Clardij.)

Bonus changes: Anthony Green kept the SQL Server versions file up to date.

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.

SQL Server Pop Quiz: A Key Lookup Without the Index

Execution Plans

How do we get this to show up in an execution plan:

Without having an index seek or index scan operation on the same table?

Take any copy of the Stack Overflow database and write a query that will produce a key lookup on the Users, table, but it’s not allowed to have an index seek or index scan operation on the Users table. You’re allowed to change the database structure – for example, if you need to change the clustered index, drop it, add nonclustered indexes, whatever – and of course include those changes in your comment as well.

Post your query in the comments here by end of day Wednesday, and I’ll pick my favorites and put ’em in the blog on Friday. My favorite creative answers will get 1 year access to the Recorded Class Season Pass: Masters Classes. My own answer blog post is written & scheduled for Thursday.

[Video] Office Hours in the Valley of Fire State Park


I drove the Ferrari out through the Valley of Fire State Park and stopped out in the middle of nowhere to take the questions you’d upvoted at https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Introductions
  • 00:39 Saket Mittal: Hi Brent, I am working with a developer on a query, this query has lots of join , cross apply subqueries etc. When running this query, it gives result within 2 mins, but when this query runs as select … into #table it keeps running for ever. Could you suggest the reason?
  • 02:43 Anthony DiNozzo: Did DBA Brent ever have to deal with boredom on the job? How did they deal with it?
  • 04:40 Lennie Briscoe: Do you read the SSMS release notes? Anything exciting of late?
  • 06:04 Rashad: Has SQL trace flag 1800 ever boosted you over a HADR / Log shipping speed bump?
  • 06:54 Martin Guth: Hi Brent. Regarding HA I soon have to switch from FCI (we ran for years) to another option (currently running Standard Edition). What’s your take on Mirroring in 2022? Is this still an option? Can you name 3 perks moving to AGs on Enterprise Edition could bring regarding HA?
  • 09:50 Too Much Spare Time: Hi Prof. Party Person, my friend is an app developer (but long time SQL Server enthusiast) working at a big company with hundreds of sql servers & team of DBAs. He feels like only one DBA is friendly/easy to work with – any suggestions to keep on the rest of the team’s good side?
  • 11:42 The Ferrari

Office Hours Speed Round: Text Edition

Not all of the questions you post at https://pollgab.com/room/brento are hard. Some of ’em can be answered in one line:

Q: accidentalDBA: Hi Brent, My friend is moving from two physical servers (production + failover) to VMs on the same host and SAN. His manager wants to keep log shipping as a DR solution. I advised against this as most disasters would affect both VMs equally. Am I missing something?

If both your servers are in the same physical location, you don’t have disaster recovery. You just have disaster.

Q: Efraim: When refactoring a clustered index for a multi-terabyte table, is it recommended to take the db out of full recovery and put it into simple recovery mode first? Concerned about maxing out the transaction log during the refactoring.

Will the business allow you to lose point-in-time recovery while this operation is happening? If no, then no.

Q: Doug E: Is Azure Data Studio ready for SSMS users to make the switch? If not, what functionality needs to be added to Azure Data Studio?

Why do you need to make the switch? Your answer to that question will determine whether Azure Data Studio has what you need in order to switch.

Q: Pramod A: On one of the servers, BPE is enabled and the SQL max memory is set to 4x the physical RAM. Does this make sense ? Are there any best practices for SQL max memory configuration when BPE is enabled specifically ?

I haven’t seen a good use case for BPE. Read this. I’m sure there *is* one somewhere, I just haven’t seen it.

Q: SaveTonight: You always say that once we move to the cloud we cannot move back to on-prem. Azure doesn’t let you manage your backups, but there must be a way to go back to on-prem from Azure SQL Database. Azure/AWS cannot steal your data, that would be a law infringement, right?

They didn’t steal it. You gave it to them.

Q: AlwaysLearningDBA: Hello Brent, a friend of mine : ) has been asked to implement TDE on two node AlwaysOn with Replication. He is starting with Failover Cluster with Replication for now.He knows that you don’t work with Replication.Google didn’t help much.Do you recommend any resources about this?

You’re asking me for good learning resources for things that you know I don’t use? Okay, uh, sure, try this. That video is everything I know about 2-node AGs with TDE and replication.

Q: Jack McCoy: Which job provides more satisfaction / income, development DBA vs production DBA?

For me, development DBA because I’m not on call, and I can provide a high value in a short period of time. Production DBA work is more grueling, involves a lot of on-call and weekend time, and isn’t as financially rewarding because you’re seen as the cost of doing business to keep the plumbing flowing.

Q: Anthony DiNozzo: What percentage of work time should DBA’s commit to continuing technical education?

Learning the right stuff pays off in higher future earnings. How much more would you like to earn in the future, as opposed to today? Figure out what you want to earn, and then learn what it would take to earn that. If your goals are minor and incremental, your current employer might sponsor it. If your goals are major and involve leaving your current employer, well, you’re gonna have to figure it the %$&* out.

Q: Joe Friday: Is clustered index fragmentation any more / less detrimental to query performance than non clustered index fragmentation?

Watch this.

Q: Pramod A: A table has 80 statistics ( Index + system ) on table row count of 160 million. To improve the performance of the query, if I have to pick few stats specifically and update those stats with full scan, how would I know which stats to pick in order to expect a performance gain ?

The ones the query is using for its cardinality estimation decision. We cover how to do that in my Fundamentals of Query Tuning and Mastering Query Tuning classes.

Q: Omer: Did DBA Brent have a outlook rule / folder strategy for classifying / triaging the various SQL errors encountered on a daily basis during their career? If so, what strategy was used?

As soon as you start putting alert emails into a folder, you’re screwed. You’re not looking at those folders. Go change the alert thresholds so you only get actionable emails you’d want to have in your inbox.

Q: Ned B: Is there any harm / benefit in using SQL page compression if the SAN is also compressing the data?

Compressed pages can help you can store more in memory and get faster throughput to storage since less 8KB pages go across the wire.

Q: DBAInHiding: If you’ve been employed as a staff employee for various companies over the years but feel your next gig as a DBA or developer will require you to be a 1099 contractor, what’s the essential checklist to prepare for such a move and what gotchas should you be on the lookout for?

I don’t have an easy answer for that one. I’d pick up a book on how to start freelancing.

Q: Anul: What is the ideal cluster and sector size for new SAN drives hosting SQL data files and transaction log files?

Ask your storage vendor.

Q: Kurt Wagner: What are the monitoring hurdles / challenges when moving bare metal SQL from onprem to Azure SQL? Do we throw away our current SQL monitoring software?

Not all of the same monitoring data is available, and when it’s available, it has to be captured in different ways.

Q: Hello Brent, my friend need some help to understand apply, cross apply and outer apply operator. He understand joining table and function using those, but not joining 2 table using cross or outer apply. Probably he is missing some basic understanding.

Pick up this book.

Q: Andrew: Hi Brent, On daily basis we keep our data on a SAN storage, so does the SQL servers, not so long ago we made a SQL RAS, and i asked the Microsoft Premier Field Enginer about the virtual disk separation for the SQL data files. What do you think about it?

What’s the problem you’re trying to solve?

Q: Raphra: Hi Brent, I thought I knew how a recursive CTE works until I saw the execution plan of a recursive CTE (that was to return the last 100 calendar years) and I saw a Nested Loops (inner join). Can you explain what a Nested Loop operator is doing there?

Not without seeing the plan, but I’m not sure why you think nested loops are bad. They can make perfect sense for some operations.

Q: Hany: Hello Brent, what is the best Azure video learning resources out there?

Read this.

Q: Bob: Why is an Int primary key on a date dimension recommended instead of a date? All tables that link to the dim keep their native type and can use built in date functions. This can also avoid joining the Date dim for simple queries.

Ask whoever recommended that to you.

Q: Quartz: Why does CAST(‘2022-06-13’ AS DATETIME) fail for SET LANGUAGE british but work for SET LANGUAGE us_english?

I didn’t know British was a language.

Q: DBABA (database administrator by accident): What are good alternatives to dacpac deployments? (Not having to write manual migration scripts but defining the target state of the database)

Check out Redgate Schema Compare.

Q: I Inherited It: I have a server that has a 14TB Data Warehouse database on it. For a couple of days each month, it get’s hammered, often by very poorly written queries and performance suffers. It never recovers, even when the traffic backs back down, performance remains bad until reboot. Why?

Analyze its wait stats like we talk about in Mastering Server Tuning.

Q: YourbiggestFan: Hi Brent, If you are asked to trbshoot perf issues on az paas database, how would you go abt finding the main bottleneck. Would you use sp_blitzfirst(log to table) or use query store to get reliable wait stats info or via any other way? (Account for DB scale down during offhours)

I’d log wait stats to table.

Q: SteveB: In a recent office hours you said the best way to upgrade an AG was with a Distributed AG. Is there a reason you would not add the new servers to the current AG and just failover to them? We done this and it worked great but I might have just gotten lucky.

Yeah, whenever I have new servers in an AG, I prefer testing their failover processes, doing cluster validation, etc before go-live, and that’s really dangerous when you have an existing AG.

Q: Yabba DB Doo: We have a web app in Azure that has individual databases for each of our customers. What’s the best way to manage DB changes during development and then deploy them out to multiple production databases?

See above.

Q: MikeO: My vendor friend is using sp_prepexec. When passing the query with the recompile option the plan shows using an index seek. When passing the same query without the recompile option the plans shows using an index scan. Is this a parameter sniffing issue or a quirk with sp_prepexec

I can’t tell without seeing the queries and plans.

Q: mr_Arturo: hi Brent, a friend of mine (: recently changed the job and “he” (: noticed that the new company use lots of views, but even stranger is that some of views are request data from other views! What about performance and should it be avoided request the view data of the view data?

Read this.

Q: Ozymandias: Is there a DB size where native SQL backup / restore becomes a concern?

Generally by the time you hit a terabyte, full restore times are tough.

Q: mukesh chaurasia: Dear i am new in sql administrator so i want to know about which book is best to guide about sql administrator real time problem.

Read this.

Q: Daniyyel: Is it ok to run perfmon directly on the SQL server?

No, try this DMV instead.

Q: Konstantin: How does Jorriss combat oxidative stress?

He’s made of carbon fiber, not metal.

Q: Odafin: Any noteworthy columnstore improvements in SQL2022?

Read this.

Q: Jethro: Is the OPTION Label hint ever helpful?

I’ve never used it.

Q: Experto no en la materia: What is the largest number of active databases you have seen hosted by a single SQL instance?


Q: Arslan: When troubleshooting a SQL agent job step failure, is it better to “Include step output in history” or to output job step results to an Output file?

Which one is easier for you to parse when it’s a big long output? There’s your answer.

Q: David: Who is the “Brent Ozar” of the SSIS world?

Andy Leonard.

Q: SqlPadawan: Hi Brent! A part from grabbing parameters and good/bad plans (as you teach in MPS), how have you used Query Store during your 2-day emergency rescue? Any other practical uses in terms of performance tuning?

Not during my 2-day SQL Critical Care, no, but if I had a full time DBA job again, I’d use it.

Q: Dev on the Dark Side: What are the top 10 (or even 5) things that you can “teach” a development team to do / not do in regards to a mix of stored procs, entity framework, & CF ad hoc queries. So many problems… so little time…

If your developers can only learn 5-10 things, they’re doomed.

Q: Kunoichi: Hi Brent! My question is related to CDC : Why does MS SQL Server allows ONLY TWO capture instances on any given table at a given time?

For “why”, ask Microsoft.

Q: Steve E: Hi Brent, All of our SQL Servers are currently on premise but I feel I should gain some awareness of cloud services such as Azure Managed Instances or Azure SQL Databases. Is there a way I can do this as a personal user? Most of the plans seem to be 00’s of (British) pounds p/m

Read articles or watch videos rather than running your own server.

Q: Eric Beaumont: My friend read a blog that write once file shares are good at stopping potential ransomware from infecting sql backups. Have you ever implemented at write once file share for sql backups? What all is involved?

The DBA shouldn’t be the one implementing it. The sysadmin team should be. This kind of thing is usable across all servers, not just the database servers.

Q: DBA by addiction: What kind of malware / virus protection can or should be installed on a SQL-Server? Can you give some advice if things around this topic are pretty much senseless?

Whatever your security team requires.

Q: Have you used SQL Server with docker? If so, please elaborate.

Yes, in an early attempt to do SQL Server development on my Mac. It was a huge pain in the rear. I hated it. I can see why people would use it for continuous integration, but it’s not for me, especially with the new Apple Silicon processors.

Q: Hi Brent! A CLR function uses a temporary table. I don’t want to use forced parameterization at the database level at this time. In this case, how to deal with the bloated plan cache?

I’m not a fan of using CLR in SQL Server for a whole bunch of reasons, but if you insist on using it, then I’ll assume you can solve the problems you run into.

Q: Emelio: Does page level compression affect backup performance or size?

Read this.

Q: Fyodor: Do you have a File Group strategy you recommend when creating new SQL databases?

If it’s going to be 1TB or larger within 30 days, create a new filegroup with 4 files spread across 4 volumes, and make that the default filegroup.

Q: Does Basically Anything: Hi Brent, I’m having issues with the T-log not freeing space after log backups and growing a lot. There are no runaway queries, but there are 200+ sleeping SPIDs with is_implicit_transaction = 1. Are they the likely culprit? What troubleshooting path do you recommend? It’s ISV.

Read this.

Q: Kinneret: What is your opinion of SQL 2022 Buffer Pool Parallel Scan feature? Who will this benefit the most?

I haven’t looked into it at all, but the Microsoft post on it implies that it focuses on people with a lot of memory, say 512GB and above.

Q: Jayden: What are top gotcha’s you see when using implicit transactions in TSQL and C#?

Read this.

Q: Biz: Is there a good way to view cache hit / cache miss ratios for when SQL has to load data from slow disk vs fast memory?

By going back in a time machine to 1999 when that was how we did performance tuning. Today, use wait stats instead.

Q: Bart: Have you consulted on Microsoft Dynamics AX (2012) database query/index optimizations?


Q: Lincoln: What bad things can happen when data warehouse and OLTP mixed workloads are run against the same SQL 2019 enterprise instance?

Slow performance.

Q: RoJo: We have Dev, Stage, Prod environments. Sometimes the config (for example) points to the Dev from Stage or worse. To isolate them from each other, I’m thinking white listing inside Windows Firewall. Is this too slow, or any best practice to keep collateral damage from near servers

I don’t think I even understand this question. It’s probably too big of a scope to ask for Office Hours – try dba.stackexchange.com.

Q: Henrique Almeida: Hi Brent, how are you, I hope so. Which free SSMS add-ins do you recommend and if not, why?

You hope so what? What the hell does that even mean?

I don’t use any because I can’t usually install apps on the client machines I work with. I’m sure there are good ones out there, but I just don’t have experience on what they’d be.

Q: Raphra: I like notebooks in Azure Data Studios and I can see how we can add comments to stored proc, by replacing the stored proc’s text with a notebook file (with markdowns for comments). However, do you know how I can call this notebook from SQL Agent instead of the stored proc?

I adore notebooks too. I wouldn’t use them as scheduled jobs though.

Q: Maani: Hi Brent! how come select from a view executed by ‘sp_executesql’ is much slower than executing it as a query (it took 4 mins to be executed)?I wrote option(recompile) at the end of the select clause from view, and the request was executed in less than a second.(was no parameter)

I would need to see the query and the execution plans to answer that.

Q: Neil: i have a job that uses OLE Automation to pull some json stuff. can i enable and disable OLE automation at the beginning and end of the job to prevent anyone else from using it ?

No, it’s completely impossible to enable and disable OLE automation, as shown by this link.

This Is Your Last Chance to Attend My Mastering Classes Live This Year.

Conferences and Classes

You’ve conquered my Fundamentals classes, and you’re ready to take it up a notch.

You know you can get the recordings of my Mastering classes, but you prefer the interaction of live classes – the ability to ask questions live, have me go off-topic for demos, and block out your calendar to make sure you conquer the material.

You have one last chance for 2022:

Or attend the whole set for $1,995.

Once this round of live online classes finishes, I won’t be teaching classes until at least mid-2023. I totally love doing it – but after the pandemic marathon of teaching weeks of online classes every month for years on end, I’m ready to take a break for a while! So if you wanna hang out live, come on down. See you in class!

Here’s What I’m Teaching at the 2022 PASS Summit.


The lineup for the PASS Data Community Summit is out, and in addition to my pre-conference workshop on Mastering Parameter Sniffing, I’m also teaching two sessions:

Everything You Know About Parallelism Is Wrong. Just because you see parallelism icons on execution plans doesn’t mean the query went parallel. SQL Server doesn’t load-balance work across CPU cores. CXPACKET isn’t a problem you fix by changing MAXDOP. Hell, even the term “MAXDOP” doesn’t mean what people think it means. It’s not that SQL Server’s parallelism is bad, it’s just not what you think it is.

Fragmentation Explained in 10 Minutes. Short, fast-paced lightning talks require difficult choices for presenters. If things go even the slightest bit off the rails, you are completely screwed. You have to viciously edit down your material to get the right points across, and no more than that. Then, you have to rehearse, rehearse, rehearse to get the timing right. The last lightning talk I did at PASS, I was wearing a Bob Dylan costume, a wig, and a harmonica. This year, no costumes – just aiming for ambitious delivery.

I’m really excited because I still think of the PASS Summit as the best-of-the-best, the place where the sharpest presenters show their top work. I wanna bring my A-game to this premier event, and I’m so psyched to share it with you.

See you in Seattle!

[Video] Office Hours Speed Round


In ten minutes, let’s cover as many questions as possible from https://pollgab.com/room/brento:

Here’s what we raced through:

  • 00:00 Introductions
  • 00:17 dbacat: We’ve been fighting an AG issue for a while. Sometimes during failover, one or a few databases go into initializing/recovery state on the new secondary. It’s random, never consistent. Only fix is to drop DB from AG and add back. MS support was not helpful. Have you seen this?
  • 00:38 Gaspard: Is performing RESTORE WITH VERIFY_ONLY enough to test backup health? If not, what could go wrong between this and a full database restore?
  • 01:22 RoJo: It used to be common to have a dedicated IO channel for App, Data, Logs. Now that SAN is common can everything go to the same channel or would it be better to keep separate? Would it ever be better to keep SAN as one channel and others on say local RAID?
  • 01:57 MyFianchetto: LPIM. Is it a must have after setting MAX SERVER MEMORY?
  • 02:50 Steve McGarrett: Do you have any sizing guidelines / tips when migrating an onprem SQL 2019 Enterprise instance to Azure SQL?
  • 03:37 Dave: Hi Brent, we have an on-prem sql server used as a DWH, my company is starting to move some of the workload to AWS, what is your take on Amazon Aurora as an alternative (we can change the application to support it)?
  • 04:34 Anatoli: How do you know if your SQL server physical RAM is going bad?
  • 04:55 Mirza: Hey Brent, you had mentioned that PLE metric is useless. However, I had a talk with someone from Microsoft and that person says that PLE is a good way to measure memory pressure. How do I convince him to stop paying attention to PLE? Thanks in advance.
  • 05:34 Gerardo: sp_BlitzFirst is showing several hours of CMemThread waits on our 64 core SQL 2019 Enterprise CU16 instance. Trace flag 8048 did not help. How do we use sp_blitzcache to find the top queries causing this wait?
  • 07:00 Hiroto Katagiri: What is your opinion of Azure Synapse and Snowflake cloud data ware houses?
  • 07:50 Danny Reagan: What are your pros and cons for using SQL tables to implement queuing?
  • 08:25 Levi: Is SQL page level restore a practical solution for dealing with data corruption? Have you seen it successfully used?
  • 09:17 chandwich: Will we ever get a, “Watch Brent upgrade SQL Server”?
  • 09:59 Bruno Luis: We use PARSENAME as a sneaky way to split strings in 2017 (eg IP addresses). However, we can’t create an index with it because it’s non-deterministic? But… WHY? Please, WHY?

Who’s Hiring in the Database Community? July 2022 Edition

Who's Hiring

Is your company hiring for a database position as of July 2022? 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.

Columnstore Indexes are Finally Sorted in SQL Server 2022.

There’s a widespread misconception that SQL Server’s columnstore indexes are like an index on every column.

I debunk that myth in the first 30 minutes of my Fundamentals of Columnstore class, where I explain that a better way to think of them is that your table is broken up into groups of rows (1M rows or less per group), and in each group, there’s an index on every column.

So essentially, every column has a whole bunch of indexes on it.

But there’s no order whatsoever as to which rows end up in which index.

This isn’t a problem for relatively small tables, but as you get to billion-row data warehouse fact tables where columnstore should really shine, performance gradually degrades. In data warehouses, fact tables often have a commonly filtered column, like SaleDate. However, until SQL Server 2022, even if you wanted a small SaleDate range, your query would likely check hundreds or thousands of row groups, each of which had a huge range of data.

Here’s the problem before SQL Server 2022.

Take the Users table from the Stack Overflow database – it’s a bad candidate for clustered columnstore for reasons that we discuss in the class, but it’s the table most of y’all are familiar with, so we’ll start there. We’ll create a new table and put a clustered columnstore index on it:

After loading the data, visualize the columnstore contents with sp_BlitzIndex, and at first, it looks like the table is broken up into randomly sized rowgroups, sorted by their Id:

But that’s simply because our source, the Users table, happened to be sorted by Id. Over time, as you delete/update/insert rows in this table, you’re going to need to do index maintenance on it. (I know I talk a lot of smack about people who do too much index maintenance on rowstore tables, but columnstore tables are very different – they actually need maintenance for the reasons we discuss in the class.)

After a couple of rounds of index rebuilds, check the contents again:

And the rowgroups are literally sorted in random order:

Meaning that if you want to find Id = 26837, you’re going to be checking multiple rowgroups – and more and more over time, as your data’s locations continue to randomize. This isn’t such a big problem for a tiny table like the 1GB Users table – but it’s a terrible problem for real-life sized tables, like the ones that really need columnstore.

You could work around this –
emphasis on work.

You could reload the entire table regularly. It sounds extreme, but if the table isn’t large, and your hardware is fast enough to make a copy of the table sorted in the order where you want rowgroup elimination, it works. Copy the data into a new structure with a clustered index on that column, then create a clustered columnstore index again. The data will be ordered by the rowstore clustered index columns.

That solution doesn’t scale well for the kinds of shops that really need columnstore, though, because we’re talking about blowing the table back up to its full uncompressed size, and then compressing it all back down again – and all of this is a logged operation.

A better option is to partition the data by your commonly filtered column. I like this solution a lot at the billion-row tier because it also enables much better index maintenance strategies. I frequently point out that daily rowstore index maintenance is usually a waste of resources, even suggesting to scale back to monthly, but with columnstore indexes, things get a lot more complicated depending on your workload. We talk about that in class, too.

In theory, I’m a fan of partitioned views, too – using a table per year or per quarter – and then unioning them together. I just haven’t seen that solution implemented in the last several years though. I’m sure it’s out there – I just haven’t seen it lately.

SQL Server 2022 fixes this
with ordered columnstore indexes.

Hey, go figure, indexes need to be put in order! Who knew? I mean, aside from all of us. Literally, every one of us. Here’s the syntax from Books Online:

Rejoice! The data is now sorted by Id, so if you’re looking for a particular Id, SQL Server can narrow its search down to just one rowgroup:

Okay, I lied. It’s still not aligned. In fact, if anything, it’s even worse. You might argue that it’s because I didn’t specify a single-threaded index creation, but…those ranges overlap across lots of rowgroups, not just the 4 to represent the 4 cores in my VM.

When I try it again with MAXDOP 1:

That doesn’t work either, and even if it did, MAXDOP 1 isn’t really doable in real-world table sizes – even the 1GB Users table took over a minute to do the above work.

In theory, when the feature finally works, it’d be useful if many of your reporting queries share a common filter – like in data warehouses, a SaleDate column. Again, definitely doesn’t make sense for the Users table – it’s not a good fit for columnstore at all – but it does fix the problem of the data being randomly ordered between rowgroups.

When examining your own data and reporting queries to figure out which column(s) to order by, check out the guidance for Synapse Analytics, which already has this feature. And I assume it actually works out there. Although you know what they say about assume…

Update 1, 2022-07-05: the command actually executes in CTP 2.0 when you use the right syntax, as pointed out by Adam Machanic in the comments.

Update 2, 2022-07-05: no, it doesn’t work.

Based on suggestions by Joe Obbish in the comments, I spent hours today trying repeated testing with columnstore indexes on tables up to 100GB on a VM with 30GB RAM. I even tried with MAXDOP 1, and still no dice – they’re not sorted:

Note the overlap in CreationDate ranges. I give up – until Microsoft has a demo showing this feature actually works, I’m going to hold off on further testing.

Update 3, 2022-07-11: it kinda works.

Ryan Stonecipher (Microsoft) reached out to me and we talked through it.

The data is kinda sorted, and they’re referring to it as a “soft sort.” The sort order is maintained in-memory as the index is being built, but if the sort runs out of memory, the currently sorted data is flushed to the next operator in the index build plan. Joe Obbish reverse engineered this in this well-written blog post.

The benefit of this design decision is that it avoids spilling to TempDB – that’s good.

The drawback is that the rowgroups aren’t perfectly sorted. There are going to be overlaps between rowgroups. The idea is just that there’s going to be way less overlaps than there would be with a completely unsorted set of rowgroups.

I’m totally fine with this. At the end of the day, it’s way better than the ALTER INDEX REBUILD behavior of columnstore, except for the fact that it’s offline only. The docs just need to better reflect that “order” is a best effort thing, not the kind of exact stuff that we usually expect from databases. I kinda jokingly think of it as a MongoDB simulator.

[Video] Office Hours: Professional Development Questions


Most of the questions y’all post at https://pollgab.com/room/brento are technical ones, but there were a handful of interesting professional development and career ones in the queue, so I cherry-picked those for today’s episode:

  • 00:00 Introductions
  • 00:20 GI Joe DBA: Have you ever refused a consulting project \ opportunity and why?
  • 03:35 Doug E: How do you prefer building up your SQL vm’s in AWS? Terraform, docker, chef, etc?
  • 04:46 Hany: Hello Brent, Who is “Brent Ozar” in the Azure world?
  • 07:00 GI Joe DBA: What do you do when you’ve inherited a project and the business logic and institutional knowledge is overwhelming and it makes your “eyes glaze over”?
  • 08:35 DBA_preparing_for_jobsearch: what is your opinion on interviewers asking SQL Server internals questions instead of practical day-to-day activity based questions?
  • 10:26 Trushit: How would you handle clients/managers who want everything “ASAP”, especially their “ASAP” expectation is weeks and somethings months apart from my “ASAP”? Usually they come from business background and think that if something is easy to use, it must be easy to build.

Is Remote Work the New Normal for DBAs?

An intriguing Office Hours question came in through my PollGab room.

Anatoli asked, “Is remote work the new normal for DBAs? What are the pros / cons?”

I happened to be teaching an online training class yesterday, so I asked my attendees where they were attending the class from.

The vast majority – 27 out of 30 students – were attending the class from home. Now, naturally the audience represents a biased sample, because not everyone’s employer is generous enough to pay for my training classes. It’s possible that this audience represents a luckier group than average.

Just out of curiosity – not for any scientific purpose – I also posted a poll on Twitter that specifically called for folks who are DBAs:

I’ve been working remotely for almost two decades, so I don’t want to answer the pros & cons part – but I’ll leave it to you, dear reader, in the comments. What have been the pros and cons of doing DBA work from home?

Now is the Right Time for the 2022 Brent Ozar Unlimited Scholarship Applications.

Company News

We normally do this in the fall, but given last week’s news, I bet you can understand why we’re doing this a little early, dear reader.

You work at a charity or non-profit, helping them make a difference with data, fights for the rights of the underrepresented, or cares for them.

Maybe you write reports to help fundraisers do a better job of raising money to protect voting rights. Or maybe you’re a developer at an organization who prevents suicide amongst lesbian, gay, bisexual, transgender, queer, and questioning youth. Or maybe you’re a sysadmin at an organization that provides reproductive health care, but you can’t get training because your organization desperately needs to devote all their financial resources to protecting women right now.

Pocket Square
Time for the heart.

That’s where I come in. I wanna help.

I want to empower you to continue making a difference. My scholarship program is simple: recipients get a Level 2 Bundle, which includes all of my recorded training, SQL ConstantCare®, and the Consultant Toolkit.

To give you an idea of the kinds of organizations I’ve supported over the years:

  • The American Institute of Physics is committed to the preservation of physics for future generations, the success of physics students both in the classroom and professionally, and the promotion of a more scientifically literate society.
  • UNOPS helps people build better lives and countries achieve peace and sustainable development.
  • International Justice Mission – a global organization that protects the poor from violence in the developing world.
  • Elizabeth Glaser Pediatric AIDS Foundation – 400 children are infected with HIV every day. I don’t know how to type those words without crying and taking a break from the keyboard.
  • Mencap – improving the lives of UK people with a learning disability.
  • The Smith Family is an Australian charity helping disadvantaged children get the most out of their education so they can create better futures for themselves.
  • Easter Seals-Goodwill Northern Rocky Mountain serves children and adults with autism and other disabilities, plus disadvantaged families in Idaho, Montana, Utah, and Wyoming.

The fine print:

  • You must work for a foundation, non-profit, charity, or similar company that’s doing good work. (If you work for Ginormous Profitable Global Corporation, you’re not going to make the cut, so don’t waste my time or yours.)
  • Your organization or government rules must allow you to receive free training. (Some companies prohibit their employees from accepting gifts.)
  • You must already have a job working with SQL Server. (This isn’t about getting a new job.)

Apply now. Applications close July 4th, aka Independence Day in the United States. I’ll just leave that there. In the words of Theodore Roosevelt, do what you can, with what you have, where you are.