Blog

9:27AM: Hugo Kornelis is fighting AML, so there’s a heart-touching video playing about #TeamHugo.

#SQLPass

I’m in Seattle for the biggest annual gathering of Microsoft data platform professionals, the PASS Data Community Summit. This is the first in-person Summit since the pandemic, and the first since Redgate took over ownership from the old PASS organization. I’m really excited – this is like a family reunion for me.

Microsoft’s Rohan Kumar is kicking things off this morning as we speak with the Day 1 keynote. Rohan is the corporate vice president of Microsoft Azure Data, and he’ll likely be introducing lots of other Microsoft folks to cover things during the keynote.

Historically, the Summit’s day 1 keynote has covered:

Reminders of recent Microsoft data features
Release dates for upcoming products
Customer success stories from companies who’ve been using the products prior to their official release

There’s less than 60 days left in the year 2022, and we still don’t have a release date, pricing, or Standard vs Enterprise feature lists for SQL Server 2022. It’s reasonable to expect these things to finally be revealed – or if not, perhaps a rename to SQL Server 2023.

You shouldn’t expect outright surprises for SQL Server 2022 – as in, “Hey, here’s an all-new feature for SQL Server 2022 that we’re announcing for the first time!” After all, we’ve already got Release Candidate 1. Instead, the Day 1 keynote is usually used to remind folks who don’t stay quite as in-tune with the news as you do, dear reader – not everybody’s savvy enough to read my blog.

I’ll be live-blogging the keynote, sharing my thoughts about what happens. You can refresh the post to follow along – the most current notes will be at the bottom, so if you’re reading later, you can read through the recap as it happened.

7:30AM: Registration is open! So cool to see it back in the same spot, like coming home.

7:40AM: Azure Data Studio v1.4 brings Apple Silicon support. It’s now compiled natively for ARM, so it’s faster. The keynote hasn’t started yet, but I just noticed that got published today, and I wouldn’t be surprised if it was mentioned onstage. I’ve used Azure Data Studio on my Mac for quite a while, but the speed improvements will be very welcome because startup time has been pretty slow. (I know, I know, I should just leave it running.)

8:02AM: The doors opened, and attendees are pouring in.

#PassDataCommumitySummit keynote setting up. pic.twitter.com/YF2ca7pET7

— Brent Ozar (@BrentO) November 16, 2022

8:10 Kate Duggan, Chief Marketing Officer for Redgate, took the stage to welcome everyone and talk about how big the event is. (The WiFi is overloaded, so I’m probably not going to be able to upload photos of things as they unfold.)

Kate Dugan, Redgate’s Chief Marketing Officer, welcoming everyone to the #PassDataCommumitySummit. pic.twitter.com/1jYqGQ1Kn2

— Brent Ozar (@BrentO) November 16, 2022

