Last Week's Almost Definitely Not Office Hours: February 22

ICYMI

Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.

Thanks for watching!

Video Summary

In this video, I found myself going down a rabbit hole of technical discussions and personal anecdotes. The conversation started off with some interesting questions about database views and reporting tools, which led to a lively debate on the best practices for minimizing query hops and optimizing performance. As we delved deeper into the topic, it became clear that even simple decisions can have significant impacts on system efficiency.

One of the highlights was when Josh Darnell chimed in with his usual wit and expertise. He shared some insights about creating views directly on the data mart versus using local SQL Server views to access remote data, emphasizing the importance of reducing complexity for better performance. His famous status on Stack Exchange was a bit of a surprise but also a reminder of how much we can learn from each other in this community.

Full Transcript

Scallywags. All you scallywags. All right. Another Friday.

Hard to believe, right? Do-do-do-do-do-do. Oh, my God.

Someone showed up. You must have nothing to do. This is like a preview of what this mustache is going to look like in bits. It’s going to be, it’s going to be horrible. I can’t wait to shave this thing off my face.

I’m so terrified. But the worst part about it is having to explain to my family why I have a mustache. Because, so Joe, I don’t, I don’t know if you’ve, if you’ve been paying attention to certain things, but, at, at, at, at, at, at SQLBits, I am doing, I’m doing a charity thing where I’m dressing up like Freddie Mercury at Live Aid to, to deliver my, my regular session.

And, and he has quite a mustache for that. And so I can’t grow the whole beard because he didn’t have the whole beard. So I am growing the mustache to participate in this charity event.

So that’s my story. And I don’t know. That’s why I don’t have a whole beard.

That’s why I just have a mustache. And that’s why explaining it to my parents is a little, like, like anyone who sees me is a little bit awkward. Today’s my daughter’s birthday. And so there’s like video calls going on and they’re like, what’s on your face?

What’s, what’s, what’s wrong with you? Well, I have been. That’s why I got scruffed, but I got to, I don’t know. Because I, I don’t, because due to England’s weird laws, I’m not terribly confident that I would, I would be able to buy the necessary equipment to shave a beard off on short notice.

I think I might have to, I might have to apply for a license or a permit to, to get like some clippers because they, they have some weird stuff. But then I want to get like busted by the police.

Like, look, we found this weapon. I’m like, I was going to shave my beard. They have weird laws over there, man. They like, like won’t sell eggs and flour to people under 18. Like you can’t buy like plastic knives and stuff.

And the police like go around to like various, like, like, like, like projects in England are called estates. And like, there’s all these like funny Twitter things of like the, the police there going around to various projects.

And like, we did a weapon sweep and look what we found. There’s like bike tires, like a pair of scissors and like a butter knife. It’s nuts. Like, wow.

People are insane. But yeah, I don’t know. Funny stuff. All right.

So does anyone actually have like a SQL Server question? Or do you just come here to hear me babble about other things? It’s fine either way. I can, I can go on for days. I don’t know.

We were getting peril, perilously close to breaking double digits. Yeah. Oh yeah. You had a question last week that I missed because I got embarrassed and turned the camera. Actually, no, there’s nothing from chat was coming in and I felt very strange.

Like, cause this is like silence. Oh boy. All right. So one, I like to use SQL Server non AD logins for linked servers. Is there any reason I should switch to AD users instead?

I can’t think of a good one. You know, for me, the linked server question comes down to a couple of things. One, if you’re on a version of SQL Server prior to 2012, you need SA privileges to be able to use like cross database statistics.

Uh, which can make like running queries kind of awkward. Um, and I’m not saying that you should go and use SA for all your linked servers.

I’m saying that you should probably maybe like get on SQL Server 2012 service pack. One ish, I believe is when they fix it.

Uh, so I would, I would be more concerned with that. Then like, you know, for, for linked servers, you know, um, like just really like bare minimum permissions to get in and get out and like, don’t try to run the query across there, whatever data you have, just go grab it and pull it locally.

