[Video] Office Hours: Professional Development Edition

Videos
0

I spent an entire video just talking about 3 questions y’all posted to https://pollgab.com/room/brento because these were pretty big-picture and important.

I know it sounds goofy and sentimental, but I genuinely care about y’all’s careers, and I want you to work as little as practical in order to spend as much time with your loved ones as possible. So here we go:

What we covered:

  • Kinneret: When are work communications appropriate for a chat channel such as Slack? When should those communications take place via direct audible communications (phone, webex, etc)?
  • WhatsUpDocs: Hi Brent, have you ever needed to look at business documentation (check business rules/logic) when consulting or as an employee, but it was severely lacking? Recently joined a different team in work and trying to find simple answers to questions is an uphill struggle…
  • Roy: Hi Brent, How did you manged to upskill yourself early in your career with a busy full time job? What recommendations will you give to somebody early in his DBA career?

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

Who's Hiring
14 Comments

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

The rules:

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

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

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


Office Hours Speed Round: Text Edition

Some questions that come in at https://pollgab.com/room/brento have such straightforward answers that I don’t need to cover them on video. It’s time for a rapid-fire speed round:

Q: Uncle Buck: Is there a good way to tell if a given include column for a NC index is being used at all?

No.

Q: Steve E: Hi Brent, In the past you have shared links to a “reading list” tool you used to bookmark various online articles and share them with others,what was the tool please? I’d like to bookmark the various articles I’ve read over the years and be able to easily share those with others

For bookmarks I use Pinboard, and here are mine. For RSS feeds and newsletters I use Feedly, and here are my shared items.

Q: FrankieG: When faced with hundreds, if not thousands of NonSargable predicates over a wide range of solutions/products that have been in production for years in a huge base of legacy code how to begin to “fix” the problem?

Use sp_BlitzFirst @SinceStartup = 1 to find your server’s top wait types, and use sp_BlitzCache to find the top queries causing those wait types. I explain more about that process in my How I Use the First Responder Kit class.

Q: Faruk: Is it a fair expectation for employers to expect DBA’s to also write reports (SSRS, Power BI, etc)?

Generally, development and administration are separate job roles.

Q: Luis: Any performance gotcha’s to watch out for when the query plan is using the Filter operator to satisfy a non-sargable search predicate?

If you understand enough to write the question, then you already understand the answer. Performance will be bad.

Q: Last Action Hero: What are your thougths about contained databases?

I explained the problems with ’em here in 2009: part 1, part 2, and part 3. As I predicted, most DBAs have still never seen these in the wild, nor do I expect them to in the near term.

Q: Jose Luis: I need to migrate my existing DWH in SQL2012 to a new OnPremises SQL 2019. Should I consider AlwaysON for it? maybe SQL Failover Cluster? Or just a BIG Standalone Server? Most of my DBs are in Single Recovery Model.

For personalized architecture planning, hire me for consulting.

Q: Reed Richards: What is the best resource for extended events training?

Start with Jonathan Kehayias’ series 31 Days of Extended Events.

Q: Punxsutawney Phil: I thought that when you refresh a database on a nightly schedule for reporting/production use, that’s considered a “groundhog day” method and should be avoided. Yet, I’ve seen you twice in recent months recommend that as a solution to refresh production reporting databases.

Groundhog Day refers to the practice of reloading data via logged methods: inserts, updates, deletes, SSIS, etc. Restoring a backup is not a logged method.

Q: Leon Spangenberg: I keep seeing trace flag 3604 being turned on and off in my sql log. Why would someone do this?

That is detailed here.

Q: Max_Null :A very small table in Azure SQL used to keep track of locking in the application (~15 columns, no triggers) and has no more than 3 rows at any given time. Other than stale stats and blocking, is there any other reason that this table would be slow for a simple SELECT?

If you’re trying to track locks in a table, you’re going to hit locking problems, period, full stop, end of story. Bad design idea. Do something different.

Q: Alan: Do you think cheap cloud storage, like s3, will eventually replace old fashioned tape libraries and off-site backups?

For many of my clients, it already has.

Q: FrankieG: Hi Brent, can you talk about and explain why upgrading to the rewritten cardinality estimator (i.e. upgrading SQL Server from v2012 to v2016) causes serious performance degradation and the only way around it (that I’m aware of) is to force using the legacy estimator in settings?

Yes, I cover that and the alternatives in my Mastering Query Tuning and Mastering Parameter Sniffing classes.

Eyes up here, kidQ: Debbie Downer: Does Stack Overflow db have more downvotes than upvotes? If so, why?

You can download the database here for free to find out.

Q: Stan Sitwell: Is PostgreSQL consulting as opportune and lucrative as SQL Server consulting?

I have no idea – I don’t consult on PostgreSQL.

Q: Pina: Should I drop temp tables at the end of my stored procedures? What if there is one stored procedure that calls a bunch of other stored procedures, each of which has several to many potentially large temp tables? Is there a performance gain to dropping them? A performance loss?

I cover this in my Fundamentals of TempDB class.

Q: Ravi Pratap Singh: what are the books need to cover for good SQL understanding

Check out this list.

Q: Does Basically Anything: Hi Brent! When looking to tweak MAXDOP from a “sane default” to the best setting for my workload, what metrics should I pay most attention to as I try different MAXDOP settings to test the performance difference?

I cover that in the parallelism module of the Mastering Server Tuning class.

Q: DGW in OKC: Have you ever used encrypted connections (like SSL) to a SQL instance? Is there ever a reason one might be required to do this?

No, but I’ve heard of security teams requiring it in some cases.

Q: Need4Speed: A friend asked if there was a way to bring a detached database back online if you are missing one of the three data files.

Detaching a database is dumb. Deleting a database is dumb. If someone did *two* dumb things to a database, I’d say you know what, I can’t trust those files. Let’s restore from backup. If that person tried to fight me, I’d say, “You’re the one who did two dumb things already – how’s about you step away from the keyboard and cool off for a while, and let the pros take over?”

Q: Kirk Saunders: Are there situations where you would use a RIGHT OUTER JOIN? I have found I can put that table higher in the JOIN structure and LEFT OUTER instead. I just don’t want to miss out on a solution option if there is a real strong use case for it.

Me personally, I’ve never used it.

Q: Have you tried turning it off and on: We want to implement RCSI on a (10k batches/sec / 4.5TB / 7K session) server to help reduce blocking. What QA do you suggest, and any books/videos to consult?

Check out the isolation levels module in my Mastering Server Tuning class.

Q: KIRBY W BURKHOLDER: While watching a recording of “Mastering Query Tuning” recently, I remember you mentioning a problem the CE has with “Ford” and “Mustang”. My friend has the problem but all Fords are Mustangs and all Mustangs are Fords. Vendor supplied code that he can’t change. Any suggestion

Leave a comment on the training class module. There’s space for longer questions in there, including PasteThePlan options to include the execution plan, so I can see the exact problem you’re talking about.

Q: George: What is your favorite way to measure storage speed in Azure SQL?

The fast/easy storage test is CrystalDiskMark. It’s not completely definitive – it’s just a hot-or-not test – but in most cases, the storage is nooooot hot, so I don’t need a definitive test.

Q: Neil: We always used default instances. Planning on a multi-instance server (in test) to host multiple web environments with copies of the same db’s on each instance. What are some ‘gotchas’ to look out for? I’d probably give each instance its own data/log folder names, anything else?

The term you’re looking for is instance stacking.

Q: Doug E: Do you ever bother with reconciling AWS / Azure bills to actual resource usage?

Yes, we’ve done it for SQL ConstantCare’s own back end during performance tuning.