8:15: Amusing behind-the-scenes videos of people traveling to their first in-person event in years. (Seriously, this is more true than y’all might know – several speakers I’ve talked to have said, “I totally forgot things I used to routinely bring to events, and I forgot how I used to pack.”

8:20: Rohan Kumar, Microsoft’s Corporate Vice President of Azure Data, takes the stage.

Watching @RohanKData take the stage at #PassDataCommumitySummit. pic.twitter.com/Gmfh6JUpHB

— Brent Ozar (@BrentO) November 16, 2022

Rohan: “While all of you are familiar with the star player, SQL Server…” Not a bad way to segue into saying, “I’m gonna talk about Azure stuff because you already know SQL Server.” That’s fair. Keynotes are useful for vendor education, and as rapidly as things keep changing, people need Azure education.

8:26AM: SQL Server 2022 is generally available now. That’s not coming from the keynote – that’s from a blog that probably went live a few minutes too early. Here’s the full release post.

8:30AM: The winner of my Guess the Release Date Contest is interesting. Nobody actually guessed 2022/11/16 in the proper date format, at least. Scott Holiday was the only person to guess 2022/11/15 (closest without going over), so he wins.

8:36AM: Failover to Managed Instances isn’t ready yet. In the footnotes of the release post, Microsoft notes:

The bidirectional disaster recovery capability of the Link feature for Azure SQL Managed Instance is available in limited public preview.?General availability will occur at a future date.

That’s a bummer because it’s one of the most widely anticipated features, but at the same time, you don’t wanna use something that complex until it’s ready. It reminds me of how database mirroring wasn’t supported until a service pack came out.

#PASSDataCommunitySummit attendees will get the SQL Server 2022 shirt @RohanKData is wearing o stage. pic.twitter.com/O0r3OtJZYh

— Brent Ozar (@BrentO) November 16, 2022

8:39AM: Rohan acknowledges that some companies aren’t moving to the cloud yet, or can’t due to country regulations.

8:40AM: Announcing a new pay-as-you-go SQL Server licensing model. You can already do this in the cloud (AWS/Azure/GCP), but now Microsoft will rent you the licenses on premises, too.

New pay as you go license model for #sqlserver, works on premises like the cloud does. pic.twitter.com/rBc1MMnjdf

— Brent Ozar (@BrentO) November 16, 2022

8:41AM: Bob Ward and Anna Hoffman took the stage to talk about Azure-connected SQL Servers. God bless ’em for trying to make a security demo amusing.

8:46AM: Anna demoed the pay-as-you-go license choice built into the SQL Server 2022 installer. Nobody clapped, but I seriously should have clapped because that’s bad ass. Assuming the Azure authentication is pretty quick & easy – and I bet it would be – then this makes managing your licenses way easier. Let Microsoft do the accounting.

8:48AM: Anna demoed the failover and failback feature of SQL Server 2022 to Azure Managed Instances. (This is the part that is only in preview now, not generally available yet.)

8:51AM: Video about how Mediterranean Shipping Company (yay, Javier Villegas) is leveraging the Microsoft data platform.

8:53AM: Bob Ward and Conor Cunningham taking the stage in cowboy hats, to the tune of country guitar music. Since 2022 is code named Dallas, they wanted Rohan Kumar to wear a cowboy hat.

HAHAHA, love it. #PASSDataCommunitySummit pic.twitter.com/keW8lhbAop

— Brent Ozar (@BrentO) November 16, 2022

8:55AM: Bob Ward demoed Degree of Parallelism Feedback. He used Perfmon (there wouldn’t be a keynote without Perfmon) to measure parallelism, demoing 2022’s new Degree of Parallelism Feedback. Query Store shows how query performance improved over time, cutting the degrees of parallelism, and the query got faster with less cores. Conor talked about how 2016’s Query Store built the foundation for this, and how he’s excited to see tools continue to leverage it. He casually mentioned that row mode queries don’t scale well beyond 8-16 cores, but batch mode queries do.

9:00AM: Conor Cunningham showing a preview build that does lock escalation differently. This demo is just about impossible to follow. It would have been better in plain English to just say, “We’re trying to solve lock escalation problems,” hahaha.

9:01AM: Lindsey Allen jumping into the stage to show Azure Data Studio with Github Copilot. She started writing a comment first to explain what she wanted, and then typed SELECT, and Copilot guessed the function she wanted based on her comment. It’s basically English Query. This will be amusing to see SQL Server discover the intellectual property risks of Copilot that other language developers are already arguing (and suing) about.

9:05AM: Rohan back onstage to talk about Managed Instance Link feature (HA/DR failover to/from cloud), and backup portability (restore MI backups down on-premises to SQL Server 2022.) The slide says “General Availability”, but that’s a little tricky since the 2022 RTM release post says MI Link isn’t actually generally available today.

9:09AM: Azure Cosmos DB for PostgreSQL lets you run your Postgres apps with the data stored inside Cosmos DB, without having to change your apps. No applause here – it just isn’t the right conference for it. Was worth the 30 seconds he spent on it, but no more than that. It’s a neat idea though.

9:10AM: Azure Synapse Link for SQL is generally available, which makes your ETL processes way easier. Evidently they’ve just added a Schedule Mode, smaller compute capacity support, vNet support, and full support for datetime data types. I read that list and go, “Uh, you tried to launch an ETL product without those things?!?”

9:14AM: Azure Data Factory SAP CDC Connector in general availability, Azure Synapse Mapping Data Flow for M365 Graph is in public preview.

9:26AM: The WiFi in the conference center died for a while. I wrote a few updates, but lost them in the WiFi outage. <sigh>

9:30AM: It’s a wrap! Off to today’s sessions.

Office Hours: Read This, Watch This Edition

Last Updated November 10, 2022

SQL Server

This week, a lot of the questions you posted at https://pollgab.com/room/brento just need pointers to resources at other places. Welcome to the Read This, Watch This series of answers.

Don’t Blame Anthony: Our developers prefer writing TSQL queries directly in their .NET code rather than writing sprocs and calling them. What are the pros / cons to this approach?

Divakar: Is there any formula based on number of processor cores that I can set for Maxdop for a sql server

Mr. No: What is your favorite NOSQL database?

Dan Griswold: How would you manage access to SQL Server Agent jobs. Our development team shares jobs and they all want to have access to run and modify them. Of course, sysadmin is the only way to grant that privilege. So, we created a sql account and gave them all the pswd. Any better ideas?

Jr Wannabe DBA: Hi Brent, once in a lifetime I saw a new index blocking insert operations to a table. What are the reasons it can happen? The support team removed the index before I was able to take a look at it, but it looks like the index created some restriction on what can be inserted. Thx.

Watch this video and this video.

John Martin: A query is imbedded in a compiled program. Is there a way to force it to use a specific index? I can’t change the query. It often picks the wrong index even if we’ve updated statistics and rebuilt the index

Try the Microsoft Assessment and Planning Toolkit.

Magnús: Do you have any scenarios where you like to use the QUERY_OPTIMIZER_COMPATIBILITY_LEVEL query hint?

Watch this video.

Barney Fife: Worried about unmanaged / unlicensed SQL servers running under someone’s desk. What are some good ways to automate discovery of all SQL servers on the company network?

ScenarioFromRealWorld: You log on to a SQL Server to help your client. You notice that almost every process/connection in the SQL Server is using the sa account. Both locally and also from clients through the network. Would you say anything regarding this, and if so, what would you say to your client?

Read this and this.

Leif Hole: Hi Brent ! Is there a noticeable performance difference between cluster index on one vs two integer columns ?

Watch this video.

Dominique B: What’s the white (moto) helmet’s story ?

MyFriend: Hi Brent. sp_BlitzIndex (latest) is reporting “Index Hoarder: Unused NC Index with High Writes” for some indexes that have 0 Reads in Usage stats. But have numbers in “singleton lookups” and/or “scans/seeks” in Op Stats. Why is that & would you still consider them safe to drop?

Watch this.

Ramil: Hi Brent! Is there a tool to replicate production load on other environment? For example, we make backup or snapshot of a database, then we start to record all transactions with sessions. Then we replicate this load on other environment as it was on prod.

MyFriend: Hi Brent Have you ever “fixed” Parameter sniffing problems, because of spills to disk, by turning Adaptive Memory grants off in SqlServer2019? And if so, what would be the main drawback in doing so?

Watch this.

CliveP: Hi Brent. Can you let me know how you would handle an Aggressive under index warning on a table where there are no missing recommendations and I don’t have access to the application code?

Watch this.

I chuckle a lot when I see data professionals banging their heads against a wall on a hard problem, Googling their brains out, frustrated because they’re not finding any easy answers.

You’ve been consuming my free material for years. You’ve worked hard to polish your knives, to continuously get better.

But for some reason, you think you’re too smart for training classes.

Does it ever occur to you that the reason why you’re not finding any easy answers is because there aren’t any? You’re a senior data professional. You’ve run out of easy buttons. It’s time for you to upgrade your skills so you don’t have to keep beating your head against that wall.

Level 1 Bundle

$395Normally $1,785

SQL ConstantCare®
The Consultant Toolkit
All Fundamentals classes

Level 2 Bundle

$995Normally $2,780

SQL ConstantCare®
The Consultant Toolkit
All Fundamentals classes
All Mastering classes

Or, Buy It for Life.

Pay just once, and get access to my recorded training classes for the rest of your life. (Note that these don’t include the SQL ConstantCare® or the Consultant Toolkit apps.)

Fundamentals for Life

$595Normally $795

All Fundamentals classes
All future Fundamentals classes, too

Mastering for Life

$1,495Normally $1,990

All Mastering classes
All future Mastering classes, too

Why Not Both?

$1,795Normally $2,785

All Fundamentals classes
All future Fundamentals classes, too
All Mastering classes
All future Mastering classes, too

The Fine Print

To get these deals, you have to check out online through our e-commerce site. We only take payment via credit cards – no Subway coupons or IOUs.

Can we pay via check, purchase order, or wire? Yes, but only for 10 or more seats for the same package, and payment must be received before the sale ends. Email us at Help@BrentOzar.com with the package you want to buy and the number of seats, and we can generate a quote to include with your check. Make your check payable to Brent Ozar Unlimited and mail it to 9450 SW Gemini Drive, ECM #45779, Beaverton, OR 97008. Your payment must be received before we activate your training, and must be received before the sale ends. Payments received after the sale ends will not be honored. We do not accept POs as payment unless they are also accompanied with a check. For a W9 form: http://downloads.brentozar.com/w9.pdf

Can we get discounts for group buys? Not during the Black Friday sale: these prices are as low as I go all year.

Can we send you a form to fill out? No, to keep costs low during the Black Friday sales, we don’t do any manual paperwork. To get these awesome prices, you’ll need to check out through the site and use the automatically generated PDF invoice/receipt that gets sent to you via email about 15-30 minutes after your purchase finishes. If you absolutely need us to fill out paperwork or generate a quote, we’d be happy to do it at our regular (non-sale) prices – email us at Help@BrentOzar.com.

[Video] Office Hours: Sponsored By Black Friday Edition

Last Updated November 10, 2022

Y’all post questions at https://pollgab.com/room/brento, upvote the ones you’d like to see me cover, and then I talk through ’em.

Here’s what we covered today:

00:00 Start
00:22 CKI: Could please give me pointers how to answer the question “Do we need to create a new instance for the new application or can use the existing one?” Thank you very much
01:18 Chris May: Within an index, if you can guarantee unique selectivity with 2 key columns, is there a fundamental difference between adding an extra key column or an included column? Which would you typically prefer?
03:15 Pyjamarama: Hello Brent. In one of our customers our User databases (Greek_CI_AI) are in different Collation than TempDB (Latin1_General_CI_AS). What is the best way to fix that? (We prefer to keep the CI_AI). Thanx!
04:41 Maksim Bondarenko: What would you do first if two sql servers with the same version and CU generate almoust identical execution plan with one exception? Second server generates one additional block (in my case it is Index Spool after Cl scan) and that’s why 10 times work faster. Thx
05:47 Piotr Rasputin: What’s your opinion of azure vm host caching for SQL vm’s?
07:30 Peter: What does your op love ones at home say when you talk like Clippy all the time? 🙂 Thanks for all the knowledge you share with us all
08:00 Gerardo: What is your top story for when you were burned by missing / incorrect SQL Server documentation?
10:30 Too Much Spare Time: Last time there was a question about how to deliver bad news as a consultant. What type of bad news do you find delivering the most?
11:49 chris: With a SQL Server built on a virtual machine on VMWare is there a way to determine CPU and/or memory pressure on the host without having access to VCentre?
12:38 LockedUp: I have a proc that is causing deadlocks. I have tracked it down to an Update statement with a ROWLOCK hint. Could the ROWLOCK hint be causing the deadlock? Update statement is well written and indexed, not sure why ROWLOCK is even needed.
14:24 Leonard: What is your opinion of the SQL Server data collector? Any good for performance troubleshooting?
15:12 Maciej: Hi Brent, do you have a missing feature in SSMS and/or SQL Server that you treat (at this point) as an old friend? 😉 In addition if you could “influence” Microsoft to implement one which one would it be?
17:11 Leif Hole: Hi Brent ! Really enjoy your “think like the engine” and the fundamentals ! My friend have this theory that Mr. Codd meant that all joins between tables is the best way of getting data. Never denormalize ! Is there any thumb of rules when it comes to denormalizing ?
18:09 chris: I understood Azure SQL DB was always current; however, when I view the compatibility level of some of my databases they’re not current. Is it up to me to change this? Any gotchas with doing so?

[Video] Office Hours: Surprise Edition

Last Updated November 6, 2022

Y’all post questions at https://pollgab.com/room/brento, upvote the ones you’d like to see me cover, and then I talk through ’em.

There’s a slight problem with this episode: I forgot to hit the Record Questions button in PollGab, so I can’t quickly/easily generate the list of questions we covered. If you asked a highly voted question in the last week or so, it’s probably in here.

Is There a Bug in SQL Server’s MAXDOP Calculation? (Update: Yes!)

Last Updated November 3, 2022

Configuration Settings

I think I’ve found a bug in SQL Server setup’s MAXDOP calculation, and I need you to take a second look. Setup is recommending MAXDOP 8:

Which is odd, because this is running on an AWS i3.16xlarge with 2 sockets, 2 NUMA nodes, 32 logical processors per node, 64 logical processors altogether. In this screenshot, I’ve laid Task Manager alongside setup so you can see what I mean:

If you click on the “Configure the max degree of parallelism” link in setup, it says:

In this case, the SQL Server has multiple NUMA nodes, with greater than 16 logical processors per node – that’s the last line of the screenshot. In that line, Microsoft says MAXDOP should be half of the number of logical processors with a max of 16 – so 16.

But it’s recommending 8. Hmm.

Is Soft-NUMA the culprit? No.

After SQL Server finishes installation and starts up, the error log shows automatic soft-NUMA kicked in because NUMA nodes had more than 8 physical cores:

So it created 4 NUMA nodes, each with 16 logical processors. In that case, maybe the setup calculation was taking that configuration into account – 4 nodes, each with 16 cores. In that case, let’s revisit the guidance:

Now, the number in play is the 3rd line of the screenshot – “Less than or equal to 16 logical processors per NUMA node.” In that case, we’re supposed to keep MAXDOP at or below the # of logical processors per NUMA node.

Now, technically 8 is below 16 – but where the heck is 8 coming from? Why not, uh, 16, or 4, or 12, or for that matter, 2?

I’m guessing I’m missing something obvious, but I asked on Twitter, and nobody figured it out. Worst case, either the documentation is wrong, or setup is wrong, there’s some kind of other recommendation that isn’t shown in setup – like maybe there’s a hidden max of MAXDOP 8 during setup?

So, what’d I miss?

Update 1: one of the smartest SQL Server folks I know, Joe Obbish, writes that he thinks it may not be documented anywhere. It’d be cool if we did get it documented, though – whatever logic is good enough for setup should be good enough for KB 2806535, so users can make that same decision as their VM sizes grow.

Mystery Solved: Yep, There’s a Mismatch.

Microsoft employee Sean Gallardy answered that setup uses a different formula than the documentation recommends:

Step 1: Calculate Hardware NUMA and Soft NUMA
Step 2: Decide whether Hardware or Soft NUMA will be used
Step 3: Divide the total logical processors by the NUMA used
Step 4: If > 15 LPs/NUMA, MAXDop = (LPs/NUMA)/2, otherwise MAXDop = LPs/NUMA

Which means that if you have:

14 logical processors per NUMA node: MAXDOP will be set to 14
16 logical processors per NUMA node: MAXDOP will be set to 8

<sigh> That doesn’t make any sense, but it is what it is. It’s probably always been this way since SQL Server 2016 “fixed” MAXDOP by setting it during setup.

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

Last Updated December 6, 2022

Who's Hiring

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

The rules:

Your comment must include the job title, and either a link to the full job description, or the text of it. It doesn’t have to be a SQL Server DBA job, but it does have to be related to databases. (We get a pretty broad readership here – it can be any database.)
An email address to send resumes, or a link to the application process – if I were you, I’d put an email address because you may want to know that applicants are readers here, because they might be more qualified than the applicants you regularly get.
Please state the location and include REMOTE and/or VISA when that sort of candidate is welcome. When remote work is not an option, include ONSITE.
Please only post if you personally are part of the hiring company—no recruiting firms or job boards. Only one post per company. If it isn’t a household name, please explain what your company does.
Commenters: please don’t reply to job posts to complain about something. It’s off topic here.
Readers: please only email if you are personally interested in the job.

If your comment isn’t relevant or smells fishy, I’ll delete it. If you have questions about why your comment got deleted, or how to maximize the effectiveness of your comment, contact me.

Each month, I publish a new post in the Who’s Hiring category here so y’all can get the latest opportunities.

[Video] Office Hours: Answering Your Microsoft Data Platform Questions

Last Updated October 24, 2022

Ask questions at https://pollgab.com/room/brento and upvote the ones you’d like to see me cover.

Here’s what we covered today:

00:00 Start
00:59 neil: is it dangerous/risky to expand a drive in azure with SQL data files on it? sql on azure vm. dont know what happens behind the scenes
02:07 Brett: I am a consultant at a large cloud provider. I have to protect my job and not upset our customers too much. How do you recommend balancing the line when delivering bad news? At times I have to soften my words, but in reality everything is in the toilet.
02:35 Madisynn: Does PostgreSQL handle xml / Json data any better than SQL server?
03:14 Tobias: What is your opinion of the new buffer pool parallel scan feature in SQL 2022? When is this a compelling reason to upgrade from SQL 2019?
04:41 Fyodor: What is your opinion of the Microsoft Azure Premium V2 cloud storage improvements (125 – 1,200 MBPS, 80,000 IOPS, 64TB)?
05:40 Yitzchak: What kinds of performance issues have you run into with SQL Change Tracking?
07:00 DBAInHiding: After 10y as a prod support DBA, I moved to ETL dev with T-SQL + SSIS for 5y. I still tinker with my DBA skills with a home lab setup and training up on PowerShell/dbatools. I’d like to go back to being a DBA but will my recent developer track be seen as an asset or hindrance?
08:09 Efraim: What conditional debug message printing techniques do you like to use in your stored procedures?
08:42 Pyjamarama: Hi Brent, SQLServer 2014 with LARGE amounts of multiple plans. I used your query to find the top10 queries involved (your “Why Multiple Plans for 1 Query Are Bad”post). They are SELECT statements by our PowerBuilder application.Parameter sniffing?What do you suggest we do? Thanx
09:18 Magnús: Which windows server admin concepts / technologies should a SQL DBA be familiar with when running SQL Server 2019 on a Windows Server 2019 cloud VM?
10:22 Haydar: What are common mistakes you see regarding the use of TSQL cursors?
11:34 Jagelman: My Friend needs a Reporting Database on AWS RDS (SQL 2016). He plans to use replication, however there is a problem. The fact table is a partitioned, and has a clustered columnstore index, so it has no primary key (cannot be replicated). Any suggestions on how to deal with it?
12:50 Hans_Niemann_is_innocent : Hey Brent, I’m 5 years into the DBA career and never had to deal with any complex corruption issues. Is this still an important skill? I’m thinking of complex corruption as something more than just doing backup/restore to get online. Go Hans Niemann!
15:16 Yousef: What is your opinion of Azure Cosmos DB for PostgreSQL?
16:24 Yitzchak: Can having a [UNIQUIFIER] hidden column on clustered index affect query plan / query performance?

[Video] Office Hours: Nine Minutes of Answers

Last Updated October 22, 2022

Post your questions at https://pollgab.com/room/brento and upvote the ones you’d like to see me discuss during my live streams. This week, I took a break from working on my PASS Summit sessions in order to chat:

Here’s what we covered:

00:00 Start
00:32 Jeremy: We have an older web app we’re going to be rewriting to use microservices. We’ll also be migrating it from on-prem to Azure. Our dev consultant is recommending we use Postgres rather than MS SQL Server to save money. What are your thoughts on moving from MS SQL to Postgres?
02:19 Peter: Have you ever deleted unused stats or think it might be worth deleting stats on a table to improve performance of a stats maintenance job? My pain point is an exceptionally long running stats update.
03:27 Jacob H: Hi Brent, how do you approach tuning when you are not able to run a query in production?
04:30 Kimberly Hathaway: Brent – battling sysadmins in my org. They want Veeam backups for all SQL svrs & not SQL backups. Claim is point in time DB recovery with Veeam. I say logs are not getting truncated so Veeam is not talking to SQL. Can you point me to a doc that will support this.
06:19 Cats_Everywhere : Hey Brent, I’m planning a new SQL Server build. I expect the Business people want this server to exist for 8-10 years. Are there any good arguments (business people would love) to persuade them to stick to a 5 year max? I picked 5 years due to Patching support from MS.
07:39 Ólafur: Any indexing tips for “SELECT DISTINCT F1, F2, F3”? Is this more / less important than indexing for the WHERE condition?
08:17 Bingo Boy: What are the pros / cons of setting up a SQL Server learning environment for log shipping / A.G. using containers vs virtual machines?

Learn the Fundamentals of TempDB for Free This Week.

Last Updated September 18, 2022

TempDB

Every weekday during the month of October, one of the modules from my Fundamentals classes will be absolutely free to watch. But you gotta watch it that day – you snooze, you lose.

For this week’s TempDB class, you don’t have hands-on labs, but if you want to follow along with the demos, I’ll be using the same 50GB Stack Overflow 2013 database we used during the first two weeks.

Oct 24 – How the Version Store Affects TempDB
Oct 25 – How Temp Tables are Different from Regular Tables
Oct 26 – How Table Variables Can Be Better Than Temp Tables
Oct 27 – Temp Tables & Table Variables at Scale: PFS/GAM/SGAM Contention
Oct 28 – How Memory-Optimized Table Variables Reduce Contention
Oct 31 – How Execution Plans Use TempDB

Eagle-eyed readers will note that this isn’t all of the modules from each class, nor is it all of my Fundamentals classes. That’s because this whole thing is a diabolical mind trick, dear reader. I aim to get you hooked on the quality of my online training, and then come November 1st, when my annual Black Friday sale starts, you’ll be ready to get out your wallet – or your boss’s wallet – to get a Recorded Class Season Pass: Fundamentals, or Mastering, or the Level 2 Bundle when they go on sale.

Let’s Hang Out in Person at the PASS Summit Next Month.

Last Updated October 20, 2022

#SQLPass

I’m so excited to see everybody again at the PASS Data Community Summit in Seattle on November 14-18.

Here’s the crazy part: I’m not going to attend a single session. Not one. (Well, except the ones I’m teaching, and the keynotes.)

Instead, I’m gonna spend every single conference hour hanging out in the Community Zone, talking to people in the hallways, and catching up with people that I haven’t seen for a couple of years. People like you.

Weird, right? Especially since this is a release year for SQL Server 2022, and there are probably gonna be a lot of good sessions from Microsoft. You know what? I don’t care. I don’t. I’ll watch the recordings and read the slide decks later. When I’m in Seattle, I’m gonna have the experience that I missed so much.

I wanna see y’all again, hear what you’ve been up to, take pictures with each other.

If you can’t come in person, it’s also a hybrid event: you can either attend in-person in Seattle, or online via live streaming. If you attend online, PASS is recording the live stream, and you’ll be able to re-watch it during the week of the conference. This is super helpful for folks who have an emergency at work, have to drop off the call, and want to be able to catch up on what they’ve missed.

Here are my sessions.

Pre-Conference Workshop: Mastering Parameter Sniffing – You’re a database developer or DBA with at least 5 years experience performance tuning queries and indexes. You already know you have parameter sniffing issues, and now you need to figure out how to fix it. In this one-day class, you’ll learn how to reduce the blast radius with index changes, query tuning, and database-level settings. You’ll also see firsthand how SQL Server 2017, 2019, and 2022 try to reduce it with adaptive joins, adaptive memory grants, automatic tuning, and caching multiple plans. Attendees will also get one year access to the full 3-day Mastering Parameter Sniffing recordings, too! That’s a $395 value, and it’s free with your workshop admission. (You’ll get the access in class.)

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

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

That just got weird, and I don’t even care.

Updated First Responder Kit and Consultant Toolkit for October 2022

Last Updated October 18, 2022

First Responder Kit Updates

It’s fall – time to review your SQL Servers’ configurations, health, backups, and performance before fall turns into fail. To help, we’ve got a new version of the First Responder Kit.

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

To get the new version:

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

Consultant Toolkit Changes

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

Fix: now works when system database collations don’t match. (#3132, thanks Bjorn Nordblom.)

sp_Blitz Changes

Enhancement: check for most recent log backup using DBCC DBINFO rather than msdb. This way, it works even if the log backups were taken on another AG replica. (#3143, thanks SQL Lambert.)
Enhancement: warn about additional dangerous 3rd party modules. (#3149 and #3150, thanks David Wiseman.)
Enhancement: works on Amazon RDS even if the server name has been changed. (#3146, thanks Bo Anderson.)

sp_BlitzFirst Changes

Fix: returns the Azure SQL DB compatibility that was broken in the last release. (#3139, thanks SJOrderDIRECT.)
Fix: even more compatibility for Azure SQL DB. (#3130, thanks DMonlineUK.)

sp_BlitzQueryStore Changes

Fix: Azure SQL DB Managed Instance compatibility. (#3136, thanks Chris May IVCE.)

sp_DatabaseRestore Changes

If you use sp_DatabaseRestore and you have servers in different time zones, we’d appreciate your help testing #3113. It’s not included in this release because no one has tested it yet. If you can test the StopAt parameter with different time zones and report back in that issue (not here in the comments) about how it worked, that’d be great.

Bonus changes: Anthony Green kept up the tireless work of keeping the SQL Server versions file up to date.

For Support

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

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

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

Learn Columnstore Indexes for Free This Week.

Last Updated September 18, 2022

Columnstore Indexes

Every weekday during the month of October, one of the modules from my Fundamentals classes will be absolutely free to watch. But you gotta watch it that day – you snooze, you lose.

For this week’s Fundamentals of Columnstore class, you don’t have hands-on labs, and you’re not meant to follow along with the demos. Columnstore is about bigger data, so I use the 2018-06 ~180GB copy of Stack Overflow (info page.) You definitely don’t need to grab that, but I’m mentioning it here in case you want to follow along with the demos.

Oct 17 – How Columnstore Data is Stored
Oct 18 – How Columnstore Data is Deleted, Updated, and Inserted
Oct 19 – How Columnstore Data is Selected
Oct 20 – How Columnstore Data is Rebuilt
Oct 21 – Clustered Recap and Nonclustered Columnstore Indexes

Make sure to keep up – next week, we’ve got Fundamentals of TempDB to finish up the month.

Office Hours: One-Word Answers Edition

Last Updated October 13, 2022

Production Database Administration

Some of the questions y’all post at https://pollgab.com/room/brento are pretty straightforward. Let’s get ‘er done.

LetTheDbaHandleIt: My friend needs to track who has accessed what data. This is easy at OS and app level, but how can my friend verify that those who have direct DB access are not snooping on data inappropriately? e.g. payroll or HR DB. Yes, trust them, but how to verify (efficiently)? SS Profiler?
Erik: Do you know how to see last execution of a SQL view in SQL server?

Auditing.

PaginationWoo!: Hi Brent. In all of your training and demos you’re very AR (lol) about always prefixing your tables with the schema name (.dbo) which, let’s face it, is the correct way to roll, but was there an experience you had that enforced this decision?

No.

Cats_Everywhere: Do you think Microsoft will stop supporting on-prem Windows Failover Clustering to push customers to the cloud in the next 5 years? WFC appears to no longer be covered in their training on learn.microsoft.com — that’s all just azure focused now.

No.

SeekingUnderstanding: You frequently make it clear that you don’t care about external fragmenation. Do you care about internal fragmenation (page density)?

Yes.

Dopinder: What are your thoughts about new support for the MERGE statement in PostgreSQL 15? Better than Microsoft implementation?

Dunno.

Gershom: Does use of NOLOCK affect the query plan?

Rarely.

ZappyDBCC: Hi Brent, what would your advice be to dev teams to allow DBA to easily associate SQL requests to applications ? “Application Name” in cnx string ? comments in SQL requests ? other ? Thanks

Logins.

Peter Riis: How does a Nonclustered index behave on a Heap?

Watch.

Wes: Hello Brent, what do you think is the best way to have multiple sequences in the same table (one for each company id). I currently use a trigger with an UPDLOCK when selecting the highest previous value to avoid duplicates.

Dunno.

Efraim: Does having access to PostgreSQL source code make it easier to troubleshoot hard performance issues as compared with SQL Server?

Yes.

EngineHorror: Hi Brent! How often do you see PolyBase used in production? What are the top used data sources and their sizes?

Never.

Srinivas: Is it ever ok to consolidate two NC indexes with some overlapping include fields if it puts you over the 5 field max suggestion while reducing the number of NC indexes for a table?

Yes.

Pyjamarama: Hi Brent! How can I locate unused and missing indices using sp_BlitzIndex? Thaaaanx

Watch.

Gershom: Are there any RDBM’s that do a good job of automating NC index management (creation, monitoring, altering)?

Dunno.

Gershom: Do you have a recommended way of testing the selectivity of multi col Index A vs multi col Index B when consolidating similar NC indexes?

No.

Sister Hazel: Will you be releasing any new course content featuring SQL Server 2022 functionality?

Yes.

John-DK: Do you know how SQL server select which rows when I use sample in runstat?

No.

Rush: Hey Brent, I’ve got a set of tables that have a non-clustered PK (long story). I saw in your index tuning class that you don’t need to have the ID in the index normally. Is that also the case with a non-clustered PK? Thanks!

No.

CraigUK: I assume that paramaterised Dynamic SQL is prone to param sniffing? Any benefit to concatentaing the variable to build SQL that contains a hardcoded string literal and have a plan per value? Assuming that Forced Paramaterisation is not on. Or would that lead to too many plans?

Read.

Better than Ezra: Any new SQL 2022 books you look forward to reading?

Yes.

DBAbyDefault: Big fan, thanks for answering these questions Brent! My friend is wondering how much of a difference going from a default setting of 512 byte sectors on data/log/tempdb drives to the recommended 64k sectors? Is it work the effort to shuffle data around and redo everything?

No.

Ezrah: Should DBAs embrace the awesomeness of Ottertune or fear being made obsolete by it?

Yes.

Anatoli: Do you have any recommended books on the origins / history / evolution of relational databases?

No.

Efraim: SQL Sever 2022 not announced at Microsoft Ignite, should we be concerned?

Why?

I’m a potato: Any word of advice against indexing views?
Haydar: When addressing non-sargable queries, what are the pros / cons of materialized – indexed views?

Watch.

Yitzchak: Before accepting a new VM from sysadmin for a new SQL installation, what Network and Disk checks should SQL DBA’s perform?

Read.

Mikail_Tal: Brent! Have you or someone you know ever switched jobs to work with a particular technology?

Yes.

Blurred_Lines_on_Docs: On the official Microsoft documentation I found a query for Azure SQL Database that allows to pull data from sys.resource_stats and find AVG and MAX CPU, IO, Log Write, Sessions and Workers… but what are Sessions and Workers?

Really?

FrankieG: After 20+ years in the MSSQL space the more I feel like there is no clear answer to the majority of the problems I’m tasked to resolve. One could argue that this is due to the advanced features etc but frankly I feel that it’s just making it less attractive as a product.

Okay.

[Video] Office Hours Live: Ask Me Anything

Last Updated October 3, 2022

Got a question for me? Post your questions at https://pollgab.com/room/brento and upvote the ones you’d like to see me discuss. Before heading to NYC for vacation, I recorded a live Office Hours on my Twitch channel.

Here’s what we covered:

00:00 Start
04:40 neil: are there any ‘band aids’ a DBA can apply for a third-party application that deadlocks all the time or is it up to the vendor of the application? maybe maxdop=1 or just tweak some indexes & general performance tuning and hope for the best? 06:58 Yehudah: What is the best way to track down the top queries that are experiencing ASYNC_NETWORK_IO waits? Did not see this as an option in sp_blitzcache source.
07:21 Youssef: For your 5×5 NC index guideline, do you have a total byte limit you try to keep the include fields under?
07:54 Remko van Hierden: Hi Brent, BI dev here. New server is slower than the old one. Supplier does not seem to be able to pinpoint it. I adviced management to hire DBA consultant. SP_Blitz says ‘Slow Storage Writes on Drive..’, could this be due to format with allocation blok size 4kb? Other reasons?
08:45 Malachi: What are your recommended minimum network speeds for corresponding SQL Batch Requests Per Second rates of 1,000, 3,000, 20,000 batch requests per second?
10:09 Yehudah: What do you estimate the percentages are for your students that pay for Ozar training via their company vs out of their own pocket?
11:16 MyFriend: Hi Brent. Do you consider an Uptime of 1 whole business day, too small? In the context of making reliable DEATH method decisions on a production DB based on sp_BlitzIndex @BringThePain=1 results. What would you consider to be your optimal/minimum Uptime?
13:19 DeferredNamesCauseProblems: Hi Brent! I am looking for a query to find all procedures that have deferred name resolution, ie the messages that appear when I issue sp_refreshsqlmodule (the messages come out informational). I want to capture those for resolution, but do not know how to do it all in TSQL.
14:48 Leszek: Hi, Is there (since SQL server 2016) reliable, with good performance feature to do auditing – data changes (table name, column name, value before, value after change) ? Or better do it manually in code?
17:27 Youssef: Hi Brent, I’ve been introduced to SQL at 18, I’m 22 now with a Senior Production/Development DBA fulltime position and I’ve somehow kickstarted a consulting career in my city, consulting for hospitals, banks, Insurance companies and NGOs. how can I move up higher & go global?
19:12 Don’t blame Anthony: Back in your DBA days, how did you handle requests to delete old data and requests to delete old DB’s?
19:39 YouGottaDoWhatYouGottaDo: Hi Brent, as a consultant have you ever found performance issues related to Change Data Capture (I think about Tlog growth or DB in Availability Group) where you had your hands tied? How did you manage those cases?
20:29 Uncle Leo: Do you have any clients that do poor man’s log shipping where they do log backups but have their own code to do the copy/restores? When is poor man’s log shipping a better fit over true log shipping?
22:14 Stefano: Hi, is there any performance or “less-blocking” benefit to query a SNAPSHOT of an AlwaysOn replicated database, instead of the replicated database? Are queries to snapshot databases generally less blocking, even though most of the data remains in the main database data files?
23:21 StillLearning: Hello Brent, I must execute long running queries on Azure SQL DB (migration scripts, update jobs) with concurrent OLTP queries. As Azure SQL DB doesn’t have the Resource Governor feature, are there tips to write resource friendly SQL script to workaround that ? Thanks.
24:31 Cassian: What is your opinion of memory optimized tempDB metadata in SQL 2019/22? Is it safe to enable?
26:07 Yedidyah: Do you have a recommended way to easily migrate SQL Server 2019 from one Azure Windows VM to a newer more powerful Azure VM?
26:39 Desi: Is it better to use SET NOCOUNT ON in stored procedures?
26:59 Yedidyah: Is it easy or difficult to to learn PostgreSQL if you already know SQL Server?
28:04 Brasso: What are your thoughts on using SQL Server to query Parquet, CSV, and JSON files in S3 or ADLS?
30:23 JasonEverill: We have a SQL 2008 R2 instance and I’m trying everything within my power to get the organisation to move to 2019 (I’m a web developer). What are the best resources to allow me to do this? We currently don’t have a DBA (surprise surprise) – should this be our first task?

#tsql2sday: Start Your Dynamic SQL with a Comment.

Last Updated October 7, 2022

T-SQL

When you write dynamic SQL, start like this:

CREATE OR ALTER PROC dbo.MyProc AS
BEGIN
	DECLARE @StringToExecute NVARCHAR(4000);
	SET @StringToExecute = N'SELECT  /* MyProc */ '; 
...

CREATE OR ALTER PROC dbo.MyProc AS

BEGIN

DECLARE @StringToExecute NVARCHAR(4000);

SET @StringToExecute = N'SELECT /* MyProc */ ';

...

Right after the SELECT (or INSERT or UPDATE or whatever), immediately put a comment – using /*, of course, because you’re not a terrible person.

That way, when you’re looking at the plan cache or monitoring tools, you can see what generated the dynamic SQL, and where you need to go if you need to performance tune or fix it.

You can’t put the comment at the very beginning because SQL Server tracks the exact point at which your query begins, and that’s what shows up in most plan cache or monitoring tools. If you put the comment immediately after the first word of the query, however, it’s easy to spot the query’s source.

This becomes even more important in SQL Server 2022 because of the PSPO implementation of cached plans.

This Week, Fundamentals of Query Tuning Classes are Free!

Last Updated October 9, 2022

Execution Plans

Every weekday during the month of October, one of the modules from my Fundamentals classes will be absolutely free to watch. But you gotta watch it that day – you snooze, you lose.

This week is Fundamentals of Query Tuning. Last week’s class had hands-on labs you had to follow, but this week’s doesn’t. You’re welcome to follow along with the demos, though, and if you want to do that, you’ll need the same setup as last week: SQL Server 2017 or newer, with the most recent Cumulative Update, the most recent SQL Server Management Studio, and the 50GB Stack Overflow 2013 database (10GB 7z file, extracts with 7-zip.)

Oct 10 – Building a Query Plan
Oct 11 – How to Find the Right Queries to Tune
Oct 12 – How Parameters Influence Cached Plans
Oct 13 – Improving Cardinality Estimation Accuracy
Oct 14 – Common T-SQL Antipatterns

Make sure to keep up – next week, we’ve got Fundamentals of Columnstore coming fast & furious.

These are recordings of live classes. In the live class, the questions & chat take place over Slack. You don’t need to join the Slack – I only monitor that chat room during the live classes. (I’m on vacation in New York at the moment, heh.)

[Video] Office Hours Silent Edition

Last Updated September 29, 2022

Okay, I, uh, forgot to unmute the microphone for this one:

So here are the answers in text form instead. <sigh> Thank goodness it was a speed round!

Mike: When Brent Ozar’s certification exams for SQL Server will be available? Looking forward to having exams like “Fundamentals of QT”, “Mastering Server Tuning” etc, so you can pass in the testing center. 1st cert. exams on performance tuning for SQL Server in the world. When?

Never. I don’t wanna write exams.

RoastMe: Hi Brent. You have earned the reputation for roasting people for asking certain questions with talent and entertainment. Do you think some will ask a question to get a roast? Do you think people watch just for the roasting?

I think some people do, but I think the purposely trolling questions don’t get that many upvotes.

MyFriendConsulting: Hi Brent, In consulting, do you recommend doing all the business/clients and technical work myself at the beginning (say first 1-2 years) or do you recommend hiring someone (maybe part-time) to meet clients and handle the business part?

Don’t hire people until you already have more incoming business than you can handle.

Mike: What do you think will be the consequence of events, if Microsoft releases SQL Server 2022 with PSPO implementation as it is in its current form ?

People who need monitoring won’t use 2022 compat level.

Index_It_All: Hello Brent, We are looking to determine how to classify all our SQL Servers into small, medium,Large and Very Large. We want a default template so we can onboard clients databases. What metric would you use to measure what should be a small, large or very large client Database?

Will Marshall: How do you decide when a given a single stored proc should be refactored into multiple child stored procs?

We cover that in my Mastering Parameter Sniffing class.

Yourbiggestfan: Hi Brent, I know you have spoken about SQL22 changes, but the ‘Percentile and persist mode mem grant fdbck’ improvment scares me as MS have smartly overcome issues with Mem grant fdbck (where it only relied upon last exec). I fear avg DBA would no longer be needed in 10 yrs time.

If you’re content with being average at something, then yes, you’ll find that you’re probably not going to be needed no matter what your job title is.

Mehmet: What are your thoughts on the use of surrogate keys vs natural keys in new SQL tables?

I don’t think about that. Get this book.

Espen Eriksmoen Løke: Hi, I have been working with SQL Server query tuning for many years and think I am quite skilled. I would like to go to classroom training for and advanced course both to sum up and learn new things. Tired of online courses and Teams 🙂 Something to recommend?

Ralph: I love your simple explanations, e.g. recently “what’s fragmentation” at SQLBits! Do you have anything like that for how LOB data is handled and how it affects performance if we use e.g. nvarchar(max) extensively?

Yes, watch my free How to Think Like the Engine course.

Don’t blame Anthony: How did you handle reviews for new SQL code back in the day?

I didn’t. Companies are rarely staffed well enough that people can review every new line of code. Instead, focus on the top resource-consuming queries.

Gang of DBAs: Is there a resource like the Gang of Four Design Patterns book for Data Modeling? The data model is such an important piece of my development, but I always struggle and feel like there must be people who’ve solved these issues before.

I don’t even know what that is.

Who’s Hiring in the Microsoft Data Platform Community? October 2022 Edition

Last Updated September 18, 2022

Who's Hiring

Is your company hiring for a database position as of October 2022? Do you wanna work with the kinds of people who read this blog? Let’s set up some rapid networking here. If your company is hiring, leave a comment.

The rules:

Your comment must include the job title, and either a link to the full job description, or the text of it. It doesn’t have to be a SQL Server DBA job, but it does have to be related to databases. (We get a pretty broad readership here – it can be any database.)
An email address to send resumes, or a link to the application process – if I were you, I’d put an email address because you may want to know that applicants are readers here, because they might be more qualified than the applicants you regularly get.
Please state the location and include REMOTE and/or VISA when that sort of candidate is welcome. When remote work is not an option, include ONSITE.
Please only post if you personally are part of the hiring company—no recruiting firms or job boards. Only one post per company. If it isn’t a household name, please explain what your company does.
Commenters: please don’t reply to job posts to complain about something. It’s off topic here.
Readers: please only email if you are personally interested in the job.

If your comment isn’t relevant or smells fishy, I’ll delete it. If you have questions about why your comment got deleted, or how to maximize the effectiveness of your comment, contact me.

Each month, I publish a new post in the Who’s Hiring category here so y’all can get the latest opportunities.

Erik Darling’s Month of Free Tools Training

Last Updated October 3, 2022