Office Hours: Open Database Q&A in Las Vegas

[00:00:00]Good morning party people and welcome to office hours the webcast in which I look at your questions and and chuckle at what were you thinking when you were asking me that. Um no I I always try to think like how can I be more positive and uplifting? So I'll tell you a story.

[00:00:18]When I first got started blogging I worked uh in the marketing department at Quest Software. Hey sirly Dev. Oh do I have the chat? I should have the chat alarms on. I'm surprised I didn't pick that up. Uh I uh worked in the marketing department at Quest Software. I went from database administration to actually being a DBA in the in the marketing team. Um and as part of the marketing exercises, you were taught to pick three words about the brand that you wanted to have like the thing you wanted to be known for. And my three words were I wanted to be technical, approachable, and likable. I wanted people to understand that I knew what I was talking about. I wanted them to feel comfortable approaching me uh and asking me questions and I wanted to be known for having a fun and easy to deal with demeanor. Um and over time what I learned about that was I I was pretty successful at doing that. But then the flip side of that was everybody felt really comfortable asking me their questions and having me kind of solve their problems for free. And that was not a whole lot of fun. So I end up uh changing that gradually over time. I don't have three words right now. Like I don't have I have a lot of words. Some of them are printable, some of them are not. Um, but I should probably go back and redo that branding exercise at some point. That would be kind of fun. It would You know what? It would be fun. It would be fun to see if chat GPT what three words it would come up with about me. Let's try that. Let's do it for fun because I think this is going to be kind of amusing. Let me fire up chat GPT and I will share my screen. I just want to make sure that I don't have uh uh anything up here that's going to be like ugly from clientwise or with the work that I was doing this morning. Uh let's get rid of that. Let's get rid of that. Okay, perfect. Let's get rid of that. Now, let's share. Can I share that? Yeah, I can share that desktop monitor to Oh, let's see. Oh, it didn't. Oh, good.

[00:02:18]Oh, no. I just need to drop my my uh desk a little there. There we go. Um and I need to change the green light. It's going to be a little fuzzy underlining because of the green light. That's okay.

[00:02:29]We're going to deal with the little fuzzy things so I don't have to change stuff. Then I'm going to ask chat GPD.

[00:02:34]Oh, Alexander says hello from the Netherlands. Shane is here. Kansas.

[00:02:38]Someone named Kansas is here from Belgium. Okay, that's an interesting approach. Um so that uh so we'll put that up over there. Whoops. Oops, that's not right. was trying to move my Twitch chat. Let's move that over here. And let's move me a little bit smaller because there's no need for me to be that large. Um Sirly Jeff said I would use cars in house. That's not bad. Um so I'm Brent Ozar. Um, uh, what if you had to describe me in three words based off what you know, uh, about me publicly, not our chat history. What three words would you use? That'll be kind of funny to see. Um, and see what it comes up with. Oh, wow. Oh, that's nice. Oh, I could I don't mind that. I don't feel so bad about that. Um, practical, candid, and generous. Wow. Okay, I can live with that. That's not terrible. Sirly Dev says, "Oh, Serly Dev says, co-pilot said, practical, insightful, and entertaining." These are words that I could live with. I don't mind those so that's that was I like being vulnerable, especially on screen or whatever, you know? like I like sharing these kinds of things and it's kind of fun to have these discoveries about, you know, what would it say? Now, the other thing that I'm going to ask is I'm going to say now based on what you know off of me with my chat history, what three words would you use? And I didn't want to have that up on screen because sometimes I'll search for things that are either client related because they told me it was okay to share things with chat, GPD, and all that. I'm not getting into that now. Um, uh, and I'm going to say, uh, what three words would you use if your context also included our chat history together? Um, Chris says, "I always like telling chat GPT to give me SQL advice and scripts and the personality of Brennos are." I I like doing that, too. I'm I'm working on a tool that'll uh let me write with Claude um using my voice based on samples of my past work. Um uh but the the thing that I use is Anthony Bourdain. I say all the time, give me advice in the style of Anthony Bourdain.

