This week, Richie and Erik discuss altering database columns, separating log files, encryption, NOLOCK issues, developing new skills and experience, troubleshooting deadlocks, Oracle, calling C# method from a stored procedure and the type of headset Erik uses.

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours 9-27-2017

 

I need to update a 100GB table…

Erik Darling: The first one is from a fellow with the atomic symbol for boron in his name, Jorgan. And his question is, “I have a table with the size of 100GB…” My goodness, very nearly a lady. “In 100,00 rows – I need to alter one column from .2110 to .2410. Do you have an idea to minimize table lock time?” Well usually I stay away from altering a column like that. Usually what I do is add a column that’s nullable and then I update that column in chunks, so that I don’t have to deal with altering an entire column.

I don’t know what version of SQL Server you’re on, but there are some sort of enhancements when changing things like that about columns. But that’s what I would do, then you could use sp_rename to flip the columns around. And that’s a pretty simple metadata change, so that’s probably what I’d stick with for that.

Richie Rump: Yup, that’s what I do.

Erik Darling: As long as that column isn’t like the primary key of a clustered index or something, it’s a fairly easy switch. You may need to rearrange your indexes a little bit though.

 

Should I put TempDB data and logs on separate drives?

Erik Darling: “When installing SQL Server 2016…” Welcome to almost the past. October 2nd, SQL Server 17 comes out, so J.H. I don’t know why you’re installing this old and busted version of SQL Server when the future awaits; the future is out there. “When installing SQL 2016 on a VM, is it still good practice to put tempdb data and log files on separate letter disk drives? Since it sounds like all the letters are coming from the same underlying storage, is performance better?” No. Brent, has a great post Brent, about how separating log files doesn’t help you with anything. More importantly, on a SAN, you’re right, it’s all the same underlying pool.

Now, there are some, kind of, manageability reasons to do it. So like, if you have tempdb data and logs all on one drive letter, then one thing kind of grows out of control, you could affect everything else. So if tempdb blows up and then your data file blows up, well if your data file wants to grow after that, it’s not going to be able to grow. You fill up the drive, you’re kind of stuck. It’s no fun, right. So there are manageability reasons for separating them. Like you put tempdb on a drive that’s 100GB, you put your data files on another drive that’s say 500GB, put your log files on another drive of 250GB or something; if any one of those databases or files grows up and blows things out then you don’t have everything impacted. You just kind of separate out the impact. You minimize the impact across the board.

So for performance, no, you get nothing out of that, but there are some manageability perspectives that make sense. What do you think, Richie?

Richie Rump: I think if you run sp_Blitz and you have them all on the same drive letter, it’s going to flag you for it.

Erik Darling: Especially if you’re backing up to the same drive.

Richie Rump: And if you have tempdb on the same drive.

Erik Darling: Also, it will complain if you have anything on the C: drive. Why would you do that to yourself?

Richie Rump: Let the OS have its OS stuff and have SQL…

Erik Darling: [crosstalk] Leave the OS alone with its stupid page file. Dreadful. I wish I could say dreadful the same way Gordon Ramsay says dreadful. Every time I watch Masterchef and he says something is dreadful, I’m like, “Oh it’s so good.” Then when I say dreadful it’s just like…

Richie Rump: “I’m an idiot sandwich.”

 

Can I change certificates with Always Encrypted?

Erik Darling: Alright, let’s see here. David asks, “With Always Encrypted, is it possible to change the location of the certificate without decrypting and re-encrypting the columns? I’d like to change it from current user to local computer.” Gosh, I don’t know. I haven’t used Always Encrypted all that much and I have not ever tried to move a certificate in that way. So, I would head over to DBA.StackExchange.com and ask, because I’m sure someone over there – there are a bunch of folks who work for Microsoft who answer questions on that site, and I’m sure there are some folks who have even used Always Encrypted that could answer your question better than I ever could, because security is not really my forte. There’s no query plan for certificates, so I kind of suck at that.

 

