RX 24 - A SQL Server Love Story - Jeff McClure

Transcribed Content

Alright. Welcome everybody. Our next session is a SQL Server love story with Jeff McClure from Lake Point. And there's gonna be some tips and tricks thrown in for good measure. Let's welcome Jeff. Thank you. Thank you. Can you hear me? Is this is this thing on? Okay. Welcome everybody. Appreciate you showing up here. This is my first time ever to be mic'd up and standing in front of a group of people, especially at yeah. Thank you, Nick. Nick. So, yeah, when Nick asked asked me to talk about SQL Server at the Rock Conference, I told him I had twelve hours of material. He he said, great, you got forty minutes. So here we go, right. My name is Jeff McClure. I'm from Lake Point Church in Rockwall, Texas. That's just outside of Dallas. I've got a wife of thirty one years. I've got three mostly grown boys. Mostly grown knuckleheads. Two of them, one of them is married, one them is almost married and the other still doesn't know what he wants to do. The QR code on the screen takes you to a very thrown together GitHub repo where I've placed the slide deck. It's actually changed a little bit since it went up there. Along with some more dangerous scripts including the demo scripts that I'll hopefully be sharing with you in about twenty minutes. Hopefully those are interesting to you. They are just, these are my personal just thoughts and scripts and whatnot. Use them at your own risk. Just know that when you get into SQL Server, you can wreck your world real fast. John talks about staying inside the castle walls. I've not heard that a single time since I've been here this year but just remember when you wander outside the castle walls, when you open up SSMS and SQL Server, you can wreck a lot of things really, really fast. You can also do some amazing high performing queries that will rock your world too. So thanks for everybody that's here to support me in this. Got several of my teammates here. Appreciate you guys. Watched a movie last night, Great movie about databases. Yep. Can't wait for the sequel. You all are here for the the development track so I'm going to assume that you are familiar with some of the developer tools. At least the basic concept of what SQL Server is, maybe what it isn't. And as a developer there are many ways to accomplish almost any task. All right, there's a thousand ways to skin the cat. The demos that I do here, hopefully I'm going to do a live demo. Just pray that my hotspot and my VPN stay up for that. Okay? The demos will be done exclusively in SSMS, that's SQL Server Management Studio. All right. Our agenda. This is what I hope to cover in the next forty minutes. I trust you want some of the nitty gritty so we're going to get into some of that. In fact, I'm going to blow through a lot of these slides. I had 53 slides originally and then at about an hour I figured out I got through about 20 of them. So I just moved the end slide up and there's, so in the slide deck that I've uploaded, all that stuff is there. I'm not going cover a bunch of tips and tricks that you typically hear in sessions like this because you can Google those, they're in the slide deck. We're just not going to cover them today. All right. By the way, this is going to be more about SQL Server and less about Rock. Okay? This is SQL Server session more than it is a Rock session, much more. All right, so here we go. In the year February, right around the time of the .com bubble burst, anyone remember that? Maybe. I told my wife I was going to interview at a.com but not just any old online retailer. Nope. I was going to interview with match.com, the OG of online love for the masses. And turns out I got the job. This is what it looked like in the year February by the way. It's go to the Wayback Machine you can find some of these old graphics. It's pretty funny. So I started there as an access report writer, a Microsoft Access Report Writer. And I quickly transitioned onto our database operations team and our development team which was really one and the same at the time. And I started working with SQL Server '2 thousand, SQL Server '2 thousand and five and then all the way up through SQL Server 2017. Anyone have one of these moments? But God had other plans. Match was really a blessing for me but it had run its course and I was ready for a new love story, one that focused on his kingdom. I had no idea how different that next chapter would be. I started at Lake Point Church in January of twenty twenty two leading the rock team, best team ever by the way, but knew nothing about Rock. Nothing about Rock. As a SQL Server guy, I thought, oh well, Rock's built on SQL Server so anything, you know, it will be easy. There was a real challenge though. I was a one trick pony and SQL Server was my only trick, okay? My twenty two years at Match exposed me to a lot, a lot in SQL Server but really only a small slice of it. It's a very wide product primarily around the development process and performance. Okay? Our application was built solely on stored procedures so all of the database access was done through stored procedures. Stored procedures are generally easy to tune. They're easy to index. They're easy to make perform well. Entity framework is not. Okay. So we're dealing with a different animal in Rock. Ready to dive in? Here we go. Quick look at the LakePoint environment. We have a production environment. We have got one burstable B20 VM. We've got one Azure SQL instance running somewhere between a P4 and a P11. We have three development environments. They're running on standard much, much less hardware and much, much less SQL Server. We have runbooks in place that automatically scale our production instances up for our services for our high demand times and then scale them back down when that's over. And then our ROC, our development environments go to sleep every night. So we just turn them off at night basically. So they're only up during the week. Saves a little bit of bread that way. Okay. SQL Server generalities. Here we go. So SQL Server is an RDBMS. That stands for relational database system. Simply put, the data in each table is often related to other tables through relationships that we call foreign keys. The data is typically normalized. Normalization simply means that we only write data in one place and with similar data, right, which improves accuracy, reduces maintenance and whatnot because if you have to write it in multiple places, you've got to keep up with it in multiple place. We'll talk about that a little bit. It's generally configured as case insensitive and I bring this up because your C, a lot of your managed code is case sensitive. SQL Server is not, at least out of the box. You can turn on case sensitivity. I wouldn't recommend it but there are there's a case for that. We've got a really cool demo where I show you how to do case sensitive searches. You can do that in your queries. SQL Server is amazingly fast and scalable. Highly available, there's a lot of options available there. It's also very expensive. Very expensive. All right. So Azure SQL. We moved to Azure SQL in December of last year. We were on prem hosted. We had a really nice set of Xeon Gold servers with NVMe, fast storage, decent amount of RAM. We were running on SQL Server Standard Edition so we were limited a little bit by that. But here's some of the benefits. This is part of why we went to Azure SQL. The licensing is included and you're basically running Enterprise Edition. Okay? And you're always running the latest edition of SQL Server. So you're not having to worry about am I on 2014 which is about to go out warranty, right? I'm running on the latest version. It is quickly and easily scalable. You pay for what you need or at least you pay for the capacity that you need whether you use it or not. It is very secure. You can bypass that if you want to but I wouldn't recommend it. The backup and restore service is outstanding. It's excellent. It's easy to manage and it's very easy to use. There's a lot of integrated performance tools and if you're on a premium tier, you have access to a read only replica. Who's using Azure by the way in here? Okay. So there's still some on prem folks. All right. That read only replica, you can offload some reporting and some things like that. It is, you have to be on the premium tier to get it. It's basically a separate instance that's reading the log from your main instance, from your read write instance. I do have a demo on that, at least showing you how to connect to that if you're interested or if we have time. Moving on. So not all of your on prem features are available in Azure SQL. This one was a punch in the face to me. The ones, some of the features that aren't available in Azure SQL, the ones in orange are ones that I miss most. So the SQL Server Agent's gone. You can't schedule jobs in SQL anymore but that's okay because Rock has a job scheduling engine and it's really good. Extended events, it does exist with some limitations. Extended events is probably the tool that I use most from a performance tuning perspective or at least it's one of my go to tools for performance tuning and for diagnosis of slow running queries. Cross database queries, I'm not going to talk about that but on Azure SQL, really you're only running one database. All right. Let's talk about tables a little bit. So a SQL Server goes into a bar and walks up to a couple of tables and he asks, can I join you? Okay. Sorry that's cheesy. I'm full of dad jokes. I'm a dad. Alright tables and data storage. Right. So your tables are basically like workbooks in a spreadsheet. Courtney talked about this a little bit earlier. You're looking at rows and columns in your tables. You've got various keys. You've got primary keys, unique keys, foreign keys, all kinds of things. You're going to hear about those. They're really just special pointers or special identifiers for your data. Rock is built on Entity Framework. As we've already talked about a little bit, most of those records are basically Rock objects. If you think about them as Rock objects and the columns in that table are the properties for those objects. That's almost diametrically opposite to what Courtney said earlier. She said rows are not objects. You can't have objects in You can't have objects in columns. That's true. Okay. Data is stored in eight ks data pages on disk and in memory and I highlight that because there's several places where it's important to know that from a performance tuning perspective because you're going get numbers back out of the system and that number is representing data pages often unless it's labeled with KB or MB or something like that. Moving on. Primary keys. Let's talk about structures, right? These are your keys and some indexes and some other things. Primary key is basically a unique value that serves as the internal row ID for each row in each table. If you don't explicitly define one, SQL Server will do it for you and it won't be called a PK, it's called a row ID and it's kind of buried under the surface. All ID columns in ROC use the identity property. This is an auto incrementing value. So every time you insert a new row, it automatically sets the next ID in that sequence. Foreign keys are going to be what help you enforce your referential integrity. Referential integrity is those relationships between tables. Think about the group type ID. If familiar with some of the rock tables, group is really common. We use it all the time. And group type, okay? So the relationship there. They both have an ID column. Each table has an ID column. But the group type ID exists in the group table as a foreign key. There's a relationship, there's an object that enforces that in SQL Server. It's a foreign key. When you insert a record into a Rock table, SQL Server is validating that that foreign key value actually exists in the parent table. Okay? So if you're writing a single row into a table that has 10 foreign keys, it's doing 10 select statements behind the scenes to go check that those values are actually legitimate. So there's a performance cost to having foreign keys but there's a performance cost to not. Indexes. Indexes are extremely important. Rock has a ton of them. Actually I think there's over a thousand. I'm thinking of foreign keys. There's over a thousand foreign keys actually in Rock, I've counted them. So indexes are really just mini tables that associated with your primary table that you can't see but the query engine can, okay? But it is a physical object that's stored separate from your table. And what it does, it allows the data to be sorted separately from how it's sorted in the main table. A heap, if you hear this terminology, that's simply a table without a clustered index. We call them heaps. Don't Generally you want to have a primary key on all of your tables. Normalization and denormalization. We're gonna just touch on this briefly. The saying is normalize till it hurts. Denormalize until it works. So the history is generally the more normalized you are, the more expensive your queries are. That's not always true. Certain types of queries actually perform better with normalize. You just end up with a lot more joins. It's a little harder to read. When you denormalize things, duplicating data from other tables into, you know, into maybe a single table. And there's good reasons to do some denormalizations. In Rock we've seen some in recent releases we've seen some denormalization like the age column. Right? There's three age related columns in the person table. Well age, you can calculate age from birth date, right? If you know the birth date, you can calculate how old they are. So why is there an age column? Well because the calculation of that birth date is expensive and if you have to do it for every row that your query returns, there's an extra cost there when you can just pre calculate it and store it. There's a couple other types and like the persistent values and attribute value, that's one of the most obvious examples. All the analytics tables are denormalized data as well. All right. Quick tip on table sizes and data size. It's not really The most important thing to know about your table from a performance perspective isn't so much how many records are in it but how many pages because that's ultimately how much data is being stored on disk and how much data or how much space is being taken in memory. So that's a quick I've got a demo on that as well. All right. Let's talk quickly about indexes. So I already talked about an index being just a mini table that the query engine can see but you can't unless you know where to look for it that is. The sorting is what makes them special. An index in SQL Server is generally the types of indexes that we use, let be clear, is really btree structure, a balanced tree structure. I'm not going go into the details of that. You can Google it. It is made up. It has keys just like your base table does. It's got keys. And that's the initial columns. And in this example, the keys I've got highlighted in yellow here. So we're seeing that this would be the script to create that and there's a name, create non clustered index, ix percent age and I'm giving it a whodunit, that's jmack, that's me, and a date. SQL Server does not record the date of when indexes are added to table. So if you want to know when they were created, you got to put it in the name. Okay. So I'm putting that on the person table and those are the columns that I'm included, including. You can tell it for each individual column which sort order you want it to be in. So let's say you know you're creating an index that is going to show people, mostly you're dealing with your senior saints in your congregation. You might want to sort that index so that age is descending so that they generally show up at the top. That helps you with some index scans. Okay. We're going to move a little quicker here. We've got get to the demo. All right. Different types of indexes. The ones you're going to find most in ROCK are clustered. This is, you can only have one per table. Non clustered indexes, you can have pretty much infinite number per table. Unique, that just simply a constraint meaning that the data in that index can only exist once in the table. And then covering which is not actually a type of index but it is a type of index that's added to solve a specific query problem typically. And that's usually going to be a performance issue. All right. Index operations. We've got seeks, scans, and key lookups. Hopefully we'll see some of that in in the demo. I will show you a little bit of that seek and scan. But basically it's just data access methods. Can you get to the if you're seeking, you're using that btree structure to navigate to the data that you're looking for. If you're scanning, you're basically saying I'm reading all the data in. I've got to look at all of it in order to figure out where I want to go. All right. Simple table index. So just to visualize what your indexes look like compared to your base table. What you see here is the top left there, you've got a fictional employee table. I've thrown some people in there. And then I've got three indexes and then just a legend. So I've got a separate index here for birthdate and you can see that birthdate is the key of the IX employee birthdate index and the data is sorted in birth date order. And then the blue column is a pointer that points back to the primary key or the row ID in the base table. Excuse me. So the green is your leaf level data. That's the actual data that's included in in your clustered index which is your table. Your table is your clustered index. You've got all of the data exists in what we call the leaf node of the index. You only see that in your non clustered indexes. Typically they only have a pointer going back to the base table but in some cases you can add by including, you can add leaf level data and that helps you cover a query with an index without having to go back to your base table to get it. And you can see that in the IX employee title, whatever that one is over there. That guy right there. Alright. I can tell I'm running out of time. Index compression, Google it. I'm sorry. So why don't we just index every column? Well, there's a cost to adding indexes. There's a cost to not having them. There's a cost. Everything, there's a trade off. Right? If you index every column, well every insert you do, not only are you writing to your base table, you're writing to every single index. You have to add that row to every index. Okay? So there's a cost to that. It slows down your writes. The good thing is Rock is read heavy. It's probably 80 to 85% reads and only about 10 to 15% writes. Write operations are very slow because you're physically writing to disk. That's always a slow operation. When you're reading, you want to make that as fast as possible so we add a few more indexes to make that read performance better at the cost of writes. All right. The best part about indexes is that for most ROC workloads, because it's heavy read, most of that writes, by the way, happen in overnight processing. But the best part is that they can have the biggest potential for solving a query performance problem if you know where to put them. They can also be added without affecting or touching core code, Staying in the castle walls or not disrupting the castle walls. All right, query performance. Just a quick note here about the query optimizer. This is the engine within SQL Server that takes your input, your query and tries to figure out what to do with it. It usually does a really good job. However, sometimes it can be flat out wrong or sometimes if the query is too complicated, it will only take a certain amount of time and it'll just give up and it'll give you its best guess. As far as it could process, it's like I'm out of time. I gotta just go with this. And it's not always the best option. So anybody had this? Report worked yesterday but today it times out. Yeah, Nick. Nick's the only one I think. I'm just kidding. You're not crazy. This does happen, right? This happens. And it's probably one of the most frustrating things with SQL Server because it's not predictable. It's so frustrating to developers because the performance can change over time. There's many reasons for that and I can't get into all that. I just don't have time. Okay. Let's get into the demo and I think we've only got about ten minutes left. So we fifteen oh, I got fifteen ish? Okay. So that's good because we're gonna need it. Alright. I'm going to switch out of this mode. If you'll give me just a second to change over to it's a live demo. Pray this baby works. Let's go. And I'm gonna duplicate my screen. We got it? Yeah. Okay. And drop to here's the top of the demo screen, of the demo scripts. This is one of the scripts I've included in the repo. So you have full access to all of this and most of it is safe. There are a few things that could get dicey down below. We probably won't even get to those. So if you want to see who's active on your server right now, there's a command. Exec is simply saying execute. The store procedure is SP underscore WHO two and the active is a keyword or parameter that we're passing in there that simply tells it only show me the people that are actually active on the processor. Okay? So this is what the output would look like from that. Sorry, that's a lot of text. And it's basically just showing you who's on there and what you should see is that I'm in there and I'm in the Rockdev database and I'm doing a select into operation. Okay? So if there's blocking, if there's something bad going on or if there's some long running queries, a lot of times you'll come in here and you'll see it. And over here on the left you see that this is called the SPID. This is the process ID basically. And sometimes you might see the same SPID repeated 10 or 15 times and that's going to be a parallelism query where it's running on, it might be taking up all of your processing that your server has. Okay. Let's jump over here. Have some other options for this. This is a third party. It's called SP Who is Active. You run this guy and it actually, it tells you not only who's active but it gets rid of all that system junk that you really usually don't care about and it just tells you who are the users that are active or some other processes that might be active. In this case I've got two extended event targets that are running. Those will always show up. They're really, they're very harmless in this case. Okay. We're going to skip the read only replica because I want to get into some other things. This is a very powerful tool called set statistics IO on and you can set it to off very easily. I'm going go ahead and turn that on and what that's going to do is that every time I run a query, it's going to show me which tables it hit and how many reads in pages that that query did or that it actually used. So a quick demo on we're going to look at query plans and some of the things to look for. In this particular demo we're going to be looking we've a simple query here looking for Bubba's phone number, Bubba's cell phone number in fact. Okay? Bubba's a good friend of mine. We're from Texas. So a query plan is we're going look at the graphical query plan. It reads from bottom to top, right to left. Okay? And there's a couple of ways to get to it. I'm sorry it's going to be really small but at the top of the the window here, we've got two options. We can display the estimated execution plan for a query and then we can also say give me the actual execution plan that happens after you've executed a query. So what I'm going to do, the good thing about this editor is I can select and run something individually. So I'm going to select that simple query and I can parse it and make sure it works. It worked. Good. I'm going to tell SQL, tell me what the estimated execution plan is. All right. So as we look at this plan, pretty simple. There's a couple of things that stand out right away. We're not going spend a whole lot of time here but it's basically thinking, hey, we've got an index scan on the person table. And you can see it's telling you right there. Index scan, non clustered, on the person table. It's also saying we've got an index seek on the phone number table and it's telling you which index it's gonna seek into. Once it figures out what it wants to look at, it's going to do this key lookup that's basically taking a pointer or an ID out of the first loop operation or this join operation and it's going to go back to the phone number table to get the actual formatted phone number. Okay? Now what happens when we actually run the query, we're gonna look at the estimated query or the actual query plan. I'm gonna run this. And we're gonna see something a little bit different. We got the information we needed. By the way, if I hit f four, it's gonna bring up this connection properties and it's gonna tell me how long it took to run that query. K. One point two eight five seconds. It doesn't sound like long. If I run it again, it'll probably be less time because now that data's cached in memory. However, let's go look at that execution plan. Okay. It's a little bit different looking now. Some of the things that we're gonna look at here is the two main things that stands out to me is, well, there's three things really. One is the cost. You can, it kind of gives you some, this is Microsoft math. The costs don't always add up. These percentages, they don't always add up. See that cost 89, cost zero, cost $0.11. This one actually adds up. Amazing. Okay? But notice that thick, thick arrow. It's gray on my screen. It doesn't look very gray up there. So what this is telling me, it's pointing to where's the cost in this query and that cost thing tells you anyway. But if I hover over this, it's going to tell me even more details. Some things there, I don't know if you can read those but if you look down about five or six lines, it says, well first off it's saying at the top, index scan, non clustered. Telling me it's reading the entire index and it says actual number of rows read. 363,000. It read the whole index. Okay. How about this one? By the way, it tells you how many it thinks it's going to read. There's actually an estimated number of rows read and in this case it says I think you're going read 363,000. Turns out you read 363,000 so I was right. The query optimizer was right. This is not a great query plan. The other thing that really stands out here is this green text. Does that show up green? It does. Missing index. There's a number there. The higher that number, the more important this is. But there's some things you really have to think about before you go start adding the indexes that the optimizer suggests in here. You don't want to add everything it suggests because you're going to end up with trouble. Okay? There's there's there's a right way and a wrong way to do that and there's a lot of experience that goes into that. It's not going to hurt anything but you end up with a bunch of indexes you probably don't need so you really have to weigh that. And you have also have to think about how often are you going to run this query. If this is a one off, let it go. Who cares? One second. Right? If this is going into your code and you're going to run it 50 times a second, you might need that index. Okay? The good thing is we switch back over to our query. I actually have an index that covers first name and last name so why didn't it use it? Okay. Here's the actual If I look at By the way, this is how you look at your index. This is the quickest, easiest way. Sp help index for person. Oh, I shouldn't have done that. You want to turn off your execution plan before you run some of these system store procedures because they're really gnarly. Okay. That's So you get all that back. Let me turn that off right quick and we'll go back over. We're going to run this sbhelp index on person. This is the person table in Rock. There's some that I've added. You can see some with dates and my initials and some stuff. But here we've got is deceased first name, last name, is deceased last name, first name. These are the key columns in that index. The problem is our query didn't include the first column of those keys. That's the primary sort, right? Everything sorted from left to right in your key columns. So this this index is sorted first by is deceased then by first name and then by last name. Okay. In this case I'm passing in or in the in the example above I only gave it the first and last name. So it basically had to scan to find that first name because I didn't tell it what that first column value would be. So here, if I just simply add, I don't want the dead people, by saying is deceased equals zero, right, watch this. We execute that. That seems slow. That could just be my connection. Oh, I forgot to show you that. Okay. But I got the same results. Okay. Let me turn that execution plan back on and we're going run it again really fast. Notice that that big thick arrow is gone. The cost and if I look at the number of rows read, actual number of rows read, one. Okay? So and it's not telling me that I'm missing any indexes. That's a good sign too. So if I go back and look again, I wanted to show you one other thing. Actually I don't remember what I was going show you so I'm going to skip that. All right. We're going to move on to the next example. Here is an example of what I might call pseudo Cartesian products. So someone talked about an example earlier where they had multiple communications being sent maybe to the same person. Why might that be? Well, here's a very similar query to what we just ran except I'm also inner joined to our group member and our group tables. Okay? And why might I do that? Maybe I want to find out some group info about Bubba here as well as his phone number. So if I run this, I end up with the same data over and over and over. Why is that? The reason that happens is because Bubba exists in multiple groups. So what you're seeing here is every instance that join happens and it brings back a row for all the groups. So there's one Bubba but he's got nine groups and so what SQL does is it just fills in the gaps. It's like it just fills it all in. So you got Bubba McClure nine times and his phone number nine times. It's the same data but the difference is or the reason that is is because of that join to group. So sometimes you can end up with extra joins and things that you don't need in your queries And what's neat here is if you look at the execution plan, you'll notice that because we didn't return any information from the group table, it doesn't even touch the group table in the execution plan. There's no mention of it in here. Okay? So it starts off with person, we got phone number, we got phone number, phone number. Group and group member were not mentioned because we didn't need to return the data. It didn't even look at the tables. However, that doesn't explain why there were nine columns. So I guess it did look at it or maybe that was statistics, I'm not exactly sure. But that's one reason why you might have extra rows coming back. Let's move on. Some other join types. Inner join and left outer join. Inner joins and outer joins. Something you need to know and understand if you're writing a lot of queries. I can't go over all that today. I know we're running low on time. So inner joins can also serve as a filter. Inner joins for data to come back in an inner join, the data or those keys have to exist in both or all tables in order for a record to come back for anything from any of those tables. I don't know if that made a whole lot of sense but here's what I can I'll show you. This is an inner join query of all the bubba's in our database, okay, and their phone numbers. Now if I want to see so what I'm using here is I'm saying only show me the bubba's that have phone numbers. I didn't explicitly say that. Right? But because of this join, this inner join to phone number, it means they had to have a phone number to come back in the query. If I change this to a left outer join, dangerous and it's danger. Okay. We're going to run that same query again with the left outer. It didn't work. See? That's why you don't oh, I've got written down here. You have to move the filter basically into the join and out of the where clause for this to actually work. But what you'll see is now I get back more Bubba's including those without phone numbers. That's what your outer join is doing for you in a scenario like that. Alright. Cross join. You won't use this very often but there are some of those out there. Basically takes every column from the left table and every, I'm sorry, every record from the left table and every column from the right table and just smashes them together. So you'll know how many results you have by the number of rows in the left table times the number of rows in the right table. In this case we've got, I don't know, a handful of campuses and a handful of sites and we get 198 rows. There's some very useful things for that but you'd have to Google that to go check out exactly when and why you would want to use that. That. This is probably one of the most powerful features that you need to know as well. It's called a windowing function. Again, some of this you're just going have to learn on your own. But I'm going to show you, just give you an example. Here we've got a fairly complex query plan. There's some parallelism and I can show you what that looks like in the query plan. There's also a missing index suggestion in this one. But what we're going to do is it's going to list out basically everyone that we've put in the database from a certain period of time forward. And I've got some dates hard coded in here that's hard coded on purpose. I wouldn't typically do that in like production code. I would use a variable. But for demo sake, we're going to do this and in fact we probably need to go back a little further than this because this environment hasn't So this is basically going to give me every person that's been added to my person table since June 7 and then it's going to tell me, it's going to join up to some group information and it's going to get me the most recently attended group. This used to be really hard to do but this row number function, I can partition it by the person ID and then sort it in a way that it What it's going to do, let me show you the inner query first. It's going to be a lot of data probably. So what this is saying is that this person attended our first and second grade and it just so let's see. Here's one where they attended multiple times and that row number function, it it sorted them based on the date, right, and then it assigned a row ID. And so in my other query, in my outer query, I say just give me the row numbers where it's one, right, and that's gonna be the latest one because I sorted it descending by time in that first query. Triumph has a fantastic video that explains these windowing functions so it's there's a link in one of the slides for that if you want to see it. But basically, if I run this and we look at it real quick. Yeah, a lot of data there. So we can see that David here, he attended this particular group and that's the most recent attendance but there were also 15 total attendances in that group since the time it was done. I didn't realize this until preparing for this that average, sum, min and max are also windowing functions. So you can actually do inner partitions on that and that's I had put this here, this total group attendances just to prove that point. So you see sum and then I've cast our didAttend as an int which is normally a bit and then I'm partitioning that by the name of the group. So interesting, I didn't even realize that. Oh man, golly, so much in here. So much in here that I wanted to get to. I know we just don't have time. Where we at? We had three Okay. Probably one of let's see. Scalar funk okay. This is probably one of them we're gonna do this one because it's a biggie. We talked about calculating that birth date. So if I go and I calculate, if I want to know all of my 55 year olds, this is one way I could do it. Alright. I'm selecting the the count of of records from by the way, count star is not a bad thing. The optimizer is smart enough to know that you're not selecting all of the columns out. Okay? So count star is very, very legitimate. Don't don't be afraid of that. Select star, you only want to use that when you're just poking around at data. You don't want to put that in your code. Okay. So our execution plan, our logical reads was 1575. We had 2275, but how long did it take? Look at our time, it took four seconds to do that. That's because for every record out there, it had to evaluate all three hundred three hundred sixty nine thousand and calculate the age based on the birth date. Okay? Now here's another way to do it. Why don't we just use the age column? Well, if I run that, what do we get? Well, it's faster. But if we look at our execution plan, we still had 2,600 reads on that. Why is that? Well, that's because if we look at our execution plan, we see it's an index scan and it's telling us we're missing an index. The age column isn't indexed. So if you want to use the age column a lot and seek on it, you need to add an index to that. So here's a way around that. You could actually code around it and you're putting your function basically in the right hand side of your where clause. So we're not wrapping birthdate in a function and you'll see this comes back pretty quick. Got a little gnarlier execution plan but if you look at our logical reads, it's only 16 and we get about the same results. And I say about because there's some things we'd have to do to calculate age a little more accurately than just saying fifty six years ago and fifty five years ago. Okay. I've got some really cool index scripts in here if you want to go learn about. I got the McDudes Mega Index Info script. Go check that out. Actually I'll just run it for you just so you can see it. And we are at time. Here's the results of that. And it will show you all the indexes on the table that you tell it to show you indexes on, how often they're used, what kind of activity, whether they're being seeked or scanned or using it for lookups. It tells you the column names, both the keys and the included columns. Last time it was used and whatnot, don't use this to go start deleting indexes. That's my that's probably your first thought. It's like, well, it's not used. I should delete it. Don't forget, every time you restart your server, those things get reset. So it's not a from all time, it's a since last server restart. Okay. I got a lot more here to cover but I don't have any time so I'm gonna let it go with that. Go ahead. Thank you guys. Jeff?