Q: SQL Serenader: Hi Brent, We have seen an uptick in dropped connections. Do you think using the deprecated SQL Native Client 11 driver, instead of the more connection resilient ODBC 17 or OLEDB 18 drivers could have anything to do with our connection issues?

I have no idea.

Q: SeeCoolGuy: what is your opinion if you find your colleague forcing the engine join hint to use a hash join vs letting the query engine find it’s own join criteria?

That colleague might have attended my Mastering Parameter Sniffing class where I cover hint usage.

Q: Philip: I’m getting a warning against an Azure SQL DB saying “Non-parameterized queries are causing performance issues” with Azure wanting to turn on ALTER DATABASE Name SET PARAMETERIZATION FORCED. Are you able to talk about this a little, and how you would go about investigating?

I assume you’re getting that warning in sp_BlitzCache or sp_Blitz. Copy the URL into your browser and read that. I’ve written extensively there on how to fix it.

Q: Enzo: Hi Brent, As you like fast cars, will you be attending the first F1 race in your home town next year (Las Vegas 2023)?

Yes, if I can get good tickets.

Q: Roadtripping fool.: Any chance you can make these into a podcast like your old office hours? I used to make my family listen to them on road trips. Ah memories.

No, it took time and cost money.

Q: Michael Devor: Hi Brent, My friend would like to know if there are any special considerations or concerns when tuning indexes on a partitioned table?

Yes. Have your friend attend my Mastering Index Tuning class.

Q: SQL Crooner: What is your opinion of the Parquet file format?

I have no opinion. Never used it, never opened it. Nothing against it.

Q: Festus: What percent of the time do you see customer tables where the primary key / clustered index are on same vs different columns?

Way, way, way less than 1%.

Q: prasad: What r the steps we should take to learn sql in depth?

Use it, help other people solve their problems with it, and then teach it.

Q: Eric S: How many years before our AI overlords displace human DBA’s?

OtterTune is already working towards that for MySQL and PostgreSQL, and I’ll be excitedly watching their progress. I think it’s a cool problem. Given Microsoft’s lack of progress on the automatic indexing front, and given how little of that is coming in SQL Server 2022, I think our jobs are still safe for a long, long time.

Q: SQL_Ninja: Hi Brent, what is your thoughts about SQL server vulnerability assessment functionality or would you recommend any other third party software for similar assessment ?

I don’t get the chance to try every tool out there, so I’m not really qualified to give a good review.

Q: cloudy: Hi Brent, we are currently use on-prem sql server for dwh & reporting, my company is starting to run some workload on aws, what’s your take on migration to Amazon Aurora (we can change the application to support it)?

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

Q: Anatoli: What is the best cloud database technology / brand for a shop that has plenty of developers but doesn’t want a DBA?

Use the one most of your developers are most familiar with. That’ll result in faster delivery to customers.

Q: Joe: Hi Brent, Under what circumstances would you set Min SQL Memory to anything other than zero?

None, because I hate instance stacking.

Q: Maksim: What is your opinion of disabling the guest and SA users in SQL Server?

I don’t really care. My bigger issue is usually shared passwords, like app passwords, and disabling accounts doesn’t fix that.

Q: Haydar: Do you foresee any DB company acquisition / buyouts on the horizon? What company takeover is the odd’s on favorite?

I don’t track the business side of the industry.

Q: Gary: Hi Brent. What’s your take on the number of instances one Production DBA should be responsible for? Automation helps and it’s rare everything breaks or needs service at the same time, but it can be a burden for one employee to manage 200+ instances. Thanks!

See this post.

Q: Joel: Do you ever look at sys.dm_os_schedulers for performance related troubleshooting?

Yes. We talk about it in my Mastering Server Tuning class.

Q: ?akir: What is your favorite tool / technique for monitoring failed SQL agent jobs?

Alerts.

Q: Gözde: How often should we back up SQL agent job definitions?

Back up your system databases daily. Jobs are stored in the msdb database.

Q: Ronaldo: Is tech documentation reading a lost art? If so, why?

Yes. Words are great for searchability, but videos are often easier for beginners to approach. Thus the rise in just-in-time learning: people searching for something, finding a 5-minute YouTube video on it, watching it while doing the thing, and then discarding the video, and never really learning the technique. I’m fine with that. It works for beginners.

Q: Leon Spangenberg: Hello Brent. I am inserting row into an existing table. However when ever I do the insert with a predefined set of filter the insert just does not work. I don’t get an error its just like sql sits there and does nothing. Any advice?

Use sp_WhoIsActive to see what it’s waiting on.

Q: Brent: Why are so many of these answers pointing people towards a training class?

Because the answers are 30-60 minutes long, and that’s why I teach classes. I love these questions, don’t get me wrong – but I love them so much that I’ve built entire presentations to do justice to their answers.


I’m teaching Mastering Parameter Sniffing at the PASS Summit!

#SQLPass
2 Comments

At the PASS Data Community Summit in Seattle on November 12th, I’m teaching a one-day pre-conference workshop.

This year, the Summit is 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’s the abstract for the workshop I’m teaching on 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.

This course is 100% demos: the only slides are the introduction and recap. The rest of the time, I’ll be working live in SQL Server Management Studio and SQL Server 2022. You can even follow along in your laptop if you bring SQL Server 2017 or newer and the 50GB Stack Overflow 2013 database.

We’re going to be moving quickly and covering a lot of ground, and I know it can be tough to keep up. That’s why 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.)

Registration is open now with early bird pricing. See you there!


Breaking News: SQL 2019 CU16 Changes Backup Formats, Can Break Log Shipping.

The newest Cumulative Update for SQL Server 2019 has this gem in the release notes:

It’s not a known issue, it’s an improvement

This is a massive problem if:

  • You have databases encrypted with TDE
  • You take backups WITH COMPRESSION (which has a history of bugs already), or you have the server’s default compression option turned on
  • You’re doing log shipping to other 2019 servers
  • You patch the primary to CU16 before you patch the secondaries

You might say, “Well, you should always patch the secondaries first” – but in some shops, here’s how they approach patching:

  • Patch the secondary (Server2)
  • Fail over to the patched secondary (Server2) but do not patch the former primary (Server1) just yet, because you’re worried about something going wrong in the patching
  • Wait a few hours or days before patching Server1

At that point, the unpatched Server1 is the secondary – but it can no longer restore backups because of this “feature” of CU16. (That’s exactly how I discovered this issue – a client’s log shipping was broken when they had problems with CU16 and tried to fail back to Server1 – but they couldn’t. CU16 is a one-way trip if you’re using log shipping.)

The “feature” is detailed in this KB article, which says:

In my client’s case, to get back to the CU15 servers quickly, we had to take uncompressed full & log backups from the CU16 primary over to the CU15 server, and were then able to go live on CU15 again. (I wasn’t involved in troubleshooting the issues they had with CU16 that drove them to fall back to CU15.)


Designing a Data Model for Gender and Sexuality (Oh And Also, I’m Pansexual)

Personal
360 Comments

These days, BrentOzar.com mostly focuses on the Microsoft data platform. However, you and I have a relationship, dear reader, so I just need to talk about some personal stuff today.

No, I'm not listening to YMCA.Lemme get two things out of the way first:

  • I’m pansexual, and
  • From a data modeling perspective, that’s different from bisexuality

I totally understand if you’re breathing into a paper bag right now, freaked out because that’s a lot to take in. You can close this blog post and come back to it later, or even just close it, hahaha – I understand that it’s a challenging topic.

But if you wanna learn more about me and data modeling, let’s dig in.

Let’s design a data model
for gender and sexuality.