Uh, this, this really awful thing that happens with, uh, remote. I forget if it’s updates and deletes too, but this is really horrible thing that happens with remote inserts where SQL Server will behind the scenes, turn it into like a row by row thing.

Instead of like doing the insert all at once, it like, like just sticks a row in at a time. I forget. I think, I think Max Vernon was telling me about it. I’ll go, I’ll go ask him and I don’t know, tell him to blog about it or something.

Cause I, I don’t, I don’t even have enough SQL servers to make a link server. Uh, Julie asked, actually, Julie, I have a question. Did you get the stickers I sent? Cause I sent those out. Kevin said he got his. I wanted to make sure you got yours too.

do I have suggestions for learning visual studio, man? I should, I don’t even know visual studio. All right. Man.

Uh, no, I mean, what do you want to do in visual studio? Are you like using SQL Server in visual studio? Are you like learning or doing C sharp? Like what’s, what do you want to do with visual studio that you want to learn?

Learning visual studios, like how do I learn SSMS? It’s like, I don’t know. Watch shortcuts. Silly, silly stuff.

Okay. Yeah. Those virtual labs are like disappear. Oh, I don’t, I don’t know. I can’t say that they’re disappearing, but, um, the one that I really liked was failover clusters.

And the, uh, the Microsoft virtual lab that I used to go, I used to have like bookmarked for failover cluster stuff disappeared recently.

They’re like, they like got rid of a whole bunch of them. Uh, Julie says, uh, I’m developing SSIS. And Deja.

Oh, wow. Okay. Uh, good question. I, I don’t know. I don’t, I don’t really use SSIS. I hear that bids is what a lot of people open. Um, if you’re on Twitter, I would harass Andy Leonard about that or just like go to his, Andy Leonard’s blog.

Cause he, he writes a lot about that type of thing where I am. I am absolutely useless when it comes to SSIS. I’m like, like demonstrably bad. An SSIS lab.

Wow. Maybe I should go do that. Maybe I’ll become like a really good SSIS consultant and give Andy a run for his money. This can make it. I could never do that. It’s, it’s, it’s too late for me to go learning those tricks.

I gotta, I gotta stick to the tricks. I know at this point. So what do you use SSIS for? I’m curious. Like, I don’t, I don’t think it’s a bad thing.

I, I think SSIS has many wonderful, uh, applications. I’m just curious what, what, what are you guys using it for? ETO.

Yeah, that’s pretty common. Probably, probably better than, PowerShell for ETL, right? Anyone ever tried mangling text files in PowerShell? It is painful.

Oh, profiles from FTP. Wow. Yeah. Uh, I, I used to, I used to do that with TC. Uh, I had, I had a, I had a thing, I had a whole thing set up.

Uh, I did, I did store procedure that would create a cookie. Like it would, it would like create a, it would create a user cookie and it would feed it to curl. And curl would go check this URL for a new file.

And man, that was funny to write. Mostly because it worked. And I have no idea how or why it worked.

Files are created from marketing cloud. Wow. Sounds intense. There was a, I used to remember, I remember I used to have to, I had a, was it seven zip or like one of those zip files.

Well, I had like a, I had a store procedure that would like open up the command line executable for those. And like, like unzip stuff. Brutal.

I don’t know why I never learned like, I don’t know why I, I was like, I got to do this in T SQL. Like, I got to write a store procedure that does this because there are like so many better.

Like languages that would have been good. But at that point it would have been like, Oh crap. I have to learn a whole new language. Like I already knew T SQL. You could just like feed stuff to XP command shell all day long. FileZilla is good for that though.

FileZilla is, I don’t know, the best FTP program. I don’t know. I thought, I thought war FTP was cool when I was younger. I used to run FTP servers off my, off my computer.

And like, I thought it was funny. Cause I would like share MP3s with my friends. And war FTP. That was, that was, that was, I think like the, one of the first client server things I ever got into. The other one was hotline server.

That was crazy. And like the nineties hotline, man, that was a wild West. You could, you could get anything. I remember like when I, when I was, when I was getting my NT certs, that was like, I used hotline and that was like, there was like wares all over the place.

