This week, Brent, Tara, and Richie discuss storing phone numbers in a database, forced parameterization, how AI will affect DBA jobs in the future, what languages a DBA needs to learn, backup issues, measuring the overhead of transparent data encryption in terms of CPU on SQL Server, starting SQL server without tempdb, and much more!

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 Webcast – 2018-11-28

Brent Ozar: Jason asks, “Do you have any suggestions on storing phone numbers? I was thinking bigint versus varchar, say, 15. Do I need to deal with international supports; smallint, ex, field for optional extension s, any thoughts?

Tara Kizer: This is a Richie question, for sure.

Richie Rump: Yeah, use character fields, because at some point you’re going to have to do international and there’s going to be a whole bunch of other stuff…

Tara Kizer: Really?

Richie Rump: Yeah, character fields. And then, what you want to do is format that, not in the database but on the way in through the app, in the app, whatever format so it’s consistent at the very least. Otherwise, you don’t want to have all these numbers and just not have any format because then you’ll have all these different formats all over the place, then your app doing all this work to reformat it. But scrub it on the way in. Have it a consistent format. Store it in a varchar because you won’t be doing any math on it, so why an int?

Brent Ozar: Leading zeros are a problem. And then the other thing you end up having to worry about is commas, because in some auto-dialer software, you need commas to wait for a specific period of time. Terrible things I’ve learned, terrible.

Tara Kizer: Not something I’ve even thought about.

Brent Ozar: No, I was going to make phone number jokes when he first asked him. Like, what is this? You’re putting your black book inside a database; what’s going on there?

Richie Rump: I mean, you don’t, Brent?

Brent Ozar: No. I’m married. I don’t even know my own phone number. I’m just like, honey…

Brent Ozar Shaun asks, “Is there a way to send unique metadata with queries that doesn’t bloat the plan cache? I’d like to send a value of where in my code a query is being called from. I have forced parameterization turned on, but most queries are already parameterized. It would be nice if there was a flag to turn on or off comment stripping before creating plans.”

Tara Kizer: I don’t even understand the question; unique metadata with queries – oh, the documentation in it? I mean, it comes across. It’s in there.

Brent Ozar: I’m going to whip out SQL Server Management Studio, because I don’t think it works exactly the way you think it does, forced parameterization. And now, when you run plan cache queries like sp_BlitzCache, that’s going to only show the parameterized string in there. So we’re going to pop into Stack Overflow 2013. I’m going to set forced parameterization at the database level. So parameterization, let’s go into forced and say okay. Let’s set a new query. And I’m going to put in a comment. I’m going to say, hi mom, and then select star from dbo.users where display name equals Richie Rump or display name equals Tara Kizer – neither of which are their real names on Stack Overflow…

Tara Kizer: No, I’m Tara Kizer on Stack Overflow.

Richie Rump: I’m not…

Brent Ozar: Are you really? You guys might be thinking DBA.StackExchange…

Tara Kizer: Oh, well no, I should be…

Brent Ozar: Alright, so here we’ll do Richie’s…

Tara Kizer: I had to ask, I think, a PowerShell question a few years ago, so I’m on the other side.

Brent Ozar: So now here’s that. If I go query include actual execution plan and I go run it, in the execution plan, it doesn’t look like it’s there. And even if you go into the add a query text, which is freaking broken in SSMS – this drives me crazy. But if I go into sp_BlitzCache and I go look at the plan cache, hopefully it’s going to be up in that list. So here we go. So here it looks like your comment isn’t included. But if I go click on the query plan, it’s going to be inside here if I edit the query text. As you can see here, the comment is completely gone. What the hell? It should have preserved the comment inside there. I would have been sure it would have preserved the comment inside there.

Tara Kizer: I certainly see comments when I open up people’s plans.

Brent Ozar: yeah, even with forced parameterization.

Tara Kizer: Maybe it’s only stored procedures?

Brent Ozar: Oh, well stored procedures will definitely do it. I’m going to gamble and move it. I’m going to try moving it in over here.

Tara Kizer: And I just looked up my name over there. It’s just Tara. I’m surprised. I’ll have to change it.

Brent Ozar: let’s free the plan cache and then let’s try it again, just to see, execute, and then run sp_BlitzCache and see. If it doesn’t show up here either, I’m going be pissed. It doesn’t show up here either. Oh, there is – we’ve already blogged about this even. How does this even happen? Shaun, if we’ve blogged about it, why are you making us look stupid on the live webcast?

Richie Rump: Because it’s so easy, Brent.

