Behind-the-Scenes Home Studio Tour

Videos
13 Comments

Whenever I move to a new place and set up a new home studio, I give you a walkthrough so you can get a rough idea of how I work. I just finished setting up my new San Diego home base, and here’s a tour of what it looks like both as an attendee, and behind the scenes:

You can see more details about my hardware, software, and config in my past Home Office posts, but here’s a quick overview:

Wanna see how it looks? Join me for free classes next week!


I’m Not Ready for Maskless Conferences Serving Finger Food.

At a conference in Belgium this week, Steve Jones posted a video:

My first reaction was, “That’s an awful lot of people in really close proximity, talking loudly at each other, and none of them are wearing masks.”

And then I saw the finger food. No sneeze guards. Just get your dirty fingers in there and serve yourself.

I feel sick to my stomach just watching that. I’m not ready for that.

Let’s set aside any discussion of this specific conference. The organizers chose to do what they chose to do, and I’m not here to say whether that’s right or wrong. I don’t want to turn this into any blame about the organizers or the attendees. I only want to have a discussion about my own personal readiness for conferences right now, and the kinds of precautions I’d want organizers to take before I’d attend.

A mask is like a seat belt.

When you get behind the wheel of a car, you put your seat belt on. Years ago, seat belts were controversial, but today, we just accept it as the right thing to do for ourselves and our loved ones, and we buckle up. It’s a quick, easy, harmless measure for most people to do, and it’s a litmus test for whether someone should really be behind the wheel of a car.

Similarly, because wearing a mask indoors is so easy and harmless for most people, I get nervous when people won’t do it. I start to question their judgment about other less easy-to-see things, like whether they washed their hands, avoided shaking hands with people, and whether they’d stay home if they had COVID symptoms. I hate to say this, but I even question whether or not they’re telling the truth when they say they’re vaccinated or already had COVID.

Now, don’t get offended: I’m not talking about you, dear reader.

You and I have years of history and friendship. When it comes to masks, I know that you know that I have asthma, and my health is already at risk. I’m doing the best I can to protect myself, of course – I wear a mask, wash my hands, and I’m vaccinated twice over with both Pfizer and Moderna. However, that still isn’t a bulletproof defense: vaccines aren’t 100% effective, and there are plenty of examples of breakthrough cases. I know that you and I have a good understanding that if I saw you, and you weren’t wearing a mask, and you told me you had a medical reason not to wear a mask, I’d probably give you a pass because I’d just be so excited to see you. It’s been so long, hasn’t it? Come on over here and give me a hug, you big lug.

A stranger who won’t wear a seat belt
makes me nervous.

I’m not talking about you – I’m talking about strangers and the people that I don’t know as well. You know the ones I’m talking about – the ones who make incredibly bad decisions with their databases every single day. They only run CHECKDB once a week, but they’re deleting log backups older than 24 hours. They don’t even understand the process of how to recover from corruption, let alone rehearse it on a regular basis. They’re running around without scissors every day.

It’s bad enough that they take database risks, but if a stranger takes a risk with my health, I start to get really nervous. I don’t want to go near them, but people will default to getting close to each other. Event organizers probably aren’t going to say, “You need to distance by default, and only go closer to people when they have verbally invited you to get within a meter of them.” They’re going to assume consent – which is an awfully odd default in the day and age when we’re trying to get people to understand that consent is not the default answer: you have to ask for consent first.

And of course, those people are the ones breathing on the finger food. Coughing on the finger food. Plucking one of the finger foods with their unwashed hands, and moving the food around.

A room full of them?
Forget it. I’m out.

When I see a roomful of strangers meeting each other for the first time, and they’re taking good precautions like wearing masks and socially distancing, I get excited. I’m much more confident that they’re making good decisions, and that they’ve also washed their hands, avoided handshakes, and got plenty of antibodies running through their veins. I know they’re not only doing their part for themselves, but for others as well.

But on the other hand, when I see that video at the start of the post – a roomful of strangers indoors, networking closely with each other, eating finger food out on public display with no sneeze guard, not wearing masks….

As much as it pains me to say it, I can’t go into that room. I would do a U-turn and leave immediately.

Because they’re strangers, I can’t trust their judgment. They might have taken every precaution – but I’m just not comfortable making that assumption.

If you’re okay with it,
that’s fine. I respect you.

You, dear reader, are well-qualified to make your own judgments about your health, my health, and the health of those around you. You’ve done your own research, and I respect that you’re making well-informed decisions based on science, not feelings. You’re not just going to a conference maskless because you’re desperate to recreate 2019 – you’re going to a conference maskless because you believe it’s the right thing for you, your family, and those around you.

Again, I’m not talking about you.

I’m talking about the roomful of maskless strangers.

They’re the reason why I’m not ready to go to in-person conferences yet, even though I’m vaccinated, and even though I’m wearing a mask.

How about you? Are you ready to go back to a conference that doesn’t require masks, doesn’t default to social distancing, and serves food the same way we did before the pandemic?


This is the Last Year You Can Buy a Live Class Season Pass.

Company News
8 Comments

When you buy my Live Class Season Pass, you can attend all of my live online training classes for a year straight, plus watch the recordings.

It’s an excellent deal: go to your manager just once, get approval just once, and then spend the entire year learning how to master SQL Server. You don’t have to shoehorn specific dates into your calendar – just drop in whenever your schedule permits. When an emergency pops up at work, no worries – you can catch up with the recordings after hours, or watch ’em later.

In order to make it work for your schedule, I teach all of my Mastering classes at least 6 times a year: 3 times in Americas-friendly time zones, and 3 times in Europe-friendly time zones. I basically spend about 1/4 of my year teaching.

Now, I love teaching, and I have a ton of fun with it. You’ll see the joy and excitement in my eyes if you drop into my free classes happening right now, and depending on when you’re reading this, there might even be one streaming right now. I know y’all have fun with it, too, because students return again and again to sharpen their skills throughout the year. I love seeing their techniques improve on the labs over time.

But…I’ve been teaching a heck of a lot.

And I’m gonna be honest with you, dear reader: I like not working.

Well, by that I mean I like not having any specific day/time events on my calendar. I don’t mind working – I actually love what I do – but I like the flexibility of being able to run off wherever I want, whenever I want, and go have an adventure. It’s hard to do that when I have so much training time blocked out on my calendar, so far in advance. Starting a year from now, in November 2022, I’m going to mostly focus on recorded classes rather than live ones. I’m going to continue to produce new material, but I’m going to teach the live versions much, much more rarely.

So if you’ve always wanted to get in on my live classes, the clock starts November 1, and ends December 1:


[Video] Office Hours: Ask Me Anything On the Balcony

Videos
5 Comments

Good evening, party people! I finally have my new San Diego home base all set up and unpacked, so I took a champagne break to review your top-rated questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Introductions
  • 00:27 Alex: Hi Brent. For a few hours we got a “Could not continue scan with NOLOCK due to data movement” error on a proc that used read uncommitted isolation (I know!). The errors stopped when the proc was recompiled. What are your thoughts on why the recompile stopped the error? Thanks.
  • 02:15 No Longer a DBA: Hi Brent, recently accepted a security role at a new company and I was the sole DBA at my old place of employment. I’d like to consult as a DBA on the side at my old employer. My question is what would be a good consultant fee to charge them? The business is based in FL. Thanks!!
  • 04:02 Igor: Hi Brent!My friend noticed on his AlwaysON AG occasional replica change state from PRIMARY_NORMAL to RESOVLING_NORMAL/PRIMARY_PENDING back to PRIMARY_NORMAL without actual failover.This is happening 3-4 times a month. What can cause this behavior in AG? Is this a normal behavior?
  • 05:42 party people: How to create clean SQL code? Friends suggestions: use CTE (avoid subqueries), use Inner Joins (avoid joining in the WHERE clause). What are your tips?
  • 07:23 Maciej: Hi Brent, thank you for recommending “500 Level Guide to Career Internals” in one of the previous Office Hours. I really enjoyed it. Q: As an IT professional who witnessed dot-com and housing market bubbles to burst do you find any similarities to the current economic situation?
  • 09:08 George: For the purposes of cost allocation, I need to collect statistics and metrics on a table level that can be directly attached to a single user or an AD group. For example how many queries a user has to a given table, I/O, resource all. Is that possible without using XE and audit?
  • 10:43 Future-DBA: I need to truncate a table with 50 million rows and 150 GBs size; the table is in a database that is part of an Async AG. What will be the effect of this operation on the AG latency and the size of log backups? Is there a recommended way of doing this?
  • 12:32 Mike: Hi I want to work on open source SQL database projects not Stackoverflow or stackexchange questions. Where can I find these opportunities? What are you suggestions on it. (Projects just for knowledge purpose not monetary)
  • 13:34 ZeRemoteCrowd: G’day Mr. Ozar! ?an you quite honestly say that things you teach during your classes were already explained numerous times by others and in other sources or there is something you consider a sort of “commercial secret” in your teaching materials? P.S. your classes ARE great!
  • 15:44 DBA Lite: Since changing companies where before I didn’t have a dedicated DBA, it’s been eye opening how bad developers are at write queries. Thoughts on ways and/or skills to start with to upskill my development team with SQL with a limited training budget?
  • 18:13 DBA Lite: Thoughts on how to improve collaboration between DBA and Development teams? The tension seems thick even with questions you field.
  • 20:35 Chad Baldwin: Aside from your hourglass method. What are some ways you like to help manage your time, goals, tasks, to-dos, etc. I feel if I had a better handle on this, I’d be much more successful, and you seem to get so much done despite having such a hectic schedule.
  • 22:26 Ice Ice Baby: Hi Brent, a friend of mine wants to put a Data Warehouse on the same server as an AG readable secondary and use it as the source for data extraction. Is this a good idea and are there any gotcha’s when using a readable secondary? All servers are 2017.
  • 24:14 KB: Hi Brent! Thank you for all your free content! Do you have any opinions on Microsoft SQL Server on Amazon RDS for a production workload?
  • 25:22 Kajimial: sp_BlitzCache? In which cases you need to remove plan handle from cache. For example you create an index and you have auto update statistics off, is the SQL clever enough to recompile the execution batch and create new plan, or do you need to delete from cache the existing one
  • 26:38 DBA: Can you talk about using Ola Hallengren’s backup solution vs a 3rd party backup solution like Rubrik? We currently use Ola’s. Our infrastructure team uses Rubrik for their VM snapshots and would love to see us adopt Rubrik for db backups. I don’t see advantages to switching
  • 29:15 Oleg: Hi Brent, My friend is wondering what might the reason for “Columns With No Statistics” on primary key?
  • 29:49 Scott: When debugging the “yellow bangs” in an actual execution plan, I sometimes come across a single operator writing many trillions of pages to tempdb, to the tune of multiple petabytes. The tempdb is 1 TB. Is this a bug in SQL Server or am I not understanding something here?
  • 31:11 Wrap-up

Free Classes Start Next Week. Register Now.

Company News
8 Comments

This month & next, I’m going to teach you the fundamentals of SQL Server database performance tuning, live, for free.

The first class is my How to Think Like the Engine class. It’s already free online in both video and blog post formats, but this fall I’m updating and expanding it to a 3-hour version. You could totally start watching the recorded version now, but…I know how it is. Some folks prefer watching live because they can ask questions as I go along:

Now, we start getting to the fun stuff – things that normally cost $89 for each class’s recordings. These are all-day classes. On Tuesdays, I’m teaching them in Europe-friendly times, and on Wednesdays, I repeat the same class in America-friendly times.

Register here to attend all of those, for free. The Americas-friendly classes will be 9AM-4PM Eastern US time, and Europe-friendly classes will be 8:00-15:00 UTC. (How to Think Like the Engine is only a half-day class though.)

After you’re registered for class, set up your server.

All of the Fundamentals courses except Fundamentals of Columnstore have the same prerequisites. Set yourself up a SQL Server:

  • SQL Server 2017 or newer, either Developer Edition or Evaluation Edition. Download pages are linked from SQLServerUpdates.com. Express Edition, Azure SQL DB, and Amazon RDS won’t work, unfortunately.
  • Use the default collation during install, SQL_Latin1_General_CP1_CI_AS (don’t get fancy with binary collations)
  • Apply the latest Cumulative Update
  • Install the most recent SQL Server Management Studio

To follow along with the demos, download the 50GB Stack Overflow 2013 database (10GB 7z file). I’ll be using the medium-sized 50GB StackOverflow2013 database, and it’s vital that you use the same one. Query tuning and parameter sniffing is all about getting different behavior based on your query’s parameters, so I need you to have the exact same data distribution that I’ll be working with onscreen.

Fundamentals of Columnstore has more ambitious prerequisites because columnstore is about bigger data. You don’t have to follow along with the class demos at all – it’s totally optional, and you can just watch me do them on the screen – but if you do want to follow along, here are the columnstore prerequisites.

Register now – attendance is limited to the first 1,000 students who get in. See you in class!


[Video] Office Hours in My New San Diego Apartment

Videos
1 Comment

Just got the keys to the new home base in San Diego, and the furniture isn’t even in yet. I went through your top-voted questions from https://pollgab.com/room/brento before my smoke alarm battery started chirping:

Here’s what we covered:

  • 00:00 Introductions
  • 01:10 Yoni – We have 60-100GB DBs on SQL server on windows (VM). Will it be better in performance if we change Windows to Linux only for the SQL and my APPs would stay on Windows? Is there any problems anticipated with Unicode, datetime, etc. TNX you ROCK! wish we could have a coffee someday
  • 02:36 Brandon – Are you able to see sp_AllNightLog without hearing Lionel Richie?
  • 04:15 Gustavo – If I have a table with 10 foreign keys Does the SQL server create an index for each foreign key? And if I insert a new tuple, are there 10 inserts in each index? And to check integrity, SQL Server checks the primary index for all other related tables?
  • 05:43 WillemHank – Is switching from SQL Server to PostgreSql a big step? I find some articles regarding differences, but I was curious about your experience.
  • 06:28 Sean – What are some SQL Server undocumented/secrets you’ve found? Hidden system functions, or undocumented clauses/keywords. For example “Inner Reduce Join” works on SQL Server Standard.
  • 08:22 Bailing out due to my smoke alarm battery

[Video] Office Hours: Ask Me Anything at the Old Harbor in Reykjavik

Videos
0

On a very chilly morning, I went through your highly-upvoted questions from here and talked through ’em:

Here’s what we covered:

  • 00:00 Introductions
  • 01:02 Johnny: Good morning, Brent! VARCHAR or NVARCHAR; which one to use in our Western world? One could say that disk space is cheap so it doesn’t matter. But with SQL Server’s 8 kB pages, these pages will go full much faster when using NVARCHAR. I’m excited to hear your thoughts. Thanks!
  • 02:24 volcano: Hello Brent, If i keep my compat level to 150, and enable legacy CE, how does SQL internally use latest SQL 2019 features like adaptive joins or memory feedback.
  • 04:03 PostgresCurious: My company mainly uses MSSQL and Azure SQL but is exploring Azure Database for PostgreSQL as lower cost alternative. What are the main “gotchas/rough edges” that developers and DBAs need to be aware of when switching to Postgres? Are there any advantages besides the lower cost?
  • 05:43 AlwaysLearningDBA: Thank you for taking time to answer our questions for free. Helping us save our face (by telling us to use “A friend of mine”), mimicking clippy and SQL Server voices is a great way to add fun to learning. I just can’t get clippy voice out of head now. LOL. Thank you, Brent.
  • 06:04 For My Friend: Migrating to Azure SQL Databases from OnPrem and experiencing huge performance hit (1s onprem vs 8s in Azure). If we add OPTION (HASH GROUP) in Azure it runs in 1.2s? How bad is it to use this query hint any suggestions? Thanks for everything you do!
  • 08:20 juggler314: I’ve read your post on instance stacking being not advisable. But if I’m limited to one OS install (licensing reasons). And performance is wildly over spec’d. Are there any downsides aside from the mentioned performance tuning issues? I can give each instance enough RAM/CPU/IO.
  • 11:14 SunburnedDBA: A friend of mine is trying to capture queries that bring PLE to 0. He’s tried Extended Events. Any suggestions?
  • 12:59 FutureDBA: Hi Brent, Love your videos. Thank you so much for sharing with the community we really appreciate your efforts! Question: Select Into or Insert into select from ? I Ran the stats 1st uses less time but 2nd one has less cpu/logical reads and scan count. Which one is better?
  • 14:47 Rob B: Hi Brent, my daughter recently took a trip to Iceland and told me that the locals are big into tomatoes – specifically tomato ice cream. I wondered if you’ve run into this / or if you’ve tried it.
  • 17:13 Steve: Hey Brent. Been a long time. I’m having a time trying to get developers to test well. Any advice.
  • 20:30 Mr. Griffith: Will we see you partner with a software vendor to design a SQL Server monitoring product? (Hint, hint.)
  • 21:47 MattC: Hi Brent. How did you find making the leap from TSQL to PostGres. How similar are the 2?
  • 23:05 BusyIsGood: When troubleshooting performance, you mention starting with top waits. Should I prioritize total wait times, or Avg ms Per Wait. Total suggests CPU, but Per Wait suggests Locking.
  • 24:47 Wrap-up

[Video] Office Hours: Ask Me Anything About SQL Server at Gulfoss

Videos
1 Comment

I stopped at a waterfall in Iceland to answer your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Introductions
  • 00:28 Accidental DBA: How do I determine if SQL Server instances are licensed?
  • 02:22 Jose: What activities do I need to do if I sell managed SQL Server as a service?
  • 03:23 Mr. Griffith: How do I defend backups from ransomware?
  • 04:16 Accidental DBA: Will you do Office Hours in San Diego?
  • 05:07 Accidental DBA: What are the consequences if my SQL Servers are not licensed?
  • 05:50 Random Name: Should I use local accounts or domain accounts for the SQL Server service?
  • 07:20 Oleg: Should I use INNER JOIN or a comma-delimited list of tables?
  • 08:55 Abner: Activity Monitor shows spikes of waiting tasks. How do I dig deeper?
  • 10:01 Mr. SQL Seeks: How do I do an impact analysis before I make a change?
  • 11:10 Wrap-up

What Should We Change About This Year’s Data Professional Salary Survey?

Salary
104 Comments

Every year, I run a Data Professional Salary Survey here. Thousands of y’all have answered – here are the past results in Excel – and it helps everybody get a better idea of how overpaid they are. (Did I word that right?)

Query bucksHere are the questions I’m planning to ask this year:

  • What’s your total salary in US dollars, annual before taxes?
  • Your country
  • (Optional) Postal/zip code
  • Primary database you work with
  • Years that you’ve worked with this database
  • Other databases you worked with in 2021
  • Job type: (FTE, FTE of consulting/contracting company, independent, part time)
  • Job title
  • Do you manage other staff
  • Years of doing this job
  • At how many companies have you held this job?
  • How many other people on your team do the same job as you?
  • How many database servers does your team work with?
  • What is the population of the largest city within 20 miles of where you work?
  • Employer sector (private, government, non-profit, etc.)
  • What are your career plans for the year 2022?
  • To which gender do you most identify?

If there are any changes you want to make, leave a comment and we’ll talk through it. A few things to keep in mind:

  • If you want to add a question, tell me what actions you would take based on the findings. This survey is about salary, not things like what percentage of the audience uses what tools.
  • The less questions, the better the response rate – I’mma make you fight for new questions, and other readers will need to chime in.
  • The response types are fairly limited: either short text boxes or multiple-choice answers.

Update: we’re not going to add work-from-home related questions. The problem is that the work-from-home situation at a lot of companies is wildly in flux, and it’s often dictated by government rules or vaccine policies or availability. Companies are constantly changing their work-from-home policies, and any answers that folks might give could be outdated within 30 days. I don’t think you can really draw conclusions from that kind of thing right now.


[Video] Office Hours: Ask Me Anything About SQL Server at the Volcano Hekla

Videos
2 Comments

I drove up the volcano Hekla in central Iceland – also known as the Gateway to Hell – and parked the Defender here:

Brent on Volcano Hekla

While my travel companions hiked up higher (and took that photo), I set up the tripod and camera, then went through your highly-upvoted questions from https://pollgab.com/room/brento.

Here are the questions I covered:

  • 00:00 Introductions
  • 00:40 Mehdi: How do I check progress of a long-running query before I kill it?
  • 02:29 Viking: Why is CHECKDB taking so long?
  • 04:41 James: When I fail over my AG to a secondary, a random database gets stuck in a resolving state. Why?
  • 05:45 Joe Gleason: Should developers be allowed to run traces in production?
  • 07:27 Stanislav: How do I reduce blocking?
  • 09:17 Jaden: How do I understand big, complex queries quickly?
  • 10:16 Rent-a-Balloon: What’s the optimal way to run stored procedures one row at a time?

[Video] Office Hours: Ask Me Anything About SQL Server at Vestrahorn

Videos
0

The beach at Vestrahorn mountain looks different every time we’ve visited: high tide, low tide, sun, clouds. Always a fun side trip when we’re out in the southeast of Iceland. I took a break to answer the questions you upvoted here.

Questions we covered:

  • 00:00 Introductions
  • 00:38 Scott M: When do you recommend sp_BlitzWho vs sp_WhoIsActive?
  • 03:15 Brandon: Does Clippy consider selectivity when building missing indexes?
  • 05:10 Hugo: I have a temporary staging table whose statistics constantly update. Should I turn off automatic stats updates?
  • 07:02 Kavia MP: Is RCSI a good alternative to NOLOCK?
  • 07:31 Glev: Do you do consulting on Postgres?
  • 09:10 Fundamentals of MS Docs: What does the query optimizer fixes setting do?
  • 10:44 PPI: Can I use log shipping to migrate from SQL Server 2012 to 2016?
  • 12:32 Andrew: What do you do when a training class participant can’t keep up?
  • 14:44 Rojo: We’re considering multiple satellite databases and one central server…
  • 18:00 Daniel: How do I estimate memory requirements for memory-optimized tables with 200M rows and VARCHAR(MAX) columns?
  • 19:29 Simon: How can I predict if my SQL Server has enough resources to handle a 50% increase in workload?
  • 21:48 Matt: Does a DBA deserve credit when the server is bored out of its gourd?
  • 22:25 Severio: How big will the transaction log be when I create a clustered index on a table?
  • 23:48 Terrible DBA: If I want to move to database development or architecture, who do I approach?
  • 25:52 Mark: I’m creating an ODS by using SSIS to do a backup & restore, then running scripts…
  • 27:27 JJ: Where do I start learning more about databases, indexing, performance tuning?
  • 29:40 Wrap-up

What Is a Cost-Based Optimizer?

Execution Plans
0

When you execute a query, the database server has to figure out things like:

  • Which table to process first
  • Which index to use on that table
  • Whether to seek on that index or scan it
  • Which table to process next
  • How to join the data between those two tables
  • When to sort the data

For even the simplest queries, there are usually several possible ways to get the job done. The database server has to figure out which way is going to be the fastest – or at least, fast enough for the meatbag who ran it.

One way to do that is to build a cost-based query optimizer. As it builds execution plans, it assigns a cost to each operation inside the execution plan. That’s how SQL Server does it, and you can see the evidence if you hover your mouse over components in an execution plan:

In this case, I’m hovering my mouse over the Clustered Index Scan, and you see a few cost details.

  • Estimated I/O Cost 5.46609 – means that SQL Server thinks this operation will cost 5.46609 units. (More on the units in a second.) The larger the table is, the higher this cost is going to be. SQL Server even makes guesses about how much of the data will be in cache, versus how much will need to be read from storage.
  • Estimated CPU Cost 0.165757 – some operations require a lot of CPU work, but not this one. Here, we’re just scanning a table, kinda like you scanning a phone book. You’re not really going to be thinking hard – you’re just looking for a particular last name. You’d probably get bored quickly. (Maybe not, though, because you’re easily amused.)
  • Estimated Operator Cost 5.63084 – add up the two above numbers, and you get this one. Yes, usually when you have two numbers that make up a total, you’d put the two small ones first, then the total next. No, that’s not how SQL Server does it. When SQL Server shows data in a tooltip, it’s either in alphabetical order, or they load up the Data Cannon™ and fire it at the screen. Wherever the numbers end up, that’s where they end up. This tooltip was rendered with the Data Cannon™.

If you add up the costs of all of the operators in a query plan, you get its total cost, as shown here by hovering my mouse over the SELECT’s operator itself:

In this case, this particular execution plan costs 12.8589. (Again – we’ll get back to what the unit of measure is here in a second.)

SQL Server builds multiple plans,
and uses costs to compare them.

After building the first plan, SQL Server rolls up its sleeves and says, “Alright, let’s give ‘er another shot.” It builds another execution plan, and then compares that new plan’s cost to the cost of the original plan. After a few iterations of this, it keeps whichever plan is the least expensive cost, and runs that.

I’m simplifying a lot here:

  • Sometimes a query is trivially simple, so SQL Server only builds one plan.
  • Sometimes SQL Server builds a plan or two, and then says, “Screw it, these costs are so inexpensive, it doesn’t make sense to keep building plans. I should just roll with this plan because it’s good enough.”
  • Sometimes SQL Server builds a plan and says, “Sweet Potato, would you look at the time! If I tried to build another execution plan, it might take way too much time. I’m just gonna call time here, and we’re going to run with this plan, even though I might be able to build a better plan given more time.”

A cost-based optimizer is about using costs of query plans in order to determine which query plan to run with. It isn’t necessarily about exhaustively trying every possible query plan, or…buckle up…even about having accurate costs.

Cost-based optimizer implementations
aren’t necessarily perfect.

Paul White makes big Query Bucks

The costs are arbitrary. The database system has to decide how to assign costs to execution plan operators. You’d love for them to use “estimated time,” but the reality is that – well, go ask your project managers how accurate their estimates have been lately. It’s really hard to guess times. Instead, SQL Server guesses how much CPU and IO work will be required. A long time ago, this used to be guessed in seconds, but these days, we call the unit of measure Query Bucks.

The costs don’t reflect your hardware. The cost-based optimizer was built in the 1990s, and it hasn’t been updated to reflect modern CPU, memory, or storage speeds. We’re still using the same costs per key lookup that we used over twenty years ago. Personally, I don’t think Microsoft should even try to make costs reflect current hardware: after all, when a SQL Server gets installed, it might be used for ten years or more across all kinds of different hardware. It’d be hopeless to try to make the costs accurate for hardware that hasn’t even been built yet.

Some query work doesn’t get a cost. For example, memory grants aren’t taken into account here: you can see identical queries with a 1MB and a 100GB memory grant that have exactly the same cost.

Even things with a cost aren’t necessarily accurate. Some operations like multi-statement table-valued functions and linked server queries can’t be estimated accurately. Or maybe they could be, but Microsoft just never took the time to build accurate estimations into the cost-based optimizer. As a result, their work might be underestimated or overestimated, leading to inefficient query plans.

The more you know,
the faster you go.

SQL Server’s cost-based optimizer is more than good enough to handle the vast majority of scenarios. Most query authors just never need to know how the cost-based optimizer works, let alone its gotchas and pitfalls. They just write queries, and the queries run well enough – especially given how small many databases are.

