Live SQL Server Q&A!

ICYMI

Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.

Thanks for watching!

Video Summary

In this video, I share my day-to-day struggles and the challenges of balancing work and personal life. After discussing how sleep has been elusive lately, I transition into talking about my current projects, focusing on SQL Server training materials for both beginners and advanced users. The content ranges from foundational concepts like indexes and query plans to more complex topics such as hardware optimization and query tuning techniques. I also delve into the importance of read committed snapshot isolation (RC SI) and how it can significantly improve database performance by reducing blocking issues.

Full Transcript

I’m exhausted today. Beyond compare exhausted today. Sleep has not been my friend lately. Sleep has been. Sleep has been for someone else lately. Sleep has not been for me. Wish there was a good reason for it. Just like, sleep, cared, deprived, annoyed. So everyone go home. We’re out of here. It’s okay. I’ll stick around. I’ll try to be a reasonably perky host in this podcast. It would be really nice if you two showed me who was here.

The chat would know. I can’t see who was here. I can’t see anyone’s name. So you all have to say hello in order for me to say hello to you personally. Yes, Rowdy. Yes, Rowdy. I will be perky. Just for you. You be Rowdy, I’ll be perky. That’s about the best deal we can come to. Maybe. Maybe. Maybe. No. That’d be a weird thing. Weird. I have so much stuff to write. So much stuff to write.

It’s an awful, tremendous mountain of things to write. I’m just hoping that I can get through it all. Hoping. All of it’s about SQL servers here. None of it’s about anything weird or foreign or strange. I’m not going to like start writing on Python. Your home’s sick? My goodness. How do you get sick? Are you still in Texas? Last day I heard you live in Texas. Who knows where you are now? Man about town. Man about the country. You could be in an RV.

