[Video] Office Hours 2018/6/6 (With Transcriptions)

This week, Brent, Erik, Tara, and Richie discuss index maintenance, stats updates, the acquisition of GitHub, using linked servers to move tables across, talking to developers as a DBA, the best cloud option, setting the number of tempdb files, and their favorite SQL Server features.

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 – 6-6-18

Should I run update stats before or after index maintenance?

Brent Ozar: David asks, “Is it better to do an update stats before I do index maintenance or after and why?”

Erik Darling: It’s a trick question…

Tara Kizer: I know, for real.

Brent Ozar: Do you still beat your father? No, no, wait, yes, no…

Erik Darling: I think so – at what? Chess? So, David, around these parts we’re not too keen on doing index maintenance like your indexes owe you money. It’s not really the right way to do things. All the time and effort that you expend figuring out if an index is fragmented and then, you know, doing actual work on it is kind of a lot of wasted effort from our point of view and it doesn’t really fix the kind of performance problems you want to fix.

Updating stats is a really good thing to do. I’m a fan of that. I enjoy the stats update because it does the two best parts of an index rebuild without all the crazy work involved. It update statistics on your indexes and it invalidates old or potentially bad plans in the cache. So a really smart guy named Michael Swart once commented on one of our blog posts that index rebuilds are the most expensive statistics update in the world.

So I would tend to run with that advice a little bit and say that you should not just blindly keep running index maintenance but keep on updating those stats.

Tara Kizer: And then, to answer the question though, if you are doing index maintenance, do your update stats after and make sure it’s intelligent enough to not bother updating stats on an index that just got rebuilt because you already got update statistics if you had an index rebuild. So I normally recommend to my clients, update stats daily and index maintenance much less frequently. Some of them are really – they don’t want to make big changes, so I’ll say, okay, weekly then for your index maintenance, but I really want to tell them monthly, quarterly, you know, every once in a while maybe. It’s update stats that matters.

What are your thoughts on Microsoft’s acquisition of GitHub?

Brent Ozar: Let’s see, Michael asks, “Hi team Ozar, what are your thoughts on Microsoft’s acquisition of GitHub?”

Tara Kizer: Good question for Richie…

Brent Ozar: yeah, Richie, what do you feel about it?

Richie Rump: I’d take the money – I’d take much less…

Brent Ozar: $7.5 billion…

Richie Rump: Sounds like a good deal for GitHub. Microsoft is different than it was 10 years ago. It’s different than it was in the Ballmer era, it’s different than it was in the Gates era. They’re much more open to open source. If you didn’t know, Microsoft is the number one contributor to Linux. I mean, who’d figure that? So I think it’s a good deal for GitHub, I think it’s a good deal for Microsoft. From what I’ve read, they say they’re not going to really do much to anything to GitHub, but if you take a look at any of their dev presentations for the last three years, they’re all using GitHub anyway, unless they’re specifically showing something from the guys out in Charlotte, South Carolina. That’s where the Visual Studio Team Services team is.

Unless they’re showing something like that, it’s all using GitHub stuff. And all the Microsoft stuff, even Microsoft Docs is now over on GitHub, so it just made sense that they have all this money, they might as well put it into a system that they use, they love and hopefully they’ll continue to have it grow and more focus on the Hub part as opposed to the Git. So I like it.

Brent Ozar: And we use it ourselves. We won’t change off of it. If anything, I get more excited about it because we’re Microsoft people. We do all kinds of stuff on a Microsoft stack, so anything…

Tara Kizer: What’s this, “We people?” I mean, we don’t all use GitHub…

Richie Rump: Yeah, hey Microsoft person, what computer do you use again, I forget?

Brent Ozar: Ah yes, an Apple, yes. Somebody was like, they were telling me, are you going to leave all your stuff off of GitHub because you’re one of those open source people? I’m like, I use an Apple, I make a living with SQL Server, I really don’t care whether my stuff is open source or not. I mean, we give out open source stuff, but the things that we build aren’t open source. Our SQL ConstantCare is closed source.

We had a discussion about that when we first started too. Like, PasteThePlan, should it be open source or not? And then SQL ConstantCare, should it be open source or not?

