SQL Server Performance Office Hours Episode 39

Questions:

What are your favourite alternatives to alerts for Page Life Expectancy? I know that I have memory issues, but alerting on high I/O waits seems silly.
You mentioned you often prefer select into and adding the index later for parallel inserts. Not having real control over the nullability is causing me issues with creating primary keys after select into. Is a unique clustered basically the same thing?
You look like you’re bulking up again. I thought you were dying. Are you on steroids? What is your routine?
What are your lest favorite things to see in a query plan?
I forgot to ask how much protein you ate with steroids

Video Summary

In this video, I dive into answering five user-submitted questions for free during a live office hours session. We tackle topics ranging from alternative methods to monitor page life expectancy when dealing with memory issues, to the pros and cons of using `SELECT INTO` for parallel inserts and adding indexes later. Additionally, we explore what I consider less favorable elements in query plans, such as eager index spools, unnecessary scans, and parameter values embedded directly into the query text without being reflected in the plan properties. The session was packed with valuable insights and practical advice, making it a great watch for anyone looking to improve their SQL Server skills. If you have any questions or topics you’d like me to cover next, feel free to drop them in the comments below!

Full Transcript

Erik Darling here with Darling Data and today we’re going to do office hours in which I answer five, 1, 2, 3, 4, 5, that’s this many fingers, user submitted questions for free, right, that’s a whole point I guess. Down in the video description if you would like to ask a question there’s a link down below where the fingers are pointing down below just look, look down, right, my links are down here pal. And you can also find links there if you if you would like to hire me for consulting, buy my training, become a member of the channel in a paid way to support my endeavors here. And of course, the usual, if you like this blah, blah, blah, like, subscribe, tell a friend, you know, that, that. This Seattle, I’ll be in November with Kendra Little teaching two days of the finest T-SQL pre-cons you have ever seen. I will have all sorts of new swag-er-ific items for you, T-shirts, stickers, training materials. So there is no reason for you not to show up, is there? No, you can’t, you can’t give a good re-, you can’t give me a good reason for that. So, let’s answer these questions, right, let’s do, let’s do this thing, cuz I guess I promised you I would. Uh, what are your favorite alternatives to alerts for page life expectancy? I know I have memory issues, but alerting on high IO weights seems silly. Well, if you have memory issues, there are certainly going to have high IO weights, so why is that silly. Um, alerts? I don’t know, like, I, I don’t, I am not in a situation where I receive alerts from servers, so I don’t think a lot about what I, what I would, uh, what, what, like, alerts I would send myself, uh, in, in the course of the day. Um, you know, for me, it’s, it’s more general monitoring. Um, you could look for spikes in page IO latch SH or EX weights. Those would be worth, uh, worth monitoring.

I think for me, uh, if I was sure that I had memory issues. Um, I also may want to alert on, um, when a significant amount of memory goes towards query memory grants, perhaps even when resource semaphore weights happen. Uh, those are things that I would rather alert on than PLE, cuz PLE is a stupid dump. All right. Uh, oh, I should have, I should have highlighted that question. There we go. Yes. Um, anyway. Um, these are usually how memory issues manifest themselves. So, uh, that’s, that is what I would keep an eye on. Uh, uh, uh, uh, here we go. You mentioned you often prefer select into and adding the index later for parallel inserts. Not having real control over the nullability is causing me issues with creating primary keys after select into.

Uh, is unique clustered index basically the same thing. Uh, so to, to hopefully remedy your first situation, if you wrap whatever column you wish to not be null, uh, in your select list in the is null function and give it an appropriate replacement, uh, your, the resulting column will, uh, have a not null, uh, thing to it. So use is null. You can’t use coalesce for it. Coalesce doesn’t work, but is null. Uh, if you wrap your column in that, the result will be a not null, uh, not nullable column. Um, is unique clustered index basically the same thing? Yes. Aside from the fact that a unique clustered index will allow one null value in there.

So, uh, depending on how you prefer to approach this, uh, you could add a unique clustered index. If you’re sure there are no nulls in there, or you could also wrap the column in, uh, is null and have a better time. Okay. Uh, come on, cursor. Where are you? Oh, you’re over there. Ah, there we are. All right. Uh, you look like you’re bulking up again. I thought you were dying. Are you on steroids? What is your routine?

Uh, all right. Well, um, I guess thanks for noticing. Uh, I, I, I, I have, I have been, uh, a little bit better about, well, actually I’ve always been good about the gym, but, um, um, I, I have been good about, um, I guess more, uh, progressive training. Um, I am not on steroids. Uh, I, if, if I were on steroids and I still looked like this, I would get my money back. Uh, my routine is generally, uh, basic barbell exercises, squats, deadlifts, bench press, overhead press, uh, depending on, uh, what I’ve done.