Brent Ozar: Copy, paste – I didn’t know … was a movie. Oh, Kendra blogged about it. Oh, okay, so alright. No, in that case, I would read whatever Kendra wrote and then pay attention there. Sadly, we don’t…

Tara Kizer: Consider it gold if Kendra wrote it.

Brent Ozar: It’s true. She’s way smarter than we are.

Brent Ozar: Let’s see, next up August says, “Hi Brent, I notice that you have included Ola’s scripts in your DBA first aid bundle. I was wondering how I’d manage version control between checking your updates and Ola’s? By the way, your great post, Brent, is amazing.” Someone’s trolling us. So we don’t include Ola’s scripts. We don’t include Ola’s script at all. [crosstalk]

Tara Kizer: I was just about to go check, like really?

Brent Ozar: No, no, no, no. We don’t want to manage that kind of version control either. Tara wrote sp_DatabaseRestore, which relies on Ola’s scripts, but we don’t ship them with our First Responder Kit.

Brent Ozar: Jason, throwing back to his question about how to store phone numbers says he was, “Thinking about using bigint because it would be smaller in terms of size.” Yeah, but your problems will be larger.

Richie Rump: Yep.

Brent Ozar: Let’s see, Mark asks, “How do you think artificial intelligence will affect DBA jobs in the future? Also, will DBAs need to learn more languages than just SQL? I know you dislike PowerShell.” We’ll go each through this. So how artificial intelligence will affect DBA jobs in the future – Tara, you first.

Tara Kizer: I don’t think it’s going to affect us. I mean, maybe if there’s someone starting out new, if they’re in their 20s and becoming a DBA, you know, we’re going to retire in the next 20 years or so. I really don’t think it’s going to affect us completely, but certainly, a lot of people will have moved to the cloud and you do have some changes happening for you. But it still takes a human being to be able to look at things. I mean, even BlitzCache, which has a lot of logic built in to help you find things, you still have to open it up and dig into it. And I don’t know that AI, at least for the stuff that we work on, would ever be smart enough to really do what a really good DBA can do.

Brent Ozar: For a while there, there were people who were trying to build something that would automated test your T-SQL to make sure it was the same after changes. Their goal was to have tools like artificial intelligence tune a query the way a DBA would and make sure that it returns the same results afterwards, and those projects keep stumbling all over the place too. Richie, how about you?

Richie Rump: I think we’ve been asking this question for, what, the last 40 years or so. And every time it comes up, people are going to say, we’re going to lose our jobs. But technology keeps improving and yet we still have our jobs. Now, our jobs have changed a bit. We’re not managing so much at the detail level; we’re a little bit higher up. But it’s going to be the same thing. We’re going to manage maybe something at the higher up or maybe we’re going to transition to something different, but we’re not going to be losing our jobs. It’s just going to be something else, something more important that’s going to come on and we’re just going to jump on it. I mean, even when we are working with Aurora, which is pretty much hands off – even to spin it up and to get it going, it’s just click, click, click, boom and all of a sudden we have a cluster in the cloud – there’s still stuff in it that we have to know and we have to get into because we kept crashing it. There you go.

Brent Ozar: I think it’s going to keep fixing smaller problems, you know, that we’re always fixing harder problems these days. Man, I would just love for database servers to back themselves up. It feels like is that so much to ask?

Richie Rump: You look good, won’t you back that thing up?

Brent Ozar: Why can’t, when the SQL Server gets installed, why can’t there be a wizard that says, where would you like your backups to go and how long would you like to keep them?  That seems like genuine intelligence to me. It’s not really that hard, but it’s a question of resources.

Richie Rump: Actually, the cloud does that.

Brent Ozar: The cloud does it?

Tara Kizer: Yeah.

Brent Ozar: Yeah, but it’s not artificial intelligence, it’s just a freaking wizard.

Richie Rump: That’s right.

Brent Ozar: Before it steps through, it’s really not that hard. But people are sticking with a whole lot of on-premises databases and this stuff just isn’t getting any easier. Even when you go to the cloud, there’s a lot of this, okay now I need to do a restore, I need developers to start provisioning out restores, I need to manage the security of data, I need to manage what parts of data get masks, what parts do not. There’s things I get excited about for AI to cover, but it’s just not covering everything. It’s not even close.

Richie Rump: Right now, I don’t think that any company’s going to say, let’s go ahead and put all this effort into all these little AI projects and it’s not going to increase our bottom line whatsoever.