Richie Rump: I mean, I think we’re a perfect example of how Microsoft has changed, you know. Back 10 or 15 years ago, I was 100% Microsoft stuff. I didn’t use anything else other than that and now I’m using AWS, I’m using other database tools, I’m using NoSQL tools. I’m still using .NET; I’m still using all the Microsoft IDEs, I use Visual Studio code, but there’s all this other stuff that has come into my development environment and they’re great. Microsoft stuff is still great. We still use SQL Server, obviously; we’re a SQL Server shop. But there’s other stuff that’s great too and I think that Microsoft has just kind of embraced that as well. Say hey, we make good stuff, other people make good stuff. We could leverage this stuff in our product and purchase other stuff like GitHub and hopefully not make it suck. Hopefully, it’s better than their purchase of Skype.

Brent Ozar: Skype – one thing everybody immediately goes to. And LinkedIn seems to have set a good precedence. I don’t use LinkedIn – I use it as one of those things where I go, okay I’ll accept a bunch of connections. My general filter on LinkedIn is do you have a picture? Because if you don’t care enough to have a picture then I’m not even going to bother connecting. And it’s not like I look at what you look like, I just see, is there a picture or not? And then second, does it say recruiter? Because I’m not touching a recruiter. But as long as they have a picture and they’re not a recruiter, I’m like, yeah, sure, whatever, car dealer, sure, whatever, professional photographer, whatever.

Can I use a linked server to move data between two instances in different domains?

Brent Ozar: Teschal asks, “Is it possible to create a linked server between two SQL Server instances in different domains? I want to move tables from one instance to another, but they’re in different domains.”

Tara Kizer: Oh boy, why are you using linked servers to do this job? This is not a job for a linked server. Use SSIS. Use the import-export wizard. I mean, use other features. Yes, you can do it via linked server, but it is not a great feature as far as moving data around between two servers. And it’s really easy to set up a linked server and use SQL authentication, but there’s security concerns there/ it’s just not the right tool. Use the right tool for the job.

Erik Darling: Tara, I notice that you didn’t say replication…

Tara Kizer: I’m assuming this is like a onetime move, you know. I mean, if it’s moving data all the time and if you can’t use availability groups then I’ll say, okay, replication.

Richie Rump: SSIS, I mean, this sounds like an SSIS problem. If you need to move a planet, you use Superman, but if you need to go and kill a crime-lord, you use Batman. So this seems like an SSIS problem.

Tara Kizer: I like it.

Brent Ozar: That’s pretty good.

How can I approach the developers to say SQL Server is tuned and that the application needs to be looked at?

Brent Ozar: Corey says, “Sites like Stack Overflow scale by using tools like Elasticsearch and Redis to offload requests from their expensive database server. I work in a company that has SQL Server, but we don’t have any of those tools to offload requests. Developers complain about slow performance. Assuming I’m the greatest DBA…” I like that. that’s a good one. “And my server is tuned to the max, what’s the conversation I can have with developers to say I’ve tuned everything I could and maybe the application needs to take a different approach?”

Tara Kizer: I’d be looking at BlitzCache output and look at the XPM sort order, the transactions per minute and seeing what’s in there. I bet you, you’re running queries a lot that they don’t need to be running a lot. You could be caching the data.

Brent Ozar: I’ll fire it open to show you. So if we open up – sp_BlitzCache is a tool that originally was written by Jeremiah Peschka and is these days managed by our good friend here, Erik Darling, because it has XQuery in it, and that’s like kryptonite to the rest of us. We’re like, yeah, no, sorry, unsubscribe. BlitzCache sort order equals – XPM stands for executions per minute. This will show you the queries that are running the most often on your SQL Server. And if course, mine there’s going to be nothing in here because I just started it up and IntelliSense started running. But if you scroll across just a little, there’ll be an executions per minute column. Might as well throw in a select from users, have it run a few times, select count star from DBO users. Actually, you know what, I’ll do if I want high XPM, select top one star from DBO users, go 1000, 10,000 whatever. And then as the thing starts to run more often, it will float up to the top of sp_BlitzCache. Of course, it’s not going to happen yet because we haven’t had a minute pass by, but you can also do, in cases like this, check out sp_BlitzCache sort order equals executions.

