bit Obscene Episode 1: What Developers Need To Know About Transactions
In the inaugural episode, Joe Obbish and Erik Darling talk about how developers use and abuse transactions, and some surprises you might run into for them.
Links:
- https://erikdarling.com/the-art-of-the-sql-server-stored-procedure-transactions/
- https://erikdarling.com/batching-modification-queries-is-great-until/
- https://www.youtube.com/watch?v=CuZSoZb8ziE
- https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/
Video Summary
In this video, I delve into the world of SQL Server transactions and share insights on what developers need to know about them. Joining me is my special guest co-host, Joe Obish, who brings a unique perspective from his time in the Wisconsin State Penitentiary library. Together, we tackle common misconceptions and bad habits that can lead to data loss or corruption. We explore scenarios where simple delete-insert patterns can fail, emphasizing the importance of using transactions to protect your data integrity. By understanding these potential pitfalls, developers can write more robust code and appreciate the value of transactions as a safeguard in an unpredictable database environment. Whether you’re just starting out or looking to refine your TSQL skills, this video offers valuable lessons on handling uncertainties and ensuring data reliability.
Full Transcript
Hello and welcome to the very first recording of our podcast, probably yet to be titled. And today, my special temporary guest co-host is Joe Obish. And Joe Obish, if you would like to introduce yourself a little bit, so people who may have forgotten who you are and the break you’ve taken from publicly berating SQL Server, why don’t you tell the nice folks why they should pay attention to you? Well, as we said before, this is part of my public rehabilitation. I even, you know, dusted off my, my most impressive SQL Server shirt, right? You see the speaker here? Yeah.
I started SQL Server work in 2011. I remember when I used to think that wasn’t very long ago. You know, now I’ve got like gray hair and everything. It’s terrible. I’ve done a lot of great performance tuning. I’ve worked on servers as small as four cores and as large as 108. I’ve done some columnstore ETL work, some OLTP.
Don’t undersell that columnstore ETL work, you bozo. That’s some of your most impressive stuff. I know. So, the best presentation I ever gave and will ever give is on that subject. It’s way more impressive than what Eric did. Eric and I are going to do here. So, you know, if you make it to the end and think, you know, that was okay, but I want something even better. It’s on YouTube.
Eric and I’ll put, I’ll put the link in the show notes. Okay. Cool. So that people don’t have to go figure out how to spell your name and then figure out what you were talking about on YouTube. Okay. We’ll make, we’ll make that easy on people. Okay.
What, are you done? I mean, yeah. I don’t know. Did I forget something important? I don’t know. You tell me. It’s your, it’s your bio. You can, you can say what, you can keep going, say whatever you want.
No. No. No. All right. Well, I forgot to mention Joe is live from the Wisconsin State Penitentiary today. He’s in the library right now.
My name’s Erik Darling. I am a consultant extraordinaire. I do all SQL Server performance tuning work. And I have a blog and a YouTube channel. And those links will be in the show notes too, just in case anyone who finds me was unaware of the fact that I have, I have those things. So that’s fun today. We’re going to be talking about what developers need to know about transactions, because apparently developers really don’t know anything about transactions.
That’s been my experience that many developers when, when we’re, when I’m working with clients are using transactions and more like abusing transactions. And today we’re hoping to share some of our professional wisdom about transactions so that hopefully the developing, the developer community at large can start using transactions and stop abusing transactions. So Joe, take it away. And I’m just here for color commentary. Joe’s going to provide all the, all the audio genius for this one. So let’s, let’s go for it.
Joe, you’re going to share that screen or you’re using a nice way of, you know, saying that Eric’s making me do most of the work. Yeah. As usual. So specializing, you know, there’s, there’s not knowing a lot about transactions. And then there’s like, like knowing the wrong thing or like having bad habits.
Like I’ve worked at companies where the things developers, you know, they, I think they know they’d be better off. Knowing nothing. The primary relationship that developers had with the transaction log was it’s important to write as little data as possible to transaction log.
That was their like overriding consideration for how they wrote the code. And, you know, to be fair, I mean, there is some value in that, right? If, if you can do the same amount of work in a way that writes less data, that’ll be good for performance.
But this was taken to an extreme, you know, even to the point where in a big, you know, complicated ETL process, they would always batch their transactions. Both into the final destination of the recording database, but even on the staging database, which was, you know, a simple recovery model. It’s supposed to be a temporary workspace, but they were, you know, they’re batching those transactions because.
They were fighting against the transaction log. They weren’t working with it. They viewed as their enemy.
They, they, they wanted to give it as little data as possible. And that caused a lot of problems. Performance is bad. Scalability was bad.
Sometimes they would try to add new features to the ETL and they just couldn’t. They had some process. I forget what it’s called.
It was like some fixed process where, you know, if the ETL process for a table was, was canceled midway, they would then have a bunch of code that would try to undo the partial progress that was made because, you know, they, they, they weren’t using transactions in the way that they should. They were fighting against the whole concept. And I’m sure you can imagine how complicated and horrible that procedure was.
I sure can. So, so where, so in, in, in cases like that, where, you know, they just wanted everything to roll back, what, what, what prevented them from using something like set exact abort on so that they got, they got a more, they got a better rollback scenario from things. It was because, you know, to take the, one of the, well, it was because they would do something like, you know, say you’re loading a million rows into this table.
They’d load like a hundred thousand at a time, each in their own separate transaction. Oh. So it’d be something like, you know, if they load half a million, but then the customer cancels it or something, they would then have a fixed procedure that would run, which would, which would delete those 500,000 rows that they had inserted.
Oh. So yeah, like it was really something. That is, that is charmless for being honest.
I ended up. So that among other things. Makes me want to say that, you know, to, to any developers out there who, who are, who are listening or watching transactions are your friends.
They’re not something to fight against. They’re there for your benefits. Um, you know, it’s part of the way that the database can serve you.
Um, it, you know, it’s a way to protect the data and make sure that it is there. It’s a way for you to get the results you’re expecting. And it’s a way for DBAs and customers and your boss to bug you less.
Cause you know, if you write code and your code loads data wrong, well, that can be a big problem. So, you know, if you take, if you take nothing else away from this, I would, you know, leave with the sentence of transactions are your friends. Um, you know, and you know, like Eric has a lot more friends than me.
So, you know, like I’m kind of reduced to thinking of, you know, SQL Server transactions as, as being my friends, but, um, that’s, that’s, that’s, that’s just how it is. That’s, that’s why Joe names all those transactions. Oh yeah.
That actually is a thing. That is actually a thing. We’re, I, uh, making an executive decision that is not important enough to cover here, but you know, maybe in part two. All right.
So, all right, I’m going to try to share exhibit a, yeah, let’s do it. And. All right.
Can you see, uh, some. I see. We’re starting to share. We got some purple dots. Oh no. You know, it worked so well when you weren’t recording.
It really did. All right. Yeah. Let’s try it again. Um, maybe, maybe you’re, are you, are you connected to a VPN or something? No, I’m not connected to VPN.
Is it working now? There you go. Look at that. Worked instantly. All right. Great. Um, so Eric, this is going to be used in the quiz. So you need to pay real close attention to this.
Um, so let’s say, you know, your, your, your test or a civil procedure that either creates new record. If there isn’t one or updates an existing record and you, you know, you’re knowledgeable enough to know that that kind of pattern is called an upsert.
You, uh, do some searching on Google. You find yourself reading some confusing. Blog posts about like what the right upsert pattern you should use is. You know, it uses all kinds of things.
You don’t know if you’re not really big on reading in the first place. So you think, okay, whatever, I’m not going to do updates. I’m just going to delete the record if it’s there and then insert it. I’ll solve all my problems.
Now this code is kind of dangerous where if the procedure fails for whatever reason after the delete, but before the insert, you, you’ve then lost data. Mm-hmm.
So Eric, it’s a time for your quiz. Time for my quiz. Oh boy. What are the reasons you can think of as to why this procedure could fail after the delete, but before the insert?
Uh, I would say maybe if there is a trigger or a foreign key or something else that, uh, that would cause a conflict with the, with the delete. All right.
I actually didn’t have those. So you’ve already passed. Uh, can you think of any other reasons why? Any other reasons why? Um, let’s see why, why the delete would fail and cause the insert. The, the, the, the delete would succeed, but the answer would fail.
Uh, well, uh, we’re deleting an ID. So my, like, you know, the first thing that would come to mind would be some sort of constraint violation, but you know, uh, I guess, you know, if you’re already deleting, like, so I’m assuming that ID is the primary key of the table.
So if you’d already deleted that and then you went to insert the same value, then it wouldn’t be a primary key violation. Uh, yeah, I can’t think of, I can’t think of any quick reasons why the insert would fail. Maybe a deadlock, maybe, uh, maybe, uh, I mean, I’m going, I’m going back to triggers and foreign keys, but that’s, that’s, that’s, that’s about what I can come up with.
All right. Well, you have passed. Congratulations.
So, you know, just to go over the scenario again, it’s, you know, if, if, if we’re trying to update an existing record and the delete succeeds, but the inserts fails, we’ve then just wiped out that old record. So we, we’ve lost data. So that’s why this code pattern is dangerous. Now in terms of, you know, like I’m a pessimistic person when it comes to writing TSQL, I try to view it defensively, you know, like assume the procedure can fail at any point for any reason. And if it does make sure that the data you expect is still in the database.
So the things that I thought of were, um, you know, you could just have a hardware failure or a power outage. It’s a bit severe, but sure. I mean, Hey, I mean, you know, these, these things happen, right.
Janitor’s mopping knocks the power cord out. Yeah. No, I mean, I mean, Hey, it’s, I mean, you know, these, these, these, these, these things happen, maybe not all the time, but they happen. That’s true. And you wouldn’t want to lose your valuable data in table one. Yeah. Right. That would be a death blow for the business.
Um, maybe some DBA doesn’t like you and he, uh, kills the process. Um, sure. Long running or worse. They have, they have an automated script that looks for blocking and says, if there’s any blocking whatsoever that kill that speed.
I mean, and I’m, I’m, I’m sure that, uh, someone’s done that. Um, of course you could have a lock wait for the insert and you hit a client, uh, timeout. Sure. Um, someone could be patching windows or SQL Server, or, you know, maybe, maybe the trans or maybe 10 TB fills up and they just restart SQL Server all together to, you know, fix it. Cause that’s how you fix that. Yeah, totally. Oh, a failover.
Yeah. Uh, your transaction log could fill up, you know, um, if you make it all the way to the end, then hopefully, you know, developers watching won’t be causing those issues anymore. Um, yes.
There could just be some bug in, in a SQL Server that that makes it fail. Sure. Like you have a non yielding scheduler or just some, you know, just some horrible error. Um, yeah.
So I think, I think, I think what you’re telling people is that sometimes when they’re, they’re writing code, they need to think out something. So I think that’s what, what’s inside the code is what could go wrong with, with, with stuff, with stuff that’s happening in there. There is a greater world out there.
Which I think is what tripped you off. Cause it seems like you were focusing on just the code. Yeah. Yeah. No, no, no, no, no. I was, I was very focused on stuff that’s going on in the database, but sure. You’re, you’re absolutely right.
I was, I was in the developer bubble. So it’s my, is that the right word? My, um, I think so. Yeah.
Sounds great. I mean, those, those, those, those, those aren’t the kinds of words that we should be using. Um, and you know, like I’m sure all the developers who are, you know, dedicated and, uh, ambitious enough to watch this, they all write perfect code, but you know, someone could modify your code later. Or someone could modify the, uh, table schema.
Like, sure. Like what if, like, what if someone shrinks the size of that, uh, fire car column? Yeah. You know, whenever, whenever there’s a problem with my code, like to say, it’s not, it’s my, it’s perfect code in an imperfect world. So, you know, we just have to set the deal with the imperfections around us.
So, you know, even the most innocent looking at procedures, like you can fail for all kinds of reasons. Sure. At any time, at any moment.
And. Just like in life. Transacts. And transactions, you know, they can help you. All right.
So this is our improved code. Now, admittedly, this is not code that you should be using as a template. There’s still lots of things to complain about. Especially the format. You know.
Um, that aside, you know, at a bare minimum, I’ve added a transaction. So now assuming like, you know, reasonable configuration, like if the delete succeeds, the insert fails, that delete gets rolled back. And now you’re not just losing data because of, you know, some horrible gremlin in the database made your code fail.
So this is, this is why I say that transactions are your friends, you know, like they protect you from losing data. You know, there are a lot of bad things that can happen at any time in a database. Um, you know, and transactions can help you.
They can protect you and they can make your job easier. Sure. Um, now, of course, you know, if you’re doing this for real, you should look at Aaron, uh, Bertrand’s absurd. There goes your rehabilitation.
There goes your rehabilitation. Just, uh, use that. Aaron Booth Baronski. Hey, look, I’m like the president. I’ve had a stutter my whole life and I’m, uh, doing my best, you know, even if you have a stutter, you can still, you know, fly to such great heights as being a speaker.
Speaking at past summer 2019. That’s right. You got it.
The, the, the, uh, last good past summer. That was a great. It was a great one. Yeah. You were there.
I was there. I was there. We, we hung out. It was wonderful. All right. So Eric has passed the quiz. Um, the whole quiz.
Yeah. Like I said, you, you, you only had to get one. Oh, okay. Well, you know, I do like to set a little bar. Um, so switching gears here. Oh boy.
You know, sometimes you might find yourself running a stir procedure in production, you know, as part of troubleshooting. Mm hmm. Um, or really just anywhere else. Um, or really just anywhere else.
If, if that procedure fails. Your transaction might not roll back. And you can find yourself in a place where, you know, you have an open transaction without realizing it. And if your database isn’t using accelerated database recovery.
That can effectively cause a production downtime. Because while your transactions open, you know, if you’ve modified any data, you can’t have log reuse. You know, the transaction log can’t wrap around and roll back and rewrite over your changes.
Mm hmm. So, you know, like, I mean, I, I’ve definitely experienced this. I’m sure Eric has too, where, you know, someone has, someone has open transaction. They don’t know about a, a, a, a few hours pass and suddenly all kinds of code are, are, is, is just failing across the board.
Yep. Um, you know, the only, the only thing I can really say here is, you know, if your code fails, make sure you don’t have an open transaction. Um, how would one do that, Joe?
What are some techniques? They could close the SMS tab and it’ll, it’ll, it’ll give you a warning about, oh, you have an open transaction. So you want to close it.
You know, you could just type rollback transaction. Sure. If you have one to close it, if not, it fails, but there’s a, no big deal. Um, that’s what I would come up with. Or, but most, most, well, I mean, most developers don’t, or rather most applications don’t function by developers running queries for people in SSMS.
So, uh, I would. I’m talking about a, a, a, a, a, a. Oh, like an ad hoc scenario where.
Yeah. Someone’s doing something. Sure. Yeah. Uh, I mean, if you’re gonna, if you, I mean, so I, I think that if you’re going to use a transaction, uh, the things that you need to do to prepare that transaction for success are, uh, to use set exact abort on. So that, that ensures that everything rolls back, uh, not just the, the thing that failed.
Uh, I, and I also think that you need, you need to, uh, have a catch block on your code that if there’s an error, you can, you can, so that you, when, when, if there’s an error, I mean, not saying that errors are inevitable. If there’s an error, uh, you can make sure that, uh, you, you, you have a rollback command in that catch block and that you also have a good record of what error occurred, uh, by, by using some of the built-in error functions. Uh, and, uh, I think that, uh, you know, the, the throw command is mighty helpful.
Uh, you know, if you can throw, throw a raise error that maybe gives you some additional, so like if your code is using a loop or if your code is, uh, you know, setting any, uh, variables or using any parameters that you can, you can use, uh, raise error to sort of give you which, uh, which, like, part of the loop or like which, uh, parameter or variable values were part of the error. I think there’s a lot of stuff that, uh, people don’t put into code because, uh, it’s a lot of extra typing, but that all that extra typing can, can really, can really save your hide when, uh, you do have, uh, unexpected transaction failures. I think you’re absolutely right.
That’s why you’re here. The encounter with is though, you know, oh, well, but I have a thousand procedures and none of them do that. And, you know, I couldn’t possibly update them to do it all, or I’m calling some horrible third party procedure that I can’t modify. And, you know, they, they haven’t seen Euro wisdom, you know, they haven’t seen the light.
So they don’t know to do those things. Uh, what do you think about setting transact abort on as like a default connection setting? Uh, I think, you know, I, I wouldn’t be, I wouldn’t, I wouldn’t say no to it.
Uh, I wouldn’t say no to it out of hand. Uh, it might be overkill for some procedures, but, you know, for the, for the, even for the procedures that it’s overkill for, like, who cares? Like, like, like, I’d, I’d rather have it there to safeguard the ones that it’s not overkill for than to, you know, uh, have it, have it not on for ones that, you know, like, if it’s just a select query in a procedure, whatever.
Right. Like, or if it’s just like a reporting procedure, like, uh, then whatever, like who, who, this, if it, if like, what, like inserts to it, like the temp tables or whatever are going to roll back. I don’t care like, like for, for the, like, I view it as a good general guardrail for an application.
Uh, and you know, you can, you can make a choice in certain procedures to turn it off if you, if you really want to get crazy. But, you know, I think, I think that it would be a good general guardrail for, for most applications that I see, especially ones driven by third party vendors who are generally clueless as to how to work with SQL Server. If you had it on at the connection level, would you still add it at the top of every new procedure you’re making?
Uh, I don’t know. Uh, that might be a whole lot more work. I mean, it’s sort of like, sort of like if you think about, uh, you know, the difference between, so like, let’s, I mean, let’s say you had RCSI and snapshot turned on.
I wouldn’t go turn snapshot on for procedure unless I absolutely need it. Like, unless something specific about that procedure needed, like the specific behavior of snapshot isolation. Like I wouldn’t, I wouldn’t go and put that at the start of every procedure as well.
So like, you know, uh, I can’t think of a great reason to, to go into every procedure if you’re setting it on at the connection level and also set it on there. But, you know, maybe, maybe you’ve seen something interesting that I haven’t. No, I mean, I was just, you know, I find it a little bit odd that it seems like most of the online discussions about this are you should add to every procedure.
And very rarely do people say, oh, we’ll just turn it on at the, uh, connection level and then you’re done. You don’t have to do anything. Um, you know, like to your point, like technically you could get some bad behavior.
I think if you have, if you have an application already going, like you might have some badine error, like, you know, someone has some, someone tries to like close a cursor twice. Cause they just have something copy and paste or something. Sure.
And, you know, now that would be unable to execute. But I think in general, like, you know, it’s hard to imagine many scenarios where you want that setting turned off. I did do that once.
You, uh, should I, uh, talk about that? Yeah, go for it. Talk about that. So you can, you can talk about why you turned it off, turned it off once. And I’ll tell you, uh, how I use it. So you go ahead.
So, so this is the, uh, transact or what’s it, what’s the actual word? It’s kind of hard to say it’s a transact abort. Yeah. Exactly.
So, so for somehow, somehow X, somehow trans got turned into X. So it’s exact abort. Maybe it’s execution abortion. I don’t know. I’m pretty sure it’s exact abort. Cause you know, I mean, you wouldn’t want to, we wouldn’t have to type out trans every time.
Yeah. Right. That’s just too much. Exact abort. Um, so, you know, this is one of those things where, you know, I was reading, uh, a very lengthy and complete blog post about transactions by a, uh, famous European whose name I actually never known. Is it Erlen Somerskog?
Yeah. Yeah. That’s not, it’s not that hard. Well, it’s not that hard because you’ve, you’ve been there a bunch of times. You’re a very well-cultured person. Well, also, also it’s just, it’s just letters. Somerskog.
I mean, all words are just letters. Fairly easy to figure out at this point in life. All right. Well, like, like, I always have restraints. I have you here to pronounce the European names, so I don’t have to. Okay.
Um, so I, I think I ended up doing a thing that he explicitly warned against, but you know, it’s, I haven’t gotten fired yet. So, you know, it’s true. Um, so my scenario was I, I had a DML trigger and if that DML trigger failed for some reason, I didn’t want to roll back the, you know, insert or update. Sure.
You know, like, like we viewed it as better for the business to very occasionally not have that trigger fire compared to making the trigger failure cause the data not being inserted. Sure. So the way I did that was, uh, it was, it was horrible.
Um, I explicitly turned off exact whatever it’s called. Yeah. Exact. I used the transaction save points. Okay.
I, that I then checked if it was possible to roll back to the save point cause in some cases you just can’t. Mm-hmm. So, you know, for a certain small class of errors, if that trigger fails, we just roll back to the save point and we don’t cause the parent insert or update to fail. And it’s reasonable.
It, I don’t know about that, but it seems to work. From my point of view on this, you were tasked to do a somewhat unreasonable thing in SQL Server and you had a reasonable reaction to that set of unreasonableness. Sometimes when you have to do unreasonable things, they require unreasonable techniques.
So, uh, which, which actually like cancels out like two negative numbers. It makes the unreasonable technique reasonable. That’s a, that’s a very generous view to say, but it’s actually a great segue into my next one, which is bashing transactions in the database.
Okay. Um, I like had like a fire extinguisher analogy that I, I like really want to use, but it’s, it’s not quite perfect. I’m going to go for it anyway.
That’s fine. I don’t care. You know, a few things in life are perfect. I feel like batching transactions is often like using a fire extinguisher, you know, like, like it solves the immediate problem. But most of the time you might have a mess.
Yeah. To, uh, to a cleanup later. Um, I think that it’s easy to fall prey into the wrong mindset. Like, you know, you’re a developer, you’re writing your code, you don’t want people to bother you.
Some evil DBA says you’ve met developers before. Messages you and says, Hey, you know, you’re a code, fill up the transaction log. You, uh, you, you need to fix it.
You, you, uh, Google it. You find many other developers who have suffered in the same way you have. Yeah. And, you know, there are some smart people out there. It’s, oh, you know, instead of doing one big transaction, you can do a bunch of little transactions.
And then they just make that change. Yeah. And I feel like in some cases that’s perfectly correct. There’s no downsides, but I also feel like it gets into the minds of, you know, transactions are my enemy.
I need to work around the, uh, the, uh, the, uh, transaction log. So like, I’m a little bit concerned about the whole thing with that respect. Now, in the blog posts of yours that I’ve read, I remember you always saying something like, you know, it’s important to make sure that you are using transactions when you need to.
Like if, if a set of, you know, demo statements, either all needs to commit or no needs to commit, you need to use transactions. So in other words, you know, like don’t use batching when it isn’t correct from a business perspective to do so. Sure.
Well, I mean, my, my, my point of view on batching is that, uh, like there are times for several reasons when it is absolutely necessary to use, uh, any sort of, you know, archival process that you don’t have a partition table for that. You can quickly switch stuff out, uh, any sort of data migration process, uh, you likewise probably want to, want to batch. And, uh, anytime that like, so like there, there are times when I’m performance tuning queries that do modifications.
And even if a lot of rows aren’t being deleted in a, like by the, by the, you know, by the, whatever, uh, you know, update, delete, uh, because of the way the database is implemented, um, the, the slowest part of the plan is the part where you actually physically change the data. So it could be over indexing. It could be, uh, you know, foreign keys.
It could be triggers. It could be any number of things. It could just be like, you know, you’re on, you’re in like an Azure managed thing with like, but hardware or like unfair caps on the transaction log that makes throughput to it really slow. You know, there’s like, there’s like lots of reasons why like the modification is slow, right?
You could just have your like slow ass transaction log. Uh, so there are a lot of reasons why that could be slow, where I might even take, like, I might even take something that’s only changing like 2 million rows and batch that up into like, you know, 500,000 row chunks because like the actual data change part of the, of the, of the plan is the slowest part. So it sounds like you’re getting back into your, you know, you’re asked to do an unreasonable thing, like update millions of rows on, um, low end hardware as we like to call it.
So you got to respond. That wasn’t the analog. So, um, I try to always, I think the way that I would summarize it is if you’re going to batch your DML statements, make sure it’s okay.
If only some of the batches happen, because as we discussed earlier, you know, you’re coping to fail for all kinds of reasons. Sure. Some of which aren’t your faults at all.
Sure. And like, I’ve had, I’m sure there’s scenarios where it, you know, it would be okay for some of them to happen. Some of them that’s happened. Like there are some really great use cases for batching. Um, ones that I came up with are, you know, you’re deleting old data from an archive from a log that no one could be looking at.
Yep. Or you’re, you’re creating a new column that the application isn’t querying yet. You know, there, there’s no reason to load it all at once.
Mm-hmm. Or like backfilling the column, you mean. Yeah, yeah, exactly. Or, you know, you’re, uh, you, uh, have a, you, uh, have a downtime, you have a maintenance window. There are no end users during that wonderful brief period of time, you know, do all the batches you want.
Or, you know, like if you have to, you’re maybe doing some horribly complex thing on some, uh, local temp tables. I mean, you know, if batching is the way to do it, then go ahead and batch it because, you know, no one can see that data. Yep.
So, you know, all those scenarios share one thing in common, which is there’s no end user who can see the data while it’s being processed. Sure. I think if that’s not true, you need to really, you should think critically, you know, like, is it okay for them to just see like half the data if they just still happen to query at, you know, an unlucky time? You mean in the, in the unlikely event that the code isn’t full of no lock hints anyway, Joe?
They could just see whatever? Uh, yeah. See, it gets weird out there. So, I mean, again, this is, this is sort of the value of optimistic isolation levels where, you know, these things come into play a little bit.
I think it’s, I think though, I mean, I’m not a lacking expert, but even if people are using your recommended and favorite isolation level, um, RCSI, like, aren’t you kind of creating an invalid snapshot to a degree where, you know, the snapshot they might get is we’ve batched half the data as opposed to all or nothing. So, I do, I do feel like batching can be a little bit defeating even with RCSI on too. Sure.
Uh, you know, I mean, you, you, I mean, you’re, you’re right. If, if, if, you know, if it’s a situation where like you just like, we’re like, you know, if, if a user sees like the first part of your batch, but not the second three parts of your, like the next part looks like three parts of your batch or whatever, then, uh, yeah, that, that could, that could potentially be awkward. But at the same time, I think one of the values of batching is that, uh, if the user hits refresh enough times, they’ll, they’ll, they’ll eventually see the right data, which is.
Yeah. Yeah. Eventually might be wrong time, but yeah, no, it’s like, you know, I, I think patching at this place, I have seen a few blog posts that don’t even talk about, you know.
Alternatives. Well, like, you know, the problem you can run to, which is then you just use part of the data. Right.
Maybe that’s a really big problem. Right. Well, I mean, I, I, I have one. It isn’t always, but you know, it could be. Yeah. But like, look, generally the, the stuff that I write that does batches, uh, does batching for, uh, at least very specific sets of data where it doesn’t matter if a, if, if a user were to see like a, like a partial thing.
But I also do have a post about, uh, how like batching modifications can be great until, you know, one of the batches fails and then you would need to undo everything you did before. And in that post, I go over using like the output clause with your modification query to like sort of save off the changes. So you can undo those changes.
Uh, if one of the batches fails and you want to like go back and unbatch things. So I am, I am, I am sympathetic to your, to your point that, you know, batching stuff that could cause incorrect data would be bad, but. And, you know, I usually don’t mess.
I usually don’t do. I usually don’t do. Listen, in an ideal world where we can think of transactions as our friends who are there to help us, you know, that entire problem goes away. Now, as we’ve alluded to, we’re often not an ideal world, but, you know, um, like one of the, like, uh, and this is a topic for another day.
Cause we definitely run out of time, but you know, when I was talking about that ETL story from before about how they’re batching even staging great. Yeah. And, you know, we ended up rerunning the entire application and one of the driving principles was, you know, like let’s use transactions in a way that’s helpful for us and for the data.
And like a lot of the. The years developers have, Oh, we’re going to fill up the transaction. You know, like we were updating like billions of rows and in some cases we would batch by partition, but that was it.
Like there wasn’t any cheating. Right. And that, you know, I don’t think there like was a case where the customer transaction like filled up.
Yeah. Well, I mean, we, we, not every, not every transaction log filling up scenario is the fault of a developer though. Like some people are just woefully unprepared for like the workload that their SQL Server is about to do.
They might have, you know, they could have, they could have like a hundred gig transaction log drive for like a 10 terabyte database. And that’s not going to go well under most circumstances. Or like, you know, they could be one of those foolish people who like sets, like sets like a max data size on their transaction log.
And that’s not the developers fault. Like, you know, like developers have to be free to work with data and the way that data is best, best worked with. And, you know, for whatever scenario the app of the application presents.
So I, I like, you know, not every, not every, you know, transaction log full error is like some developers idiot mistakes. Sometimes, you know, like there is, there are infrastructure issues or there are, you know, settings issues with, with the database that, that caused that. They are not like the developers shouldn’t have had to worry about.
You’re absolutely right. And that’s why you’re here. We had to, we had a little bit of work to do in that area too, because there were some scenarios, you know, for the detail thing, like customer had terabytes of data. Well, you know, like we would tell them, Oh, you know, you can have a transaction log max size of 20 gigabytes.
Yeah. So as part of our grand compromise, we like bumped up a little bit. Yeah. And, you know, like, it’s really, you know, not that big of a deal. Um, it reminds me of, you know, this wasn’t exactly a transaction log, but in a very important developer database, we would do an ETL and there is some comically small drive and it would, it would like always fill up.
Yeah. Cause the process to fail. Right.
We’re basically having like, um, daily failures. And it was like some 48 by drive. It just wasn’t big enough. Yeah. And I was, you know, trying to work with our it department who was very frugal. And they, uh, weren’t cooperating, you know, um, I, uh, hit my like five year tenure mark.
And I go, you hit five years, you, you, uh, get a gift, you know, would you like this bowl or this, like, um, this, this like leather portfolio thing. A bowl. I know a bowl.
And, uh, I just ignored it cause I didn’t want either. And I think my manager’s like, Oh, you know, you need to pick your gift. What do you want?
And my answer was, I, I, you know, I would, I would like to go to best buy, buy a hundred gigabyte hard drive, plug it in the server. And then that way, you know, we can, we can not have developer production go down every single day. And shortly after that, it finally cooperated and made the drive a little bigger.
Yeah. And the thing that the thing stopped failing. Yeah. So 41 gigs, you know, yeah. Maybe the things were unrelated, but you know, it’s, uh, something that can happen.
Um, yeah, no, totally. Uh, you know, uh, you know, infrastructure can be just as much to blame for, uh, things as developers can. And, you know, well, well, developers are certainly, uh, prone to doing strange things in the database.
Uh, there are, there, there are just as many, uh, you know, uh, either untrained DBAs or sysadmins posing as DBAs who, uh, can do just as many goofy and restrictive and, uh, harmful and offensive things to SQL Server. So, you know, it was, let’s not just, not just blame developers for this. Some developer out there is like, you know, I did everything right, but you know, this, this two gigabyte log transaction log is just not helping me.
You think there are people out there with, uh, counterfeit, uh, past summit speaker shirts? Uh, I mean, counterfeit in the sense that they probably, like they bought them from a goodwill store. Yeah.
I mean, maybe, yeah, sure. I mean, you know, not to be grim, but let’s face it. If you, if you were to die tomorrow, that shirt would end up in goodwill. Someone out there would be like, cool golf polo. I’m gonna have to.
Uh, update my will to make sure that doesn’t happen. Well, I don’t know who you’re going to give that to. I don’t think you have any friends who could fit in that. Are you saying, um, what’s that even mean?
I mean that, you know, you’re a, you’re a particularly slender man. Uh, not anymore. Uh, you know, you could, you could leave it to anime. Not anymore.
And the, the, uh, shirt still fits. All right. It’s stretchy. It’s a forgiving shirt. I think, I think most speakers have to be forgiving. Like when I was giving a company t-shirts at conferences, uh, like, like the, there would be like, you know, everything would be gone, but there’d be like a pile of extra smalls. All right.
All right. I don’t want us to get canceled on our first podcast. So I’m sharing some, uh, T SQL here. All right. Uh, so it’s possible to insert.
There is results of a story procedure into a table. I think everyone didn’t, doesn’t know about that. Um, it certainly has its perils.
Um, you, uh, can’t nest it, you know? So like, in other words, you, you couldn’t have the story procedure also do an insert into exact. So that just feels in there.
Um, in some cases you, uh, don’t have a choice. So for example, if you want to get the list of active trace flags, I think this is the correct way to do it. Yeah.
Right. Like there’s some DMV or anything. Right. You have to insert the DBCC’s trace stats results and do somewhere. Um, one thing to know is doing this will cause the entirety of that server procedure to be within a single transaction, which I always thought made sense. But by that, by that you mean the store procedure inside the exec, not the calling store procedure, the one inside the.
Yeah. Yeah. Yeah, exactly. Um, so this is something to keep in mind where, you know, you’re not like, you know, I suppose I should have had a, uh, another picture here to kind of illustrate it visually.
Um, you could just draw some arrows on this one. Um, you know, you, you’re effectively forcing. In this case, DBCC trace tests, which doesn’t matter to be in a, in a transaction.
So, you know, that could lead to. Unexpected behavior. So, you know, sometimes you have to use this like for. Trace flags or some third party procedure.
Um, sometimes I’ll use it for cases where. I need to use dynamic SQL to insert a new attempt table. I don’t want there to be like, you know, a million cash plans, like, like one for every session I did. Sure.
Um, I don’t, I don’t think that trap is always worth it. Um, but sometimes I’ll do it. Uh, do you think I need to explain that better? You want to explain that better? Which part?
Like, like, like why someone would. If someone has to load and do a local town to use a dynamic SQL. Why using incident to exact would be helpful for a plan caching point of view? Uh, are you, are you talking about like the, the, like the multiple cache temp tables thing?
Yeah. Yeah. Uh, that might, that might be a little bit much for this talk. We can, we can, we can, we can, we can do a whole thing about dynamic SQL and temp tables.
We can, we have, let’s not, let’s not, let’s not try to cram everything at once here. I mean, we, we, we still have some time left. All right.
Well, um, in any case, uh, sometimes you find yourself doing this and, you know, you should keep in mind that this will effectively force a transaction. Read my next.
Well, I, so for that, for that screenshot in particular, there, there, there are a couple of things that I would warn about and none of them is the table variable in this case. Uh, I will say though, that, uh, there are times when.
A table variable is in use like that. Where I will use exec with dynamic SQL so that I am not constrained by a serial execution plan for the select query inserting into the table variable, because sometimes those table variables get passed to store procedures as table value parameters.
And so like, I don’t want to like, like do the insert into a temp table, then do then go from the temp table to the, to a table variable to pass to the store procedure. Sometimes I’ll just hold onto the table variable, do the exec with whatever, and then, uh, do the exec with dynamic SQL and do the insert that way. So I get like, I get that awful looking, like, here’s your, here’s your select execution plan.
And here’s your parameter table scan plan. Uh, with this though, like, like the dangers that I would see for this is what if Microsoft decides to change DBCC trace status tomorrow. And all of a sudden you have to use like with table results to get back the tabular results instead of something else.
Or like, what if Microsoft adds a column to DBCC trace status and all of a sudden your, your insert starts failing. So I think one of the, like the two biggest perils aside from the transaction part for doing insert with exec is. If the store procedure definition underneath changes, but your table definition doesn’t change, all of a sudden you’ve got failures.
And, uh, yeah, that could be, it could be unfun for you. So with, with the, with the, with results that’s cause you can actually specify a subset of columns. Is that right?
No, no, like I’m saying with, well, with, oh, with results sets for a store procedure. Yeah. But who the hell is using those? All right. Well, I mean, I mean, like, I, I don’t think I have ever used a, I think I tried to use it once and it was a big failure. Well, then it was just like, eh, so.
Um, but to handle your snare where Microsoft improves DBCC trace status. I didn’t say improve. I said change. I’m, I’m, I’m, uh, I’m being nice about yourself.
I want to start my real estate. Oh, okay. Um, how would you write code that would protect yourself against that scenario? Uh, can you use the, the, the result set thing?
Like, does that do it? I’m sure you could, if you, uh, you could, as long as the store procedure, well, I mean, I guess would have to for this produces a single result set. Uh, you know, there’s some weird stuff about it that I remember from, I remember running into while I was using it that I don’t remember now.
Like, I just remember thinking, this is too weird for me. I don’t want to deal with it. Uh, that sounds familiar.
Yeah. And like, well, cause, but like, I was trying to use it with like, like some analysis store procedure to dump it into attempt table and like even returning a single result, the single result wasn’t predictable because the final results that was dynamic SQL. And depending on like version and addition and some like parameter things, you could get different columns back from the, like, I just remember it not working well for that, but I could be misremembering and going off on an unnecessary tangent.
To further add to my rehabilitation. Um, I do have to give Microsoft credit for reducing their breaking changes caused by version upgrades. Sure.
Cause I remember reading, I remember reading release notes where it’d be like, I don’t know what the last version was. Maybe it was SQL Server 2016 where they have like this very long list of like, here are all the things that, you know, changed that, that might bring. Right.
Right. You know, like re, like renaming DMV columns, all kinds of things. And I remember like, like one release, maybe 2017, that, that list was effectively like nothing. Yeah.
So it seemed like there was some kind of policy change where they stopped doing all those breaking changes kind of willy nilly. Right. They seem to get that under control. Um, now they keep deprecating big features, but you know, I, I think that is an area where they have improved.
So, so I consider every SQL Server feature deprecated until proven otherwise, because there, there are certain things that, uh, have been half implemented for a very long time that, uh, that are not deprecated, but just see no further development. Uh, you know, I think the, if I, if I, if I had to pick like the big three from that, it would be indexed views, partitioning and Hecaton. So like those features have all been around for, well, I mean, Hecaton about 10 years, partitioning forever, uh, index views forever.
And, uh, there has not been a lot of very active development and improvement on those features. And quite a while. So those features to me are, are deprecated.
You can still use them. They’re not, they’re not removed from the product, but they’re not undergoing any active development. So I don’t, I just, I consider every feature deprecated until something, until something happens with it. They’re, they’re in cold storage.
I’ll take a lot more time to unpack that on a different occasion. All right. That’s, I don’t, I never heard a philosophy of that before. Um, speaking of table variables, you remind me of something which I, which I don’t have in my notes here. That’s okay.
Table variables. If you have, if you have a scenario where you need to roll back a transaction, but you want to keep data from before it, you can use table variables. Yes. Tim tables will roll back.
I mean, yeah. Table variables and just like, like local variables too, I guess. Like, you know, I think those are the only ways to do that really. Um, so if you need to do some like fancy error logging or something, that could be a scenario.
I think it’s not very common. I have very, very used it myself. No, but it’s something that people always bring up when they talk about table variables as if it’s some saving grace. Oh man.
I don’t want to be one of those, you know, no, it’s a valid point. Like, no, it’s just a valid point. If you, if like, if you, if you were to use the contents of a table variable to drive some, uh, change, then it would be useful to have that survive an error so that you could undo that change. That’s totally valid.
I mean, it’s, it just makes me think of one of the first presentations I attended, which I remember asking the speaker a difficult question. He know the answer. Yeah.
And years later I realized, wait a minute. Like that guy wasn’t actually an expert. He was just some guy, you know, given the presentation as a SQL starter, which, you know, there’s nothing wrong with that. I mean, in fact, it’s, it’s great. We, you know, always need more.
I don’t know if there are SQL starters anymore, but you know, they’re called data Saturdays now. They’re a little bit less common than, you know, I mean, it’s a little bit less common than, you know, it’s a little bit more. Uh, SQL Saturdays were data Saturdays. Great data.
Um, but you know, not just, or not only SQL. I mean, in some cases, this is a little bit too, uh, pedestrian, I think. Um, where, you know, like, no, I’m not like you who wants to be like, you know, evergreen and useful to the community. Like, I like talking about unorthodox things or things you might not know about, or, you know, stuff like that.
Well, you know, there’s, there’s, there’s ever, so there’s evergreen, which is the stuff that people always need to know about. But people also need to know about the stuff that you’re the unorthodox stuff as well. They need to know about the surprises because, uh, you know, it doesn’t do you any good to produce or absorb evergreen content that leaves people in a naive state.
Like they, like there, there, there’s always going to be something in the, there always has to be something in the evergreen content that is unknown or surprising to someone at the end. Otherwise, you know, you’re just, otherwise you’re just reading the documentation. That’s, I’m really glad you said it because I think I have a few surprises in my final topic.
All right, let’s do it. Maybe, you know, it spends an extra time to you. So, um, so, you know, like, so the final thing I wanted to bring up is, you know, so the trans-doctoral song is your friend. You should work with it, but at the same time, there’s no need to log data that isn’t useful, that you don’t need to, you know?
Sure. Like why write the data twice when you can write once? Sure.
You know, back, back in the old days, people would talk about how important it was to get minimal logging. Um, you know, you remember, you remember the minimal logging. That white whale. Yeah. Um, now, you know, like, so like, what are some things that you can do?
Um. Yeah. You know, like one simple thing is if you’re writing temporary data, use a temp table. I’ve seen developers not use temp tables.
They, you know, just create a keyed, like, Yep. User table. And, you know, if that’s part of the availability group, all the data gets sent over to the secondary and then eventually deleted for no reason. Mm-hmm.
So, you know, like just use temp tables. Or if you’re lucky enough, you have an ETL application, you have a staging database. That’s staging database is simple recovery. Mm-hmm.
You know, use minimal logging. Or, you know, um, like, like avoid patterns where you’re inserting once, then updating like, like, like 20 times. Yep.
Yep. After it. Which I’m sure you’ve seen. Because, you know, it’s, you know, to, I don’t know how all this example works, but like, like, imagine you, you imagine you have to like write a report on everything you learned from this presentation. You know, you, you, if you, if you are right, or if you write your first draft, then print it on your printer, which you probably don’t have.
I do have a printer right here. Well, you know, you’re a professional, but. It’s got printer, scanner, and fax all along.
I think many. Oh, geez. Fax. I don’t have a phone line for that. It’s legit. I’m not. Um, you know, so then, like, if you, if you want to make ads to it, you wouldn’t make the ad then print it again. Right.
Then make an ad then print it again. And so on, like, you’re going to have this big stack of paper, which, uh, am I. Yeah. You guys get a little blurry. It’s all right. All right. Well, whatever. Um, no, but like, like, like this, it’s a good analogy because like, you wouldn’t print it out, like make the edits on the paper and then transcribe the edits to the document and then reprint it and follow that you would just.
Yeah. Like, like, like, like, admit them. It’s like re re proofread the document on the, on the computer and make your edits.
You know, or, you know, as some people say, you should, uh, you should get there right the first time. Right. It’s true.
Um, yeah. You know, I think you can argue that doing an insert then 20 updates is effectively printing it over and over again. Because every time you, you know, you update one column at a time. Mm-hmm.
No, I, I see, I see a lot of times when people do that because it would make like the insert logic very complicated. And like, you know, some of the, some of the results of like some of the updates have to happen as a result of what the values are after they get inserted or just some goofy thing where like, you know, there’s an insert into a temp table. And then there’s like, you know, 20 updates to the temp table to adjust certain things based on other stuff.
And it’s like, like, I, I, I understand why from a development point of view, that would lead to one very complex insert query that no one wants to write. And it’s simpler for them to like mentally grasp. Uh, well, your hand is very clear.
Yeah. That’s all right. There we go. We’ve made, we’ve, we’ve, we’ve achieved stasis. No more blurry witness protection, Joe. It’s back to back to convict Joe.
Uh, yeah. So like, I dunno, I, I, I get it from the point of view that complexity is hard and like writing one very big complex insert statement is prone to error. And, you know, uh, making a change to a complex thing is harder than making a change to like the 19th update that you do after the next.
Afterwards or something, but like, yeah, I mean like in general, you’re right. And too, like even to, even to a certain extent, I would rather do like 20 inserts than like one insert in 19 updates. Like I would, I would rather just continue to refine the data in new chunks rather than do 20 updates, because then I can validate each of those chunks a lot easier than I can.
20 updates. On the subject of non-updating updates, in other words, you’re updating a column value to be the same as before.
Some scenarios that won’t run anything in transaction law. Recently, though, I’ve had to do plenty of code fixes where I’m changing that code to fill drives with non-updating updates.
The reason I’m doing that is in production, we have our databases and availability group. My understanding is there’s no such thing as non-updating update for availability groups.
We’re having these processes where they’d run once an hour or whatever. If it wasn’t in AG, it would be fine. Because it was, we were always basically sending the entire table over every hour.
We actually saw big gains from a simple work clause where instead of sending the thing always to 1, make sure it’s not equal to 1 or not. It’s not an hour or whatever. That really helps.
Right. And there’s other reasons you can… I think if you have… Is it Snapshot?
Mm-hmm. If you have that on, you don’t get non-updating updates too? Or change tracking? Or some other things you can turn on that caused that too. Don’t remember that.
Which I don’t remember either. I mean, change data tracking and… Sorry, change tracking and change data capture might make that weird. I forget exactly which one and how.
I mean, it’s not important. You can look it up. You can look it up. You can blog about it. I don’t see a very accomplished New Zealand-based blogger.
I already wrote about it. But I don’t see the availability of your thing mentioned very often. And that is something to know.
Yeah, totally. So that’s one of those cases where it’s just a net benefit. The last, you know… The last one, I think, is even more niche.
Where we had an ECL. This was in the SageNateBase. You know, we’d have like a… Like, say, imagine a one terabyte SageNateBase.
And we had to create a bunch of times to transform the data. I actually had a very long-standing misconception where… I thought that if you’re selecting, you know, everything from one terabyte table, you would get lock escalation.
But you probably won’t. Because, you know, like, as the data is read, the locks are released. Right. Yeah, the locks don’t accumulate to the point where reads would escalate. So, man, I was just wrong in that for so many years.
But, you know, the point being that locks are written to the transaction lock. So, we actually saw a noticeable improvement in, you know, in CPU caught used by the transaction log writers. Just by adding, like, you know, with tab lock to our staging tables.
Because we weren’t logging all those locks anymore. Yep. There have been a few times recently where even with temp tables… Well, actually, I shouldn’t say even with temp tables.
I should say there have been a few times recently with updates where just adding page lock hints has improved things noticeably. Because SQL Server was attempting to start with row lock hints. Because we got a nice index seek to find our data.
But the locks would accumulate very quickly. There would be a lot of row locks. And then there would be attempts at lock escalation that would fail because of competing locks.
But, yeah, I’ve noticed that even just for some things using page lock hints has improved stuff a bunch. Because SQL Server, like, you can… Like, row lock hints are individual locks, right?
So, you can have lots of rows on a page. And all those individual row locks are there. But if you have… If you use page lock hints, you can just lock a whole bunch of pages. And, like, you can have, you know, many rows on a page.
So, locking pages was a better granularity for things than locking individual rows. So, there definitely are, you know… During your development journey, you may run into cases where you need to reduce the amount of data written international lock.
In some cases, you can just do that. And it just makes things better. All right. So, I’m going to wrap things up here. All right.
Eric said he didn’t want to go over an hour. He’s a busy man. He has to fix his fax machine, I think. Yeah, yeah. So, you know, we don’t want to run too long. Mom picked up the phone again while I was on AOL and ruined my download. So, to quickly summarize, transactions are your friends.
They make it easier for you to do your job. Your code can fail at any time, at any place. Even very simple code, which you think is perfect.
The failure might have nothing to do with the code you wrote. In some cases, building up big transactions into small transactions is the right thing to do. It can be an improvement.
But if it’s possible for end users or other processes to see the data in between batches, you should think critically and make sure that that’s actually okay. I was thinking about scenarios where, you know, as I said before, your code can always fail.
So, it is okay if half the batches occur. If you’re using insert into exec, remember that the exec part, the server procedure, will be in its own transaction. If you need to log data that doesn’t get rolled back by transaction, you can use table variables.
And, you know, in some cases, you may need to reduce the amount of data written to the log for performance reasons, especially if you’re using availability groups. And there are various things you’re doing that, you know, using a logging, temp tables, all the stuff we just talked about.
Anything you would like to add? No, that’s a wonderful summary, Joe. You did better than chat GPT.
Thank you. You’re welcome. All right. Well, thank you all for joining us and listening to me and Joe go on and on about transactions. I don’t know.
Are we going to record another one of these next week? Do you have another nevergreen topic to talk about or semi-green topic to talk about? I don’t want to be one of those guys who is like, yeah, you know, we’re going to do one like every week. And then, you know, we immediately start failing.
So, all right. Let’s keep it as a surprise. Surprise. What do you think? Right. Just like transaction errors, transaction logs ending up, filling up, these recordings will be a surprise to everyone, including us. We’re just going to ambush ourselves with that.
So, thank you for joining us. And remember to have those index rebuild jobs spayed and neutered. We’ll catch you in the next episode. And I think I have to hit, where is the record button here?
Where’s the stop button? Oh, there’s the stop button. We’ll see you next week. Martin coverage rational
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