Brent Ozar: And really, if you’re building all of that, it’s not cutting your costs today. AI is still way too expensive. Try and go find someone off the street who’s willing to work for nothing as an AI researcher. It’s not going to happen. You had another question in there too like, what languages will a DBA have to learn? If I was a production DBA today – if you made me go back and get a production DBA fulltime job where I was managing multiple servers, I’d learn PowerShell in a heartbeat; not because I want to but because I think I would have to. In our weirdo jobs as consultants, we’re really brought in to kind of resuscitate one server at a time, so we don’t end up using it. But I do believe that it’s a really cool interesting language for those of you who have to manage armies of servers. I just don’t want that work myself.

Richie Rump: That’s for the DBA side. But if you’re on the database development side, it should be Python. Python is the language you should know, not PowerShell, because I don’t even think PowerShell is a language. It’s Python. Learn Python if you are a developer.

Tara Kizer: I’ve actually written a few PowerShell modules over the years and I just learn it – I relearn it every single time I have to write a new one, and I just Google the hell out of it until it starts working.

Brent Ozar: That’s what we also call artificial intelligence.

Tara Kizer: Although I have taken a PowerShell training class a few years ago.

Brent Ozar: Yeah, it’s hard.

Richie Rump: We have PowerShell training classes coming up, don’t we, Brent?

Brent Ozar: We do, and here it is, it’s on the slide. Drew Furgiuele with Hands-on Labs. And it’s specifically for DBAs too. I’ve gone through it too as well. And it teaches you how to accomplish specific tasks that DBAs need to accomplish, which is what I think is much more important in learning. I don’t want to learn internals that I’m never going to map to my day to day job. I want to learn something that’s going to help me do better tomorrow. That’s what Drew’s stuff is focused on.

Tara Kizer: I just saw the slide. It says it uses Hands-on Labs. Is that the virtual machines from AWS also?

Brent Ozar: Yeah, exact same thing.

Tara Kizer: Awesome. I didn’t realize we had any guest speakers that were doing the Hands-on Labs also.

Brent Ozar: I try to encourage everybody to do it. Like, any class that has labs, the students seem to retain better when it’s a practical topic. It’s less about when it’s a theoretical topic.

Brent Ozar: Gordon asks, “A problem with my SSMS initiated backup is it’s timing out after less than a minute. It had to do with the execution timeout being set to 45 seconds. Why did the backup timeout since the backup had already started?”

Tara Kizer: Why is anybody changing the Management Studio timeout setting? I mean, that’s one of the benefits of being able to run queries in Management Studio, that it by default does not timeout. The answer to his question is basically it’s a kill command. You’ve cancelled the query when the timeout happens.

Brent Ozar: That would be bad.

Tara Kizer: It just seems odd to have changed that setting.

Brent Ozar: I’m wondering if maybe somebody didn’t do it to say, oh if I have a query that runs away, I want it to automatically reel back in. But man, that’s dangerous. That gives me the heebie-jeebies. I’m also a big fan of not doing backups via SSMS. I would much rather kick them off via agent jobs, even if it’s a one-time thing, just so that I can make sure that agent will keep running the thing and it will succeed to completion. That’s probably not a real phrase.

Augusto asks, “How can we measure the overhead of transparent data encryption in terms of CPU on the SQL Server?”

Tara Kizer: What kind of load testing do you have? This needs to be done in an environment before you hit production; apply a production load to a test server and see what happens. Hopefully it’s production-like hardware. Check what your system needs.

Brent Ozar: And when you don’t know how to do a load test, because a lot of us don’t, just start with index rebuilds, backups, and CHECKDB; things that you would usually do in a maintenance plan. And you can look at the differences between those while they run.

Tara Kizer: Yeah, good luck on those backups. You get no compression with TDE.

Brent Ozar: So there was a switch. So I can’t remember what Service Pack they came out with, and I want to say it was 2016, where it all of a sudden gave you compression on backups. And after that, there were like six Cumulative Updates in a row that fixed corrupt backup issues. It was so bad. Whatever you do, don’t enable this without CU whatever…

Richie Rump: Oh my gosh…

Brent Ozar: Yeah, it was bad. Plus the worst of it was that your backups succeeded but you just couldn’t restore from them.

Tara Kizer: Oh wow, and how many people are doing those test restores? I mean, I always did, but I don’t think very many people in the world are testing their backups.

Brent Ozar: Not nearly enough. Not enough people in the world take their backups.

Tara Kizer: True. We know that.

Brent Ozar: Teschal asks, “Is it possible to start SQL Server without tempdb or provide a different drive on the fly?”

Tara Kizer: What world of hurt is Teschal in right now? Certainly, it can from the command line – SQL Server .exe and there’s a switch that you can use to have minimal stuff startup. But I think tempdb can be excluded. Okay …