[00:04:50]I'll say Walter Kron, Kronite, Marilyn Monroe, you know, I'll use all kinds of historical personalities. Um, so chat GPT. Oh, interested. Uh, chat GPT says connect Gmail to get better information.

[00:05:02]Chat GPT says, "Give me three different uh words." This is curious, pragmatic, and playful. Um he says curious because a lot of our conversations start with I wonder if rather than how do I you explore edge cases, historical features, product design. Oh, that's very true. Um uh pragmatic. It says you're this is probably your strongest through line.

[00:05:24]You regularly reject ideas that aren't grounded in reality. Uh you've asked me to push back on things if it wasn't technically defensible and you ask for tradeoffs rather than hype. Uh that's actually delightful. I kind of like that.

[00:05:38]All right, let's see here. What have we got for now? Now we're perfectly primed to go tackle people's uh uh questions from over on PGA. Oh, wow. Surely Dev says, "My personal experience, he's one of the few database educators who makes performance tuning feel less intimidating." I I I totally strive for that. Like I want to make things seem as easy and simple as possible. There's no magic here. You know, once you understand what's going on inside the black box, anybody can understand how this stuff works. It's just a matter of finding uh educational material that is presented in a way that demystifies things instead of glorying, you know, glorifying the mystery. I you when I was coming up through the ranks, there were a lot of training materials that tried to make it seem harder than it was like they would dwell dwell on trivial pursuit type stuff that didn't really help you uh get the problem solved. Good morning, Randy.

[00:06:30]Uh so, let's see here. Let's go hit our top voted questions from PGA. The top voted question from Polab is, "Hi Brent.

[00:06:36]We're seeing hater sync commit weights causing brief outages on random days, but it's consistently between 2:00 a.m.

[00:06:43]and 4:00 a.m. Pacific time. Um, we're not making any app changes and we only run backups on the sync secondary. How can we pinpoint the root cause and prevent this? Thank you." Okay, [snorts] so first off, a little background information for people who may not be as savvy as DBA in action is because DBA in action has some good background information here in a small tight area of space. You usually see hater sync commit which means that the primary in an availability group is having to wait for a sync secondary to do its rights.

[00:07:15]You usually see that when the sync secondary can't keep up with the primary. Now, one way you could think about that is that the secondary is too slow, like it's crappy hardware. The other way that you can think about it is that the primary has too many changes happening. And DBA in action says, "Okay, this is happening between 2 a.m.

[00:07:37]and 4:00 a.m. There are no app changes.

[00:07:39]So, what are our two options?" Either something's happening on the primary that's producing a very high rate of change. Index rebuilds is the classic example for this. ETL jobs where somebody deletes an entire table and then recreates it from scratch. Um, those are the two things that I would looking at. What jobs are running between 2 a.m. and 4:00 a.m.? Um, and can you may not be long running queries because like in the case of index rebuilds, it can be rebuilding a whole bunch of really tiny tables really quickly. The rebuilds will roll through quickly, but then they'll they'll saturate the network with all these changes. So, our what's our change rate look like during those times? And if you wanted to get a rough idea of that, you could use the DMV cydm DMIO virtual file stats. I'm sure I'm mispronouncing that, but or like, you know, misremembering the exact spelling.

[00:08:34]DMIO virtual file stats. You could compare the contents of that before your 2 a.m. window and after the 4 a.m.

[00:08:42]window and look at that compared to your regular 2-hour gaps throughout the day to see if there were a whole lot more rights happening inside that window. I say that because then that way you don't have to do any kind of query profiling or plan cache analysis in order to find out if this is happening. Just have a job at 2 a.m. capture the contents of DMIO virtual file stats. have it run again at 4:00 a.m. and see are we writing a lot of data to disk during that time. The other thing you'll look at is you said we only do backups on the sync secondary. Well, if the sync secondary storage isn't that good, as you're hammering it for backups, it will absolutely cause slowdowns on the primary cuz before the primary was just barking commands out continuously to the secondary and the secondary was writing them down on the log files as quickly as possible. Once it starts doing backups, backups can affect the right speed of what's happening in storage. Because just like you, storage can only do so many things at once. And the more things that we stack up on its plate, the more context switching it has to do, the more it has to clutter up your queue of write log requests, the the harder it's going to be for you to get those backup jobs done. So, if you wanted to, you could try switching your backups on the sync secondary. If they're happening between 2 am and 4:00 a.m., try pulling them earlier. Try, say, midnight to 2 a.m.