What’s Richie doing this week?

Richie Rump: Actually, encryption is one of my things I like to mess with, and I haven’t even played with it yet. Actually, this week and a little bit last week I’ve been messing with 2016 SQL Server. It’s weird for me, I’m actually on SQL Server this week; it’s strange.

Erik Darling: How do you feel about that?

Richie Rump: I feel okay. I like SSMS; it’s better than pgAdmin. How’s that?

Erik Darling: pgAdmin is miserable.

Richie Rump: It’s bad. No, I used to write SQL functions inside of Postgres, but now I’m running Postgres functions in SQL, so now I’m all sort of confused.

Erik Darling: That’s a snap back to reality, huh? One thing, whenever I’ve used Postgres in the past that I always missed was the ability to get the graphical Showplan. Getting the explain analogs thing was just like, what? I’m like awful at it. Reading through, I’m like…

Richie Rump: It was the same thing with Teradata, when I had to do an explain plan through Teradata, I’m like, “Oh god no.”

Erik Darling: I mean, that’s what it used to look like for SQL Server; like you’d do set statistics profile on and you get explain plan with weird stuff spread all over the place. I still like it a little bit because it shows right up front the estimates first, actual in the rows. So, it’s really great to spot bad cardinality estimates and stuff, but other than that, I get lost in…

Richie Rump: That’s only because you’ve gotten so good at reading XML, that’s why.

Erik Darling: That’s true. XML is like – I can actually read XML like regular human writing.

Richie Rump: Which is so weird. It’s like, “Move on to JSON like the rest of us, Buddy. Come on now.”

Erik Darling: Someday, someday I’ll get there. As soon as query plans are in JSON, I’ll be there.

Richie Rump: That would be cool.

 

Has NOLOCK changed recently?

Erik Darling: So, M.M. asks, “We are migrating from 2008 R2 to 2014…” Why? Why are you living in the past, man?

Richie Rump: Jumping point, right? They’re going to go 2016 or 17 soon?

Erik Darling: Maybe, I don’t know. I never trust it when people say that. I’m always like, you just want to give me a little Band-Aid so I stop griping too much. “Our developers have NOLOCK everywhere in dozens of jobs, not my choice. Are there any breaking changes with NOLOCK?” Well, there was a cumulative update that broke NOLOCK, where even with a NOLOCK hint, it would not observe the locks that other queries took out. But that’s been fixed, so as long as you’re on 2014 with whatever the latest service pack and CU is, you’ll be fine. I’ll leave it up to you to work on getting the NOLOCKs out of there, unless no one cares…

Richie Rump: Yeah, you need to get thee NOLOCKs out of there.

Erik Darling: Unless no one cares… If everyone’s like “Cool, fine, do whatever, I don’t want to deal with optimistic isolation levels,” then NOLOCK your… [crosstalk]

Richie Rump: I’ve used NOLOCK, but it was on a system that – there were no updates on it, so who cares.

Erik Darling: So why did you put NOLOCK on there?

Richie Rump: Because we were having locking on reading issues.

Erik Darling: Really?

Richie Rump: Yeah man, that was a 60TB thing, man. A 60TB database, man, there was crazy stuff going on all over that thing. It was fun. I mean, it’s fun to see all the weird stuff that goes on when you’re dealing with that size of data.

Erik Darling: Seriously, I mean my previous limit was somewhere around 10TB, so I would love to see what happens at 60TB. I would love to see what a – all the stuff, like statistics time and I/O would break on that. [inaudible] over big int pages.

Richie Rump: Yeah, there was a lot of weird things that went on, and it was like a batch loading type schedule, but no updates. So there was some stomping going on, especially if you were in the same partition and all the other fun stuff that you have to deal with.

Erik Darling: It’s funny, like big servers and big data stuff like that, it’s like quantum mechanics or something. Like getting real close to a black hole and just watching what stuff does.

 

What should we do about our badly formatted disks?