And that’s where I got like, so like I would, I would like pull computers out of the trash for like companies that went out of business. And I set up like a home, home network in my room.

And with like installed stuff from, from hotline. It was like, like all the NT 4.0. I’m probably going to get arrested for like piracy right now. Josh, John L says, hello.

Does Josh have a SQL cloud? I don’t know. Josh is now. DBA. DBA. Stack exchange famous. He answered a question that hit the hot network question list. And now forget it, man.

It’s going to be too good for us soon. It’s going to have a moderator badge. It’s going to be off. It’s going to be very lonely without him. He’s the only web developer I can stand.

And you, you’re a hot network question, man. It’s not fun. No, you can’t lie. You’re going to, people are going to be asking you for autographs. People, people are going to be quoting you in questions.

You’re going to say, Josh Darnell says this, and then they’re going to ask a question about what you said. They’re going to expect you to answer it.

They’re going to just wait for you. SQL dev DB has another good question. I want to give the reporting tool access via views.

Is it better to do this by creating the views directly on the data mart or creating views on the reporting tools, local SQL Server, and have those views go to the views on double.

I would, I think the fewer hops you get into the better. Generally, I prefer fewer hops. The more hops there are, the more complicated things get.

It’s not, and not even from like, like a, like a tech, like a, like a technical perspective, but just like the poor optimizer now has to like, like unravel a view and then go across to another server and unravel a view.

And you’re just going to give that optimizer a break, man. Give it, give it a rest. Just let, let it, let it just do things once. Josh is so famous.

He just said, LOL famous and left. If anyone needs WordPress consulting, you should talk to Josh. He’s very, he’s very good at CSS. He helped me tremendously on my site.

Uh, it would look awful if it weren’t for him. Everything would be misaligned and out of whack. And like, uh, like all, all of the form text would be the same color as the background.

It would be, it would be a disaster. So if, if, if I ever make a million dollars, the first thing I’m going to do is hire Josh to just hang out and do nothing for a few months.

Pretend he’s working on my WordPress site. The rest of the million dollars is going to go to booze and women though. Forget it.

Glad to help. See what, what, what I should have done is, is gamified it. And I should have, uh, I should have, I should have like asked a question on like web dev.

Is there, is there like a web dev stack? Exchange or is it just stack overflow? Let’s see. Josh says, I needed to add a filter to an index view that required a left join the other day.

Do you ever run into that? How do you work around that restriction? Uh, no. Uh, and that’s one of the unfortunate things about index views. And actually, I think, let me go look.

Cause I sort of remember, hold on. Uh, uh, index. You. Cause I remember that it was a lot of weird stuff. You can’t do in an index view, but I can’t remember.

Yeah. So you can use not exists. I think, I think not exists would be your friend there.

I would, I would, I would, instead of messing around with more joins, if you need to find something that exists or doesn’t exist, I would, I would do that.

I don’t know. Maybe, maybe I’m, maybe I’m just, maybe, maybe I’m wrong though. Uh, let’s see. Louie says, SP who is active, get locks equals one. The lock column has lots of info on it. How is best to decipher it?

Uh, it shouldn’t have a lot of information in it. Uh, when that happens, I, I, I think that something might be a bit of muck.

So like, I know it’s like sometimes when you run a modification query and you do get locks equals one, uh, you might end up seeing a bunch of information from like system tables.

And I generally tend to ignore that. Uh, when I, I, and like focus on the user tables at hand.

Cause when I see that stuff, I think like something is something, something weird happened in the XML. Um, another good way to see, uh, locks that is actually. So, uh, I, I, uh, a while back when I was talking, to Adam, like, like I haven’t, I don’t talk to Adam, but I, I was talking to Adam specifically about SP who is active and actually over on the documentation page.

Uh, I don’t know if he mentions explicitly that get locks equals one is, uh, can be kind of tough on the server when there’s a lot of locking activity going on, but he told me about a couple, uh, commands in the, in the documentation.

