This week, Brent, Tara, and Richie discuss increasing a table’s varchar field from 1 to 2, predicting how long CHECKDB should take, SQL Server configuration options, reporting, reducing high waits, analysis services for SQL Azure, advice to first time PASS attendees, compatibility levels, cardinality estimator, PowerShell, and Tara’s hairdo.
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 Webcast – 2018-10-24
Should I use SSIS to manage AG backups?
Brent Ozar: Let’s see, Josh asks, “We have a suggestion for backup jobs to use an SSIS package around to move the jobs to whatever node is the primary in an Availability Group. Have you seen this setup and can it work well?”
Tara Kizer: I haven’t seen that for the Availability Groups that I’ve supported. We had two different methods at three companies that I had Availability Groups. But at the last one, we just had a server that would run the backups and it would just point to the listener name. So all of our backups, index maintenance, anything that needed to connect to a specific replica, we would just put the jobs on that server instead and it would be a SQL CMD and it would point to the listener name. And we did that for the backups because we wanted the backups to be on the primary replica. I don’t necessarily agree with offloading such a critical task.
Brent Ozar: And why not?
Tara Kizer: Well even on a synchronous replica, it’s not completely up to date. So that is just a good idea if your RPO goal is so low.
Brent Ozar: Between that advice too, and if you think about an SSIS package that would move stuff around, there’s going to be a delay there. Like, your backups won’t be up to date until the SSIS package moves a job around. And if that job process breaks, you’re not getting backups. I’d be like, I can’t unsubscribe fast enough from that plan. What I would do there instead is Ola Hallengren’s stuff. Ola Hallengren’s backup scripts will automatically run on every replica and just backup wherever you tell it to; like, if you want to prefer a secondary or if you want to prefer the primary. Configuration’s not super simple on Always On Availability Groups, but it works.
I need to go from VARCHAR(1) to VARCHAR(2)…
Brent Ozar: Pablo says, “Hello, friends…” Hello Pablo, “I have an 800GB table that needs to increase the size of a varchar field from one to two. What way would you recommend doing that to reduce impact?” Isn’t that an instant change?
Tara Kizer: Yeah, I was going to say, I don’t know that this is going to take a while. Run that on a test box.
Brent Ozar: Yeah, I’m pretty sure that that’s going to be instant.
Tara Kizer: Yeah, and if it isn’t, I would probably just move the column to another table. I would cheat because your table’s just so big. Remove the column – I realize that there’s a huge impact there, but…
Brent Ozar: Somebody had a really good post just recently about a switching cups way to do it. What you do is add a new column with the new data type that you want, but with a different name. You put in a trigger so that whenever an update happens, it sets the new column over to the old value’s name. Then you go through and roll through in batches of like 4000 rows at a time, gradually updating the new column. Then you switch column names.
Tara Kizer: Oh, I like it. [crosstalk] I mean, it’s 800GB. I mean, we don’t even know the row count, because maybe it’s 800GB because of data, you know, the size of a row.
Brent Ozar: Yeah, let’s try it and see. And I’m just going to grab, out of the Stack Overflow post table, I’m just going to grab one of the columns post-only because – oh, you know what I’ll do, find out if votes has a varchar in it. No, it doesn’t. Posts has a varchar and posts has a decent number of rows in this. I’m going to change title from nvarchar 250 to nvarchar251. Alter column, and then I’m going to freeze because I get a spinning beach ball. Oh, that’s excellent, c-c-c-c-column. It’s like some kind of pop song.
What did I say I was going to do? Title nvarchar 251, and let’s see if that happens instantly. So the next thing I’m going to do is switch into the right database, then I’m going to run it. There you go. Yeah, that’s a metadata change. It should happen instantly, regardless of the number of rows in the table. Love it when we can give people good news instead of bad news.
Richie Rump: It doesn’t happen often.
Brent Ozar: It does not happen often.
How long will CHECKDB take?
Brent Ozar: So next up, Brian says – Brian, this is a great question, “Is there a good way to predict how long CHECKDB should take? I’ve tried to space out my various jobs, but sometimes they step on each other’s toes with my jobs running into business hours on the next morning.” Any ideas, Tara, Richie?
Tara Kizer: I mean, you know, CHECKDB, I don’t even like to run that on a production server. I offload that task to another box which, obviously, you have to license that other box. But I like a box that maybe doesn’t have a lot of hardware, but it can churn through just running CHECKDB in all the databases. Eventually it completes, and maybe it’s doing the work for more than one server, but it might have lesser cores for licensing reasons, because you do need to license it.
Richie Rump: I mean, do you need to license that?
Tara Kizer: You do.
Richie Rump: Wouldn’t the development work?
Tara Kizer: No, because you’re offloading a production task. That’s the key there.
Richie Rump: Semantics.
Brent Ozar: If you just happened to be restoring it every night into your development environment and you wanted to make sure that your development environment was okay, that’s totally still not legal, but there we go.
Brent Ozar: Marci says, “Hey, did you get my question?” Yes.
What should my setup defaults be?
Brent Ozar: Jason says, “When you helped us configure our SQL Server 2012 database a few years ago, you recommended that we start with MAXDOP 8, cost threshold at 50, and eight tempdb data files and then tweak from there. We’re not switching to 2016. Do you recommend we start those same settings?”
Tara Kizer: Yeah.
Brent Ozar: Assuming you’re going to the same size or bigger of a box. Heaven forbid you’re going to a two core box or something like that.
Tara Kizer: I don’t know if this is in our set of checklists. I don’t think it is. But on 2014 and lower, it’s recommended to have trace flags 1117 and 1118, but on 2016 and greater, you don’t need that anymore because it’s on by default for tempdb specifically. The trace flag on older versions would apply to all databases, unfortunately, but still recommended. But yeah, I don’t know if Jason would have that in place anyway, but that would be the one change if I’m setting up a new 2016 or 2017 server; I’m just not going to be putting those trace flags in place like I would on older versions.
Is the color right on my monitor?
Brent Ozar: Michael says he is, “In shock and awe of Tara’s new hairdo.”
Tara Kizer: Me too. It’s going away this evening. I have an appointment. Not all of it, it’s just too much red, you know. It needs to have a little bit of brown here because when I’m on these calls with my clients, I see the webcam picture and it’s driving me just bat-shit crazy.
Brent Ozar: Really? Oh…
Tara Kizer: Yeah, because you don’t have to look at yourself all the time in your normal day. You look at yourself in the mirror when you’re in the bathroom and that’s about it. But the webcam is like, oh my god, my hair is so red.
Richie Rump: But we do have to look at Brent all the time. It’s that required photo of Brent on our desk.
Brent Ozar: It’s got the little googly eyes on it, they follow you around the room.
Richie Rump: Oh really, I thought those were cameras and you were just watching us all the time.
Brent Ozar: I don’t want to see you people. You don’t even put on clothes for your job. You’re still wearing your PJs from yesterday. I know how this works.
Tara Kizer: I did actually shower this morning. I don’t have my PJs on today. I normally do. My PJ shirt clashes with the hair because it’s like a pinkish color, so it’s like oh my god, I can’t wear that.
We have a corrupt SSAS cube….
Brent Ozar: Marci says, “We have an SSAS cube whose refresh job is in SQL Server agent and it failed with physical file corruption…” why don’t I read these questions before? Marci… “I have job notifications turned on so I was aware of the failure, but I want other email recipients to see the job log as well without having to log into SSMS. SQL Server doesn’t appear to have an easy button to include the agent job log in an email notification, or am I missing something?”
Tara Kizer: You could set up something fancier for the failed jobs, but the failed notifications, it’s not going to send that out to you. You would need to go into SSMS. But how often are jobs failing? You know, this type of job…
Brent Ozar: Wow, that’s – okay, now I’m going to get really fancy and terrible. You could do something like, since you’re using SSIS already, you could look in MSDB’s agent job history tables and if there’s a failed job, you could go look at the file, pick it up, and email it to somebody. That, I think, would be relatively trivial as an SSIS job, not an agent job, to not T-SQL code. I wouldn’t want to do it in T-SQL code; like trying to pull XP command shell and get a file. But I love the idea, that’s really cool.
Richie Rump: I’m sure you could do it in PowerShell with some salt or Salt Bae or something.
Brent Ozar: And if you need any help with that, it’s Richie@BrentOzar.com…
Richie Rump: Actually, I just changed my name to Erik Darling…
Brent Ozar: It’s been a while since we’ve had Erik do anything with PowerShell. He’s not on the call. We should probably assign him something.
Richie Rump: Totally.
Tara Kizer: I’m the only one in the company that likes PowerShell, so you can send it to me. And I don’t really necessarily like it, I just don’t hate it. You guys hate it.
Richie Rump: I don’t hate it. I just don’t think it’s the right tool for every job. I mean, I take a look at it from a programmatic standpoint and a developer standpoint and it’s like, why do I want to do everything in this? That doesn’t make any sort of sense, which is what a lot of DBAs kind of force. Oh look, here’s my hammer; the PowerShell hammer, everything’s a nail.
Brent Ozar: And we’ve had that same feeling about all golden hammers. We had a question a while ago from someone who wanted to use T-SQL to do bulk copy inserts from files with changing columns and all this. And we were like, no, there are different hammers for different purposes. SSIS is a great hammer for that…
Richie Rump: Well I mean, it even works for SQL Server features, right, cues and things, let me go in and just – what is that silly file thing that they had a few years ago?
Brent Ozar: Well there were two; file stream and file table…
Richie Rump: yeah, file stream, it was like, what? No, no, stop, just stop.
Brent Ozar: Great moments and bad ideas. I say that casually and then this is when all my Microsoft friends come out with knives and they’re like, what do you mean the feature I implemented is a bad idea? Well it just wasn’t a great idea. It wasn’t your best idea.
Richie Rump: Well I mean, the use case is a very thin and narrow use case where it is a good idea. And then people take a look at that and say, oh it’s a new feature, then I can use it for everything. And that’s when we start getting problems and they start calling us and…
Brent Ozar: And it seems like Books Online never says, this is a bad idea; this particular feature is a bad idea in these circumstances. So I was thinking the other day, the pages for scalar functions, table variables, et cetera, in Books Online, they should really have a disclaimer at the top; hey, bud, before you use this feature, here are just things to be aware of. And I thought, well it’s also open source. I could go write that and check that in as a pull request. And I think we all know how that story would end.
Tara Kizer: The thing is about Books Online, MSDN, is that at least for my usage, I don’t even look at the top of the page. I immediately scroll down to the examples. So I’m going to miss that it’s a bad idea anyway. I am only going out there to look for examples.
Brent Ozar: Yeah, so the other thing we should do is, down in the code examples, we should put in code that specifically doesn’t work. Like, make it break so people go, I copy pasted it and it didn’t work… then they won’t read the directions and they’ll just stop.
Can I ignore CLR_SEMAPHORE waits?
Brent Ozar: Joe says, “I’m seeing very high waits for CLR_SEMAPHORE; 300 seconds in a 30 second sample. Are these truly safe to ignore? Is there anything I need to do to reduce these?” For this one, check Paul Randal’s wait stats library. Anytime someone asks a wait stats question, I always want to make sure that they know about that. So if you fire open the web browser – and usually, even if you just search for the wait type – Richie is moving his webcam.
Richie Rump: Yeah, it was crooked and now I’m trying to fix it, and then I’m just like, that’s a kind of cool…
Tara Kizer: Now he’s on a cruise ship…
Brent Ozar: Walking around drunk.
Tara Kizer: This one’s on his ignore list, I’m pretty sure. Yeah, filter out. So we had it filtered out, but we added it back in so it was viewable because I had one client where they were having really bad CLR waits that they needed to address. I didn’t even notice them because it was on the ignore list. And I believe it was the Redgate free monitoring tool that they were using, which was showing that there was an issue here. And they’re like, yeah we use CLR. I was like, oh… So we added it back in because of this one specific client. So anytime I see it as a high wait – because I do see it a lot on client machines as a high wait – I’m like, are you using CLR? And almost always, the answer is no. And if that’s the case, this is all built-in SQL Server stuff, you know, so contact Microsoft if there’s a lot of waiting time. But as Paul says, it’s mostly ignorable.
Richie Rump: So CLR is another one of those, hey it works great in these narrow use cases, and then people start using it for all these other things and it’s like…
Brent Ozar: Yeah, absolutely.
Does Power BI need SSAS or Azure?
Brent Ozar: Steve says, “Here’s a stupid question. In PowerBI, do you need an Analysis Services on-premises or SQL Azure to have your users start using this? Everywhere I’ve read says it uses SSAS or Azure.” When it first got started, it was really focused on those databases, but they’ve very rapidly added compatibility with lots of other databases, for example, I use it with Postgres and Amazon RDS. So you definitely can throw all kinds of data into it. You may need an on-premises gateway server, which is just a VM that the PowerBI services will connect to your gateway server, and then go pull the databases that you want. For example, Microsoft can’t directly query Postgres. They have to go through the gateway server to go do it. But yeah, you don’t have to have analysis services or SQL Azure.
I’m a first time PASS Summit attendee. Now what?
Brent Ozar: Colin asks a great question that we’ll all have to switch around and answer differently. Colin says, “What advice would ya’ll give a first time PASS attendee?” Richie, you’re making faces. What would be the first thing that you would…
Richie Rump: Oh, go to all the parties. Don’t worry about tickets. Got to every party. I wouldn’t even worry about attending all the sessions either. I would star the things I’m really curious about or really interested in and go to those. And as you’ll see, as you go on through the conference, you’ll hear things or you’ll talk to people, oh I was really doing this, or you’ll meet speakers, oh I’m really talking about this… And then, fill your schedule up with that stuff. Also, don’t forget the hallway track. The hallway track is just you wandering around meeting people, talking with them, because they’re skipping stuff like you are and they have time and you could find some really good stuff.
I remember, I was at the Microsoft booth and I got this one on one conversation with one of the Microsoftees about parallel data warehousing. I knew zero, and when I walked out of there, I’m like, I know exactly what it’s doing. That thing is costly but kind of cool.
Brent Ozar: Tara, how about you?
Tara Kizer: I agree. Make sure you go to all the parties. I mean, PASS, you’re going to learn some stuff in the presentations that you pick, but a lot of PASS is networking and meeting people and hearing about their environments, and socializing, non-SQL Server stuff. I think PASS still has the newbie – I think it’s like Tuesday evening. The main conference starts on Wednesday. So on Tuesday evening, they have a newbie mixer type thing and generally it’s all first-timers there. So you’ll meet first-timers. And if you ever go to PASS again, you might see some of the same people because a lot of people, once they go to PASS, they want to keep going to PASS. It is a fun conference and, you know, your company can pay for it because you are learning as well.
Brent Ozar: And if your company does pay, one thing to do is go to your boss with the schedule and say, can you pick two sessions or three sessions that you want me in, that when I’ll come back, I’ll debrief the rest of the team on. So that way, it makes the manager feel like they’re getting money’s worth out of the conference. Then, go sit in those sessions, take notes, and when you come back, go kind of give like a five minute, here are the things that I learned and here’s where to go to learn more, recap. Because that way, then your boss is more likely to send you to the conference next year instead of somebody else who came back and didn’t share any knowledge.
Richie Rump: The other thing is, don’t be afraid to bail on sessions. If in the first ten minutes, you’re not getting out what you thought you were getting out of this thing, dip. Leave, go to something else. Don’t waste your time there on something that maybe it wasn’t fleshed out correctly in the description. Maybe it was a kind of bait and switch type thing with the title. I mean, there’s a lot of things that go on. Don’t be afraid just to get up and walk out. No one’s going to be offended. And if they are, then it’s Buck Woody, and you sit your butt back down and you listen to Buck.
Tara Kizer: And it’s awkward though if it’s not a highly attended session, because they’re going to see you.
Brent Ozar: And they’re wailing as you’re walking out the door…
Tara Kizer: One of my 20 people have left…
Should I change my compatibility levels?
Brent Ozar: Brian says, “I’ve noticed on our server that we have a ton of databases with different compatibility levels. I guess they retained the original compatibility level from earlier. Should I change this? Is it a good practice to just change compatibility levels when I upgrade the database, other than the whole cardinality estimator thing in 2014?”
Tara Kizer: Oh, he knows about the cardinality estimator. So that is the big risk for me. Microsoft has a whitepaper on that cardinality estimator topic, and it says do not change your CE to 2014 or greater because you need to spend the time testing performance outside of production. It says, if you do not have time to do a full test, then do not change the CE. This is a Microsoft whitepaper. It’s a fairly recent whitepaper I think, so there’s risks there. You’re saying except for the cardinality estimator thing, well, you do need a test. Depending on how old the SQL Server compatibility level, you already could have some T-SQL errors, so definitely advised to test that out.
What logins should I use for Power BI reports?
Brent Ozar: Nika says, “We have a client wanting to connect PowerBI to our production instance for reporting. Would you use a SQL login or a Windows login, and what difference does that make as far as security?” generally, I would use a Windows login. I would use either at-person’s login, so you can identify who’s running the crappy queries; assuming it’s the PowerBI desktop that they’re connecting in with. And then that way, you can shut off their logins when they leave. You’ll also hear people say, if it’s groups of people using PowerBI, you’re better off with a Windows group, like an active directory group, so then your helpdesk can control those permissions without getting you involved every time.
Is Brent wearing PJs?
Brent Ozar: Marci says, “I was wondering if Brent is wearing red pajamas because his red doesn’t show.” I do have both red and blue pajamas, but I’m not wearing my pajamas today at work. I have a huge wall of windows right over there, so it would be awkward for me to be wearing my PJs where all of downtown San Diego can see me. Not that it would stop me, but it would be…
Richie Rump: That confused me. I’m like, why?
Brent Ozar: Not that it would stop me.
When would filestream be good?
Brent Ozar: Ron asks a tricky question, “When would filestream be good?”
Tara Kizer: I’ve no idea. I’ve never looked into it.
Brent Ozar: The one thing that I’ve seen – the use case that people will say is, when you absolutely have to have transactional consistency on a group of files that must be restored to the same point in time as the database. But frick, man, when you start putting files in the database, there go your backup sizes. So just be really careful. Yeah, so many bad things there.
Can I run Ola Hallengren’s scripts from another server?
Brent Ozar: Brain says, following up on his question, he’s using Ola Hallengren’s scripts for database … “Can that be set to run from a different server?” Dude, so here’s the thing; when you call scripts across servers, you’ve got to make doggone sure that that call is running every time. Because if that call stops working and you don’t get any job failure emails, you’re going to assume everything’s okay when it’s not. So can things be called from another server? Absolutely, you can set up agents centrally on one server to call jobs across other places. You can do linked server calls. There’s lots of tricks you can do. But just make sure that whenever you do it, that it’s bulletproof reliable.
Can I export query results to Excel with PowerShell?
Brent Ozar: Cee asks, “Speaking of PowerShell, is there a quick way to export query results to an Excel worksheet?” I don’t know…
Richie Rump: Copy paste?
Tara Kizer: there is because PowerShell supports the CSV output type and Excel can automatically read CSV.
Brent Ozar: So we’ll Google real fast because I have a feeling that the dbatools have it. Export query results to Excel… there’s a script. There you go, there’s a script gallery to do it. I’m surprised that there’s not a thing in dbatools to do it.
Tara KIzer: I think if you had switched that to CSV, it might have shown up.
Brent Ozar: That’s probably true…
Richie Rump: PowerShell hammers.
Tara Kizer: Maybe … is not good on their website.
Brent Ozar: On dbatools, maybe? But there’s other scripts to do it, so that’s cool. Oh, export CSV, that’s kind of cool.
Tara Kizer: Yeah, I was going to say, I think that is part of the language.
Brent Ozar: Makes sense.
How do I configure Ola Hallengren’s scripts?
Brent Ozar: And then last but not least, J.H. says, “I downloaded Ola Hallengren’s scripts and I want to do a test run on just one database. The script is quite lengthy and a bit confusing. Where in the script do I tweak this?” It’s in the call parameters, so when the stored procedure has parameters for CHECKDB or index optimize, whichever one you want to do, and there’s a databases list and you can pass in databases by name, you can pass in exclusions, all kinds of stuff, so very easy to do there.
Richie Rump: I mean, going back to the Excel thing, it’s a lot more complex than just a CSV. I mean, if you want different queries on different tabs and if you want [crosstalk] adding and if you want – I mean, there’s a ton of stuff going on inside of a zip file, which we would call an Excel SX file.
So I don’t know why I would have any knowledge of any of that around here. Someone around here thought it was a good idea to output stuff into Excel. I’ve never heard of such…
Tara Kizer: I can’t do my job without that file, by the way. So if Brent ever fires me for whatever reason, I have to still use that.
Brent Ozar: When Richie first started, when we first brought him on, I said, okay here’s the thing that I need you to do. I don’t want you to spend more than one to three months or something on it, but it needs to run all of our diagnostic scripts and put the script results in different tabs. So it would have sp_Blitz on one tab, sp_Blitzcache on another tab. But as soon as you say export stuff to Excel, not only is it complicated in terms of dumping out the results, but there’s some things that won’t work in Excel. For example, query plans. And so Richie wrote this tool that will loop through query plans. Also, if the query plan is in the column for the output, it will go dump that into a separate file and all that.
And I know what y’all are going to ask; can we have that utility? And the answer is no. When we first did it, I was like, this is going to get us by for two years, maybe three years worth of consulting, because next, I’m going to have you build this thing called ConstantCare. It’s going to take a lot longer, but eventually, we’re going to switch over to using that. And I promise, you won’t have to support this. It will be legacy code. I won’t ask for a lot of work. And Richie knocked it out of the park.
Tara Kizer: And the reason why we use Excel is because we’re not always connected to our client’s machine. We’re connected for a few hours, and then that’s it. So this is just an easy way to have all the data collected in one place.
Brent Ozar: And before we connect to them too.
Richie Rump: Yeah, and one thing I didn’t tell you, Brent, is that I have dumped stuff into Excel programmatically for my entire career. So I mean, okay, we could do this damn thing again…
Tara Kizer: I like that Excel, you just need to have the file with the format you want and Excel just uses that format. So you have a template format.
Richie Rump: Yeah, it will just dump it to the cell if there’s a format there, but we could programmatically overwrite that format if people so desire, which we don’t because it’s just easier to format the page.
Brent Ozar: Alright, well thanks everybody for hanging out with us this week and we will see y’all next week at Office Hours; adios.