Erik Darling: Alright, we should probably answer a question at this point. Frederick asks, “I just found that a previous DBA was not careful with the virtual disk volumes. All disks were left at the default 4K block size our infrastructure team gave us. Should we worry about changing that for servers that have been in production already for a year or two?” Dude, you having problems? Are you having I/O issues? Is there something about those drives that’s not working correctly? The 64K block size thing was, I guess, a bit more important with direct attached storage. Since you’re talking about virtual disks and VMs, well, you’re probably sitting on a SAN anyway, and there’s so much junk and obfuscation going on between what you consider a disk and what the SAN considers a disk, that it doesn’t matter anymore.

Usually, when you see that kind of stuff, that’s because that is what the SAN vendor recommended. So what I’d want to do is check with whatever SAN vendor documentation, maybe open a ticket with them to make find out what best practices are. Maybe I’m wrong. Maybe in the forward going future you should set the block size to 64K, but most block sizes I’ve seen with SAN vendors, they’re like, “No just make up the small ones; we make our IOPS numbers look better with that.”

(Edit: to clarify, there can be a couple of different issues between block sizes and NTFS allocation units, but the basic idea here is that tweaking this doesn’t usually get you across the finish line. With either one, the amount of work required versus the payoff is rarely worth it once the server’s in production.)

 

How should I build my AG skills?

Erik Darling: Grahame asks – or rather Grahame states rather prolifically, “I want to build my skills with AGs and failover clusters but my organization doesn’t want to implement them. I’ve done proof of concepts in a test environment, but I want to get real world skills with AGs, how should I go about this? Offer free DBA services.” You know, a good DBA ain’t cheap and a cheap DBA ain’t good. So, don’t offer your services for free, because no one will want to deal with that.

What I would probably do is start looking for a new job where someone’s looking for those skills. If that’s what you’re interested in and your current job isn’t going to go for it, well it’s time to move on. You have to follow what you’re interested in. This is what happened to me back a couple of jobs ago when I was working for a market research place. I was doing SQL and I was doing ETL and I was like writing reports and I was doing all sorts of crazy stuff with it, but at the end of the day, I was only like DBA-light. I wasn’t getting to do all the things that I wanted to do.

I wasn’t getting to implement things that I wanted to implement and it was really difficult for me to argue structural and procedural changes with people because I was not that, and they didn’t need that. They had a bunch of sys admins who handled that stuff. So that’s when I started looking for a different job, then I started working for an e-discovery shop that needed a DBA, and I started doing all sorts of DBA things that I was very happy doing and learning about. Getting to work with failover clusters, getting to set up mirroring and log shipping and all sorts of other stuff. Unfortunately, no AGs because – well, I wouldn’t say unfortunately no AGs, because I was the only DBA, that would have been a nightmare.

But if your current job has no interest in letting you pursue or pursuing the technologies you’re interested in driving that, then it’s just time to move on. It’s as simple as that. I’m sure you have some emotional attachment or whatever, but it’s time to go, man.

Richie Rump: Well the other thing that I’d add in too is, as much as you may not want to hear this, there are a lot of speakers out there that don’t actually do the technology they teach. They do a lot of training on it and they do a lot of messing around in labs and doing all that stuff, and they don’t get into the real world-y type stuff. And I think that’s perfectly okay, because there’s actually things that you learn on the fringes when you start poking at the machine and doing different type scenarios on it. You are really seeing how things work, as opposed to if you were in a real job, you probably wouldn’t be pushing the edge on some of these technologies and seeing where the boundaries are and what happens in certain conditions.

So, if you’re interested in that, why don’t you go put an hour presentation together and submit it to your local SQL Saturday, or give it to your local user group. I started out doing a lot of that kind of stuff when I wasn’t happy with my gig and I was the manager and doing architecture and a whole bunch of stuff that I didn’t want to do and I wanted to do more technical stuff. That’s how I got my technology chops back, going out, doing demos at home, putting the presentation together and then sharing them with other people and then having conversations with them afterwards. What was good, what was bad, how does this really work for you and doing those kind of fun stuff.