Tara Kizer: That’s one I don’t use ever with my clients. I see it in our Excel file, but I just don’t use that one.

Brent Ozar: yeah, so here he goes, there’s our select top one star from users and then it shows you the query has run X number of times. Yeah, that’s good. What else would we tell him if he wants to tell his developers to look at the application code?

Richie Rump: You need to point out the queries that are going bad. The developers don’t know and if we go up against our development or test, everything is fast and then when it goes to production, everything is slow, so you need to be the one going into the queries and seeing, hey, do I need an index here, do I need this there? What if this query needs to be changed because we’re using recursive whatever and blah, blah, blah – that needs to be you, dawg. You’re the best DBA in the world and now you need to prove it. So yeah, you need to go into it, you need to take a look at all the queries and you need to say this query is slow, this query is slow, even if we shave a half second off this, because it’s running so frequently then we’ll be saving X amount of time in and the system would be that much faster. So yeah, you need to dig into that, bro.

Erik Darling: The other thing is that, you know, when developers complain that something is slow, you need to figure out A, what they feel slow is. Like, you need to set some basic guidelines, like okay, what’s slow, like 100 milliseconds, half a second, one second, two seconds, and then show me what’s slow to you. Like, physically run something in the application that you feel doesn’t happen fast enough, then let’s try to repro that on the SQL Server itself. Because if you run that query on the SQL Server and it’s not slow then it’s slow in the application and fast in SSMS and you might have a totally different set of problems.

Brent Ozar: Oh, then you know what – there’s one thing I should say too about the last question. And god bless, Corey, you probably are the best DBA in the world, but for the folks who are reading this later or watching the Youtube video, whatever, be really careful about pointing the finger somewhere. Very often, when I was a database administrator, I’m like, it’s your application; your application sucks. And they’re like, well we just hired in an outsider and they found out that it turns out we have no indexes in our tables. What, who, wait, what? You know, or I’d totally hosed all the storage.

Richie Rump: Brent bad guy Ozar.

Brent Ozar: Brent idiot Ozar too.

Should I add multiple tempdb data files and use trace flag 1118 even without tempdb contention?

Brent Ozar: Michael says, “If you have tempdb contention that isn’t related to PFS GAM or SGAM pages, is it still wise to increase the number of files and set the…” So basically, what he’s asking is, should I set the number of tempdb files that I have and use that trace flag 1118?

Tara Kizer: I like to go, you know, as soon as I install SQL Server I go to best practices and that’s just how I configure it. And Microsoft does recommend trace flags 1117 and 1118, so I’m just going to apply those and increase the number of files up to eight, depending on the number of cores. So right off the bat, I’m just going to best practices and then I might change things later on depending upon what I see.

Erik Darling: yeah, I mean, those things became the default in SQL Server 2016 installer, like trading one tempdb data file per core up to eight cores and trace flags 1117, 1118 and a whole host of other ones just became default behavior because they were such no-brainers to turn on on modern SQL Servers with, you know, any kind of workload on them. There’s really no reason not to have them. I would be interested to see you turn those on, come back next week and tell us if you’re still having the same problems. Because if you’re still having the same problems after that then this is a completely different discussion; like what the hell are you doing in tempdb, my friend. Like, 40 concurrent sessions with four billion row temp tables getting created and then – I don’t think all the trace flags in the world are going to help.

Brent Ozar: No files…

Why does Brent always have to do such awful things to databases?

Brent Ozar: Next up, Joshua says, “Why does Brent always have to do such awful things to databases, a la today’s post.” So I will show you guys today’s post because it’s awesome…

Richie Rump: So you don’t have to…

Brent Ozar: Yes, so you learn them and you can take them to work. I loved this particular thing. I wanted to write a select where everything was keywords. So select select, from from, where where, like like, and that’s valid and that compiles. That’s kind of cool. You can put spaces in a query. Like, you can break this up, you know, query across spaces. You can break them across lines, but this one is the one that I really find delightful. This is so gorgeous. It’s only a picture on the WordPress page. I’m going to go get it from GitHub because it’s really nice when you copy-paste it into SSMS and it looks like it shouldn’t even work.

