sp_WhoIsActive: Tracking Down Implicit Transactions
Video Summary
In this video, I delve into the intricacies of implicit transactions in SQL Server using SP who is active as a tool to track down these pesky issues. As usual, I begin by explaining why Microsoft hasn’t provided a straightforward way to monitor implicit transactions and how they can cause unexpected behavior, especially with drivers like JDBC and Python. I then demonstrate how SP who is active can be enhanced with specific parameters to reveal more detailed information about queries within implicit transactions. However, even with these enhancements, the tool still falls short in fully capturing the complexity of such transactions. Throughout the video, I illustrate this through practical examples, showing that while SP who is active is a valuable resource, it isn’t sufficient on its own for comprehensive transaction tracking. The takeaway? Implicit transactions are best avoided unless you have a deep understanding of their implications and can manage them carefully.
Full Transcript
Erik Darling here with Darling Data, as usual. Forever, forever and so forth. So be it as it may. And in this video, I’m going to show you how you can tell with using SP who is active. Still waiting for Microsoft to write SP whom is active or something. To track down if queries are using implicit transactions. Now I have another video fairly recently about just how bad implicit transactions can be. Should probably watch that. Promise it’s a barn burner, as they say. But you see them a lot from the JDBC driver, you see them a lot from the Python driver, and they can come and cause some real weirdness in a lot of ways. Now, SP who is active is no shortcut around that weirdness. And I’ll show you why and how wow that is true. So if I run this now, again, we’re going to have my noisy CDC job in the background because I am working on CDC stuff for a client locally. Figure out some ins and outs there.
Some specific use cases, as it were. So I’m going to use get transaction info. And if you have not updated SP who is active in the last year, year and a half, you’re going to need to do that in order to see this information. So that’s what that’s what this is the secret sauce, as they say. But you get a new column called implicit underscore tran. And that that’s what’s going to tell you if something’s going to be an implicit transaction or not. This is a null. In this case, not every transaction is going to have a on or off here. I don’t know why. Something’s just weird in the place that this is tracked. Maybe an is null or a coalesce would help with that. Let’s say is null. No. So what I’m going to do is over in this window, I’m going to set implicit transactions on and I’m going to show you why things are weird. And then I’m going to run this count query. I’m going to get a result and I’m going to run this query and I’m going to get a result, but I’m not going to hit commit. I would have to use commit to close this transaction out.
So if I run SP who is active now, I get this back. And in the SQL text, it just says select count big from badges, which, you know, is not really the full story because we had that other select from the users table in there. Now, if I use another parameter for SP who is active called get full inner text and I set that to one, you might think that you would get the full inner text of the set implicit transactions on the select count from users and then this, but you don’t. You still only get the full command. You still only get the select count big. Notice up here in the SQL text, we do get the full store procedure instead of just the wait for command.
Just to refresh your memory, if I quote that out and I run this, all we see is wait for delay, whatever. Now, there’s another parameter you can use with SP who is active called get outer command. In order to see that, we need to change this a little bit to say SQL command.
And if we run this, we’re still not going to get the full story of what else happened in there. All right. So like notice for SQL text, we get the procedure that’s running and we even get we get the name of the procedure, the execution of it over here. But over in this column, we still only get the select count.
So even SP who is active can show you somewhat misleading information about about multiple queries in an implicit transaction. Of course, if I come over here and I hit commit, then everything goes away. I’m running. Now I can’t I can’t double commit.
But just because I have implicit transactions on, I have to commit this here. And now if I run this whole thing, all I see is the information about the CDC job. So what else is there to say?
I’m going to say implicit transactions are a real pain in the butt. They’re a real nuisance. I really don’t suggest that you use them without a full understanding of exactly what they’re doing.
The full the full understanding of everything that you’re doing in a single batch that might hold locks for a really long time. In the implicit transactions specific video that I have, I talk about how that how that can happen. So, yeah, most of the time I just tell people don’t use them because most people just don’t have a good handle on what they’re doing with them.
And there’s no global way to say not allowed because any user session can say, well, I feel like using them. I’m going to override you. I’m not listening to you, mom and dad.
So, yeah, that’s that. Implicit transactions, bad for the body. Bad for the body SQL.
Avoid them. They’re confusing. Even SP who is active has a tough time seeing through their dismal charade. And so do not recommend.
Thumbs down. Thumbs down. Anyway, thank you for watching. Hope you learned something. I hope. Well, I would say that I hope you enjoyed yourselves, except this video was rather depressing, wasn’t it? It was a bit dour.
We didn’t. We didn’t. We only learned how bad something was. We only learned how tough it is to deal with this thing. It is so bad that it even makes SP who is active look foolish, which is a tough gig. Anyway, please find your way to the like and subscribe buttons.
If only so you can see my life and say, God, everything’s kind of all right for me. Anyway, I’m going to record something hopefully less depressing after this one. So thanks for watching.
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.
In this video, I delve into using SPWhoIsActive to gain deeper insights into query memory grants on SQL Server. Specifically, I demonstrate how to leverage the new `@GetMemoryInfo` parameter to retrieve detailed information about memory grants, including requested and used memory. However, I also point out some quirks in the output format that might be a bit confusing for users, such as the way memory is reported in pages rather than more intuitive units like megabytes or gigabytes. Despite these minor inconveniences, the tool proves to be quite useful for troubleshooting scenarios where query memory grants are problematic. I encourage viewers to update SPWhoIsActive if they haven’t done so recently and explore this feature further to enhance their diagnostic capabilities.
Full Transcript
Erik Darling here with Darling Data. If you can hear anything that sounds like I’m at a dentist office on this recording, I do apologize. Someone started sawing or drilling or something outside and I don’t know, there’s just not a lot I can do about other people at this point. Someday, someday. Anyway, this video is going to be a little bit more. So, I’m going to talk about how you can use SPWhoIsActive to get additional interesting information about query memory grants. And also how the output for this is a little weird for my tastes and why. So, I’m going to use this demo, which I used in a prior memory grant video. And I’m just going to run this and open up and run SPWhoIsActive. And then I’m just going to kill this because we don’t really need anything more from this at the moment. You can ignore this top line. In case you can’t read up here, this tab is titled CDC crap. And I was trying to figure out some stuff for a client about Change Data Capture. And so, I have this Change Data Capture job constantly running on my system now. There’s a way to run this.
So, I’m going to wait for it. And it goes and does some stuff. But anyway, newer versions of SP. Now, the last version of SPWhoIsActive was released about a year, year and a half ago. So, if you haven’t updated SPWhoIsActive in the last year to year and a half, now would be a good time to do it so that you’re able to use this fabulous new parameter called at Get Memory Info. So, that’s this lovely parameter right here. Now, I’m using the output column list just to get back the columns that I care about. You don’t have to do this to see the memory grant stuff. This is just to make the results a little bit more digestible for the video that I’m recording. No other reason for that. But it is kind of a cool example of how you can use the output column list to get just the columns that you care about back for certain things. But we’re going to talk more about that in another video.
So, just like in other memory grant demos that I have, this set of queries when run in a foursome will have one query that gets stuck waiting for memory grant. So, you can see that because it is requested memory but granted memory is null. Now, where things get weird with these results is, and this is not the fault of the gentleman who added this code in, a smart fellow named Michael Fuller who’s been indispensably helpful working on who is active over the past, I don’t know, I guess, well, I guess past couple years.
There hasn’t been a lot of active development just so recently. But, so when, for all of, so these three columns are what got added to the output list. This used memory column has been in there forever.
And the logic for the used memory column is to report this in pages, KB. So, used memory is divided by eight. I don’t necessarily agree with this choice because you don’t really measure query memory grants in eight KB pages.
You measure them in megs or gigs or something that is a little bit more in tune with how you think about memory grants generally. You generally don’t think about eight KB pages when you think about query memory grants in SQL Server. So, these numbers here are always going to look a little bit weird.
Of course, we have this query down here which has requested memory but has not been granted memory and has been waiting two and a half hours. And, we’ve talked about that in other videos which were highly successful. Smash hits, they’re the reason I make billions of dollars on YouTube and no longer need to work a real job.
I’m kidding. If only, if only. But anyway, so you get used memory, max used, requested, and granted memory back in the output list here, like top-level columns.
But then you also get another top-level column called memory info. And memory info is an XML clicky column that will report this information. But, like, this stuff gets reported sort of like from the raw DMVs.
There’s no adjustment made to it. So, you get the request time, grant time, like you get requested memory, used memory, all that stuff in KB. So, like, you do have to do a little bit of KB to GB math if you want to figure this stuff out.
Generally, I don’t care about memory grants that are under the one gig mark. Generally, I don’t care about query memory grants until they hit, like, you know, several gigs or more. That’s just me, though.
You know, especially on larger systems, you know, smaller memory grants just don’t, just do not titillate the mind. But this one is interesting, sort of. But the one down here for the query that’s waiting is a little bit more interesting because there’s a little bit more information to take in.
Such as, and this is stuff that we’ve talked about in other resource semaphore videos. Go find those if you want to, you know, learn more about these columns. But these are, because this query asked for a memory grant and didn’t immediately get it, it’s sitting in the queue.
It’s the next candidate. So, the wait order for it is zero. And, you know, you can kind of get some information about the workload group that it’s in, what the max memory grant that a query can ask for is. And this 25 is based on max server memory.
And then you can get some information about that, about the workload group, like total memory, how much is available, how much has been granted out, how much has been used from that. So, there is good information in here that is usable. You know, XML isn’t always the most consumable thing, but it is an easy way to present a whole bunch of information like this all in one go.
So, like, it doesn’t help if you want to stick it into an Excel file or something, but it is a reasonable way, I think, to present like grouped information in this way. Cool. So, get a new version of SP who is active if you haven’t updated it in a long time.
And check out the get memory info column. It’s a useful doodad for if you are troubleshooting, you know, a SQL Server where query memory grants are sort of out of control. Again, other videos about memory grants, resource semaphore and stuff where you can learn more about all of that information.
I highly suggest you peruse every video on my channel so that I can get enough views to have YouTube say, you should consider monetizing your channel. And I can say, no, I’m a man of the people. I do this.
I do this for them. I don’t do this for money. I mean, I do do this for money. Just like not whatever. Anyway, you get the point. Thanks for watching.
Hope you learned something. Hope you enjoyed yourselves. And if you would be so kind as to like this video and subscribe to my channel, I would be ever so grateful. But since I don’t get paid for this, I can’t give you any kickbacks.
But just my love and affection. Maybe a kidney if you really need it. But I kind of don’t think you want my internal organs at this point.
I’ve been working with SQL Server too long. I’ve just been tremendously abused. Anyway, see you in the next video.
See you in the next video.
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.
Updates to sp_QuickieStore and sp_PressureDetector!
Video Summary
In this video, I’m Erik from Darling Data, and I’m excited to share some updates to my free open-source stored procedures, SPQuickieStore and SP_pressure_detector. First up is an enhancement to SPQuickieStore, which now allows you to query all databases with Query Store enabled without specifying a database name. This feature can be particularly useful when dealing with environments where multiple databases have the feature turned on or when you’re unsure which ones do. I haven’t fully fleshed out the ability to specify a list of databases to include or exclude, but based on initial feedback and usage, I may add this in the future if it proves popular.
Next, I’ve added a disk latency report to SP_pressure_detector. This new feature provides detailed insights into disk performance metrics such as read and write stalls, which can be crucial for identifying potential bottlenecks, especially when monitoring memory pressure or CPU usage. The addition of these metrics is particularly helpful in scenarios where heavy TempDB activity might skew average values, allowing you to get a clearer picture of the overall system health.
Full Transcript
Erik Darling here with Darling Data. And we’re going to talk about a couple oh so exciting updates to SPQuickieStore and, sorry, SP underscore, underscore, underscore, QuickieStore and SP underscore pressure detector. I apologize for the little hat line on my head. I was out working on some stuff for me. I was out working on some stuff in the backyard. I’m a little sweaty, but, you know, this isn’t Smell-O-Vision, so I don’t think we have too much to worry about there. But, um, short video. Just because I haven’t recorded anything in a bit, I got a bunch of stuff in the queue to record, but, you know, life gets busy and hazy sometimes, so I don’t always have as much time to do this as I’d like. But anyway, in order to keep my promise of a brief video, we’re going to look at a couple of the things that I’ve added to my wonderful free open source stored procedures. Alright, so the first one is SP underscore, underscore, QuickieStore, the teeny bit of a tongue twister. I added a parameter, and I haven’t fully incorporated the feature yet. And by that, I mean, I haven’t added a way to, like, give a list of databases to get or give a list of databases to skip. I’m going to see how much this gets used before I spend what would probably be a silly amount of time dealing with that. But anyway, if before with SP QuickieStore, you used to have to give it a specific database name, and there are sirens out there, hopefully, hopefully, hopefully, that adds to the charm of my, my straight out of New York recordings. But it used to be you have to, you used to have to give SP QuickieStore a specific database name to look at. And for various reasons, like people having quick query store on in multiple databases, not knowing which databases have it.
me being lazy, I’ve added a way to just go look at all of the databases that have query store properly enabled, and return data from those. So if we execute this, we are going to get back a slightly longer result set than we normally would. And we’re going to get back, of course, data from multiple databases. So it’s not going to be all that interesting, because I don’t do it. a lot of work in the other databases on my server, you know, like I have my clean copy of the Stack Overflow database that I don’t allow any updates to. So if I mung anything up into my actual copy, I can just reset that data pretty easily. And of course, like everyone else, I have a database called crap, that I do random crap in a lot of what ends up in here is, of course, like, like, if anyone is ever so kind as to, you know, add, create table, and whatnot statements to a Stack Exchange question over on dba.stackexchange.com, then I use the crap database to sort of sandbox all that stuff.
So like I said, there’s not a whole lot of interesting stuff in these other databases. But the Stack Overflow 2013 database does see a bit more action than the rest. Well, actually, it sees all the action, because that’s where I do the majority of my my demo writing work, because that’s the one that I will use for training, pre-cons, blog posts, and all that stuff. So might as well keep it all as homogenous as possible. The other incredibly exciting addition, news from 2005, is I’ve added a disk latency report to SP pressure detector. This will only come up if you have, I should probably just stick this in the script, so everyone’s aware that what to check equals all. Well, that doesn’t say all, does it? That doesn’t say all either. That says all. Rocking and rolling. So what to check has to be set for all to pull this back. If you’re looking for just CPU or just memory, this check and the tempdb check get skipped.
So by that I mean if we run this for, let’s say CPU, we will just get weight stats back, and then we’ll get information about the CPU stuff. I’m a little on the fence about skipping weight stats, because often I use those in demos when I’m looking at something specific. And, you know, I kind of like having those show up no matter what. But, you know, if you have differing opinions, feel free to hit me up. But if you use CPU or memory, then things get skipped. But if we do all, we get a bit more back. We get back the weight stats. And then down here, this is the new bit. And by new, I mean, I mean, this stuff has been around in SQL Server DMVs for just about ever.
I was just, I don’t know, somewhere between hesitant and lazy about adding it in here, just because, you know, usually when I care about CPU or memory pressure, disk is a much smaller factor. But I did decide to add this in, just in case disk is also, like, especially for memory, if we care about, like, you know, let’s say we really care about how much page IOLatch is going on on the server, or how much write log is going on on the server. And, you know, there’s, like, a lot of it, and, like, you know, it’s, like, slow for something, like, it’s just way more of that weight than we would care to see generally.
Then I do kind of want to look at disks to see if there are any, you know, sort of inherent bottlenecks there. My first implementation of this, I only, I had, well, actually, the default value for this is 100 milliseconds for minimum disk latency MS. The default value is 100 milliseconds. I’m using 20 just to make sure stuff shows up here.
But this section here sort of mimics some of the other sections in here where it tells you how many hours we’ve been up, which drive the files are on. And I know I’m a bad DBA because I have everything on the C drive, but it’s a VM. Assume me. The disks are the disks. It’s not like they’re going anywhere special.
The database name that the files belong to. And then database file detail. So this tells us which file is involved.
If it’s a data file or a transaction log file, you see that changes there. And then it gives the full path to the file. I used to have this so that database name was sort of incorporated into this sort of string, this built-up string of details.
But, I don’t know, it kind of made it a little too busy, and I couldn’t think of, like, a good, like, I used a backslash to separate it. And then, you know, I figure a lot of people want to filter by which database they look at if they, like, paste this into an Excel file or something. So I left this separated out.
You know, I’m not crazy about it either way, but I don’t know. That’s just what I did. So deal with it.
Then kind of getting into what we get back. We get the size of the file that is listed here. We get the average read stall.
We get the average write stall. We get how many gigabytes have been read, how many have been written, the total read count, and the total write count. I don’t really care about the total, like, minutes or seconds or milliseconds of stall in these cases, just because the average is usually what people care about. Like, does this thing suck on average?
Like, how slow are the reads and writes on average from this? So I left that in, and then these are just sort of, you know, to get a sense of, like, how busy things are. I work on some, or rather I have worked and work on some systems where there have just been, like, terabytes of writes to TempDB for databases that are, like, 100, 200 gigs.
And in those cases, I do kind of like want to be like, well, what’s going on with TempDB? Because that’s, you’re doing a lot of stuff in there. What’s all the stuff you’re doing in there?
And that especially might relate to, you know, if we have, I don’t know if there’s any that are going to show up, but if we have any weights up here that are potentially related to TempDB contention, then we might care to know, you know, that stuff about TempDB. But, you know, like a lot of other DMVs and SQL Server, you know, the averages include when terrible things go on. So, like, with TempDB, if you’re the kind of born-in-the-wild fool who still does index rebuilds or, you know, you’re the kind of smart person who, you know, does DBCC check DB, you might see a lot of TempDB activity at some points on the server that might make the averages spike up.
But these, like, the averages here aren’t necessarily going to be related to user workload. So, like, I don’t have, like, a great way of distinguishing that because this is just a snapshot. It’s not like a time slice of when things went up.
If you need that stuff, get a proper monitoring tool. Should a proper SQL Server monitoring tool ever come into existence again, it would be nice if you got that and used it so that you could see that sort of thing. So, there we go.
There we have it. Two exciting new features in my store procedures. I’ll put the links to the GitHub repos for these things in the video notes. And, I don’t know, happy troubleshooting, I guess.
Thanks for watching. I hope you like, enjoy, use, find some value in these store procedures that I have spent many, many hundreds, maybe even thousands of hours in my life working on. If not, I don’t know.
I hope you’re using, I hope it’s because you’re using a different data. I hope it’s because you use Oracle or Postgres and you just, you just can’t run these there. Anyway, like and subscribe if you’re into that sort of thing.
If not, then, I don’t know. You’ll just have to wait until YouTube randomly recommends one of my videos to you based on prior search or viewing history. All right.
Cool. Thanks for watching.
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.
sp_PressureDetector: Which Queries Are Causing CPU and Memory Pressure?
Video Summary
In this video, I continue to explore the insights provided by SP\_pressure_detector, a powerful tool for monitoring query performance under CPU and memory pressure. We delve into specific sections of the output that highlight queries running during times of high CPU and memory usage, showcasing how these queries can be identified and analyzed in detail. By focusing on these particular aspects, we gain valuable information about which queries are causing bottlenecks and how SQL Server manages resources under stress. I also take a brief break to joke about my lunch plans, which involve an adventurous approach to eating a whole live chicken—a bit of light-hearted humor to keep things engaging while discussing some serious technical topics.
Full Transcript
Erik Darling here, representing Darling Data. And this video is going to be continuing to look at what you can see about queries that are running in SP underscore pressure detector. Now, we’re going to sort of replay some of the demos that we looked at for the CPU pressure and the memory pressure stuff. because one set of, I mean, well, I guess technically two sets of results that I didn’t really explore much are information about the queries that are running while things are going poorly. So, we’re going to do that. We’re going to have fun. We’re going to have a lot of fun. And then we’re going to eat some lunch because it is getting close to that time and, uh, I don’t know, really. I don’t really like to, uh, I, I, I, I, my balanced breakfast was either three or four, uh, double shots of espresso. So, um, my balanced lunch is probably going to have to be chicken or something. A live chicken. A whole live chicken. Not, not, not de, not de-boned, not de-feathered. I’m just going to, I’m just going to rip it off.
I’m just going to rip into it. Chupacabra. So, anyway, uh, this video is sponsored by New York City tap water. Hmm. It’s not radioactive yet. What else can you say? All right. So, important stuff. Uh, we’re going to use just this setup. We don’t need to do anything else on this one. Uh, cleared out weight stats.
We don’t have to worry about that. Let’s make sure this is highlighted. So, future executions do not also clear out weight stats, even though they’re not really important for this, for this part. Uh, you know, whatever. We’re going to find various ways to enjoy ourselves. All right. So, let’s kick off, uh, that CPU pressure demo. And let’s unhighlight that. Let’s be really counterproductive right off the bat. And we’re going to give this a few runs just to kind of, uh, let things warm up a little bit.
And, you know, we’re going to see the weight stats pile up for, like, you know, CPU related stuff, which is, you know, about what you’d expect for a CPU pressure demo. But let’s scroll down a little bit here. And let’s look at this section. So, this section will, uh, tell you kind of more specifically about CPU related stuff, uh, on a server. And one thing that I want to show you is, uh, this, these two columns over here. So, uh, under intense enough CPU pressure. I just want to go back a little bit.
So, this section up here will only show you queries that are asking for a memory grant. Uh, if queries are not asking for a memory grant, they will not show up here. There’s a, there is some differences in the results. And, you know, you can use the at what to check parameter to look at CPU or memory or both or, you know, whatever. But as we scroll down through here, under significant enough CPU pressure, notice that we have these queries that all of a sudden, uh, this is all the same query running.
This is my system. And there are so many of these things that cannot possibly be terribly contaminated by other stuff. And we can scroll back over to the query text to see this is all the salon, the select count query that I’m executing. Right. And we have some queries that under significant enough CPU pressure, right, where we’re hitting thread pool weights, right, for a long period of time in here.
Uh, the notice the dop suddenly drops off. All right. I’m going to cancel this just so it’s sometimes this one, well, you know, sometimes it takes a little while to kill other times. It’s just immediate. So that’s, that’s nice work, work quickly while recording.
That’s always a, always a welcome, always a welcome surprise. So if you remember the video that I posted, uh, talking about how bad of a feature dot feedback is in SQL Server 2022. Um, now in preview in Azure, um, now in preview in Azure, the number of Microsoft features for SQL Server 2022 that are in preview as of RTM, sort of astounding.
You know, like, uh, Microsoft’s like, ah, well, just preview in prod. Why not? Uh, test that for us. We’re afraid. It’s a little, it’s a little amusing.
But, uh, one of the, one of my main, one of my main beefs, one of my main grievances with the dot feedback feature is that, uh, it’ll, it’ll downgrade and downgrade and downgrade, uh, but it will never go to dot one. And here we have SQL Server on its own, under memory pressure, uh, downgrading a whole mess of queries from dot eight to dot one because we did not have significant enough CPU resources. And if we scroll over back this way, and I’m just going to highlight this bottom row because we know this is dot one and we can make a reasonable assumption that this is the select count query that we’re running from a SQL query stress.
If we look at the query plan for it, SQL Server is still showing a fully parallel execution plan for this query, even though at runtime it is only getting one thread. So, dot feedback people at Microsoft. You can do it. You can make it happen.
You can do something smart with this feature. I don’t know why I’m whispering. This isn’t a secret. The feature stinks.
Uh, so there’s that. All right, cool. So other stuff we can see about, uh, queries executing in here. Uh, you know, session ID, database name, how long they’ve been running for, query text, query plan.
Uh, you can get some statement offset and whatnot from this just in case it’s part of a, you know, larger batch or whatever. Uh, I like to give you the plan handle in case you want to go look stuff up. Uh, you get the status if it’s being blocked.
Uh, any, you know, important weights for these. You see some of the, um, CX consumer weights up here. These queries run for 20 seconds and they’re just pounded in CPU. So, um, you know, that’s, that’s fine.
Uh, you know, other, other interesting stuff. Uh, CPU time, elapsed time, uh, reads, writes, memory, isolation level, and of course, dop and parallel worker count. So, uh, pretty good set of, uh, information there if you want to, uh, get information about queries that are, uh, using a bunch of CPU.
Now, let’s switch over a little bit and let’s look at the, uh, memory pressure demo. It seems like a reasonable thing to get to. And let’s, uh, let’s execute this.
And let’s run SP pressure detector. Let’s give this a couple few seconds to warm up. We’ll know that, um, we’ll know that things are starting to warm up when we start to see, uh, resource semaphore weights show up in here, right?
That’s when we know things are getting good. Uh, more some of the last demo, we do have, uh, uh, the thread pool weights piling up here, right? We have about eight and a half seconds per weight.
Not a good sign, right? CPU pressure sign. But now let’s, uh, scroll down and let’s stop a little bit earlier than before. And let’s look at the section that gives you queries that are asking for a memory grant.
Again, no memory grant. You don’t show up here. This thing specifically looks for stuff that are, these queries are asking for memory grants. I didn’t want to mix the two because I kind of show you different things from each, right?
So session ID, database name, how long it’s been running, query text, query plan, yada, yada, yada. Where these, where these differ is, uh, these show you queries that have, well, when they requested memory, uh, when they were granted memory, how much memory they requested, uh, how much, uh, was actually granted of that request. Uh, of course the ideal memory.
So the ideal memory was way higher than what was granted, uh, the required memory. So like, um, if you end up with a, uh, sorry, I’m looking at the wrong section. So if you scroll down a little bit and you look in this section and, uh, you see stuff in this forced grant count row, uh, this is the required memory.
So if you have a query that gets forced to run with a lower memory grant, that’s where you’ll see that. Uh, I’m just going to kill this off because boy, oh boy, is my, are my computer fans tired? Uh, you’ll see how much memory of the grant was used.
I should maybe rearrange these columns a little bit. So they’re a little more, a little more lined up with things. I was actually, you know, I was going to make a couple of notes here, mental notes.
Uh, I would like to have a date diff on the request and grant time to see if there was, uh, there was any significant, uh, delay in a query asking for a grant and getting for a, getting a grant. That’d be nice, right? I should probably put that, uh, probably put a difference between, uh, well, I don’t know.
We’ll figure some stuff out. I don’t know if there’s a difference between like used and requested or used and granted. That seems like a good idea.
So if you scroll over a little bit further, uh, you’ll see some information about, so like if a query got a memory grant, these will be null. If a query is waiting for a memory grant, these will not be null. You’ll see which QID they’re in, the weight order of the queries, zero based counting.
How nice. You’ll see if a query is the next candidate, which, you know, could change from Q to Q. And then, uh, how long the query has been waiting for a memory grant, which, I don’t know, maybe that’d be interesting to factor into the, uh, the, the, the weight, waiting for a grant equation.
Wouldn’t it? I think so. So we got that.
And then we see some of the, the queries that are up there running, that got their grants, because this stuff is null, waiting on CX consumer, parallel queries. Yeehaw. Uh, but the queries that are waiting are all waiting on resource semaphore.
Hmm. And, uh, you know, some other information that looks a little bit like below the DOP, reserve work account, use work account. So there’s sort of like a mix of information in here.
But again, there’s enough different that I didn’t want to sort of pollute one result set with data from another, because we would end up with a scroll bar that cuts off an arm and a hand from everything. It would not be fun or enjoyable. So, uh, that’s that there.
Now, do you always have to look at both of these? No. But, uh, as a whole, these results can give you a whole lot of great information about the various types of pressure that, uh, SQL Server can be under. Right.
CPU and memory, those are the most common. Um, and you can also see some good information about the queries that might be causing that. Um, you know, the plan handles are good things to copy off for queries, so you can go do some digging later. Of course, the query plans and query text are good things to dig into there.
But you can get more information via the plan handle from various sources. Uh, sort of calling back to another video about SP underscore quickie store, you can use that plan handle, uh, to look up queries there. So, uh, all fun stuff in there.
And, uh, I don’t know. I think, I think that’s about it for that. It was just those two results that I wanted to go over because, uh, the other videos about, uh, CPU and memory pressure specifically cover the rest of the results. And those were about 20 minutes each.
And this one’s going to be about 15 minutes. So, you might get some pretty good understanding of why I didn’t mix everything in because then you would have been sitting around for 45 minutes watching me on YouTube double or triple speed. Or I would have sounded like a, one of those sped up soul samples trying to explain CPU and memory pressure to you.
And that, that is not a good, not good for my voice. My voice does not do well with, with the speed up. Cool.
Cool. So, again, SP underscore pressure detector might be the finest or procedure ever written. Uh, I do encourage you to look at the, uh, the source code.
Behold its majesty. Behold its elegance. And once you do, I think you’ll understand why Darling Data is indeed the hottest, hippest, most fire SQL Server consultancy on planet Earth. And again, I’ll get the lawyers out if you disagree.
So, anyway, thanks for watching. Uh, I hope you, uh, enjoyed yourselves in some way. Um, hope you learned something.
If, uh, you enjoy either something physically about me or my voice or my SQL Server abilities, then, uh, I would encourage you to like and subscribe to the channel. Um, if not, well, have a, have a, have a great weekend. You find folks working on the Dopp feedback feature?
Well, you, you, you can do a serial plan. You can do, you can assign a single thread to a query with a parallel plan. It does not require a recompile.
You can do it. I have faith in you. I haven’t met you. New crop of summer interns. But I have faith in you. You can do it.
You fix generate series. You can do anything. All right. Cool. Thanks for watching.
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.
sp_PressureDetector: What SQL Server Looks Like Under Memory Pressure
Video Summary
In this video, I dive into how SP\_pressure detector can help identify memory pressure issues in SQL Server. I explain two primary forms of memory pressure: queries requesting large memory grants and the buffer pool being depleted to fulfill those requests. By walking through a demo with a specific query that requires a 10GB memory grant, I illustrate how SP\_pressure detector displays real-time data on stolen server memory and SQL reservations, highlighting the impact on overall system performance. Additionally, I demonstrate how concurrent workloads can exacerbate these issues by competing for buffer pool resources, leading to increased page IO latch weights and resource semaphore waits. This video aims to provide practical insights into managing memory pressure in your SQL Server environment.
Full Transcript
Erik Darling here with Darling Data, the hippest, hottest, most fire SQL Server consultancy on the entire planet. Get my lawyers involved if you say otherwise. And I meant to record this video yesterday, but I don’t know, I hit various weird things and some time constraints and then my wife forcing me to watch Vanderpump Rules, did not get a chance to do it. So here we are, day two, and we’re going to talk about how SP underscore pressure detector can help you identify memory pressure in SQL Server. Now, memory pressure generally comes, or I, as a consultant, I generally see memory pressure in two forms. The first form is queries asking for big memory grants, or even just lots of queries sort of asking for like this swarm of memory grants, and other queries not being able to get memory to execute SQL Server.
And we’ll talk about this in a little bit more detail. SQL Server has only so much memory it is willing to give out to queries to run at a time. The second form of memory pressure is that that memory has to come from somewhere, and that somewhere, because most servers just don’t have enough memory in them to begin with, that somewhere is the buffer pool. Now, memory grants don’t use the buffer pool. Memory grants share memory with the buffer pool. So there’s this tug of war between queries who are like, hey, I need some memory to run, and the buffer pool being like, hey, I need to cache all these data pages to give to queries. And, I mean, you know, there’s query memory grants come along, a bunch of pages get knocked out of the buffer pool, queries finish running, pages come back into the buffer pool, maybe, if you’re lucky.
And then queries run and then queries run and then queries run and ask for memory grants, pages get knocked out of the buffer pool. So please try to give your SQL Server enough memory so that you don’t have this constant gas station sushi effect of memory just dumping in and out various things. So, by default, SQL Server will give any query up to around 20-25% of your max server memory setting to run.
And also, by default, the single query memory grant is something that you can tinker with a bit, either with resource governor or with the max grant percent hint. Another default I find that is rather cruel but is not currently a setting that you can change is that SQL Server is willing to give out around about 75% of your max server memory setting to queries to execute as a whole. So if you have three queries come along and ask for that, like, 20-25% mark, SQL Server will give it to all three.
The fourth query asking for that big memory grant comes along, it’ll get stuck. But other queries that ask for smaller memory grants may be able to sneak in depending on how much is currently loaned out to those. There are some good ways to deal with that.
You know, resource governor, if you are on Enterprise Edition, the max grant percent hint is one way to deal with that. If you are not or if you, you know, want a more focused, targeted approach rather than forcing every query to use a lower grant. But resource governor is something that I end up using quite a bit, especially on client servers that have lots and lots of memory, like terabyte, two terabytes of memory in there, because 20-25% of two terabytes is a pretty gosh darn big memory grant.
Standard Edition is, you know, a different story. You get that 128 gig cap for the buffer pool, but you can use memory over that for other stuff. So my general advice is if you’re on Standard Edition and it’s a server you care about, it should be Enterprise Edition.
But barring that, Standard Edition, you should give 192 gigs of memory. I was really disappointed that SQL Server 2022 didn’t increase the 128 gig buffer pool limit on Standard Edition. But, you know, I can’t fight the bean counters, dorks.
But anyway, you give SQL Server Standard Edition 192 gigs of memory. Set max server memory around the 180 or so gig mark. You have 128 gigs for the buffer pool and then 50 or so gigs for other memory consumers like query memory grants, lock manager, plan cache, all that other stuff to sort of deal with.
So I have, even though I have like 96 gigs of memory assigned to this humble virtual machine, I’ve turned max server memory down on this one because I want to exacerbate my problems. 96 gigs of memory is, you know, pretty good for the size of the database that I’m dealing with.
So I turned it down a bit to make things a little more close to what I see in real life. So if we look at this execution plan and we look at what this query got for a memory grant, it is 10 gigabytes. All right.
So this query gets 10 gigs of memory. This is about the most that a query can get on my server with max server memory set to around the 55 gig mark or so. So and this query returns zero results.
Why does it why does it return zero results? Because I am I have a row number function in here and I am filtering to where row number equals zero. So I want nothing to come back at all, because I don’t want to wait for SSMS to show me a bunch of stupid results.
But I want this whole thing to have to be expressed before this filtering happens. So the query plan is, you know, do all of this work in here. Right.
And this is what this is the work that happens inside that CTE and then filter on that work there where we get nothing back because SQL Server for some reason doesn’t know that a row number cannot be zero. So in this context, which is a little odd, but works to my advantage. And I don’t want to ever.
I don’t ever want that to change. Microsoft, please don’t file that as a bug. Now, I have this query running in batch mode because I really want because it’s just better that way. A lot of things are.
But I have this query running in batch mode. So it finishes pretty quickly. It finishes in about six seconds. This helps with the demos. But in row mode, this thing runs for like 20 seconds. And it makes the demos less interesting.
So let’s come over to this window. And let’s clear out wait stats so that we don’t have to care about anything. And oh, come on.
Get out of there. There we go. Now we’re really clear. So let’s start sort of with how this thing looks with nothing going on. We have a little bit of SOS scheduler yield because even though I just cleared out wait stats, there’s always something going on.
SQL Server is always up to something. It’s probably that customer experience improvement program thing running, ratting on me, ratting me out to Connor about all the things I’m doing in SQL Server. Trace flags.
Using the debugger on things. So some stuff that SP underscore pressure detector will show you about your SQL Server off the bat. Top memory consumers.
So we have this section up here. And the first thing that I want to show you is how much is consumed by the buffer pool. I want to show you. Let me get rid of this blue highlighting. That just makes the pink look weird.
All right. There we go. I want to show you how much memory is currently kind of being taken away from the buffer pool. And then under that, I have the top five non-buffer pool memory consumers on your SQL Server.
One thing that I find a lot in here, which is something that I have a script to help with in my GitHub repo, is a memory clerk called UserStoreTokenPerm. And that’s a security cache.
And I find lots of weird things happen to SQL Server when that gets above like the 2 to 4 gig mark. And clearing that out often alleviates a lot of weird issues. If there were any queries running that required a memory grant, we would see those in here.
And then we also have this low memory section. And this will tell you any point in which SQL Server had a low memory warning inside of it. So this can be useful to figure out how often your SQL Server is kind of tanking out on memory.
Down below that, I have a whole bunch of information about database size, how much memory is in the server, what max server memory is set to. Some of this data is duplicative just because I wanted it all to be in one place.
And so there are some extra rows in there. But sue me. I don’t know. Whatever. I should. I don’t know.
I like it. Works for me. If you’re using lock pages in memory, you’ll see that here. And then you’ll have information about various memory pools in SQL Server. Typically, the ones that we see action in are the zero pools right here, like this one.
This one here is usually the one that I see fluctuate as queries ask for memory grants and such. But you see the target memory of the pool, the total memory, the available memory in the pool. If any memory has been taken out of the pool, we’ll see that here.
Lots of good information. And I don’t know. I guess pool ID 2 is the better thing to sort of keep in mind there. I show all of these.
I used to filter these down some. But then I found in, like, some servers, the pool that I was filtering down to was not the one that was in use. So you’ll see this especially a lot in Azure, various Azure implementations, SQL DB, stuff like that.
Because that has a whole bunch of extra things in here for some reason. So I try to be kind to Azure SQL DB, even though I sort of hate it. Aside from the fact that it uses read committed snapshot isolation by default.
All right. So let’s dive in to what SQL Server looks like when queries have memory grant pressure on the box. Now, I have that same CTE query in here.
And we’re just going to – we don’t need 10 threads. We don’t need 10 iterations. But I want to do this so that you can see – well, we kind of want you to see a bloodbath a little bit. So now let’s kick this off.
And let’s give this a run. And we’re going to see some stuff change in here. All right.
We have this section here. We still have – I don’t know, we’re running about 9 gigs there. But now we see this has popped up to about 10 gigs. So the two lines that I want to focus on sort of off the bat are these. All right.
And so stolen server memory is, I mean, in this case, like in isolation, how much memory – like queries have used of their memory grants that have, like, really taken stuff away from the buffer pool. And the second line, SQL reservations, is how much memory – these queries have been given for memory grants. If we come down here, and we’re going to focus on this section first.
We’re going to look at the line that I was talking about before, where we have 40 gigs available. Well, we have 40 gigs in total, but we only have about 10 gigs available now because about 30 or so gigs has been granted out. All right.
31 gigs or so has been granted out here. All right. So we have all these queries running. All right. And of that 30 gigs that has been granted – oops, let me focus on that a little bit better. Of the 30 gigs that’s been granted, about 12 gigs has actually been used by the queries.
We have three queries that have been given their memory grant and seven queries that are waiting on memory grants. If we give SP Pressure Detector another run now, we’re going to see this resource semaphore weight show up. And we’re going to see this is the result of queries waiting to get memory grants to execute.
All right. So if we focus in a little bit on this line, we have had 24 tasks wait on average about 27 seconds to get a memory grant here. Now, all well and good, except I also want to show you what happens when we have queries sort of fighting to get memory on top of queries that are sort of fighting to get data pages into the buffer pool.
So I’m going to clear out wait stats because I don’t want any remnants in here. And now I’m going to kick off a second workload. And this second workload is going to just select counts from these tables.
Now, I don’t have any nonclustered indexes on these tables. So in order to do the count, we hit the entire clustered index. I realize that there are other ways to get a full count of like rows from a table that don’t involve getting a count from the table.
It’s not always realistic in all circumstances. Sometimes you need to filter certain values in the table, which querying DMVs will not allow you to do. So let’s let’s go.
Let’s run this by itself first for a minute. And let’s look here. Now, none of those queries are asking for a memory grant. So there’s nothing in here. But we’re going to start to see these page IO latch weights crop up.
And we’re going to see buffer pool memory start to come up as well, because we’re going to be reading data pages from disk into the buffer pool here. Right. Cool.
Now, let’s throw this one back into the mix. And as we do that, we are going to see this buffer pool slowly drop down as stolen server memory and SQL reservations crop up. All right.
So we went from 40 something gigs to 30 something gigs. Now we’re down to 20 gigs here. And I guess we went down to 24. So I think this stabilizes around here. But now we have a whole bunch of queries that are asking for page IO latch.
Well, that are emitting page IO latch SH weights. Right. So now all these queries that are running, they need to go read pages from disk into memory all over the place. And, you know, granted, this is a laptop with good local storage on it.
So the page IO latch weights aren’t going to be too, too terrible. All right. We, you know, we have about 2.5 milliseconds on average per weight, which isn’t awful. But, you know, these resource semaphore weights are still cropping up.
So right now, this is a, I think, what I would consider probably one of the more common scenarios that I see in SQL Server, where we have queries trying to put data pages in the buffer pool. They can’t because SQL Server has given chunks of the buffer pool out as memory grants to queries.
And so queries have to go out to disk, which is, of course, even with good local storage, not as fast as going to main memory. Right. And we can run this and kind of observe things a little bit as they go.
And, you know, we’re going to see things pretty, I don’t know, they’re going to stay pretty, pretty much the same where, you know, the buffer pool is going to stick around the 20 gig mark. The stolen server memory and, you know, all these reservations are going to stick around the 30 gig mark.
And, you know, the page IO latch and resource semaphore weights are going to keep sort of ticking up. And, you know, we’re going to see more waiting tasks. We’re going to see more wait time.
The average milliseconds per wait on resource semaphore, of course, over time has dropped down to about nine and a half seconds. Oh, there’s my, there’s the end of the green, there’s the end of the camera right there. That doesn’t look weird at all.
So, but anyway, that’s sort of the deal with that. So let’s kill these so that you can’t hear the laptop fans running in the background. And that’s probably, it’s probably good enough of an example of what happens when SQL Server is under memory pressure.
And, of course, these are the two, what I, in my experience, most common types of memory pressure, where we have queries that are fighting with each other to get memory to run. That’s a resource semaphore.
That’s memory grant stuff. And then those memory grants directly affect other memory consumers in SQL Server, like the buffer pool, because they reduce the size of the buffer pool while they’re running. All right, because memory has to come from somewhere.
SQL Server doesn’t work with pages directly on disk. Any pages, any queries that execute and ask for data, if that data isn’t in the buffer pool, we go to disk, we get it, we put it in, we start handing it out to queries, and then we go from there.
So, this is how SP, this is what you should look for in SP underscore pressure detector in order to figure out if there is indeed memory pressure on your server. Resource semaphore weights are a good sign.
Lots of page I.O. latch, underscore, probably most commonly, underscore SH and underscore EX. You might see those pop up to the top. Of course, we only see the underscore SH weights here, because all we’re doing is reading data.
We are not taking any exclusive latches on data pages to modify them. So, that’s kind of what you should look for. Of course, this is another good section to review in here.
If you are seeing any, if you see your buffer pool consistently a lot lower than, say, your max server memory setting or something like that, you see lots of stolen server memory, and the SQL reservations memory clerk might occupy a large percentage of that.
And then down here, you might see stuff along one of these lines where the available memory line drops off significantly. The granted memory line goes up significantly. You might see some queries that are granted memory, some queries that are waiting to get memory to run, and other stuff like that.
Another good counter to look at when you’re looking at this is the forced grant count. This happens when there is such significant memory pressure on the server. The SQL Server tells queries, well, screw you.
You’re just going to go run anyway. You’re just going to run with the minimum memory grant that you can get. So, that’s nice, isn’t it?
It’s really nice. Cool. All right. So, that’s it for this one. Thank you for watching.
I hope you learned something. Hope you enjoyed yourselves. Please, like and subscribe, if you don’t mind. If you have a YouTube account, if you have a Google account, you can do that.
And I will see you in another video sometime soon. Thanks for watching.
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.
sp_PressureDetector: What SQL Server Looks Like Under CPU Pressure
Video Summary
In this video, I delve into the intricacies of CPU and memory pressure in SQL Server, focusing on how to identify these issues through practical examples. We start by examining what CPU pressure looks like in a server environment, specifically looking at thread pool weights and SOS scheduler yield weights. I walk you through calculating worker threads for different versions of SQL Server, explaining why the calculations changed with newer versions. Additionally, I demonstrate how parallel queries can impact your server’s thread pool and provide insights on using SP Pressure Detector to monitor CPU usage effectively. By the end of this video, you’ll have a clearer understanding of how to recognize and address CPU pressure in your SQL Server environment.
Full Transcript
Erik Darling here from Darling Data, with Darling Data. It came from the data. He’s just emailing me. Never mind. Nothing important. And we’re going to continue our lecture series on SP underscore pressure detector by first talking about what CPU pressure can look like in SQL Server. That’s a pretty good one to start with. We’ll also look at what memory pressure might look like in SQL Server. And we’ll also, well, well, not in this video. That’s the next video. If I put everything in one video, it would be an hour long and you would die watching it. Who knows? Oh, and then in another video, we’ll talk about how SP pressure detector can help you identify which queries are having which issues. Don’t look so amazed. Anyway, one thing, like, I think the way that it most commonly manifests itself is that it’s not going to be a problem.
when servers are having CPU pressure. It’s sort of either thread pool, a lot of thread pool weights, or a lot of a lot of SOS scheduler yield weights, right? Just not enough CPUs to service all the CPU stuff that’s going on. It’s not going to be CX packet, CX, or any of the CX. It’s going to be SOS scheduler yield and thread pool for the most part. Now, there are various ways to figure out how many worker threads. you have. And this is generally the calculation that you’ll want to use. And this is the calculation to use for 64-bit server. If you’re using a 32-bit server, you… I can’t conceive of you at this point in time. It’s windy out there today. If you can hear wind chimes in the background, it’s because it’s a very windy day here. So this is the calculation that you would use for, say, modern-ish versions of SQL Server that have fewer than 64 cores in them. Now, Microsoft actually did change the calculation recently for servers with more, well, semi-recently, with servers with more than 64 cores.
Before I talk about that, I did get a user question. They said, why don’t you wear one of your t-shirts when you record these videos? Why are you wearing an Adidas shirt when you record these videos? Because I don’t have any of these shirts left. I did not save one for myself. I gave all of these away at various pre-cons, SQL bits and past data community summit. Sorry, community summit. And should I ever get invited back to one of those lovely venues again, you can be sure that there will be more available.
I think I have a pretty good grip on which sizes I need to bring now, so there won’t be a scramble to give away leftovers of lots of small and extra small t-shirts at the end. But let’s look at this impressive piece of documentation. What we’re going to do is skip this part, because if you are using a version older than this, I just feel bad for you.
So we’re going to focus on this section down here. So starting with SQL Server 2016 SP2 and SQL Server 2017 and up. This also applies to 2019 and 2022.
Of course, it doesn’t stop at 2017. That’d be absurd. The calculations that you want to use would look like this. So if you have fewer than or equal to 4, you get 512 worker threads.
If you have greater than 4 and less than or equal to 64, you get 512 plus the number of logical CPUs minus 4 times 16. And if you have greater than 64 cores, the only thing that changes is it goes times 32. Now, it’s a little confusing, because it looks like it’s 512 squared, but it’s really just a footnote down here.
I don’t know. It’s an odd design choice, that whole thing. But whatever. Anyway, that’s where I got that calculation from.
And that’s how I know that based on the number of CPUs I have, minus 4 times 16 is the number of worker threads for my server currently. So you should be glad to know all that. Now, the reason why this is pertinent to the conversation is going to be partially because of the thread pool thing that we talked about.
Now, on just about every server, you are going to see some amount of thread pool weights. My wonderful friend, Josh Darnell, a.k.a. Josh the Coder, a.k.a. the guy who keeps my website alive, did some digging. And he had an interesting blog post that I’ll link to in the show notes about how SQL Server will grow and trim the worker thread set, the pool of workers that you have, as the workload needs more threads and as the workload needs fewer threads.
And so you’re always going to see some thread pool weights as SQL Server grows and trims the pool of workers. Where you have to worry about it is where you have, you know, many, well, I guess you want to compare the total duration of thread pool weights to server uptime. And if it’s a rather significant portion, if it’s like 5%, 10% or higher, you might have a pretty consistent thread pool issue, especially if there are long average weights per weight on thread pool.
Right? You want to, queries are waiting many seconds to get threads to run. That’s not a good sign.
Now, where things become rather interesting, of course, is with parallel queries. Because parallel queries can reserve a lot of worker threads. And they can work towards exhausting your available threads a lot faster than a bunch of single-threaded queries.
So execution plans are turned on, so I don’t have to press any extra buttons. If we run this query and we get a count from the comments table, we will have a parallel query plan. All this stuff here is parallel.
And if we look at the properties of the select, and again, you know, the properties section is a really important thing to look at. I’m going to blow this up over here so I don’t have to do too much tinkering with moving around and stuff. We will see that this particular query, which ran at DOP of 8, well, let me show you guys to open up this, reserved and used 8 threads.
A parallel query can reserve far more threads than it actually uses. So that’s a good thing to look at if you’re troubleshooting a parallel plan issue. Now, let’s get rid of that for a moment.
Now, that’s a parallel query that has just one single liner, one single branch. If we run this query, which will join a couple other tables together, and we look at the query plan, eventually it will show up. I promise.
This one has some additional branches and has some additional parallel exchanges. We have some parallel exchange here. We have a parallel exchange here.
We have a parallel exchange here. And typically, we want to think about those exchanges as being stopping points to a particular branch of the query. Now, sometimes SQL Server can share threads prior to a parallelism exchange.
So if we look at the thread stat here, it looks a little bit different. Now we have two branches, and we have reserved and used 16 threads there. Now, what’s important to note is that there will always be a plus 1.
So if you have DOPS set to 8, it’ll be 8 plus 1. And you can see that if you look at a parallel operator. It doesn’t have to be a parallel exchange operator.
But if you look under here, you’ll see that there are nine threads involved. Thread 0 is this coordinator thread, and then the other eight threads are ones that are doing actual work. All right, so it’s 8 plus 1.
So it was technically 9, but don’t shoot. And if we run this third query, which is a little bit different, the join between… Well, the join that I do to vote is a little bit different.
And none of these joins make a… Well, I mean, this join makes sense. This join makes no sense. I just did this to get a different join operator between votes and users. But if we change this query a little bit to join comments to votes, and we join on post ID column, which makes far more sense, we will get what looks like a quite similarly parallel plan.
And it’ll finish eventually, I promise. I swear to you. I wouldn’t lie about that.
God, I write good demos, don’t I? Who writes demos better than me? Look at that thing. 19 seconds on the nose, just like I predicted.
But if we look at this query, and again, we go to the… Oh, hey, thanks, tooltip. Show up whenever you want. If we go to the properties here, and we look at the thread stat, now we have a third branch, and we have 24 reserved and used threads.
All right, so typically the bigger and the more branches you have in a parallel query. If you notice the difference between this plan and the plan before, I don’t want to run it again and make you wait for another 20 seconds, but the last plan had a nested loops join in it between users and votes, and that’s why we didn’t have an additional parallel exchange.
This one, we have parallel exchange here, we have a parallel exchange here, we have a parallel exchange here, and we have parallel exchanges here and here.
So these are all sort of potential separate branches to things. But since we had DOP8, and we had three branches in this plan, we had 24 reserved threads, even though we only have eight threads really working.
But again, it’s going to be plus this coordinator thread. All right. So with all that out of the way, we’re going to use SQL query stress again, and we’re going to run this query in it, which you may recognize from the remote DAC video.
And I’ve already got this window up here set to show me, or set to show remote DAC stuff. And we’re going to use SP pressure detector to check on CPU issues.
So right now, I don’t really have much going on here. I have, you know, this, some, some SOS scheduling weights, but I just cleared out weight stats.
We don’t expect to see much of anything. We have some CPU details in here that are kind of nice. You know, we’ll tell you if any CPUs are offline. This is usually what I look at, like look at first, but there’s some other stuff in here that’s, you know, kind of useful to know, get kind of sort of the lay of the land for the CPUs.
And then if there are any, if there are any points in the, since the server has been up that you have hit greater than 50% CPU, we’ll also log those over here, right?
You can see that. But we’re going to pay, we’re going to, we are going to be paying attention to this section over here for kind of the remainder of the demos.
So let’s do this and let’s get this and let’s hit go here first and let’s hit go here. And since we’re already, we’ve already have the DAC, what we’re going to see is which queries are running.
There’s a section here, which will tell you if there are any requests currently hitting thread pool weights. Now this is important because sessions waiting on thread pool do not have a session ID yet.
They don’t have that context set for them. So you can sit there waiting for a very long time on, you know, waiting to get a worker thread.
Weight stats are going to start to pile up as the workload kind of chugs along. But if we look at this section right now, we’re going to see that we have, you know, again, 576 total threads, but we have 618 in use, which means we have negative 42 threads on this server.
Now at the moment, we have seven requests waiting for threads. All right, you can see that there. And if we look down in this thread pool section, there’s one, two, three, four, five, six, seven in here that have been waiting on thread pool for a bit.
Now let’s run this again since the workload’s been going for a little bit. Now we have four that are waiting on thread pool. And if we look at the weights, thread pool has slowly crept up.
And what we don’t, again, you know, just sort of my contrived workload, we haven’t had a ton of queries waiting on thread pool, but we have had on average queries waiting like five and a half seconds to get thread pool weights.
That’s if my cursor will show up in the right place. All right, server, I’m really beating the tar out of you there. We have, these queries are waiting about five and a half seconds to get a thread to execute, and that’s where things can look no good at all.
So, you know, this is holding pretty steady. We have some additional requests. The query that I’m running runs kind of slow, so this isn’t going to like, you know, really give you like an awesome view of like that creeping up and like getting a lot of requests that are waiting on it.
But I think you can get the idea pretty well from that. So let’s cancel this stuff, and let’s just take a look at weights again. Now, remember the two things that I told you are going to really creep up when we are waiting on, or when we have CPU pressure on the server.
One of them is going to be ThreadPool, which is cleverly documented for you using SP Pressure Detector as worker thread exhaustion. And again, that held pretty steady around the five and a half second mark, which is not good.
We don’t want this to be a weight that queries are experiencing this kind of weight on. But where things are, I think, really interesting is the other weight that I told you can get out of control when your server is under CPU pressure, and that’s SOS Scheduler Yield.
So if we look at that, right, they have that weight here, SOS Scheduler Yield, which again is query scheduling. And if we come over here a little bit, we have had 151,215 waiting tasks.
And on average, they waited 157 milliseconds for CPU attention, right? That’s queries saying, I’m ready to go.
Can the CPU pick me up, please? Waiting for my CPU driver. So that’s not a good situation. And now this is a very exacerbated situation, but I think what we should keep in mind with SOS Scheduler Yield Weights is queries when they get a CPU, get on a CPU to do some executing.
You have this thing called the CPU quantum, I guess we can be close enough for that. And I apologize for keeping scratching my eyes.
Again, it’s very windy out today, and I was outside earlier, and I have like grit in my eyeballs that is making noise. I blink.
It’s fun. But so you have this quantum, which is a set amount of time that a query has to use a CPU before it jumps off and allows another query to have CPU attention if there’s another query waiting, or your query will just immediately jump right back on if there’s nothing else in line.
In this case, everything was screwed up, and that quantum, that set amount of time, once you factor everything in, ends up around four milliseconds.
I’m going to say exactly four milliseconds, because imagine measuring four milliseconds exactly every single time. Bonkers. It’s insane. Now, granted, there are situations where you could have a non-yielding scheduler where something is just dominating a scheduler and not letting it go.
I see that happen sometimes with weird XP command shell stuff, but, or like system stuff too, like buffer latch timeouts, you might see non-yielding schedulers arise from that.
But queries were waiting 157.4, bunch of other numbers, milliseconds, to get back on a CPU to get four milliseconds of CPU attention.
And that’s a really bad situation. So, we have learned the prior video, why you want the DAC, the dedicated admin connection, that’s so you can run diagnostic queries when your SQL Server is under a fair amount of strain, which is what we’re doing here.
We’re using the dedicated admin connection. And we learned that when we have a set amount of worker threads for our SQL Server, and when we start exhausting those threads, things can really start to pile up and cause trouble.
Now, you don’t have to hit thread pool to have CPU pressure, but I showed you two signs of it. The thread pool weights with long average weights, and the thread, and the SOS schedule yield weights really piling up to the top.
And especially if it has long average weights there, because that means queries are waiting potentially longer to get on a CPU than they’re spending on a CPU.
And that’s a really, really bad sign. That just means either, usually you, either, I mean, it can mean a lot of things, right? Your workload is just god-awful. You have too few CPUs to service the workload.
You’re, I don’t know, you have really old CPUs that have like one or two gigahertz, and they’re spinning too slowly. Maybe you have balanced power mode on your server.
A lot of things can happen in there. But anyway, I’m going to call this one here, and I’m going to start putting myself together to talk about what servers can look like when they are under a lot of memory pressure.
So that’ll be, that’ll be fun for you too, right? Anyway, thank you for watching. I hope you enjoyed yourselves. You’re listening to the grit in my eye crunch.
If you, if you enjoy this sort of content, you know, gritty, gritty eye crunches or not, you should like and subscribe to the channel.
So every time I publish something, YouTube will yell at you about it. You get 10 million notifications. It should be a lot of fun for you. And I think that’s about it.
Yeah. 22 minutes of me babbling here. That’s, that’s quite enough. All right. Thanks. Goodbye.
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.
sp_PressureDetector: Why You Need The Remote DAC Enabled
Video Summary
In this video, I delve into the importance of enabling the remote DAC (Dedicated Admin Connection) in SQL Server when troubleshooting a server that’s experiencing issues. I demonstrate how to turn on the remote DAC and use it effectively by showing its impact during a simulated stress test with SQL Query Stress. This tool allows you to connect as a sysadmin using SSMS, ensuring you can access your server even under heavy load conditions where normal connections might fail. By enabling this feature, you’re prepared to quickly diagnose and resolve critical issues without the need for additional setup when problems arise.
Full Transcript
Erik Darling here with Darling Data. And here’s an unnatural Clark Kent curl going on in front. Can’t have that. Don’t want anyone getting me confused with the Man of Steel. But, uh, today, or rather, I don’t know, maybe we’ll talk about more stuff today, so it might be a little premature to just say the only thing we’re going to talk about today. because we’ll talk about some other stuff today, is, uh, we’re going to get back to talking about how I use various, uh, community tools related to SQL Server troubleshooting. And, uh, I believe so far, we’ve talked about SP Human Events and SP Quickie Store. Sorry, SP underscore Human Events and SP underscore Quickie Store. And, uh, this one I’m going to continue to talk about, uh, my stuff. Because my stuff is the most important. And, uh, we’re going to talk about the importance of turning on the remote DAC, the dedicated admin connection, when we might need to troubleshoot a SQL Server that’s, uh, having some issues. And, uh, the store procedure that we’re going to be talking about for the next couple of few videos is going to be SP underscore pressure detector, which is one of my favorites. It is nice, well, used to be short and sweet, and slowly has accumulated some, uh, some, some length to it. Not so much girth, just, just kind of length.
And, uh, but it’s all useful, useful length, and useful girth. So that’s good. I don’t want useless length or girth. I’ve got some useless girth. SP underscore. Pressure detector does not, though.
Uh, one thing I discovered recently is that if this whole SQL Server thing doesn’t work out, I’m going to get super into shadow puppets on the green screen. I’ve got to figure out the lighting a little bit better, but you get the idea.
You’re in for a real treat. Oh, Microsoft goes bankrupt. Shadow puppets it is. So the first, uh, thing, so, SP pressure detector has code similar or exactly like this baked into it.
Uh, so that it will tell you if the remote DAC is enabled or not. So it’ll, it’ll do a check to see if this is set correctly. When this is set correctly, we will have, uh, remote admin connections, but the value in use will be one instead of zero.
Right now it’s zero. We don’t have that turned on, and I don’t have that turned on, uh, because I want to show you how to turn it on. And then maybe even how to use it should be a very useful thing, wouldn’t it?
So right now, uh, this is not turned on. And one thing that is, is good to know about this, and I’m going to figure out the right way to move here, is that this is not an advanced option.
If this were an advanced option, you might on your server need to, uh, run SP configure to show advanced options before, uh, turning this on. So, uh, actually one thing that I do want to, that I’ve always found kind of interesting is if you execute SP configure with no parameters sent into it, it will just list out all of the configuration options.
Uh, it will not tell you all of the stuff that, uh, selecting from the sys.configurations dynamic management view returns, like if an option is advanced and stuff like that.
So that’s kind of weird, I think. Maybe, maybe SP configure could just do a select star from sys.configuration instead of messing around with this other stuff, but, uh, I digress.
Um, as I usually do. Uh, so let’s, uh, let’s run this, and let’s reconfigure our server, and say, see up here, uh, we have changed our configuration option.
Viva. Uh, it doesn’t tell you that you ran reconfigure. It says run the reconfigure statement to install, so, you know, done.
But when you run reconfigure, or it doesn’t say, like, reconfigure complete, or anything like that. It’s just a, just a silent yes. Um, I know that there’s also a reconfigure with override, uh, that you could do there, but that is, um, that is if you want to set settings that SQL Server maybe doesn’t agree with.
So, I would, uh, I would avoid using that, uh, as a practice. Just, just reconfigure like a normal person. Now, if we go back and look at this, the results here, we are all set.
Our value and value in use for our lovely, talented, remote admin connections is set to one. All right. All right.
Good stuff. Now, another thing that, um, SP underscore, pressure detector, will tell you, is, it’ll look to see if someone else out there connected to your SQL Server may have possibly, potentially, uh, taken the dedicated admin connection.
And the reason that’s important is because only one person at a time can use the dedicated admin connection. All right.
One at a time. Now, uh, some people, or I used to get some guff back when I used to, like, care about what people thought. Uh, they used to say things like, oh, it seems like a security risk.
Well, it’s not. Uh, you can only use this connection if your login has sysadmin. And, uh, quite frankly, uh, there’s just not a whole lot more interesting you can do with the DAC than you could do if you already have sysadmin.
Uh, I guess there’s some stuff with, like, the, uh, the resource database you could do, but that’s really not, uh, all that interesting compared to what you can do if you’re already a sysadmin.
All right. Groove is in the heart, so they say. So, uh, I’m gonna get GitHub open. So, I was doing some work on SP underscore blitzlock today.
Making some improvements. Always improving. That’s me. You can tell them. Nothing, just always getting better at things.
Uh, so what I’m gonna do is, uh, I’m going to, uh, I’m gonna really beat the crap out of my server. I’ve got two instances of SQL query stress open.
And the reason I’m using two instances of SQL query stress instead of one instance of O stress is, uh, I’m getting some weird ODBC errors from O stress that I just don’t feel like figuring out right now.
Uh, I’d rather record a video. Uh, I haven’t recorded in a week because I was on vacation. I was in Paris, France. And, uh, you know, you just don’t come back from France wanting to, like, dig into ODBC errors.
So, again, groove is in the heart. So what I’m gonna do is kind of show you why you want the DAC when the server is having issues.
And I’ve got this, you know, kind of crazy, quirky query going on in here. And I’m just gonna fire both of these off. And it’s gonna be, you know, kind of crappy. Like, I know this, these videos are supposed to be about SP underscore pressure detector, but, you know, even running SP underscore who is active has a tough time.
And even just trying to open a new query window is having a really tough time generating a connection, which is no bueno, right? We’re just gonna sit, this is gonna just, this will eventually time out.
But I’m not gonna sit there and make you watch it time out. That’s just kind of cruel and unusual. But if I kill this, and, uh, I’m gonna try to cancel these. Sometimes this doesn’t work as well as I’d like it to.
But lo and behold, uh, you know, that, that worked out pretty well. And, uh, one thing that I should show you here is that, uh, so SQL query stress has a default time of, I think, 15 seconds. So, uh, right here you can see that there are, like, you know, we, we, we, we completed some, but there, you know, we had some exceptions as well.
Rather, I think we just had all exceptions there. I’m not sure. I’m actually not sure how that’s getting measured, but we had exceptions on both of these, uh, equivalent to the number of iterations. So I think, I think we had a lot of stuff timing out, trying to, trying to connect.
We only have 200 threads, so if we completed 178 and had 178 exceptions, that’s twice as many as we could possibly have, almost twice as many as, uh, the threads that we have allocated here.
So, um, I don’t know. Math is hard. My friends, math is hard. So, now let’s, uh, let’s, let’s change things up a little bit. So, uh, SP who was active barely got any of the way through.
We ran for 23 seconds. Um, you’re just gonna have to take my word for it on that one. I guess I could move this way. There we go. And I could do a really careful zoom and hopefully I don’t catch any, any weird nether regions over there.
And there we go. there’s our 23 seconds of waiting for who is active to return results when it didn’t. Of course I killed it because, you know, the server is boned. And I don’t expect who, I don’t expect anything to be able to run when a server is really under a lot of CPU pressure like that because we need CPU threads to do things and we had like saturated the CPU threads on this one.
So, what I’m gonna do is show you a tiny itty bitty little shortcut. Very useful shortcut. Very useful engine. This is our top of hat would say.
And I’m gonna right click and I’m gonna choose connection and then I’m gonna choose change connection. And what I’m gonna do is I’m gonna use the SA account because that seems to be sensible.
And then I’m gonna come up to this line here where it says server name and I’m gonna add the word admin and I’m gonna make me do it again.
Thanks SSMS. I’m gonna add the word admin in here admin colon SQL2022 and I’m gonna put in my password and no one look.
Alright. If you saw what I typed please do your best to forget it. And I’m gonna hit connect.
Now there used to be a funny bug in management studio where you would do the admin connection thing and you would get an error message saying like it didn’t work.
But it did work. And we can validate that it worked. Again I’m gonna have to move to the side a little bit and again hopefully you know what I’m just gonna do something. I’m gonna do some clever navigating here.
I hope. Or try to. Let’s see. I gotta go this way and this thing has to go this way and then we will eventually see lo and behold that we do have the admin connection here.
So that’s good. We have the admin and spwhoisactive will work of course instantly because nothing’s going on. That’s needless to say.
But now let’s come back over to SQL query stress and let’s fire these two bad dogs off again. And now with the remote admin connection we get results back instantly.
Alright. We can run this a bunch of times. We’ll get all the stuff back. We’ll see that we’re supposed to have like I mean like under normal circumstances because we have 200 threads from each of these going in.
Like we would like expect to see like you know 400 rows in there because queries are running they’re running for a long time but under these circumstances there are a whole bunch of queries that can’t even get to the server to execute because we have the saturated worker threads all over the place.
So I’m going to cancel these again before I set my nice laptop on fire. And we’re going to close this one out without ever actually having looked at SP pressure detector.
This is going to be the importance of enabling the remote dedicated admin connection. So when your server is having problems you don’t have to worry about doing it then.
you’ll be all set. You’ll have your special SSMS login with the admin colon server name. You’ll have everything ready to go and you can fire up whatever tools, scripts you care about to figure out what the heck is going on with your server and why CPU is at 10 million percent or whatever.
Again, math is hard. Anyway, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. If you enjoyed, if you did either, you should like and subscribe.
If you did neither, I don’t know, go get into my SQL. Who needs you? That’s how I feel about it. Go watch some green screen hand puppetry, I guess.
Anyway, thank you for watching. I’m going to record some other SP pressure detector videos today and hopefully if you are not enlightened in the least by this one, you will find yourselves greatly enlightened by future videos or at least enthralled, amazed.
My wife is texting me so I’m going to go pay attention to that for a minute. Upload this video. So, yeah, goodbye.
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.
A Little About Indexed View Maintenance In SQL Server
Video Summary
In this video, I dive into the topic of index view maintenance in SQL Server, sharing insights from my experience as Erik Darling with Darling Data. We explore a common misconception about the overhead associated with maintaining indexed views and demonstrate how proper indexing can significantly impact performance. By walking through an example where we create and maintain an indexed view, I illustrate the importance of having supporting indexes to minimize the time and resources required for updates. This video aims to demystify index view maintenance and provide practical advice on when and how to effectively use them in your database design.
Full Transcript
Erik Darling here with Darling Data. In this video we’re going to talk a little bit about index view maintenance. We’re going to talk about index view maintenance and create this lovely shadow right here. It’s because a lot of people have a pretty big misconception about how expensive it is to maintain indexed views. Now, I fully admit that adding an index view is like adding another index to a table. There is overhead to it. You’ve got to make sure that everything stays up to date in there. And I do concede that when you start doing things like creating index views, filtered indexes, computed columns, you know, columnstore indexes, that you are probably dealing with a particular index view. pretty large volume of data and that you probably have a pretty good reason to be a little terrified about how you’re going to keep all these different things up to date and in sync. So let’s dive right in. And this is not going to be a, you know, very technical video about things. This is, if you want deeply technical, I would suggest checking out, you know, index view maintenance is probably three or four blog posts about that at this point, maybe some stuff about bugs as well.
That have, but I said, but anyway, twice in a row, it’s a good lunch today. So if we run this query to update the post table, we get a query that takes about three seconds total to run. This takes about three seconds total to run because we have to update an index, a nonclustered index on the table to update the clustered index on the table. And right now we just don’t have a very good supporting index to find the data that we care about in the table. Okay. So, uh, bench line there, right? Two points. Now, if we, this view, uh, I say, I assure you, it has a very good reason for being created.
And we create this index on that view. Let’s create this unique clustered index. Uh, this index is going to take a little bit of time to create, which might be our very first sign that we’re going to have trouble doing index view maintenance, probably because we don’t have a very good index currently on the votes table to support creating the index view. When you create an index view, SQL Server has to run the query that creates the index view. When you want to modify the index view, you have to run the query that creates the index view to modify it.
If creating this unique clustered index on the index view, you can probably imagine that modifying the index view might take a decent amount of time. So let’s try that. Let’s run this and we’re going to do that same update that we did before. And we’re going to roll it back. And I’ll just keep in mind that I do have accelerated database recovery enabled for this database.
So I do not have to worry about the rollback time on this. The only things that I’m concerned about in this query are how long it takes to do that update. We went from like like 2.8 or so seconds to from read. And I think 12.984.
It’s probably close enough to 13 most people. I don’t know if he ran this a thousand times and took the average and probably probably probably pretty sure that we would hit about 13 seconds there. But if we follow the yellow brick costs or rather the yellow brick operator times in this query plan, we’ll find that most of the time starts We have to sort of assemble that index view in the We have this clustered index scan on votes takes about seven seconds.
We have, you know, some hashing things going on here. This one spills a little bit. Coming over a little bit more in the query plan. This takes about 10, 11 seconds in here.
And then finally, when we add in the 11.454 at the end of the branch that assembles the index view to the 1.5 seconds we get that it takes to do the actual update. We get about 13 seconds total. So doing a little bit of query plan analysis, we can probably guess that if we had a better index to support the index view being created, then we would have better performance creating or assembling the index view when we need to do that for modifications.
So let’s create index on the post ID column of the votes table since that’s really just about the only thing that we care about in here. From the votes table, you know, we’re joining on that column here. Everything else is pretty post centric except the ID column from the votes table.
But, you know, that is unique column anyway. And since it’s the primary clustered primary key of the table, it will be implicitly a key column in the index we create on the votes table. So now with an index on votes, let’s try this update.
Cool. Back to about three seconds. Remember that first iteration, that first modification that we did took about 2.8 seconds.
This doesn’t really add a significant amount of time on when we do this. So I’m pretty happy with that. If we look down here where the plan used to accumulate, used to be about seven, eight seconds in here.
We no longer have that. It’s about seven, 700 milliseconds. So a proper and properly supported indexed view in SQL Server. Too bird brained behind the scenes with it.
But, you know, I guess being bird brained is sort of a relative metric, isn’t it? Kind of is. Anyway.
After the clustered index view, we don’t really… …watching. I hope you enjoyed yourselves wherever you are.
Hope you learned something about indexed view maintenance. … … Final video of the day before I go to the airport.
Thank you. That was a really important journey after I come back. And bethe as well, the fact is for David and Tom and and use him to them to And to recognize our gender or his humanity has a more constant age. Thank you.
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.
A Little About Why IMPLICIT TRANSACTIONS Are Horrible In SQL Server
Video Summary
In this video, I delve into why implicit transactions are considered terrible, horrible, no good, very bad things in SQL Server management and development. Exploring the default settings of Microsoft’s JDBC driver and Python driver for SQL Server, which enable auto-commit by default, I highlight how this can lead to significant issues. The video examines the implications of using read committed isolation level as the default setting in SQL Server databases, except Azure SQL DB, where it’s a rare positive exception. Through practical examples, I demonstrate how implicit transactions can exacerbate blocking and deadlocking problems, making debugging and performance tuning much more challenging. By emphasizing the importance of switching to read committed snapshot isolation or using optimistic isolation levels, I aim to help viewers avoid common pitfalls and improve their SQL Server environments.
Full Transcript
Erik Darling here once again with Darling Data. And in this video, we’re going to talk about why implicit transactions are terrible, horrible, no good, very bad things. Now, part of why they are no good, horrible, very bad things is because they are the default auto commit option for the Microsoft JDBC driver and the Python driver for SQL Server. Whenever I talk to a client who is using either one of those, I know that I’m in for a long day of explaining the auto commit feature, the auto commit, I don’t know, connection string, element, whatever you call it. I’m not a programmer. I don’t know the names of these things. Flag. I don’t know. I don’t care that much. Call it whatever you want. But part of why that is a poorly chosen default is because of Microsoft’s poorly chosen default of read committed, the garbage isolation level, is the default isolation level for SQL Server databases, except Azure SQL DB, which is the one good thing I’ll say about Azure SQL DB. The rest of it is a waste of time and money.
Yeah. So because Microsoft made a big, big oopsie daisy back in like 2005, we have lived with the consequences and the consequences of billions and billions of NOLOCK ends ever since. So happy birthday, NOLOCK. So what I’m going to do is in this window, I am going to step through this script one thing at a time, and then we’re going to go look at a different window. I’m going to set implicit transactions on. We are going to run this update, and then we are going to run this select, and all that’s going to go fine.
And then in this window, we are going to go fine. And then in this window, we are going to set implicit transactions on. We are going to run this select, and we are going to wait for all eternity. Now, more recent versions of SP who is active, and I didn’t actually mean to close that, have added details about implicit transactions to the output. If you use the at get transaction info parameter, and set that to one. There we go. So you set that to one, and you will get a column a little bit further over in the results, called implicit tram. And we will see that implicit transactions are open for both of these queries.
And if we come back a little bit, we will have what appears to be a select query against the users table. I’m going to try to get my head right between these two. We have a select query against the users table, block what looks like blocking a select query against the post table. But that’s because we made a connection, we ran one query, and then we ran another query.
And because of the implicit transactions, SQL Server ain’t telling us the whole story. Now, the real crappy thing is, I mean, the deadlock XML and the block process report can already be misleading enough on their own, like capturing whatever query is in the buffer at the time that a deadlock or the blocking, blocking block process monitor loop goes through and find some blocking.
But this makes it worse because whatever query in the batch that was part of the implicit transaction was open, will show up in the block process report deadlock XML. And if you’re using a tool that, you know, like SP who is active that shows you what’s currently running, this will look very, very strange to you.
So implicit transactions cause a lot of additional blocking problems, at least, you know, from everything that I’ve ever seen with people using them. And they also make, well, actually blocking and deadlocking problems.
Part of that is because Microsoft made a bad choice with the default isolation level for SQL Server. And part of that is because implicit transactions are just a terrible idea. So if you are using the Microsoft JDBC driver, if you are using the Python driver to connect to SQL Server, I would strongly advise you to use read committed snapshot isolation as your isolation level, because you will avoid a lot of the really crappy blocking, deadlocking, and very confusing reports for analyzing locking and blocking.
So if I had to rate things, I would say user and optimistic isolation level, flat out, period, do it. Don’t use implicit transactions, flat out, period, don’t do it. If you’re going to use implicit transactions, because you’re too lazy to change your code, then you absolutely need to use an optimistic isolation level with SQL Server.
Otherwise, you will be creamed by the locking and blocking that goes on. Or you can do what everyone else does, slather your code in no lock hints, and just hope and pray for the best.
Hope and pray that you return correct data to your clients and nothing is ever wrong, and you never catch a weird update in the middle of anything. Because, Lord knows, that never happens.
Anyway, thank you for watching. I appreciate your time. I hope you enjoyed yourselves. I hope you learned something. I hope that you will choose to like and subscribe to this channel full of SQL Server wit and wisdom. And, again, I don’t know, I’ll see you in the next video.
And, again, thank you for watching. Thank You How Six Years man Shadow
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.
In this video, I delve into why the `ISNUMERIC` function in SQL Server is problematic and why it’s best to avoid using it. I illustrate how `ISNUMERIC` can incorrectly identify non-numeric characters as numeric, leading to potential data integrity issues. Additionally, I discuss my frustration with the `TRY_CAST` and `TRY_CONVERT` functions, particularly their inconsistent behavior when dealing with certain data types like unique identifiers. To address these challenges, I provide a regex-based approach using `NOT LIKE` to filter out values that contain non-numeric characters, ensuring more reliable results in your queries.
Full Transcript
Erik Darling here with Erik Darling Data, Data, Data, Data, or just Darling Data, Data, or just Erik Darling. We’re going to talk about why isNumeric sucks and why you shouldn’t use it and alternatives you have to using it. And I’m going to sneak in also why I get annoyed with the TRICAST function in some circumstances.
So, let’s start. Let us begin. Gather around, children, by putting all of these things into a table. A temp table, because I am staunchly anti-table variable. All right. So, we have a dollar sign, a comma, a period, a one with some commas, a dash, a plus sign, whatever car 9, 10, 11, 12, and 13 is, I sort of forget.
And let’s look at the output of this. Now, the isNumeric function is dead certain that all of these things are numeric. Not numeric, darling. Just numeric.
So, a dollar sign is numeric. A comma is numeric. A period is numeric. A one with two commas around it is numeric. A minus sign is numeric. A plus sign is numeric. And whatever the heck these blank ASCII characters are, are also numeric.
Now, where TRICAST falls a little bit short on us, and… Well, I mean not short. It’s not wrong. It’s just… So, like, I get it. Like, if it returns a null if it can’t convert something.
But then it returns a zero when it can. And that can lead to some confusing logic for some folks out there who are somewhat dimwitted, like yours truly. I don’t know. That’s about it.
At least right now. But, uh, if you use a sort of regex-ish expression, you will get what I believe correctly are zeros all down this line here. And I always have to look this up whenever I need to do it.
Except now I get to look it up on my blog because I have a post about it and I have this video about it. But if you say where T dot N, in this case, because my table is aliased correctly as T, and the column’s name is N. If you say where that is not like this expression right here.
Alright, this string. Percent, open bracket, caret, zero through nine, close bracket, percent. This will tell you if there are any characters in a row, well, in a particular, uh, you know, column row, that, uh, that are not the numbers zero through nine.
And I know this logic is a little bit obtuse and feels a little bit backwards, but that’s, that’s what we’re looking for here. So, that’s good. Alright, we can return consistent zeros for things that are not the numbers zero through nine, which are probably the things that we care about the most.
Um, if you wanted to make allowances for, uh, dots and commas, you could do that, uh, in here as well. But that is totally up to you. Now, where things get annoying for me with TriCast, and TriConvert, it’ll do the same thing for both of these.
What we’re looking for is, is the whole point of, can you try to do this for me? Is that you expect to get a null back if you can’t do it. If you try and fail, you should get back a null.
That doesn’t always happen though. Now, let’s take for instance, we do this. Explicit conversion from data type unique identifier to int is not allowed.
Then return a null. Give me a null. Give me a null. I know it can’t be done. I want a null back.
Now, this is a not terribly common occurrence. I agree. But, um, there are, uh, at least various points in SQL Server’s DMVs where, uh, there are SQL variant columns.
And those SQL variant columns, of course, are, uh, you use that data type, that horrible mistake of a data type. Because, they have all sorts of different things in them. Some of those things are GUIDs.
Some of those things are numbers. Some of those things are strings. Uh, there’s just all sorts of crap jammed into them. And if you want to try to cast that column as something else, or if you want to try to filter on where, uh, you know, try cast to something sends back, uh, a not null value.
Like, where try convert, try cast to try convert something is not null. Which, you know, again, isn’t the greatest idea query-wise in the world. But if you’re querying system DMVs, who cares really?
You can throw all the crap at those you want. And it’ll probably turn out just about the same. Because the code behind them is absolutely wackadoodles. So anyway. Don’t use this numeric.
Uh, try convert, try cast can be tricky. Uh, they can lead to some weird. Uh, logic hoop jumping that you may want to do.
And, uh, what I find to be the most reliable thing to do is use not like, and, uh, again, this expression right here to, uh, filter out rows that can, uh, values that contain things that are not the number 0 through 9.
If you’re aware of any numbers that, uh, are not, that are outside the bounds of 0 through 9 that could be used in here. I don’t mean 11, cause 11 is two ones and all that stuff.
Uh, then please let me know, cause, uh, I would like to patent them as soon as possible. Cool. Alright.
That’s good. Thank you for watching. Hope you enjoyed yourselves. I hope you learned something. I hope that you have been enlightened. I hope that you have had quite the epiphany watching this video. Uh, if you, if you feel enlightened and epiphanized, uh, please like and subscribe.
Uh, I’ll give you a dollar if you do it in the next 30 seconds, starting now. Uh, 30 seconds is up. Sorry about that.
I move fast around here. Alright, cool. Uh, thank you again for watching. You are beautiful.
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.