Most of us are aware of two genders: male and female. In terms of data modeling, we might think of it as a bit column, IsFemale. 0 would be male, 1 would be female.

Sure sure, you could do IsMale, and have 0 be female, 1 be male. I’m fine with either definition – I swing both ways. (Brace yourself – the jokes only get worse.) So:

Then if gender is a bit column, then defining sexuality is pretty easy too. We can’t do it with just a bit flag, but here are the four possibilities:

  • Heterosexual: someone is attracted to the “opposite” gender – for example, if you’re a man, you’re attracted to women
  • Homosexual: attracted to the “same” gender – for example, if you’re a man, you’re attracted to other men
  • Bisexual: attracted to both men and women
  • Asexual: not attracted to either (listen, there are days when I want y’all to all die in a fire, especially the days when I read the comments)

We might design it as:

Most data modelers would stop there, and I would understand why. That’s all most of us have been exposed to. If you’re nodding along, that’s fine – you would define me as IsFemale = 0, SexualPreference = Bisexual.

I would totally understand if you said, “Ugh, Brent, this is way too much information,” and you closed the blog post here. I get it. This is an uncomfortable topic. But if you wanna learn about why bit columns don’t work for genders, then we need to keep going.

Gender isn’t really a bit flag.

In about 0.02% to 0.05% of births, the person is born with ambiguous genitals.

I know some readers are gonna rage-quit right there, and I understand. It’s a touchy topic. (No pun intended. (Okay, maybe the pun was intended.)) You’re welcome to do your own research on the intersex topic.

But if we agree that there are any births where it isn’t black-and-white which gender someone is at birth, that opens up a data modeling problem. A simple bit column isn’t going to be enough, because there’s more than just male and female. Gender is non-binary.

Furthermore, as people age, they may find that the bit value they were assigned at birth is wrong. The truth is, your biological sex isn’t carved in stone, but a living system with the potential for change. (I stole that sentence from this Scientific American piece, HT Andy Mallon, and I have so much respect for transgender folks who have to deal with this struggle.)

As a data modeler, it’s not my job to lay out every possible option for gender. I just need to be aware that a bit column isn’t going to cut it, and I’m going to need a lookup table along the lines of:

For the purposes of this blog post, I don’t give a damn what’s in the table. I don’t even care if you define it as:

Even if those were the only 3 options, that’s clear enough that a bit flag isn’t going to cut it.

So if a bit flag isn’t going to cut it, then we’re gonna need more than 4 options for sexual preferences.

Our 4 starting sexual preferences
are still a thing, but there are more.

All of these are still valid:

  1. Heterosexual can still mean men who are attracted to women, and women who are attracted to men. That’s fine.
  2. Homosexual can still mean men who are attracted to men, and women who are attracted to women.
  3. Asexual can still mean people who aren’t sexually attracted to anyone else.
  4. Bisexual can still mean men attracted to men & women, and women attracted to women & men.

But we’re gonna need more definitions, too, because people who are bisexual aren’t necessarily attracted to people whose gender is in the grey area. We need something like bisexual, but that also means any gender is okay, not just male or female.

That’s pansexual: people who can be sexually attracted to anyone else regardless of their gender – not just men or women, but people who are in the grey area. (I know, before you read this post, you thought bisexual people were open to a lot! Turns out there’s a world in which bisexual people are considered picky, hahaha.)

There are more sexual preferences than that, too. My goal in this post isn’t to define every possible sexual preference – it’s just to remind you that gender is not a bit flag, and the table for sexual preferences has more rows than you might consider at first glance.

I’m pansexual:
gender isn’t a factor for me.

That sounds like I have really low standards, hahaha, like I’m attracted to everything that moves. That couldn’t be further from the truth: I am very, very picky, but I just don’t consider gender as part of the criteria that I look for. I’ve met sexy women, men, transgender folks, and nonbinary folks.

Right now, I’m in a relationship with someone non-binary who was born a man, but is comfortable presenting as either a woman or a man, depending on what they feel like doing that day. Most of the time, they present as a woman.

You might be asking yourself, “Wait, Brent – is that your wife?” No, my wife and I got divorced when we returned from Iceland in 2021.

I’m blogging about my sexuality
for 3 reasons.

First, I wanna be here for folks who are surprised by this. Right now in politics, there’s a lot of divisiveness, a lot of us-versus-them. I know some of my readers are going to be shocked that I’m one of “them”, the sexual deviants who have some kind of agenda. It’s okay – I understand if you’re shocked, and I’m here if you want to talk about it. You can ask me anything you want publicly or privately, and not feel dumb or guilty.

Second, I wanna be here for folks who feel seen. Right now, non-binary, transgender, and pansexual people are reading this blog post going, “OMG, I’m not alone in this industry.” If there’s anything I can help you with, please don’t hesitate to reach out.

Third, because I can. I’m blessed enough to be at the point in my career where I can talk publicly about this stuff without fear of retribution. Oh, retribution is going to happen, for sure – there are readers right now going, “Screw this guy, I’m unsubscribing from this craziness.” That’s fine. I will survive.

But don’t worry,
this blog is still about data.

I don’t want you to think that I’m constantly going to be pushing this in your face, dear reader. The blog is still going to be about databases, and my social media feeds are still going to be about cars and travel and food.

No, I’m not going to hit on you at a conference or a user group. I’ve never done that before, and I’m not about to start now. If you’re worried that I’m looking at you with some kind of ulterior motive, relax. You’re not my type. I’m specifically talking to you, not the other readers here.

The eagle-eyed will notice that I try to use gender-neutral names and terminologies as often as I can. I try to sneak in little fun stories and lessons where it makes sense, and I’m sure I’ll start working in more subtle stuff in demos.

But this blog isn’t going to become some kind of pansexual activism point. The biggest reason is simple: the pansexual pride flag is ugly.

C’mon, seriously, it’s as if someone said, “These pansexuals say they’re okay with anything? Well, let’s see how they feel about THIS, ha ha ho ho!” Ugh.

I know this is complex.
Feel free to ask me questions.

If you want to know more about me, gender in general, or sexuality in general, you’re welcome to post comments here or email me directly at brento@brentozar.com. You can also check social media today because May 24th is #PansexualVisibilityDay.

I know this topic is very different than the ones we usually discuss here. I will make sure the comments section is a place for honest discussion and learning, free from trolls or abuse.

Update: wanna know what
kinds of messages I got for this?

After publishing this blog post, here’s an example message I got on LinkedIn:

Part of me is honored that y’all think I’m professional.

The other part of me is completely unprofessional, and is surprised that it took you so long to find out.

As a reminder: BrentOzar.com is my blog, not yours. I’m here for a good time. If you’re not here for that, it’s best if you unsubscribe.


Never been to a SQL Server training class? Let’s fix that.

Company News
4 Comments

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

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

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

You’re ready for Fundamentals Week. Next month, you’ll spend a week with me learning about indexes, query tuning, columnstore, how I use the First Responder Kit, and more. I’m teaching it two weeks in a row:

June 27-July 1: Fundamentals Week, US-friendly times:

July 4-8: Fundamentals Week, Europe-friendly times:

Bonus: live Fundamentals of TempDB on June 9 & 10 – if you buy your Fundamentals Week ticket quickly, I’ll let you sneak into this class too!

Registration is open now. If you can’t make the live classes, like if you have an emergency at work, there’s a new option during check-out – you can add on lifetime access to the recordings, too. See you in class!


[Video] Office Hours: Cabo Balcony Edition

Videos
1 Comment

Post your questions at https://pollgab.com/room/brento and upvote the questions you’d like to see me discuss.

