Create an SQL Performance Improvement Agent

[music] [music] [music] [music] [music] [music] >> Hello everyone and welcome to today's session. My name is Reese and I'll be your moderator today. We are just going to get started in a couple of minutes at the top of the hour. We're just going to wait for everyone to join before we start. However, if you would like to join in today, have >> [music] >> a look at the session resources. We've got a couple of prerequisites if you want to join in live as well as a few relevant session files as well. So, yeah, if you want to join in today, please do check out the session resources. They are pinned in [music] the chat and also the first link in the video description. If you can't see it cuz you were here a couple of hours ago, please refresh the [music] page and you should be able to see them. However, you should see it in the chat anyway. If you haven't done so already, please do also register for this session. That means if you need [music] to jump out at any point or you want to catch up with the recording, we will email it to you and yeah, that receive the full in >> [music] >> your inbox. So, yeah. Um yeah, if you need to for whatever reason, uh please do make sure you [music] register for this session. You can also head over to datacamp.com {slash} webinars, where you'll find this session as well as all of our future sessions, as well. Also, if you have any questions or notes or anything else, let us know in the chat. We're going to be dedicating the final 10 minutes of the session for your questions, so make sure you stick around for that. However, we may answer a few questions along the way if you are following along live and you need any help. So, yeah, [music] please do let us know in the chat. Also, if you enjoy this session at any point, please do drop a like on this video. [music] It really helps promote the channel to like-minded people like yourself. So, yeah, if you found this interesting, give [music] the video a like and yeah, it'll get promoted to another person like yourself. And we can spread the word. So, uh I will repeat these messages a couple of times for anyone that's just joined. Welcome to the session. My name is Reece and I'll be your moderator for today. We are just about to get started in [music] a couple of minutes. Before we do that, if you'd like to join in live, please do check out the session resources. They [music] are There's a link to them in the video description and it's also pinned in the chat. So, that will include the prerequisites for joining in today as well as a few session files, >> [music] >> um as well as the slides we're going to cover as well. So, yeah, if you want to join in live, please [music] do check out the session resources. And if you do just want to watch along and catch up in your own time, you're registered for the session. [music] You can scan the QR code that's on screen. You can find the link in the video description. I'll also post it in the chat again. Um and you can also head over to datacamp.com [music] {slash} webinars, where you'll find this session as well as all of our future sessions, >> [music] >> as well. If you have any questions, notes, or comments, let us know in the chat. We're going to be dedicating the final 10 minutes of the session to your questions. So, make sure you stick around for that [music] if you have a question. Um also, if you are following along, we can um answer a couple of questions along the way. So, yeah, please do let us know if you run [music] into any hiccups or anything else. But yeah, if you do follow along right now, you should be looking at the session resources >> [music] >> uh and getting set up. Um brilliant. I think that's about it from myself. So, now I hand you over to your host for today's session, Richie. Richie, please take it away. >> Hi there, data scamps and data champs. Uh welcome back to Aginic Data Science and Engineering Week. This is Richie. Uh very pleased to have you here. Uh let's see who we got in the chat. Uh all right, we've got uh Coding with Junior calling in from the land of doctors or possibly Dominican Republic. Uh we've got uh Ahmed uh calling from Pakistan, the Jawad from Afghanistan, Tech Cat from uh Philadelphia in the USA, Bilal from Nigeria, uh Alex uh from Brentford in England, uh and got Ikey from London uh in the UK. Uh someone with only initials from Taipei. Uh great to have you all here. Uh always nice to have a a global audience. So, uh it's been quite a week. Uh we spent the last 3 days talking about careers. We talked about AI careers, data engineering careers, data science and analytics careers, how agents are changing all these things. Uh today we're going to build an agent. Uh one of the hottest topics right now is things like cost saving, getting better value from data infrastructure. Uh one of the most important ways data teams can contribute to better value in this area is by optimizing the SQL queries. So, today we're going to be learning about database performance management. Uh we'll be focusing on uh using SQL Server Management Studio, but you know, uh uh a lot of the principles apply to whatever database you are using. Uh I got a fantastic guest for you today. Kevin Kline is an Academy Award and Tony Award-winning actor. Uh wait, sorry. Wrong Kevin Wrong Kevin Kline. Let's try that again. Kevin Kline is a senior staff technical marketing manager at SolarWinds. Welcome Kevin. Great to have you here. >> Thank you for having me. It's a pleasure to be here. >> Wonderful. Yeah, I'm looking forward to this session. So Kevin's got 30 years of experience working with SQL and databases. At SolarWinds he focuses on SQL performance monitoring and database tools. He's a 13 time Microsoft MVP and AWS data community hero. Wait, how do you get that award? >> It's a nomination process. So you're nominated by your writing, your speaking, and your activities in the community. >> Okay, I feel that's more awards than the other Kevin Kline has. So >> [laughter] >> you're doing very well there. Nice. So on top of this Kevin is also the author of the best selling SQL in a nutshell and the co-author of several SQL Server titles. Very conveniently placed there. Yeah, I love that. He's also a founder and former president of the professional association for SQL Server. All right, so wonderful. With that please take it away Kevin. >> All right, thank you. And everyone, thank you so much for joining us. I know you have a lot of constraints on your time. And so I really appreciate it that you're carving out some time to spend it with me today. Reese, if we could switch over to the slides. Yeah, okay. The main thing I want to get across to you here on this and our next slide basically is how to get in touch with me. So I'm at kevin.kline@solarwinds.com and on LinkedIn, Facebook, Twitter, Blue Sky, I'm at KE Kline. So all of those kinds of um ways of connecting um they're open and available to you and I love to answer questions. I love to be helpful. And so uh I can do to assist you, I'll try to I may not know the answer to your question off the top of my head, but I probably know someone who has. Um if you are interested in taking a look at the book SQL in a Nutshell, which is a best-seller, also I'll point out that you can get access to it for 1 month for free uh using this code here. So, take a look at that uh and for that matter, I believe you can look at other O'Reilly titles as well with this passcode. So, if you are keen to look at some of their other titles specific to a given database platform, say Postgres or Oracle or what have you, uh you can look at other titles as well with this little this little freebie for you. So, we're going to spend the vast majority of our time today in actual product, right? We're going to be looking at SQL statements in uh SQL Server Management Studio. That's not to constrain you, though. Uh the lessons that you are going to learn are essentially uh applicable to any of the major database platforms. If you are using, say, uh MySQL or Postgres or um something like that, these lessons apply to the they apply to all relational databases because all relational databases use execution plans. So, if I will just close that screen here, what I'm using right now is for SQL Server. There are other great query interfaces and tools that you can use, and almost every one of the uh database platforms ship with their own kind of utility to query the database. Uh if you want one that works across multiple database platforms, my suggestion uh and my personal preference is a tool called Dbeaver. So d b e a v e r and it's an open source tool connects to SQL Server, MySQL, Postgres, Oracle, SQLite, anything that any of those databases that use a relational back end and it enables you to do the kind of thing we're going to do here today. Okay? So the assumption for this session is that you are um you're familiar with and perhaps even up to intermediate level in your SQL skills. You uh know how to write a select statement. You probably have worked with inserts and updates and deletes. You have written a create table statement. Uh you've created indexes. So that's the level of skill that I'm assuming that you have. What do you do next to take that skill level up a level? That's where performance tuning comes in and so I know how to write a query but is it a good query? That's where we see a dividing line between many um many novices, I guess you could say, and people who become much more skilled is that ability to know how to get that query from working to working really well and efficiently. And the the secret ingredient to that is knowing about execution plans. As I mentioned, all the relational databases have execution plans behind the scenes that tell you what is happening with your SQL query. So the thing about SQL that's different than almost every other language out there, say Python or if you're using any of say VB .NET or Visual Studio ASP kinds of uh languages, C#, uh those are all what we call um uh those are procedural languages, right? You tell it the exact process that you want it to perform to retrieve a data set for you or to modify a data set for you. SQL, the structured query language, is different. It is a declarative language. Uh in some ways, uh LINQ, if you've worked with those, are declarative. So, you tell it what you want. But behind the scenes, kind of magically, it decides how to procedurally answer your question. And it's kind of a black box, right? You look at it and you're like, "What are you doing?" So, I'm going to teach you that step that tells what it's doing behind the scenes. And when you learn about how databases work, you come to realize that sometimes the database does a great job, but sometimes it does less than a great job. And it could be because of the way we have written our query. It could be because of the way that the database itself and the tables have been structured and whether they have been indexed or not. Uh indexes are kind of the fulcrum on uh which a lot of your performance capabilities depend on. So, they pivot on that, and if you don't have good indexes, you frequently will not get good performance out of your database unless it's a very very small database. So, again, if you're using any of those other tools like uh Dbeaver or maybe um uh some of the other ones that come with the databases themselves like Oracle ships with OEM, uh where you can run queries, you're going to have something similar to this. And this is SQL Server Management Studio. It is not the newest release. It is a little bit behind and that's intentional cuz the newest release has all kinds of AI stuff built into it and I want to teach you that manually because that's what's going to enable you to be powerful is to know how and why these things work without skipping straight to the end by using the AI to build these things for you. So, uh and by the way, these scripts are all available in your download package. So, if you go to the the resource page for this session, you'll see these scripts, you'll see the AI prompts that we're going to go through in a little while. And uh you'll see all the other prerequisites. So, you have all of this information for you to take along. Now, one other thing I'm just going to switch to real quickly is in our code along the uh um all of the red flag that we are going to be looking for in our execution plans, okay? These are the indicators of problems, right? If you see any of these red flags that we're talking about, then you know that you need to rewrite the query and tell that particular process that that happens behind the scenes declaratively that we change it so that it does not invoke that particular red flag. That's That's what query tuning is. We try one version of the SQL that gives us the right result set and maybe Well, we need to change it now because it gave us the right result set, but it didn't process it as well as it could. It was less efficient than possible. So, now we need to write it in a different way so that it avoids that problem. So, I encourage you to take this along with you and, you know, maybe print it out. This is particular to SQL Server. However, each of the major database platforms has its own set of red flags. Many of these are in common across the different database platforms, but um uh for example, not using a a particular index you know that should be used, but this is specific to SQL Server. All right. So, let's go back to the demo. So, uh I always use a test harness when I'm tuning SQL code or writing it for the very first time so that I can ensure that performance is really really good and that the performance of the queries that I'm testing are not interfered with by any other processes happening on the server. For example, um uh this first little bit of code that I have right here simply tracks the starting time for a large batch of queries. So, maybe I have a dozen different queries and I wanted to see what the total elapsed time is for all of those queries. So, I collect the start time and then I would include right here where this comment exists, I would change that out for the batch of SQL statements. And this would then, after the completion of that batch, tell me how long it took to ran all of those. But, that's not a really good a really good way to performance tune, just looking at the clock. Again, that could be because uh there's other processes going on. Perhaps you have a dev server that has lots of different developers on it at the same time. So, this is useful vaguely, but it's not something I use all the time unless I'm the only person on that uh whoops, unless I'm the only person on that instance. Some other things I do, if I am the only person on that instance, is I will uh when I'm testing, I will flush all of the dirty pages, which are pages that have changes on them that have not been uh written to disk. I'll force that to write all of those changes to disk before I start my troubleshooting by issuing the checkpoint command. All the It's an ANSI standard statement, and all the different database platforms enable you to do that. I will also clear the different caches that are used to support transactional activity. Uh this is what we call a cold cache testing method. In [snorts] the old days, uh in '80s and '90s, I Yes, I started in the middle '80s. Uh when SQL was brand new. And uh back in those days, you you didn't have commands where you could empty the database cache. You basically had to do what we called warm cache testing, which meant that uh because hard disks were so slow back in those days, uh if you started your query and none of the data for the query was in the cache, it might take 30, 40 seconds or more to load that data into the cache. So, in the old days, we had to load all the data and only test the results and measure the telemetry on the second run. That way, it would be consistent across all runs as we did our testing. Nowadays, what we can do is we can get a more realistic experience by doing the whole test with a cold cache, knowing that it has to come from IO subsystem, get loaded into the database memory, and then returned to us. So, that's a more realistic uh way to look at your SQL performance. And so, SQL Server has commands that enable us to clear those caches that are uh in use. Also, there's a couple um statements you can issue that will keep track of things like statistics on uh for the IO operations against different tables. And this is really useful to determine how much how many records are returned by different operation. I'm going to go ahead and turn that on here. Going to run that statement. And statistics time tells us about things like how much CPU time is spent on a given SQL query. So again, I'll execute that just to make sure that I have both of those statements. So not looking at execution plans yet. An example query here where I'm looking at a um what is actually a view. And we can tell because the database designer has put the V in front of the the table name. And so now we get the results right away. But since we have turned off or turned on IO and time, if we come down here to the messages, we'll see this is the amount of time spent 125 milliseconds parsing and compiling and it took almost a third of a second to complete that. And then we can also see on this query since it goes to a view, which is another query that's been materialized, we can see the different IOPS happening against the subordinate objects of that view. If it says logical read right here, that means the amount of data that was in 8K pages that was read from the data cache of the database. If it says physical reads, like it does right here, those are the number of 8K data pages that it didn't find in the data cache and had to go to the IO subsystem, the disks, and load that up into memory. So the higher this number of physical reads, um that is probably going to be a slower part of your ser- of your query, whereas logical reads are going to be almost instantaneous. So, if we have a high number of reads, we want them to be more on the logic side rather than the physical side, okay? Also, we can look at our execution plans statistically or graphically. I'm going to turn that off, by the way. So, um if I turn on my execution plans textually, which I'll do right there. Now, I'm going to run the same statement I just had a moment ago. I'm going to find all the individual customers that have an ID business entity ID between 6131 and 8000. We get here a textual representation of the execution plan. Now, nowadays, um I find that a a lot of the old-timers, people like me, we do still like those textual plans, but in general, they're much less common, uh at least for SQL Server. The open source databases don't have as much um uh high-end tooling built into them. So, this is what it will look like when you're on PostgreSQL, say, or you're on MySQL, unless you're using one of the better tools like DBeaver, uh which can show you a graphic execution plan. So, I just wanted to show you what the um what a textual execution plan looks like. The rest of this session, we are going to look at graphic execution plans, okay? So, when you're looking at a graphic execution plan, you are going to want to Oh, you have to enable that here, too. So, I'm going to actually include the actual execution plan here. Let me make sure I click that properly. Yeah. Okay. So, now these queries from here on out are going to produce a graphic execution plan. And I'll show you how to um how to read those in just a moment. >> [snorts] >> So, one of the things you'll see in the literature about working with queries is you'll see this abbreviation, sarg, sard. That means search argument, right? And a search argument is going to be pivotal for any of your queries. Basically, it's it's when it show a a parameter that you use in a where clause or in a join condition. And that will point the database if it's a searchable argument a search argument, that will point the database to try to use an index if it exists, okay? And so, when we say, "Hey, this particular table, sales order header, it has a sales per uh we're looking for anybody with a salesperson ID of 1 uh 283." So, when we execute that, notice we have a new tab down here on the bottom for the execution plan, okay? And I'll make this a little bit bigger so you can see it. So, what we have here is a description of what is happening behind the scenes when the database decides how to execute our query. And so, what it sees is that we have asked for a salesperson ID equal to 283, and there's an index there. And because there's an index there, we do not have to read the entire list of values from that column in that table. We can go directly to the one record that we need uh to answer this query. When we use an index to go directly to the to the record or set of records that we need, that's called an index seek. Okay? And that's what we want to see. We want to see seeks. Now, if you're on Postgres, by the way, it doesn't differentiate between seeks and scans. A scan is when the database has to read every record. So, what you have to do on databases like Postgres is you have to run it with the query that has the search argument and without it, when you run it without it, that will definitely get every record in that column in that table. And so, that tells you what the full scan is. Then you compare to a search argument related, um, and then you could say, "Oh, well, the table has 150,000 records in it. I can tell that this is this is actually seek happening behind the scenes cuz it only retrieved one record." But, in SQL Server, they make it obvious. Index seek, get right to the record we need. And that's the thing that's important to know. Relational databases are not row by row uh, processing engines. They are set-based. [snorts] And it wants to retrieve a set of records, even if the set is only one record. So, that's how it behaves. So, if we said where the value is equal to or greater than 283, we're going to get a lot more records back now, but it's still a set. And so, looking at this, we see we have, over here in the bottom right, uh, 1,146 columns. But again, it's actually able to use the exact same execution plan because it knows all I have to do is go to record uh, with a value of 283 or greater, and then I can scan through the rest of those very, very quickly and get a result set from it. Um, One of the things that's very tricky though is when we ask for not a value. So, when we say, "Hey, give me all of the the queer the sales person IDs that aren't equal to 283." That seems like a reasonably easy thing for a database to respond with. But and again, um we get our record count here. If we look at the execution plan though, look how elaborate it is. The reason why it's so much more elaborate is because relational databases don't know how to find not a value. They know how to find sets of values. And so, what it has to do behind the scenes is it has to look at every value and say, "Equal to 283?" Uh yep. Throw that one out. Not equal to 283? Okay. So, it's very very difficult to actually do a not my value kind of calculation. A lot of people are surprised by that. So, now that I have a fairly elaborate SQL statement here, the way we read it is not starting here. This is the end. What you do is you start at the lower right and work your way upward and left. So, this constant scan, this compute scalar, as well as this constant scan, and this compute scalar provide us with a concatenated um a concatenation operation. Also notice it tells us the the cost and the name of the operation that is happening at each of these different steps. So, what's the most expensive operation here in this execution plan? Well, you actually have to look through all of these values to find it. And it's the index seek. So, once again, it is having to join all of these other steps to the index seek. Okay? Now, and then we come to the final step of providing that result set. One of the things you can do in your coding to make your coding perform faster is to be aware of how the relational engine works, and then write your code to take advantage of that. So, if you have not values where it's not equal to 283, we could actually rewrite that query in a way where it is two sets that answer in the same way. So, notice we've got 3,617 records answered by this query. >> [snorts] >> But, what if we said instead of where it's not equal to 283, we said where salesperson ID is greater than 283 three, or it's less than 283. So, this will give us the same result set when we execute it. See, 3,617 rows. But, look at the execution plan. Super simple, right? Uh and that's because it now knows it's looking for two sets of data that is functionally equivalent to not equal to 283. And it gives us the results with a much simpler query. So, that's a little tip to use. If you're looking for not values, see if you can rewrite it in a way that it uh finds the sets that you need without saying not that value. So, here's another query, simple kind of uh correlated subquery. Okay? So, we execute this query, and we're saying where the values are in the subquery. And notice now we have two basic two basic actions. So, we have the outer query and the inner subordinate query. And each of those were answered by a seek. And again, we've got some really cool information that pops up. If you hover over that, it'll tell you what's going on. But again, when we look at something that says not, we're going to see that same sort of behavior happen in which we get a very elaborate execution plan because it has to find all the values that aren't equal to that and uh compare them to what it is equal to to come up with that final result. So, it's much more difficult to find not values. So, it has to scan. You see right there it says index scan. How about this um this one? This is very tricky and this is true across the relational databases. Notice we have a function call on the salesperson ID. So, we're saying if it's null, treat it as if it's a zero. And then look for those that are equal to 283. When we look at the execution plan, again, this is what tells you whether it performs well or not. And notice uh-oh, we've got that red flag that I mentioned. If we see scans, right there, we've got a scan happening. And the reason for that with all of these relational databases is that when we apply a function to a column that has an index on it, typically, we can't use the index. So, the better way to do that would be to push that over to the right-hand side. So, we don't call is null here. We maybe use a set of parameter and the the is always equal to zero if it's not um if it is null. Again, if we look and say where the value is null, so this is a alternative. We can say where it's null, then we get a very simple and easy execution plan. Okay? Now, another problem that happens a lot and again, I'm showing you examples of queries that invoke those red flags and how we can find those in our execution plan. So, one of the big problems that happens on relational databases is data type mismatch. So, an example would be if we have a uh national ID number in a column and let's say it's an integer. If we don't put quotes around it, the database thinks, "Okay, this is an integer." But if we do put quotes around it, it thinks it's a text. And text and um integers work fine, but they don't work well when you mismatch them. So, if you uh were uh to rewrite this in the first way, you would not have a problem. But if if we write it in the second way where now it thinks it's a text instead instead of um integer, we're going to have an issue and so I'll leave you here. So, executing those queries, we get the exact same result set, but when we look at the execution plans here, notice that the top one takes more processing power uh and we get an alert. And it says that it is a if you can see right here, convert implicit. So, what that is actually doing is it's applying a conversion function behind the scenes to that column and as you know, uh when we apply a function as we showed in the earlier example, it's going to not use the index. So, that's what we have. We got a clustered index scan. And if we look at the second version, which was the easier, it only took 50 45% of the total compute capacity of both of those queries offered together, we see that it uses a index seek to get the answer. Also notice though, it has a key lookup. Lookups are another one of those red flags. So, it's a situation where if you want to become really good at that performance tuning, you need to learn how to read these execution plans. Okay? And then they will tell you and as you can see here, in some cases we'll even get a specific warning that there is a problem and you need to fix that. Um sometimes we'll have situations where we know an index exists, but it turns out that the database doesn't choose to use it. Or in this case, the the database knows that we would perform much better if we added an index. So, that's called a missing index warning. And so, um it will tell you, "Hey, here is a situation where you can improve the performance of this select statement by adding an index." >> [snorts] >> So, here's a slightly more complex SQL statement. We've got a select statement looking at a some values. We're doing some aggregates. We're doing sums. And whenever you have aggregates, you have to order by the non-aggregated columns using a group by statement. And then we also have an order by statement, meaning we want to change the default sort order of the result set. So, when we execute this, it's going to take a moment. All right. So, we got uh 79,433 records. When we look at the execution plan, what is the most expensive step in this execution plan? Well, we see that we do have a um a query warning that there was a memory grant issue. And we start again with our SQL queries by going to the lower right and reading upward to the upper left, okay? So, we see we start with a clustered index scan. That means it's scanning every row in the table. It's a pretty expensive operation. We see that now there's also an index scan on our sales order header um sales uh I believe that's the uh the sales let's see. Sales order header customer ID. And then we also have another clustered index scan on our primary key for the um sales.customer table, right? Then we do a merge join. That merge join produces a result set. And we do a hash join against the clustered index scan here. And then we do a sort. So, I asked you what is the most expensive operation in this execution plan? Your answer is a sort. And that's one of the red flags on the slide I showed before we jumped into the demo. Why is Why is a sort so expensive? Well, sorts are invoked by things like this order ID. Um it's also they're also invoked by if you use the distinct keyword, and there's one or two other circumstances that might cause a sort. And what happens is anytime SQL Server needs to sort records, it actually will spill that uh uh intermediate result set to tempdb in SQL Server, and it'll do the sorting operation there. So, anytime you see sort, that means tons of IOPS. It's got to spill it to tempdb, sort everything out, rearrange it, pull it back, and give it back to you. So, it's expensive. Sometimes I've seen people because they had the subqueries in there, they had multiple order by statements throughout query with subqueries in it, and that is just a massive waste of performance of your resource your resources to process that query because really all you need is the final result set to be sorted, not any intermediate ones. So, make sure if you have are looking at an execution plan that if you see more than one sort in there, you immediately need to go and rewrite that query and take out all but the final order by statement. Okay? >> [clears throat] >> So, this is uh uh basically a poorly performing SQL statement because of that sort. It is poorly performing only though because we made that request of it. It's It is probably going to perform as well as it can under the circumstances of what we've given it. Now, uh another thing another red flag that we might look for is um something called a tipping point. Okay? So, when we have a elaborate SQL statement, we look at the execution plan here. SQL Server and all the relational databases look at something called cardinality, which is a very fancy word that simply means the number of records. Okay? And so, by uh it uses the index to figure out how many records your query is going to request to pull back. And if it is inaccurate or maybe the indexes haven't been refreshed in a long time, that's where we see a lot of red flags pop up because the what it expected to bring back to you was nothing like what the actual number of records that answer that query. So, we want to see if we have good cardinality estimates here. Uh you won't get those if you need an index to be created. But notice where we're saying where all the where the customer ID is greater than 10,000. And so, we have two scans that happen here to answer this. On the other hand, if we say where it's greater than 30,000, that's a much smaller result set. And so, it may choose to do a different process behind the scenes to answer that query. Which is indeed what it did. Notice it has seeks in both of the cases. And the reason for that is because when we said greater than 10,000, that was a whole bunch of records. We later came back and said where it's greater than 30,117, that's a very small number of records. Notice it says it's only 289 records. So, that's a tipping point where SQL Server says, "Oh, you want greater than 10,000? That tips over to a scan. We've got it it's just as good or even faster just to scan the whole table cuz you're going to ask for the majority of records. But when we ask for only a few, that tips us back over to seeks. And so, that's the more optimal way of answering your question." So, that's how execution plans show us how to perform um how to rewrite this query in a way that it would perform better. If we can see seeks, if we can see sorts, if we can see any of those red flags I mentioned, the missing index alerts, missing statistics alerts, and things like that. And in some cases, we'll have queries that really don't have any explicit red flags, but could benefit from a rewrite. So, let's execute that one. And so, here's our execution plan. If we look again starting at the lower right, working our way to the upper left. So, we see this result set from one of our subqueries here is processed. Here we have two other of those subquery values processing nested loop join, the second nested loop join to bring together all of the result sets, and then finally our last step to deliver the data to us. Doesn't seem like there's any particular red flags, but we're going to use this as our test case now as we look at AI. Okay. Now, if I were to um do this independently of using any AI, first thing I would do is probably rewrite this query as a um as a join rather than uh correlated subqueries. But, let's see what what our AI tells us. Now, in this case, I'm using Gemini. And part of the reason that I'm doing that is because I want to show that all of the LLMs will work pretty much the same way. Okay. Uh and here's the prompt I've written that you can reuse yourself whenever you want going forward in the future. So, uh a good prompt in include several common character characteristics. First of all, you tell it its persona, right? You're an expert database administrator and SQL performance tuner, right? And your purpose is to analyze So, what that does is that kind of puts it in the mind of, "Okay, now I'm going to be looking through all of my vast library of intelligence, and uh I'm going to use SQL-related content from that." Then I tell it, "Here's what I want you to accept. Here's your input formats. It could be the execution plan and the SQL statement itself." Uh I didn't go to this extent, but if you are using a commercial database platform, not Pete not Postgres, not MySQL, not SQLite, but if you're using Oracle, DB2, uh SAP Hana, SQL Server, they all have uh very, very much richer and deeper uh query optimizers, and for those relational platforms, I encourage you to also include the full schema of the tables or views involved in your query. Because there are uh aspects that uh a query performance that the LLM can utilize, uh knowing what the uh particularly knowing what the different constraints are, primary keys, foreign keys, and that sort of thing. But at a minimum, you want to include the original SQL text and the execution plan. So, uh if we go back over to um if we go back over to SQL Management Studio, if we right-click here, we can save the execution plan, okay? I've already done that, just to make it a little bit easier, and you can save it as XML, you can save it as JSON, uh you can save it in a lot of different ways. So, back to this. Now I tell it, "What I want you to do is identify that really expensive, whatever's most expensive." And we only had a moderately uh sized SQL query and it was it took a little looking through to find that sort being the most expensive thing. So imagine if you had a really big query that ran page, you know, page after page. It can become very difficult to find the expensive operations in there. So we want our LLM to identify those for us and we want it to compare the actual versus the estimated number of rows that would be returned by those queries. If those numbers are more than say 10% different, we know that the indexes that exist on those tables are stale. They need to be refreshed and that's why we have a big difference between what it estimates and what actually happens, okay? And then finally, I mentioned just a few of those red flags. If we have index scans on large tables, if we have key lookups or row ID lookups, if we have sort operations, particularly if we have more than one, if we have spools and spills, those are the behind-the-scenes operations that create temporary work files in temp DB and those can often be pretty expensive and and time-consuming. Implicit conversion is a massive performance killer. So we got to find those. Function calls on um on the where conditions. Remember I showed you where we had the is null? That causes that salesperson ID to not use the index. Um and if we have any of those missing index uh uh or uh missing statistics alerts. So I tell it to look for some other things, provide uh response in this structure. So I tell it what I want. Tell me about the red flags that you've detected and tell me about any index creation that might benefit this query. So I ran that agent. It said, "Okay, I'm ready. Hit me with what you got." So I gave it the SQL statement and I gave it the um execution plan, and here's what it came back. It said, "All right, well, you know, that one didn't have any uh, that very last one we looked at ran okay. It did not have an explicit red flag, but it notices several things. Some of the execution plans only return a single row. This doubles the CPU and logical IO consumption." It says that there are two distinct scalar subqueries. Uh, so that's not good. Uh, some of the red flag we found here, um, it perform must evaluate a value that's not part of the index. So, maybe we could add that as part of the index. So, what it comes back with is a rewrite. So, the first thing it says is, "Hey, if you don't have this, you would benefit from having this adding this index, and here is the code, the SQL statement you need to rewrite the index. Yay. And if you even if you don't add that, here's a better way to run that exact same SQL statement, right? So, let's have that alternative. Uh, if we switch back over to um, our example set here, let's go up to the um, and I wasn't sure if we would be able to um, actually process in the amount of time that we have, cuz sometimes AIs can take a few minutes to go away for a while, and um, uh, they don't respond as quickly as we'd like while we're doing a demo. But, let's give it a try. So, I'm going to look at these three, the ones that had the implicit conversion. Uh, and let's look at those execution plans again. And we can see that this first one is more expensive. It has that implicit conversion warning. The other two uh, do not. So, let's take this one and put it into our um into our LLM. And let's also save the uh the demo here. I'm sorry, the execution plan here. And I'll just call it one. So, we have that. And evaluate this query and the attached execution plan. And I'll add this, and we'll add that file. And there's the execution plan. And hopefully it won't take too long to run. While we're letting that run, um Reese, do we have any questions at this point? >> Uh yeah, we've got a few questions from the audience. Uh so, actually maybe we'll go through uh Uh there's this one from Lucas saying like uh what happens if you've got a really big data set and you can't even get it to run, uh so you can't generate a an execution plan? Is there a >> [laughter] >> Is there a way around that beyond just buy more memory or run it on a bigger machine? >> Yeah, uh there is. Uh and again, uh the it's specific details depend a little bit on which ver um which database platform you're using. On SQL Server, for example, uh you can and I'll flip over to the uh Management Studio. One of the options is it's is over here. It's called an estimated execution plan. So, what this does is this says, "Hey database, I'm not running the query, but I'm thinking about running this query. What execution plan do you think you would use?" And so, um it will come back, it'll show you one of the execution plans, you know, in the format that we have here. And if I was looking at it, in your case, I would look for any situation in which I had a an an unbounded uh clustered index scan or table scan, because that means it's reading all the data from a given table when we might only need a subset of that. So, if we added a where clause that didn't exist before, that would then really uh greatly reduce the amount of records returned. And sometimes when I'm uh writing and debugging a query for the very first time, uh again, I might actually tell it, knowing that it's going to when it's in production, we want it to get back, you know, 2 million records, but right now, just for testing, only give me the values between 100 and 200, so that I get a real small data set, but it is the same uh basic SQL query. That way I can get it working, and then I can loosen those parameters on it, so that it gets the bigger and bigger data set, the full data set eventually. >> Okay, I I love that you can kind of try before you buy on the execution plan and uh >> Exactly. >> All right. Uh have we got time for one more question before you resume? >> Yes. Mhm. >> Uh cuz there's a a question from Radhika just saying, "Does this also work in BigQuery?" Maybe you can just uh expand on this, cuz we've been focused on SQL Server today, but I mean, you know, there's BigQuery, there's Redshift, there's like Snowflake, Databricks, there's all these different tools. How generalizable are these principles we've been talking about today? >> Um so, uh generally speaking, if it is a SaaS um platform like Snowflake, like BigQuery. uh Uh, yes, you can get an execution plan. The amount of instrumentation that's available will be lower. I should say, uh, it's not as much telemetry. So, these from Oracle and SQL Server give you tons of information, whereas, um, Snowflake has its own proprietary engine, but, uh, uh, or BigQuery has its own engine as well. So, they have a little bit less of the telemetry in there. But, yes, you can get these in almost any relational data data platform. >> Uh, okay. Yeah, so, um, in theory, it's sort of auto-magically handled in a lot of these SaaS platforms cuz they figure out their own kind of, uh, infrastructure. >> Right. So, for example, in Postgres, you'll need to use something called PG Analyze, which is a a bolt-on, um, to get lots of really good information. Uh, so, they all have them. It may be it requires a little bit of extra work for you to load a plug-in or something like that. >> Okay, nice. All right. Uh, I'll let you continue, then we'll go through more questions towards the end. >> Sure. Sure. So, we we already knew with this last example that this would this way of addressing the data would cause an implicit conversion. And so, we've asked it to look for any of those red flags in our agent. So, sure enough, it comes back and says, "Hey, I found an implicit conversion right away." Rewrite that in a way so that and we saw in the examples if we just put a quote marks around it, then it would, um, it would behave differently and give us a better execution plan. And we also have that key look up. So, here is the rewrite or here's the index it suggests for us, which is actually just a change an existing index and add, um, the login ID with it. And then the the query it says, "Hey, just rewrite it by adding the N, which means Unicode, and put it in quotes, and it will not have that implicit conversion anymore." So, uh what other kinds of questions do we have? >> Uh all right. So, uh there are a few technical questions here. Um So, we've got one from Lucy saying can we import DB schema and combine with Uh I can't remember the context of that. That was a few minutes ago. Um so, do you just want to talk us through like what what DB schema is and and how it might be useful in this case? >> I I believe what they're asking is the schema of the database. And if you're willing to take them A lot of us are in a hurry >> [snorts] >> and skip that step. If you skip that step, you'll still get good in input from the LLM about how to improve your query, but if you include all of the database create table, create index statements, so that the LLM also knows what the structure of the database is, you will get even better uh recommendations from the LLM. So, yes, if you can do that, do it because it will give you better uh results. >> So, this seems to be uh a common thing when you've been using LLMs, that the more context you can provide about the problem, the better results you get in general. So, this is in this case the the schema is incredibly valuable information there. >> Absolutely. >> Nice. >> And And I would even go further uh by saying uh and I didn't specify in this particular uh prompt, for example, what version of SQL Server it was, um whether it's SQL 2022 or SQL 2025. I didn't uh describe whether it was standard edition or enterprise edition. If you give it all of that information as well, that can be very useful because uh for example, older versions of the database you're working on probably didn't have some of the improvements in the optimizer that they added later on. So, it might make a recommendation that's great for the latest release of PGSQL, but it's not good for PG13. Uh something like that. So, it again, you can further improve those prompts by saying, "I'm running on this specific version, you know, this release date, and it's enterprise edition." So, then it can give you even better tuned recommendations. >> Okay, nice. Uh yeah. Uh all that extra information uh yeah, I'm sure it's going to give you clearer answers or a higher chance of a good answer. All right. We've got this question from Heather. So, Heather says, "Uh do you have to balance between creating more indices to improve performance versus the additional load of virtual table?" So, yeah, I guess um there's no magic bullet that's going to make everything every query you run faster. How do you think about the tradeoffs between different approaches? >> Yeah, you know, Heather, that's a very insightful question. And yes, it is extremely important, especially on high-end systems. If, you know, if it's a smaller system run maybe a couple dozen users, couple hundred users, you might not need to go to this extent. But on very high-end systems, it's extremely common to try to separate your read-heavy workload from your insert, update, and delete-heavy workload because you want to add indexes to speed up your select statements. That makes select statements go faster. But [snorts] there's a cost. For every select I'm sorry, for every index you create on say, there's a customer ID column and a product ID column and a salesperson ID, every one of those, whenever you enter a new value, um or update a value, whenever you do a write, it has to also update not just the table, but all of the indexes. So, if you've got two dozen indexes on a fairly wide database with a lot of columns, that means it might have to do two dozen IOPS just to change one value with an update statement. It's going to change all the different indexes everywhere. So, you'll see that yes, people will do a lot of analysis to say, "Okay, we can add five indexes without our write performance declining. So, what are the five most important ones we need?" And because they have a mixed workload of lots of writes and lots of reads for their reporting system. But you'll see as these IT systems get bigger and bigger, they'll try to segregate that reporting system into its own maybe they'll have a secondary server that's a read-only server that gets refreshes. And so, we'll do all of our reporting so that we can have those two dozen indexes, but on the one that has all the data entry in the OLTP activity, we'll just keep it to the minimal five indexes that make it run pretty well 90% of the time. Good question. Very insightful. >> Okay, yes. You really think about like where we actually using most of our computer here and that's when you start thinking about like, "Okay, these these are the tables that should be indexed." All right. So, I guess related to this, I mean, there's this very famous old quote from Donald Knuth about premature optimization is the root of all evil. You don't want to be optimizing every single query that you do because it's it's not going to help and it's going to take too long. So, tell me through when do you want to use the techniques we've been talking about today? >> So, typically uh and here's your red flags once again. And as we go into the final stretch here, typically you I use them regularly, right? So, I always look at execution plans when I'm writing SQL statements. And I've been writing SQL statements since 1986. So, um but my general recommendation for for those of you who are new or maybe haven't done this so much, I would look at execution plans all the time, every time. But I would take it to the LLM when I see lots of issues in the execution plan. Uh and that will help you identify exactly what you need to change uh and make it much faster and easier. For me, I learned those um you know, I learned that over decades of working with the uh with the products. And it it was hard-earned and hard-won experience. You can get on the fast track by having this act as a mentor for you. And uh you know, kind of explain because it explains at each step, "Hey, the reason we suggest this change is because it will enable us to use a different index that performs better." So, yeah, I would use execution plans every time and I would use an LLM unless I have learned it. And at some point you will if you do this a lot. Uh I would use the LLM to assist me on on the big ones, on the ones that are kind of tough to troubleshoot. >> Okay, nice. Uh so, uh there are a couple more questions I'd love to get to. We're We're at 2 minutes to time. Are there more things you want to show before we wrap up? >> No, I just wanted to mention that uh you know, I represent a company that makes performance monitoring tools for databases. So, if you would like a system that warns you when a SQL statement is performing terribly, our database observability products at SolarWinds will do that. And we also have a free product that I just want to point out called uh Plan Explorer. It's just for SQL Server, but what it does is it takes all of the red flags that I showed you in the slide and it makes them pop. So, that when you look at that graphic execution plan, it actually shows red when it has one of those really bad things like a sort that shouldn't be there or um a very large scan. So if you want to get even better, download Plan Explorer for use on SQL Server and it will make your execution plans very easy to read. >> All right, very nice. Uh so before everyone dashes off, I don't ask formal questions every agent cuz there's a few comments in the chat, but uh for everyone who wants to come back next week, we've got a session on the EUAA Act because there's a big deadline coming up August 2nd. So for anyone who does business in Europe, very worth attending. Also a session on DataCamp's new MCP server technology for if you want to manage DataCamp via via your favorite LLM, then that's worth showing up for. And then first week week of August we've got a whole week around Agent E-commerce. So actually question for you around making this automatable, having like working towards having an agent. I guess the next step is to take those prompts using about like these specific problems and package them either into well we're using Gemini or Gemini gem or cloud skill. >> Yes. >> Um how do you go about repackaging those things and and other things like that are already available? >> Um yeah, so I would either create a gem um but what I would really for me personally what I would do is probably create a an agent that can loop. So I'd say, here's a file containing 20 SQL statements. Loop through that and perform the same things that I've showed you in the single prompt as an agent. Now I want you to do that autonomously. I'm autonomously. I'm going to go to lunch and when I get back, you'll have all of these great suggestions for me to have on how to fix up many many different SQL statements in my application. So that would be the next step I would take at this point. >> Okay, yeah. So, you basically in this case you making use of co-worker. I I can't remember what the the Gemini equivalent's called. So, yeah. You're saying, "Okay, go through all my SQL statements, find all the problems, making use of like yeah." >> [laughter] >> A more refined version of those problems. Mhm. And then you can do it really. >> If if you were already pretty good with AI, perhaps you're using Claude code. I'm a [snorts] a very strong believer in human in the loop with AI. Don't let AI change any of these things without you manually saying, "Yes, change that for me." We hear stories every day in the news about how somebody let the AI go forward and make those changes itself and hijinks happen. Personally, I hear Benny Hill raggedy What is it? Yakety Sax. It's like, "Okay, hijinks are going on here. Some mischief is happening. So, whatever you Maybe a couple we can really trust our AI now. I encourage not to trust them without you reviewing their work first before you implement. >> Yeah, certainly once you have production databases like uh if it's dropping tables or even just like exposing user data in some weird way they shouldn't have or you're emailing like the wrong people about the wrong thing cuz a join went wonky. Yeah, there's potential for a lot of disaster here. So, yeah. Quality control is definitely a almost issue here. All right. Nice. Okay, with that we are well past time. So, thank you once again, Kevin. That was very informative stuff. And yeah, hopefully everyone SQL runs a little bit faster from now on. Thank you to everyone in the audience who asked a question. Thank you to everyone who showed up today. Please do come back next week and for future sessions.