[00:10:09]and see if that matches up with your shift. If so, that's a sign that you may want to do the backups from an async secondary instead. Uh, that way it won't hold up the process of getting your rights committed on your sync secondary.

[00:10:25]Um, now those aren't the only two. The other thing that happens all the time overnight. Yes. Surirly Dev says DMIO virtual file stats. That's the one. Um the the other thing that happens overnight all the time in environments is you're probably sharing your storage with other people. And if you have noisy neighbor syndrome where some other neighbor is doing all kinds of work on storage or CPU for that matter uh then you can run into this exact problem whether it's happening on the primary or whether it's happening on the secondary.

[00:11:02]So if if this is a VM for example, I would ask your CIS admins, hey, can you show me the storage uh uh like latency graphs or load IOPS graphs uh for a 24-hour period and see if you see a huge slam from 2 a.m. to 4:00 a.m., a huge set of peaks. Um or look at uh you can either ask that from the storage people or ask it from the CPU uh layer at VMware or HyperV or whatever it is you're using. Um, and if you're the whole host is getting hammered during those times, that'll also tell you. So, hopefully that gives you some uh places to start. Oh, Vlad DBA. Oh, Vlad DBA, welcome.

[00:11:41]Um, Vlad DBA, I am so sorry to hear that you uh lost your job there. Got laid off there. Uh, I read that on LinkedIn and I was like, "Oh my god, that's unbelievable." And I reposted it, too.

[00:11:53]Boy, the the economy is really rough right now, right? like there are so many people who've gone through uh layoffs.

[00:12:00]Companies are doing weird things with AI. It's it's a really rough and rocky time. Um every month I publish a who's hiring right now in the Microsoft database space. Um and it's it goes up and down kind of seasonally, but summer is usually a terrible time if I remember right from the graphs. Um but I don't think there have been any jobs posted to July's uh help wanted type thing.

[00:12:25]Absolutely terrible. But I I feel you and hopefully you find something really quickly.

[00:12:32]Next up, Thomas France says, "Doesn't it make more sense?" Be careful how you word these things. Uh to index valid to instead of valid from. Oh, uh the valid from is usually unknown. Okay. So, here's what he's asking. So, it's a classic design problem in databases, not just SQL Server, but databases in general. um to have a table that says uh it has a start date and an end date.

[00:12:59]Sometimes it's valid to and from.

[00:13:01]Sometimes it's like discount codes.

[00:13:03]Sometimes you're uh uh and it's it's on the row level. So if you know valid from and valid to it's not like you can seek to one of those uh because the valid from or valid to may span wider or earlier than the data that you're looking for. Thomas, this question is so good and I I don't get it very often from clients, but the answer and you're not wrong, but you're also not right.

[00:13:28]The answer is in a blog post. If you search for Brenttozar start date, end date index. Uh, and you may also want to confine that search to brentoar.com and then that will get you the answer. And I have demo queries in there that show it both ways where in and data distribution skews. The real answer depends on how the data distribution skew of your data.

[00:13:52]With some data distribution SKs, it makes more sense to index valid from.

[00:13:57]With some of them, it makes more sense to index valid to. And you can easily produce a demo that proves either answer and I do in that blog post. But that's why I explained that the answer really depends on the data that you have inside your database. So that that answer can change. Of course, the the good part about that uh is that uh the you can change that answer really easily. You can always change the way that you index without having to change your queries. Uh but I love the the thought that you're putting into that too because you'll you'll like this blog post a lot.

[00:14:31]Ah, web uh thank you. Welcome to the club. Um, that's funny. So, let's see here. There was a a chat thing that came in earlier and I missed it. Sirly dev says, "I asked Copilot, what database educators would you group Brent with?"