The light balance on this video is all over the place, getting brighter and darker constantly, so you might wanna leave this one playing in the background and just listen to it rather than watch.

Here’s what we covered:

  • 00:00 Introductions
  • 00:32 SQL Crooner: What are the tell-tale signs that your SQL Server has been granted too much memory and the OS not enough?
  • 02:14 Gülnaz: Is ‘SELECT *’ ok or discouraged when used with ‘IF NOT EXISTS…’ ?
  • 03:27 Shingen Yashida: Is table partitioning largely unnecessary since modern SANs can move data to hot / cold storage tiers?
  • 04:14 sp_azuresql: Hi Brent – What is the best cut-over strategy in PROD migration scenarios from SQL Server AG to Azure SQL in terms of blocking the incoming traffic (if any) like disabling logins etc?
  • 05:53 TurnerBurn: Hi Brent. My company moved a database from a physical SQL 2016 SE cluster to a VM; same SQL ver. VM has fewer cores (24 vs 32) but processing power on the VM is better & memory is the same. I now see many, more deadlocks reported from sp_BlitzLock on the VM vs the physical box.
  • 07:38 Noam: On a scale of 1-10, how would you rate the online Microsoft SQL Server Documentation and the online PostgreSQL documentation?
  • 09:20 Piotr Rasputin: What is your opinion of DP-900 Azure Data Fundamentals certification?
  • 10:42 Q-Ent: Hi Brent, is there any scenario where enabling RCSI on a database that uses the default isolation level to turn your data into trash?
  • 11:30 Prohiller: Hi, can we somehow predict % decrease of overall CPU usage of a server when optimizing a query? Given that we know difference of CPU time of current and optimized query and number of executions per hour/day? I’m looking for additional KPIs when selling optimization to Business.
  • 12:37 MyFriendtheDBA: My friend has a table with 450,000,000 rows on a production server. They want to know the lease disruptive way to index this table.
  • 15:10 Yoshiya: What is your opinion of Azure Synapse? Does it compete well with Databricks and Snowflake?
  • 15:18 SQL Crooner: What are your top SQL best practices that you wish more clients would follow?

[Video] Office Hours: Cabo Pool Edition

Videos
0

You posted SQL Server  questions and upvoted the ones you’d like to see at https://pollgab.com/room/brento, and before the Cabo Clasico finals start for the day, I sat by the pool to talk through ’em:

Here’s what we covered:

  • 00:00 Introductions
  • 00:53 Neil: Does putting up a giant monitor on the wall with SQL Server info like CPU %, Disk activity, blocked sessions, top queries, etc, so non-DBA’s and Executives can see the server status sound like a good idea or is that just inviting trouble?
  • 03:07 Omer: What are the possible causes for sp_whoisactive to show a SPID with a long running INSERT statement (non blocked) but the SPID is in a sleeping state?
  • 03:42 Theo: Does the order of include columns ever matter for a non-clustered index?
  • 03:53 Uncle Leo: What are the pros/cons of using third party SQL backup software over native SQL backup?
  • 05:05 Mehdi: Hi Brent! Auto-update stats is enabled on a database. Does the database need up-to-date statistics with the maintenance plan?
  • 06:07 DBA_Mufasa: Hi Brent! What’s your best approach for tracking tables usage in SQL databases, in order to find tables that haven’t been touched (for eg. no select, updates, or inserts ) for a a defined amount of time. Considering that table statistics get cleared after server restart.
  • 07:49 default blame acceptor: Hi Brent, could you advise a KPI that I can show not-technical folks to convince them that SQL is not a root cause of the application slowness (when it seems that it is true!) and they should look for a solution somewhere else instead of convincing me to restart SQL’s VM…
  • 09:03 Yousef: How do you determine the optimal auto growth size for a given SQL data file running on NVME SAN?
  • 09:47 Ted Striker: What are the pros/cons of running sp_blitzfirst for longer periods of time (@Seconds = 300)? Any maximum value of @Seconds we should stay under?
  • 10:57 Wilma: What is the optimal monitor size / count / configuration for viewing large query plans in SSMS?
  • 11:25 Alain: Hi Brent, what are the advantages of using VMWare stretched cluster instead of AlwaysOn? Thank you
  • 12:56 TeeJay: We’re expecting to move from on-prem (2017 vintage) to azure VMs soon. What should I benchmark on our old servers that’s useful to assure us that the virtual hardware in azure is (at least) not going to leave us with worse performance than we currently have?
  • 13:56 Ain’t Lyin’: Got a tuning problem with a query inner joining some large tables to a table with 0 rows. Optimizer estimates 1 row anyway and does a lot of work before realizing there are no rows to return. I can’t change the query, how do I get the optimizer to figure this out early?
  • 16:29 Hamid: What is the fastest way to split a comma delimited string in in TSQL 2019 / 2014Compat level DB?
  • 17:56 Kfir: What is the maximum size key/include column you would consider adding to a non-clustered index?
  • 18:58 Thomas Franz: How much memory should I reserver for OS / other stuff when I configure the Max Memory settings for servers with 1 or 2 TB (!) RAM. Following the common recommendations I would end with ~256 GB for OS / 1.792 for SQL which seems a bit too much (most servers have much less RAM).
  • 20:55 CKI: Management wants to create a copy of the production database for reporting purposes. The new “reporting” copy should be refreshed nightly with production data. Production database is on AWS Web Edition 45GB. What is easiest way to do it? Thank you very much!
  • 22:04 Gerald Krobath: Is it recommended to install CU updates for MSSQL 2016/2019 automatically via the regular updates or should this always be done manually?
  • 23:47 Sir Galahad: Has OPTION (FAST N) query hint ever got you across the finish line?
  • 23:59 Buck: Have you noticed the annoying issue in SSMS 18.11.1 where it does not maintain your query plan scroll position when you switch off that tab over to another tab then back to the original tab with the originally displayed plan? Is this worth submitting to feedback.azure.com?
  • 27:27 Recap, discussing skimboarding

I’m Speaking at the PASS Data Community Summit This Year!

#SQLPass
3 Comments

For years, the biggest conference in the Microsoft data platform community has been the PASS Summit.

This November 15-18, it’s coming back to the Seattle Convention Center. You can attend in person, or virtually.

I’m going to be there in person. It’s not a decision I take lightly – I still get nervous when I see rooms full of unmasked people. However, I was so overwhelmed with happiness at the in-person SQLBits this spring that I’m ready to give it another shot. (HA! Get it? Booster shot! It’s a joke about… okay never mind.)

This year, I’m not going
to attend a single session.

Not one. 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.

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, and be as friendly as we can while still maintaining good precautions. (No hugs, no shaking hands, no kissing with tongue.)

I can’t talk about my pre-conference session just yet, nor can you register for it. However, now’s a good time to start having conversations with your company management and your significant other to talk about whether maybe an in-person event makes sense this year – or whether you’d rather do it virtually.

In the immortal words of the sisters Pointer, I’m so excited.


The 6-Year-Old T-SQL Book That Never Goes Out of Style

T-SQL
20 Comments

You’ve been working with Microsoft SQL Server for yeeears, and you’re pretty confident that you know how to write a query.

So when I tell you about a book called T-SQL Fundamentals, you’re all, “nah, man, I got this covered.”

You are incorrect.

Sure, the first 5 chapters of the book are about 1-2 table queries, CTEs, subqueries, etc, and you’ve probably got that stuff down cold. But here’s where chapter 6 and 7 go:

  • UNION, INTERSECT, EXCEPT
  • Windowing functions
  • Pivoting data
  • GROUPING SETS, ROLLUP

