ICYMI
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Thanks for watching!
Video Summary
In this video, I explore some quirky aspects of SQL Server maintenance and query optimization. Starting off with a lighthearted joke about webcasts and attendance, I dive into more serious topics like index usage, DMVs (Dynamic Management Views), and the optimizer’s behavior. I share an amusing demo illustrating how certain columns in tables can influence parallel plans, leading to performance differences that are both interesting and somewhat perplexing. The discussion then shifts to backup encryption and restore processes, touching on best practices for securing backups when using cloud storage services like AWS. Throughout the video, I also share some personal insights—like my ongoing mustache-growing challenge and a few funny anecdotes about past projects and experiences with SQL Server. It’s been great engaging with you all, and I hope to see more of your questions in future sessions!
Full Transcript
I sure hope I’m alive. Wee! Alright, let’s get that worked out. And…
What if you threw a webcast and nobody came? Good question, right? I should share this thing on Twitter. Words that…
…the horrible coming out of my face. … …
… … … … all right and we’ll see oh we have a person hello a person there’s all well also a truck out front the truck is my new best friend i guess and i have a bunch of wine openers might ask you know it’s funny these uh these wine openers are functionally the same there is absolutely no difference between any of these wine openers these corkscrews they are identical in every single way right from the backs the fronts but they just have slightly different branding on them which i find very funny so i got two houdinis and a word i can’t read because it’s in too fancy of a script and uh everything about them is completely the same and it reminds me a bit of um like dmv queries in sql server where uh you know they can like they can be wrong you can totally write a wrong query but when you write the right query it’s going to look exactly the same as everyone else’s right query like you can find ways to mess with it like to do something a little bit differently but it’s all being the same dmvs and columns everything else and it’s very strange to think about that that everyone has the exact same set of tools to work with and everyone has the exact same set of tools uh to to look at but people get very territorial about their very special dmv or diagnostic queries and you you would you would act like they invented the thing but they didn’t it’s kind of funny anyway there are there are approximately three of you here one of you is going to say something one of you is going to ask a question like three people in a room one of you has a question about sql server maybe what’s up arthur daniels great post today about the memory grant thing enjoyed that uh something kind of funny that you’ll notice uh with stack overflow is when you’re writing queries against it uh stuff against like the users table that involves about me or the post table that involves uh a few of the columns or like the comments table that involves the text column the text of the comment uh the optimizer is quite biased against parallel plans when you involve when you involve those columns uh i noticed that like the even on 2010 like the the parallel version of the plan that you were looking at was quite quite a bit more expensive than the serial version but uh i’ll send you over a funny demo query specifically for the comments table where if you use like if you use a creation date column play goes parallel everything’s fine but if you use the the comment text column it goes to say serial and everything sucks it’s kind of amusing yes yes indeed indeed indeed we should all these things optimizer is quite quite a strange strange beast used used used to maxed up one oh yeah you use a sap right is it business one that you use mostly or something else there’s a funny delay on these things seven of you god bless does does one of seven people have a question i’m uh i’m busy growing out my various hairs uh you’ve since you’re here you you’ve probably all seen the uh the charity thing that i’m doing where if if i hit a certain dollar amount i’ll i’ll deliver my my regular session at bits not not the not the full pre-con i’m not going to dress like freddie mercury for eight hours i don’t think freddie mercury even dressed like freddie mercury for eight hours but uh if i’ll do the one hour session dressed just in freddie mercury’s get up from the live aid concert uh and so i’m trying to grow out the the mustache and the hair so i can i can look more freddie-ish when i do that going all for it i’m not gonna i’m not gonna get skinny though i don’t think i don’t think i have it i don’t think i have the capability to get to get that skinny but i tried once it didn’t go well i got very dizzy all the time no fake teeth no my my regular teeth are weird enough you put fake teeth in here i don’t know what would happen they’re all over the place you know what’s funny is i actually got uh my wit my wisdom teeth were perfectly fine like they weren’t impacted everything was normal with my wisdom teeth but it was decided that they should get pulled out and everyone who said that you know if you go take your wisdom teeth out it’ll help your like regular teeth straighten out and i did that when i was 19 and here i am now i think like i’m i’m 2 19s in age and my teeth haven’t moved an inch or not even like at all an inch would be huge but like my teeth did not change one bit so everyone lied to me my teeth did not like magically decompress in my gums uh arthur has an interesting question uh have you ever seen an execution plan in cache with just a select operator and zero estimated cost if it helps a query return zero rows and i run the query manually i get a clustered index seek uh so that can happen uh let’s see what’s the what’s the best way some um if and it’s it’s just a select query it’s like it’s not wrapped in like an if exists or like in any sort of if logic or branching logic anyway uh so when if you run the query manually um you might be getting a trivial plan which is which in which case you will get um uh what do you call it there an index seek but if you get a non-trivial plan um yeah so if the cash trivial plan you might be getting a constant scan in there uh which might be weird i don’t know though um um if you want to show me privately what you mean i’d be happy to take a better look at it but it is a little little rusty on that uh lee asks any gutches with backup encryption we’re copying backups to aws now and i’d like to ensure no one can restore it but us uh no no real gutches uh the only real gutches with backups right now are around um uh tde so if you’re if you’re just like if you’re using like like like like the like built-in backup encryption and not using tde and then taking the backups uh so like with tde it gets kind of funky because on versions of cc older than 2016 you would get this kind of weird thing where all of a sudden you couldn’t compress your backups when tde was enabled uh and then you know your backups were huge but after two cc was over 2016 introduced encryption for uh for or reintroduced backup compression for tde enabled databases but there’s been all sorts of crazy bugs uh can i still restore a backup of prod to dev without a bunch of hoops to jump through well initially you’ll have some hoops like you’ll have to make sure that they have the certificate and all that other stuff uh on on the on the dev server but after that it’s pretty easy to pretty easy to do uh and all that stuff is built into oldest scripts so or at least at least on the backup side of course ola doesn’t write a restore script poor ola yeah the trivial yeah the constant scan is a weird thing uh so like what my guess would be that sometimes in prod the query is non-trivial and it might bail out for some reason like there might be some contradiction detection uh so i don’t know lee says he’s using dba tools to generate restore scripts to restore a dev uh i’ve never actually used them for that um i still don’t have a real job where i would have to do backups and restores but um you know hopefully hopefully i’ll avoid that uh but you know if if they have if they have everything built in then i wouldn’t i wouldn’t distrust it the dba tools folks are uh pretty pretty good pretty smart folks pretty good at powershell much better at powershell than i’ll ever be i hope if anyone wants me to learn powershell i don’t know what i’ll do let’s go cry in a corner somewhere that’s what i what i do best anyway cry in a corner very goth all right any other questions any other people want to know about a thing there there are 10 of you here now this many no one asks me anything i’m gonna go back to doodling become quite quite a doodler the last few weeks maybe next week i’ll show you some of my doodles i recorded a video earlier too joe asks is this going to be a regular thing like office hours used to be yeah as long as i can keep it up uh you know it’s it’s just me and um you know so there’s like no coverage so if uh if if the day ever comes when i’m booked with a client friday at noon then i won’t be able to do it but i think as long as uh as long as i can i’ll do it i’ll make it regular well maybe i’ll do it on a different day maybe i’ll you know bring me i just tried fridays because you know when we did office hours on wednesdays people would always be like uh it’s wednesday at noon i can’t do anything fridays at noon people are a little bit more um a little bit more uh relaxed at work i think hopefully so i i started i just decided to do fridays because they seemed like a pretty reasonable day uh have you used the failover detection utility from the tiger team no um you know i it’s not that i don’t think it’s cool i think it’s really cool and i love that they built their they’re built the microsoft is building tools like this uh it’s just been so long since um i’ve even had an ag demo environment with my vms to play with that stuff it’s just it’s just not my main focus this is not what i’m particularly interested in with sql server um it looks great though uh i know a few people excuse me who have used it who do have uh production ag stuff and they are pretty pretty psyched on it um arthur asks on oltp systems are you a fan of compression um you know if if io is an issue sure um it’s if so yes if like so if you’re in a situation with oltp where like uh you have trouble or like even if you have a really big oltp system you know if you have you know if you have trouble either keeping all your data pretty uh you know pretty like a ton of data or if you know uh you if like when you read data from disk it’s you know uh it’s not as fast as you’d like it to be the compression can certainly help there uh as long as you’re not like terribly cpu bound it’s pretty neat also you know it’s one of those things where like you have to be really careful that a like you’re actually getting a compression benefit from things b that the compression isn’t messing with your inserts updates and deletes and see that um you know when it’s it’s not like causing any like not causing like harmful cpu overhead uh it totally has a good place with oltp i’m not against it by any means but you know there are some there are some boxes that you have to check before i think before you go and uh implement it the other thing that kind of stinks about compression is that there’s no way to set it at like the table level or database level or anything like that so like people can go and create a new index and with no compression and then you’re just kind of like stuck yeah if you want to change it you have to rebuild the index but then again that’s one of the reasons that i would support rebuilding an index is if you wanted to add compression lee says i recently compressed a database and the next day one of our nightly ssis packages to that database suddenly jumped five minutes in duration with a lot more logical reads interesting i wonder what did you did you happen to capture what was different about it i mean like i get that you compressed the database and that was different but i wonder what was different about the process or like what what what the process did differently in some way be interesting darren says he always has a cpu worry with compression well no of course nothing changed in the ssi but like the ssis package does stuff in sql server and sometimes those things have execution plans or they have you know things to go differently um you know like so for an example uh there are these funny things called dml request sorts when you modify data and some sometimes those dml request sorts can add a exactly what they sound like a gigantic sort to a query plan and sometimes i would guess that if you had to compress data loading data in a sorted pattern would would help so that if you like if you had an insert that was at one point not doing the the dml request sort and then after you did the compression it was then that might be the clue as far as like the cpu where he goes uh you know i very few times do i see a system that is cpu constrained in a way that like cpus are pegged so like i don’t see a lot of people whose systems are running oltp and they’re at like 99 which is where which is like where i’d be worried about compression where i most people like when they people have cpu which is on a sql server mostly what i see is like they’re running out of worker thread stuff where they’re hitting thread pool weights and compression won’t hit won’t compression compression won’t affect that compression will affect like if you have cpus that are running hot then they’re going to run a little bit hotter with compression not like a ton and says you should have seen your system a couple months ago could take a look now want to pay me the compile rate is super high in our internal apps like the how much when you say compile rate do you mean like queries compiling or do you mean like how long they have like how long it takes them to compile or uh how frequently they compile there’s a lot of questions they have so many questions so many big questions percent compile the batches oh and uh i wonder what changed because obviously a lot of compilations is never a good thing uh did you introduce like some weird dynamic sql or some temp tables or uh i don’t know i’ll tell you one of the funniest reasons uh that i ever saw for a high compile rate was um i forget what the application was i want to say it was like asp.net but all of the queries that went in uh they would they would they would like be this like initial query that would run that would like test to see if the query was valid so it would come in it would be like set fmt only on lower like set browse table on or whatever and then it would run a query that would hit like 70 million roll rows in a table and then it would like change the settings back and all of these queries are coming in and they were just compiling every single time because the the session would start it would run those set options and it would cause a compilation which is really funny to see i was like why why why would you do that silly can you fix parameter sniffing with option recompile uh so that doesn’t actually fix parameter sniffing what that does is it disables parameters sniffing um and while it is you technically a fix sometimes there are underlying plan quality issues that need to be addressed because what option recompile does is it it assumes that the query plan you get when sql server can make a good guess is a good plan and i don’t i don’t buy that a lot of the time i think a lot a lot of the time when people are like oh if i option recompile it fixed the problem but it does it there may still be these weird underlying things that you have to deal with oh well you know it’s it showed up in chat so i felt i figured i’d talk about it sorry i didn’t i didn’t mean that you weren’t funny i just i was i just figured i’d uh i’d talk about option recompile it’s not it’s not like i have a lot of other stuff to talk about i’m pretty boring i already knew a vacuum cleaner it was funny like uh my wife had knocked over this like pink princess unicorn snow globe that my daughter has and it just like it was like shattered glass and plaster and whatever the liquid inside of a snow globe is everywhere and she she used our nice vacuum monster dyson monster thing to to uh to vacuum it up and apparently you shouldn’t vacuum water leave it non-shop back because now like anytime you put one of the attachment heads in it just starts spinning even if you’re not vacuuming so there’s a short in there somewhere so i ordered a new vacuum this morning hopefully that shows up before the floors are covered in disgustingness uh that’s the rest of the way always turn on query store in sql server 2016 plus um no uh there is some observer overhead to it and well i think it’s a very valuable tool and especially if uh so i’ll put it like this if you’re going to use it then you should turn it on and see if there’s an there’s no unacceptable performance decrease with query store i think it’s a really cool thing to to be able to use to track query performance especially over time because the plan cache is such hot garbage at keeping things in it um so i would say that i would i would like to see it turned on more and i would like to see um the various weird bugs around it uh fixed and i would also like to see some more options about where the data is stored with query store i think that’d be nice but uh you know i’d say generally if you’re going to use it and there’s no you know um unacceptable performance overhead to turn it on uh one of my last great posts over on brent’s site was about uh the cruel defaults that uh query store is turned on with so like uh there are collection modes where they define the types of queries that will end up getting collected by query store by default it goes to all and all captures and since this is my channel i get to curse all capture some real bullshit all will capture every single like system query and all this dumb stuff that goes on in the background that who cares but you if you use the collection mode auto that like queries have to meet some certain thresholds they’re not documented so i don’t know what they are but uh they have to um they have to meet certain thresholds before they get collected in query story they’re in like like compile time or like how like you know if like the query took a while to run or something like that so they’re like that turning on auto makes the overhead a little bit less tough but you know it’s it can still be there and arthur was helpful enough to post the link for us thank you arthur you are my you’re my number one junior office hours dba i wish i had a better title for you uh darren says he has an odd question we have a data mart where they rebuild all the data databases are in simple recovery model it seems odd to have no maintenance at all though any thoughts uh no that’s like the perfect perfect time to not have any maintenance if you’re just blowing everything away and loading it back in every night who cares let’s say it’s like a dba’s dream to not have to care about that sort of thing i wouldn’t do any maintenance on that i mean like there’s like there’s like almost no point in backing it up there’s no point in doing index maintenance on it i mean okay i can’t even think of a good reason to run check run check db there i guess i guess check db would be would be okay like if you wanted to run that before you loaded data just to see if like any system tables were corrupt that might be a good idea but i can’t think of a good yeah it’s just isn’t really good if it’s just gonna get like you’re taking a backup of something that’s gonna be gone anyway all right out the door out the door with you see what other maintenance is there i wouldn’t update you know i wouldn’t update stats because i’m pretty sure that like if you like truncate or drop tables and create tables and reload everything i wouldn’t even like you like the stats would update there i mean i guess if you needed i guess if you needed better statistics than what the autumn like the whatever like automatic stats update threshold is then there might be there might be a use case for updating stats there at a higher sampling well they do all right we’re on the same page look at that good job you are whoever designed that is now a full-fledged sql server consultant they should i don’t know i don’t have any i don’t have any certifications i’m sorry if they did if i did i would i would say that they are honorary honorarily certified but i’ve got none none i’m too dumb to pass a test see lee says i have a two new two node ag on standard edition with five ags because i have to use basic availability groups any reasons not to migrate this one ag to enterprise if i do make so uh i’d say cost is the primary thing like how many so you know once you go from standard to enterprise your cost jumps up pretty significantly it’s a five thousand dollar per court difference if you do migrate any suggested method uh well if you’re migrating to like if you’re on uh windows server 2016 i think you can make it make it a bit more seamless but i think like i know with ags there’s some weird stuff from like the windows server the windows server level needing to match and then like the sql server level needing to match there’s weird stuff involved there oh if you have i mean if you if you already have it paid for then yeah i’d probably move uh if you need to move to like like a net new environment i would use uh like log shipping or something i wouldn’t use mirroring in this case because you’re going from standard to enterprise and uh you know you obviously you already have an ag setup so that would be rather awkward to try and implement so um i i would i would say to just if you wanted to use log shipping and just uh get your current databases moved over to whatever new vm hardware fake hardware whatever virtual hardware uh you’re going to set up then that’s probably how i’d move things just to just to make the transition a bit more seamless and that’s assuming that you couldn’t you know set up another vm to you know get the ag also synchronizing to and then fail over to that and then fail over to whatever’s next and then destroy the old environment yeah that’s tough so uh at least he’s trying to simplify things so i don’t have so many ags to manage and check like when we reboot for us yeah that’s a bummer um you know that’s one of the reasons why you know i i asked the question again ah man all my all my all my solid gold material is still still on brent’s site but uh you know i i had a poster over there recently about like have ags gotten any easier and i think you know when it comes to stuff like patching and um you know so and even something like you know just doing uh like uh checking out indexes in an ag right so you know you have the stuff on the primary and you check like index metrics there and you know you can see that like i have a bunch of unused indexes but they get used on a secondary if you’re offloading reads so yeah you know there’s still a lot of stuff that’s tough about ags and patching is certainly one of those very perilous things because with ags you know in particular like there are a ton of patches i mean there are a ton of fixes in each patch specifically for ag stuff and ag patches have been known to break stuff too so it’s like you’re very perilous you know it’s like whatever i’d say whatever you get um whatever you what do we have for your ags make sure that you know when you do those patches you’re testing them in a lower environment first because boy howdy if one of those patches breaks something you have to uninstall a patch uninstalling a patch is my one of my least favorite things in the world because i always forget it’s the best way to i know there’s like there’s like a few ways to do it and there’s like one of them that’s like the best way to do it and i always forget which one it is yeah daren mentions you might be able to do a rolling upgrade if you can do a rolling upgrade great if you’re on like windows server 2012 or something though i don’t think you can do the you can’t do a rolling upgrade if you’re moving to like windows server 16 or 19 down the line so it would have to be at least 2016 just fun nothing but a good time with those ags who’s emailing me no i’m not talking to you don’t worry it wasn’t brent uh let’s see all right anyone else weird questions the audience has dwindled down to six now i feel bad i bored someone well i must have must have must have made someone sleepy all right well if no one has any other questions i’m gonna i’m gonna unlive this thing or try to figure out how oh there there’s a big red button that says end stream all right uh i’m gonna i’m gonna d live myself and go um i don’t know what i’m gonna do i’m gonna practice singing i guess anyway uh thanks everyone for joining me uh i had a good time i’ll see you next week i hope you know god willing and uh yeah thanks lee yes i’ll see you next week and uh go have fun this weekend do do stuff i wouldn’t do yes you have a good weekend too joe all right everyone take care bye
Video Summary
In this video, I explore some quirky aspects of SQL Server maintenance and query optimization. Starting off with a lighthearted joke about webcasts and attendance, I dive into more serious topics like index usage, DMVs (Dynamic Management Views), and the optimizer’s behavior. I share an amusing demo illustrating how certain columns in tables can influence parallel plans, leading to performance differences that are both interesting and somewhat perplexing. The discussion then shifts to backup encryption and restore processes, touching on best practices for securing backups when using cloud storage services like AWS. Throughout the video, I also share some personal insights—like my ongoing mustache-growing challenge and a few funny anecdotes about past projects and experiences with SQL Server. It’s been great engaging with you all, and I hope to see more of your questions in future sessions!
Full Transcript
I sure hope I’m alive. Wee! Alright, let’s get that worked out. And…
What if you threw a webcast and nobody came? Good question, right? I should share this thing on Twitter. Words that…
…the horrible coming out of my face. … …
… … … … all right and we’ll see oh we have a person hello a person there’s all well also a truck out front the truck is my new best friend i guess and i have a bunch of wine openers might ask you know it’s funny these uh these wine openers are functionally the same there is absolutely no difference between any of these wine openers these corkscrews they are identical in every single way right from the backs the fronts but they just have slightly different branding on them which i find very funny so i got two houdinis and a word i can’t read because it’s in too fancy of a script and uh everything about them is completely the same and it reminds me a bit of um like dmv queries in sql server where uh you know they can like they can be wrong you can totally write a wrong query but when you write the right query it’s going to look exactly the same as everyone else’s right query like you can find ways to mess with it like to do something a little bit differently but it’s all being the same dmvs and columns everything else and it’s very strange to think about that that everyone has the exact same set of tools to work with and everyone has the exact same set of tools uh to to look at but people get very territorial about their very special dmv or diagnostic queries and you you would you would act like they invented the thing but they didn’t it’s kind of funny anyway there are there are approximately three of you here one of you is going to say something one of you is going to ask a question like three people in a room one of you has a question about sql server maybe what’s up arthur daniels great post today about the memory grant thing enjoyed that uh something kind of funny that you’ll notice uh with stack overflow is when you’re writing queries against it uh stuff against like the users table that involves about me or the post table that involves uh a few of the columns or like the comments table that involves the text column the text of the comment uh the optimizer is quite biased against parallel plans when you involve when you involve those columns uh i noticed that like the even on 2010 like the the parallel version of the plan that you were looking at was quite quite a bit more expensive than the serial version but uh i’ll send you over a funny demo query specifically for the comments table where if you use like if you use a creation date column play goes parallel everything’s fine but if you use the the comment text column it goes to say serial and everything sucks it’s kind of amusing yes yes indeed indeed indeed we should all these things optimizer is quite quite a strange strange beast used used used to maxed up one oh yeah you use a sap right is it business one that you use mostly or something else there’s a funny delay on these things seven of you god bless does does one of seven people have a question i’m uh i’m busy growing out my various hairs uh you’ve since you’re here you you’ve probably all seen the uh the charity thing that i’m doing where if if i hit a certain dollar amount i’ll i’ll deliver my my regular session at bits not not the not the full pre-con i’m not going to dress like freddie mercury for eight hours i don’t think freddie mercury even dressed like freddie mercury for eight hours but uh if i’ll do the one hour session dressed just in freddie mercury’s get up from the live aid concert uh and so i’m trying to grow out the the mustache and the hair so i can i can look more freddie-ish when i do that going all for it i’m not gonna i’m not gonna get skinny though i don’t think i don’t think i have it i don’t think i have the capability to get to get that skinny but i tried once it didn’t go well i got very dizzy all the time no fake teeth no my my regular teeth are weird enough you put fake teeth in here i don’t know what would happen they’re all over the place you know what’s funny is i actually got uh my wit my wisdom teeth were perfectly fine like they weren’t impacted everything was normal with my wisdom teeth but it was decided that they should get pulled out and everyone who said that you know if you go take your wisdom teeth out it’ll help your like regular teeth straighten out and i did that when i was 19 and here i am now i think like i’m i’m 2 19s in age and my teeth haven’t moved an inch or not even like at all an inch would be huge but like my teeth did not change one bit so everyone lied to me my teeth did not like magically decompress in my gums uh arthur has an interesting question uh have you ever seen an execution plan in cache with just a select operator and zero estimated cost if it helps a query return zero rows and i run the query manually i get a clustered index seek uh so that can happen uh let’s see what’s the what’s the best way some um if and it’s it’s just a select query it’s like it’s not wrapped in like an if exists or like in any sort of if logic or branching logic anyway uh so when if you run the query manually um you might be getting a trivial plan which is which in which case you will get um uh what do you call it there an index seek but if you get a non-trivial plan um yeah so if the cash trivial plan you might be getting a constant scan in there uh which might be weird i don’t know though um um if you want to show me privately what you mean i’d be happy to take a better look at it but it is a little little rusty on that uh lee asks any gutches with backup encryption we’re copying backups to aws now and i’d like to ensure no one can restore it but us uh no no real gutches uh the only real gutches with backups right now are around um uh tde so if you’re if you’re just like if you’re using like like like like the like built-in backup encryption and not using tde and then taking the backups uh so like with tde it gets kind of funky because on versions of cc older than 2016 you would get this kind of weird thing where all of a sudden you couldn’t compress your backups when tde was enabled uh and then you know your backups were huge but after two cc was over 2016 introduced encryption for uh for or reintroduced backup compression for tde enabled databases but there’s been all sorts of crazy bugs uh can i still restore a backup of prod to dev without a bunch of hoops to jump through well initially you’ll have some hoops like you’ll have to make sure that they have the certificate and all that other stuff uh on on the on the dev server but after that it’s pretty easy to pretty easy to do uh and all that stuff is built into oldest scripts so or at least at least on the backup side of course ola doesn’t write a restore script poor ola yeah the trivial yeah the constant scan is a weird thing uh so like what my guess would be that sometimes in prod the query is non-trivial and it might bail out for some reason like there might be some contradiction detection uh so i don’t know lee says he’s using dba tools to generate restore scripts to restore a dev uh i’ve never actually used them for that um i still don’t have a real job where i would have to do backups and restores but um you know hopefully hopefully i’ll avoid that uh but you know if if they have if they have everything built in then i wouldn’t i wouldn’t distrust it the dba tools folks are uh pretty pretty good pretty smart folks pretty good at powershell much better at powershell than i’ll ever be i hope if anyone wants me to learn powershell i don’t know what i’ll do let’s go cry in a corner somewhere that’s what i what i do best anyway cry in a corner very goth all right any other questions any other people want to know about a thing there there are 10 of you here now this many no one asks me anything i’m gonna go back to doodling become quite quite a doodler the last few weeks maybe next week i’ll show you some of my doodles i recorded a video earlier too joe asks is this going to be a regular thing like office hours used to be yeah as long as i can keep it up uh you know it’s it’s just me and um you know so there’s like no coverage so if uh if if the day ever comes when i’m booked with a client friday at noon then i won’t be able to do it but i think as long as uh as long as i can i’ll do it i’ll make it regular well maybe i’ll do it on a different day maybe i’ll you know bring me i just tried fridays because you know when we did office hours on wednesdays people would always be like uh it’s wednesday at noon i can’t do anything fridays at noon people are a little bit more um a little bit more uh relaxed at work i think hopefully so i i started i just decided to do fridays because they seemed like a pretty reasonable day uh have you used the failover detection utility from the tiger team no um you know i it’s not that i don’t think it’s cool i think it’s really cool and i love that they built their they’re built the microsoft is building tools like this uh it’s just been so long since um i’ve even had an ag demo environment with my vms to play with that stuff it’s just it’s just not my main focus this is not what i’m particularly interested in with sql server um it looks great though uh i know a few people excuse me who have used it who do have uh production ag stuff and they are pretty pretty psyched on it um arthur asks on oltp systems are you a fan of compression um you know if if io is an issue sure um it’s if so yes if like so if you’re in a situation with oltp where like uh you have trouble or like even if you have a really big oltp system you know if you have you know if you have trouble either keeping all your data pretty uh you know pretty like a ton of data or if you know uh you if like when you read data from disk it’s you know uh it’s not as fast as you’d like it to be the compression can certainly help there uh as long as you’re not like terribly cpu bound it’s pretty neat also you know it’s one of those things where like you have to be really careful that a like you’re actually getting a compression benefit from things b that the compression isn’t messing with your inserts updates and deletes and see that um you know when it’s it’s not like causing any like not causing like harmful cpu overhead uh it totally has a good place with oltp i’m not against it by any means but you know there are some there are some boxes that you have to check before i think before you go and uh implement it the other thing that kind of stinks about compression is that there’s no way to set it at like the table level or database level or anything like that so like people can go and create a new index and with no compression and then you’re just kind of like stuck yeah if you want to change it you have to rebuild the index but then again that’s one of the reasons that i would support rebuilding an index is if you wanted to add compression lee says i recently compressed a database and the next day one of our nightly ssis packages to that database suddenly jumped five minutes in duration with a lot more logical reads interesting i wonder what did you did you happen to capture what was different about it i mean like i get that you compressed the database and that was different but i wonder what was different about the process or like what what what the process did differently in some way be interesting darren says he always has a cpu worry with compression well no of course nothing changed in the ssi but like the ssis package does stuff in sql server and sometimes those things have execution plans or they have you know things to go differently um you know like so for an example uh there are these funny things called dml request sorts when you modify data and some sometimes those dml request sorts can add a exactly what they sound like a gigantic sort to a query plan and sometimes i would guess that if you had to compress data loading data in a sorted pattern would would help so that if you like if you had an insert that was at one point not doing the the dml request sort and then after you did the compression it was then that might be the clue as far as like the cpu where he goes uh you know i very few times do i see a system that is cpu constrained in a way that like cpus are pegged so like i don’t see a lot of people whose systems are running oltp and they’re at like 99 which is where which is like where i’d be worried about compression where i most people like when they people have cpu which is on a sql server mostly what i see is like they’re running out of worker thread stuff where they’re hitting thread pool weights and compression won’t hit won’t compression compression won’t affect that compression will affect like if you have cpus that are running hot then they’re going to run a little bit hotter with compression not like a ton and says you should have seen your system a couple months ago could take a look now want to pay me the compile rate is super high in our internal apps like the how much when you say compile rate do you mean like queries compiling or do you mean like how long they have like how long it takes them to compile or uh how frequently they compile there’s a lot of questions they have so many questions so many big questions percent compile the batches oh and uh i wonder what changed because obviously a lot of compilations is never a good thing uh did you introduce like some weird dynamic sql or some temp tables or uh i don’t know i’ll tell you one of the funniest reasons uh that i ever saw for a high compile rate was um i forget what the application was i want to say it was like asp.net but all of the queries that went in uh they would they would they would like be this like initial query that would run that would like test to see if the query was valid so it would come in it would be like set fmt only on lower like set browse table on or whatever and then it would run a query that would hit like 70 million roll rows in a table and then it would like change the settings back and all of these queries are coming in and they were just compiling every single time because the the session would start it would run those set options and it would cause a compilation which is really funny to see i was like why why why would you do that silly can you fix parameter sniffing with option recompile uh so that doesn’t actually fix parameter sniffing what that does is it disables parameters sniffing um and while it is you technically a fix sometimes there are underlying plan quality issues that need to be addressed because what option recompile does is it it assumes that the query plan you get when sql server can make a good guess is a good plan and i don’t i don’t buy that a lot of the time i think a lot a lot of the time when people are like oh if i option recompile it fixed the problem but it does it there may still be these weird underlying things that you have to deal with oh well you know it’s it showed up in chat so i felt i figured i’d talk about it sorry i didn’t i didn’t mean that you weren’t funny i just i was i just figured i’d uh i’d talk about option recompile it’s not it’s not like i have a lot of other stuff to talk about i’m pretty boring i already knew a vacuum cleaner it was funny like uh my wife had knocked over this like pink princess unicorn snow globe that my daughter has and it just like it was like shattered glass and plaster and whatever the liquid inside of a snow globe is everywhere and she she used our nice vacuum monster dyson monster thing to to uh to vacuum it up and apparently you shouldn’t vacuum water leave it non-shop back because now like anytime you put one of the attachment heads in it just starts spinning even if you’re not vacuuming so there’s a short in there somewhere so i ordered a new vacuum this morning hopefully that shows up before the floors are covered in disgustingness uh that’s the rest of the way always turn on query store in sql server 2016 plus um no uh there is some observer overhead to it and well i think it’s a very valuable tool and especially if uh so i’ll put it like this if you’re going to use it then you should turn it on and see if there’s an there’s no unacceptable performance decrease with query store i think it’s a really cool thing to to be able to use to track query performance especially over time because the plan cache is such hot garbage at keeping things in it um so i would say that i would i would like to see it turned on more and i would like to see um the various weird bugs around it uh fixed and i would also like to see some more options about where the data is stored with query store i think that’d be nice but uh you know i’d say generally if you’re going to use it and there’s no you know um unacceptable performance overhead to turn it on uh one of my last great posts over on brent’s site was about uh the cruel defaults that uh query store is turned on with so like uh there are collection modes where they define the types of queries that will end up getting collected by query store by default it goes to all and all captures and since this is my channel i get to curse all capture some real bullshit all will capture every single like system query and all this dumb stuff that goes on in the background that who cares but you if you use the collection mode auto that like queries have to meet some certain thresholds they’re not documented so i don’t know what they are but uh they have to um they have to meet certain thresholds before they get collected in query story they’re in like like compile time or like how like you know if like the query took a while to run or something like that so they’re like that turning on auto makes the overhead a little bit less tough but you know it’s it can still be there and arthur was helpful enough to post the link for us thank you arthur you are my you’re my number one junior office hours dba i wish i had a better title for you uh darren says he has an odd question we have a data mart where they rebuild all the data databases are in simple recovery model it seems odd to have no maintenance at all though any thoughts uh no that’s like the perfect perfect time to not have any maintenance if you’re just blowing everything away and loading it back in every night who cares let’s say it’s like a dba’s dream to not have to care about that sort of thing i wouldn’t do any maintenance on that i mean like there’s like there’s like almost no point in backing it up there’s no point in doing index maintenance on it i mean okay i can’t even think of a good reason to run check run check db there i guess i guess check db would be would be okay like if you wanted to run that before you loaded data just to see if like any system tables were corrupt that might be a good idea but i can’t think of a good yeah it’s just isn’t really good if it’s just gonna get like you’re taking a backup of something that’s gonna be gone anyway all right out the door out the door with you see what other maintenance is there i wouldn’t update you know i wouldn’t update stats because i’m pretty sure that like if you like truncate or drop tables and create tables and reload everything i wouldn’t even like you like the stats would update there i mean i guess if you needed i guess if you needed better statistics than what the autumn like the whatever like automatic stats update threshold is then there might be there might be a use case for updating stats there at a higher sampling well they do all right we’re on the same page look at that good job you are whoever designed that is now a full-fledged sql server consultant they should i don’t know i don’t have any i don’t have any certifications i’m sorry if they did if i did i would i would say that they are honorary honorarily certified but i’ve got none none i’m too dumb to pass a test see lee says i have a two new two node ag on standard edition with five ags because i have to use basic availability groups any reasons not to migrate this one ag to enterprise if i do make so uh i’d say cost is the primary thing like how many so you know once you go from standard to enterprise your cost jumps up pretty significantly it’s a five thousand dollar per court difference if you do migrate any suggested method uh well if you’re migrating to like if you’re on uh windows server 2016 i think you can make it make it a bit more seamless but i think like i know with ags there’s some weird stuff from like the windows server the windows server level needing to match and then like the sql server level needing to match there’s weird stuff involved there oh if you have i mean if you if you already have it paid for then yeah i’d probably move uh if you need to move to like like a net new environment i would use uh like log shipping or something i wouldn’t use mirroring in this case because you’re going from standard to enterprise and uh you know you obviously you already have an ag setup so that would be rather awkward to try and implement so um i i would i would say to just if you wanted to use log shipping and just uh get your current databases moved over to whatever new vm hardware fake hardware whatever virtual hardware uh you’re going to set up then that’s probably how i’d move things just to just to make the transition a bit more seamless and that’s assuming that you couldn’t you know set up another vm to you know get the ag also synchronizing to and then fail over to that and then fail over to whatever’s next and then destroy the old environment yeah that’s tough so uh at least he’s trying to simplify things so i don’t have so many ags to manage and check like when we reboot for us yeah that’s a bummer um you know that’s one of the reasons why you know i i asked the question again ah man all my all my all my solid gold material is still still on brent’s site but uh you know i i had a poster over there recently about like have ags gotten any easier and i think you know when it comes to stuff like patching and um you know so and even something like you know just doing uh like uh checking out indexes in an ag right so you know you have the stuff on the primary and you check like index metrics there and you know you can see that like i have a bunch of unused indexes but they get used on a secondary if you’re offloading reads so yeah you know there’s still a lot of stuff that’s tough about ags and patching is certainly one of those very perilous things because with ags you know in particular like there are a ton of patches i mean there are a ton of fixes in each patch specifically for ag stuff and ag patches have been known to break stuff too so it’s like you’re very perilous you know it’s like whatever i’d say whatever you get um whatever you what do we have for your ags make sure that you know when you do those patches you’re testing them in a lower environment first because boy howdy if one of those patches breaks something you have to uninstall a patch uninstalling a patch is my one of my least favorite things in the world because i always forget it’s the best way to i know there’s like there’s like a few ways to do it and there’s like one of them that’s like the best way to do it and i always forget which one it is yeah daren mentions you might be able to do a rolling upgrade if you can do a rolling upgrade great if you’re on like windows server 2012 or something though i don’t think you can do the you can’t do a rolling upgrade if you’re moving to like windows server 16 or 19 down the line so it would have to be at least 2016 just fun nothing but a good time with those ags who’s emailing me no i’m not talking to you don’t worry it wasn’t brent uh let’s see all right anyone else weird questions the audience has dwindled down to six now i feel bad i bored someone well i must have must have must have made someone sleepy all right well if no one has any other questions i’m gonna i’m gonna unlive this thing or try to figure out how oh there there’s a big red button that says end stream all right uh i’m gonna i’m gonna d live myself and go um i don’t know what i’m gonna do i’m gonna practice singing i guess anyway uh thanks everyone for joining me uh i had a good time i’ll see you next week i hope you know god willing and uh yeah thanks lee yes i’ll see you next week and uh go have fun this weekend do do stuff i wouldn’t do yes you have a good weekend too joe all right everyone take care bye
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.
Erik,
This is what i get when I try to run the video from your blog.
Video unavailable
Watch this video on YouTube.
Playback on other websites has been disabled by the video owner.
That’s strange, I have everything set to totally unrestricted. Can you try it from another browser or device?
Thanks!
Name ideas:
Erik’s Execution Plans
Erik explains it all
select data from Erik.Darling
Erik’s Extemporaneous Exclamations