I said, put the link into chat there. So if you want an SP who is active with get task info equals two and get additional info equals one, you can, you get this additional info. Obviously you get this additional info column that gives you, uh, more summarized lock information that can be helpful in troubleshooting stuff too.

Let’s see. Darren says, I’ve seen this error before and I get the basic premise, the query processor ran out of internal resources, cannot produce a query plan. Uh, blah, blah, blah, blah.

Uh, no, well, no. So the reason that that comes up is that, uh, someone has passed in a very, so whenever I, so I’m going to say, let me, let me rewind that a little.

When I’ve seen that, when that’s happened to me, uh, it was back when I was working with relativity and the way that the GUI worked, maybe at the time, maybe it’s changed, but people could pass in these very, people could pass in like a list of IDs.

And what they would do is they would run a saved search. This like very long list of IDs would show up as the results. Then they would copy and paste that into the search box for a search.

And they would say like where ID in or not in or whatever. And there would be like a hundred thousand IDs pasted in or something. And, uh, that’s usually when I’ve seen the query processor barf is when there’s just a very long list of ends or ors or something that, um, um, has just made things far too difficult for it to, to carry on.

Yeah, exactly. Carry like a, putting like a bunch of stuff in an in cause can definitely, uh, ruin the optimizer’s day. So there’s really no way to force that except to tell your users to stop being ding dongs and write more saying queries.

If someone, if someone truly needs to like a 10,000 or 30,000 or 40,000 item in cause, just get a temp table, man, get a room.

it’s, it’s mean, it’s mean to do that. It’s mean to do that to people, especially the optimizer, that delicate, fragile optimizer, right?

Man, that thing’s crazy. So, uh, I was excited kind of because over on, on Twitter, I made a couple of polls about, uh, versions of SQL Server that people were planning on moving to because 2008 and 2008 are two are going to be end of life in like five months, like four or five months, like this summer.

This is gonna be a, I’m going to throw, I’m going to throw my own personal summer jam. When that happens, that’s, I can’t wait to not look at those things anymore. But, um, uh, I was excited because I put up polls about little people moving to.

And the first funny thing that happened is, uh, twice as many people said that they were going to 2014 as 2012, which, uh, Oh, sorry.

Going to 2012 was 2014, which cracked me up. It was like 11% to 2012 and 5% to 2014. That was really funny. Uh, a lot of the, the 2017 was the big winner there, but then I put up another one that included 2019.

A lot of people said that they were moving to 2019. So I think, I think Microsoft might have a hit on their hands at this one. I think, I think they might go platinum with 2019.

That’s going to be, that’s going to be fun to watch, especially if like, um, you know, people aren’t just like bull bull crapping me about adoption. I don’t, I don’t want to swear.

Cause I think like YouTube’s algorithm might flag me as adult content, but if people aren’t like really moving to people are really moving to 2019, that’s going to be fun. Cause like from, from a consulting point of view, I, I, I feel like kind of groundhog day with this, with this stuff that I have to keep telling people about and fixing.

And Microsoft is finally like taking the groundhog day stuff out of that. And I get to focus on cooler problems. Like, like this week I’ve been, uh, I’ve been, I’ve been helping out some, my, my, my friend, Mike Walsh over at straight path, uh, with some query tuning stuff.

And there were just like some amazingly long running queries. It just needed like one or two tiny little tweaks that weren’t like, like, you know, groundhog day stuff to tweak.

And it was just so nice to be like, no, look, it’s crazy. You’ve got these two index pools over here. When we create these indexes, it takes a minute instead of an hour. Or like, uh, when we like, you know, go from trying to like self-reference this table variable update and like dump things into a temp table first.

Instead, uh, we can get things from 18 hours to like a few minutes. And there was some really, really nice things. And, I found some like other fun queries were like, there was a total, like totally unnecessary distinct was causing stuff to run for like five to 10 seconds.

And without the distinct, it was like blazing fast. And then I found some other stuff where, uh, these, these, uh, things called performance spools, table spools were making queries run for like 10, 12 seconds.