And subsequent chapters only get more detailed from there: how to properly modify data with transactions in mind, temporal tables, and more.

When you start your 3rd year of working with T-SQL, this is the book your manager should hand you. By that point, you’ve learned enough about querying that you’re getting cocky, and yet there are so many more awesome possibilities available to you.

And if you’re the one who’s the manager – if you have junior folks on your team – then it’s your responsibility to hand Itzik Ben-Gan’s book T-SQL Fundamentals to your team. They’re not getting any smarter on their own, and every day that they don’t read this, they’re producing more inefficient technical debt that you’re going to have to go back and fix later.


Office Hours Speed Round: Text Edition

I recorded an Office Hours stream answering a bunch of your questions quickly, but…I forgot to turn on my microphone, so the video only had the sound effects:

Oops.

So here are the questions in text form instead:

Q: 22DBA: what performance recommendation you see people give all the time but it actually doesn’t work

Lowering fill factor server-wide. They somehow think that making your table larger will make things go faster.

Q: Not the Licensing Police But….: I work for a company that has 18 SQL Servers (mix of Standard and Enterprise) that I know are not licensed. I’ve flagged it and management simply don’t care about them not being licensed, and want to leave as is. “We need to save money” -What would your approach be here?

Put it in writing. Email your manager with a list of SQL Servers, their core count, and the approximate licensing fee. Don’t do it as an accusation, just put in writing that we have this many SQL Servers, and I don’t think we’re licensed for it. It’s then the manager’s problem. If the company doesn’t solve it, then it’s up to you to decide whether you want to keep working for that company – because if they’ll screw Microsoft, they’ll probably screw you when they get a chance, too.

Q: Higgins: Is Disk Queue Length a good metric to monitor for SQL server? If so, what are recommended values?

No. (Good job on the Ferrari-related name though, hahaha.)

Q: Alf: For partitioned tables, do you ever see partition keys on fields other than dates? If so, what are they?

No. I’m sure they’re out there, but I just don’t see ’em.

Q: Wally: When should “DBCC UPDATEUSAGE” be run? Is it expensive like CHECKDB?

After you upgrade SQL Server 2005 to 2008. I haven’t run it in over a decade though.

Q: Keld Rasmussen: Hi Brent, thanks for great posts. When I do select I some times use Read uncommitted because otherwise I lock the database for the application and the application updates fails. – How do I read committed from database without locking for the application? Best regards Keld

Check out RCSI.

Q: NotCloseEnough2RetirementToStopLearning: Hi Brent Any advice on how to move from a production DBA role to a Data Architecture role?

Talk to the data architects at your company to start gradually transitioning into that work. You probably won’t be able to leave your current company and jump to a new company in a data architect role – that jump is too far.

Q: Krishna: How many hours do you sleep and work per day on average?

I usually sleep 8-9pm to 3-4am, plus a 1-hour nap in the afternoon. On the days when I work, it’s usually 8-10 hours, but I just try to work as few days as possible.

Q: .NET User: Hi Brent, Love your office hours, thanks it helps a lot. In previous session someone asked about soft delete and you suggested to add IsDeleted field to the table. I wonder what are your thought about using soft delete vs temporal tables approach? Thanks.

Temporal tables make copies of the entire row as the row changes – that’s a waste of space when you just need soft deletes.

Q: Ezra: The previous CTO convinced all the developers to always use NOLOCK in all their sp’s. How do we change the mindset for this bad practice?

Search for Brent Ozar nolock and you’ll find a ton of videos and demos.

Q: Wally: What is your opinion on using views as constants holders? e.g. CREATE VIEW vStatus AS SELECT 1 AS Active, 0 AS Inactive, 2 AS Paused. Could multiple CROSS JOINs to similar views cause compilation issues?

It’s dumb because there are no statistics on the output of views. Use tables instead.

Q: Midwest DBA: What are your thoughts on Ottertune.com? Seems like a cool technology. Do you foresee Microsoft employing Machine Learning in this way to improve database/server performance?

Love the idea of OtterTune. Microsoft doesn’t appear to be taking this approach. They’re hard at work figuring out Cost Threshold for Parallelism. Gotta learn to crawl before you learn to walk, I guess.

Q: DBA_Mufasa: Hi Brent! What’s your best approach for tracking tables usage in SQL databases, in order to find tables that haven’t been touched (for eg. no select, updates, or inserts ) for a a defined amount of time. Considering that table statistics get cleared after server restart.

What’s the problem you’re trying to solve? If you’re trying to drop tables, go talk to the users.

Q: Morty: How do you determine the optimal number of DBA’s for a given product / company?

This.

Q: CKI: Management wants to create a copy of the production database for reporting purposes. The new “reporting” copy should be refreshed nightly with production data. Production database is on AWS Web Edition 45GB. What is easiest way to do it? Thank you very much!

Nightly restores.

Q: Quincy: How do you determine which SQL changes are low risk and which changes are high risk?

Whoever makes the change needs to list at least 2 ways the change might screw things up. If they can’t come up with 2 ways, they don’t understand enough about the change. Then, think about the business risks for those problems.

Q: Brutus: How do you determine the optimal Windows OS page file size for a bare metal SQL server?

I read the documentation. Doesn’t work for everybody, I know – some people struggle to read.

Q: Sandeep Pawar: I am a SQL DBA with 12 years of experience and also learning and trying my hands on Postgres as well. Is SQL+ Postgres DBA combo would be beneficial? Thanks

I wouldn’t go learning random technologies. Ask what your company needs, or if you want to change companies, think about the technology you wanna use for the rest of your life. Don’t gamble on what unknown companies might like – companies need janitors. Does that mean you should pick up janitorial skills?

Q: Stimpy: For concurrently executing queries against the same tables, will SQL Server share the read operation data between the two different queries?

Read this.

Q: cyrpl: Moving SQL query analysis out of SSMS to Azure data studios will be a big hill to climb for most DBAs, where are you on that hill Brent? Have you written any Jupyter notebooks yet, it looks like Microsoft and others are moving all in on this, what do you think?

Yes, I’ve written blog posts with notebooks. I can’t really teach with notebooks yet because they don’t have execution plan support.

Q: Twiki: What are the top SAN concepts that are beneficial for the SQL DBA to know about?

Start learning the storage your company uses. Often, it’s not SAN at all, but instead it’s cloud-based storage, and that’s completely different.

Q: TJ: We have 1TB database containing 950 GB single table. This table has 95% of data that is not needed anymore. Application team is recommending a slow and gradual delete of the data from front end, but it can lead to performance issues as you know. Can you advise best approach

If you’re going from a 1TB database down to 100GB, don’t do deletes. Insert the data you’re keeping into a new database.

Q: WhatsUpDocs?: Hi Brent, have you ever needed to look at business documentation (check business rules/logic) when consulting or as an employee, but it was severely lacking? Recently joined a different team in work and trying to find simple answers to questions is an uphill struggle…

The vast, vast majority of companies don’t document their technology. The tech is in a constant state of flux, and it’s a miracle if the tech even works, let alone is documented accurately. If you’re the kind of person who needs accurate, up-to-date documentation on the tools you use, you’ll be happier working for very large, slow-moving companies with compliance needs. Think giant global financial corporations.

Q: Neil: When a query goes parallel, does it use the MAXDOP number of threads or only as many threads as it needs ?

Generally speaking, it starts at MAXDOP + 1 coordinating thread.

Q: Erdem: Why does multi column index only have single column histogram step value?

Because Microsoft chose to fit stats in an 8KB page, and that required design tradeoffs.

Q: Conan: Non Tech question: Next Travel plans if any?

