![]() Blog - Brent Ozar. 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 You. Tube: You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go. ![]() 6 What is the #1 best example of Technical Documentation that you have ever seen? What was it that made it so effective for you? Build 123 (2017-09-25) Enhancements. It's now possible to jump between statements in the editor. For Postgres global objects are now shown in the DbTree and DbExplorer. In finance, a foreign exchange option (commonly shortened to just FX option or currency option) is a derivative financial instrument that gives the right but not the. If you prefer to listen to the audio: Podcast: Play in new window | Download. Enjoy the Podcast? Don’t miss an episode, subscribe via i. Tunes, Stitcher or RSS. Leave us a review in i. Tunes. Office Hours 9- 2. I need to update a 1. GB 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 1. GB…” My goodness, very nearly a lady. In 1. 00,0. 0 rows – I need to alter one column from . 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 Temp. DB data and logs on separate drives? Erik Darling: “When installing SQL Server 2. Welcome to almost the past. October 2nd, SQL Server 1. 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 2. 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 1. GB, you put your data files on another drive that’s say 5. GB, put your log files on another drive of 2. GB 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. . Stack. Exchange. 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 2. 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 pg. Admin. How’s that? Erik Darling: pg. Admin 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 2. R2 to 2. 01. 4…” Why? Why are you living in the past, man? Richie Rump: Jumping point, right? They’re going to go 2. 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 2. 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 6. TB thing, man. A 6. TB 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 1. TB, so I would love to see what happens at 6. TB. I would love to see what a – all the stuff, like statistics time and I/O would break on that. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |