[Video] Office Hours at Silver Strand State Beach: Ask Me Anything About SQL Server


It’s a beach day! Let’s hang out at Silver Strand State Beach and I’ll take your highly voted questions from https://pollgab.com/rooms/brento.

Here’s what we covered:

  • 00:00 Introductions
  • 00:45 Dylan: Why does an UPDATE on a 2nd key column in an index (100% FF) cause a “bad” page split; the first column is 100% unique. I noticed the tran log was doing a MODIFY operation vs INSERT/DELETE when the second column was moved to the INCLUDEs. Would you say this is expected behavior?
  • 02:05 Mehdi: Hi Brent! Why having non-aligned indexes on partitioned tables can cause inefficient query plans and CPU pressure?
  • 03:47 Bill: We have an application about to come online and I want to baseline the sql server. What is the best way to create a baseline? Do you have any recommended tools or articles?
  • 05:18 Bob: Hi Brent , What is your DBMaintenance recommnendation for VLDB ?
  • 06:03 PostNo: Is it hard to adjust being back in the states? If so, what’s been the hardest part?
  • 07:59 Woodard: I keep getting questioned on why I only let Veeam do copy_only backups and insist on native transaction log backups outside of Veeam. I don’t trust any system where I can’t touch the actual log backup files at will, and we use those backups every day. Is this uncommon?
  • 10:12 Oleg: Hi Brent, for brand new database that is in development, would you recommend to create indexes ahead of time based by guessing or applying DEATH method after it was in production for a while?
  • 11:50 sudo DBA: Hello Brent, You made an interesting point about writing a blog about what you know about SQL Server without worrying whether similar content is available online or not. What blog sites do you recommend to post my SQL Server related issues I have faced and resolved? Thank you.
  • 14:05 Greg: We have a 2TB prod db with 7 years data. We wish to reduce this to a few months but still need to hold 6 years data to meet compliance. Would you build an archive strategy on prod or trust the upstream data warehouse will have your back.
  • 16:17 Recap and demoing Helmut’s robotic top

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


It’s time for the first live Office Hours in my new home studio in San Diego! I took your highest-upvoted questions from https://pollgab.com/room/brento/ and answered ’em:

Here’s what we covered:

  • 00:00 Welcome
  • 01:30 Accidental DBA: Hi Brent, do you have a recommended checklist for trubleshooting occasional slow INSERT performance?
  • 03:19 Mark: How do companies deal with ever growing databases and writing to very large tables? Have you heard of ways to splitting your data, allowing smaller ‘active’ data on fast storage, while older data reside on cheaper storage?
  • 05:27 Kristófer Ólaffson: My TempB files fill the drive they are on about once a month. Currently sitting at 700GB combined. I have around 3TB of data on this server. I’ve been increasing the size of the drive TempDB sits on, but are there some tips in writing sql to not fill the TempDB as much?
  • 07:28 Rick: Hi Brent, I have a table in a client database with ~1million rows and a statistic that hasn’t been updated since 2018 with a modification counter of over 30.5 million. Auto-Stats update is enabled. Why would this statistic be getting updated?
  • 09:15 Dinis: Hi Brent, if I want to have maximum security in encryption, I need to have a combination of TDE, Encrypted Connections and Always Encrypted (for some sensitive columns). Is that correct?
  • 11:00 John James: My team wants to start collecting index data and use your DEATH method to send out suggestions to teams. Our company restarts all servers weekly. Does it make sense to collect the data before the servers restarts and aggregate it?
  • 12:33 Mahesh: Which is the best storage configuration, HDD (inserted locally into server) create one large virtual disk and make partition like data, log, back or create a separate virtual disk for data and like wise of log and back. What do you recommend. In my case we are using RAID 5.
  • 14:30 Tim Lara: Hi Brent, if I have a bunch of “identical” servers, but some of them perform very poorly on certain queries, while others run the same queries just fine, how can I tell what’s different between the “good” and the “bad” servers?
  • 16:08 Frank: Hi Brent, I have two high availability groups in a 2-node-sql-cluster. In case of a failover sometimes only one HA-group moved to the other node. What do I have to do so that BOTH groups always fail over together to the same node?
  • 17:46 Tom: What are the common roles of responsibility you have seen between programmers and DBAs? I am an accidental and only DBA in a dev shop of 24 and our roles have not been defined so I find it difficult to balance between what I think I should do and what I really should do.
  • 20:34 WillemHenk: Is switching from PostgreSql to SQL Server a big step? I find some articles regarding differences, but I was curious about your experience.
  • 23:50 Mr. Griffith: I understand you frown on in-place upgrades to SQL Server. Something about “juggling crown jewels”. Does the same rule apply to in-place upgrades for Windows Server?
  • 25:21 Jaden: My company using the SQL Server and they want to save the database into JSON. Why they do it and what changes to me?
  • 26:35 DBA Girl: Hi Brent! Our Company creates indexes for all foreign keys on a table. For some tables, though, this can create 20 or more indexes. How do you balance the recommendation of indexing those columns with the performance degradation of having too many indexes on a table?
  • 29:19 Bandar Almutairi: I came across a weird situation where queries against one table is way slower when ran from the readable secondary. It’s fixed after rebuilding the primary index on primary. My question is why isn’t it as slow on primary? Execution plans looked identical.
  • 32:42 Mark: We have some huge table. After huge inserts into the table the query plan takes index scan rather than seek(before it took). updatestat might fix that table but breaks other query. I can’t change query because a tool handles that. Do you have any idea why that happens. Thanks!
  • 34:26 LittleBobbyTable5: Hi Brent, When optimising queries using HINTS and OPTIONS I am hitting Bitmap Create part that are really slowwww. What voodoo optimisation technique should I employ to avoid this slow Bitmap creation. Could you show us a quick example please.
  • 36:40 Farshid: Hi Brent, Have you ever had a customer which you tried to convince them to switch from Oracle to SQL Server?
  • 39:00 Mikkel (Denmark): Some times the QueryText in the BlitzCache result is truncated. And it is not the full text in the QueryPlan. Is it possible to get the server to save the full query text?
  • 40:00 Jay: Is the CDC (change data capture) a viable strategy to publish “event” data to external systems instead of publishing from application code?
  • 41:17 Oleg: if you have to choose to tune query, one where SQL overestimates and another underestimates number of rows, which one would you prefer to tune and which to leave as is?

SQL ConstantCare® is Now Available in the UK & EU.

SQL Server

Earlier this year, we opened up training class sales to the EU and UK. Now, we’re taking it to the next level: my monitoring service SQL ConstantCare® is available in the EU and UK, too!

SQL ConstantCare

With SQL ConstantCare®, you install an app on your desktop that checks in with your SQL Servers once a day, sends us diagnostic data, and then you get one email a day with the most important tasks to make your SQL Server faster and more reliable. Just the important stuff – none of the fluff that causes you to set up an email rule to put everything from the monitoring tool into a folder.

It’s cost-effective, too: for just one price, you can monitor all of your SQL Servers.

But here’s the odd part: I don’t want you to buy it right now.

Because the whole reason we put this work in was for our upcoming Black Friday Sale. In the past, it hasn’t been open to the EU & UK due to the GDPR and VAT paperwork hassles. This year, it’s gonna be wide open to everyone. Next Tuesday, I’ll announce the bundle lineups, and my European friends will finally join in on the fun.

In the meantime, if you’re curious about how it works, check out the frequently asked questions and read about how we collect, transmit, and store your data. Because this is a cloud-based service and the servers are in the United States, I know it’s not going to be a good fit for everyone. I only want you to buy it if it’s a good fit for you and your company.

And not until November 1. See you then!

Congratulations to the Scholarship Class of 2021.

Company News

To celebrate the holiday season, we give back to those who spend their entire year giving back in their own communities. We go looking for data professionals who make a difference, and we try to make a difference in their lives too.

Pocket Square
The scholarship program makes the heart part happy.

This year, we’re proud to announce a new round of over 40 winners. We can’t mention ’em all, but here are just a handful of groups that we’re proud to help:

ACTED – saving lives and supporting people in meeting their needs in hard to reach areas, the most vulnerable amongst populations that have suffered from conflict, natural disaster, or socio-economic hardship.

Africa Health Research Institute – working towards optimal health and well-being of under-resourced populations.

Astor Services for Children & Families – providing children’s mental health, child welfare, and early childhood development services in New York’s Mid-Hudson Valley and the Bronx.

Charities Aid Foundation – a charity, bank, and champion for better giving, helping donors, companies, and charities make a bigger impact.

Children’s Miracle Network Hospitals – to provide the best care for kids, children’s hospitals rely on donations and community support, as Medicaid and insurance programs do not fully cover the cost of care.

CODAC – provides specialty care for mental illness, addiction, and trauma.

Comic Relief – working towards a just world, free from poverty.

DRF Air Rescue – one of the leading air rescue organizations in Europe.

El Rio Health – providing health care for all people in Tucson, including the underserved patient population.

Hope & Home – trains and actively supports Colorado Springs foster families.

Irish Cancer Society – transforming the experiences and outcomes of people affected by cancer through advocacy, support services, and research.

Magic City Acceptance Center – dedicated to providing a brave and inclusive space for the LGBTQ community in Alabama.

Mencap – valuing and supporting people with a learning disability, and their families and carers.

Pierce College – established in 1865 to provide career-focused education for soldiers returning from the Civil War and was one of the country’s first schools to embrace women as students.

Samaritan’s Purse – international relief and development organization that works through local churches amongst communities in need in countries across Sub-Saharan Africa, Eastern Europe, and Central Asia.

St John Ambulance – volunteer-led health and first aid charity responding to emergencies, supporting communities, and saving lives.

TNTP – working to end the injustice of educational inequality by providing excellent teachers to the students who need them the most and by advancing policies and practices that ensure effective teaching in every classroom.

WWF UK – putting nature first, making our food system sustainable, tackling climate crisis, and ensuring thriving habitats and species.

This season, take a few moments to think about how lucky you are, and how you can reach out to those around you to help them move forward, too.

Happy holidays.

Free #SQLPASS Keynote: 5 Ways the Cloud Impacts Your Career


Succeeding as a data professional is all about placing the right bets at the right times. The more you know about what’s happening around you, the better bets you can place.

I’ve been working with databases for over two decades. In this session, I’ll invite you into my home to have a frank, honest discussion about where our industry has been recently, and where it’s heading next. We’ll look at how that will impact the available jobs in our industry, and how it impacts where you focus your spare training time. We’ll even look at job ads and resumes in an effort to help you stand out.

In one hour, I can’t make you a pro at everything – but I can help you understand where you need to be a pro, and how to make your resume reflect that.

So where are we going to get together? At the keynote for the PASS Community Summit. It’s a totally free event on November 8-12, and that’s a bargain, because in the past you’ve had to pay thousands of dollars to attend. Register now for free, and I’ll see you on Friday, November 12th.

Now, uh, that we’ve got the professional part out of the way, wanna see the outtakes from when I tried to film that promo? Of course you do:

Those were shot during the day, of course – I ended up having to go back and record another version at night because my daytime versions were such a hot mess, hahaha.

Behind-the-Scenes Home Studio Tour


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

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


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

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

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


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

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?


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


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


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

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

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


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

First Responder Kit

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.