I’m going to go paste it in here – oh, this tab is hosed because of this go 10,000 times thing. I didn’t think it was going to take me seriously. I thought it would error out when I said go 10,000 because I thought there was a max. SSMS is locked up tighter than…

Richie Rump: Fort Knox…

Brent Ozar: Fort Knox is good. Golly, which means we’re probably going to lose PowerPoint here in a while too as well, but it is what it is.

Which cloud provider do you recommend for a personal lab environment?

Brent Ozar: Next we have Pablo. Pablo says, “I want to buy myself some cloud for my SQL Server lab environments. What would you recommend?

Richie Rump: A credit card with no limit.

Brent Ozar: Richie, for the record, ours has a limit.

Erik Darling: You just can’t see the limit from here. It’s like a supposed limit.

Richie Rump: I don’t know, without even trying, I’m trying, right, so there we go.

Erik Darling: Brent’s cards have a theoretical limit. You just can’t see them with normal physics, but they’re out there.

Brent Ozar: I’ve always wanted, at some point in my life, to buy a car and just put the whole thing on a credit card, say watch this and see what happens, you know. Can I try that one over there?

Erik Darling: Can I return this one, get that…

Brent Ozar: Yes, if I don’t sign it with my regular signature, can I dispute that charge? Now what – when you guys work with cloud stuff – because we’ve had the opportunity to work with Azure, we’ve worked with Google, we’ve worked with Amazon, we’ve jumped around back and forth – if you were going to tell somebody to go get started, what would you tell them? And there’s no right or wrong answer.

Erik Darling: I would say to set up an account with each of them and continue to use whichever one you find you have the easiest time dealing with because that’s going to be your main – like, if you can’t set up a server or get into your lab or make changes easily, you are going to bail on ever doing anything with your cloud environment from there on in. so whichever one you have the easiest time interacting with, setting stuff up with, that’s the one that I would go with. Whichever one you feel the best about…

Brent Ozar: Which is kind of, I think, how we feel about monitoring tools too. You should get demos and evals and you’re going to bond with one of them and you should use the one you bond with.

Tara Kizer: I bond with sp_whoisactive.

Brent Ozar: I do too. We all love sp_whoisactive. And the other thing I would say too is if you use one at work you should probably consider using that just so you get good at it.

Richie Rump: Yeah, love the one you’re with.

Should I use more than 8 tempdb data files for an instance that has 40 cores?

Brent Ozar: Augusto says, “Is it a correct approach to implement eight tempdb files in an instance that has 40 cores?”

Tara Kizer: I think he’s saying should I go above eight, so it’s plus eight. So the recommendation is to stop at eight and then if you have tempdb contention and you’ve proved that adding more files resolves it, then go higher, but otherwise stop at eight. At least that’s what I’ve read.

Brent Ozar: Yeah, a long time ago in a galaxy far, far away they said one file per core, and that’s not a good idea today.

Tara Kizer: It was the correct thing back in the day before we had more than eight cores. [crosstalk]

Erik Darling: That was an issue. Four cores, 3GB of RAM…

Tara Kizer: I remember when my database was reaching 1GB it was having some performance issues.

Erik Darling: Thomas [Grosser] tells a really funny story about how when he first started with SQL Server on, like, 4. Something…

Tara Kizer: 4.2.1, get it right…

Erik Darling: I’m not going to remember those. He used to have to go into the office 45 minutes early to hit the power button so when people started showing up for work, the SQL Server would be up and running and ready to go. Now, 25 something years later, whenever he reboots a SQL Server, it takes just as long because there’s 4TB of memory in it, it’s got to check all this stuff, go through things. So that was like 45 minutes all over again just to get the hardware up and running.

Richie Rump: There goes five nines.

Brent Ozar: Yeah, wow. Well, that’s all the questions we’ve got for today. Thanks, everybody, for hanging out with us and we will see y’all at the next Office Hours. Adios y’all.

Erik Darling: Bye.