However, as your data grows and your query complexity grows, the more you start asking questions about why your query isn’t running as quickly as you might expect. That’s when you start peeling back the covers on your query plans, trying to use query hints to boss the optimizer around into making different decisions.

That’s your sign that you need training on how the cost-based optimizer works. Start with my totally free How to Think Like the Engine class, and go from there.

And hey, it just so happens that I’m teaching it live for free next month.


Free Training Classes for Those Who Give Back: Announcing our 2021 Scholarships

Company News
10 Comments

You work at a charity or non-profit, helping them make a difference with data.

Maybe you write reports to help fundraisers do a better job of raising money to find a cure for a disease. Or maybe you’re a developer at a non-government organization whose mission is to speak for those who can’t speak for themselves. Or maybe you’re a sysadmin who’s been stuck managing vital databases, but your non-profit is already stretched to the limit and can’t afford training.

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 access to all of my recorded training with a year’s access to my Recorded Class Season Pass: Fundamentals and Masters Classes.

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. It can totally be a for-profit company, and it can be anywhere in the world, just as long as they’re making a difference. (If you work for Ginormous Profitable Global Corporation, you’re probably not going to make the cut.)
  • Your company 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 October 14. Applications have closed.


[Video]: Office Hours at Diamond Beach

Videos
2 Comments

I took a break from playing with glacier leftovers at Diamond Beach to answer the questions you upvoted at https://pollgab.com/room/brento/.

  • 00:00 Introductions
  • 1:21 Null Pointer: When we move to Azure SQL DB, should we worry about having all our data and backups in one place?
  • 03:27 Josh: We’re using indexed views, and we have to use the WITH NOEXPAND query hint. Am I doing something wrong?
  • 05:07 Uncompressed DBA: How should I troubleshoot PAGELATCH waits for a query?
  • 08:22 Recap

Updated First Responder Kit and Consultant Toolkit for September 2021

I’ve slowed the First Responder Kit update frequency down to once every 2 months. I know from firsthand experience working with clients that folks just can’t patch quickly enough, hahaha. Folks who want to be on the bleeding edge updates can always use the dev branch in Github, too, getting every new fix the moment it’s merged.