Erik Darling: Because there’s stuff that you can’t talk about when you’ve only worked with something in a lab. Like it’s really hard for you to talk about best practices and like setup for DBAs, because working in a lab is totally different from working out in the real world, aside from you’re using the same technologies and the same command; but it’s usually totally different.

What I would do, if I were you, because not many people do this, is I would talk about what happens when things go wrong with AGs. Like what happens when you start unplugging things, turning things off, when will things gracefully fail over? Do demos like that because that’s engaging interesting stuff. No one wants to see another presentation of you walking through a wizard or running a script to set things up and then being like “Hey, it works.” That’s boring for everyone. It’s like when people do demos like DBCC PAGE and DBCC IND, which show you stuff on a data page… Great, cool, I know where my record is, slot arrays, woo I’m having fun.

Erik Darling: Let’s see, Rowdy brings up a good point. He said that Grahame should hit up SQL Saturdays in his area. That is an excellent suggestion from Mister Rowdy Vincent. Rowdy, I hope all is well.

 

My company is moving to Oracle…

Erik Darling: M.M. follows up, “Red tape and money is the reason for SQL Server 2014, and the company has decided that they need to move to Oracle.” Wow, you’re going to be rich. M.M., you are going to be an Oracle DBA, welcome to planet money. Wow, good for you, man, my hat’s off.

Richie Rump: Congrats on the higher tax bracket.

Erik Darling: I hope that someone from Oracle does Office Hours because I can’t answer a damn question about Oracle. It’s interesting that money would be the issue since Oracle Enterprise Edition is $47,500 per .75 cores on your server, so good luck paying that bill. And that’s just to get the database on there. If you want to do anything like the partition add in, the performance add in, the analysis add in, like if you want to look at performance, it’s that ASM or whatever, it’s like money out the Wazoo.

Richie Rump: Think of all those steak and lobster dinners the senior managers had to have, Oracle had to pay them to do that. So they’re going to have to get repaid somehow for those steak and lobster dinners.

Erik Darling: Steak and lobster ain’t free, unless you go to Red Lobster, then…

Richie Rump: Is it really lobster?

Erik Darling: Lubster.

 

What should I do about 4-5 deadlocks per day?

Erik Darling: Let’s see here, next question is from Chris, and I’m going to try to read this one as I go, which is always a mistake. I’m going to fall into the Brent trap and start reading this and then realize… [crosstalk] ‘I have Spotlight alerting me to around four or five deadlocks on a specific database per day. They don’t happen at a specific time of day, nor do they happen all at once. Should I be concerned about these deadlocks or since they are rather random and appear to be handled by the application, should I cautiously keep an eye on them and leave them alone?” So when you say they’re handled by the application, I assume that you mean that there’s some retry logic in there, which means the transaction is just like, “Oh, got a deadlock. Hit the deadlock error message, I’ll try again in five, ten, whatever seconds.” In which case, I would leave it alone. Richie, how do you handle deadlocks in the application there?

Richie Rump: That’s pretty much it. I mean, if it’s a user facing one, you have the user do a retry on it. Typically, if you’re really curious, take a peak, see what’s possibly going on in those tables during that time, if you’re really concerned about it. But if there’s no user errors and the application is kind of handling it, then just keep an eye on it. And if they start increasing then, you know, you know what to do; you’re a DBA, go do that.

Erik Darling: Really, unless users are complaining, I can’t really see a good reason to worry about it. Worry about the stuff the users are complaining about.

 

 

Can I use SSMS 2014 to query SQL 2016?

Erik Darling: Next up, J.H. asks, “I heard…” You heard through the grapevine, “That SQL Server management studio is no longer bundled with SQL Server 2016. Can I use an existing SQL management studio 14 that I already have on another machine to work on SQL Server 2016?” My question for you, J.H. – now I don’t want you to think that I’m calling you lazy, but if you have management studio 2014 and SQL Server 2016, why don’t you just try connect it?