Israel this fall, Antarctica this winter.

Q: Preben: What is your least favorite operator in an index plan? E.g: EriK Darling’s eager spools

I thought a lot about this, and to refresh my memory, I looked at Hugo Kornelis’s operator list. My current obsession is the Parallelism Gather Streams operator, but I’m still in the honeymoon phase with it because everything seems so cool. Each new thing I learn about it, I think, “Ooo, neat, I can understand why they made that tradeoff.”

When I was looking at Hugo’s list just now, I cringed a little when I saw the Bitmap operator. I’ve managed to get through almost 25 years of working with SQL Server without having to know what that operator did. I’d kinda always had it in the back of my mind as, “I should learn more about that someday,” and so I read through Hugo’s explanation just now. I nodded my way through it, and closed the browser tab like Grandpa Simpson walking back out of the club. If you made me pick, that’s probably my least favorite.


Don’t Treat SQL Like a Programming or Scripting Language.

T-SQL
6 Comments

In a programming language like C# or Java, you tell the computer what to do, in order.

Get the customers from California, then get their invoices, then sum the total.

SQL, on the other hand, is a declarative language where you declare the shape of your result set:

Get the total sales from Californian customers.

You’re declaring the output that you want, not the methods the database server uses to build it. Oh sure, you CAN use SQL to declare the shape of your query plan, but generally that leads to heartbreak and despair because:

  • You don’t know the database engine’s internals as well as you think
  • You’re backing the database server into a corner, forcing it to use specific techniques, when it has better ones available at its disposal
  • Your data distribution is going to change over time, meaning the method you use to process the data today won’t make sense in a year or two
  • Your database server is going to change over time as you patch and upgrade it, meaning it has newer and better methods to process data, and it needs the flexibility to leverage those
  • Your hardware is going to change over time, and the query plan that made sense with 16GB RAM won’t make as much sense with 512GB RAM

When your application is young (1-5 years), use as few engine-specific hints as possible in your queries. Don’t tell the database server which index to use, what order to do its processing, or what kinds of joins to prefer. Give it as much flexibility over time as possible by keeping your query simple and letting the engine make different decisions as your data, database version, and hardware changes.

When your application is mature (5-10 years), then your data distribution, database server version, and hardware has started to specialize. This is the time where you can start bossing the database engine around with specific hints to tell it how to do processing.

But if you use those hints when your application is young, you’ll find that you’re constantly revisiting old code, having to fix query performance, because the query hints you used on a 1-year-old application just don’t make sense anymore, and they’re making performance worse instead of better.


The Top Feature Requests for SQL Server

SQL Server
28 Comments

If you want to get a look at where Microsoft SQL Server is heading in the future, it helps to get an idea of what users are repeatedly asking for. Here’s a quick rundown of the top requests from feedback.azure.com as voted by the people who cared enough to log in and upvote ’em:

10: Enable Query Store for collection on a read-only replica in an Availability Group: 563 votes

9. Add an SSMS keyboard shortcut that executes the statement where the cursor is placed: 589 votes

Of course I have a straight face under here, why do you ask?

8: Improve NoSQL functionality: 652 votes

7: Run DBCC CHECKDB with PHYSICAL_ONLY automatically in the background: 672 votes

6. Support DISTINCT for STRING_AGG: 717 votes

5. Add a row position column to STRING_SPLIT: 747 votes

4. Develop an SSRS ReportViewer for ASP.NET Core: 1,262 votes

3. Restore a table from backup: 1,347 votes

2. Dark theme for SSMS: 1,958 votes

1. Put the Debugger back into SSMS: 2,041 votes

When thousands of folks band together to ask for something, Microsoft hears your voice. They know it’s important to keep the end user community satisfied so folks will keep evangelizing the database they know and love. That’s why in every release, Microsoft keeps bringing crowd-pleasing game-changers like SQL Server Ledger, blockchain technology in the database that keeps your transaction log around forever inside the database. I’m pretty sure that one was #11 in the list above. Maybe #12.

That’s why it’s so important that you create feedback requests to reflect what you’re interested, take time to read the requests from other folks, and upvote the ones you’d like to see implemented. Microsoft believes that your votes should matter, and that’s why they show the list of feedback items sorted by votes. Your voice matters: check out Microsoft Feedback today.


“Index Seek” Doesn’t Mean Much.

Execution Plans
13 Comments

When you see “index seek” on an execution plan, that doesn’t mean SQL Server is jumping to exactly the row you’re looking for. It only means that SQL Server is seeking on the first column of the index.

This is especially misleading on indexes where the first column isn’t very selective.

To explain, I’ll take one of the big Stack Overflow databases and create this index on the Users table:

And then I’ll run this query:

The actual execution plan shows an index seek:

But hover your mouse over the index seek to see the popup details:

Look at “Number of Rows Read” – we read 6,044,557 rows to produce exactly 0 rows. That’s not what you or I would really call an index seek – we read 2/3 of the entire table!

The problem is that the term “Index Seek” only refers to how we access the first column of the index. At the bottom of the screenshot, the term “Seek Predicates” indicates that we did indeed seek to Reputation = 1, but unfortunately, there are millions of users with that reputation.

A little higher up in the screenshot, the term “Predicate” indicates that we have a leftover predicate that we couldn’t seek into. I wish this was labeled as a scan predicate because we’re scanning all the rows that match our Seek Predicate – we’re scanning them all because they’re not in an order that helps our search.

Sure, technically DisplayName is in the index – but it’s not the second column in the index, so it doesn’t really matter whether it’s in the key or the includes.

When you see an Index Seek in a plan, before you jump to conclusions that it’s a good usage of the index, compare the Number of Rows Read versus the Actual Number of Rows. If your query is reading way more rows than it’s actually producing, you might be able to dramatically improve performance by tweaking the indexes.


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

Who's Hiring
25 Comments

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

If you want to be notified as people leave new jobs here, there’s a “subscribe without commenting” option where you leave a comment. I don’t use that for subscribing you to anything else.


Office Hours, Text Edition: No-Brainer Questions

For my Office Hours streams on my Twitch channel, y’all post questions at PollGab and upvote the ones you’d like to see me discuss on the stream. However, not all questions require a video answer. Here are some simple no-brainer ones:

Q: BehindTheScenesDBA: With all its limitations, what do you think about memory-optimized tables? Have you recommended to a client to use it?

A: They don’t make any sense for the vast, vast, VAST majority of clients, and no, I’ve never recommended them. In-memory temp tables are different though, and I cover when to use those in this module of my Fundamentals of TempDB class.

Q: Sultan: Does this mean Azure storage is now as fast as a Y2K SAN?

A: Well, a couple of VM types are now competitive with my laptop, so…that’s good, I guess?

Q: allmhuran: Azure SQL databases come with lots of connectivity issues with SSMS – a tenant MFA prompt (legit), then another MFA prompt for every other tenant where I have a guest account (?), then a firewall prompt (if I can add the rule, why even ask?). Do you know if MS is working on this? 

A: Did you call them to open a support case to get them to fix it?

Q: Florence Nightingale: What is your opinion of the stat histogram step limit (199) in SQL Server? Should this be changed in future versions?

A: It’s fine for most use cases. I think in the grand scheme of things, there are other things I’d rather have Microsoft focus on fixing instead. Most people don’t even have accurate knowledge about this kind of thing, let alone know that it’s a problem. Hell, in your own question, your facts are wrong, ha ha ho ho – the limit isn’t 199 steps.

Q: Levi: Will long running index reorg / rebuild jobs for large tables prevent the transaction log from clearing? Is this a risk?