Wanna learn how I use it? Register for my free one-day class on Oct 19th or 20th on How I Use the First Responder Kit, or buy the recordings.

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

  • Fix: if databases aren’t online, don’t alert on non-default database settings. For example, if someone’s got a database offline or in restoring state, they shouldn’t be alerted about the delayed durability setting on that database. (#2984, thanks David Schanzer.)
  • Fix: added 1204’s explanation when the trace flag is enabled. (#2985, thanks Erik Darling.)

sp_BlitzFirst Changes

sp_BlitzIndex Changes

  • Improvement: scripting for unique constraints. (#2950thanks Erik Darling.)
  • Improvement: new informational (priority 250) check to tell folks when optimize_for_sequential_key is enabled for an index. Only shows up in Mode 4 since it’s a low-priority action that you don’t usually have to do anything about. (#2963 and #2991thanks Erik Darling and FlatlandR.)
  • Improvement: new unindexed foreign key check. (#2964thanks Erik Darling.)

sp_BlitzWho Changes

sp_DatabaseRestore Changes

  • Improvement: new @SetTrustworthyOn parameter lets you enable this on newly restored databases. (#2981, thanks jesusnac.)
  • Fix: if the database status isn’t already in restoring, try to set it into single user mode. (#2960, thanks jesusnac.)

For Support

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

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

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


[Video] Office Hours at the Seltjarnarnes Lighthouse

Videos
0

You posted and upvoted SQL Server questions at https://pollgab.com/room/brento, and I went out for a walk on the Seltjarnarnes peninsula to the lighthouse to answer ’em.

Here’s what we covered:

  • 00:00 Introductions
  • 00:58 Gio: Any known gotchas from using Windows Update to apply CUs to SQL
  • 02:17 AlwaysLearningDBA: Hello Brent. A friend of mine wanted to know what camera/system do you use when you are outdoor doing Q&A office hour sessions? He is just curious as the camera follows your movement as if someone is moving the camera physically and he thinks it’s awesome.
  • 03:34 PGA: Hi Brent If I have firstname lastname in one index and city,age on other index if my query is Order by City , Age My second index will be used?
  • 04:47 Inpasta DBA: Hi Brent! Do execution plans replicate over to the secondary in an AG? Wondering if a failover, planned/unplanned, occurred would the plans be the same. Thanks for what you do for the community!
  • 05:49 Jack: Hi Brent, with all the certifications available now for Azure, AWS and GCP has your opinion on the value of certifications changed?
  • 06:02 I’m a potato: Hi Brent! Is there a way to know the health of your statistics on SQL server and prevent their most common issues?
  • 06:44 Thomas Horner: In your experience, is using Always-On Availability Groups require more DBA monitoring and maintenance (i.e. babysitting)?
  • 07:26 Rick Lively: How should we capture a “baseline”, or a series of captures, with the Blitz scripts that you wish would have been collected to help understand performance problems when the server is under stress at a later time?
  • 10:07 Willem: Do you know of any success stories of companies that have made a the move to Azure with a large SQL Estate(+- 80 Enterprise Servers and up)? We’re figuring out how to attempt this, but there seems to be so many pitfalls to be aware off.
  • 11:47 Stockburn: Hi Brent any advice on deadlocks where the two processes are an update and a delete, they both end up hitting the clustered primary key and then as you put it, one of them gets a bullet from the deadlock monitor. Thanks for these office hours!
  • 13:04 catmanjan: Applications now have tech like kubernetes and are more or less hands off. Why don’t database engines do this too?
  • 15:17 LazyD: Hi Brent In upgrading from SQL2014 to SQL2019 what can be done about CLRs so that they don’t “break” due to changes in SQLCLR security since SQL2017.
  • 15:43 VegasDBA: What do you think of RedGate SQL Toolbelt? A friend insists that it’s a necessary purchase to help production DBAs manage an environment. Any other similar tools or utilities you’re a personal fan of?
  • 17:13 NullPointer: I have been fighting with tuning a query/view, this view joins to another view that when run by itself is super fast (with good estimates) but when I join it to a larger query all the estimates are way off
  • 17:58 Dave: What would you think about a disaster recovery plan that involved taking differential backups of the production server every 2 hours? Asking for a friend.
  • 20:03 Scott McFadden: Hi Brent, appreciate your SQLServer book recommendations. They are spot on. Do you have any book or training recommendations for learning PostgreSQL?
  • 21:28 Champaign DBA: Brent, I read your 5 part blog on foreign keys, and your concern about locking when using cascade delete. Wouldn’t that not be an issue when using read committed snapshot?
  • 22:14 Pat: Hi Brent! I’m a accidental DBA. My DB monitoring software often alerts on “OS paging”. What’s “normal” to hone in on?
  • 23:17 Seven: How does sampling work for statistics? If a FULLSCAN has been specified previously…
  • 23:37 SpongeBob SquarePants: Do you have any recommendation for tools specific for ODS in SQL Server?
  • 23:49 David: Do you have an tips to deal with people who take other peoples advice or tips as there own in the workplace? Here I am with 1.5 years experience in SQL, teaching people with 5+ Years experience. But when they tell the Boss it them who came up with query. Some don’t know union
  • 26:01 Khaled Budajaja: Duplicates on Primary Key Column. Some of my customers on SQL Server 2014 have this weird issue.
  • 26:45 SQL_Deadwood: How do I perform index maintenance on a synchronous AG database with heavy fragmentation (there was no maint. job) without, well, ruining everything? On-prem VM.
  • 28:18 SQL_Deadwood: According to the tricksy documentation hobbit, “There are specific but uncommon scenarios when…index maintenance may be needed in Azure SQL Database” – what are they?
  • 29:23 Recap

[Video] Office Hours: Ask Me Anything in the Reykjavik Harbor

Videos
0

You posted and upvoted questions at https://pollgab.com/room/brento to pick what you’d like to see covered in a Q&A session:

Here’s what we covered:

  • 00:00 Introductions
    00:26 Mehdi: Hi Brent! What is the first step when SQL Server is overloaded? But we can still connect to SQL Server by SSMS.
  • 02:36 Allan: Do you think learning SSIS is still worth or should everyone hope on to AWS?
  • 04:15 Igor: Hi Brent. In a recent office hour you had a question about changing the char data type column into varchar. In your anwser you mentioned that “she” correctly spoted that char data type should not be used and that it should be avoided in the future development.Why is that?
  • 05:49 1440×1080: hi Brent ! Is there a way to remove completion time of JUST a specific query(something like nocount) and not use the “global” settings in ssms (for all queries)
  • 06:35 Steve: What is the funniest mistake you have seen a Developer make that took down the whole database? I was once writing an sp to delete data from multiple tables with a Begin trans and still don’t know how but it took down the DB offline for few hours for recovery.
  • 11:03 Alexey: Hi Brent. How hard would it be for Microsoft to rename the ‘master’ database in SQL Server to something else? (Just as the did on GitHub – renamed ‘master’ branch to ‘main’)
  • 14:17 Uncompressed DBA: Hi Brent! If I use compression for some tables where the keys are ever increasing, does that increase the odds to encounter PAGELATCH_XX waits?
  • 15:35 AGuyNamedJim: Hi Brent! Do you feel “with encryption” is the best remedy in an Enterprise software context where leadership argues against Stored Procedures & Functions as it means our code is on the customer’s servers? (That’s what we did before and it was painful from a support perspective.)
  • 17:49 Torben: Schema lock. Select * Into NewTableName From OldTableName Will that lock the schema while the select is running, i think if oldtable contains millions og record, it can take some time, and would it be better to run it first with “where 1=0”, and change it to insert select syntax
  • 18:52 AsianDBA: Love your work Brent! When would you choose read scale (clusterless) always on over traditional always on availability groups?
  • 20:24 Zack Jones: Follow up to my varchar(max) to varchar(2500) question. As an aside the max usage in that one column is only 1000 characters. Is there a performance impact for including a varchar(max) as an included column in a covering index? I’m basically trying to optimize for reports. Thanks
  • 21:41 Zack Jones: Is there a file size for a .MDF file when you should consider adding .NDF files? If so how’s the best way to approach such a task? What’s the benefit of doing so? Is an 18GB MDF file a bad thing?
  • 23:33 Still_a_Clarion_programmer: I’m a slob and am never consistent with indenting, columns stacked or on-one-line, capitalization of keywords, etc. Do you ever use a prettifier such as Redgate’s SQL Prompt? (And if so, which?) Or should I just try to develop some self-discipline in my old age?
  • 24:53 Recap

[Video] Office Hours: Ask Me Anything About SQL Server at Hallgrímskirkja

Videos
6 Comments

I hang out by Hallgrímskirkja, the modern church in Reykjavik, and take your highly-voted questions from https://pollgab.com/room/brento. Here’s what we covered:

  • 00:00 Introductions
  • 00:18 __normally_weird (Sean C): Can you dispel the now prevailing myth that you and your wife are the only8 people in Iceland at the moment?
  • 01:33 Andy Leonard: What “bugs” exist in SQL Server 2019?
  • 04:22 Michael Devor: You have said you want to retire (relatively soon). How have you gone about preparing to support yourself and your wife for your retirement.
  • 06:03 Kevin M: Have you encountered sp_BlitzCache, QueryStore, and SQL Profiler showing a query returning a large rowcount, high CPU time, and large read count, but running the same query from SSMS shows less rows, reads and less CPU?
  • 07:56 Philip Clark: Can I Just say, you’re awesome Brent. Hope I get to meet you in person one day!
  • 09:24 Mike Byrd: Possibly a dumb question, but is it possible to redirect output from SET STATISTICS IO ON to a file or table?
  • 11:35 Random Name: A certain security software vendor is pretty adamant that the application should be installed on the same VM as the SQL Server because “there could be a lot of traffic.” Any guiding principles for when (if ever) to have the full application installed onto the database server?
  • 13:15 Martin: Hi Brent, I know that using MERGE is advised against. Are there any other operators that are problematic?
  • 15:10 Philip Clark: What do you recommend these days in terms of what processor (Intel/AMD) with clock speed and number of cores to choose? Minimum to start with for an Enterprise 2019 dedicated server? And how would this differ when specing an Azure VM for SQL?
  • 18:06 Michael Devor: Can you have too few indexes and could that be my friends issue?
  • 19:32 Seven: I asked a question earlier about stats fullscan; maintaining the integrity of full scan during auto updates. i guess what i meant was if enough rows have been modified to trigger an auto stats – if it could “append” a fullscan rather than replace it, or ignore it (no recompute)
  • 20:40 CacheTellMeTheTruth: How do you find which COLUMN stats sql server used to create an execution plan? I’m trying to find a list la col stats “potentially” useless (double quotes are mandatory here )
  • 23:11 Daniel Moll: Hi Brent. We are monitoring sys.dm_os_sys_memory and did not have any warnings since I activated “lock pages in memory”. now we have several events (high=0) a day. (Why) is this a problem?
  • 25:38 Juan Falcon: Is there and automatic way to update OpenSSL which SQL Server installs with Python Services?
  • 25:54 Igor: Hi Brent, what is the best way to reduce table size, after drop column?
  • 27:00 __normally_weird (Sean C): I have an interest in contributing to the community to “pay it forward” as well as selfishly gain experience to meet a long term goal of becoming a performance tuning consultant. How can I, as a mere mortal, positively impact the community beyond answering stack questions?
  • 29:04 Aaron: I have several queries that generate plans that have an Index Seek + Lookup for their respective tables. What are some key things to take into consideration?
  • 30:16 marcus-the-german: Hi Brent, a friend of mine would like to know how she can determine that a parallel executed query is executed on one NUMA node? Let’s assume we have a 2 x 8 core server and MAXDOP is set to 8
  • 31:24 Artur: I’m doing the D.E.A.T.H. method. Is it a good idea to test any index changes in non prod environment before I apply them in prod environment? 32:28 Artur: Have you ever used Distributed Replay?
  • 34:11 Mike B: I’m testing a solution using triggers to compress LOB data on insert with the COMPRESS function with a view containing the DECOMPRESS function. Thinking to trick the app by replacing the table with a view and avoid changing code. Seems to work, but any gotcha’s to look out for?
  • 35:37 Andrew from Canada: Synching a recent stack users table to an older stack DB to do some tests. It takes a long time and the system appears underutilized. why? by design?
  • 37:30 qTechnik: How to monitor cpu load by each database on sql server? Don’t like statistics from plan cache because different lifetime for each plan in cache.
  • 39:24 marcus-the-german: Hi Brent, do you have any experience/recommendation on receive side scaling (RSS) on SQL Servers?
  • 40:01 David Nelson: What are the options for using a source control system for versioning of stored procedures (like git, svn, etc)
  • 40:33 Steven: Hi Brent, thank you for the anwser on “why does a join spill to tempdb”. The parallalism is nearly evenly distrubted, so the operator was not allocated enough memory. Is it possible to allocate more memory to a specific operator in a query or do I have to use min_grant_percent?
  • 41:16 AlwaysLearningDBA: what do you suggest to transition existing career as production DBA into Architect or Data Science role?
  • 42:20 Kyaw Than: The app admin wants to set system DBs to compat mode 2016. I could test it out but would appreciate your opinion.

[Video] Office Hours: Ask Me Anything About SQL Server in Stokkseyri

Videos
2 Comments

Before Fjöruborðið opens for lunch, I answer a few of your questions from https://pollgab.com/room/brento including:

  • 00:00 Introductions
  • 00:29 Peter: What is your opinion about the SQL generated by Entity Framework?
  • 02:00 Peter: When would you switch from EF to stored procedures?
  • 02:45 Always Learning DBA: What are free resources to help me transition into an architect or data scientist role?
  • 04:43 How is the auditing and monitoring different for Azure SQL DB?
  • 06:33 Recap


[Video] Office Hours: Ask Me Anything About SQL Server On My Patio

Videos
1 Comment

I sit on the porch and discuss your highly voted questions from https://pollgab.com/room/brento.

Today’s discussion includes:

  • 00:00 Introductions
  • 00:27 DK: What was your Gordian Knot problem?
  • 03:25 George: How exactly does locking work on INSERT INTO…SELECT FROM?
  • 05:48 Michael: Can I tell if hyperthreading is turned on or off?
  • 07:25 Party People: What are the most common anti-patterns you see?
  • 10:20 Recap

 


Menu