This week, Brent, Erik, Tara, and Richie discuss database restores, opening a case with Microsoft, buying a pre-built server vs building your own, auto-shrinking all databases with a single command, SQL Server dev environment options, the best way to learn Git, excessive memory grants, and more.
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 – 3-7-18
How did my users get messed up during a restore?
Brent Ozar: Deborah says, “Last week, I restored a database…” Yay, “From a test server to a production server…” What, what?
Erik Darling: That’s more than I did last week.
Brent Ozar: “From a test server over to a production server. At some point in the process, the admin user for that database was changed to one that resides on the production server and then removed the proper database, which caused the apps admin user not to function. I found a couple of articles but no reason about why that happened. I would like to prevent that from happening in the future.”
Erik Darling: An orphaned user?
Brent Ozar: That’s my guess. So explain what orphaned users are.
Erik Darling: Oh, I can’t. I just remember having to do that when I restored databases a long time ago.
Tara Kizer: Every single time you restore, you run the script.
Brent Ozar: So when you create a login in SQL Server, it creates a SID; a security identifier. And you don’t usually get to pick the SID; it just automatically creates a SID. You could create with a specific SID if you wanted to, but it’s unique to that login on that machine. You can create the same login name on a different SQL Server, but when that database comes across, it’s got a different SID. So the username may not be able to log in if it’s the wrong default database or a default database that person can’t access. So there’s a stored procedure you can run, sp_change_users_login. And I never remember where the underscore is…
Tara Kizer: It’s all of them, I think. I think it’s all, yeah…
Brent Ozar: It might be.
Richie Rump: In between each letter there’s an underscore.
Brent Ozar: Worst casing ever. Yeah, so sp_change_users_login or you can just drop that login on both servers and then recreate it with the same SID. Anytime you Google for sp_change_users_logins, you’ll find more information about how to drop and recreate that guy the right way permanently.
How do we open a case with Microsoft?
Brent Ozar: Sree asks, “How do we open up a case with Microsoft? I’m just wondering about the process if I ever have to open up one.”
Tara Kizer: I always save the 1800 number in file so that I have it because it was hard to find the phone number, but I was a Premier Account, and I don’t know if that’s available to everybody.
Erik Darling: yeah, I think how you open a case depends on what level of support you bought into, you know. I get nothing from Developer Edition, honestly. Joe Sack just responds to my emails graciously once in a while. That’s fine, that’s all I get.
Brent Ozar: What do you email him? Do you just say, “Hey honey, how are you doing? Can you help me with this?”
Erik Darling: No, I text him directly…
Richie Rump: “Hey sweetie, it’s me again…”
Erik Darling: I’m going to guess that if you’re asking that question, you don’t have like a dedicated person that you email.
What are work files and work tables?
Brent Ozar: Let’s see, Pablo says, “Ola, amigos. How do I interpret work files’ and work tables’ logical reads? Is it always on tempdb?”
Erik Darling: Yep. So work files and work tables get spun up, usually for hashing operations. Sometimes, as you’ll find out in an upcoming blog post, they happen for merge joins when they are many to many, when there are duplicates in the join columns. But yes, they are all directed to tempdb; that’s where they do their dirty work. It’s the same number of 8K page reads that you would see for a regular table or lob logical reads or any of the other stuff.
Brent Ozar: Kevin has a follow up on the support case and this horrifies me. Kevin says, “Something to know – last time I opened a case with Microsoft, I found out they’re outsourcing it to a company called Convergys, or something like that.” I know it’s been different by product too, and like for out-of-support products they’ve farmed it out too before.
Tara Kizer: Nobody wants to work on it.
Brent Ozar: No – you call in with a merge replication program and… Hold on…
Erik Darling: I’m going to guess that Kevin either has SharePoint or Dynamics if he’s getting sent off to someone else. He’s probably getting sent off to some like, you know, Microsoft partner consultant who does that kind of work.
What kind of camera is everyone using?
Brent Ozar: Brandon asks, “What kind of camera is everyone using to cast themselves?” I think we all do have different webcams. What webcams…
Erik Darling: No, we all have the same… [crosstalk]
Richie Rump: You bought us all the same one.
Brent Ozar: You all bought the BRIO. We all use the Logitech BRIO then. Logitech BRIO – it’s 4K. It has really nice light sensitivity. I like it a lot. The only thing is that it says in there that it’s got a tripod mount on the bottom, so if you’re like me, I tried to unscrew it so I could put it on a tripod mount. It’s not a real tripod mount, so all I’ve done is broken the mount on my webcam, so now it kind of flops around on the top of my screen and I got to go get a new one. I can’t turn it in under warranty either because it’s just clearly that I broke it.
Should I build my own home lab, or buy something off the shelf?
Brent Ozar: Udai asks a question that we will have passionate responses about. He says, “I am trying to build a home lab and I wanted to know if there would be an advantage to buying a pre-built server, like say a Dell PowerEdge, versus building a machine using consumer parts?” Tara, you have one – we’re all at different camps here. Tara, your hardware budget is up. What do you think about doing with your hardware budget this year?
Tara Kizer: I still haven’t submitted it. Dell XPS 8930 – I don’t want to have to mess with it. I want to just get whatever hardware I need and have them test it and have a warranty on it. Whereas Erik bought a piece here, bought a piece there and then dealt with all sorts of incompatibility issues. I just don’t have the patience for that and, frankly, I don’t have the knowledge for that type of stuff. I make Mike do that kind of work and he keep saying, “Let’s build it from scratch.” And I’m like, “No, I really don’t want to.”
Erik Darling: But it’s so gratifying when it gets working. I actually didn’t have too many problems with compatibility. I used a website called PC Part Picker and I was able to get all that stuff weeded out upfront and kind of figure out which CPU…
Tara Kizer: You had issues, though. You had to return some items?
Erik Darling: Yeah, I had to – well my issues weren’t because of, like, technical problems. They were because of dirty customer problems where someone had returned their old motherboard in a box and then whatever – I can’t remember the name of it – had sold me the motherboard open-box…
Brent Ozar: Micro Center…
Erik Darling: Micro Center sold me the open-box motherboard for like 80 bucks less and when I got home and I started looking at pins on the CPU connector I was like, “Wait a minute, I don’t think this is legit.” [crosstalk] I returned it and, of course, I walk into the store and I’m like, “You sold me the wrong motherboard. This one’s broken.” And all the salespeople are like, “Really? Tell me more…” Like no seriously, I just brought this home; it’s broken.
I don’t know, I built mine just because I got all excited about – Nick Craver had published the Stack Overflow Developer Desktop Build on his website and I was looking at that and I was like, “That’s really cool. I want to try my hand at building that.” And then I ended up buying some other stuff. And since – I got a new laptop this year. Next year I’m looking at that pieces of that I might upgrade with next year’s budget.
Richie Rump: [crosstalk] Than buy a laptop. It’s like a technical coaster is kind of what you got.
Brent Ozar: You got a hand-me-down.
Erik Darling: Yeah, well I got Jeremiah’s old laptop when I came on here, so…
Richie Rump: Oh snap.
Brent Ozar: Although it is still faster than Andy Leonard’s – and I can say this because Andy’s not here. Andy’s got the generation before and during his class, his power went out. And he and I had been talking in the morning and I’m like, “Well how’s the battery life?” And he’s like, “Oh it doesn’t work a dang. When I unplug it, it immediately goes dark quick.” So all of a sudden, we got to see a live test of how Andy’s battery countdown [crosstalk] outage.
Tara Kizer: I was stuck with an old laptop when I worked at Qualcomm for at least two to three years longer than I should have. And eventually, I just couldn’t do my job anymore. I was like, “Listen, I’m a production DBA; I need to be able to do my job.” And finally, they replaced it.
How much RAM and SSD space do you have?
Brent Ozar: Sree follows up with, “How much RAM and SSD space do we have?” So I follow – I didn’t answer either. I have a home lab of an Apple Mac Pro, which is the trashcan looking thing. I have 64GB of RAM and 4TB of SSD space. Erik, how about you?
Erik Darling: I have – let’s see – 3.6Ghz, 6 core processor, 128GB of RAM and I have my SSDs in a RAID. I bought a dock and I have 6TB SSDs in a RAID that gives me about 2.5ish terabytes. Terabyte PCI NVMe M.2 card and a big 8TB spinning piece of crap that just backs stuff up too.
Brent Ozar: Richie, how about you?
Richie Rump: Okay, so…
Erik Darling: Here we go…
Brent Ozar: Why did I ask?
Richie Rump: Save your money for fixing your plumbing. Go to the cloud. Just do all the cloud stuff. Don’t but hardware. All your databases are going to be in the cloud anyway; just go to the cloud. Use your Cosmos DB, use your Redshift, use RDS Aurora. Get to know all of them, save your dough, go to the cloud.
Erik Darling: And then what happens if you need to go someplace where there’s no wifi and show stuff that you’re doing? You can’t really.
Richie Rump: Don’t go to Doug Lane’s house.
Erik Darling: Okay, good point.
Brent Ozar: Richie goes and opens up his backpack and takes out one of half a dozen laptops which collectively have maybe 1TB of space.
Richie Rump: Actually no, each one has 1TB except for the Surface.
Tara Kizer: Mine is a lot smaller than you guys, which is why I need a new one. So 20GB of RAM. It came with a lot less but we did upgrade that, and just 500GB of disk space. I’m looking at 1TB and 64GB or RAM for my next machine. I’ll still use the laptop but I need something with more power so I can do large memory grant demos because my machine just could not keep up. And there isn’t enough disk space either.
Should developers have SA?
Brent Ozar: Kevin says, “How do you let your dev folks give themselves database owner permissions on a database without giving themselves SA permissions?”
Tara Kizer: If it’s dev – is it a question for dev or prod?
Brent Ozar: Oh god, I hope it’s dev.
Tara Kizer: If it’s dev, I let everybody have DB owner; I don’t care.
Richie Rump: Don’t [crosstalk]
Tara Kizer: Access, maybe…
Richie Rump: Don’t even give them read access in prod, don’t do it.
Tara Kizer: In dev?
Richie Rump: Give them all access, give them SA, who cares?
Tara Kizer: I don’t give them SA because then I have to fix the server, but the database I can at least easily restore the database.
Richie Rump: Let them go off, let them screw with it. Let them play with the new features. Let them do all that stuff. This is how they learn and try to do all the bad ideas that they have like user service broker and all this other crazy stuff. Let them do whatever they want in dev. Now production, don’t even give them read access. Forget it. I mean, it’s the same thing I did to Brent; I didn’t him…
Brent Ozar: I was just going to say, I should point out the irony here that Richie is our developer and Richie has complete access to every environment, development, production, whatever. I own the company and I don’t have write access to our production databases and I don’t want it.
Richie Rump: And there’s a reason for it, Brent. When you stop breaking the builds… SO a few weeks ago, I put in database tests, he goes off and changes a value [one in database creation scripts] and it breaks the build.
Brent Ozar: Yeah, I’ve got a 100% build failure rate.
Richie Rump: But hey, the work, right? The database tests, they work great.
Tara Kizer: Wait, one of my managers, a few jobs ago, he was my mentor 20 years ago, he was the reason I became a SQL Server DBA and got good at things. He later moved away from SQL Server and into management and then other technologies and he then became my manager again, and here I’ve got this extremely mission-critical SQL 2012 Availability Group system; a very complex system. He’s like, “Oh I probably should get access again.” It had been years since he touched it. I was like, no, nope, can’t have it now.
Brent Ozar: Nope, no good is going to come of me touching our production SQL instances.
Can I shrink all of my databases with one command?
Brent Ozar: Andy asks, “Is there a way to auto-shrink all of my databases with a single command?”
Tara Kizer: Oh Andy – I was going to say, this is a troll.
Brent Ozar: And there is, because I had a client doing this. You run sp_MSforeachdb and then DBCC SHRINK-whatever and it’s unbelievable. It actually works.
Richie Rump: I thought that was sp_shrink_Buck_Woody…
Brent Ozar: Ah yes, that’s why you don’t give him access to the database.
How do I log everything anyone does?
Brent Ozar: Let’s see, next one, Anon Ymous says, “An IT group wants us to start logging in individual logins including those with inactive directory groups what changes and what actions they perform. I believe this would be difficult for us and will impact performance on our low-RAM server.”
Erik Darling: How low? Not that I think more Ram is going to help this, but Brent had the best answer to this. I forget if it was in Office Hours or via email or something a while back, where it was just like, imagine if you came into work and instead of just doing things, you had to write down everything you did before you did it, then go do something, then go write down what you did after you did it and what changed, then that was your day. And just imagine how much slower your life would be if you had to take note of everything you did before and after you did it. That’s kind of how SQL Server’s going to treat the whole experience. It’s not good for you.
Brent Ozar: It’s never going to get faster, that’s for sure. Yeah, if you really want to audit who does what, you buy a third party appliance like Guardium or Imperva and they sit in between SQL Server and the network, they log everything. They’re supposed to work at wire speeds, but these are six-figure appliances; they’re not cheap.
Tara Kizer: [crosstalk] you get this with low-RAM server.
Brent Ozar: Low-RAM server.
Erik Darling: You know, that’s a better name for it than, like, SQL01, like, low-RAM.
Brent Ozar: Prod42…
Should I use Docker or Kubernetes for SQL Server development?
Brent Ozar: Joseph says, “What’s a good resource for me…” And I’m going to step back and ask a bigger question, Joseph. Should I use Docker or Kubernetes to use SQL Server development environments?
Tara Kizer: I’ve never heard of Kubernetes. Is that how you pronounce it? I feel like there’s extra…
Brent Ozar: He couldn’t spell – he couldn’t write it…
Erik Darling: Well if you can’t spell it, I can’t answer it.
Brent Ozar: Richie, is there anything that would make you choose Docker or Kubernetes for a SQL Server dev environment?
Richie Rump: I don’t like either for SQL Server. I haven’t jumped onto the Docker bandwagon yet. It’s easy to install and everything, but there’s value in going through the install experience yourself and picking what things you want and whatnot, especially for a developer, you know, just getting into SQL Server. And I’m assuming you’re going to want a developer instance on each developer laptop. I guess I would go Docker, if you put a gun to my head, but I haven’t jumped onto the whole Docker thing. I’m a bare metal type guy. I work for Brent Ozar unlimited. We do database stuff. What makes you think I wouldn’t be a bare metal guy? I mean, I think that was one of the questions, “What do you think about virtual machines?” Bare metal, it runs faster I guess, I don’t know…
Brent Ozar: This is coming from the guy who runs serverless. Not only is the OS abstracted away but the platform is. We could be running on Commodore 64s on Pluto; we wouldn’t know.
Richie Rump: Yeah, but I don’t have to worry about that, right. I mean, I don’t have to worry about spinning them up or anything, it’s just it’s there, and it’s there in less than a second, most of the time. I’ve been keeping track of it.
Brent Ozar: Joseph says he owes his “Six figure salary largely to you guys. Really appreciate what you do.”
Erik Darling: We take cash, checks, booze, steak, however, you want to owe it to us, you can choose and we’ll graciously accept…
Richie Rump: Japanese whiskey.
Brent Ozar: No Docker containers. Don’t pay us in Docker containers.
Erik Darling: None of those fancy PowerShell dollar signs either. I know those tricks.
Brent Ozar: Query bucks, we print those ourselves.
What clients do I use for Git?
Brent Ozar: Anon Ymous follows up with, “Hey Richie, I am a noob researching Git for version control and I don’t understand what we would need. Our company has a Git repository, but what clients do our developers need? What is like Git tortoise? Do they need Visual Studio Team Services too? What should I do to start this from scratch?”
Richie Rump: All you really need is Git for Windows. And I believe Phil Hack and team at GitHub has a nice client for that. There’s also a GitHub, or Git for Windows, for JavaScript that you could download that works pretty well…
Erik Darling: [crosstalk]
Richie Rump: I know, I use Sourcetree occasionally. So if you wanted a client, to use that. But really, the best way to learn Git is through your command line. I kind of recommend that for everyone getting used to it because then you understand the bare metal, how everything kind of works, and then you can graduate to whatever client you want to run. You don’t need Visual Studio, you don’t need anything like that. it’s all kind of right there. A tutorial that may help out, Joel Spolsky, and I believe it was for Mercurial, but Git and Mercurial are so close to one another that you could just walk through the Mercurial tutorial and he has a really great way of explaining the concepts just in that one. I forget what it’s called. Google Joel Spolsky Mercurial tutorial, or whatever, and you’ll get that. Walk through that. It’s only a few pages. And you’ll get a better idea of how Git works and the Git workflow and whatnot; from a Mercurial standpoint.
Brent Ozar: It’s so weird too, like distributed version control is so different from what I used to deal with, with visual source safe and team foundation and all that. It’s very different. And good, I like it.
Richie Rump: Yeah, it’s so much better. I mean, I’ll take this for an example. Yesterday, Brent made a check in and there was a conflict on the merge – no, it was a fine change. There was a conflict because I had also changed the same file. I went to GitHub and said here, let me go ahead and merge it. I did the manual merge; just deleted what was wrong and just kept what was right and hit submit and boom, everything was there. But he ran it on his own machine, I ran it on mine, all the tests passed, everything looked good, just when we kind of merged it together, that’s when kind of this special sauce of the distributed – everything kind of works where you’re at, and when we kind of put it all together, that’s when some of the problems happen. But for us, 90% of the time, 95% of the time, no merge. Everything works great.
Erik Darling: Everything works great because there’s no merge.
Brent Ozar: There’s no conflict, yeah.
Richie Rump: No, it does the merge but there’s no conflicts, right. There’s no conflicts.
Brent Ozar: Well also, 95% of the time you’re the only one doing the development.
Richie Rump: Hey, I’ve messed myself up. Left in multi-branches, you know, because I’m working on different bugs at the same time and, you know, you collide with yourself. It happens.
Erik Darling: I mean, my only experience with it is GitHub desktop and I was surprised at how easy that made it to, like – like it’s really intuitive the interface. Like I had to go into someone else’s pull request and do stuff and I was able to do that really easily, just like a couple of dropdowns and there I was and I could fix things. Because I’m dumb, like, if you want to think about a target audience of the lowest common denominator you need to explain things to with pretty charts and drawings and stuff, I’m it. So if I can figure out a user interface, I’m amazed. I’m like, wow, that’s…
Richie Rump: GitHub for Windows is – GitHub Desktop I guess they call it now – but it’s really improved since it originally came out. It’s a lot, lot, lot better.
Should I shrink the database regularly?
Brent Ozar: Sree asks, “If we have tables where millions of rows are inserted and then deleted, like loading happening, do we need to shrink the database or should we just update stats to bring back and not play around with used space?”
Richie Rump: Shrink everything.
Tara Kizer: I mean, how often are you doing this? Inserting certainly won’t be shrinking it, but deleting – this is a one-time shrink where you’re purposefully deleting a lot of data in one recover space. One-time shrinks I’m okay with, but not regularly. It sounds like this is more of a scheduled process. I would be doing shrinks, for sure. I never, ever schedule shrinks, for sure, ever.
Brent Ozar: Why don’t you? What’s the bad thing of scheduling shrinks? What does it do in terms performance?
Tara Kizer: Well, I mean, the shrink, it can cause blocking, but that’s not really the reason why I wouldn’t do it. It’s just because I’m going to need that space again, you know. It also causes fragmentation and, you know, there’s just no reason for it. When you have to auto-grow back out, that’s an expensive operation, if it’s the log file especially because you don’t get instant file initialization, even if you have that setup, it has to be zero initialized. So if your auto-grow is 1GB, there’s going to be a pause. It’s going to probably do it fast, unless you’re on some kind of slow I/O back in.
Richie Rump: Slow SAN…
Tara Kizer: Slow SAN.
Brent Ozar: We know about this week.
What causes excessive memory grants?
Brent Ozar: Steve says, “Any general comments about what causes excessive memory grants?”
Erik Darling: Excessive memory.
Brent Ozar: Having too much of it lying around, you just end up giving it away.
Tara Kizer: Varchar MAX.
Erik Darling: Yeah, so the two main things in SQL Server that will cause a query to get a memory grant are sorts and hashes. So you need to sort data because you don’t have an index that supports the sort order, you get that. Or if SQL Server chooses a hash join or a hash match, it will request memory to do that. Tara is absolutely spot on; one of the things that impacts the size of the memory grant is the size of the data that needs to be processed via the sort or the hashing operation. So if you’re thinking about logs or string data, that will certainly impact the memory grant. The number of rows that have to be sorted will impact the memory grant and all sorts of other good stuff. Poor cardinality estimates can certainly contribute to that, but most of the time it’s just something that you need to fix or tune with or query your indexes.
Brent Ozar: Alright, and that’s it for this week’s Office Hours. Thanks everybody for hanging out with us. And we will see y’all next week; adios.