[00:14:44][laughter] And what you should try is not just uh database educators, but overall educators. And that would be kind of funny. Uh, and he said, "If you asked me who belongs on the SQL Server Educators Mount Rushmore, I'd probably say Brentosarbone, Paul Randall, and Kimberly Trip. They each changed how a large portion of the SQL Server community learns and works.

[00:15:03]Brent's niche among them is that he's probably the most approachable. See, that damn word approachable. Uh, approachable and developer friendly of the group. I will say out of all four of those, uh, me, Izzik, Paul Randall, and Kimberly Trip, they are all amazingly approachable. They are the nicest people you will ever meet. They are friendly.

[00:15:25]They are fun. Um, really I I think I I almost said there's nobody in the SQL Server community that I would not put into that list and as soon as those words came out of my mouth I was like I can't say that out loud. Um, but the people that you follow and know and love, uh, Eric Darling Vlad, you know, like all these people who, uh, whose blogs you read and know these days absolutely fall into that category there. Never be afraid to go up and talk to a presenter at a conference because you'll you'll be amazed at how excited they are to uh talk to someone else about the same kinds of things uh that you share. Um uh as as Khan says, I'd put Eric Darling on there, too. I know and love Eric. He is brilliant. He's one of the smartest people that I've ever had the pleasure of meeting. Um and I was so excited when he put out that open-source performance monitor tool. Um because I think that's really going to change the industry going forward long term just like the first responder kit uh did. I still haven't used it yet.

[00:16:29]It's one of those where I was like it's it needs to be out for six months before I go and use it just cuz I want I don't want to have to keep like redoing blog post screenshots or whatever as the product keep changing keeps changing.

[00:16:38]But we're coming up on the date where I told myself I'd start going in and playing around with it. And I'm really excited for it because I know knowing Eric the quality of what he does is just freaking absolutely topnotch.

[00:16:50]Yx, you're absolutely right. Let's see.

[00:16:53]Next up, what do we got here? Next up, rookie says, "Our database is in readcommitted isolation level. We own both the code and the database. I want to enable RCSI. It's my understanding that possible new concurrency bugs will be in multi-statement transactions.

[00:17:12]Is this correct?" Oh, wow.

[00:17:19]Okay, here's the thing.

[00:17:23]There may be your job is on the line, right? You don't want to give the wrong answer here. It sounds like you're about to go in front of your developers and your managers and you're about to say something like the only queries we need to worry about are statements or transactions with multiple statements.

[00:17:42]Don't do that.

[00:17:44]You might be right. I don't know. I have never personally even looked to see if single statement transactions could cause problems. If you wanted to do that, here's what you go do. Go to chatgptemini co-pilot as Sirly Dev seems to like to use. Go to your favorite AI product of choice and say, "Here's the deal. I want to prove that if I change from readcommitted to readcommitted snapshot isolation, a single statement queries between single statement transactions will cause problems because you're coming at it from the way that I believe they won't cause any problems. Is that true?

[00:18:29]If you tell that to an LLM, the LLM will believe you. the LLM will be like, "Of course, you're attractive and you smell great today." Like, it's what you want to do is you want to come at it from the exact opposite angle because these tools will agree with whatever you say. You want to tell it that you want to prove the thing you disagree with. say I need to prove I need a blog post or TSQL code sample whatever that's going to show how two single statement trans or two single statement transactions can behave differently under read committed versus RCSI do that you're probably going to get an answer let's try it together and see I'll share my screen and we'll go hop over into co-pilot or into why did I say co-pilot see dev's got me like programmed Now, surely Dev's getting me programmed. Now, I'm going to fix that.

[00:19:25]The green light thing here. Hold on a second. That's going to drive me crazy if I don't fix it. So, let's go fix that. I got a couple of green lights in here that usually seem to fix that.

[00:19:34]Yeah, that pretty well did it. Um, okay.

[00:19:37]So let's say under um I want to write a blog post proving that in SQL server when I move from read committed to read committed snapshot isolation RCSI.