A: For rebuilds, yes and yes.

Q: Mr. Bean: Hi Brent, do multiple data files per file group help with user DB performance like how they help with TempDB performance? Running SQL 2019 on NVME SAN.

A: Only if your workloads are the same as TempDB, meaning users create and drop thousands of tables per second.

Q: JerseyDba: Migration from 2016 to 2019 OR 2022 – I am currently ready to upgrade our SQL database servers and was wondering if it was worth upgrading to 2019 or waiting for a stable version of 2022?

A: Don’t hold out hope for something that doesn’t even have a public preview yet, let alone a release date.

Q: Sev A: Hi Brent, when Compilations/Sec are high, is there good way to view the recently compiled queries in real time / near real time?

A: sp_BlitzCache @SortOrder = ‘recent compilations’

Q: Dwight: Is analysis of the auto generated stats ever recommended or useful (i.e. WA_Sys_00000003_15502E72)?

A: Yes, and I discuss it in my Fundamentals of Query Tuning class.

Q: Mr. Ed: Hi Brent, what tool / script do you recommend for formatting SQL agent job query results to HTML for subsequent emailing?

A: If you want reports, use a reporting tool. You’re trying to use a job scheduling tool for reports. Sir, this is a Wendy’s.

Q: Augustus Gloop: Will the max of 160k query plans be increased to accommodate Parameter Sensitive Plan Optimization in SQL Server 2022?

A: Anything about 2022 is subject to change until release, so I wouldn’t worry about the specifics yet.

Q: Noam: What’s the easiest way to see the number of locks acquired for a given update statement (for lock escalation concerns)?

A: sp_WhoIsActive @get_locks = 1

Q: Unsigned Biggie: Hi Brent, worried about eventually running out of int64 values for an identity column on a new table in SQL. Should I seed the identity column value with the max negative value for an int64 to prolong the life of this table?

A: Sure.

Q: Sir Logs-Alot: Hi Brent, have you ever seen sp_whoisactive intermittently crash dump on SQL 2019 CU14?

A: No.

Q: Hatsune Miku: Hi Brent, what are your recommended settings for SQL Agent job history log size and job history rows per job?

A: I don’t have any opinion on that.

Q: Marco: How often does using the KEEP FIXED plan option get you over the finish line?

A: Never. Not even once.

Q: Brandon: Has a SQL Server service pack / cumulative update ever given you buyers remorse? If so, which one?

A: Many. Search for “breaking news” on this site.

Q: JAH: Do you know if Polybase suffers from the same performance limitations as linked servers? MS docs says “the benefit of Polybase is to allow the data to stay in its original location & format so that it can be queried in place like any other table in SQL Server.” But at what cost?

A: Yes.

Q: Doug: Hi Brent, is there a good tool or way to compare the estimated number of rows (equality) for each step in a stats histogram with the actual number of rows in the real table? Might be good to know when there is large variance between the two.

A: Nothing built in, no.

Q: OhLordI’mStuckInAzureAgain: My friend has a stored procedure that takes a minute to execute. The plan has a statement with a MEMORY_ALLOCATION_EXT wait where the WaitTimeMs is 400, but the WaitCount is almost 1 million. Should he be concerned by the high WaitCount even though the WaitTime is relatively low?

A: No.

Q: Voltron: Hola Brent! I inherited a SQL Server 2016 database that has the trustworthy database property enabled. The application vendor confirms this setting is required. What can be done to provide a secure environment for this database and our other SQL Servers?

A: I don’t do security work.

Q: Talip: What is your opinion of the SQL Virtual Machines best practices assessment tool for Azure? How does it compare with sp_Blitz?

A: I have no idea.

Q: Stanley Hudson: Sometimes we see log shipping alerts notifying us that we are more than X minutes out of sync on the log shipping secondary.Hard to tell what burst of transaction log activity (other than Index maintenance) causes these warnings. Is there a good way to track down the root cause?

A: Try logging sp_WhoIsActive to table and check out the Writes column.

Q: Robert: Do you know why, in the execution plan, the number in % of actual vs expected rows is stored as an integer instead of a bigint? It went negative on me today cause of int overflow 🙁

A: “Why?” For “Why” you would need to ask Microsoft, right?

Q: eshirvana: Why optimizer is using nested loops join when I join my tables? what can I do to force optimizer to use a better join algorithm? under which circumstances should I use join hits, If I should at all.thank you

A: Check out my Mastering Query Tuning class.

Q: Ralph Hinkley: Does sp_blitz check for high VLF counts?

A: Open the code and do a control-F for VLF.

Q: Dan Heile: Why can’t sql DMVs or monitoring software like SentryOne capture performance statistics on stored procedures that open symmetric keys / certificates to decrypt encrypted columns?

A: “Why?” For “Why” you would need to ask Microsoft, right?

Q: Bill B: On an alert 14 insufficient permissions for example is there a way to get the machine name instead of just the ip address? DESCRIPTION: Login failed for user ‘userxxx’ . Reason: Could not find a login matching the name provided. [CLIENT: 123.123.123.123]

A: I have no idea.

Q: Accidental_dba: Hi Brent,Thank you so much for this great work,I have question on memory table,my normal insert for 18m record taking 40 hour with 8core and 150 gb ram,top waitype as per blitzfirst are Sos_work_dispatcher,pwait_directlogconsumer_getnext Anything i can do here ?Feel like m baddba

A: That’s beyond what I can answer quickly here, but check out the Consulting section of the site.

Q: DBA_Europe: Hi! We have a few 3 node AGs(2 ha in sync & 1 DR cross site async). Cross site network throughput is too slow for replication to DR(log send queue accumulates). Can you combine AGs and log shipping? BOL doesn’t mention this in the interoperability Section.

A: Yes.

Q: Tobias Fünke: What is your opinion of the Azure SQL Database DTU Calculator app for lift and shift capacity planning a move from onprem SQL to AzureSQL? https://dtucalculator.azurewebsites.net/

A: I’ve never used it.

Q: Mark Tomlinson: Hi Brent – can you think of a situation where a sql server spid could exist without a query plan associated/created for it to operate?

A: Yes, system processes.

Q: RetiredDBA: I used to admin a third-party db which had hundreds of tables containing millions of rows and lots and lots of indexes. The vendor used guid’s exclusively for PK’s. What would be your advice on this choice of datatype for PK’s?

A: I explain that in the clustered indexes module of my Mastering Index Tuning class.

Q: GoingWalnuts: Brent, I have an Azure database where foreign keys are just not being enforced. All enabled/trusted. Full of bad data now. You can even drop and create FKs that should never EVER work, and they do. Am I missing a setting… or some medication? SQL just whistles on by. Weird..

A: I explain that in the foreign keys module of my Mastering Index Tuning class. (I know, I feel kinda bad when I link to those, but there’s a reason those modules are 30-40 minutes long.)

Q: CKI: My developer wants to have permission to Kill his own processes . Currently the user has only read/update/delete/insert permissions on the database. How can I grant the “Kill only his processes” permission ? Thank you!

A: Put the kill in a stored proc, and sign the proc with a certificate. Here’s an example in the sp_BlitzFirst documentation.

Q: Ben Cox: Hey Brent – What are the technical reasons why CDC is not supported for CCIs?

A: I have no idea.

Q: Timbalero: Hi Brent, a 1TB clustered table has a nvarchar(max) column and a nc index built of all base columns. Including the nvarchar column into the nc index was supposed to be a way to fix blocking on that column but I wiev it as a sledgehammer approach. Is there a more elegant solution?

A: Answering that would really require seeing the tables, queries, etc.