Brent Ozar: Of course he does.

Richie Rump: Of course he does.

Brent Ozar: There you go – /F, minimal configuration, that’s what it is. And then you can alter the tempdb location from there. So what Teschal probably did was alter and set the wrong location for a path that doesn’t exist. Teschal, I’ve been there too.

Richie Rump: I love in the cloud; I haven’t been there.

Brent Ozar: J.H. says, “I’m trying to track logins to a particular database. I’m trying to figure out if that database is still being used. Is a server-side SQL trace pretty much the T-SQL example of the profiler GUI that just runs on the SQL Server?”

Tara Kizer: I’m not quite sure what J.H. is asking because with the server-side SQL trace, you have to select which events you want to do. It’s very tedious work, so I usually use profiler first. It gives me the template after [suck all my vents], and then I script it out and then I’m good to go with the server-side trace. But you have to add your events. Server-side trace doesn’t give you anything; you have to add things to it.

Brent Ozar: Yeah, say that you want to replay exactly what happened. And I’m going to do a terrible trace here just to show as an example. And then yeah, sure, whatever, untitled one is fine. And run, and then immediately stop. If I go file templates – no it’s not templates. Save as trace template, is that what it is? Yeah, boy, it’s been a long time since I’ve done this…

Tara Kizer: No, no, no, it’s under file. It’s definitely under file. Save as – there it goes, yep.

Brent Ozar: Is that it?

Tara Kizer: Yep.

Brent Ozar: Okay, now let’s see if we can open it. Now, who the hell knows where this went?

Tara Kizer: Yeah, I was going to say… There it is.

Brent Ozar: there we go. So yeah, then you can save that as a server-side trace. But is it the same overhead, if that’s what you’re asking? Its overhead is not as bad as you’re running it on your local machine in Tucson, Arizona when your production server’s in Buffalo, New York, you know, trying to go across data centers with huge latency. It’s a lower latency. If you’ve got to do a trace, this is a better way to do it. The other thing I would do is, in a login trigger, I don’t know if you can – well, you know what, it wouldn’t matter. And you know, J.H. I don’t think yours is going to matter either because the problem is, are you really going to be sure that, say, the Stack Overflow 2010 database isn’t used. If someone could go into, say, tempdb and they can run a query like select star from Stack Overflow 2010 dbo.users, even though I’m not logged into that database, I can still run that query. So I don’t know that a trace is really going to get you there.

Tara Kizer: I would probably just disable logins, rather than taking the database offline, just disabling it and then I would imagine that the applications have some kind of alerting in place to notify you’ve got some kind of problem.

Brent Ozar: I love it. That’s even better than – I was going to say – set it to read-only, but that’s bad. I like your idea better.

Brent Ozar: Sheila says, “We have a mass run…” And as soon as she says that, all I can think of is the video Chicken Run, where these chickens are running from the…

Richie Rump: I was thinking like a marathon and we have all these little jobs just in corrals and everybody’s just trying to get through that one start just to get going.

Brent Ozar: That’s because Richie works with our serverless applications, where we have, all of a sudden, thousands of jobs that are trying to – or Amazon Server, Postgres…

Richie Rump: Run at the same time, yeah.

Brent Ozar: “We have a mass run with high executions every night at the same time. We have no consistency with the number of claims per second that get processed during that time. CPU and memory are all consistent from one night to the next and I’m gathering wait stats every 10 minutes now via the first aid kit. What should I look for?” So I would – god, there’s so many interesting things here. I would probably start with when you say our first aid kit, it’s one thing to gather the stats. The other thing you want to do is use the PowerBI dashboard to go check and see the wait stats over time. Richie’s giving a face. Are you giving that face because you can’t believe that I said the words PowerBI, or what are you saying?

Richie Rump: I still can’t believe you actually use PowerBI.

Brent Ozar: I know, right, and enough that I use it in my browser now. I have an Azure account to use it.

Richie Rump: It’s insane – so easy, Brent Ozar can use it.

Brent Ozar: And enjoys it, yeah. It still has more bugs than a mass run of whatever. It’s buggy, and it’s missing tons of features, but I still like it a lot. So this is what I would start with; graphing things out with the First Responder Kit’s PowerBI dashboard, and then you can see what wait stats look different during that time and tackle whatever your top wait type is. Maybe it’s blocking, for example, and that’s why you can’t get more throughput. That’s a common one. THREADPOOL waits, there could be all kinds of things that would get you. Alright, well that’s all the questions that we have this week at Office Hours. Thanks, everybody for hanging out with us and we will see y’all next week at Office Hours.