[00:19:58]um that I can have problems when two single statement queries like one update, one select, one delete, etc. um can have different behavior interacting with each other than they did under read committed. Um any valid TSQL is uh acceptable uh index hints, query hints, uh isolation level settings, etc. Um and then uh please give me a demo query I can run to show this happening. Um this is such a useful technique uh to probe edge cases. And when we were talking earlier about what chat GPD knows about me, I use this technique all the time when a client will say, "Could this ever happen?" And it man, you know, I like sometimes I can visualize and go, "Oh, yeah. I can see how that would happen." But this is so much more cool.

[00:21:08]All right. So, let's say um [snorts] so here's a clean two window deal. It says create table users. It knows that I always use the Stack Overflow users table uh for all of my demos uh because it's really simple. I can re uh reuse it over and over again. Um so insert values one. Oh, a trigger.

[00:21:29]Oh, that's interesting. Update users.

[00:21:32]I'll do that. One blocks the other. Oh, that's true. Oh, that's genius. Um wow.

[00:21:42]Oh, I I never would have come up with this as an example. So, what they're showing is is that if you do the insert while the insert is running before under read committed, anything that tries to modify this first user that is still locked, the update down below because it's going to try to update this user.

[00:22:06]the update down below would be blocked by the insert that's still running. But when you switch over to the new RCSI re I say the new I mean 2005 21 years ago when you switch over to RCSI if you do the insert uh uh up there at the well we'll see test two in reset the row go insert it and then do the update and oh it's doing two updates now it's kind of janky but it gives you the the idea returns the previously committed version so that's pretty Cool.

[00:22:42]A select. Okay, that's pretty. An update and a select. That's Yeah, I love it.

[00:22:48]That's totally true. So, there you go.

[00:22:50]So, there you go. There's your answer to that. Where's our Twitch chat? There's the button. Um, that you absolutely can have different behavior between a single statement transactions. I just love that. Now, having said that, having said that, the reason I said that I've never had to look for that before, that that it wouldn't be the place that I would go. Having said that, the thing that I usually tell clients is let's focus on transactions where you have a bunch of stuff going back and forth, hitting the same tables, uh, or getting variables and then using those variables later on inside the transaction. Let's start there with our analysis because usually it's easier to find those kinds of problems wi in those situations.

[00:23:37]Let's see here. Next up, Andrew says, uh, "Hi, Brent. Do you have a blog post or a video that goes over the advantages and disadvantages of different databases? For example, SQL Server versus MySQL versus Postgress. I know sometimes through your training it gets sprinkled in." No. So, the reason why I don't is I don't know all of those databases well enough that I could have a a good informed opinion. Another reason why I and I will say about specific features from time to time. Um, another reason why I don't say it is is that really that I don't think that my you would add any value there in me saying here's here are the different pros and cons of each one based on that.

[00:24:17]here's what I would recommend. Because when you, you and I, we're almost never in the position to be able to recommend a database.

[00:24:29]It's so rare that developers will come to us, hey Vlad DBA, welcome to the club. Um, [snorts] it's so rare that developers would come to us and say, hey, I'm thinking about building a new application. Which database should I use? Usually that's that's not even a like they're just like real quick I'm just going to uh give one or the other.

[00:24:51]Um and that's kind of the end of their decision point.

[00:24:55]So uh because of that I don't think it would be very valuable. Plus it changes so quickly based on a point in time the when I when you choose a database here's and I also don't care about sports arguments like my team is better than your team. All the teams suck. you know they go back and forth but really there is no set it and forget a database that just works and is cheap you know it just doesn't exist um and somebody will say well what about my SQL or what about Postgress if you use them at any kind of scale you will very quickly see that they are not set it and forget it either um so uh what I would say is is what is most of the team comfortable with what is most of the team already used to using that is the one that will give you the most likelihood of success regardless of what you're doing with the tool.