Q: Kamy k: Can’t find the answer anywhere, Is it possible to pass environment variables into Code snippets to autofill ie current date, user, DB. Specifically for use in generating header documentation.

A: I have no idea.

Q: Jr Wannabe DBA: Hi Brent, I see often wait stats like “For 2004 seconds over the last 5 seconds” on servers with 4 to 12 CPUs with sp_BlitzFirst, mostly CXPACKET or CXCONSUMER. How to read/understand this? It looks too much for that time frame.

A: Your server has multiple CPU cores, each of which can run multiple queries simultaneously. I go into more details on that in the first module of the Mastering Server Tuning class.

Q: DannyDing: Hi Brent did you have any experience with SQL Server supporting PeopleSoft ERP?

A: Yes.

Q: StanTheMan: Hello Brent,sa account.When master DB stores usernames and pswds,how can I secure the master DB before attack?Recommendation is not to use sa as users DBs owner+disable sa and the one and only DB account per server as DB owner in case of Princpiles of least priviligies.Thats all?

A: I don’t do security training, sorry.

Q: SeeCoolGuy: can sp_blitz help out to identify those long Network I/O sessions?

A: No, for that, use sp_BlitzWho or sp_WhoIsActive.

Q: PleaseAndThankYou: Hi Brent, It says online that cluster indexes are faster that non-cluster indexes. Does changing a non-CI to a CI have performance benefits? I ask, cause they want me to do this to see if the company program wil run faster.

A: Check out my free How to Think Like the Engine course.


[Video] Office Hours: New Las Vegas Home Office Edition

Videos
2 Comments

It’s the first webcast I’ve done from my new Vegas pad. You posted questions at https://pollgab.com/room/brento/ and I discussed the highest-upvoted ones:

  • 00:00 Introductions
  • 02:15 Jim D: Should I use full backups or log shipping for migrations?
  • 03:53 Sev A: What are the top misuses of SQL Server that are better accomplished without SQL Server?
  • 07:16 Greg: What are the best/worst KPIs for SQL Server DBAs?
  • 09:15 DBA_Mufasa: Why can’t we restore a table from backup yet?
  • 11:38 Tolga: Should we fix deadlocks by doing retries or changing the query?
  • 12:54 Anyong: Should I put clustered indexes on temp tables to avoid high forwarded fetches?
  • 14:45 Can I JOIN You: How can I showcase my worth when I improve things?
  • 17:32 You’re the man now dog: (something about SaaS databases, not clear)
  • 18:32 john doe: What are your thoughts on crypto currencies?
  • 19:36 Jeb: How do you convince someone to implement caching?
  • 21:57 Aslan: What are the cons of hosting a database on a USB drive?
  • 22:53 Sultan: Is it okay to run SQL Server 2019 Standard on a physical box with more than 24 cores?
  • 25:29 Recap and discussing the Las Vegas home office

What DBAs Need to Know About Snapshots

Storage
5 Comments
Brent Reading Book
Snapshots by Polaroid? That’s not right.

You’re a DBA responsible for making sure SQL Server databases are online, backed up, corruption-free, and fast. Your databases have gradually grown in size over time, and you’re starting to hit new size issues you haven’t encountered before.

Nightly maintenance windows are getting smaller, you’re not able to refresh your development environments quickly enough, and you’re not able to run DBCC CHECKDB as often as you’d like.

You’re starting to wonder – how do people with multiple terabytes of databases handle it?

In a free webcast on May 12, I will explain how I came to love storage snapshots. I’ll discuss how they work, why they’re safe to rely on, and how to tell when they’re a good fit for your workloads.

Register here free, and if you can’t make it live, you’ll also be emailed when the recording goes up.


[Video] Office Hours: Last San Diego Balcony Edition

Videos
0

It’s the last San Diego balcony edition of Office Hours! You posted your questions at https://pollgab.com/room/brento and upvoted the ones you’d like to see.

Here’s what we covered this week:

  • 00:00 Introductions
  • 00:50 Steve E: Hi Brent, How are you enjoying the Porsche 944?
  • 01:55 Midwest DBA: Hey Brent, The wife says my DBA oncall schedule is too intrusive to our family time. How do I switch to being a developer with no on-call? Is being a developer the only career transition I can make that would have no oncall? Will I make less or more? I’m currently at $90K
  • 04:25 NotCloseEnought2RetirementToStopLearning: Brent As a prod SQL DBA since 2001, I’ve expanded my skills into Azure SQL and Cloud generally to keep relevant. It hasn’t increased my marketability as I expected. What are technologies (Postgres? Synapse?) are worth learning to stay in high demand for the next 5-7 years?
  • 06:15 DBA_Mufasa: Salut Brent! What is your recommendation regarding failing over an Always-On Availability Group during Server OS patching for instance. I have “heard” it’s best practice to move the cluster resource within failover cluster manager first, before failing over the AG group in SSMS.
  • 06:43 Wally: How do you deal with justified / unjustified criticism as a DBA?
  • 08:18 Doug: Are there any good use cases for when stats auto update should be turned off for a given table?
  • 10:45 Doug E: Hi Brent, is there a good way to know when a plan operator has a hard coded estimate?
  • 11:01 Halim: What are your top two most painful mistakes as a DBA?
  • 15:21 SQLing Pigs: Third Party App is sending ad hoc queries with IN statement that has varying number of items. I’m getting thousands of execution plans from this. What are my options? More important plans keep getting pushed out.
  • 17:16 Tony Feuz: Brent – I know you are a fan of assigning one database to each customer instead of a multi-tenant model but if you are going down the path of multi-tenant: then is using an inline table valued function a good way to implement row level security? Are there performance issues?
  • 19:54 HerbyHoover: Hi Brent, What would you say are a few core performance metrics that a Jr. SQL Server DBA should focus on better understanding?
  • 20:41 Sultan: Hi Brent, should Microsoft put the TSQL debugger back in SSMS? Do you ever use the TSQL debugger in Visual Studio?
  • 23:08 Wally: Which SQL server docs do you read / focus on when a new version of SQL Server is released?
  • 24:25 Jim Ignatowski: Hi Brent, our top wait is PREEMPTIVE_OS_GENERICOPS (Wait time hours = 3746.1, Hours Sample = 812.6, Per Core Per Hour = 0.1, Physical SQL 2019 Enterprise with 64 cores). What can we do to reduce this wait or should we ignore it?
  • 25:09 Benny Hill: Do you or your clients ever run into issues with SQL Server / TLS?
  • 26:04 The F5 Masher: Hi Brent, should Microsoft enhance extended events to allow writing of session results to actual tables?
  • 27:45 Latka Gravas: Hi Brent, for a dedicated backup test / checkdb SQL Server (SQL 2019, 64 cores), is it a good idea to set Maxdop to 0 to maximize CPU use? Using dbatools sql agent job to do the backup restores / checkdb’s.
  • 30:18 Ralph Nader: Hi Brent, is there any safety value in running CHECKDB against TempDB and Model?
  • 31:41 Jacob: Does updating stats for a table have similar free space requirements to that of rebuilding an index?
  • 32:08 SQL Serenader: In one of your articles, you suggest 4 GB or 10% of RAM (whichever is LARGER) for MAX server memory. What about a server with 767 GB of RAM? Allocating 77 GB to non-SQL processes seems excessive. Could we get away with 16 GB (2%) if we don’t see memory pressure or paging to disk?
  • 34:32 Genady D.: Hi Brent Recent expensive queries in Activity Monitor miss (by my opinion) link to user/host who run it. How do you deal with this problem? Which way except profiler the best?
  • 35:50 Recap and goodbye to the balcony