Uh, you know, deadlifts typically one set of five squats, either a three sets of three, three sets of five, uh, somewhere in there. Um, if, if I’m feeling real wiped out, I might do a lighter weight on squats with three sets of eight, uh, or three sets of 10, just to get some extra work in. If, if, uh, my legs are feeling particularly burned out, uh, overhead press, I do one day of, uh, 10 singles at a pretty heavy weight.

The last, the last one I had was, uh, 200. So, uh, for the 10 singles. And then, uh, I’ll also do a slightly lighter day on overhead press for, with three sets of five. Uh, also in the mix, I’ll, uh, usually make room for some rows, some, uh, chin-ups, uh, in there.

Uh, occasionally, uh, I guess RDLs. Those are, those are nice hamstring work. Uh, if you get them heavy enough. And, uh, yeah, that’s about it. I don’t, I don’t, I don’t do anything. Uh, I don’t have like a, a bodybuilding routine with lots of hypertrophy and whatnot mixed in.

Uh, to do. What are your less favorite things to see in a query plan? All right. Lest, lest we forget. Uh, I don’t know. Um, okay. Let’s think of some stuff in here. Uh, I hate when I see spools in a query plan, uh, especially eager index spools.

Um, uh, lazy table spools are also not, not something that I usually love seeing. Uh, I don’t like when I see really big lines going into a sort operator. That usually tells me we have, we have an issue.

Um, uh, let’s see. Uh, I dislike seeing a top above a scan. That’s another one that I don’t like to see. Uh, and I don’t like when I see, uh, the sort of constant scan, um, concatenate stuff, sort merge interval, and then a nested loops join.

Because that usually tells me that, um, we either have a join with an or clause or that we have, um, a mismatched data type. That quite frequently, if you have, like, let’s say, a date time, uh, uh, date time to column, and you have a date or a date time parameter, SQL Server has to do some extra work to, to work things out. So those are things that I typically dislike seeing.

Um, of course, you know, the usual stuff, um, unnecessary scans, right? Like you have a, you have like a scan where there’s a predicate on it that’s not a bitmap. You’re like, oh, maybe I should create an index there.

Sometimes you’ll get the helpful little green text, sometimes not. But, um, you know, those are things that I typically dislike seeing. If I have an actual execution plan, uh, that’s a little bit different because then I have operator times to sort of guide my tuning efforts in a, in a smarter way. Uh, estimated plans, you know, you can spot some red flags in those, but typically, um, you know, you’re like, eh, uh, it takes a little bit more effort to figure things out.

Um, table variables, uh, dislikes, uh, table variables and plans. One thing that I, uh, really dislike in, uh, seeing an execution plans is when like there are parameter values, uh, like as part of like the query text. But then you go and you look in the, the, like you get the properties of the root operator in the plan and there are no parameter values in there.

Cause that usually tells me, uh, unless there’s a recompile hint where the literal values get, where the parameter embedding optimization kicks in and you get literal values in the query plan. That usually tells me that someone is using local variables and I think, hmm, I shake the fist at you. Um, not necessarily because the local variables are causing a problem, but just because I can’t get the values for local variables.

Cause SQL Server does not sniff and use them, right? Like it does with a parameter. So those are things I dislike seeing. There’s probably others if I thought, uh, I forgot to ask how much protein you ate with steroids.

Uh, all right. So once again, I’m, I’m clearly not on steroids. Um, I, I, I, I, it’s not, not my thing.

Um, you know, life is hard enough without trend making me emotional. Uh, but as far as protein goes, um, I drink two protein shakes a day, uh, that are 50 grams of protein a piece, uh, unflavored Isopure, uh, powdered Pedialyte, some, uh, psyllium husk to, to keep things. Uh, orderly.

And, uh, I, I have the scoopable creatine. So I’ll do 10 grams of creatine a day and across the two shakes. Um, and then of course you must, you must eat real food. What lest you become a skeleton.

Uh, and I usually try to get around another hundred or so grams of protein from actual, uh, meat sources. So chicken, steak, stuff like that. Uh, not a big fan of pork.

Um, not a religious thing. Just, you know, it’s not, it’s not that it’s bad. It’s just, I, I like other stuff better. Uh, so I think, I think that about does it.

Interesting mix today. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video, uh, whenever that may be. Uh, I guess tomorrow.

All right. Thank you. Goodbye.

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.