You Know, For Kids
Video Summary
In this video, I wanted to share some insights on crafting effective abstracts for conference sessions. As someone who has presented at various SQL Server events and continues to do so, I’ve learned that the key lies in striking a balance between intrigue and detail. The first line of an abstract needs to be compelling enough to grab attention—like a movie poster or Netflix thumbnail. It should pique interest without overwhelming with specifics. Following this, the title is crucial; it must clearly convey what attendees can expect while also catching their eye. I often reference Adam Mechanic’s approach as a benchmark, reminding myself and others that clear, engaging content is key to drawing in potential attendees.
Full Transcript
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
أي KHERRI. What a Friday, huh?
what a Friday it’s always exciting when it’s Friday because I know exactly what I’m going to be doing all weekend working working, working, working working on things for you lovely people out there working on things will hopefully make your lives easier less painful you know all that good stuff all that good stuff hopefully I can give you ways to make your job like maybe not hate your job as much or something, I don’t know like little things, right?
like little things that you get out of life not hating it not hating life is typically an admirable perhaps not achievable but admirable goal we should all strive to not hate life so how is everyone?
I assume you can hear me since no one’s complaining about not being able to hear me and Streamlabs is at least telling me that my microphone is receiving input but who knows who knows what these eyeballs have in store who knows what’s behind these eyeballs?
there we go we have message number one 99 to go until Twitch congratulates me on people 99 to go oh my goodness is that Chrissy here?
I’m terrified now now I’m terrified I’m going to say something I’m going to say something incorrect about seltzer and get yelled at see I’ve got two cans of seltzer here if you’ve been to my previous streams you know that I take seltzer very seriously take seltzer incredibly seriously and this this LaCroix seltzer right here is perhaps one of my least favorite seltzers of all time you know why?
the bubbles big and soft when you open it they sound big and soft like that’s a big soft opening and when you drink it it’s barely a seltzer it’s barely a seltzer the bubbles just pass over your tongue too quickly this Canada Dry the official seltzer of Canada it’s much sharper bubbles when you open it ooh you hear that?
bah it’s a big pop it’s a big heart these are good aggressive bubbles they spike the tongue they get on there they make your tongue feel like you’re drinking a seltzer it’s the big stuff it’s the good stuff big soft bubbles if you like big soft bubbles thank you thank you if you’d like to know more about it I will post things about the mic and headphone combo when I’m done but to me I feel like I’m still junior level with all of this with all this streaming stuff I feel like just very entry level I’m just showing up like hey I have a great screen hopefully I won’t elbow it and knock it over it’s like silently praying that I won’t fall backwards unless I ruin the illusion and see my dumb posters beavis and butthead artwork the best streamer on the whole twitch plant I don’t know I’ve never taken my shirt off shown my feet or played a video game so I think that there are people who are light years ahead of me with streaming who I think could easily topple me as in the best streaming category we are going ASMR with soda bubbles you’re going to learn a lot about soda bubbles today a lot you know what I think what I might start doing so that I can meet in the middle with much better streamers than me is I might get the headset with cat ears so I can at least be sort of cute on stream could at least be like a little cute do like a few cute things I could do like some paw stuff like this and then maybe I would catch up maybe I would catch up with with other streamers who are who are much better than I am much more talented than I am much better setups than I am I get I see what happens to me is I get weirded out because I watch people who stream and record streams and I think I think they have either much more talent with video editing than I do or they have like a company that does it for them because they get all these like cool graphic popovers and like sound effects and like things are like queued up properly like they’ll be like if you want to see more of my content like and subscribe and like a little ding ding ding bell will go off and like the button will show and you’re like what how’d you do that me I’m just like there’s a button somewhere on the website you’re looking at press it it’s okay I would like to have you back having people here gives me self-esteem when I have eyeballs when I have eyeballs I feel better especially on a Friday I feel better on a Friday all right so let’s get some nonsense out of the way let’s see it’s still hard for me too I hear slobs helps with the glamour but I haven’t so that’s what I use right now I use Streamlabs OBS and I think without the help of Drew Fergwell I would I would have I would have a much much crappier slob setup it there was a bunch of weird stuff that went wrong at first like I was starting up like when I share these screens with you they’re RDPs to either a VM that I have local on my laptop or a VM that’s running on the desktop that I have down here and to my right so for like SQL demos and stuff I go to the desktop so that I don’t have the streaming stuff competing with the demos right because some of them can get pretty intense when I used to record videos and everything was local on my laptop if I ran a demo that was particularly stressful on the hardware like if I was intentionally making SQL server boot on memory or on CPU the video recording would start to suffer like my voice would go all and like the camera would get weird and like pixelated so I realized pretty quickly that I had to like offload that stuff somewhere else but when I first started doing it I would do what I normally did when I did like Camtasia or something and I would just be like okay well I’m just going to hit I’m just going to type RDP down in the bottom and the RDP window is going to come up and I’m going to share that but what got messed up was that slobs couldn’t detect the window when I typed in RDP I had to type in MSTSC and Drew helped me figure that out Drew also helped me get my act together with the size of the canvas I was working on and the chroma key thing and getting things worked out so there was just a lot of stuff that were not for some help from some smart kids I never would have figured out on my own but as much as I’ve encouraged the smart kids to blog more about things they know about streaming they haven’t so it might just be on me to write the dumb kid distillation of what the smart kids taught me how to do so it’s tough so slobs does help with stuff but I just don’t feel like I’m a super advanced user and you know like adding in the crazy pop up here’s a thing like right now I don’t even have the slobs ability to not have my arm I can’t figure this out I end here that’s the end of my world and I wish I wish that I could figure out how to get like myself to just sort of be big enough to reach across the screen but without like making me so big that I cover up a lot of the screen without it like I just want I want to be able to move my arm in bigger directions but some of that is like office space limitations too like I don’t have the most amount of space right now but hopefully hopefully we’ll see what happens so we’ll get a couple silly things out of the way we are or we I am going to be presenting two online classes about performance tuning SQL Server it’s a full day of learning and it also a ticket to that class will get you access to all 24 25 hours of my recorded content if you use the coupon code floating above my head there that for some reason powerpoint has told me is a typo we’ll fix that we’ll fix you powerpoint if you use that you will get 75 bucks off the face value of a ticket and I don’t know that’s that I hope to see you either on July 10th or 24th those are both Fridays one of them is next Friday and one of them is I don’t know see if I could do this in movie style then it would be Friday and then next Friday and then Friday after next and I don’t know if they line up quite that well but they’re both Fridays in July so we have that to look forward to have that to look forward to so let’s get out of this enough enough advertisement as they say and let’s look at we’re going to look at a blog post first the first thing we’re going to do before we write one single second of a thing on the screen we’re not going to read the whole thing because you can tell by the size of this scroll bar over here that it is a long post and there’s a lot of information in this well actually there’s a lot of comments on this post apparently there’s more comments than post maybe but it is a fairly long post and there are a fair bit of words in it what I’m going to do though is I’m going to stick the link into chat so that everyone has it if you want to read it if you want to ignore me and read this if you want to save it for later either way is fine with me I won’t judge I appreciate I appreciate that you would come here and then click on that at all but this is a blog post by my old pal Adam Mechanic and he wrote this Adam you know spoke quite a bit Adam was you know quite adored and admired by the SQL server community until he left us for Python and Postgres and whatever other things he’s doing I hear he cooks with sea urchins a lot these days I don’t know he’s a very fancy person very fancy person but he wrote that he used to speak a lot he used to you know do a lot of pre cons SQL Saturdays user groups conferences all that good stuff and he was like he wrote what I thought were were very good very detailed abstracts and intros to his pre cons and so whenever I’m sitting down to to write anything new about what I’m going to teach people about I always like to go through this and just kind of you know like remind myself of a few things and I think one of the most important things is is this header right here because this is something I always mess up I try to write I try to write an abstract that I would think was like cool or funny or you know that like like I would look at and be like oh yeah I’d want to show up to that and that like well that’s not entirely wrong what that leads to a lot of the time is me not giving enough detail about what I’m going to be doing I sometimes make the mistake that like people might see my name and people might see like the title of the the session and people might see like sort of a round up of things that like things that I put in the abstract but like without enough detail without enough me saying like here’s like exactly what I’m going to go through the thing is that like sometimes I find that sometimes I find that tedious to read and sometimes I don’t know if that’s what’s going to grab people so what I try to do is have a mix in there I try to make that like first line like pop that first line has to have like some zing to it that first line has to be like like this is what’s going to grab you and make you want to like maybe read the rest of it or at least skim enough of the rest of it to like be like yeah I’m into that right like like like like you know like the cover of a movie back when movies used to have covers you know you’d go to like blockbuster be walking down the aisles and be like looking for a movie to rent you’re looking around and I know I know this is going to age about as well as a phone book analogy for indexes but that’s okay it’s okay you we will work through it we’re grown-ups we’re adults we’ll hold hands we will make through make it through this together but like has to pop right has to be interesting now like you know if you’re scrolling through Netflix or like Hulu or whatever service you use they don’t sponsor me either the only the only sponsor I’m after is Canada Dry Canada Dry if you’re watching I’ll work for Seltzer but so like when you’re when you’re like looking for something to watch like like either like the the picture that you see or like that the first line that you that you see has to kind of grab you right a lot of the times with with conference speakers you know there’s a cult of personality around them you know if you show up to a conference looking to learn something in particular or you’re looking at a conference website looking to learn something in particular there’s a list of names in like your mental rolodex of what things that you work on things that you’re interested in and you know you might like see a name that matches up with that you know whether you know it doesn’t matter what it is and have to be SQL Server it could be anything right like if you’re really into HADR you might have read like Alan Hurt stuff and you’d be like oh yeah I gotta go with Alan Hurt right like stuff like that there’s a mental rolodex of people who do that kind of work and if you are going to a conference or a session or let’s just let’s stop saying going to let’s say attending because going to these days is not it’s not a thing if you’re attending a conference or a user group or anything mentally you would you mentally associate this person with doing that thing like like that that’s like one of the first things that grabs you if you don’t like see then if you don’t see a name that grabs you immediately then you might start looking at titles so the title has to be really clear and the title has to grab you once you make it past the title well do people really want to sit there and read 10 paragraphs of explicit detail about everything you’re going to cover and learn and talk about and all like little bullet points and factoids and everything I don’t think so I mean I’ve never I’ve never had it come to the point where someone was just like you know I’m on I’m on the fence about attending your session I’m this close I just want to know if you’ll cover this one specific topic that wasn’t listed here like I’ve never had that like in you know I think sort of generally people understand from the title and from like the zinger what you’re going to get into and so I try to like you know get really specific with that stuff so like the title has to be specific but it has to be catchy and memorable and the the first line like the stuff that you put into the first into like that first paragraph or so the first few sentences not even like a full paragraph like like two to three sentences has to like grab people a little bit and it has to be something that people identify with you know if you are going to be teaching level 100 stuff or level 200 stuff that’s totally legit you can totally do a full day of teaching people you know like how to SQL with training wheels on I’m not against doing that it’s not typically what I aim for but it’s you know if that’s if that’s your jam go for it but sort of like generally you know I aim for somewhere in the I aim for an average of 300 but the day is gonna spend time between 200 and 4 like on the 200 side and on the 400 side I want an average of 300 I want people who maybe aren’t so advanced to be able to get to 300 and I want people who want really advanced stuff to also be satisfied with going above the 300 mark so you know you try to straddle the 300 no one’s really doing 500 you can’t do 500 500 is would be very difficult to do in an hour 500 is still pretty tough to do in a day 500 requires so much technical background and detail that it’s it’s difficult to properly humanize and I think the average conference attendee doesn’t appreciate that you know there are there are select few people who appreciate that but if you want to really really like reach out to a wide audience and get like like a like appeal to a bunch of people saying that like you know you’re gonna spend the day at 500 a that’s a tough mark to hit it’s tough to go eight hours at 500 eight hours at 500 stuff eight hours at 400 is tough eight hours at 500 is like I think you would have to be David DeWitt to spend eight hours at 500 but so like you know try to straddle it’s like try to straddle things right like set expectations for what we’re gonna do and set it appropriately you know I think that everyone learns something at some level regardless of whether it’s that 200 300 400 but you know you do you do have to you do have to give people mental breaks and you know giving the people who show up for the 400 level stuff a mental break is good and giving the people who show up because they need to get to 300 some like like like like totally it’s like like really tough stuff to think about down the line that’s also good right like challenging people is good but you don’t want to lose people right so it’s sort of a sort of like a it’s a fine balancing act he says I had a week course at 400 I reckon it was exhausting well yeah that sounds that does sound exhausting I think a week even a week at 200 is exhausting like I think like you know like beyond a day you’re that’s that’s a tough one to pull off it’s tough to pull off so there’s there’s lots of important stuff that goes into figuring out a what you’re gonna say what you’re gonna say about it you know you’re gonna have people you’re gonna have to get people in quickly you’re gonna have to get people to invest quickly because they have a lot of choice especially these days there’s a ton of choices out there for people to go and you know get their learning from everything is online and that just makes everything so much more accessible he says it was way over me if I’m being honest I was good for two days really yeah so you know there’s um if if it’s constant 400 after I think you’re right at probably after two days the the glamour wears off and you’re just like constantly bludgeoned by despairing facts and crazy niche stuff that you might have to do and be aware of and learn in these very specific scenarios but the other I think so like that brings up something in it because I think I think another really big problem with constant 400 level stuff or I mean forget 500 level stuff anyone who says they’re presenting at 500 I’m not sure I’m not sure like that I think that like like you would have to really carefully qualify someone talking about something at a 500 level like it couldn’t just be someone who like just uses it I think you would have to be talking to someone who like was part of the design or development team of a product or feature in order to get 500 but I think a lot of the the problem or the a lot of the difficulty with a constant stream of 400 is you’re just playing SQL jeopardy you’re listing off facts all day you’re listing off stuff that might never apply to people you’re listing off stuff that you know like you might have seen once in 20 years of working with whatever technology you’re talking about and you feel compelled to tell someone about it because just maybe just maybe it’ll it’ll help them one day but you know that’s what’s when you start that’s that’s that’s when like the the checklist stuff comes in and the SQL like the you know I guess this the this subject jeopardy stuff kicks in it just gets really tough to stay at that like get like get at that level and stay at that level that’s a it’s a tough one so there’s stuff in here that’s very good to consider when you’re writing an abstract right appealing to your audience figuring out who they are I start a lot of my abstracts with saying you know you’re a DBA or developer who’s been working with SQL Server for x number of years you know identifying the crowd be like oh yeah that’s me you know you have tough performance tuning problems like XYZ and XYZ can be you know XYZ doesn’t have to be terribly specific like XY doesn’t have to be like and you have a lot of problems with with like paging queries or you have a lot of problems with like I don’t know whatever else right like you don’t know how to read execution like there’s a lot of like it’s like like overly specific stuff that you can put in there but you need to be able like like this is like for me some of that stuff is like what goes later on like when we get into detail when I’m trying to figure out what problems you need to solve I’m like you have a tough time like figuring out where to start you have it you don’t you don’t know where the problem is like you know you have performance problems but where are they is it the queries the indexes is it like you know is it like the way your tables are designed are your server settings terrible is your hardware like underpowered like like like where do you start right like people who are just looking at SQL Server and like maybe open activity monitor or like run SP who or SP who to and is it just like the I don’t know what happened because SQL Server is tough like that SQL Server does not make it terribly easy does not make it terribly easy to figure out what happened they make it very easy to see sort of they make it easy to see what’s happening there’s a lot of stuff you can capture hitting f5 but you know if if something was wrong you know at the rate that users report it you know last Thursday or two hours ago or something you know this might not be a lot you can figure out or do with that so you know a lot of people do have problems a lot of people do have problems with figuring out you know just where to begin with SQL Server troubleshooting like and you know it’s it’s my job as a presenter to think to tell them which place I’m going to get them to right carvin says can you please suggest database migration checklist no that’s not the kind of thing that I do but if you really want something good to help you with that give me one second to bring up the commands over here migration here we go if you want something to make that easy there you go but just to make it perfectly clear this isn’t that is not something that I do and that’s not something that I have and if if you have more detailed questions about it I have I cannot I will not have more detailed answers if I had to do it that’s what I would do I would I would I would hit it’s not f5 in PowerShell in PowerShell it’s f8 I would grab that script I would hit f8 and I would I would sit back and let PowerShell do the rest I’m not smart enough to have written it but I’m smart enough to use things that smart people write because that’s that’s basically what the world is is is learning something is like being lucky enough to have smart people around you do things and you just say oh they can make my life easier so let’s see when we’re when you’re trying to figure out who we want to talk to how we want to talk to them right and we and we want to make sure that we do it in a way where you know someone who might be like nervous or just sort of like unsure where they fall into the world well I mean you have you have to be careful with the way that you phrase things and way you the way you word things right because you you want you really do want everybody to be in there like you don’t you don’t want to say like oh like you know you’re you’re a you know beginner IT guy or something like that right you just don’t like you know leave that kind of stuff out so what we need to do is figure out who we want to talk to what we want to talk to them about and then we can come up with the catchy stuff we need we need to know we need to identify a few things first before we go and before we go and even start writing a single thing he says must be utterly nerve-wracking doing presenting as I said it’s no like the presenting part doesn’t really make me nervous anymore streaming makes me nervous because I am just not confident in the technology enough like I I I’m relieved and I find it to be quite miraculous that I could download a few things hit a few buttons and show up on a screen and close to real time in front of people I am amazed by that but like it still makes me nervous it still makes me very nervous I don’t like like nothing I think is bad technology it’s just it’s it’s nerve-wracking like just think like waiting for something to go wrong right like waiting for audio to cut out waiting for video to cut out waiting for like my internet to fall apart waiting for like one of like the receiving servers to just fall apart like just all this stuff that could go wrong when you’re on screen you’re just like oh please don’t fail like like and it’s not because I’m like I would be embarrassed for me it’s because like I don’t want I don’t want anyone who watches me to have a bad experience watching it’s tough enough watching me without technical difficulties so yeah so Adam brings up a good point and this is kind of the point that I was starting to make is people can’t be some people can’t be bothered to read big full paragraphs of words because I like the lack of pretzel so I I tried pretzel for a minute I downloaded it and I started listening through the music the EDM category was deeply deeply offensive deeply offensive but even more offensive was the hip-hop category if you ever want to be deeply offended if you if you like hip-hop at all and you want to be deeply offended look at like listen to the music in the hip-hop category on pretzel you will be so angry it’s it’s horrible it’s horrible read in 2020 let’s see I know that’s in here somewhere yes read in 2020 is there is there a reference to 2020 in here I thought there was so yeah so let’s let’s let’s back out of this blog post because I think we’ve spent enough time in the blog post all right there’s there’s enough in here there’s enough for you to go over later on your own so let’s talk a little bit about what we could present about because usually you know the thing that I get into and that font is just terribly small I don’t know why I don’t know why you’re messing with me PowerPoint’s messing with me all the time let’s make that a nice size font 28 sounds good to me that’s because what I usually talk about is performance what I don’t talk about things that I’m not particularly good at is HA no DR no security hell no not my jams PowerShell I’ve tried it was not I found myself sadly wanting in all things PowerShell couldn’t hack it I was not good I was not I was not good enough I was not smart enough and and power me and PowerShell did not get along what would always happen is uh you know I would and like I would I would I would have something to do and I would think this is what people use PowerShell for this is what people use it for and I would spend some time like searching around for the right commands to run and I would like try some stuff out and then like you know I would start getting closer and closer and then like three four hours later it would just be like me weeping over the keyboard because I couldn’t get anything to work it was just I T SQL has so infested my brain that that is like that is just where I have to go and where I have to stay whenever things get whenever things go outside of that I fall to pieces this is like if I’m not performance tuning something I’m like I don’t I don’t know what it does like like like poking it just like what are you I don’t understand so uh we shouldn’t we should at least get this to now match up right heck yeah so I do performance stuff uh yeah so it’s just it it’s funny how like like someone can be you know very very good at one thing technically or even intellectually or you know and just like you know be able to just like very quickly deeply like understand and grasp things and then look at something else that’s like equally and you’re just like like I don’t know like riding a bike like like riding a bike just like ah pedals like you could be an f1 driver like like be able to speed around tracks at like close to 200 miles an hour and handle things perfectly and then like like you know look at look at look at a bicycle and be like I what is this is this I don’t I don’t know I don’t know it goes too slow for me to figure it out so we have performance which I’m into HA is out DR is out security is out powershell is out but we still have some things within performance right within performance we have some choices do we want to talk about server tuning which would be weight stats hardware settings etc do we want to talk about index tuning and if we’re going to talk about index tuning what are we going to talk about within index tuning we have to set some expectations here because there’s a lot of different kinds of indexes within SQL Server not all of them are typically well used and they’re probably not things that people would expect you to cover but you have to you know if like I think you know you don’t need to say uh no XML spatial or in memory like you probably don’t have to go so far as to say that’s but you should probably specify are you going to cover a columnstore are you only going to cover rowstore like what what are you going to cover within these things now I I love I love columnstore but I do not have the chops with columnstore to spend a full day talking about it the internet is full of blog posts about columnstore that are just sort of rundowns of the documentation you can kind of tell that someone flipped on their laptop you know had like one of the smaller Microsoft databases ran a few scripts and they were just like cool here’s a blog post you they’re missing that sort of deep understanding of columnstore the things you can run into it like the actual like production usage of it like my friend Joe Obish he uses columnstore like a champ he uses columnstore like a champ he but he’s been through serious serious pain learning it without that pain some of that some of that learning just isn’t there and you kind of get this like you know that shady acres pamphlet like just send your data to columnstore it’ll be it’ll chase rabbits all day there will be other data just like it it’ll have friends without like any of like the real deep understanding of just like oh damn don’t do that oh dude just stay if you’re doing that run screaming like bad idea bad idea bad idea you know you get like you get like the glossy pamphlet you don’t get you don’t get the full story there’s a lot of errors and omissions when people have not used something deeply in production and so as much as I love columnstore I am not qualified to talk about columnstore I have never done any big uh you know columnstore migrations I’ve never done any big columnstore tuning projects I’ve done regular query tuning and I’ve you know figured out when people would be better off with columnstore and I’ve helped them you know move some stuff into there but I just haven’t run into like like the bevy of problems that you know you would if you’re regularly working with columns or with ETL stuff like that so we’re gonna not do columnstore if we’re gonna do columnstore I am going to stick generally to rowstore indexes, because that’s where my knowledge is.
I just don’t know columnstore well enough to stand there and answer questions about it. So, if I’m going to do server tuning, this is an interesting one.
This is an interesting one. Because it feels to me like the more you talk about waitstats, the less people want to use waitstats.
Waitstats have very real flaws in them. Particularly the way SQL Server logs them, where they’re just sort of aggregated since the server started up.
You can have very, very unreliable data there. But they’re somewhat helpful to identify big bottlenecks. Big problems.
Big problems. All right? If you see just like crazy waits on something, it can be helpful there. But you can miss a lot of the picture, right?
If you have a bursty workload that’s only busy some parts of the day, and does really nothing else for hours at a time, waitstats become less useful.
So, if you’re going to teach people about waitstats, you have to give them a way to gather waitstats in a way that makes them more useful for them. Not many people have that constant 24-7 pounding workload on a server.
And even if they do, that workload isn’t typically all user-facing, right? Even those servers will have some sort of night maintenance, you know, code rollout, change management, you know, whatever they’re doing, taking backups, running CheckDB, index maintenance, stats maintenance, whatever it is they’re doing, there’s typically some maintenance window for that.
But very few people have the, like, 24-7 need to just constantly be running queries. So, or user-facing queries, I should say. So, if you’re going to do waitstats, you really do need to give people a way to gather waitstats in a way that they can make sense of their workload.
There’s all sorts of stuff about waitstats. Sure, you can hit F5 and you can get sums and averages and percentages, but a lot of the stuff that you can just hit F5 on, you also don’t get, like, how long the server has been up, so you can kind of compare things to that.
Because a really important metric when you’re looking at waitstats is, like, compared to what? Like, the famous economist question. Like, compared to what? Like, how are you doing today?
Compared to what? I don’t know. Like, compared to someone who is staring at an IV in the hospital, probably pretty good. Compared to someone who’s sitting on a yacht in, like, the south of, like, off the south of Europe, you know, doing something fantastic with themselves, probably not as good, right?
Compared to what? So if you’re going to give someone stuff about waitstats, you need to give them a lot, like, a lot to, like, make sure that they know what to compare it to, how to gather stuff, how to read stuff, what waitstats mean, what waitstats are problems.
And that’s a tough gig. Because most of the time, when you start writing stuff like this, you start thinking, well, maybe you should just get a monitoring tool. And you think, maybe I should write a monitoring tool.
And you think, oh, that sounds hard. I should just go work for a monitoring tool. And you think, oh, I don’t really want a real job. And so I tend to stay away from this stuff now.
The thing about hardware, too, is that with a lot of workloads being virtualized or in the cloud, stuff that you can say with confidence about physical hardware changes quite drastically when it comes to virtualized hardware.
You know, there’s stuff you could say about, like, SOS scheduler yield, CX packet, you know, page IO latch, all that stuff. That, you know, on bare metal hardware, you would be right.
Virtual hardware, you would have a lot more to dig into. So if you’re going to start talking about hardware, you kind of have to know a lot about not just, like, the CPU and the memory, but now you have to start understanding virtualization layers, how VMs talk to the virtualization layer, how things might look if you have a lot of VMs on, like, a lot of VM guests on one host, all sort of making crazy requests, asking for resources in different ways.
Like, maybe you have an over… Like, what used to be a concept like, oh, your CPU, like, your SQL Server hardware is just underpowered. Could be, well, you gave SQL to the SQL Server VM enough hardware. The problem is it has to share that hardware with, like, 30 other SQL Server VMs on the same host.
Because everyone who virtualizes is a cheapskate, and they license enterprise at the host level, and they’re like, cool, so everything gets enterprise and goes here. And that’s, like, the new kids on the block version of, like, just stacking a bunch of SQL instances on the same server.
So, like, if you’re going to do hardware, you really have to understand, like, virtualization. You really have to understand VMware, all the little intricacies of things that can go on in there with, like, settings and, you know, how you, like, can, like, have VMs allocated and, like, where they go and, you know, like, crazy stuff, too.
Like, just, like, how, like, what a big difference, like, para-virtual SCSI connections can make over other things. And then, like, you have to, like, you know, like, the stuff you have to get into with hardware can be pretty challenging.
Forget all the stuff you might need to know about the cloud and cloud instances. It’ll be wrong in three months. So, hardware is kind of getting turning into a tougher and tougher subject to teach.
I used to really like talking about hardware because there was, like, some, like, cool stuff that you could show people, like, like if you have a bunch of queries run and run out of worker threads, or when you hit resource semaphore because you run out of memory to grant out the queries, or when, like, your server just plumb doesn’t have enough memory and you spend most of your time waiting on page I.O. latch or whatever it is.
But, you know, like, and, like, well, I still enjoy that stuff from teaching people about it from a performance perspective. You know, you get to the point where you’re like, okay, so, like, is the fix for that in the cloud, like, to just move to a bigger instance size?
Because, like, there’s no longer that challenge of, like, oh, we’re going to order the memory, shut the server down, install the memory, turn the server back on, wait three days for post to test the memory, stuff like that.
It’s just, like, it becomes a lot easier to just say, well, flip a button and see if it goes away, or flip a button and see if it minimizes some, right? So, like, when I teach people about those wait stats now, it has to be in the context of, well, how can we tune the query or the queries or the indexes in order to make better use of the hardware so that we are not pounding SQL Server out the way that we used to?
And then, you know, settings, golly and gosh, I can’t imagine someone sitting through a full day of how to set maxed op and cost threshold. So, the server tuning stuff, I kind of get away from a little bit.
So, within performance, we can talk about, you know, index tuning, and we can talk mostly about rowstore indexes. So, that’s one possibility.
There’s also query tuning, right? And query tuning, but, you know, query tuning should go hand in hand with index tuning. I would say there are certain query writing patterns that should certainly be taught and addressed, but if we’re going to talk about how to tune a query, you can’t just leave out how to tune indexes.
You can’t just leave out how to identify if your index key columns are in the wrong order, if you should fix a key lookup, if you should fix a sort, if you’re getting the right type of join or the wrong type of join because of the way your indexes are designed.
There’s just, like, so much that you need to think about and figure out when it comes to that stuff. The query and index tuning kind of go hand in hand.
There’s a couple of things over in chat. I’ve been watching your video from 11 days ago for the past 30 minutes and realized I wasn’t live. Ha ha ha! Well, now you’ll be able to tell because I’m much hairier. We have OLAP queries in the weekend which screw up all the wait stats.
See, that’s another thing. You can, like, that’s another terrible thing about wait stats is, like, you can’t filter them out based on when they happen. And unless you’re on SQL Server 2017 and you have Query Store turned on, it’s very difficult to figure out which queries are responsible for which waits.
Like, what happened to you? Like, what caused you? Oh, like, that makes a big difference too, right? Like, if you had some, like, big OLAP query come along and cause a bunch of, like, thread pool to restore a semaphore waits, you might look at wait stats overall for the server and be like, holy smokes!
What happened to you? But, then, like, you look at, like, the regular user workload and you’re like, none of that’s happening then. So it’s just like, come on, Microsoft, give me something.
Give me something. Throw me some bone. So within performance, query tuning and index tuning should go hand in hand.
And, I think it’s very important with indexes, specifically, to not, to make sure that people understand you’re not going to sit there and teach them what a B-tree is because that doesn’t help them.
You don’t want to, like, what’s on an index? Like, here’s an hour of DBCC page demos. Like, it’s just, stuff has to be practical too.
Right? Like, if you just sit there and do a full day of, like, this crazy trick that no one’s ever, no one’s going to walk out of there and ever see in their life, they’re just going to be befuddled as to what happened.
Like, what did I do? What did I just learn? If I see this, this one very specific set of circumstances that this consultant up on stage saw once in his 25 years of working with computers, well, eh.
But it’s so great to teach people about IO complexity. Yeah, when they’re really interested in IO complexity and they’re geared up to learn about IO complexity, that’s a great thing.
If you have a bunch of accidental DBAs in a room who are just like, do I have IO complexity? Is my IO complex? Do they really need to learn about how complex IO is or do they need to learn how to find their problems and fix them?
Right? It’s like, if you have people who are like, yeah, teach me about, like I’m a sanded man, teach me about the IO complexity or like, you know, like it’s just someone who has an interest in it because they’re down with it, then like what?
Yeah, great. IO complexity in general, not going to solve a lot of problems for a lot of people. It’s just not. A lot of people are not going to look at IO complexity and be like, oh, now I know why that query is slow.
So query and index tuning should go hand in hand, but it has to be practical things they can use when they leave.
Right? We can’t, we can’t just teach crazy stuff all day long. So we have query and index tuning. We have, we have that.
But there’s a lot of query and index tuning stuff out there. Do we want to specify it? Do we want to say something like for OLTP, for OLAP?
Like, do we want to specialize? I don’t know. I don’t know. Within query performance there’s other stuff too. Right? Like, if we’re going to query and index tune, what if it’s not just the query?
What if there’s blocking? Do we need, do we need to cover blocking as, do we consider blocking to be a performance problem?
Is blocking a performance problem or a concurrency problem? That’s something else we need to figure out. Right? Like, like, like, where do we want to go with this thing?
Like, which areas do we want to cover? Within performance there’s a ton of different things that you can look at. So, what do you think? What do you think out there?
There are, there are, there are people who have been in here listening to me. Listening to me talk and listening to me talk about, like, you know, like performance tuning subjects.
And not just today, like, you know, over the past, like, couple weeks or so that I’ve, I’ve been streaming. What, what things do you find yourself wanting to know more about?
What, what things do you find yourself having trouble with? He says, one thing I have found a gap in with a lot of things I have attended, it’s really looking at queries the size of the ones you see in real life. Presenter needs to get to the point across, but it’s a different world.
Yep. So, and I run into that too. So, here’s the thing. If, if I may, if I spent the time to make every single query big and complicated, and I, and I showed it to you, there’s a lot that you would get distracted by.
You would be looking at the query, trying to figure out what it does. You’d be looking for mistakes. You’d be trying to find this out of the other thing. What presenters need to do is come up with the simplest way to describe a concept, to describe an anti-pattern to look for, to give you something to look for in those big queries and in those big plans that you can single out and try to fix.
It’s not always like, you know, the most germane thing in the world to try to, you know, write a gigantic query that has this one problem in it and focus in on that.
Sometimes you have to say, look, here’s the problem you’ll see. It could be part, it could be a small part of a big picture, but here’s the small problem and here’s how to fix it. Let’s see.
Coyote McD says, does a pre-con have to be super practical? What about a pre-con for nerds who just want to learn how things work? Sure, but that’s a very, very limited audience. If I’m going to do a pre-con, I want to appeal to a wide, to a wide range of people who need, who need help.
Right? The nerds who want to know how things work are, I mean, what? one in, like, like the people who are really, like, ready for that, interested in that, and need that, there’s a much, much smaller crowd than I would aim for.
I want to be able to teach, I want to be able to teach as many people at one time as I can. So for me, it does have to be practical. And, you know, it’s funny, it’s funny the way you worded that because a pre, like a pre-con for nerds who want to learn how things work, sounds pretty practical to me.
But I get what, I think I see what you’re getting at with, like, you want the deep internal stuff. You want, you want that next level in that isn’t common knowledge. And like I was saying before, to get that sort of thing, that’s where you need, that’s where, I mean, say, like, that is where you need a Bob Ward type person who has that, who has access to the, like, who can see the source code, who can see the private symbols, who is, you know, whip crack with window bug, and can, who can, like, give that deeper internal’s knowledge and, you know, do pretty well with it because people would want to learn that from Bob.
There are very few people who, A, people would want to learn that from and there are very few people who I think are ready to and who would fully grasp whatever they’re teaching. So, what you said is actually a very practical thing.
A person who just wants to learn how things work. Right? They just want to, just want to know how to solve a problem. Learning how something works so they can fix it.
Lee says, I guess it’s a fine line between hobby and work. Hobbyists want to get deeper, the next thing I want, yeah, exactly. And you have to, you have to be able to respect both crowds. Right?
And there’s also, there’s also a funny question there, is it’s, does the hobbyist show up to a day-long pre-con to learn? Does a hobbyist get, you know, their work to pay for a pre-con to, like, for them to attend, show up, hang out, learn stuff for a full day?
Does the hobbyist show up for that full day training? The hobbyist might show up to a conference to get some time off work, to get some free travel, show up to a couple few sessions where the title attracts them, but I don’t know if the hobbyist is going for that full day.
Getting the hobbyist into the, if you can, if you find a way to get the hobbyist into a full day, you have cracked a very, very unique market. That is, that is a very, that is a tough, tough nut to crack.
Mostly you, mostly you need the people who, you know, either, you know, they are the hobbyist, or rather, like, yeah, they’re the hobbyist who wants more, who, like, you know, craves more, or, you know, they might be, you know, the people who, you know, who just want to, like, learn how to solve a problem, but their boss is just sick of them being that person.
They’re like, look, we have real problems that you want to solve. Here’s an extra 400 bucks. You’re going to go to this day. You’re going to learn some stuff. Maybe it’ll help. Right? Look at all the crowd that you have.
Yeah, you know, sometimes it’s better than others. Sometimes it’s better than others. This isn’t, this isn’t a particularly riveting SQL Server topic. So I don’t know.
I didn’t expect a big crowd today. But I’m happy for anyone who shows up ever at all. But, you know, if I’m doing something where I’m actually talking about, like, real SQL Server stuff, then you usually have a few more people.
And this is, this is like a weird soft skill one. So I don’t expect a lot of people in here who aren’t just, like, drunk, bored, in Europe after work. Something like that.
So, you know, I had different expectations for this one. Different expectations. But, we’re having fun anyway.
And we’re going to write this thing anyway. So, we figured out a few things. We’re obviously going to talk about performance. But where do we want to go with performance? I do a lot of performance tuning training that ends up hitting pretty advanced stuff.
And I think, well, I think two things at the same time. Sometimes it’s difficult. But I think two things at the same time.
One is that there is probably a market out there for people who are beginners who want to start being advanced.
And then there are also people who think they’re way more advanced than they are. They’re the people who always have a what about but what if but it’s never about anything particularly pertinent or anything that would really work.
So, there’s two crowds out there. If I’m going to focus on a crowd right now as far as material goes, I think I want this crowd a little bit.
I want people who realize that they don’t know what they don’t know. who are having real troubles performance tuning.
They might read blogs. They might, you know, watch videos. They might, you know, they might go to user groups and stuff. But they’re just not making that jump.
They’re not making the right connection to figure, to like get themselves on the path to advanced. So, I want to start, I want to start, I think I would like to, for this one, attract people who need to go, maybe not from like 200 or 300 to 400, but maybe from like 100 or 200 to 300.
I don’t want to have to get crazy deep into stuff. I just want to give people, I want to give people enough so that when they start looking at code and indexes and query plans, they can start like, like thinking for themselves, learning for themselves and fixing problems themselves.
He says, I don’t think he wants to see us. That’s not true at all. I would love to see faces. Like, maybe not in this format, but if, if, if, well, we were live and in person and I was looking out at you while I was doing this, I would be very, very happy to see, see those faces.
So, it’s not like I don’t want to see you. I just don’t think this is a great format to see you in. Like, if I had like a Brady Bunch style, like lineup of faces off to the side, I don’t, I don’t know that that would be helpful.
The CPL puts you and it’s like, connection timing out and whatnot. So, what could we call, what could we call a pre-con?
What is our title going to be? Where we try to, we try to attract people who need to go from like one, two hundred to three hundred.
Like, what’s some good stuff in there? What’s some good stuff that we could call it? I’ll give, I’ll give y’all some time to think. I’m going to, I’m going to give it a few. Let’s see. Maybe, uh, the beginner’s guide to advanced performance tuning.
That might be a good one. Uh, man, you’re failing me miserably. no and no. Uh, damn, Arthur.
Putting me up, putting me on blast like that. Put me on blast. Video freeze for anyone else. I don’t know, but I’ll stop and wait for someone else to answer.
Maybe try refreshing, Arthur. Did the audio also freeze or can you still hear me? Uh-oh, Arthur.
Might want to check that internet, pal. So maybe the beginner’s guide, why are you blue? Oh, because you’re, no, screw.
I’m not changing that. Beginner’s guide to advanced performance tuning that might run. starting SQL, um, uh, let’s see here.
Uh, let’s see. Uh, what would be a good way of starting? Maybe starting isn’t that great of a, maybe that isn’t. Had to reboot.
Like your whole computer? I’ve been playing too many video games, man. It’s got, you know, all that precious VRAM. That precious VRAM is sucked up like video games.
SQL Server, taking the next step from beginning to advance. Okay, that’s got something to it. All right. I don’t know if I necessarily want SQL Server in front of that.
Maybe I could do, you know what, maybe we could, we could combine forces a little bit here. Maybe we could call it, oh, and look at that pasting with full formatting. So I’m going to give you, I’m going to give you probably the most invaluable piece of advice that I have.
When you have text that is formatted in a certain way, like when I paste that text there, it comes up with a background and different fonts and everything. If you click on the Windows icon, oh, it’s not in here.
If you click on the Search icon and you paste something in there and then you copy and paste it out, you get rid of all the formatting. But we’re going to have to fix these words a little bit.
Next. Step. From. Beginner. To. Advanced.
There we go. Using Notepad for that. See, there you go. There’s all, see, there’s all sorts of fun tricks out there. I don’t trust Word.
When he says paste values in Word, I don’t trust that. Every time I paste values in Word, you know what happens? It changes fonts on me. Like, I’ll hit enter a couple times and a font will switch to something else.
It’s never a good experience. I don’t, unless I can, unless I have the raw values from somewhere else, I just don’t trust Word to do anything right. Like, like, you ever try to, like, get Word to, like, like, go, like, you scroll down and then you’re like, oh, I want to put something here and you’re like, hello, and then you, like, write some stuff up here and you hit this and then, like, this just jumps down and then you, like, spend some time trying to get this to work and it’s just like, it just jumps up and down in, like, weird increments on you.
Like, I just don’t trust Word to do anything right. I just don’t. I just don’t ever do it. So, let’s call this title. So, we have two now.
We have Beginner’s Guide to Advancing and we have this one. Oops, come on. Come on. All right.
So, what else do we have in here? What else could we do? We’ll be, I’ll give, I’ll give, I’ll give one more lucky, lucky person a chance.
Come up with a title. Advanced Performance Tuning, Starting SQL, Taking the Next Step from Beginner to Advanced. see, the problem with this one is, we need, we need, we need, we need people to know, uh, that it’s about performance tuning.
If we don’t know that it’s about performance tuning, people will say, Beginner to Advanced, what? What you should know about performance tuning?
I don’t know about that one. It needs to be, it needs to be more, be more action packed. Right? It needs to be more action packed. It’s a bit nebulous. We need, we need something that, we need something that signifies someone is, someone is, someone is starting from the beginner area.
Right? Someone is on the path to advance, but they haven’t quite made it there yet. Right? So, we’ll, we’ll, we’ll come back to that and we’ll think. So we have the title. So we have the titles down.
So, who? There’s performance tuning, find where it hurts and fix it. Woo! I, I like it and I would use that for something else, but you know what?
But I, I, I, but I, what I need to, or what I would need to do with that is I would need to, I would need to figure out how to get, oops, I would need to figure out how to get it to also include the fact that, you know, this is a beginner level class, that this is not going to be, you know, three and like, you know, 400 level stuff.
stuff, and I would need, so I would need like that, that sort of thing in it. We’ll get some, get some capitalization in here.
So, who, who are we talking to? In this case, we don’t necessarily want, uh, DBAs, because, uh, oftentimes, so actually, you know what, let’s not do it by title.
What we don’t want, this is, not about, infrastructure, issues, like, backups, hardware, HADR.
Right? So, accidental DBAs is, is okay. I’m okay from, I’m okay with, uh, accidental DBAs. I’m also okay with software developers. But what I don’t want is, infrastructure DBAs.
I don’t want a junior DBA, who, is consumed by, you know, taking backups, restores, check DB. Not because I don’t want them to learn about performance tuning, but that’s just not where they’re focused right now.
That’s not where, like, that’s not what they’re showing up to a class to learn. I don’t want people to think, I’m going to teach you how to take backups faster. Right? And I want people to learn, like, like, you know, how to, you know, how to, you know, get your availability group to fail over faster.
So, like, I don’t want the infrastructure DBA. I want the, I want DBAs who are doing performance tuning. So, let’s, let’s, let’s focus in a little bit about that, on that.
You’ve, then, performance tuning SQL Server, for, for, let’s see, for a year or two. So, a year or two is probably good.
Um, because that would at least get people in the door who have, you know, looked at a query, query plan, have looked at indexes, have probably fixed some problems on their own, and who are, who have probably gotten to the point where they’ve hit a problem where they had to go read something.
Right? So, like, they’re not totally unfamiliar with things. They just might not have the depth of knowledge on certain things that gets them to the advanced part. So, did the advanced part.
Uh, so let’s say you’ve been, you’ve been performance, like, uh, we want people, let’s, let’s not write the abstract in the who. People who have been performance tuning for one to two years, probably read blogs, watch videos, and are familiar enough with SSMS.
Oops. Uh, query plan, oops. Someday I’ll get it right. Query plans, uh, indexes to not need, um, let’s see.
Well, let’s figure out a different way to say it. Um, let’s say to not know, like, so like, like, what I want to identify is, you know, people who, um, um, people who know what these things are and where to find them.
Like, I don’t want someone who’s just like, but what script should I run to look at my indexes? Like, I want someone who’s a little bit more engaged in that. Uh, uh, and how to find them.
That’s good enough wording for now. So, let’s see here. Lee says, it’s the glue that links different concepts together to provide a solution. That’s the hard part.
Yeah. So, you know, um, whenever you’re tuning a query, you know, there, you know, there could be any number of things that look pathologically wrong with it. You know, it could be something in the query plan with the parameters, the way the query is written, but getting like to the end result of what was actually slowing it down, it could have only been one or two of like the five or six things that you spotted.
Right. Like it could be, and it could be like, you were just like, Oh, Oh, it could be that. Oh, it could be that. But then like, you know, you go hit a five, you look at the actual plan. You’re like, Oh, that’s what it, that’s that one thing.
It wasn’t the five or six other things that will probably go wrong next. It’s that one thing that was wrong now. So, so we want people who have not been doing this for a very long time, but who at least have the wherewithal to know some stuff up front.
Right. Like, I don’t want anyone to ask me like, how do I get an execution plan? You know, how is query formed? Uh, what’s the difference between a clustered and a nonclustered index? I want people who have like some meat on their bones, but I don’t want people who are like up on stage flexing.
Right. I want like some people who have just like kind of got a little bit out of it. So, and I want to know what’s their pain. Do they need to, are they, do they have a tough time reading query plans?
Um, understanding what’s wrong with way, uh, query, ha ha ha ha ha queries written.
Oh man. I buffed that one, huh? designing indexes. Um, like what, what, what, what pain points do they have?
What are they, what are they currently just struggling with? So think back to when you were like a year or two into performance tuning, what kind of stuff, um, what kind of stuff were you just befuddled by?
Okay. Identifying the real body in the bottleneck. Okay. Uh, I find it difficult to find out what I should expect as performances for a query. Parallel.
Ha ha ha ha. Coyote McD, a lot of people are still befuddled, flummoxed, and perplexed by a parallelism as we have recently learned. It’ll be, actually, no, let’s go find it.
Let’s go see where things are at. So, um, my dear friend, Paul White has, has a Twitter poll. Has a Twitter poll.
And if I go search through here a little bit, I will find the Twitter poll. It’s in here somewhere.
There we go. So if you’re on Twitter, I highly suggest that in the next three hours or so, you go and answer this poll.
The poll is a, it’s a good question. Some replies were hidden by the tweet. Ha ha ha.
Good for you. So the answer, or rather the question posed by Mr. White, for scientific purposes. A row mode parallel query runs at max.4 on a SQL Server, 2005 to 2019 instance with eight total cores.
What is the maximum number of threads that can be running concurrently for the query? Notice we’re not saying schedulers or cores. We’re not saying CPU, the maximum number of threads that can be running concurrently for the query.
Oh, Michael, I’m not going to say if you’re, if you’re right, wrong, or anywhere in between.
I am going to say that you are a very, very smart person. And that if more people listen to you, more people would, would be smarter too. He says resource usage versus query speed.
I know that you didn’t say cost, but, you know, resource usage is interesting because what if, so here’s, here’s an example.
What if you have a query that uses one second of CPU, uh, and runs serially? So that, that every time that query runs, it takes one second.
Now let’s say you have, you tune that query and it goes parallel. It now runs at dot four. So it now uses four seconds of CPU, but it runs for 250 milliseconds.
In this case, we had perfect parallelism. Everything teamed up. Gene Omdahl stretched out in his grave, put his arms up and screamed, we did it.
I’m not sure if Gene Omdahl is dead. It just had a good visual to me there. Zombie Gene Omdahl, like we did it. I’m sorry if you’re not dead, Gene.
I apologize. So you used four times the amount of CPU to get the query to be four times as fast. You used, you used more resources, but the end user gets the result faster.
Did you tune the query or not? Did you do better? Is the query better? Can you, is there, is there a, is there a serial plan for that query? That would be 250 milliseconds.
This is, these, see, when it comes to resource usage, it’s a tough thing to gauge whether resource usage has made a query better or worse. The same thing goes for reads too.
Same thing goes for reads. Same thing goes for reads. You can, you can, I have tuned queries, I swear to you, where I have ended up doing more reads, but the query has been much, much faster.
It’s a, it’s a real thing. Kalil says, depends on the query and how many branch. Well, go vote. You have, the link is in chat. You can go vote.
You can, you can tell Paul what you think about his question. So that’s interesting though.
So we have some things, some things in here we have to add. So we had some stuff. Identifying bottlenecks.
Parallelism. Resource usage. By queries. Let’s see.
So what else? What other, what other pain points might people be struggling with in their first year or two of query tuning? Maybe, well, we have designing indexes.
Let’s just, let’s add what’s a good, what’s a good index. Making CTE faster. That’s a good one. Parameterization.
Arthur. Holy smokes. Man, is that, is that, so, I’m going to ask you a tough question because you’re a smart person, Arthur.
How would you teach beginner people about parameterization? Like, like what would be your end goal? Michael says when there are too many indexes.
Too many indexes. That’s a good one too. Because too many indexes can really, really cause things to barf up in the wrong way. And they really cause things to go down the wrong pipe.
So having too many indexes is probably a good thing to identify. I’d be with you on that. I’d be with you on that.
Three most common. Ad hoc, prepared, and procs. Here we go. Prepared and procs.
And so, when you teach them about parameterization, do you also go into forced parameterization? Do you go into, oops, that didn’t, that didn’t go well.
Let’s, let’s scoot that over a second. Parameter snapping, things like that. Maybe dynamic SQL. Ooh la la.
I love the sound. That. So, what about dynamic SQL? What about dynamic SQL? Would you, would you, would you want, you, you have wanted your past just starting out with SQL Server stuff to learn?
Would it be, you know, staying safe by, staying safe, no SQL injection, when to use it.
So, I guess we, we already have, oh, staying safe, when to use it.
maybe, maybe, maybe, maybe, maybe, maybe, maybe that stuff, that’s ungood.
So, it’s, you know, it’s funny how much the, the parameterization thing, and the dynamic SQL thing, dynamic SQL thing, come into, come into play together.
Right? So, let’s actually make this, a topic up here. Why didn’t you do what I said to do? You’re very mean to me, Microsoft Word.
So, maybe we’ll, take designing indexes out of there. And we’ll keep, what’s a good index, when there are too many, well, you know it’s about indexes now, so we don’t have to keep that in there.
So, let’s see here. Let’s call this, query, anti, patterns, and what, and stuff that might fall into, this.
So, it’s interesting you say deadlocksly, because, are deadlocks a performance problem? Or, are deadlocks a logic problem? Are deadlocks, well, see, and we talked about this earlier.
So, locking and blocking, are they performance issues, or are they concurrency issues? If I wanted to teach someone, if I wanted to do a day of concurrency, I would be all game, to teach people about locking, blocking, and deadlocks.
I would be all, I would be all about that. But, I’d have a tough time, covering, the amount of ground, that I’d want to cover, with performance tuning, and also getting to locking, and deadlocks.
When it all goes crazy, with triggers and firing keys. So, you know, and that’s, that’s a funny one too. Because with foreign keys, in general, not always, but in general, as long as, you have, pretty good, indexes, to support your foreign keys, then you’re in good shape.
The trouble with triggers, the trouble with triggers, is that, people, are going to do, dumb things, inside of triggers, all the time.
If I, if I were to, try to tell you about, or try to show you, the triggers I’ve seen in my life, where people have written, applications, inside of triggers, that run to account, for like, a decade of business logic, when a single row, gets inserted, not only would you not believe me, but we’d have a hard time, like, tuning that trigger.
People do some real bad things. CLR triggers. No, I don’t, I don’t do CLR much, because I’m not smart enough, to use C sharp.
So, CLR, CLR, well, it seems like a fine thing, and I’ve, I’ve bought books on C sharp, I have them, I’ve started to read them, and you know what always happens? I start typing, and, I, I, I fall over.
I fall over. I just, you know what it is, I think it is, is that I have not had, I have not had, a good reason, to, or rather, I have not had a good application, for CLR in SQL Server, at least one that didn’t already, have a solution to it.
So, like, recently, my dear friend Josh, helped me, write a, a, a CLR utility, to take, all the numbers, out of a string, or all the string, all those, like, alphanumerics, out of a string, or something like that, and like, he was very smart, and good about that, he did it very quickly.
If I were to try to do that, I would have beefed on that thing, for days, probably come up with something, that, like, didn’t even compile, maybe if it compiled, the results would be wrong, I just haven’t had, a good application for CLR.
Is it CLR, so, anything is a bad idea, in, in the right amount, right? I don’t think, see, there’s anything necessarily wrong, with CLR triggers, I don’t necessarily think, there’s anything wrong, with some business logic, and triggers, a lot of what, a lot of what goes bad, in the trigger, is going to be, what goes bad, inside of, other user queries, someone’s going to write, a bad query, someone’s not going to, understand how to, index to make the trigger, go as fast as possible, things like that, like, people are, people are going to, like, you know, write cursors, inside triggers, use triggers, to call store procedures, triggers, one of my very first, consulting gigs, was, working with a client, who had, terrible, terrible problems, every time they, inserted to a table, really quickly, I, like, we, like, I was able to spot it, because I was running, SP who is active, every time they, inserted a row, a trigger would run, that would call a report, that would generate, three different reports, on every single, like, different, on, like, the same table, three different ways, I was able to spot that quickly, but no one else, looking at it, everyone else, was just like, boom, so, like, if there’s a, if there’s, like, a moral to this, or it’s like, sure, don’t put store procedures, that call three different reports, on a table, inside of a trigger, every time you insert a row, is that a good performance tuning topic, I don’t know, so, I think, you could take a lot, of the performance tuning stuff, and apply it, to the bad idea stuff, that people put inside of triggers, I just don’t see, how, like, targeting triggers, is going to really help, Aaron Bertrand, has a really good talk, I think it was at SQL bits, Aaron Bertrand, SQL bits, triggers, where Aaron Bertrand, talks about, some ways to write, more effective triggers, I’m going to close that window, before the video starts playing, but I’m going to stick the link, into chat for you there, so, Aaron Bertrand, has a good, has a good session, on writing, more effective triggers, but a lot of the stuff, that’s bad, that people do, inside of triggers, is bad stuff, that people do everywhere, you know, they’ll put the, the entire content, of the trigger, inside of a transaction, they’ll, you know, you know, call cursors, and loops, and iterate over things, and you know, and like, more understand, how to tune the queries, or the indexes, that go inside the trigger, so I’m like, I’m like, I just don’t think triggers, are like that appealing, of a subject overall, I would rather have people, be able to, learn as much as they can, about query tuning, and be able to apply it, to things that they see, inside of those triggers, to make those triggers, go faster, so when people want to apply, 10 years of business logic, it happens as quickly, as possible, so let’s think about, some other stuff, so we have, stuff about query plans, we have stuff about, query anti-patterns, some stuff that we can put in there, off the top of our heads, it might be, table variables, might be, functions, might be, sargability, might be, implicit, conversion, what are some other things, that we might see, as a query anti-pattern, spiritualizes, that’s something you can put in a class, the good, the bad, and the ugly, I don’t know what you mean, I don’t know what you mean by that, clarify, and I will answer, CTE, yes, well CTE, we know they’re not magic, with all that, CTE, let’s see, what are some other things, that we could stick in there, wait stats, so we, I talked about, why I stay away from wait stats, earlier, you showed up a little late, I’m not going to, talk about it all again, but wait stats, are just not that interesting, to me when it comes to, tuning a single query, wait stats are more of a, server tuning thing, Bosco says, nesting store procedures, again, if you nest, nest store procedures, that’s totally fine, there are actually, very very valid reasons, to nest store procedures, again, what I’d rather cover, is, you know, rather than like, something like that, I’d rather cover, making the code, inside of those, nested store procedures, as fast as possible, so that people can, tune those store procedures, to go so fast, that no one cares, that they’re nested, right, like I see, like I see what you’re getting at, but you know, nesting store procedures, to me, is a good choice sometimes, I’ve actually solved, a lot of problems with that, let’s see, Spare Deli says, I guess you do that already, similar to what you discussed, about triggers, examples of the, this is ugly, this is the bad, and then the good, yeah, so, sure, but you know, again, the triggers are going to be, T-SQL anyway, right, the triggers are going to have, queries in them, the triggers are going to, need to be tuned, in certain ways, so it doesn’t matter, that the code isn’t a trigger, what matters is people, being able to like, get the query plan, look at the text of the trigger, see what indexes were involved, and then start to solve problems, from there, but learning by example, is huge to me, right, because if, like learning by example, is I think the best way, to learn, right, if like, like if, if it’s just a bunch of theory, then, people, people, people leave kind of empty handed, if it’s just all theory, people don’t have concrete examples, of, when things are bad, how to know that they’re bad, how to fix them, stuff like that, then like, what do people really walk away with, more guesses, right, more, more things that they’re not sure about, right, if you give them, if you give them a, a steps, to solve a problem, that’s a, that’s a powerful, powerful thing, teach them how to fish, as they say, right, teach them how to fish, all right, so let’s see, what are some other query, anti-patterns, that we can maybe put in there, let’s see, curious, curious, curious what else, we could maybe, put inside of there, oh, we know what’s a good one, I can’t believe I didn’t think about that, local variables, okay, so I think that’s a pretty good list, so when we say, identifying bottlenecks, maybe we should put, identifying bottlenecks, up under reading query plans, because that’s probably, where we’re going to identify, the bottleneck, that’s like the most reasonable place, to identify a bottleneck, is looking in the query plan, if you just look at a query, it might be pretty hard, to figure out, what in the query, is causing the bottleneck, one of the biggest, one of my biggest pet peeves, if I’m looking at any Q&A site, is someone posts, just the text of a query, and they’re like, I need to tune this, okay, what would you like to do with it, what’s slow about it, right, like, so like, if we’re going to identify a bottleneck, we’re probably going to need, some deeper information, that deeper information, is probably going to be, stuff that we find, in the query plan, so reading query plans, let’s take the question mark, out of there, we’re going to be identifying, bottlenecks, let’s see, go, it says, put UI logic in query, ouch, that is no good, so one crazy thing, that I saw recently, was someone, had, a table, with, a, a binary column in it, and that binary column, and that binary column, could be converted to XML, and the XML contained, the entirety, of each individual user’s, user settings, application logic, things that they had customized, about their application, and, every time someone logged in, they had to go through this table, convert, their row, from, var binary, to XML, and then, search the XML, for different things, it was, one of the ugliest things, I’ve ever seen in my life, it was like, like, just, like, what were those, what happened with your developers, that they did that, the worst part is, that they, they, they fancied themselves, to be X query experts, but they were casting their XML, to Envarkar, and just searching things, as it, as like a SQL blob, and I was just like, bless your little hearts, bless your little hearts, so, let’s see, reading query plans, identifying bottlenecks, query anti-patterns, like table variables, functions, archibility, and plus diversity, local variables, parallelism, alright, so, Coyote McD, if you are still here, what about parallelism, would you want your one, or two year, into SQL self, to learn?
Let’s see, good tables, and normalization, so, good tables, and normalization, is certainly, a, a performance topic, it’s very hard, to get people, to make changes, to table structure, there are, there have been, many, many, many, many, many times, in my consulting career, when I’ve tried, to tell people, well, look, it’s going to be, really hard for you, to, to get the kind of performance, you want, unless you, normalize stuff out, like this one, big wide table, you have, is giving you, a really hard time, within that table, we have a bunch of, groups of columns, that would actually, they’re actually sort of, identify, themselves, as tables, like you might have, columns with a bunch, of prefixes, like client name, client address, client phone, and you might have, a bunch of, of, columns that identify, themselves, as, like things, that just sort of, belong together, like phone one, phone two, phone three, phone four, phone five, things like that, like you might have, those sort of, self identifying tables, with inside, like inside of your tables, and that’s, that’s an okay thing, to tell people about, but that’s a hard thing, to get people to change, it might help them, the next time, they start a project, from scratch, but it’s really hard, for them to get, to change their application, or change their like, table design, and then change their application, to work with the table design, even if you give them hints, like, well you know, you could change this, and then use like a view, that has like the join in it, to sort of do things, it’s, it’s not, it’s not an easy undertaking, there are a lot of gutches in there, so good, good tables, and normalization, sure, but I have about, five minutes worth of things, to say about that, and you just heard it, let’s see, what’s going on here, why repartition streams in there, and why ordering is bad, with parallelism, so coyote mcd, that’s stuff that, you would want yourself to know, after one or two years, of learning about, SQL Server performance, that’s, that’s a tough couple things, for people, like, like if I started talking about, like, exchange spills, or parallel, like, interquery parallel deadlocks, to people who have been working, with SQL Server, for a year or two, I think that, I don’t think that, they would be able to make, heads or tails of it, it would be like, a scare quote, right, it would be like, one of those old movies, about pot, where someone jumps out a window, like, and, and, and the other thing is, if I tell them about that, you know what they’re going to do, they’re going to hate parallelism, forever, they’re not going to trust parallel, query, every time they see a parallel query, they’re going to be like, oh god, is it doing that thing, that guy told me about that one time, is it spilling, what’s going on, maybe I was a little advanced, at one to two years, yeah, yeah, so, you’re, you’re a smart, you’re a smart fella, you’ve, and you’ve, and you’ve hung around, smart fellas, and you’ve invested the amount of time, that would, that would certainly get you, um, past where most people would be, um, uh, but, I think, it would be tough, tough to sell that to, someone starting up, so, Khalil Jamil says compression, sure, so, what about compression, we have row compression, and we have page compression, what kind of stuff, would you want to learn about it, so, let’s see, uh, let’s take these out for now, well, you know, let’s move these a little bit, because we have some questions about these, I’m going to figure these things out, let’s go, X, U, and, and we’ll add compression to the list, and so, what about compression would be interesting to you, for anyone else, for anyone else out there, like, what sort of, if you have any ideas about, like, what your one to two year into SQL Server self, would be into, about parallelism, resource usage by queries, or compression, throw it into chat, we can, we can try to figure this out together, try to figure out what kind of stuff, you might be interested in, this is all stuff I’m game to go into, it’s just, I want to make sure that I’m, I’m going down the right path, for what people, for what would, what would really bake people’s noodles, what would get you going, what would make you, happy to see, be taught, all right, got kind of quiet there, all right, so I think we have a pretty good list of topics, right, so what’s their pain, all of these things, it’s a challenge, knowing what you don’t know, yeah, yeah, it is a challenge, knowing what you don’t know, but it’s, it’s also a challenge, trying to teach you, what you need to know, you know, you, you know, like, just maybe, the thing that you need to know about parallelism, is, whether it’s good or bad, if you, if it’s something you should worry about, like, what it is, what it does, you know, like, or maybe, what you need to know about resource, which is by queries, is this like, like, like, is that, is, is that something that you should be concerned about, is, like, do, do, will queries that use more resources go slower, for compression, it’s, what, like, what kind of compression should I use, I mean, that’s pretty well documented, there aren’t a ton of gutches with compression, I sort of like compression, I just don’t see it get used a lot, the other thing about compression, is it just hasn’t changed much, since like, SQL Server 2008 R2 or so, so like, you know, it’s, it’s another one where like, if I’m going to teach it, I need something very specific to teach people about, you know, like, like, really, it’s, it’s sort of a, it’s like a five minute statement, in a lot of ways, it’s like, compression, it’s good because, this, you should use row compression when, you have fairly unique data, you should use page compression when you have less unique data, I mean, it’s just like, you know, like, stuff that you can just get out of the way pretty quickly, it might not demo well, and it might not be anything that people need to know about, Ken McT says, the very basics of what the optimizer does, so, hmm, okay, so let’s see, let’s see, let’s go back to here, the basics of what the optimizer does, he says, I would have liked something about how to measure changes you make in a better way than just watching the timer at the bottom right of SSMS, so would that go under resource usage, and how to measure changes, uh, yeah, there we go, so parallelism, uh, so let’s just call that, uh, how to set settings, that might be a good one, that might be, uh, that might be close enough, and we’ll, we know what the settings are, we don’t have to worry about that, we know we’re not talking about resource governor, Kendra didn’t show up, because I, apparently I’m not talking about resource governor, so Kendra didn’t show, I was very hurt by that, I’m kidding, I think it’s just, Kendra’s probably just drunk on a couch somewhere, I don’t blame Kendra, it is that time, it is that time, when you’re in the UK, okay, it is that time, it is that time, we’re not talking about, the basics, of, the optimizer, so, Coyote McD, it’s a good suggestion, but I want to ask you, what are optimizer basics, if you, if you had to think about, optimizer basics, would it be, um, you know, uh, optimizer assumptions, like the cold cache, uh, that the data exists, that data is independent, stuff like that, uh, would it be, um, you know, figuring out query plans, or like, like maybe optimizer tricks, like, uh, like simplification, or, you know, contradiction detection, or stuff like that, uh, collapsing sub queries, or expressions, like what kind of, what kind of optimizer, or what would you consider, to be basics, of the optimizer, that would be, or maybe like, uh, optimizer has rules, stuff like that, uh, query plan reuse, so query plan reuse, I would put that up, under parameterization, uh, so I would put that up here, and plan reuse, so that would be a good topic, under parameterization, because that is, that is very, that is very, very good, and that definitely falls into, like a pretty big wheelhouse, of, of subjects, and topics, that you can talk about, with people, that will, like, boggle, and make them angry, and be like, why does it do that, like, who does, design this thing, why are they so great, like, what are they thinking, so yeah, that’s definitely a good one, that we can, um, we can put in there, I would add that in there, uh, has something about statistics, been put down yet, no, nothing about statistics, statistics, but, what about statistics, what’s something about statistics, would you want to learn, how to look at them, uh, how to figure out, if they’re good or bad, you know, there’s like, the ascending key problem, that got, that got kind of fixed, with SQL Server, with the new cardinality estimator, it’s a kind of fixed, but like, what about, like, like old statistics, when to update statistics, because if we’re talking about, when to update statistics, that’s a maintenance thing, oh, it’s off, but I never grasp, the service broker, how to use it, why be better than the, yeah, it’s service brokers, I mean, Remus Rosanu, is one of the finest people, you may never meet, in your life, I still don’t know, why service broker, came to be, um, my old, my old co-worker, Jeremiah, once used service broker, to asynchronously, shrink transaction logs, people often have to, hunt for reasons, to use service broker, Michael Erickson says, I guess it is hard, to understand parameter sniffing, without basic statistics knowledge, so that’s a, that’s an interesting one, and that would kind of, tie into, what happens, with, uh, stats and local variables, uh, maybe, this would also, have something about, stats in it, sagability would certainly, have something about, stats in it, and so would implicit conversion, there would definitely be, stuff about stats in there, and then parameterization, this would certainly, have stuff about, stats in it, because you, you know, when you, when you’re learning about, uh, when you’re learning about, you know, why SQL Server chooses, different plans, then statistics are a big part of that, so Lisa says, let me rephrase that, estimations and how to troubleshoot, when they are out by a lot, you mean aside from updating statistics, statistics.
So there’s, there’s a lot that goes into that, so when, when, so when cardinality estimates are terribly wrong, you know, you have to go back and you kind of have to look at, is it one of these problems?
Did I not update statistics recently? Um, or, did I write my predicate, or my join in a way, that SQL Server is unable to make a good guess?
The other thing that’s a big, the other thing that’s big there, is figuring out when, uh, when inaccurate statistics guesses are actually a problem.
I see questions posted quite a bit about, statistics were right, but everything else was wrong, or, statistics were wrong, but like, and I have, here’s my query plan, how do I fix it?
But the plan is still remarkably fast. So, uh, I think when it comes to statistics in general, rather than have a section on statistics, I would rather weave statistics in, to a lot of the different things I teach, because you can, you can, you can, you can really, I think you can, you can drive home how important statistics can be, when they’re, like when they cause problems, and when they don’t.
So, I would probably want to weave that in. okay, let me say, maybe something about the optimize forehand. All right, so, would I put that under, you know what, I would, I would want to have that under local variables, since optimize for, since optimize for, does just about the same thing there.
So, I would have optimize for, alongside local variables. So, well, optimize for unknown, right? so, let’s make sure that’s specific. The optimize for unknown, would be, would certainly fall into the local variable category.
I don’t, but, you know, that’s something that I would want to be, tie into there, because I don’t want people to walk away from this, with a question like, but what’s the difference between, you know, optimize for unknown, and a local variable.
And the same reason that, you know, you see the question all the time, people asking, you know, like, what’s the difference between, no lock, and, read, uncommitted, right?
Like, that, what’s the difference between, same thing, the difference between local variables, and optimize for unknown, it’s the same damn thing. He says, I guess transactions would fall under concurrency. So, when you’re talking about transactions, when you’re talking about, begin, begin tran, and commit or roll back, everything that happens within that, is subject to query tuning, right?
And we know they’re dangerous, because they increase the chance of blocking. If you update, if you like, say, begin tran, update a single row, then go do, like, go off on some crazy meandering path of doing things, and then finally roll back or commit way down here.
Well, if all of this big meandering path is fast, then that, then that one lock you took up here, that was done, say, 200 milliseconds, 500 milliseconds later, probably not the end of the world.
But, if you take that one update, and then this big meandering path is like, two to five seconds, then that lock becomes more interesting.
So, when it comes to tuning transactions, tuning transactions is more about tuning everything that happens within the transaction. Same thing with triggers, right? Same thing with functions, same thing with store procedures, same thing with anything that contains code in SQL Server.
However, if you have, if you have a whole bunch of stuff that’s slow between a begin tran and a commit or roll back, we can focus on tuning the stuff that’s slow in there. You know, sure, there’s, there, there might be times when you can move the begin tran to like some other part of the code where it really matters.
But, otherwise, what do you tune about a transaction? You tune the underlying queries and indexes, right?
You don’t go in, you don’t, you don’t, there’s like no, like, like hint, and there’s no like option for begin tran that makes things faster, right? The transactions generally would, would be a concurrency thing.
Not that concurrency has nothing to do with performance. It’s just that concurrency is such a topic unto itself with locking, blocking, deadlocks, transactions, things like that, that it’s really tough to sort of, you know, like stick into a performance tuning talk easily.
So we have a good list of things here, right? We have, we have a few different titles up here. And that’s, that’s good.
That’s good stuff. So let’s start on this page. Let’s start on this page. And let’s say, what is our abstract going to be? So let’s just, let’s not say you’re a DBA or developer.
Let’s, let’s take titles out of it. Right? And let’s say something like, you’re new to SQL Server.
And your job is to, let’s say, fix performance problems, but you don’t know where to start.
You’ve been looking at queries and query plans. And, let’s see.
queries, query plans. And let’s just say something.
We’ll, we’ll fill that in later. And something indexes for a year or two, but it’s still not making a lot of sense. Logs, read, give, bury.
Nice. Oh, stop it.
Or if it applies to you, use UUID as a primary key. I’m totally fine with that. I’m just happy if you have a primary key. I’m happy if you have tried so hard to design things properly, that you have a GUID as a primary key.
There are ways to like not have it be so painful. Like if you make your primary key, a nonclustered index, or if you use a, like a sequentially generated GUID, you can have far fewer problems with GUIDs as a primary key.
But most of the time, when I see someone has a primary key, I’m like, you know what? You tried your best. You tried, you tried hard.
And I understand why, you know what? You know, when you think about numbers in SQL Server, you think about ints and big ints, what do they have that GUIDs don’t have? They have an end.
There’s a, there’s a finite number of those numbers until you have reached the end of those numbers. GUIDs, wide open baby.
You can have GUIDs go on forever and probably be unique. Numbers, finite. Even if you start negative and go positive, they are still finite.
finite. Granted, big ints take a long time to go from the negative end to the positive end and hit both sides of that limit. But I have faith that with big enough data, with real big data, you could do it.
All right. Okay. So I’m going to take a quick break and I will be back and we will work more in the middle of the year.
Bye. Bye. Bye. Bye. Bye.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. You know I’m home. You know I’m home.
Where else am I going to be? Leave it. It’s fine. All right. So let’s finish up strong. We’ll go until the hour and we’ll finish up writing this abstract here. The blog is where you give you a very specific advice and you’re not sure if it applies to you or it’s even the problem.
So using some of these advice. So using some of these advice, beyond that, you’re not sure how to measure if your changes are, let’s say, are working. Cool.
Cool. So we got that part. All right. And we’ll say something like, you know, like in this day long, right? So let’s see.
Join me for a full day. All right. It is going to be a full day, right? You know what? I don’t like the way that sounds. You know what I don’t like about that? It starts with join.
Join. You know what bums me out about join? Too punny. It’s too punny for me. Much like LaCroix bubbles are too big and soft for me.
And only Canada Dry bubbles satisfy me. Things that are too punny don’t go over well with me. So in this full day, I don’t know.
Let’s just say something funny in here. Performance tuning extravaganza. You’ll learn all of the stuff.
I’ll get to that in a second. Okay. You’ll learn about all the most common anti-patterns in T-SQL. Oh, I messed that up terribly.
T-SQL querying and indexing. How to spot them. Using.
Oh, come on. I was so close. Using execution plans. Ah, here we go. All right.
That’s a full enough thought. In this full day. Performance tuning extravaganza. You’ll learn about all the most common anti-patterns. In T-SQL querying and indexing. And how to spot them using execution plans.
You’ll also. Leave. Knowing.
The. Let’s see here. What could we call. Some of these things. Knowing.
Why. Why they cause. The problems that they do. And. How you can. Solve them.
Quickly. And. Painlessly. Pain points. Pain points indeed. So I don’t like to say pain points too much.
Because I don’t want. I don’t want people. I want people to. To come to me.
Knowing that they have them. Without me having to point them out. Getting like. Oh. That looks like it hurts. Oh. That looks like it hurts. Oh. How’d you do that? So. Let’s see here.
Let’s see here. So we could add in some specific stuff now. Right.
So. You’ll. Learn. Let’s see. When. Which. Temporary. Object. To.
Actually. No. Let’s start. We’ll get to that in a minute. You’ll learn. How to. Write. Queries. That. Will. Never be.
Slow. I mean. That sounds good. But I don’t know if that’s. I don’t know if that’s totally true. You’ll learn how to write queries. You know what?
Screw it. We’re going to stick with that. It’ll never. Be slow. We have a lot of you’ll learn in here. You know. There’s a lot of you’ll learns. We have a lot of.
You have too many you’ll learns. Do we have. How many do we have? Not that many. It is a bold statement. But I’m a bold human being. I’m like barbecue sauce Lee.
Bold. And tangy. Alright.
That’s all I got. I’m not bald. I’m doing okay. I’m doing okay. So let’s see. Let’s read it a little. You’re new to SQL Server and your job is to fix performance problems. Ooh.
You know what we should do here? Your job more and more is to fix performance problems. But you don’t know where to start.
You’ve been looking at queries and query plans and puzzling over indexes for a year or two. But it’s still not making a lot of sense. The blogs you read give very specific advice.
And you’re not sure if it applies to you or if it’s a problem. No. I don’t like this one. I don’t like that one. Beyond that. You’re not even sure how to measure if your changes are working or even the right thing to do. There we go.
That can be a big assumption. But the nice thing there. The nice thing there.
is if they leave with the materials, they have no excuse not to learn it eventually. Even if they don’t learn it that day, they’ll bring it home and they’ll learn it eventually. So it’s bold.
So you’ll learn is like future predictive. You’ll learn at some point in the future. Might not be today. Might not be tomorrow. But at some point, you will open up that thing that I gave you.
And you’ll say, ah. And you’ll have learned it. So you will learn. You’ll learn. It’s not like saying, you’ll pay.
I would even say if they have the concept that they have no excuse to learn. Yay! I just don’t want to put that kind of thing on people. Like, look, you have no excuse.
It’s not like my mother vacuuming outside my door when I had a hangover when I was a kid. No excuse. Oh, not for the doc.
Yes, not for the doc indeed. So let’s see here. Pretty happy with this. You’ve been looking at queries. You’ve been pulling around the air to have something on a sense. Beyond that, you’re not even sure how to measure if your changes are working. Sorry, my printer just started spazzing out for some reason.
In this full day, performance, tuning, extravaganza, ganza, ganza, ganza, you’ll learn about all the most common anti-patterns in T-SQL queering and indexing. And how to spot them using execution plans. No, we’ll keep that all together.
You also leave knowing why they cause the problems that they do and how you can solve them quickly and painlessly. If you want to… If you want to…
The… Knowledge… And… Confidence…
To tune queries… So they’ll never be slow again… This… Is… If anyone…
Who’s thinking about attending… Watches this video and sees all the typos I’m making… They might change their mind. I found the main barrier for me is not being able to learn about something is my laziness. No excuse other than that.
Well… Lee… I understand that fully. If you want to gain the knowledge and confidence to tune queries… I’ll never be slow again… This is… The…
The what? This is… The training… You… Need. So let’s go back… Let’s see here…
Let’s see… Training you need… All these in one day…
So… No… I… I asked… I asked the attendees… For their ideas. There’s a lot of this stuff that you can…
You can cover… In a day. I would probably cut the line about here. Because I think…
A lot of… So… It’s a good… That’s a good question, Paranoid DBA. Okay… And… If you think about… What’s being talked about here… As…
Like… Come on… Man… Come on back… Where’d you go? Why are you not… Whatever… Screw this… So if you think about… Teaching each one of these concepts individually…
Yes… That is a big… Crazy… Wide open… Day of learning… Right?
You can think about it like that… But… If you tie these all in together… If you tie these things in together… So that…
When they… They learn about… Sargability… They also learn about implicit conversion… And like… You can… You can tie a lot of these subjects in… So that you… You kind of put… You put more of these pieces together into a puzzle…
Spirit of Lies says… The Friday session had a lot of these topics… Minus blocking… Yeah… So… Yes… But… This would be like… Beginner stuff…
So this would be like… Very… Like… Early on entry level stuff… When you’re… When you’re… Like… As you progress through performance tuning… The first thing you have to learn is…
Like… You have to learn the fundamentals of these things… And then as you get more advanced… You can… You can like… You know… You get more…
You get… As you get more advanced… You can apply them to more advanced things… So… What I’ve found over the course of my life performance tuning… Is that… A lot of the reasons why queries are slow…
Hasn’t changed a lot… But there are different audiences… And those different audiences have different strengths… Different weaknesses…
And someone just walking into query tuning… Who needs to know… Like… Just the right thing to do… They might not… They might not need to know… Like…
Lots of super advanced things that you can do with these things… But they need to know what the right thing to do is… Right? They need that basic fundamental knowledge… Of like…
Why do table variables give me a weird plan? Right? What is sargability? Well… Stuff like that… Are CTE better than temp tables? Why…
Like… How come when I use a local variable… This execution plan gets weird on me? You know… Like… If I have a query that’s going slow… How do I know if it has a good index? Things like that… The more advanced stuff…
Is just like… The next stage of… Like… You already understand what an implicit conversion is… We don’t need to talk about that… You know…
What you need to know is like… You know… You’re looking at an execution plan… And… It has a spool in it… And you need to know… Why that spool is there…
And how you can fix that spool… So you’ve gotten to the point where… You kind of know this basic stuff… But getting to the next point… Paranoid D.U.J. says… But that changes in each version of SQL…
No, it doesn’t! It sort of changed for some things in SQL Server 2019… And it didn’t even change in a complete… And like…
Overwhelmingly good way… It does not change in each version of SQL… Implicit conversion has been the same… CTE have been the same… The problems with local variables have been the same… The problem with sargability have been the same…
The problem with functions have been the same… All of these problems have been the same… With only a few changes in SQL Server 2019… And if you think that there are a lot of people…
Who are one to two years into their SQL Server journey… Who are going to be coming to training… Let’s say in the next two to three… Maybe six months… Who are all fully fledged using SQL Server 2019…
In production… You are out of your mind… But most of these things have not changed… With every version of SQL Server…
What’s a good index has not changed… Parameterization has not changed… Parameter sniffing has not changed… How to use dynamic SQL properly has not changed… None of these things have changed…
With the SQL Server… Lee still has SQL Server 2008 instances… Lee no wonder you want a different job… I hope that works out soon for you… So let’s see here…
Let’s finish this up… And let’s get on out of here… It’s been a while… Me babbling on and on to you… If you want to gain the knowledge and confidence… Toon queries so they’ll never be slow again… This is the training you need…
Let’s see here… I don’t know if I want to add anything to this… You know what I’m going to do… I’m going to save this… I’m going to save this…
This PC… I’m going to save this… I’ll save this later… What I’m going to do is save this… And I’m going to sleep on it… Free candy at the end… How about…
Um… And… You also get… Access to all my… Videos… Blah…
Blah… Blah… BING! I’m not giving away my Canada Dry Lee… Canada Dry is my favorite seltzer… It’s much better than La Croix…
Canada Dry is the best seltzer… Never going to give away my Canada Dry… You can’t take my Canada Dry…
Don’t try to… All right… So… I think we’ve done a pretty good job of getting… Getting the…
What we want to teach… Who we want to teach it to in the abstract… In there… Uh… I should tweet that… I’m too lazy to tweet that… Any vodka with it?
No… Not today… Not yet at least… Uh… It’s still… Um… You know… It’s still 3 o’clock here… And… Uh…
I don’t know… I wanted to at least get through this thing sober… Right after this… Right after this… Botsco… Sure… Let me…
Point you… To my website… Where there is… Yet another post… About local variables… Yeah…
Well that’s a bad idea isn’t it? Michael says… I think this would be good for devs that need to write queries right from the beginning… Yes… That is absolutely what I’m going for here… Um… So I want people who…
So like… Expanding a little bit on what Michael said… Because Michael brings up an excellent point… What I want… What I would say about training like this… Is…
That… When… You have… Had a… T-SQL… Application… That’s been around for a while… You most likely have a lot of bad practices in there…
SQL Server developers will show up to… Either… It’s going to be their first day on the job as a T-SQL developer… They’re not going to really be a T-SQL developer… They’re going to be a developer in some other…
Something else… And… You know… They’re going to see what you did in that code that’s bad… And… What’s going to happen is… They’re going to just keep doing that…
They’re going to keep repeating the same mistakes… They’re going to take those mistakes with them elsewhere… And… What I want to do is… Get people to the point where… What…
When they’re writing a query… They’re not making those fundamental unforced errors… They’re not continuing on that… That legacy of… Of poor T-SQL hygiene… So what Michael said is very, very…
On point with what I want to do… I want to give people the right foundational knowledge… So that… You know… They don’t get bit by a lot of… Just the… You know…
Those like… Like… Head… Like… Why performance issues… Right? That’s what I want… That’s what I want… So…
Thank you for… Hanging out with me today… While we… While we talked about… What makes a good abstract… And writing… The abstract… Also thank you very much for your ideas and suggestions… I appreciate it…
It’s nice having people… To brainstorm… With… If you… Would like to join me… Friday… 10th…
Or 24th… I have a full day of online performance tuning… The coupon code… Floating above my head… Will get you 75 bucks off… From there… If you want to get tickets…
You can head over here… And if you buy a ticket… You get free access… To all of my training… Forever and ever… You can look at… What…
My training covers… Over at that link… So… Feel free to click on those… At your leisure… If you… If this is the kind of… SQL Server content… That you enjoy watching… You know…
You can… Hit the little bell buttons… On YouTube… To like… Subscribe… Or whatever… Whatever you cool kids do these days… Same thing for Twitch… If you want to follow me on Twitch… You’ll get notified when I go live… You won’t have to depend on…
Twitter to tell you… Because… We all know how… Untrustworthy… Twitter is… So… Thanks for joining me… Uh… Come on back… I’m not…
I’m not going to be doing one tomorrow… Because it’s Saturday… And… Uh… I’ll probably end up… I’ll probably not be in good shape… For doing a live stream… Plus I think I’m about to get arrested anyway… So…
Thanks for joining me… Um… If I can get through one live stream… Without sirens… I would be so impressed… Thanks for joining me… Uh… Thanks for hanging out… And I will most likely… Uh… See you…
Uh… Next week… For… Some more live streaming goodness… Take care everyone… Stay safe… See you next time…
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.