[00:25:48]Next up, let's see here. My tea got cold says, "I remember that the release of SQL Server 2022 was quite rough." It was. And that people were right to avoid migrating for a while. Are you seeing any of the same signs with SQL Server 2025? No, I am not. I have at least seen nothing but good things. Now, there have always been bugs, you know, right in the first cumulative update or two. Uh, but 2025 is the release that I've seen the most people the happiest with quickly.

[00:26:16]Um, some of that I think is because the clients that I've had who've jumped to it were on 2016 or 2014 and those versions there were so many improvements between 17, 19, 22 and 25. If you just can get to the point where you can turn on the newest compatibility level, query store, automatic plan correction, um you get so many uh improvements that the server is just working behind the scenes gradually implementing different query plan improvements, adaptive memory grants, all kinds of stuff. SYON says 2025 is real sorry uh for or real been real solid for us. Not real. Sorry. But yeah, I I highly recommend it. If you want more information on that, search for Brentosarr, what version of SQL Server should I use? And I have a blog post that I go back and update every 6 months or so. Uh, with the state of the art at that time and I already recommend that for most people, uh, SQL Server 2025 is the version that you should be installing. I would not install 22 today. I would not install 2019 today.

[00:27:30]Uh, what the hell? Uh, no. Oh my, this camera just said no space on the memory card. Okay, great. Well, that's fantastic. That's what happens when I record 4K 60. Um, thankfully it still works on the streaming, though. [sighs] Uh, next up, Lwood Blues says, "Tempdently gets close to running out of space.

[00:27:53]Before we move it to a larger, more expensive Azure drive. Is there a good way to see which queries are using large amounts of temp DB? Yeah, if you're subscribed to my classes, hit the fundamentals of temp DB class. Um, and I go through all the different consumers.

[00:28:09]The thing that is going to catch you by surprise is it's not necessarily a query. It can also be uh processes inside of SQL Server. For example, things like the version store. So, the check out fundamentals of Tempb. Now, if you're if you're sure it's a query, go run SP who is active or SP Blitz who.

[00:28:33]These are queries that will tell you which query's running right now. SP who is active or SP blitz who. They're both open source. They're both free. Uh real low overhead. They both support logging to tables. Um so you can set up a job that runs say every 15 minutes overnight. And the column that you're looking for is temp DB allocations. That will show you the number of AK pages that the query has allocated in temp DB.

[00:29:01]If you don't see any queries with large numbers of temp DB allocations, then it's probably an internal SQL ser I say internal but it's something else not a query that SQL server is using. And for that check out my fundamentals of tempdb class. Yeah. And then let's see let's do one more. Uh T. Earl Greyh hot says after adding a filtered index recently, I found that the query did not use the filtered index but used existing indexes more efficiently. In this case, would it be better to add a filtered statistic or leave the filtered index? Um, I would try the filtered statistic to see if it works. Uh, drop the filtered index, add the filtered stat instead, and see if it works. If it works, that's lower overhead than having the filtered index because you don't have to maintain it on every insert, update, and delete.

[00:29:52]Filtered stats would be updated later asynchronously with your stats, updates, jobs, or your auto stats. Uh so, and it only changes one AK page worth of data.

[00:30:01]So, there you go.

[00:30:04]All right, we've been going for uh quite a while there. My camera ran out of memory space. That's just absolutely unbelievable.

[00:30:10]I'm doing my next week I teach live classes again for the first time in a long time. Next week I have my fundamentals week where I'm teaching all new versions of fundamentals of index tuning, query tuning, and server tuning.

[00:30:23]And this is the first time that I've done multi-day training classes online in a long time. So, I'm going through and setting up my office, making sure that uh everything works. There's all these little bumps and hiccups that I haven't uh touched in quite a while.

[00:30:36]Thankfully, I don't I don't record them with the camera on uh that or the memory card on that particular camera. The reason that I record inside that camera that I'm looking at is to get the best resolution possible cuz when I stream it out through y'all on YouTube and uh Twitch, it's the the quality degrades a lot. Um but so first world problems, there's always something. Technology, right? Oh jeez. It's always something.

[00:30:59]So hopefully y'all had fun and I will see y'all on the next office hours.

[00:31:04]Adios.