Richie Rump: Well, you know, scratch that. Flip it, reverse it, right. So if you install 2016, you could kind of use it on anything prior…

Erik Darling: Right, backwards compatibility goes backwards…

Richie Rump: Right, so I haven’t tried it going forward yet. On most cases, I’ve found that that will work, but there’ll be some odd kind of buggy bugs. Now this is stuff that I was messing with like in previous versions, not necessarily in 2014 to 2016; but nine times out of ten, things will work unless it’s some sort of new stuff.

And there’s a whole bunch of new stuff they just announced in the last couple of days too. So there’s really no reason why not to upgrade your SSMS stuff, unless you’re like I don’t want the bugs… [crosstalk]

Erik Darling: Who doesn’t want bugs?

Richie Rump: Brent keeps telling me he doesn’t want bugs. I’m like, “What kind of crazy person are you?” It’s in software, I mean, you’re going to have them.

Erik Darling: We’re only human.

Richie Rump: Unfortunately, but yes, I would just go ahead and upgrade because they’ve been really good at getting updates out for SSMS and getting fixes and patches. The only problem is, it does take a while to install. They haven’t fixed that problem yet, but they’ll get round to it.

Erik Darling: It’s funny. So SSMS is still free, right, it was always free, but you download it separately. There’s like a separate download link and package for it. It’s up to 17.2, at least the last I checked. But what’s really funny about SQL Server management studio is that 800MB download. SQL Server is 1.2GB or 1.3Gb to download. So management studio is like two thirds the size of SQL Server. Like a relational database engine that does a whole bunch of crap versus management studio, and they’re like just a little bit…

Richie Rump: Yeah, and I bet you it’s all the visual studio crummy crumb stuff, right. I bet you it’s just all that.

Erik Darling: Oh yeah, it’s garbage in there. It’s all maintenance plans.

 

Why does Oracle get such a bad rap?

Erik Darling: Wes asks, “Why does Oracle get such a bad rap?” They don’t have a bad rap, they have an amazing rap, that is an amazing platform. The bad rap comes from how expensive it is. I mean, it’s worth it because you get a bunch of amazing stuff out of it, and people who work with Oracle are smart as hell. You have to be like a genius to work with Oracle. The commands and the stuff – it’s hard, man. I look at that and I’m amazed. If you want to be amazed by question and answer stuff, go to the website AskTom. It’s an Oracle Q&A site, specifically for Oracle database, and the queries that get put up on there and the stuff people want to do is bananas. And the answers they get are like, “Why can’t I do that?” Like there’s stuff that you can do with Oracle that’s just so developer friendly, it’s so amazing. I mean, I’m not going to say never – never say never – but it will be tough for Microsoft to implement and make performant, even though that’s not a real word, all the stuff that Oracle has.

Richie Rump: And I think it’s historical too, right? Oracle, historically, had some pretty bad interface tools, you know.

Erik Darling: They still do.

Richie Rump: Yeah. So I remember, back in the day, taking a look at those tools and saying, “Okay, where’s Toad?” Because that’s a hell of a lot better than the out of the box stuff. I think that has kind of gone along with them, even though things have gotten better in that aspect. And then everyone goes straight to cost, right?

Erik Darling: The cost is outrageous.

Richie Rump: So when you’re dealing with a high priced enterprise product like that, the folks who cannot afford that kind of will poo-poo it so that they can say, “Oh we made a better choice because it’s so expensive and you don’t get as much as you pay for.” So I think that’s why – a lot of the rap there is because of the cost. And frankly, not a lot of folks can actually afford that.

Erik Darling: Yeah, I mean $47,500 for .75 cores of Enterprise is nuts, but you get crazy stuff out of it. Oracle RAC is amazing, Flashback is amazing. The stuff you can do with Oracle really is great, but it’s so expensive and it’s so hard. Like Oracle DBAs I have unlimited respect for because they have a tough gig.

 

