ICYMI
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Video Summary
In this video, I dive into some of the challenges and quirks that come with working in SQL Server, particularly focusing on entity framework queries and parameter sniffing issues. I share my experiences dealing with an entity framework-heavy workload and discuss how custom types can sometimes cause more problems than they solve. The video also delves into a question from a DBA who is outnumbered by web developers using entity framework and struggling to balance their approach. I offer advice on supporting new developers while guiding them toward best practices, emphasizing the importance of letting people learn through experience. Additionally, I address live query plans, sharing my limited but positive experiences with them, especially in demos where they can help identify bottlenecks. The video concludes with a few miscellaneous questions and observations, including some humorous remarks about technology and personal quirks like waking up at 4 AM to watch videos.
Full Transcript
All right. So… apparently YouTube decided to change which microphone it would use for no reason. You know, why not? Why… why? why not just leave a good thing alone just mess with it right mess with it the fun thing is once you’re alive you can’t apparently switch which microphone you want to use you can hear me good wonderful apparently youtube’s definition of live is far different from my definition of live my definition of live is i can be seen and heard and poked and prodded and hugged and loved but according to youtube apparently if i’m just babbling to nothing i’m still live i don’t know why my my webcam has a microphone built in so at bare minimum you should have been able to hear extreme potato quality audio from me but now we’re we’re back here back with a good one all right so now that i’ve accomplished my mission you should accomplish your mission and ask a question any of you because i know i know that there are a lot of people out there who are varying degrees of new to sql server and you probably have really interesting fun questions and i’m willing to come come out here and and answer those questions in exchange for no upvotes whatsoever so that’s fun it’s fun i get no upvotes for this youtube thumbs don’t count they’re meaningless no one’s no one’s um uh no one’s gonna care about youtube thumbs up anyway i finally did get a question via another means over over the past week uh it’s from uh a dba who will remain anonymous from new zealand uh and they had they had a fun question and it’s it’s a question that actually strikes pretty close to home this week because uh i’ve been dealing with an entity framework heavy uh sql server workload and if i can tell you one thing about entity framework queries it’s that whatever mechanism generates those things was i mean it can really only have been conceived by satan himself there’s no other way to explain it ordering things by a row number rather than just saying order by a column doing a left self join left outer self join with a where clause that led to this weird row count spool like this the things that i’ve seen this week are baffling and what’s what’s even more fun is that because uh no one who uses entity framework has any self-control whatsoever they’ve managed to select every single column in every single table and so every memory grant is nine ten sometimes 15 gigs and only like 128 megs get used it’s like every time someone threatens me with a self-tuning database i i see entity framework queries and i’m like we’re going to europe next summer and the summer after that you can escape people will find the worst possible way to use a technology every single time i i just if you start designing your applications assuming that users are both stupid and malicious i’m a stupid user i will do dumb things and i will find weird things that happen when when people do dumb things like oh i’m kind of dumb so that makes sense but malicious users people like paul white will go out of their way to find bugs that you should have been smart enough to not not have in there in the first place so there’s that to look forward to anyway peter says custom type question i spammed at you in chat with that obvi parameter sniffing otherwise caused by local vars in a proc nope nope custom types uh don’t help they are just like and var cars or bar cars or whatever like when you make a custom type it’s quite often like you don’t want people messing with it i see this a lot in applications where um well actually in one particular application i see this quite a bit but the whole point is that you know they have very um strictly defined types for their data types and so they’ll create custom types that are like far car 11 or car 15 or in decimal 18 2 so that people when they need to like like uh operate with columns and tables that you know have have those that match up to those those uh those custom types they know exactly which one to use so no unfortunately oh boy there’s a bunch of emails coming in all right all right okay i gotta get rid of those don’t want you reading those emails in my glasses zooming in anyway uh so back to entity framework what a nightmare it is and all that other stuff uh a dba who will remain anonymous we’ll call him uh no let’s just say anonymous i like that one anonymous is okay by me i’m a dba in a small town in a small island country that is quite frequently left off maps because people forget it exists i watch your your uh your videos on saturday mornings but i don’t want to get up at 4 a.m to ask a question i understand i don’t want to get up at 4 a.m for anything yet i end up getting up at 4 a.m quite often i don’t know why no matter what time i go to bed i tend to wake up at 4 a.m and feel the urge to start working i think of something i want to do and i can’t fall back asleep anyway the question says i’m the only dba in a team working working on a team working with a team of eight web developers you sir are outnumbered in a terrible way and i i i just i don’t want to answer this question as much as i want to send you weapons i want to send you lawyers guns and money to fight off these these eight criminal elements uh but anyway he’s saying they use entity framework and store procedures so they don’t just beat the tar out of sql server with with entity framework queries there’s some some sanity involved here uh and the team is usually reasonable when it comes to changing over from entity framework to store procedures when it’s appropriate because there’s just no way to get any framework to do what you want it to do uh but there’s a new developer starting there’s a new kid in town and uh the new hire feels very strongly that as much of the database model as possible should be generated generated by entity framework he’s about to be responsible for redesigning one of our databases and i’m worried he will use this as a push for poorly designed entity framework generated database schemas and not be receptive to feedback i plan to discuss my concerns with the development team lead is this something you’d be concerned about well of course of course that’s something that i would be concerned about and it’s i would be but here’s the thing you need to let people fail you need to let people try and fail and you need to not sabotage them and you need to not root for them to fail but you need to be supportive when they do and that’s the only way that you’re going to get people over to your side which is a reasonable side that you know people that the entity framework is not a panacea for database problems entity framework is not the end-all be-all of working with the database it’s great for developers who don’t know sql and don’t want to learn sql and they just want the things that they put into code to show up in an application in a reasonable amount of time so with this new new person who’s going to redesign things you should be supportive if they want to if they want if that’s how that’s the road they want to go well sometimes the only lesson learned is through experience which is which is a shame but sometimes that’s the only way to do it i wouldn’t start off arguing with them i wouldn’t start off uh trying to dissuade them from things i would just i would let them work and make progress and if they hit road bumps be there to help them get over those bumps maybe show them a way to get over those bumps that’s not entity framework then who knows maybe maybe they’re an excellent developer maybe they are the world’s foremost entity framework developer on a small island nation that of that frequently gets forgotten from maps and maybe maybe they can do this whole thing without without you messing with them it’s an entirely plausible scenario. I mean, I know it’s not going to happen, but you know, whatever. Anyway, that’s my answer for it. I think that it’s going to be a, I mean, just don’t make it a touchy political issue. Just say it’s a technology issue. People have to be aware of the limits and the good uses and bad uses of a technology. And, you know, if they try to start square pegging stuff, just say they’re square pegging stuff. Help them out.
Help them come to a reasonable place in the world. That’s the way I’d do it, you know? Anyway. Someone else from a small island, from that same small island nation that frequently gets forgotten from maps has asked a question. How useful do you find live query plans?
Not so much. You know, I, I, I’ve never used a live query plan to troubleshoot a problem. Mostly because the people who I work with are not on a version of SQL Server that has live query plans in them. I, I would use them. The only time I’ve really used them to any great extent is locally when I’m trying to write a demo demo and I’ve wrote, I’ve written too good of a demo and it’s dragged on for like five minutes or so. And I’m thinking, well, what is this plan doing? Cause I want to see, I want to see what, what’s happening in here. And so I’ll rather like live query plans are pretty useful for just seeing like, okay, where, where are we held up here?
What, what do I need to do to make this demo a little less good? And, you know, that’s about where I stick with them. I re I really like the, the new management studio version 18 query plans because they have the, I mean, they don’t have, they don’t show you the query as it progresses.
Right. They’re not, you’re not seeing the plan. You’re not like you don’t hit F5 or whatever button combination you choose and see like the execution line doesn’t immediately pop up and start running. And you don’t see like rows as they flow through and do things. And as parts get completed, the little, little, the little line arrows turn into like solid. You don’t get all that.
You don’t, you don’t get all that. But when the, when the query is done, you do get, um, uh, you do get the final plan with mostly coherent operator times attached. Of course, if you, that varies a bit between row mode and batch mode. If you’ve watched my 10 minute video that, uh, a certain intergalactic celestial being from a small island nation that frequently gets left off maps had pointed out is well worth the 10 minutes of your time. Then you may be, maybe, maybe, maybe.
Well, no, no. Why? Uh, anyway, the thing I really hate about live query plans is how often they crash management studio, but that might not be a live, live query plan problem. That might be a management studio problem. Sometimes I’m like you hit it. You get the query running and then you hit cancel and you’d like, can’t do anything to that window.
If you try to close the window, management studio crashes. If you let it go, it’ll go on forever. And like you’ll wait until you restart. It’s a nightmare. Not, not my favorite way to spend time interacting with SQL Server management studio. God’s favorite 32 bit application. I hear, I hear management studio was God’s 30 favorite 32 bit application. Marcy asks, are you wearing Swannies? Marcy, I’m going to level with you.
I don’t know what a Swannies is. Enlighten me. What is a Swannies? Darren Scott says those scope identity in the where clause were from EF as well. Good heavens. One of my favorite things that I see in some applications is where, um, when you, uh, when certain people, so like if you have an application that needs to deal with people who access things from multiple sites, right? Like say you have sites A, B, and C. And you will have, uh, a column in the table that denotes site A, B, and C. And when different users connect in, they generate context information so that the application knows which site to point them to. And so none of the tables are directly referenced in the queries. Every single table is wrapped in a view with where column equals context info, the little context info function. So user, every single user before they touch it, like they have to get routed sort of via this function to which part of the table they should be looking at. It’s a very weird way to see things. Very strange. Anyway, uh, Paul something or other says people wouldn’t use EF if it weren’t for good stuff. Yeah. Would they? Yeah. I know a fair amount of people who use heroin. I mean, I know that’s an extreme example. I know a fair amount of people who like country music. That’s an even more extreme example. You know, a fair amount of people who wear flip-flops to walk around New York City.
Jandals, if you will, if you haven’t been wiped off the map yet. I know a fair amount of people who have made very, very dumb decisions in life. I have hand and neck tattoo. That was also a weird idea, weird choice. Anyway, what can you do? What can you do at this late stage? I can only go further. I can only wait for my mother to die so I can get face tattoos.
Otherwise she’d kill me. Let’s see. Forest, Forest of McDaniel says, when do you find CPU stats from query stats DMVs the least trustworthy? Before a query has finished. Soon nothing is there. It’s lying to you. I don’t know. I’ve never, I mean, I take, I take it all with a grain of salt. You know, I know when I write blog posts and when I write training material and, you know, talk about SQL Server generally, it’s, you know, you have to correlate things a little bit.
Right? Like you don’t want to just point to one thing and say, this is the God’s honest truth about what happened. You want to look at, like, I always want to look at a few different, this is why I’m such a big fan of new query plans, getting the operator times and the CPU time and all the other kind of cool lightweight profiling stuff that we’re getting in newer versions of SQL Server and management studio.
Because it gives you multiple sources of truth, right? You can multiple sources to check, to verify, to make sure that the thing you’re seeing holds up. All right? So there’s lots of different places that you can, you can look to validate things.
You know, it’s like, it, like, there’s no reason right now. There’s no, like, unless you, like, pull the crap out of that query profile DMV that does the live query plan thing, you can’t really validate, like, the per operator stuff too easily. You can validate, like, the overall plan time stuff pretty easily. But that’s, that’s, that’s about it.
But I, I really, I really like generally the, the amount of new feedback that we’re getting, even if it’s not all down to the microsecond or whatever new unit of time Microsoft has invented to measure things to confuse us. I hear that they’re going to invent satya seconds, which are sub nanoseconds that are only, they’re only, they’re only reliable to the closest microsecond.
That’s that. I don’t know. I think everything’s unreliable. Look, even YouTube’s unreliable. YouTube screwed me here.
Change my microphone over at the beginning of a webcast. Why would you do that? Why would you do that? I’d say audio settings have changed. Why? Dummies. Marcy says, Swanwick blue light blocking glasses. No, no. These are, these are Ray-Bans. They’re a little bit old. I should probably get a new pair of glasses soon. I just particularly like these.
I’ve grown used to them. I’ve grown fond to them on, on, on being on my face. Unlike this beard. This beard’s gone. No more of that. Max Vernon says, the tats lend a bit of a cachet. I bet people tend to listen to you in person.
I don’t know. It’s, it’s, it’s funny. Like when I was younger and I hung out with people who had tattoos, having more tattoos gave you like sort of weird social status. Right. If you had, like, if you have more tattoos on someone, you, you can be like, yeah, when are you going to get that tattoo?
Like, if I, like, I was able to do my neck tattoo, be like, well, when are you going to get your neck tattoo? Wimp, you know, like make point, like, I don’t see you with hand tattoos. What’s up with that? Right. Like, there’s like some of that, like now that I hang out, a bunch of people who like, you know, tuck their t-shirt into their underwear. The, the social, social ladder is all flipped around. I’m like no one in some of these conversations, you know, you get, you get a bunch of people sitting around a table and they want to talk about, you know, something other than the, the, the small chunk of query tuning that I enjoy.
No one’s listening to me. No one wants to hear me talk about containers. No one wants to hear me talk about availability groups. No one wants to hear me talk about taking backups. I don’t want to hear me talk about taking backups. You know how dull that is?
Ugh. Nothing to do with it. Let’s check TB scheduling. Get out of here. Talk to someone who cares. You know, the only time I care about that because.
Oh, screw it. There’s a, there’s another question here. What do you think about implicit conversions and plan warnings more generally? Well, golly and gosh. Eating a lot of dinner because of implicit conversions.
Can’t complain too much about those. A lot of dinners. A lot of dinners. A lot of wine because of implicit conversions. And I’m pretty sure you’ve gotten some wine because of implicit conversions. My friend. What do I think? What do I think? I mean, if, if someone, if, if you have people out in the world who can’t be bothered to, to match data types, like what, what, what, what help is there for them?
They like this pick something randomly and compare them to random things and compare them. Like, I want to know if this knife matches his pen. What do you tell those people? What can you tell them?
I want to know if this fork is a notebook. What do you tell them? There’s no sense. There’s no, if, if people are, are that dead set on not caring, I will happily take their money to tell them they should care. That’s it.
Plan warnings more generally. We get, we get warned about the wrong things. We get warnings. We get, first off, the, the, the, the implicit conversion warnings are sometimes full of dookie. Right?
Like if, like one example that I show when I talk about them is like, if you have an integer column and you say, like, select, I let’s, let’s call it ID. Select ID as VARCAR 10. You will get an implicit conversion warning saying that the cardinality estimate of your query is incorrect.
You’ll also get an implicit conversion warnings for seek plan when there’s no index that you could possibly seek into. Right? Like let’s just say that I have, for example, another column called reputation.
And I say, select star from users where reputation equals a variable that’s, and then VARCAR something. Right? And I don’t have an index on reputation. SQL Server will say, well, you could have done a seek plan, dummy. And you couldn’t because there was no index on it.
There was nothing for you to seek into. Nothing there. Another pet peeve is the no join predicate warning, which is that, would you get that big red, can you imagine the things that Microsoft should put a big red X over in query plans? Like every spool?
Like every single spool should just have a giant red X over it. But no, we get, we get warned when completely reasonably written queries with a join predicate. I say you don’t have a join predicate. Okay.
You win. And those memory grant warnings, which may affect the reliability of me to stay sober. Right? He’s like, you’re, you’re, you had a thousand and 24 KB memory grant and you only used 176 KB of it. May affect the reliability.
I love that. I love that sentence. You know what? If there’s one fragmentation that I worry about in SQL Server, it is, it is sentence fragmentation. And these warnings affect, it may impact the reliability. It’s just like, okay, tell me more.
Tell me more. I would love to hear more. Oh God. Where are some other ones? Where are some other funny ones? Oh boy. I don’t know.
I think spills are overrated. I think spills, I think, I don’t think you should get a spill warning unless you are like really spilling. You should. Yeah.
Unmatched indexes. That was a fun one. It was, it was fun to find out that if you have a plan that’s auto parameterized, you’ll get an unmatched index warning. When, even though, even if you use the index, like you can see the index being used in the plan. But if your plan is auto parameterized or simple parameterized, whatever you want to call it, you get a warning.
We couldn’t match that index to anything. But I see you. I see you. It’s aggravating.
Aggravating. It’s like, if you could, if you could go back and start over with like the query plan, XML. Hey, would you even use XML?
Use JSON. I would, I would much rather just get like a, like an MS paint. It’s like, give me a PNG of the plans. Don’t make me use the XML. It’s like, write everything down for me. Write everything down.
Marion asks if I think Paul is real. Well, he better be. Or else, someone else has my old laptop. As far as I know, he is. Yes, plans rendered by entity framework. That would be, that would be ideal.
Because then you would never get a query plan. And you would never be able to fix anything. And you would be able to happily learn how to do something else for a living. Go learn, go learn C sharp. Go learn, go write, write a great novel.
Go write the next great novel for your country. Where is it? Next great American novel, Canadian novel. New Zealand. I mean, let’s, no one in Australia is writing a novel. Get eaten by dingoes anyway.
Dingo ate my novel. I don’t know. I often think about what I would, things that I would much rather be doing than SQL Server. The list keeps growing long.
Fun. Marcy says, speaking of weird things and plans, is there a way to keep a detail pop-up open if I want to move my cursor to another screen? No.
No. Yes.
Of plan connecting lines and all that number of rose red malarkey. Yeah, that’s, you know. What bothers me is something that I learned from you about cash plans where they are just stitched together from many different plans. So, like, when you have a plan, you see, like, a plan in your plan cache or, like, an estimated plan where you have, like, thin lines and thin lines and thin lines.
And then you hit one operator and there’s a very, very thick line coming out of that operator for some reason. And the estimate goes from, like, three rows to, like, 40 billion rows. That’s just the optimizer being kooky.
Putting the plan in cache that might have been a few other plans stapled together. Dreadful. Marcy says, I don’t know how to get a screenshot of those. They disappear as soon as I click anywhere else, like on the snipping tool.
So, I use Snagit. S-N-A-G-I-T. I use Snagit. And when I have my cursor hovering over an operator so I get the tooltip, I can just hit the print screen button. And that’ll trigger the screenshotting of my screen.
And I can zoom in on the tooltip. Snagit is great for that. It’s well worth, I forget, like, 25 bucks or something. And then, like, the occasional maintenance contract so that you get updates. Well worth it.
The other upside of Snagit is that it has, like, all sorts, like, it has, like, an editor built in. So, you can do all sorts of cool stuff with things you take pictures of. You can, like, do, like, the crop where, like, you cut out, like, a certain part either horizontally or vertically. You can draw shapes.
They have the most accusatory built-in arrows. So, if you really want to call attention to something and be like, this sucks, the arrows they have are wonderful for that. Like, some of them, like, get bigger. They, like, taper out and, like, get huge arrowheads. Awesome with that.
Good accusatory arrow really makes for a good presentation. Snagit’s good. They don’t even pay me to say that. I won’t get a coupon. No coupons for me, unfortunately.
I am surprisingly, I have never been asked to be, like, a brand evangelist. Oh, yeah. That’s my story.
Anyway, we have hit the 30-minute mark. I have babbled at you long enough. It’s getting hot in here again. I haven’t, I might have noticed I haven’t complained about it being hot this week because I plugged my air conditioners in. But the air conditioners are out there. When my door is closed, this room just slowly gets hotter and hotter because I have, like, 90 inches of screens hanging around me.
Anyway, I’m going to get going. I’m going to go get back to work. Hopefully make some money. And I will see you next week. Thank you for coming. I hope you enjoyed it.
Thank you for the great questions, and I will see you next time.
Video Summary
In this video, I dive into some of the challenges and quirks that come with working in SQL Server, particularly focusing on entity framework queries and parameter sniffing issues. I share my experiences dealing with an entity framework-heavy workload and discuss how custom types can sometimes cause more problems than they solve. The video also delves into a question from a DBA who is outnumbered by web developers using entity framework and struggling to balance their approach. I offer advice on supporting new developers while guiding them toward best practices, emphasizing the importance of letting people learn through experience. Additionally, I address live query plans, sharing my limited but positive experiences with them, especially in demos where they can help identify bottlenecks. The video concludes with a few miscellaneous questions and observations, including some humorous remarks about technology and personal quirks like waking up at 4 AM to watch videos.
Full Transcript
All right. So… apparently YouTube decided to change which microphone it would use for no reason. You know, why not? Why… why? why not just leave a good thing alone just mess with it right mess with it the fun thing is once you’re alive you can’t apparently switch which microphone you want to use you can hear me good wonderful apparently youtube’s definition of live is far different from my definition of live my definition of live is i can be seen and heard and poked and prodded and hugged and loved but according to youtube apparently if i’m just babbling to nothing i’m still live i don’t know why my my webcam has a microphone built in so at bare minimum you should have been able to hear extreme potato quality audio from me but now we’re we’re back here back with a good one all right so now that i’ve accomplished my mission you should accomplish your mission and ask a question any of you because i know i know that there are a lot of people out there who are varying degrees of new to sql server and you probably have really interesting fun questions and i’m willing to come come out here and and answer those questions in exchange for no upvotes whatsoever so that’s fun it’s fun i get no upvotes for this youtube thumbs don’t count they’re meaningless no one’s no one’s um uh no one’s gonna care about youtube thumbs up anyway i finally did get a question via another means over over the past week uh it’s from uh a dba who will remain anonymous from new zealand uh and they had they had a fun question and it’s it’s a question that actually strikes pretty close to home this week because uh i’ve been dealing with an entity framework heavy uh sql server workload and if i can tell you one thing about entity framework queries it’s that whatever mechanism generates those things was i mean it can really only have been conceived by satan himself there’s no other way to explain it ordering things by a row number rather than just saying order by a column doing a left self join left outer self join with a where clause that led to this weird row count spool like this the things that i’ve seen this week are baffling and what’s what’s even more fun is that because uh no one who uses entity framework has any self-control whatsoever they’ve managed to select every single column in every single table and so every memory grant is nine ten sometimes 15 gigs and only like 128 megs get used it’s like every time someone threatens me with a self-tuning database i i see entity framework queries and i’m like we’re going to europe next summer and the summer after that you can escape people will find the worst possible way to use a technology every single time i i just if you start designing your applications assuming that users are both stupid and malicious i’m a stupid user i will do dumb things and i will find weird things that happen when when people do dumb things like oh i’m kind of dumb so that makes sense but malicious users people like paul white will go out of their way to find bugs that you should have been smart enough to not not have in there in the first place so there’s that to look forward to anyway peter says custom type question i spammed at you in chat with that obvi parameter sniffing otherwise caused by local vars in a proc nope nope custom types uh don’t help they are just like and var cars or bar cars or whatever like when you make a custom type it’s quite often like you don’t want people messing with it i see this a lot in applications where um well actually in one particular application i see this quite a bit but the whole point is that you know they have very um strictly defined types for their data types and so they’ll create custom types that are like far car 11 or car 15 or in decimal 18 2 so that people when they need to like like uh operate with columns and tables that you know have have those that match up to those those uh those custom types they know exactly which one to use so no unfortunately oh boy there’s a bunch of emails coming in all right all right okay i gotta get rid of those don’t want you reading those emails in my glasses zooming in anyway uh so back to entity framework what a nightmare it is and all that other stuff uh a dba who will remain anonymous we’ll call him uh no let’s just say anonymous i like that one anonymous is okay by me i’m a dba in a small town in a small island country that is quite frequently left off maps because people forget it exists i watch your your uh your videos on saturday mornings but i don’t want to get up at 4 a.m to ask a question i understand i don’t want to get up at 4 a.m for anything yet i end up getting up at 4 a.m quite often i don’t know why no matter what time i go to bed i tend to wake up at 4 a.m and feel the urge to start working i think of something i want to do and i can’t fall back asleep anyway the question says i’m the only dba in a team working working on a team working with a team of eight web developers you sir are outnumbered in a terrible way and i i i just i don’t want to answer this question as much as i want to send you weapons i want to send you lawyers guns and money to fight off these these eight criminal elements uh but anyway he’s saying they use entity framework and store procedures so they don’t just beat the tar out of sql server with with entity framework queries there’s some some sanity involved here uh and the team is usually reasonable when it comes to changing over from entity framework to store procedures when it’s appropriate because there’s just no way to get any framework to do what you want it to do uh but there’s a new developer starting there’s a new kid in town and uh the new hire feels very strongly that as much of the database model as possible should be generated generated by entity framework he’s about to be responsible for redesigning one of our databases and i’m worried he will use this as a push for poorly designed entity framework generated database schemas and not be receptive to feedback i plan to discuss my concerns with the development team lead is this something you’d be concerned about well of course of course that’s something that i would be concerned about and it’s i would be but here’s the thing you need to let people fail you need to let people try and fail and you need to not sabotage them and you need to not root for them to fail but you need to be supportive when they do and that’s the only way that you’re going to get people over to your side which is a reasonable side that you know people that the entity framework is not a panacea for database problems entity framework is not the end-all be-all of working with the database it’s great for developers who don’t know sql and don’t want to learn sql and they just want the things that they put into code to show up in an application in a reasonable amount of time so with this new new person who’s going to redesign things you should be supportive if they want to if they want if that’s how that’s the road they want to go well sometimes the only lesson learned is through experience which is which is a shame but sometimes that’s the only way to do it i wouldn’t start off arguing with them i wouldn’t start off uh trying to dissuade them from things i would just i would let them work and make progress and if they hit road bumps be there to help them get over those bumps maybe show them a way to get over those bumps that’s not entity framework then who knows maybe maybe they’re an excellent developer maybe they are the world’s foremost entity framework developer on a small island nation that of that frequently gets forgotten from maps and maybe maybe they can do this whole thing without without you messing with them it’s an entirely plausible scenario. I mean, I know it’s not going to happen, but you know, whatever. Anyway, that’s my answer for it. I think that it’s going to be a, I mean, just don’t make it a touchy political issue. Just say it’s a technology issue. People have to be aware of the limits and the good uses and bad uses of a technology. And, you know, if they try to start square pegging stuff, just say they’re square pegging stuff. Help them out.
Help them come to a reasonable place in the world. That’s the way I’d do it, you know? Anyway.
Someone else from a small island, from that same small island nation that frequently gets forgotten from maps has asked a question. How useful do you find live query plans?
Not so much. You know, I, I, I’ve never used a live query plan to troubleshoot a problem. Mostly because the people who I work with are not on a version of SQL Server that has live query plans in them. I, I would use them. The only time I’ve really used them to any great extent is locally when I’m trying to write a demo demo and I’ve wrote, I’ve written too good of a demo and it’s dragged on for like five minutes or so. And I’m thinking, well, what is this plan doing? Cause I want to see, I want to see what, what’s happening in here. And so I’ll rather like live query plans are pretty useful for just seeing like, okay, where, where are we held up here?
What, what do I need to do to make this demo a little less good? And, you know, that’s about where I stick with them. I re I really like the, the new management studio version 18 query plans because they have the, I mean, they don’t have, they don’t show you the query as it progresses.
Right. They’re not, you’re not seeing the plan. You’re not like you don’t hit F5 or whatever button combination you choose and see like the execution line doesn’t immediately pop up and start running. And you don’t see like rows as they flow through and do things. And as parts get completed, the little, little, the little line arrows turn into like solid. You don’t get all that.
You don’t, you don’t get all that. But when the, when the query is done, you do get, um, uh, you do get the final plan with mostly coherent operator times attached. Of course, if you, that varies a bit between row mode and batch mode. If you’ve watched my 10 minute video that, uh, a certain intergalactic celestial being from a small island nation that frequently gets left off maps had pointed out is well worth the 10 minutes of your time. Then you may be, maybe, maybe, maybe.
Well, no, no. Why? Uh, anyway, the thing I really hate about live query plans is how often they crash management studio, but that might not be a live, live query plan problem. That might be a management studio problem. Sometimes I’m like you hit it. You get the query running and then you hit cancel and you’d like, can’t do anything to that window.
If you try to close the window, management studio crashes. If you let it go, it’ll go on forever. And like you’ll wait until you restart. It’s a nightmare. Not, not my favorite way to spend time interacting with SQL Server management studio. God’s favorite 32 bit application. I hear, I hear management studio was God’s 30 favorite 32 bit application. Marcy asks, are you wearing Swannies? Marcy, I’m going to level with you.
I don’t know what a Swannies is. Enlighten me. What is a Swannies? Darren Scott says those scope identity in the where clause were from EF as well. Good heavens. One of my favorite things that I see in some applications is where, um, when you, uh, when certain people, so like if you have an application that needs to deal with people who access things from multiple sites, right? Like say you have sites A, B, and C. And you will have, uh, a column in the table that denotes site A, B, and C. And when different users connect in, they generate context information so that the application knows which site to point them to. And so none of the tables are directly referenced in the queries. Every single table is wrapped in a view with where column equals context info, the little context info function. So user, every single user before they touch it, like they have to get routed sort of via this function to which part of the table they should be looking at. It’s a very weird way to see things. Very strange. Anyway, uh, Paul something or other says people wouldn’t use EF if it weren’t for good stuff. Yeah. Would they? Yeah. I know a fair amount of people who use heroin. I mean, I know that’s an extreme example. I know a fair amount of people who like country music. That’s an even more extreme example. You know, a fair amount of people who wear flip-flops to walk around New York City.
Jandals, if you will, if you haven’t been wiped off the map yet. I know a fair amount of people who have made very, very dumb decisions in life. I have hand and neck tattoo. That was also a weird idea, weird choice. Anyway, what can you do? What can you do at this late stage? I can only go further. I can only wait for my mother to die so I can get face tattoos.
Otherwise she’d kill me. Let’s see. Forest, Forest of McDaniel says, when do you find CPU stats from query stats DMVs the least trustworthy? Before a query has finished. Soon nothing is there. It’s lying to you. I don’t know. I’ve never, I mean, I take, I take it all with a grain of salt. You know, I know when I write blog posts and when I write training material and, you know, talk about SQL Server generally, it’s, you know, you have to correlate things a little bit.
Right? Like you don’t want to just point to one thing and say, this is the God’s honest truth about what happened. You want to look at, like, I always want to look at a few different, this is why I’m such a big fan of new query plans, getting the operator times and the CPU time and all the other kind of cool lightweight profiling stuff that we’re getting in newer versions of SQL Server and management studio.
Because it gives you multiple sources of truth, right? You can multiple sources to check, to verify, to make sure that the thing you’re seeing holds up. All right? So there’s lots of different places that you can, you can look to validate things.
You know, it’s like, it, like, there’s no reason right now. There’s no, like, unless you, like, pull the crap out of that query profile DMV that does the live query plan thing, you can’t really validate, like, the per operator stuff too easily. You can validate, like, the overall plan time stuff pretty easily. But that’s, that’s, that’s about it.
But I, I really, I really like generally the, the amount of new feedback that we’re getting, even if it’s not all down to the microsecond or whatever new unit of time Microsoft has invented to measure things to confuse us. I hear that they’re going to invent satya seconds, which are sub nanoseconds that are only, they’re only, they’re only reliable to the closest microsecond.
That’s that. I don’t know. I think everything’s unreliable. Look, even YouTube’s unreliable. YouTube screwed me here.
Change my microphone over at the beginning of a webcast. Why would you do that? Why would you do that? I’d say audio settings have changed. Why? Dummies. Marcy says, Swanwick blue light blocking glasses. No, no. These are, these are Ray-Bans.
They’re a little bit old. I should probably get a new pair of glasses soon. I just particularly like these. I’ve grown used to them. I’ve grown fond to them on, on, on being on my face. Unlike this beard. This beard’s gone. No more of that.
Max Vernon says, the tats lend a bit of a cachet. I bet people tend to listen to you in person. I don’t know. It’s, it’s, it’s funny. Like when I was younger and I hung out with people who had tattoos, having more tattoos gave you like sort of weird social status. Right.
If you had, like, if you have more tattoos on someone, you, you can be like, yeah, when are you going to get that tattoo? Like, if I, like, I was able to do my neck tattoo, be like, well, when are you going to get your neck tattoo? Wimp, you know, like make point, like, I don’t see you with hand tattoos. What’s up with that?
Right. Like, there’s like some of that, like now that I hang out, a bunch of people who like, you know, tuck their t-shirt into their underwear. The, the social, social ladder is all flipped around. I’m like no one in some of these conversations, you know, you get, you get a bunch of people sitting around a table and they want to talk about, you know, something other than the, the, the small chunk of query tuning that I enjoy. No one’s listening to me. No one wants to hear me talk about containers.
No one wants to hear me talk about availability groups. No one wants to hear me talk about taking backups. I don’t want to hear me talk about taking backups. You know how dull that is? Ugh.
Nothing to do with it. Let’s check TB scheduling. Get out of here. Talk to someone who cares. You know, the only time I care about that because.
Oh, screw it. There’s a, there’s another question here. What do you think about implicit conversions and plan warnings more generally? Well, golly and gosh. Eating a lot of dinner because of implicit conversions.
Can’t complain too much about those. A lot of dinners. A lot of dinners. A lot of wine because of implicit conversions. And I’m pretty sure you’ve gotten some wine because of implicit conversions. My friend.
What do I think? What do I think? I mean, if, if someone, if, if you have people out in the world who can’t be bothered to, to match data types, like what, what, what, what help is there for them? They like this pick something randomly and compare them to random things and compare them.
Like, I want to know if this knife matches his pen. What do you tell those people? What can you tell them? I want to know if this fork is a notebook.
What do you tell them? There’s no sense. There’s no, if, if people are, are that dead set on not caring, I will happily take their money to tell them they should care.
That’s it. Plan warnings more generally. We get, we get warned about the wrong things.
We get warnings. We get, first off, the, the, the, the implicit conversion warnings are sometimes full of dookie. Right?
Like if, like one example that I show when I talk about them is like, if you have an integer column and you say, like, select, I let’s, let’s call it ID. Select ID as VARCAR 10. You will get an implicit conversion warning saying that the cardinality estimate of your query is incorrect.
You’ll also get an implicit conversion warnings for seek plan when there’s no index that you could possibly seek into. Right? Like let’s just say that I have, for example, another column called reputation.
And I say, select star from users where reputation equals a variable that’s, and then VARCAR something. Right? And I don’t have an index on reputation.
SQL Server will say, well, you could have done a seek plan, dummy. And you couldn’t because there was no index on it. There was nothing for you to seek into. Nothing there.
Another pet peeve is the no join predicate warning, which is that, would you get that big red, can you imagine the things that Microsoft should put a big red X over in query plans? Like every spool? Like every single spool should just have a giant red X over it.
But no, we get, we get warned when completely reasonably written queries with a join predicate. I say you don’t have a join predicate. Okay.
You win. And those memory grant warnings, which may affect the reliability of me to stay sober. Right?
He’s like, you’re, you’re, you had a thousand and 24 KB memory grant and you only used 176 KB of it. May affect the reliability. I love that.
I love that sentence. You know what? If there’s one fragmentation that I worry about in SQL Server, it is, it is sentence fragmentation. And these warnings affect, it may impact the reliability.
It’s just like, okay, tell me more. Tell me more. I would love to hear more.
Oh God. Where are some other ones? Where are some other funny ones? Oh boy. I don’t know.
I think spills are overrated. I think spills, I think, I don’t think you should get a spill warning unless you are like really spilling. You should.
Yeah. Unmatched indexes. That was a fun one. It was, it was fun to find out that if you have a plan that’s auto parameterized, you’ll get an unmatched index warning. When, even though, even if you use the index, like you can see the index being used in the plan.
But if your plan is auto parameterized or simple parameterized, whatever you want to call it, you get a warning. We couldn’t match that index to anything. But I see you.
I see you. It’s aggravating. Aggravating. It’s like, if you could, if you could go back and start over with like the query plan, XML.
Hey, would you even use XML? Use JSON. I would, I would much rather just get like a, like an MS paint.
It’s like, give me a PNG of the plans. Don’t make me use the XML. It’s like, write everything down for me. Write everything down.
Marion asks if I think Paul is real. Well, he better be. Or else, someone else has my old laptop. As far as I know, he is.
Yes, plans rendered by entity framework. That would be, that would be ideal. Because then you would never get a query plan. And you would never be able to fix anything. And you would be able to happily learn how to do something else for a living.
Go learn, go learn C sharp. Go learn, go write, write a great novel. Go write the next great novel for your country.
Where is it? Next great American novel, Canadian novel. New Zealand. I mean, let’s, no one in Australia is writing a novel. Get eaten by dingoes anyway.
Dingo ate my novel. I don’t know. I often think about what I would, things that I would much rather be doing than SQL Server.
The list keeps growing long. Fun. Marcy says, speaking of weird things and plans, is there a way to keep a detail pop-up open if I want to move my cursor to another screen?
No. No. Yes.
Of plan connecting lines and all that number of rose red malarkey. Yeah, that’s, you know. What bothers me is something that I learned from you about cash plans where they are just stitched together from many different plans. So, like, when you have a plan, you see, like, a plan in your plan cache or, like, an estimated plan where you have, like, thin lines and thin lines and thin lines.
And then you hit one operator and there’s a very, very thick line coming out of that operator for some reason. And the estimate goes from, like, three rows to, like, 40 billion rows. That’s just the optimizer being kooky.
Putting the plan in cache that might have been a few other plans stapled together. Dreadful. Marcy says, I don’t know how to get a screenshot of those.
They disappear as soon as I click anywhere else, like on the snipping tool. So, I use Snagit. S-N-A-G-I-T.
I use Snagit. And when I have my cursor hovering over an operator so I get the tooltip, I can just hit the print screen button. And that’ll trigger the screenshotting of my screen.
And I can zoom in on the tooltip. Snagit is great for that. It’s well worth, I forget, like, 25 bucks or something. And then, like, the occasional maintenance contract so that you get updates.
Well worth it. The other upside of Snagit is that it has, like, all sorts, like, it has, like, an editor built in. So, you can do all sorts of cool stuff with things you take pictures of.
You can, like, do, like, the crop where, like, you cut out, like, a certain part either horizontally or vertically. You can draw shapes. They have the most accusatory built-in arrows.
So, if you really want to call attention to something and be like, this sucks, the arrows they have are wonderful for that. Like, some of them, like, get bigger. They, like, taper out and, like, get huge arrowheads.
Awesome with that. Good accusatory arrow really makes for a good presentation. Snagit’s good.
They don’t even pay me to say that. I won’t get a coupon. No coupons for me, unfortunately. I am surprisingly, I have never been asked to be, like, a brand evangelist. Oh, yeah.
That’s my story. Anyway, we have hit the 30-minute mark. I have babbled at you long enough. It’s getting hot in here again. I haven’t, I might have noticed I haven’t complained about it being hot this week because I plugged my air conditioners in. But the air conditioners are out there.
When my door is closed, this room just slowly gets hotter and hotter because I have, like, 90 inches of screens hanging around me. Anyway, I’m going to get going. I’m going to go get back to work.
Hopefully make some money. And I will see you next week. Thank you for coming. I hope you enjoyed it. Thank you for the great questions, and I will see you next time.
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.