And without them, they finish in like under a second. So it’s really, it’s really fun to like, you know, realize it’s really fun to like get kind of back in the trenches a little bit and be like, Oh wow. Like, uh, the stuff that I talk about, isn’t just malarkey that works in stack overflow.

It’s like generally applicable to people’s stuff too. So it’s been fun. Uh, let’s see. Uh, do you have any plans to expand your services into BI? And absolutely not.

Uh, no. Um, that’s just not my jam. Uh, I, I wish that I could find a way to like that stuff, but I don’t know.

It’s like, American whiskey. I just, I can, I can drink it if I’m drunk, but it’s, it’s never going to be, never going to be my sipper. You know what I mean?

How often do you see query store use in the wild? Uh, you know, not enough when I really want it. And too often when I shouldn’t.

So, uh, it’s, it’s, first of all, I love the idea of query store, but man, they’ve made that tough to turn on.

Like by default, it just collects so much crap. And then when it’s on and like, you have a busy system, people are just like, that thing just like, like logging all that information is tough.

That’s just like, that’s some serious observer overhead. You’re collecting query plans. You’re collecting all sorts of metrics and you’re just dumping it into tables. And it’s like, you think that’s free?

Uh, uh, busy system, man. Query store will mess you up, which sucks because it’s like in, in, in theory, it is such a cool idea to have that, like a persisted plan cash, right?

Where like, you can just get like endless reams of information from, that’s like such a cool idea, but then you got to realize anything you do that, that, that is collects that much, like especially that much excess, ML and all the text from your queries, all that hurts.

So I totally understand why people don’t have it turned on, but man, like when they don’t and I see it, I’m like, Oh, I wish I had you. But then I’m like, Oh, I feel bad for people to turn you on. And then their server, that CPU is like smoking and like making grinding noises.

Sorry. Oh man. All right.

Someone say something. Someone say something funny. Tell me, tell me a joke. I could use a good laugh. I wonder if anyone sent me a good email. I got a suggestion that I should take like email and Twitter questions prior to doing this.

Uh, and I’m fine with that because sometimes, you know, it’s, it’s not easy to fill, uh, like a half hour or so void.

And so I, if you want to say, if you like, if anyone’s watching this later and you want to send in like an email question, you can go to your Twitter question.

You can hit me up on, on the Twitter. You can welcome me to like 2005. I’m only getting on there. Or, uh, you can shoot me an email via my website contacting.

If you have a question that you would like answered here, but you just can’t physically show up because you have a, like, I don’t know.

You have work things or time zone things. I think Julie has a joke for me. Thank you, Julie. Why don’t you see elephants hiding in trees? I don’t know. Why don’t you see elephants hiding in trees? This better be good.

Because they are really good at hiding. Damn. That is funny. I didn’t see that coming. I thought there was going to be a trunk joke in there. That was very good though.

Oh, I appreciate that. The longer, the longer I think about it, the more I like it.

That’s very good. Now, now, now I don’t fit. Now I don’t regret sending you stickers, Julie. I’m glad I sent those over to you. And you’ve, you, you’ve made the famous Josh Darnell laugh.

Josh Darnell of, uh, of, of backups crashing SQL Server fame. You’ve made him laugh. So, I don’t know.

Maybe, maybe you’ll be famous too. Now. Maybe, maybe, maybe you can take that on the road. Yeah. You should just go home for the day. Go enjoy. Go enjoy. Go put stickers on stuff. Where did those stickers end up?

Now I’m curious. Hopefully not in the trash. Hopefully he didn’t seem like, oh, he’s ugly. How do we get a sticker?

Uh, you can send me an email. Well, you can contact me in some way that is, is private where you don’t mind sending your address and I will stick some stuff in the mail there. And I will.

So, I, I, I get, I get piles of these. Well, I got, I have, I have, I have a good pile of these things. I have, I have this many. So I can send, I can send out a few before bits.

You know, who knows? I mean, maybe I’ll go out of business, right? Just kidding. Just kidding. All those support contracts are good. Written in stone. Yeah.

Well, we send me a, shoot me an email or a Twitter DM or something. I don’t know. Figure it out. Notice Josh didn’t ask for a sticker. He’s too famous.