What headset does Erik use?

Erik Darling: We’re going to answer a quick one before we do anything else. Brian asks, “Wondering what headset Erik uses?” Good question.

Richie Rump: Audio Technica?

Erik Darling: It is an Audio Technica BPHS1. That’s Brian, Paul, Harold, Simon one. So that’s what I use.

 

Should I call C# methods from stored procs?

Erik Darling: Alright, let’s move on to something interesting. Eric asks – Eric who spells his name wrong asks, “Is it good practice to call a c# method from a stored procedure?” Is a tricycle furniture? That’s my question today.

Richie Rump: Alright, what are you trying to do and how are you calling the stored procedure? So are we talking about a CLR function that’s written in c#? It could be. I know Adam Machanic, he’s historically done a lot of interesting things with CLR and written some really performant stuff in .NET CLR in SQL Server. Now, if you’re somehow going outside of that in some sort of weird fashion to call a c# procedure, that’s probably not a good idea.

It all depends on what you’re trying to do and how performant do you need that c# thing to be. Now, mind you, there are a lot of functions, especially in 2012 On that are based on .NET stuff. Essentially you call a tSQL function and it calls .NET in the background. So sometimes when you write tSQL, it’s calling c# for you, which is actually kind of cool stuff. Format is one of those functions, I believe.

Erik Darling: I would hate that. Like just as a DBA, I would be like, “What the hell are you doing to me; why?”

Richie Rump: I mean it’s all in the background, you don’t know. I mean, from a software perspective it makes sense, right. You already have all the formatting stuff, so just plug it in.

 

Does SSMS 2017 break SSIS 2016?

Erik Darling: Kelly asks a question that I don’t have an answer for. “Does SSMS 17 still break SSIS 2016?” Kelly, unfortunately I have still never opened SSIS, to this day. Still never cracked open [bids] or whatever people who do that do.

Erik Darling: Oh, Grahame says, “Yes it does.” So, thank you, Grahame, for letting us know. Grahame, a master of availability groups and SSIS, apparently… Appreciate that, Grahame. You could do a whole presentation on that; people would be amazed.

 

What’s the best way to call PowerShell from T-SQL?

Erik Darling: David asks, “What is the best way to call a PowerShell script from tSQL without using xp_cmdshell? Is it CLR?” That’s one way. My question would be what the context is, because if you go into SQL agent, you can have a job step that’s a PowerShell script. So, if you really need to call it from tSQL, like in a stored procedure, then maybe not the best way. But if you had to call it from – I would say set up the PowerShell scripts. The agent jobs step is a PowerShell script executing tasks, then use that instead because that is probably the best way the doesn’t involve xp_cmdshell.

Richie Rump: Yeah, if you need to call it in a stored procedure, that would scare me because PowerShell just is not the fastest thing. Even if you run it on your local machine, it is kind of not fast at all. So I would think why are you doing that? Is there something where you could actually batch stuff up in an app and put it back in a database? Maybe that’s a better way of doing things.

Erik Darling: “PowerShell is getting active directory membership, the stored proc is called from SSRS.” Boy, boy howdy… That has bad idea genes written all over it. David, at the risk of offending you, I’m going to send you to DBA.StackExchange.com, because it sounds like there’s a lot more going on here than just how do I PowerShell.

Richie Rump: Yeah, there’s some good stuff going on there and it’s kind of freaking me out there. [crosstalk] Scary stuff, man.

Erik Darling: Beep, beep, Richie.

Richie Rump: They all float down here; all the PowerShells float down here.

Erik Darling: Alright, we are at the 45 mark, which means it’s time for us to go. I’m going to get back to being a monkey and writing stuff and Richie, good luck. I have interesting news, and I’m going to close the meeting as soon as I say this, so hopefully it freezes on your face. I just bought my first C++ book.

Richie Rump: The man’s crazy. He doesn’t know what he’s doing.