Erik Darling here with Darling Data. Uh and in today's video, uh we're going to talk about why your SQL Server is slow right now. I'm a little I'm a little blurry, hang on a second. I got to fix myself up a little bit. There we go. Now we're nice and crisp. Recently voted by Beer Gut Magazine as the most crisp SQL Server consultancy in the world. Uh so, it's always nice to get an accolade from Beer Gut Magazine. It's a nice change of pace from when they're threatening to sue or assassinate me. So, you know, ups and downs in relationships, restraining orders, stuff like that. Uh so, the things you're going to need when your SQL Server is slow are typically uh the remote DAC. That is the dedicated administrative connection. And you're going to want that because you're going to want a way to connect to SQL Server and run your analysis queries in a way that is not impacted by whatever awful explosion of of poop stuff is going on all around you. All right? You need to be able You want to be able to run your analysis scripts unfettered by the the catastrophe unraveling on your SQL Server. Uh you also need some useful analysis scripts like sp_WhoIsActive and sp_PressureDetector. Those are very good things to use in order to figure out exactly why your SQL Server is unhappy at the moment. All right? So connect to the DAC, use the scripts. Third-party monitoring tools are cool, but under sufficient pressure, they can't collect data, either. Uh you don't want your third You don't want your third-party monitoring tools connecting via the remote DAC to get stuff, because then it won't be available to you if you need to do anything. Uh so you know, it's Well, it's it's tempting, right, cuz you're like, "Oh, if I just have them connect to the DAC, they'll never miss out on anything, it'll be great." Unfortunately, uh you might want that for yourself. Um so don't don't don't do that. Um don't Please don't. It's great. But uh yeah, like you you know, if you might see if, you know, SQL Server really hits the skids at some point, you just might see big gaps in in your monitoring tool dashboard where it wasn't able to collect data, either. So at best, it can It can confirm when things hit the fan, but not which things hit the fan. And the reason why I'm doing this video is to sort of put uh a few different things all together in one place, because I still run into this a lot. And there's nothing more frustrating as an outside SQL Server observer than uh when someone says, "Hey, uh we had a problem 3 days ago and restarted SQL Server. Can you tell us what happened?" Because the answer is no. Once you restart SQL Server, you clear out everything useful that I could have looked at. Uh granted, I get it. Sometimes you got to restart SQL Server to resume, you know, those business-critical tasks like making money. All right? If you don't make money, what what do you do? All right? And I'm looking at you, CrowdStrike. Stink Stink bombs, you are. Uh so you know, restarting SQL Server, well, it might get things back on track, it removes, you know, just about anyone's ability to uh go like tell you what exactly happened at that time. Um it clears out all the useful stuff, you know. Um and it might also prevent some useful things from being logged. Uh now, there may be some evidence of what happened in other places like the error log. Um Query Store maybe sometimes catches things assuming it's query-related and not like something different. Uh but even then, the like the getting meaningful evidence out of Query Store after a tragic event is is kind of, I mean, it's it's it's a stretch, right? So uh what I want you to be able to do is log into SQL Server, use the remote DAC, run useful analysis scripts, maybe even take some screenshots, maybe even save some query plans, maybe you know, get some stuff out of out of SQL Server before you reboot it and uh get get business back on track. Um you know, usually, if you take an extra 30 seconds or a minute to get some additional information, it's okay. Right? Like people might sweat it out a little bit more, but you know, uh if you if you if you say, you know, uh "This'll help us avoid it in the future," so you know, most people are going to be okay with you doing some like, you know, forensic digging before the reboot. We're going to leave aside some things uh that should be happening, you know, in a more controlled way. So things like hardware changes, index changes, and settings changes, those aren't things that you should be doing while people are trying to make money. Those are things that you should be doing in, you know, low-impact times, uh not business-critical times. All right? You should avoid those things then, because you want to do those things when like, you know, you have time to like make the change, look at SQL Server for a bit, kind of just look around, get your palms a little sweaty, and figure out if that's a change that is okay to stay, or you need to roll back. Uh we're also going to leave like the unexpected failover off this because uh you know, the unexpected failover um is sort of is Surprise failovers are almost like auto-stats updates, and they're like sudden like kaboom, why is everything awful? Ah, that's why. Uh and let me tell you something. Um I I am never going to troubleshoot a surprise failover. That's not my deal. Uh I I I know people who will do it, so if you need that, I can point you in the right direction. But if you want to know why your AG failed over, uh this is not the face you want on camera. Um surprise stats updates are tough, too, because uh there are so many of them that could happen, and there are so many different things that they could affect, that it's like, you know, well, was it one stats update that bonked everything, or did like 15 different stats updates and like cause 15 different problems? It's very, very difficult to track that track that stuff down, and it's well-nigh impossible to do it if you've restarted SQL Server. Now, for settings changes, if something happens out of band, right, you know, I'm not saying who did it, uh neither will sp_LogHunter, but let's say uh sp_LogHunter will look through SQL Server's error log and it will track down what settings changed when, but not necessarily who did them. Right? You might be able to figure out who did them, because someone might suddenly like set all their Slack away messages and be like, "Nope, out for a jog! Couldn't Couldn't possibly have been changing MAXDOP and Cost Threshold in the middle of the day. Wouldn't be me!" Uh this this video is more about figuring out kind of uh what's currently happening, and the best ways to focus in on different aspects of what might be happening. Uh we're going to cover CPU pressure, memory pressure, uh out-of-band or long-running maintenance, uh blocking, and parameter sniffing. So we're going to the the the probably the five most common things uh I see uh when I'm troubleshooting SQL Server issues. Uh there have of course been some really interesting problems that I've seen over the years. They're just not common enough to put into a video, because you know, if if they're if they're outlandish, wild, like once to half a dozen times in a consultant's lifetime things, they're probably not stuff that you're going to run into often enough that you're going to get any value out of it. Uh so, let's let's get started before I mess something up. And uh the first thing that we're going to do is focus in on um uh CPU pressure. Right? So this is what this server looks like uh when everything is okay. Uh if we sort of focus in on this section here, uh we have 704 threads. There is some background stuff running that's used 82, so we have 622 available, uh nothing reserved, no threads waiting for CPU, no requests waiting for threads, and we have no current thread pool weights. All good signs. Uh another good sign is there are no queries running down here. This section is completely empty. Grand for us. So, uh let's stick this stored procedure into Oh, we already I already did that and I've already got 1,000 threads lined up for this thing. And I'm just going to kick this off, and I'm going to run uh sp_PressureDetector a few times just until we start seeing um uh THREADPOOL waits start to show up in here, because that's when we know things have gotten rather interesting. And they should start piling up. There we go. All right! Now we're cooking with gasolines. All right. So, I'm going to kill that just because we don't need to make things any worse. So, uh here's what we need to Here's what you want to look for. Uh basically, remember when uh this was a positive number and not a negative number, and uh these were all zeros? Those were much better times, weren't they? Uh everything on our server was nice and hunky-dory and we we were we were we were having picnics together and uh you know, we were we were eating crêpe- we were we were Lady and the Tramping crêpes Suzette, and it was lovely. It was it was it was truly a magical time in our lives. That time has passed. And what happened was we had a bunch of queries all come in and beat the crap out of our server, and this was not a very good time for us, no. This is this is this is times of times of war, famine and pestilence, plague. But enough about 2020. Uh what what So what we had all these queries running, uh and we exhausted our available pool of worker threads. Uh we we we went negative on the worker threads, and uh SQL Server was lining up queries to execute. Now, one thing that SQL Server will do when um when it runs low on worker threads is it will start downgrading queries to DOP 1. Now, if you notice that there's a uh there's a there is one query in here running at DOP 16 still that has 80 parallel workers assigned to it. Wowee! 80! This must be some query. Uh this thing has been, you know, this this is this is just one query that's running in parallel. The rest of these have all been downgraded to DOP 1, or actually there's I mean there's a few other 16s in there that are sucking up resources. So SQL Server does sort of like have a a protective mechanism in here. Uh the funny thing is that you will see um a parallel plan for all the DOP 1 queries, they're just it's a parallel query running on a single thread. So have fun with that. Uh so that's this is this is some of the stuff that you'll see when things get bad, along with uh all these null session IDs uh just waiting on THREADPOOL. Right? So this is the kind of stuff you'll see when you're under CPU pressure. Now, let's look at what happens when SQL Server comes under memory pressure, cuz that's an important thing to understand, as well. Uh by default, SQL Server is willing to give any one query 25% of your server's max server memory setting, and it's willing to give any group of queries up to 75% of your server's max server memory setting to run and do memory-granty things. Memory grants of- most often come from sorting and hashing. So I'm going to run- Well, we don't need 1,000 of these, that's just that's just mean. We're going to We're just going to do 10 of these. And if we focus in on memory here, uh we're going to see uh exactly kind of what happens when SQL Server runs out of available memory to uh give to queries. So, um here is uh where you can see what your um what your memory grant settings are for SQL Server. These come from uh these come from Resource Governor, whether Resource Governor is enabled or not. This is where the settings sort of come from. Um if you're on Enterprise Edition, Resource Governor can be a great way to turn that 25 number down to a much lower number so that you don't have to deal with um queries asking for insane memory grants and not using them. SQL Server is very aggressive when it comes to the memory granting. But what you'll see under memory pressure is uh you'll see probably something like this MEMORYCLERK_SQLRESERVATIONS number get real big, right? That's 50 gigs of memory grants. And you'll see some numbers down here start to change, too. So, um and I'll I I should I'm going to rerun this after I talk about this stuff so you can see that these numbers do tank down when when you're under memory pressure. So, uh you'll see we have three queries that have been granted memory, seven that are waiting for memory. Uh we'll see the 50.21 gigs that we saw before, right, of granted memory going out the door, and these numbers in here will will change, right? So uh these numbers in here will get lower, especially the uh available memory column. And uh if I give uh sp_PressureDetector a run with nothing going on, uh you'll see that that that total in these total and available memory columns will float right back to 60 gigs, right? So, uh one thing that I want to say real quickly, though, is uh once is if you don't know what's happening on the server, uh you don't have to worry about running sp_PressureDetector with any parameters. If you run it with nothing, it'll give you all the results and you'll be able to pick out from there what's going wonky, right? So just like a like a bare butt-naked run of sp_PressureDetector will give you weight stats, disk stats, uh what do you call them, PerfMon counters, tempdb info, memory info, um all like all the stuff that we've seen with the CPU and memory. Like it all it just it returns everything, so you get all all that stuff back at once and you can pick and choose what's from what's happening. So under CPU pressure, you'll hit a lot of THREADPOOL waits. Under memory pressure, you'll most likely hit RESOURCE_SEMAPHORE waits uh and uh maybe even RESOURCE_SEMAPHORE_QUERY_COMPILE waits, depending on how that memory is go- get- getting booted out the door. So th- that's a couple signs of like physical pressure on the server. Uh physical hardware pressure on the server. Another thing that you might run into is uh runaway maintenance. So, I'm going to focus on DBCC CHECKDB. Uh but, you know, it's kind of funny. I I I sure do wish that when when I saw runaway maintenance, it it was DBCC CHECKDB. Usually when I see runaway maintenance, it's some buffoon rebuilding and reorging indexes at 5% and 30%, because they read it somewhere in 2003 in a from a blog post that uh doesn't even have like working code formatting or and a bunch of broken picture links. Uh so uh as much as I wish that folks would listen to me and stop doing useless index maintenance, uh it still happens a lot, and I still have to talk people out of it. And uh some people will cargo cult around this use- useless facility until uh I until eventually someone pulls them kicking and screaming away from SQL Server. I don't You just can't help some people. It's something that I've learned after about 10 years of consulting is you you just can't help some people. They they they will just do whatever they want to do. They they find their comfort zone and they stick to it. Nothing Nothing can get them out of it. So um I ran DBCC CHECKDB uh before I started recording this so that we would have a uh log of how long it took, which is 24 seconds. I promise we'll we'll get back under my armpit there. 24 seconds for DBCC CHECKDB. Now, uh you know, you can think what you want about the 24-second number. Doesn't Doesn't make much difference to me, but if we run Now, if I if I ran this uh this query workload without DBCC CHECKDB also running, uh I want to say it took like 15-20 seconds. But if I run If I kick off DBCC CHECKDB here, you know, bloop, and I start running this here and go bloop, uh both of these uh both of these will suffer. Right? DBCC CHECKDB uh is going to take longer, and this query workload is going to take longer because they're happening at the same time. Now, if we If now, again, very simple and easy way to see if this is what's affecting your server, run sp_WhoIsActive, because you'll see if DBCC CHECKDB or any other maintenance task is running. Um now, things that I would absolutely check for: CHECKDB, index maintenance, and uh full and differential backups. If I I would expect to see log backups happening pretty often during the day, because if it's important business data, well, guess what? Log backups are a good idea. Huh? Yeah, they're great. Log backups, who would have thunk it? Amazing things. You see those running during the day, okay, it's fine. CHECKDB, full backups, differential backups, and of course, any form of index maintenance could be a pretty tough sell there. Um I would even say that like unless you unless you specifically schedule statistics maintenance to happen during the day, I would be also very surprised to see stats maintenance happening during the day. So um just to qualify what I said about useless index maintenance, statistics maintenance is not useless. Do that. It is good for you. It is a part of a balanced breakfast. But now, coming back to what I was saying, uh with the query workload running and CHECKDB also running, well, CHECKDB took an extra 10 seconds down here, right? Look at that, 35 seconds. Not Not Not Not on anything outlandish, because you know, this does have to sort of fit into a video that someone'll watch. And the query workload over here took 18 seconds. Okay, so this didn't change too badly, but the DBCC CHECKDB took longer. But one thing that I would look for, I mean, that is about I think 5 seconds worse than it was when when with without it. But anyway, uh one thing that I would absolutely look for is runaway maintenance tasks, because uh you know, DBCC CHECKDB is not is not a blocking problem. DBCC CHECKDB is like a resource usage problem. Um you know, index maintenance very much so could cause lots of blocking problems, right? Uh even with ONLINE = ON, you can still see blocking from that stuff. So be very careful there. Moving right along, another very common reason why SQL Server might be slow right now would be blocking. So let's come over here, and let's begin a transaction and let's do a very small update uh just to take out enough of a lock to cause a problem. And let's copy this, and let's stick this in here. And we don't really need to um, you know, give this a lot of threads in order to show you what's happening, uh but we do need to run sp_WhoIsActive in a very specific way. So we are going to run sp_WhoIsActive, uh we're going to use @find_block_leaders = 1. My finger disappears here, apparently. Uh so that we get the blocked_session_count column in our output. Uh we're also going to use @get_plans, because getting execution plans for these things can often help us figure out why blocking was going on for so long. Might be a very inefficient modification query, worth looking at. Um you might also be able to figure out if someone was running some sort of modification query when they shouldn't have been. Maybe they did the old forgot-a-WHERE-clause trick, I don't know. But anyway, uh this is still kicking, excellent. So let's run this. And what you'll see is very useful material. Uh I'm just going to roll this back so we don't have to I don't forget about doing that later. But if we look at sp_WhoIsActive, um we'll see a bunch of queries uh experiencing lock-weightness, and we'll see over here, we'll see these uh blocking_session_id columns populated with SPIDs. They will oft- often correlate with session IDs that you you see over here, which is a wonderful thing, cuz then you know who's doing what. And the real the real magical part of the way that I ran sp_WhoIsActive was that the results, instead of getting ordered by uh the duration column, the dd hh:mm:ss.mss, whatever, column all the way to the left, we ordered by blocked_session_count, so we knew who was at the very top of the blocking chain and, you know, who was suffering underneath them. Uh it is a little bit misleading to see this this SELECT query right here as a blocker for the other SELECT queries. That's just sort of the way it looks in the DMVs, and you know. Uh that's not real- that's not really anything that's, you know, going to change your life. You just need to make sure you understand that uh the reason this query has nine blocked sessions behind it because this query was blocking this query, right? So this this SELECT query isn't really at fault, it's this UPDATE query, and it just kind of looks like this one was doing something that the rest of them needed to do. So uh blocking, very, very common reason why you might see uh a server having problems. Depending on what the lead blocker is, um you know, like you might be able to just kill the lead blocker and, you know, let everything else move on. Um there are a lot of, you know, you get you you do have to be careful about rollbacks, because rollbacks can, you know, and if you don't have Accelerated Database Recovery enabled, rollbacks are awful long can be awful long-running, single-threaded, log-reading tasks that you don't want to deal with, because they can just make your blocking problems go on longer. Um so just be very careful if you decide to start killing lead blockers, um what ends up happening to them, right? Now, if the lead blocker is, you know, not a query, if it's some other weird thing, um it again, just be careful with what you're doing there. Uh another thing that can happen quite a bit, and that I see quite a lot in my uh consulting, is of course the old parameter sensitivity. Uh and I'm going to create a couple indexes here. And uh once these finish, I'm going to run uh this stored procedure a whole bunch of times in here. Right? For this one, I'm just going to do 100 threads uh and uh even for 100 threads, this finishes incredibly fast, right? Let's Even if I do 1,000 threads, this will finish very fast, right? This stored procedure runs very quickly most of the time. Now, if I dial this back to let's just say 10 threads so that we don't we don't overwhelm ourselves, and I run this stored procedure with a different parameter, all of a sudden, things will look slow. And we're going to run sp_WhoIsActive with a different set of parameters at the uh than before. Uh we are going to use the @get_avg_time = 1, and what this'll do is it'll look for prior executions of uh this stored procedure where um uh in the plan cache, and say, "Well, how long do you usually run for?" "Oh, that long? Well, that that's abnormal, isn't it?" And so what you'll see is a couple columns in here. This one is how long things are actually running for, and this one is how long things are usually run for. And that's where you need to be careful. So, anyway, let's let's wrap this up. Uh these are common reasons why SQL Server might be slow. Uh thank you for watching. Hope you learned something. Hope you enjoyed yourselves. If you like this video, uh give it a thumbs-up or a comment. If you like this video a lot, subscribe to my channel. I love you, thank you. Also, hire me to do figure this stuff stuff out for you, cuz I'm pretty good at it. Bye.