SQL Server Performance Office Hours Episode 69
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:02:15 – Office Hours Promo
- 00:04:05 – Personal Interests
- 00:06:06 – TV Shows
- 00:07:39 – EXISTS vs JOIN
- 00:08:45 – Reducing Compile Time
Full Transcript
Erik Darling here, with Darling Data, for another exciting episode of Office Hours, and that is where I wake up early in the morning and I answer five user-submitted questions, and if you want to ask your own question, you can find a link to do that down in the video description, that’s where that lives. There’s a link there with Office Hours, right in the words, so it’s very easy to figure out where to go to ask a question. On your way to find that link, it’s not surprising, you will find all sorts of other helpful links, where you can hire me for consulting, you can purchase my training videos, which are arguably the best SQL Server training on the internet.
You can support this YouTube channel with as few as $4 a month. I believe you could also do up to $10 a month, if you’re feeling particularly generous. Perhaps you’ve gotten lucky with the lottery, a relative has died, something along those lines, and you’re just feeling like spreading the joy.
And of course, if you don’t feel like doing any of those things, or you are not so monetarily inclined towards me, then you can, of course, just do the usual liking, subscribing, and telling of friends. If you would like a free trial of Office Hours, I’d be happy to help you.
you can go get it and you can start monitoring the performance of your sql servers uh in a way that is just as good as what all the paid tools do i’m adding some very exciting stuff to that right now it’ll be out in the next release um hoping that’ll be this week but we have to we have to tidy some things up first have to make sure that everything is working correctly before we go and do that but uh anyway um i’m skipping the the the uh the speaking promo slide because at this point i have nothing for several months so uh if something new comes up who knows right if some exciting opportunity arises uh then by gosh i will go ahead and do that but uh for now we’re just gonna just gonna admire our strange databases going crazy in a field with allergies anyway that’s enough of that uh we need to go to the excel file and we need to uh talk about this um does the option for in-memory tempdb make sorts faster i’ve never seen that claim before well sort sorts don’t always use tempdb if they if they spill the tempdb i suppose it won’t make the spill any faster but uh it used to be on older versions of sql server you could run into tempdb contention from lots of queries running at the same time and spilling so at least that goes away but uh no no that that that doesn’t do anything i forgot to highlight that was this question here that i was just answering uh let’s see here select uh someone’s being funny ah someone from a foreign land is being funny i see that you in color all right uh select count from eric’s closet at least you spelled my name right where type equals t-dash shirt and color equal color equals black and logo equals ad well i i do not have any t-shirts that are not black and i do not have any t-shirts that do not say adidas on them so uh really it’s just and i keep all my t-shirts in a drawer not in the closet i if i hung these things up it would look insane i send my laundry out get it folded perfect squares and i put it in a drawer and everything works out pretty well um i believe i have somewhere around 20 of these that i i wear it’s at various points wear them everywhere go to the gym wear them all day when i work don’t have to think about anything it’s wonderful wonderful wonderful wonderful eric tell us about you where do you live i live in new york city that one uh what do you like to do in your spare time well uh i have some probably some rather generic interests i i do like uh going to restaurants and i like traveling i like going to museums and hanging out with my family so generically that apart from that the only the only real i think hobby that i have uh is is barbell training um which i’m equally dedicated to uh is as databases at least i hope i am so that that’s that’s about me in a nutshell i’m a rather rather simple rather simple man heard that’s the way to be all right uh oh wait there’s another one what tv shows do you like oh well that’s where things get interesting isn’t it um i spend a lot of time watching um nostalgic television uh from my youth uh like cheers uh like the x-files i recently uh re-watched all of the nanny that was a very good time friend dresser in the 90s that’s a that’s a tough one to top man uh i don’t know uh i think 30 rock is probably about the funniest tv show i’ve ever seen uh community had a pretty good run um uh yeah i don’t know stuff like that you know up my alley i like i like weird sci-fi shows been watching uh widow’s bay lately uh forget what i forget where that’s streaming on but that that’s been that’s been a nice treat that’s been it’s been a good pretty good show i think so far but that’s the type of stuff that i enjoy um i attempted to watch the last season of euphoria but it was some of the worst television that i’ve ever seen in my life so ah i just i just read the spoilers anyway let’s see here uh is it bad to nest exist statements within each other rather than having one big select with lots of inner joins not on the face of it no uh i’m okay with nesting exists i think that’s perfectly okay with me no but i’ve never i’ve never found anything that is pathologically wrong with doing that but of course you know running the query is the real tale of the tape here is if if you are able to nest your exists and and get a good fast query with a reasonable query plan then keep on doing it if you need to switch things around i understand i’ve had to do plenty of switching around in my life uh wouldn’t be the first time wouldn’t be the last time but you know just you know i think the the thing with exists is uh you know they are i think generally a little bit more sensitive to indexing uh especially uh given the the row goals that often get introduced uh with them and the optimizers uh i don’t even know that i’d call it a preference but you know when of course the difference between join and exists is that you know exists only cares if a row is there or not joins find every match in like a one-to-many relationship or a many-to-many relationship so um you know the the row goal that often gets introduced there can can certainly inflict some weird plan stuff um the other thing that i find is that if uh the stuff that you’re checking the existence of if it is uh rare data if it is data that is not regularly occurring in your database you can get uh some some pretty choppy execution plans from all that so uh of course look at your execution plans i mean you have my blessing to try these all right so let’s get started with our try these things you just you just have to do the performance testing yourself unless you choose to hire me in which case i can do that for you oh boy i have a large view about 900 lines it’s pretty large right charles barkley said that’s like one of those san antonio ladies uh with about 60 60 joins that take six seconds to compile you are lucky it only takes six seconds to compile that’s that’s like one second for every 10 joints uh the business logic makes it near impossible to break this into smaller chunks how can i reduce the compile time well um i disagree with the business logic making it near impossible to break this into smaller chunks um even in the case where you need to uh like sort of you have like junction table joins where you have to do things uh it it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s quite possible to break these things down uh the first thing i’ll say is that if if this is what you’re dealing with i would i would like the my first instinct is to say that you have chosen the incorrect vehicle for this logic um that’s that’s a that’s a lot of action for one query unless things are real carefully done really carefully done um if they’re all inner joins you could create an indexed view um if if if they’re not all inner joins you could take whatever inner joins are in there and make an indexed view and you could create an indexed view and you could create an indexed view that would at least reduce some of it you’re going to you’re really going to want to lean on the no expand hint there um you could you could force all the queries that hit it to run with option force order so that sql server does not think about uh the ordering of 60 joins but you would have to very carefully write those joins in the order that sql server that gives sql server the best execution plan for them uh you would have to look at the exit like a fast execution plan for this thing uh you would have to figure out the execution plan for this thing in the exact order that sql server joins the tables together in and then you would have to write your joins in that order you could try that weird bushy join syntax but i don’t know that that’s really going to value much of anything um it’s it’s something to think about but it’s for me even that’s it’s a dicey proposition um i i if i if the first thing that i would do um and and this is this is a good use case for uh the the ais is i would i would probably feed that query to them and and ask them uh how you could if you could break it up to some degree um i do believe that the appropriate vehicle for something like this often is a stored procedure uh where you can dump little bits of things into temp tables and then carry on from there but having never seen your your view i don’t know exactly i don’t know precisely what is possible uh under under the local conditions that you have in your database anyway that is five questions you’re welcome thank you for watching i hope you enjoyed yourselves i hope you learned something and i will see you in tomorrow’s video where we are going to continue uh the learn t-sql with eric material from my course which you can buy from the video description for 100 bucks off uh we’re gonna start talking about dates and date math and time zones and stuff so got a lot to look forward to in there don’t we sure do all right thank you for watching
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.