Name: The High-Performance DBA Series Ep3
Uploaded: 2026-04-30T16:16:07.582Z
Duration: 1 h 12 s
Description: The High-Performance DBA Series Ep3

Transcript for "The High-Performance DBA Series Ep3": Hi, friends. Welcome to another episode of High Performance DBA. Today, we're focusing on memory optimization and memory management. Thanks for joining. You know, I know you have a lot of other things you could and maybe should be doing, so I appreciate that you carved out a few minutes to spend this time with me. And I think you'll learn some things that hopefully will educate you and help you advance in your career as a database professional. My name is Kevin Klein. Been in the business for a long time, written some popular books. I've been a Microsoft MVP as well as a AWS community builder, Accolade in my background. The main thing I want you to know though is how to get in touch with me, which is [email protected] or on any of the social media platforms at ke klein. What are we gonna talk about today? As I mentioned, memory optimization. We're gonna go over some key concepts. We're gonna talk about how to optimize memory on SQL Server and how to troubleshoot it, and that's not easy unless you've got some tools. Some DBAs consider their SQL library of all their scripts to be, adequate tools for troubleshooting, and it can it can suit. However, there are better and easier ways to do that. So, before we get into the details, let's have the obligatory commercial since I work for a commercial enterprise. We make quite a few tools at SolarWinds for the professional database administrator, for the database developer, and for database professionals in general. The tool I would point you to if you are primarily a developer who has responsibility for databases is called database performance analyzer, DPA. It uses a weight statistics approach, which can be used on any relational database, to do your troubleshooting, and you'll see that come up later when I do a demo about how to do your own troubleshooting without any tools. Right? So it uses weight statistics and it works across 30 or more different database platforms. So very, very powerful for a heterogeneous environment with lots of different databases. It also has excellent AI built in, so it has anomaly detection and tuning recommendations as well as that really easy to understand weight statistics approach. The second product of, our three is SolarWinds observability for databases, which we call database observability. And this product is SaaS first, and it also monitors other non relational database platforms, so things like MongoDB, if you need that sort of, information about performance for as well as Postgres and SQL Server and so forth. You can also monitor other relational databases. And then finally, SQL Century. SQL Century is the deepest and broadest product for the Microsoft data platform. So unlike all the other products out there, you can monitor not just SQL Server and Azure SQL, You can monitor analysis services, reporting services, integration services. You can monitor, Azure SQL managed instance and Azure SQL, AWS RDS SQL Server. So it is extremely powerful and does things no other tools can do out there. You know, really obscure stuff that only hits the edge cases, things like numa node misalignment. But that is, just to illustrate the power of this product, and it gives it gives you incredible power, especially if your business relies on SQL Server to make its fortune. So you need that if you are business critical running on SQL Server. K? So that's my commercial. If you are interested in seeing any of these, you can mash that speak to an expert button on the webinar, and we will set up a meeting with you, one of our very talented solutions engineers who are all themselves former DBAs, or you can click on the hyperlink that I have there for you, solar winds slash interactive demos, and you can play with live products in the real environments right now. So having said that, what's the deal with memory on, SQL Server and on databases broadly? But what we have is, I'm gonna use an analogy that my kids love. Six of my kids, of seven, are girls who love Bridgerton. And, so they like that old time English gentry sort of, sort of, scenery and that sort of thing. So imagine you're the the lord of a manor and you want to have a nice bath. Well, that means you have to send out, one of your servants to draw water from the well. They then take that water, put it, you know, into a bucket. They have to put that bucket over a fire and warm it up. That water then gets loaded into, you know, poured into the bathtub for the for the master to or mistress of the manor to take their bath. And then maybe you've gotta make multiple trips so that you can fill up that tub. And what if the tub is tiny and so the the water is always spilling out, splashing out? Right? So you gotta go back and get more. And it's really very, very similar to how memory management works in relational databases. And I would also point out, just as people carrying water around is heavy and difficult work, the interplay between memory and the IO storage subsystem is very heavy hard work. In fact, that's one of the most common places we see performance issues. So think of the well as where all the data is at. We have to get access to that, take it, and manipulate it by the database engine using our buffer manager, which knows where to find all the data and how to manipulate it properly, how to warm it up. It loads it into the cache, which is the bathtub. And if that splashes out, if you can't fit enough water in there, if you in other words, if you can't fit enough data in there, then you've gotta go back for more and keep moving it in and out. And that is a real really difficult orchestration of all these different components. And so, it's important to recognize how much work is involved here. And, of course, if the master is huge and the bathtub is tiny, there's barely enough water in the bathtub. So it's a constant refresh, and the analogy holds. Right? If you've got a two terabyte database, but you only have 64 gigabytes of RAM, your IO subsystem will be working hard loading things into memory, flushing them out, evicting them from the caches, loading the next bit in, and so you can see a lot of churn in memory as a consequence. So how does memory work in SQL Server? Well, you know, if you look at the image here to my left, you look at the image, you've got this large white plane, and that is the Windows memory, the total amount in that server. It could be a virtual server as well. It doesn't have to be a physical server. That's the max limit of available RAM on that instance. There are a number of parts of memory used by SQL Server that are not actually stored in what's called the working set. That is the amount of RAM dedicated to SQL Server and its different caches. Okay? So there those are things like COM objects, link server objects, or providers like Ola DB. These are extended stored procedures that are compiled into DLLs. Those all run-in memory space outside of SQL Server. And then we have the Windows heap too. So for all the threads, thread management, network management, and so forth. And you never want to leave, memory unavailable for the Windows OS because it will cause paging. And paging is the absolute slowest, most restrictive bottleneck you can experience. So I always recommend you at least leave two gigabytes of RAM available for the OS. Now inside of the Windows OS memory, hopefully, your your Windows Server is dedicated to SQL Server. This makes a lot of difference as we go along and look at some of the other settings and so forth. So if you have other applications on that SQL Server, maybe you only have one in, one Windows Server that is used for all your data. So you have, oh, let's say you have analysis services on it as well as SQL Server. And, you have, a virus scanner and some other things like that. All of those need memory. And if you don't have enough memory devoted to those, they will be starved of memory. Conversely, they can starve SQL Server memory and take memory from SQL Server when it could really be putting it to to good use. So keep that in mind. You want to have a a dedicated SQL Server. Now let's say we do have a dedicated SQL Server. There's nothing but Windows and the SQL Server itself running on that ins on that particular Windows Server. Then within the SQL Server process, there are different areas of memory that we call caches. Right? So the best known is the plan cache and the data cache. So the plan cache, contains all of the stored procedures, SQL statements, triggers, user defined functions, all of those things that are code are stored in the plan cache. The data cache contains all of our eight k pages of indexes and data, so the tables themselves. Right? And, there's also in that allocation, a lot of small caches. So we have token and permission cache. We have hash buckets. We have sort caches and log buffer caches and all kinds of little small ones. But, they don't make up a huge amount of that space. Right? Those smaller caches are only about 5% of the total space set aside for for SQL Server's memory. About 10%, it can vary. SQL Server can, modulate that up and down according to the internal requirements of the system. The plan cache, all things being equal, is gonna be about 10% of the total amount of cache. And then the data cache is gonna be the largest allocation, probably, it will as much as as it can have, usually about 60 to 70%, somewhere in there. Then we also have the separation between single page allocations and multi page allocations. Things in the multi page allocation would include, memory use for memory grants for SQL queries. So if your SQL query looks at all of the, parameters of your query, and in the execution plan you see it has to do a clustered index scan. Well, a clustered index scan means it has to read the entire table because the clustered index exists for every row in the table. So a clustered index scan means that, oh, SQL Server looks at it and says you're gonna need a big memory grant because this is a big table. So that will give the, the memory grants will reserve that large amount of memory in in order to execute your query. And then there's other things that happen in that too, such as your backup, your backup buffers, your network packet buffers, CLR heaps if you're using anything like that. The the thread stack also is a a set of very small single digit kilobytes of RAM needed to support each of the different threads as they connect and disconnect to the SQL Server. So, you get a SPID. You execute a query. Each lock needed by that, query will use a couple bytes of RAM. Each, each connection will use a few bytes of RAM. And so it's, it's another aspect of SQL Server that requires your attention. And then there are also the memory handling, structures. Those are the ones in the middle of the diagram, memory nodes, memory clerks, memory objects. You'll see a lot more about those in the demo in a minute. But, basically, think of each of these objects, memory clerks, for example. They are your little tiny accountants that take a look at everything that's happening and keep up with all of the requests for memory and how memory is being used by the different processes in the SQL Server. Also, just a a a quick point of reference. If you're on older versions of SQL Server and you're on Standard Edition, your max memory is 128 gigabytes. If you're on SQL Server 2025, then it's double that two fifty six gigabytes of RAM. If you are on Enterprise Edition, then you have a two terabyte max, unless you're running on Windows Data Center Edition. And if you're running on Windows Data Center Edition with SQL Server Enterprise Edition, Technically, Enterprise Edition is limited only by the amount of RAM on the operating system, and Windows Data Center Edition allows, many many terabytes of RAM. I believe it's 24 terabytes of RAM. Okay? So that's just a quick overview of what happens in memory. And inside of memory is a very important set of lessons to learn about how SQL Server manages memory. It's, this beautifully orchestrated clockwork mechanism that puts things into memory and decides how to best get rid of things, that's called eviction, how to get rid of things at the appropriate time. And so what we have here, and I'm gonna use a plan cache because it's a little bit simpler to explain, but it it applies to the data cache as well, but with more nuance. So what we have in the plan cache is every time a SQL statement or a stored procedure or something like that is executed, it is instantiated, and loaded into the plan cache as the optimization process occurs. And SQL Server monitors that using several really important processes, as you see on the screen here. I'll explain those in a minute. So let's say we create a new stored we invoke a new stored procedure called get order. SQL Server uses something called an LRU dash k algorithm to determine how long they get to stay in the cache before they're evicted. And what that means is it first creates a complexity value. This is how complex that stored procedure is. And then along with that, it creates a complexity countdown timer. K? So we see get order is more than double the complexity of any of the other objects in the plan cache. K. And this process called lazy rider wakes up about every minute or so, although it can be invoked by any SPID for for its own purposes to to verify this. LazyWriter looks around, and as it looks around the cache, it says, okay. Has anyone failed to use one of these objects in the cache since the last time I was here? And if no one has used it since the last time I was here, I will decrement the countdown timer by one and happily go on my way. A little bit later, it gets reinvoked. Right? This is happening all the time. In fact, if you watch with SP who or SP who two or activity monitor, you watch the SPIDs, you'll see there's a ghost, there's a lazy rider and a ghost cleanup SPID. Right? And so lazy rider goes to sleep for a while, wakes up, takes another look around. Hey. Any of these SPIDs get used? No. I'll decrement those again so the countdown timer is constantly going down. Some of them will hit zero. So next time, the lazy rider comes back, what do you think happens with those that are zero? Well, the correct answer to all technology questions is it depends. Okay? And what does it depend on? It depends on how much memory pressure there is. If there's not a lot of memory pressure, why don't we just keep it in cache in case somebody does execute that stored procedure or ad hoc query or what have you, so that we don't have to go to all of the expense of creating a new execution plan. On the other hand, if there is memory pressure, then we are going to evict anything that is zero or below because those numbers, again, if there's not memory pressure, they can get bigger and bigger in the negative space. Right? So, lazy rider will look around and say, some of these have hit zero. We have memory pressure. Ghost cleanup. Come clean this up. And Ghost cleanup is our garbage collection process. So it goes through, and anything that's in the cache, according to how much memory pressure there is, it's evicted from the cache. If a a really, really big stored procedure that was 30 megabytes in size got loaded into the cache, the Ghost Cleanup would take a look and see all of the objects that were the lowest value on their complexity counter, and it would throw all of those out, evict all of those until it got to the point where it had enough space available in the cache for that new stored procedure. So there are things being thrown out, things being forced and pushed out, and then things being loaded in. On the other hand, the LRU, least recently used dash k, the k part says, if it's complex, let it survive in the cache longer. So what that means is those objects that have a a complexity counter, that's pretty high, Well, if someone comes along and uses it, then we will refresh the countdown timer and it is given new life and is able to count down all the way back to zero or below once again. K? Same sort of behaviors in the data cache, except with the data cache, we have, a variety of different kinds of objects, whereas in the plan cache, we basically have execution plans. Okay? So that's that's the big difference. They are all aged in a similar sort of manner. They're they are also impacted by a very important process called the checkpoint process. So when data is loaded into the cache and modified, it's considered dirty. It's a dirty page, has not been hardened to disk yet. And so the checkpoint process, which also runs on a regular frequency, that's the process that takes things that are in memory that have changed and says, I need to make sure that those are pushed to the disk, for into the database files so that they are also updated there. K? So one last thing to remember, and I like to call it the superpower, which is page compression. I've done a ton of research. My company actually, performs tuned SQL Sentry as much as we possibly could, and we tested over 2,000 concurrent user workload on our SQL Sentry database with the different kinds of compression available for us. And, you know, we're very curious because we've known lots of BBAs who have said, well, I'd love to get that big savings. And our savings generally was around 60%, so the amount of space consumed on disk and in memory was about 60% less than without it. The DBAs would then say, oh, that's great, but, you know, it's gonna take a lot of CPU, and my server runs hot on CPU. Well, in our testing, except for when you first compress a table using page compression, it takes 0.6% additional CPU on a daily basis. So it's almost free performance. Definitely enable that page compression. There are edge cases. If you are very curious about this, drop me a note, and I will give you all of our research. I did a presentation on it, so I have a slide deck. Be happy to share with you. K? Now optimizing memory itself. Alright? Most of this is about best practices ahead of time. As I as I mentioned, you want to dedicate as much RAM to SQL Server as as you can in proportion to what the server has on offer in totality. Right? So if you have a Windows Server that has half a terabyte of RAM, then you wanna give as much of that as you can to the SQL Server and cap it. So one of the things that we see is that a lot of people will just leave these defaults on here, and it'll just be on autopilot. And for many workloads, that will work fine. But what if you're one of those, edge cases or you're one of those environments that pushes a really hard workload, you're much better off setting a minimum value, maybe one or two gigabytes, and then a maximum value, like I said, if it's dedicated to SQL Server, then set aside at least two gigabytes for Windows. If you have other services also running on there, analysis services, well, you better give that four or eight gigabytes. If you have integration services or reporting services. They will need memory too. So take that top line max number and decrement from that so that you don't have a, you don't have that external memory pressure of different services fighting each other to get access to that RAM. It's also important to set both of those values, particularly in the older days, if you have an older edition of SQL Server. It kinda didn't believe you, and I'm I'm using a narrative structure here. Technically, the details are quite involved. But it kinda didn't believe you if you said two terabytes of RAM. So it would always check to see how much RAM existed on the server, and that take a couple cycles on the CPU or a CPU. And if you have thousands of users, and the the, the ghost cleanup process, the lazy writer, the checkpoint, they're all doing their things. They're constantly asking for that memory to be, you know, tell me how much memory I've got again. And so you can be losing quite a few cycles if you're on an old version of SQL Server because you just left that number at the default. Don't do that. Also, there is a setting called lock pages in memory. I strongly encourage you to use this if it is a dedicated SQL Server instance. What happens there is that as your working set grows so say, again, we have five twelve gigabytes of RAM on our SQL Server. We've reserved 16 gigabytes for everything else. So that means, that the SQL Server has nearly 500 gigabytes of RAM. As tables are loaded into the cache and different procedures are loaded in the plan cache and things in the the backup buffers, all that, as the cache is warmed up, it grows and fills until it hits that maximum size. And when you say lock pages in memory, it doesn't let the working set go under that. K? And so that means it reserves all of that memory for SQL Server's use. Now SQL Server may load things in and out of that memory space, with that working set, but it reserves it for itself. And that's kind of a natural behavior for SQL Server is to grow, to consume as much memory as it has available to it. This is why this is why systems admins are always getting in trouble with their SQL Servers, because they just use task manager to see how much RAM is being used, and SQL Server is built to use all the RAM it can get. And so they make things really bad by restarting the SQL Server or restarting the whole Windows Server, and that means everybody has to wait for all those plans to be recompiled and loaded into the cache. Everybody has to wait for the working set to be reloaded into the cache and grow over time once again. So it's a that's an anti pattern if you use task manager to decide when to change or flush the caches for SQL Server. And, again, like I'm saying, don't restart just because you think there's a problem. That is gonna cause lots of performance problems even, further on down the line. K? Now some additional memory optimization techniques. I mentioned your page compression superpower. There's also app optimized for ad hoc workloads. And what this does is it affects the plan cache. And my typical catchphrase for the, optimized for ad hoc workloads is I have seen it help in many situations, most situations, in fact, and I've never seen it hurt. What happens is when you invoke a stored procedure, execute an ad hoc piece of SQL, SQL Server will no normally put the whole plan into the cache. What if it's a, you know, two or three megabyte execution plan? Well, that that's that can add up, especially if you have thousands and thousands of them. But if it's an ad hoc SQL statement that's not likely to be reinvoked anytime soon, what's optimized for ad hoc workload does is it puts a plan stub in the very first time. So it's a placeholder. It's not that full two megabytes. It's a much smaller allocation. And the second time someone executes that select statement, let's say, that's when the whole execution plan is loaded into cash to make it easier for the next person. So that is gonna be a big saver of, of cash, especially on the Plancast side of things. As I mentioned in the analogy about carrying water, that water is heavy. So if your developers always write select star from my table and they only need three columns out of 16, that means your your poor little bedraggled servant at the manor house is making lots of extra trips, and they're actually filling up the tub of our memory allocation, our memory, space, the buffer pool, with a lot of stuff that we're not gonna use, and all of that needs to be controlled and managed. Keep your memory grants I'm sorry. Keep your, keep your indexes fresh. Make sure you update statistics frequently. You don't necessarily have to rebuild your indexes or reorganize them, but you do need to update those statistics frequently because memory grants depend on those statistics to determine how much SQL Server is going to give to that query in terms of memory. So make sure those are up to date. Also, partitions can be very helpful if you have, like, a a an old old data that's very seldom used. Put those onto a specific partition and set that partition to read only. A couple things happen there when you do that. Plans from a read only database or a partition aren't cached. And, also, they don't engage the full locking engine either because it's read only. We don't have to do all of that. So it can save you quite a lot of of those system resources. You also don't have to keep backing up a read only partition because, you know, the data is not changing. So once you have a good reliable backup file, you don't have to keep doing that. And, you can use Resource Governor as well to put caps on what individual queries can consume, individual users or applications or databases can consume. So you can put really granular controls in place with Resource Governor. And then as I mentioned on the previous slide with, Sys admins, they like to restart the server, but there's a lot of downsides to doing that. It causes CPU spikes reloading the plan cache. It causes IO spikes reloading the data cache. So it's it's a problem. If you are a DBA, you might wanna use the free proc cache statement to clear the the plan cache, the d b c c drop clean buffers statement to clear the data cache, and then there's also, d b c c free system cache that will enable you to to flush out either all of those little system caches or individual ones. So you could say, token per in token and perm, for example, would just clear the tokens and permissions cache. Right? Now some other things that we see are things we do ourselves, and I'm gonna show you these two in a demo in just a moment. Plan cache bloat. So there's a lot of things that we do as developers that can cause us to load a lot of additional plans into the cache. So I'll show you that in demo. One thing to note too is that if you have set conditions, like set ANSI NILLS on and you have it defined on your client with a connection string and it's different than the server, that means SQL Server will have to cache two different plans. One with an set ANSI Knowles on and another set ANSI Knowles off. Same thing with coalish coalations. So if my desktop has, British English and then the server is set to American Standard English, those coalations are different. Someone has, French on their laptop. There'll be two plans of the cache, one for the French coalish collation and one for the standard English coalation. So those cause bloat too. I am doing troubleshooting, and the way I like to do the troubleshooting is, first, I wanna know, has anything changed on the server? Maybe a big new deployment of our app. And so it's caused all kinds of things to operate differently and memory to act abnormally. Maybe I'm seeing a lot of anomalies. That would be the first thing I would check. The second thing I would check is whether there are errors. First, outside of SQL Server. So if it's DNS errors or domain controllers or something like that, I don't even have to check SQL Server. But if it's inside of SQL Server and I find an error message, that's often a good thing because then I can troubleshoot according to the information in our technical documentation, our notch base, to fix that error. The next thing I need to do, step three, is to check those weight statistics. Even though the tool called, Database Performance Analyzer is built around that, you should build your troubleshooting around that too. Once we have determined what the top wait stats are and are causing our end users and processes to wait, then we want to correlate those wait statistics with other forms of telemetry, DMVs, extended events, perfmon counters. And then finally, step five is to conduct a post mortem assessment and, if possible, to adjust our alerting and improve our knowledge base about how to resolve those problems. Troubleshooting memory, just using native tools and the T SQL commands available to us, the DMBs and other sources of telemetry, it takes a lot of knowledge and experience. In this script, I've also included in in the notes here some T SQL commands that you may find useful from time to time, like how to flush the values out of certain DMBs using the d b c c SQL perf command. But in my typical workflow, one of the first things I always start is I look at the error logs. Of course, you can also look at the error logs under the management folder of SQL Server Management Studio. But let's just go forward with using the T SQL right now since it's all right here available for us. And since this is a a demo server and it's rather quiescent, there's not a lot going on. There's not a lot of errors, which is good. The next thing you wanna do is go to your wait statistics and make sure that the wait statistics are a memory related weight statistic. As you can see here, I've listed those out. I recommend that you look at the SQL skills blog post on weight statistics by Paul Randall, and also look at their SQL weight statistics library. If there's ever a weight statistic that you don't know what it does or what it's for, that library will explain all of it to you. One of the things you'll notice right here is that there's a lot of wait statistics that we do not care about. We have that not end list. These are all natural, normal, and organic sorts of, wait statistics that accumulate, just from the normal activity of SQL Server being available and running. So those are the kind of things that we don't care so much about. In this case, this shows us all wait stats that we have on the instance of SQL Server and and arranges those by the highest wait statistics. I've also got a version here that looks at the wait statistics just in terms of those that are related to memory. And so if I execute this and I were to see, okay, what's the biggest wait statistic related to memory? Page latch exclusive. So that means write operations like insert, update, and delete. We see, page IO latch. We see, other latch weights. Fortunately, we don't see the really dangerous as many of the really dangerous ones as major, as major concerns here. Alright? Things like resource semaphore, which indicates that we have memory grant issues. Here's a query where we can take a look at all of the available server memory and get a a feel for how that is, and we want to see this available physical memory is high. Also, there's an old command that's really cool, and, it is d b c c memory status. It tells us some things that no other command will tell us, in particular things like, virtual memory. So if we look at those, we can see if we have any virtual memory pressure put on our SQL Server. When we wanna look internal to the SQL Server memory allocation, we might start with just seeing how it is divided and how, if we have grown to the full working set size. We can also see that large pages are not enabled here. Remember those memory cards I told you about? Well, they give us a ton of information about how memory is being used in SQL Server. So we can see here that we have, for example, in the cache, we see SQL compiled plans is one of our highest. But the biggest by far, and this is what you do want to see, is that SQL buffer pool. So that means the data cache is the biggest user of memory. We can also see by database. So if we saw a database besides our main user database as the biggest consumer of the buffer pool, that would be, a yellow flag, if not an outright red flag. Now if we go into one of these, databases, let's say, the DPA repository, and then we execute this next command, we'll be able to see all of the objects in the cache that are consuming space in the data cache. And so this will also help you to determine whether there is, kind of a a misallocation according to, you know, one table. Why is it in the cache so much more than off, often than others? So you can kinda figure out which you might need to do some index tuning on. Also, we need to look at the plan cache. And so here's a query that tells us all of the utilization of the plan cache by type. Right? So whether those are, SQL plans, stored procedure plans, extended stored procedures, and so forth. And we can see those in more detail by looking at the DM exec cash plans, DMV, and we can get a little bit more detail, parse trees, whether it's a compiled plan like with a stored procedure, whether it's an ad hoc SQL statement or a prepared SQL statement. And then we can also just look at the queries to see which are gonna be consuming the most, the most memory. Right? And so in this case, we are looking at, by a number of different factors, how expensive these queries are, how much time they take, how much they read into, physical reads, for example, how much they read into the data cache and so forth. So we can see which of those particular SQL statements might be problematic. Now I also mentioned that we really want to correlate what we see in the weight statistics. So if we saw in the weight statistics a very high memory related weight statistic, we wanna correlate that with other kinds of metrics, either using extended events or perfmon counters. In this case, I just went with the good old DMOS performance counters. And here we can see, for example, the most important one I always look at is memory grants pending. You want this to be zero. If it's higher than zero, you don't have enough memory for some reason or another and need to further investigate it. And then I have a couple additional queries, that I'm calling appendix, so you can take a look at those as well. So working all the way down through that is hard and will take a lot of assessment and analysis to figure out whether you have specific memory problems and what those memory problems are. Just a quick recap for you here on this slide. These are some of the things that you will need to remember and put into your brain, or into your SQL light, library of SQL scripts so that you can use these again in the future. Now I showed you how to do that with the native SQL scripts. Let me show you how to do that with the SolarWinds database tools. So here I am in DPA, and I'm looking at a specific SQL Server that has indicated that it has memory issues. Right now, I'm looking at the wait statistics per day over time starting on April 7, and you could see the wait statistics vary over time, but this is wait statistics by the SQL statement. If we look at it just by weights itself, then we can see that there is some definitive issues going on. Now here, we've got memory and CPU as an issue consistently. Right? And if we look down, this is where the real power comes in. We can see we have anomalies. Right? So any of these, we could drill down even further, look at that particular time period, and determine exactly what the root cause was of that time period. And we can also see what advisers are going off for this particular SQL Server. We see that there was a a plan with a full table scan on the fifteenth. On the fourteenth, we see that we have lots of lots of other kinds of issues that are popping up. And we could go right to the resources and look at all the resource consumption on the the server over time. We wanted to look at it here. Same sort of thing I showed you with the DMV queries that we are doing earlier, except it's getting right to the the heart of the matter. And if we look at this, we say, okay. We've got these memory and CPU issues. What is it caused by? So it will teach you if you don't know these things. And we can also take advantage of one of the superpowers I believe the product has, which is here in the tuning tab. And we know we have tuning recommendations available because of this red line right here. So you click on that, and what we see is we have some recommendations for the indexes on this, database. The order, table is currently responsible for over two and a half hours of waits, but if we fix it by improving the as it says over here, if we, add a a index to that column that it's recommending, then it'll save two hours and thirty eight minutes out of the two hours and thirty nine minutes of time being spent waiting for that index to answer queries. And we can drill down on specific, queries that are having problems of a certain kind, such as memory. Super powerful. Look at that. Boom. We just go right to the SQL statement. We can look at the SQL text here and see what the problem is, and it's probably that select star. If we go over to the, analysis here, it'll do, additional information retrieving that for us, but we also have the ability to ask for an AI Query Assist. And so with AI Query Assist, we submit a query to the LLM, and the LLM will provide us with an optimization. So that's here at the very bottom. Here's the original query that caused us to have all those memory issues, and here is a better query that will cause us to have fewer memory issues. And if we don't really already know the basics, here's an explanation for all the different suggestions that it makes as it does its assessment. So in just a couple clicks, we go from knowing that exactly what the problem is. It's a memory problem. We go to the day or, time period of the week in which we can see there's anomaly, and then we can look at the specific SQL statement that's causing the anomaly and ask it to rewrite that SQL statement for us so that we spent a few minutes doing what took us quite a while to do with the native tools inside of SQL Server. Superior solution by far. In SQL Century, we have a different approach than in DPA. What I'm looking at right now is one of my AG servers, the primary at the moment, and we can see that it's quite healthy. We've had a couple blips here over the past few days. And if we were to look, say, at the alerts, for example, we can see that there are a couple alerts, but even better would be for us to look at the performance of this particular SQL Server. And so key lookups, for example, we know are a problematic issue. So I drill down on those key lookups, and you could see that on the left hand side, this is all of my Windows information, and on the right hand side, this is all of my SQL Server information correlated over that same period of time. And so if we scroll down, we can see that memory weights are a considerable amount of the weights for this server. And looking even further, we can drill down and see from a resource perspective the buffers for the data cache for the plan cache here and then the IO activity as it relates to those two together. And so with SQL Server using SQL Century, we have a resource we have a resource driven approach. And so our typical response in this circumstance would be to look at top SQL for that period of time and see what that SQL statement was and then make repairs from there. We can also look at it from a dashboard point of view, and you can build your own dashboards as well. So in this case, this dashboard has a couple of the metrics that you just drag and drop in there, and we can see exactly what is happening from that standpoint on any dashboard that we wish to create. So it's a different approach. It's resource and telemetry for those resources first, and then we go deeper from there to the exact SQL statement that gives us a problem. And like DPA, we can also use the AI Query Assist to have SQL Century rewrite the problematic SQL statement and give you a response, in a means of correction for that problem. Alright. Let's summarize. First thing you wanna make sure you know is the fundamentals. It matters a lot. And if you don't know the fundamentals, that's like being responsible for a car but having no mechanic skills. You're gonna have some difficulties. Right? Also, configuration matters. So you need to make sure that you institute some of those best practices around what your memory settings are for min and max memory, optimized for ad hoc workloads, and large pages of memory. And if you can, I encourage you to use page compression if you're on SQL Server twenty sixteen service pack two or later? Also, there's the telemetry that I, told you about. Those are good to know. And then finally, always use that checklist. It's gonna save you a lot of trouble in the long run. I know it has for me. There's lots more information available online. We have a couple interactive demos you can take, advantage of. Lots of other webinars in this series around storage, around best practices for automation, and so forth. So I encourage you to look at all that other content. And with that, I'd like to say thank you for your time. Please take a look at those tools. I am convinced that they will be able to make your life better. And with that, if you have any questions, I will hang out for a while and answer questions. Thank you so much, everyone, and hope to see you on the next broadcast.