What is that poster over my shoulder with the creepy guy? That, that poster is one of the most beloved people in my life. My friend Rue.

And it’s a list of things that Rue is good at. And it’s a list. It was a collaborative list where me and other people who have equal, equal amounts of adoration for this strange man, put together a list of, of comical things that he is very good at.

And I, I have proudly had that in every single office that every, every single home office that I’ve had. So I guess that’s like two, but, but it’s not going away.

That’s my jam. I, I will, I, I, I, I will never show you the, I would never do a closeup of the list.

I’m not going to zoom in on the list, but that’s what it is. Uh, he’s, he’s an odd bird. He’s great though. Uh, would you like me to send you?

Yeah. Do you have stickers? Do you have stickers? If you have stickers, I’ll take stickers right now. The only sticker I have on my laptop is mine. And peel that other one off. Not giving any free promotion anymore.

Yeah. Take your, you know, you, you and, you and Farrah should autograph it.

Then I’ll just hold this. I’ll just hold that over Joe’s head. Make fun of, make fun of Joe Obish for not having as much street credit as me. Raven DB stickers.

Yeah. I know you like Raven DB. They tell you why you don’t need, why indexes don’t get used. Everyone else, SQL Server doesn’t tell you that. SQL Server hides that. How’s that issue going anyway?

Did I mean, did you, do you have like, do you have more votes on that? Is there a way you would detect a server spending too much time compiling queries? Uh, so that would go to two things.

One is resource semaphore query compile as a weight. Uh, if you’re running into that, then you do not have enough memory to adequately compile your queries at once. And the other would be sort of an unfortunate, like a, like a really unfortunate plan cash query.

Uh, so if you look through SP blitz cash, um, there are parts where I pull out the compile time from cash plans. And I, I remember warning on that.

That’s an, it’s an, so a query store has it as just a column value pulled out for you. So if you have query store turned on, you can look in query store. I forget, I forget which view it’s in.

Cause I, I wrote, I wrote the queries once for a reason. So I wouldn’t have to remember, remember all this stuff, but one of the, one of the views in for query store has a compile time pulled out for you. If you just want to look at the plan cash itself, uh, you could shred the compile time thing out of the XML and, I mean, but that would be awful though.

Cause you, that like, like there’s no way to like figure out which ones were bad from the, from like the beginning. But yeah, uh, I think that’s where I would start.

I, so like when I, when I think it’s fun too, cause when I think about that as a problem, I w I’m starting to wonder is like, is it a plan caching issue where like you, like you’re not reusing plans and like you’re, you’re spending a lot of time compiling new plant, like OLTP style comp, like piling new plans as they come in, or like you have gigantic queries coming in and SQL service.

It’s like, whoa, whoa, whoa, whoa, whoa, we’re going to, we’re going to read this one close. So you’re going to print this one out and go, go take a bathroom break.

Big pile of votes. No response. Let’s see. Uh, did I click on that link? No, I just let people see it. Let me go in here. Yeah.

119 votes, man. That’s rough. You know what? I’m going to, I’m going to make it one 20. Don’t tell, don’t tell anyone I double voted. Now, now I have, I have a new voting. I have a new account for, for filing issues.

So I get to double vote on that. Don’t tell 120 votes. Wow. Yeah, that would be cool. Come on, Microsoft. Oh, who am I kidding? No one’s watching this thing anyway.

All right. Uh, I’ve been standing here for a half hour and I need to go do something else for a minute.

Uh, thank you for coming and asking questions and I will see you next week. Actually, I don’t know. I’m trying to figure it. Cause I’m going to be in Manchester for business next week. So I got to figure out how I’m going to do that. Uh, I don’t know if I’m going to try to do it like at 12 Eastern or if I’m going to try to do it at like bits, bits noon.

I haven’t figured it out. I’ll figure it out and I’ll let you know. I will, I will have an update on that. All right. I’ll, I’ll, I’ll catch y’all later. I have to hit this button here. Thanks for coming. Thanks for asking questions.

Yeah. You would make streaming difficult forest.