You could be in an RV. Was you up in it. You weren’t right my arts. And right. I’m sorry. I’m sorry. I hate work, man. But you can go out here and sit there and sit there and speak down at home when you are sleeping. something you like baseball you could be a football person my docket for today consists of trying to get through writing things for clients about what’s wrong with their servers and then trying to put put more of a dent in the the training stuff that i’m trying to produce for my site i have i have a lot of stuff written i have a lot of decks made a little demos written and uh right now up to the point where uh i’m going to do the query tuning bit so i’ve done like my sort of my intro stuff let’s go read read what i’ve done uh yeah so i’ve got my intro stuff i’ve got four decks in there about uh sort of starting like throw like a beginner’s deck um i said i think it’s probably my take on how to think like the engine probably the little engine they couldn’t uh after that uh you know some intro stuff on indexes and weight stats and query plans and then there’s like a like a real i mean i’m not gonna pretend that i can i can like deep deep deep dive but there’s like stuff you should know about the optimizer without going into holy crap what’s going on like so it’s it’s it’s high level it’s it’s not not very specific uh after that we talk about hardware so we talk about all like you know we go in depth on like you know cpu memory disk uh weight stats you’ll see related to those like when things get bad bad on the server like when things start to fall apart some of that’s based on um my my server tuning pre-con material uh then we get into some weird weight stats so my weird weight stats list consists of all the stuff that people tell you you should ignore that you should not you might you might have a favorite weight stat script that lists out a bunch of you can safely ignore these don’t worry about those pal but in reality they’re terrible terrible don’t listen to people on the internet about sql server they’re all wrong after that we get get further into indexes talk about like you know your normal run of the mill rowstore indexes you’re clustered you’re not clustered uh we get into some special indexes and uh eventually i will when i feel like it this may be about when i feel like it there will be stuff on uh partitioning i’m not crazy about partitioning i’m going to explain partitioning but i’m going to compare it to one of my all-time favorite things in sql server we’ll get into that that’s going to be a top secret it’s a big secret uh and then after that’s all the query tuning stuff is going to be like the the easy medium and hard sort of leveled off stuff there so you know we’ll have to start with the easy stuff like the implicit conversions and the you know uh like you don’t have an index and sarg ability and we’ll go on to the harder harder stuff and we tackling spools and giving them noogies and all sorts of stuff that guy’s a fan yeah that sounds great i’m gonna buy that i’m gonna buy that too we’ll show anything from you and then uh eventually eventually i think after the query tuning stuff it’ll be blocking and blocking but it’s never been my favorite thing in the world never been like wow i love i love walking and walking so i’m trying to figure out how to approach that in a way that it’ll be fun because my my favorite way to approach that is is to turn on read committed snapshot isolation or snapshot isolation and i just walk away from everything but you know there’s there’s stuff that people should know about what makes walking better and what how blocky is made see alexa you should live stream yourself trying to automate stuff using power i have nothing to automate i’ve literally i wish i had something to automate i don’t have anything to automate give me something to automate my life i would love to do it i think most of the things that that i need to automate are are done for me through biological processes i go to bed i wake up things leave my body things in my body treat treat every single day all right he says i need some serious help with getting back into query tuning well uh if if you feel like you or any or other others on your staff might benefit from the trainings of a decent looking consultant i would be more than happy to uh to to yell at people about things in a nice way i’ll be more than happy to point yes this is something you should do it stuff like that that’s helpful right that’s what people want to hear about query tuning yes you should do it yes you should tune that query yes that is slow tune it make it so aaron says rcsi should be the default i agree i agree wholeheartedly rcsi should absolutely be the default it lines up much better with what people would expect from a data boss i think and uh beyond that i think that um it’s it’s unfair to throw pessimistic locking at people and expect them to not immediately go to using your lockings and get it wrong i mean morally ethically i mean if if i look hard enough i can probably even find where it’s legally wrong to do that if you need a certain type of blocking you should be investigating the necessary hints or like sp get at lock in order to in order to get the kind of locking you need the rest of us should humbly be able to live our lives without having to deal with blocking and blocking just between readers right because it’s nonsense absolute nonsense it’s been a mistake for years i can’t i can’t say it drives me nuts the number of people i’ve seen just break down because they they can’t figure out why everything is slow sometimes or usually something like that it’s it’s just locking you go look at you go look at lock weights and it’s something like like like like lock weights are you know 30 40 50 percent of server uptime and the average milliseconds per weight is like five ten fifteen twenty seconds or beyond and you’re like yeah this is the problem you found it how are we gonna fix it it’s just like our csi is just that button it’s always right there always right there every every other smart database stuff every other smart database it’s not even it’s not even like you know uh not even like like microsoft would be the outlier unlike microsoft would get made fun of for being pessimistic by default microsoft would be like welcome to the club thanks for joining so you you finally figured it out how nice it is azure sqdb is pessimistic or optimistic by default let’s see why the box product has to be done and ugly let’s see here airlock says i automated system inventory server stats but the server configurations disk space information patching completeness account permissions on the server oh wow yeah you you must have do you have a lot of servers how many servers do you do that for that’s what i’d be curious about it’s a very enterprisey thing that’s a very like i have 20 servers and i don’t feel like i feel like figuring this out i’d also be curious if you have monitoring on those servers like if you have a monitoring tool looking at those things 120 get scanned holy cow not just for sql server either oh yeah if i had 120 servers i i’d probably i’d probably buy buy a bigger stake in power cell too me with my me with my one server i don’t know that i yeah solar lens so there are like things that i wouldn’t i wouldn’t wish on people like burpees or herpes or turkey bacon that list may or may not go on and extend to certain monitors uh you know go with go with god baron said the same thing wasn’t a good estate tool out there but i do pay for monitor service century is much better than what i’d write yet century one century one is i think um if i had to pick a monitoring tool to to work with professionally century wouldn’t probably be different that would probably be that would probably be what i could with i like i like stuff about it you know it’s not perfect no monitoring tool is but i think from what i do century one gives me the best round of information gives me the best i can point and click on some stuff and get stuff done you know it’s tougher with other it’s it’s either it’s either really hard with other monitoring tools or i just i just haven’t had the the luck of clicking in the right place on the other monitoring tools sometimes i use other monitoring tools and i right click on something and i’m like oh i would love to i would love to right click here and get more information and then i right click and it’s like save as png i’m like i don’t want to save this i want to do something else with it you know dig in what is the point of you it’s ridiculous all right he says been digging into custom queries against essentially db’s lots of data in there yeah uh i wrote a custom query in a blog post um and it’s actually kind of a cool one i was i was i was rather i was rather proud of myself for writing it uh what it is it’s a query that looks uh in the century one repository for queries that have waited a long time on getting a memory grant so and then it like compares like how long the query waited to get a grant and how long it ran after it got the grant and it’s usually crazy like i found like i found some crazy stuff when i was digging through some client repos where it was like it was like 20 30 seconds to get a memory grant and this was a goddess stories of don’t man y’all need some memory i can’t sell you that but y’all need some now that i think really i should go into ram ram sales i think i would make a killing on ram sales the century db isn’t the most intuitive but it is elegant uh i don’t know that i call it elegant but uh uh yeah i think my biggest issue with it is uh like decoding some of the hashes in there like using decompress or whatever someone’s someone’s up there stealing a car or something or maybe there’s there we go but uh yeah it’s uh so like i always run into weird issues where like when i try to use decompress on like the encoded columns where like it was somebody like the value is corrupt and i’m like well no it’s not but i’m not gonna i’m not gonna keep trying here either it goes or it doesn’t i don’t know let’s see here what else do we have going on anything come in i don’t know why i always i always look to see if someone has asked me a question somewhere else like like twitter or email or whatever but no one ever does it’s time to get lonely out there but me a very lonely very very lonely i don’t know i think uh at least i hope that um is i skipped your question where’s your question oh look at that i did uh joe says would it be odd or wrong to have a table with some indexes that are page compressed some row compressed and some with no compression at all no i don’t think so uh i mean it might not be like at first glance i would i would probably be weirded out about it at first glance i’d probably like you know what happened there but there are valid reasons you know without without without digging into like the the scant fiddle that i remember about the compression documentation now there are certain there are certain values that uh like i believe it’s string value or string values date values maybe that work better with page compression and then like integer values work better with row compression and uh some indexes don’t compress well at all in fact some with some indexes if they’re if they have a lot of write activity if they have a lot of modification activity compression can actually cause uh some issue or not issues but i think compression can slow down uh modifications in some places at least you know this is going off stuff that i remember from you know a long time ago when i was messing with it don’t it’s not probably not chapter and verse correct but it’s uh it’s decent enough it’s it’s a good enough memory to uh to uh to have that in my head so groudy says the weight stats are awesome i got into time-based index usage and operations yeah well there you go yeah a lot a lot of monitoring tools collect lots of cool stuff you know um it will be be nice if you know they got some of the stuff got exposed more in the gui so that you didn’t have to go writing crazy database queries to to get it i mean i mean on the other hand it’s cool that they let you go in there let’s see uh alexis what webcam do you use i use a logitech brio uh it’s a little dusty on top right now it’s the logitech brio i like it a lot uh i don’t recommend using the microphone for it but using it uh using it for a webcam is pretty darren says do you have any methodology you follow when auditing indexing by auditing do you mean like index changes or do you mean like the current state of indexes and if indexes should be kept or dropped or modified in some way there’s a slight difference it’s like not like a well you know it’s a big difference current state yeah so i mean i love blitz index for that uh run it in mode zero if you just want kind of uh like the the important stuff on your bigger tables run it in mode four if you want absolutely everything you know i’ll go you know i’ll go through and it’s it it prioritizes stuff for you in a pretty good way it’s not perfect but it’s it’s pretty good um i think i think what i like best about it is that uh it gives you a really good view assuming that you know uh your server has been up for a little bit and assuming that you know um uh in that you’re not on a version where index rebuilds or perhaps some recent modifications have cleared out valuable usage statistics above your indexes um it gives you a really good view too if indexes are used or not right so but uh if how how much if if all indexes overlap so you know it’s important to you know make sure that if you’re going to go in and start adding indexes to help queries go faster that you’ve cleaned up some of the cruft some of the not so hot indexes in there that already exist so you know the first thing i always look at because it’s the easiest to sort of pick out and say screw this are like the unused indexes so if like you know indexes have millions of rights against them and no reads or a very low amount of reads let’s index will call that out and you can pretty safely get rid of those you know i keep everything as a like i keep a create drop script for everything so that i know um so like if i drop an index and something goes terribly wrong the table i can add that it makes back pretty easily and then uh after after that is when i look at the uh the duplicate and borderline duplicate indexes just because i i quite often find that once i get rid of uh some of the unused stuff the uh a bit a few of the more than a few of the the border the duplicates will disappear with them so i usually do the dupes and borderline dupes last because those are a little bit more work right you have to like look at the full index definition the key columns the order the included columns and then figure out if you could like work them together in some way that makes sense and that’s more brain work that’s not as easy as just saying that’s been used zero times it’s been written to 50 million times it’s got to go it’s just taking up space it’s not helping anything uh and then so like the next thing next next next trip around will be the dupes and borderline dupes and then uh you know after that after that i’ll um so like i have very mixed feelings about the missing index requests uh you know they are certainly a good sign that we could do we could have something better like we could have a we could have a helpful index on this table the number of caveats to the indexes that that get suggested are pretty big but what i’ll usually do is like you know pick some of the really high value ones in there like what’s index will give you an estimated benefit number if it’s over like five ten million then i’m like okay we’re gonna we’re gonna we’re gonna go with this and we’re gonna see what happens right because like at that point you know it’s again it’s not going to be the best most perfect wonderful index in the world but it’s going to be a good enough bridge to get us to when we can figure out what would be so like if sql server is just absolutely screaming for some index i’ll go ahead and create it just to shut it up and then you know when i like when i have a more intimate knowledge of the workload the query is running then i’ll be able to figure out what that what the better index is for that situation because i mean it’s not like the missing indexing is always wrong but it’s wrong at a good enough pace that you know i don’t buy it for everything you know like that recommends columns and like their ordinal position in the table not by like the absolute order that they should be in in the where clause uh that they’re in the where clause or like even by which one’s more selective or anything there’s a lot of like the optimizer doesn’t really take sorts into account it always seems to stick joint columns and has included columns there’s just like a lot of stuff about the missing index requests that you know they they irk you when you’ve reached a certain point of query and index tuning but up until then they the suggestions are probably better than anything you’re going to come up with just staring at the workload right they’re they’re they’re there for a reason and they’re they’re usually a good enough bridge until you can really get in and fine two things it even says in the documentation it’s not meant for fine-drain index tuning so uh you know read that carefully if you need like you know if you need like very specific advice and don’t tell anyone i said this use dta instead just don’t create every damn index it suggests dta will do a better job than the missing index requests but dta will ask for crazy banana bonkers town indexes too dta will ask for 11 indexes that are almost the same definition just flipped around a little bit with like slightly different includes or slightly different order of key columns and it’s he agrees uh but you know so it does require adult supervision it’s the suggestions are better but it still requires an adult to sit there and go through the suggestions and say yes or no to them to me dta is usually a four-letter word because i spend a lot of time cleaning up dta messes where someone was just like oh look at these hundred indexes we need those it’s just like it’s same or whatever and and and a hundred indexes get created but you know it’s it needs adult supervision like anything else it’s the the suggestions are better than the missing index request dmds but it requires just as much adult supervision as the missing index requests and says the unused is going to be helpful seems like there’s a lot of indexes that don’t have the correct leading edge column uh well you know uh again that’s that’s an intimate workload knowledge thing that is not something that i can just say yes or nothing to here uh usually think of missing indexes as a need to index here but this might not be yeah you know it’s like it like like i said about like i’ve been saying about missing indexes for a very long time is uh they’re a lot like a crying baby right like there’s a lot of reasons that a baby could be crying and it’s it’s up to you is the the hopefully sober and caring adult to figure out exactly why that baby is crying and exactly what that baby needs to stop crying you know it might be a burp it might be a dirty diaper it might be hungry it just might want to do something but might be bored babies get you know babies get bored babies get bored and cry it’s like i just want to do something else like yeah i get it baby i’m gonna do something else i mean it’s it’s up to you to kind of figure it out it’s a it’s the the crying baby of performance tuning you know something is happening over here and it’s up to us to uh figure out how to how to fix that happening it’s uh it’s interesting though like like what like one of my pet peeves with the missing index requests is that uh when sql server has a query plan with an index in it that index pool will get created and then thrown away every time the query runs but no missing index request gets generated either in the query plan or in the missing index dmds but dta will suggest a pretty decent index to cover the index missing indexes some reason they overlook that some reason i don’t know why but you know microsoft stopped responding to my phone call my emails get a lot of my emails i seem to bounce i talk about this stuff i don’t know there’s lots of stuff that i would love to make better that i would love to love to make like getting like you know actionable like more like like more actionable feedback more readily available to people but you know not everything not everything solves a big enough s.a.p customer problem so not everything gets prioritized the way that i do instead we get poly base and we get uh poly base we get python java r we get we get lots of stuff but we still have the same performance problems lots of stuff microsoft is not dancing with who they from a lot of those find that indicating protection so which is um the party says i like the dubstep remix of this episode yes so certainly a lot of noise behind this thing one of these days one of these days i will i will be a a very rich consultant who lives in a high eyes surrounded by glass where there is no environmental anything no toys no bugs no wind no heat nothing someday I’ll be there someday when I am I guess these these webcasts will be a lot quieter I don’t know I don’t know maybe maybe maybe I’ll be too rich to do maybe I’ll maybe I’ll just have an army of SQL SQL serpents I’ll just sit around eating caviar Dallas offices are cheap yes but they’re in Dallas which poses quite quite an issue for me because Dallas is too hot constantly I don’t have a car and it’s in Dallas that commute alone makes it not worthwhile anyway we’re about at the half-hour mark and I have I have a lot of stuff right so I’m gonna get going thank you for hanging out with me thanks for uh for doing this thing with me and I will talk to you next week

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.