This week Brent, Erik, and Richie discuss whether you need to know T-SQL to be a Senior DBA, if you should use SQL Server 2017 for production, monitoring tools, C# vs Powershell, IO_COMPLETION waits, DBA jobs, and SQL Operations Studio.
Please accept YouTube cookies to play this video. By accepting you will be accessing content from YouTube, a service provided by an external third party.
If you accept this notice, your choice will be saved and the page will refresh.
If you prefer to listen to the audio:
Enjoy the Podcast?
Office Hours – 11/29/17
How good at T-SQL does a senior DBA need to be?
Brent Ozar: Let’s see here, we’ll go ahead and get started with the questions. Thomas asks, “I can answer most questions about being a mid-level DBA…” Great, we’ll put you on the webcast next week. We’re going to outsource [crosstalk]. Richie’s going to go play Switch; I’m going to go surf the web. He says, “I can even answer some for senior positions. How good do I need to be at T-SQL in order to call myself a senior DBA?” That’s a great question.
Erik Darling: Does T-SQL alone make you a senior DBA? I don’t think so.
Richie Rump: No, I’ve seen senior DBAs that suck at SQL. And one of these two guys is like that.
Brent Ozar: Me… I mean, I can write a query, but I’ll sit in on every Itzik Ben-Gan session that he ever does, and probably two out of the three things that come out of his mouth are like I’m learning again. He’s probably said them before to me, but I’m like, “Oh that’s right, that’s how the sliding window function thing works.” But the same thing with our mastering index tuning class right now that’s going on, a good chunk of the DBAs don’t know how to write T-SQL. They’re trying to figure out how to make queries go faster with indexes, but you do not need to be good at T-SQL in order to be a “senior DBA”.
Erik Darling: I’ll give you another great example: Robert Davis. I have never seen him write a query in my life, but if I had to hire an infrastructure guy, I would be banging on his door so hard…
Richie Rump: Yeah, if you need to know T-SQL intimately, you’re not a DBA, you’re a database developer. So that’s the difference. Now, if you can take a look at a query in a query plan and then tune that, I think a DBA should be able to do that, but not necessarily, “Hey I need to take a look at my table structures and see…” You know, and figure out how I can get this intricate query and then pump it out. A pure DBA doesn’t do that. If you are a DBA that does some database development, that’s one thing, but a senior DBA shouldn’t have to understand that. It’s great that you do, but you don’t have to.
Brent Ozar: Michael says, “Robert is good at T-SQL.” I’m sure he doesn’t suck. No, and I know I’ve seen a function that he writes in order to parse fn_dump_dblog, but it’s just that if you were going to write a big huge reporting system, Robert wouldn’t be the guy you would call. Itzik Ben-Gan would be the guy that you would call for that.
Erik Darling: If I needed the fastest way to traverse hierarchies or solve weird problems, Itzik all the way. If I just want the most reliable 24/7 system in the world, I’m paying Robert Davis a million dollars a year, or whatever else he needs, to keep him sitting at that desk for that long and on cocaine to stay awake.
Brent Ozar: Steak, I know he likes steak.
Erik Darling: There you go.
Brent Ozar: Thomas follows up and says, “Wow that makes me feel better about my future. I’ve been focusing on HA and DR.” Yeah, that role is called production DBA. Production DBA is where you make sure that the server is on, it’s accepting queries, that there’s no corruption, that it’s backed up, restorable et cetera.
Erik Darling: You’re doing the patching, the DR plans, you’re doing all that stuff that’s fun for some people.
Richie Rump: Not to others; I’m telling you now.
Erik Darling: Not to some people.
Is SQL Server 2017 ready for production?
Brent Ozar: Tom says, “I read that you no longer have to wait until Service Pack 1 comes out with Microsoft’s new rapid release model…” Well, that’s true, there’s no more Service Pack 1 at all, there’s only cumulative updates. He says, “Would you recommend SQL Server 2017 for production at this point?”
Erik Darling: Hell yeah. Someone’s got to find those bugs.
Richie Rump: And it might as well be you.
Erik Darling: Right, they just released cumulative update 2 last night and there were like no showstoppers in there. There was like SQL Server 2017 ceases to work if you run a query… So I would just say go for it, man. Make Joe Sack happy.
Brent Ozar: I was really pleasantly surprised. Whenever a new cumulative update comes out, we have a laughing joke-fest in the company chat room, you know, laughing at all the bugs. And I was reading down that list on CU2 and I’m like, “Well that’s reasonable, that’s reasonable.” There’s some stuff with memory-optimized tables, but I’m like, “Ah who uses that anyway?”
Erik Darling: I’m like, “Yeah, I could see how someone would miss that, that’s fine.” There wasn’t anything that outlandish like, you know, what was one recently? Like updating a column store index causes corruption, I’m like, “Well no…” I mean, you shouldn’t be updating a column store index anyway, that’s the wrong way to do it, but holy cow, no.
Brent Ozar: In the old days, they would put incorrect results in the title of the hotfix. They would say incorrect results when you do whatever – these days they try to hide the words incorrect results and they put them somewhere else. They’re just like, “results issue”… [crosstalk]
Erik Darling: Query may surprise you.
Thanks for the Black Friday sale
Brent Ozar: Results not as expected. Thomas says, “Thank you very much for the sale on the Everything Bundle.” You’re welcome; our pleasure. We have a lot of fun with Black Friday and Cyber Monday.
Erik Darling: Brent asked me to put a price on my videos and I said basically free sounds about it.
Brent Ozar: $1.99. I said well we’ve got to make something so let’s make it $3.99.
Richie Rump: I’m not paying that much for an app, are you kidding me?
Brent Ozar: Oh wow, it’s amazing what the difference is in what people expect to pay for things.
Erik Darling: You know what else is really funny is the psychology – if you make an app cost more, people are more likely to buy it because they think they’re getting more. If you’re like, “99 cents, for what, I don’t need that…”
Brent Ozar: It’s got to be garbage.
Erik Darling: yeah, but for like ten bucks you’re like, “I’m going to get the world from this. It’s going to solve all my problems.”
Richie Rump: Look at video games, right. So you buy something for the Xbox and it’s like 70 bucks, and then you say, “Hey I’ve got a game. It may not be as full feature but you know what, you’re going to enjoy it.” And they’re like, “99 cents, are you kidding me, forget that.”
Erik Darling: One of my old roommates used to peruse the online stores for that and he would find these like magical games for like six to 12 bucks that he would play for like 100 hours. He’d be like, “Why don’t they charge more for this? This is the best game.” And I’m like, you know – you come along to something like Skyrim and you’d play it to its logical conclusion, but it would cost you like 60, 70 bucks. You’d played probably about what the game studio expected, whereas you play them for a little bit and you’re like, “Wow this is great.”
Brent Ozar: I’m still going on with Hitman. That’s still the only game that I play. Hitman and, what’s the disease one?
Richie Rump: Pandemic?
Brent Ozar: Pandemic, yeah.
Richie Rump: Yeah, Pandemic is a board game to me. It doesn’t like click as a video game, you know.
Brent Ozar: Oh, it’s so good as a video game because you don’t have to worry about saying things, setting things out or whatever, you can play it yourself on the plane. It’s magical.
Richie Rump: Yeah, but that’s half the fun, Brent.
Brent Ozar: I don’t like people, so…
Richie rump: Well, we know that, Brent.
Erik Darling: Also, board games plus turbulence – no.
Brent Ozar: And booze on the table. I mean, I spill my booze on the game.
How can I monitor what’s using TempDB?
Brent Ozar: Scott says, “Yesterday our tempdb transaction log filled…” This sounds like the start of a wonderful novel. “Filled to the size of our drive – we didn’t have any logging in place to monitor the size of tempdb and I’m trying to find out what happened. We don’t know if it happened all at once or if it was progressively getting larger; any ideas?” I have an idea – buy a monitoring app. Go ahead, Erik, you looked like you were going to say something.
Erik Darling: Oh, I mean, I have a stupid blog post about monitoring the tempdb with extended events, if you wanted to head over and pick that up.
Brent Ozar: Let’s see here, let’s open up a web browser – who knows what we’re going to find… Tracking tempdb growth using extended events; that works. I’m such a huge fan of getting a monitoring package off the shelf. It’s like a thousand bucks. Let me tell you what happens if you don’t – if you go and build your own monitoring tool right now and you go, “Oh I’m going to write something so that I never get caught by surprise by tempdb filling up again.” All you’re doing is monitoring for that one thing. Two weeks from now your server is going to break for another reason. You’re going to be like, “Oh no, I never – who could have possibly guessed that we had corruption in production? I’ll go add something to the monitoring tool for that.” So you’re always behind the eight ball. Just start making suggestions to your boss. Say, “Hey, if this is important, let’s go get a monitoring tool.” But in the meantime, yeah, I’m such a huge fan of this.
Erik Darling: The thing with extended events is like everyone wants to pawn it off as like a replacement for a profiler or for monitoring of another kind, but there’s a billion and a half events and you really have to pick and choose which ones you want to monitor and which ones – like how you’re going to get that data from it and how you’re going to alert on that. Right now there’s not an easy way to get alerts from extended events. Like if you set up an extended events session and you wait for some threshold to be passed, there’s not a great way for you to like get an email when something happens. You have to like look at it later. So it’s a pretty good way to like persist a forensic trail and stuff that you want to look at, but really just a monitoring tool is so much handier and for the price it’s so much less work.
Should I learn PowerShell or C#?
Brent Ozar: Wes says, “As a developer DBA, is it worthwhile to learn PowerShell or c#?”
Erik Darling: I’d say c#. [crosstalk] So for me, if you said, “Erik, go learn a language that’s going to help you with SQL Server.” I would choose c# because learning c++ is no good because I can’t get to the source code. So c# is okay because you could at least make yourself useful writing some like cool aggregate functions in CLR and applying those; stuff that SQL Server can’t do, using RegEx in a more useful way than PATINDEX or CHARINDEX. So I would say c# for that. PowerShell, I think that’s, to me, more the Robert Davis style infrastructure production DBA gig, because that’s where you’re using PowerShell to do something across multiple servers and work all that stuff out with the automation.
Richie Rump: Okay, can database developers speak now?
Brent Ozar: Yeah.
Richie Rump: Okay, I think it’s c#. And there are other languages like Python and possibly even now R that could be thrown into that mix now, but for SQL Server, you’re probably talking c#. And the reason why you want to do something that’s more programmatic is because you’re going to get to a point, as a database developer, where you’re going to have to process data that SQL Server really can’t do very easily. You’re going to want to put that out there and process it inside of some sort of application and then put it back into the database. C# is great for that. It’s a very rich, deep, well-documented language. You have a lot of add-ins and code that’s already out there. You could grab it from [New Git], bring it in there. The unit testing is phenomenal – try to do that with T-SQL, suckers.
Brent Ozar: Or PowerShell.
Richie Rump: Exactly. So I’ve actually done this with a client of mine before I came here to Brent Ozar. I was processing something like close to a billion records, or something like that. I did all that in c#. I wrote unit tests for it, and there was all this business logic that they needed to get through. So when I left, I just handed it off to someone and said, “Hey, if you need to change something, make sure the unit tests run,” and there was no problem with the handoff. I never got a phone call about, “Hey this app doesn’t work anymore.”
Erik Darling: Because you changed your phone number.
Richie rump: That too – that kind of helps. But the other reason you want to know c# first is that you’ll start to know the .NET framework. And if you know the .NET framework, PowerShell becomes so much easier because you start seeing that, “Oh it was built on the .NET framework. I understand what this does.” And I would definitely go the c# route, but some of the other languages like Python, they should get a nod too.
Brent Ozar: I think of it as a fork in the road. Do you want to get closer to the developers or do you want to get closer to the sysadmins? If you want to get closer to sysadmins, someone could make an argument that PowerShell is better. But even there I’m going to say c# is better because it’s going to make you so much stronger and more valuable to go build apps and things that will help you with your system administration. I get it, the PowerShell crowd is totally strong, and it’s not a crappy language. It’s a great language to go learn, but you either want to get closer to the sysadmins or the developers. And if you want to get closer to the developers, I would strongly say c#.
Erik Darling: Just for me, the value of c# would be like if I had to handoff like something that does a thing to servers to people, and I could say, “Run this EXE and this stuff will happen and you can see it here.” Rather than, “Right click on this PowerShell script and over here and if you get errors then you have to do this…” Like, if you could build something graphic over someone clicks a button, it’s a whole heck of a lot easier than like, “Go to this network drive and run this script and…”
Brent Ozar: Especially as that you said you’re going to be a dev DBA. If you’re going to be a dev DBA, you’re going to be surrounded by other developers. We should say, because the PowerShell people are going to read this transcript and they’re going to be foaming at the mouth because they’re really strongly opinionated people. It’s not a bad language to learn. It won’t make your career less valuable. We’re just talking about the specific focus of someone who said they want to be a development DBA.
Richie Rump: Yeah, and you know, for me, I like to stay right in the middle of all that mess; so right between DBA and development. Unfortunately, I had to learn both that crap… Right in the middle.
Brent Ozar: Right in the middle – it’s like a Larry, Moe and Curly kind of thing…
What wait stats should I expect on a VM?
Brent Ozar; Forest asks, “What wait stats and other metrics would I expect to see on a VM with noisy neighbors?” All of them, basically. It just means that you have less capabilities for either CPU memory, network storage, whatever; it could be absolutely anything.
Erik Darling: What I would pay close attention to there is if you run a fantastic free tool like sp_BlitzFirst, that will go in and grab your wait stats for you. I wouldn’t want to look at long average waits on stuff. I wouldn’t necessarily look at a lot of single waits. Like the total number of hours and the total number of waits might be sort of indicative. But having long waits on stuff, particularly around CPUs and memory, would be the stuff that I would focus on. Because if you have long average waits on CXPACKET, that means something is draining your CPUs. And if you have long average waits on SOS_SCHEDULER_YIELD, then your queries are waiting a long time to get back on a CPU. So it would just kind of indicate to me that SQL is having a tough time getting those resources and handing them off to queries.
Brent Ozar: Somebody is going to say what does long mean. So if I said what’s a long CXPACKET or SOS_SCHEDULER_YIELD what would you say? I’m going to make numbers up…
Erik Darling: On average, I would probably say anything over ten seconds. To me, that’s because four milliseconds is the quantum for SOS_SCHEDULER_YIELD…
Brent Ozar: Ten milliseconds, you mean, not ten seconds.
Erik Darling: Ten milliseconds, sorry.
Brent Ozar: Woo, like holy hell…
Erik Darling: I have seen ten seconds, [crosstalk]. So for SOS_SCHEDULER_YIELD, queries step on and step off, it’s cooperative. It happens in four-millisecond doses that a query gets its CPU time. So if it’s stepping off for twice as long as it takes for it to get CPU time, then I’m concerned.
I love your training videos
Brent Ozar: Let’s see here, Brandon says, “I have to say that I love your training videos…” You don’t have to say that, it’s appreciated. [crosstalk]…
Erik Darling: I can handle the truth.
Brent Ozar: He says, “They’re reliable, accurate…” Now I know he’s not talking about ours. “And they have…”
Erik Darling: Did you watch them? Did you just buy them cheap? You don’t have to…
Brent Ozar: And you should have to know, we’ve talked about this in the company chat room. We have collectors. We have people who just buy our videos and then they don’t actually watch them. So good job on watching them, that’s the key to actually learning; just having them on the shelf, that doesn’t help so much.
What could cause Page Life Expectancy to drop?
Brent Ozar: Greg says, “I’m seeing I/O completion waits. My disk average read and write times and queue lengths are all within reasonable baselines. I did see page life expectancy drops. If I’m seeing I/O completion waits, what should I be looking at now?” I/O completion, usually I would go towards tempdb. So it’s like queries that are spilling to disk because they can’t get enough query workspace – sp_BlitzCache has a sort by memory grant. Run sp_BlitzCache with sort order equals memory grant. Look for queries that are getting – this is going to sound counter-intuitive – a large memory grant, because I’m wondering if they’re still not spilling to disk, even after they get a large memory grant.
We don’t have a sort for low memory grants that are spilling to disk. I don’t think that there’s an easy way to do that. I don’t know how I would catch that without a monitoring tool, come to think of it.
Erik Darling: Stuff spilling? There are extended events for that.
Brent Ozar: Yes, that’s a good point. [crosstalk]…
Erik Darling: Sure, I concur.
Can sp_Blitz help with SAP One?
Brent Ozar: Thomas asks, “Would sp_Blitz and sp_BlitzIndex help for identifying performance improvements for SAP One?” You guys, were you on the one that was doing SAP Business One?
Erik Darling: That was Tara, but I’ve had SAP Business One clients before. Far and away the most common problem with SAP One is blocking and turning on read committed snapshot isolation is usually like a big enough improvement where you don’t have to go messing around with too much other stuff. Most SAP products are engineered to work on Oracle and DB2, which use optimistic locking by default, so turning RCSI on for those is pretty much what solves a SQL Server problem.
Brent Ozar: Daryl says, “I was on Erik’s stats page yesterday and it was awesome to see that you guys had totally missed example D, Nulls for Ola’s Stats.” He’s talking about Erik’s recent blog post about why to reorganize or rebuild indexes, I think, I’m not 100% sure. He says, “There was a GroupBy by Brent on index maintenance…” Yeah, if you go to GroupBy.org and click watch past sessions at the top, you can watch all the past videos there.
He says, “There was a great comment that said Brent Ozar and Paul Randall need to get together and explain why we disagree.” We actually agree. I mean, at the end of the day, the thing is that Paul and I both agree that it could be a problem, it’s just that I want you t priorities and make sure you’re focusing on your biggest problem first. That’s why I go through and talk about wait stats in there. Paul and I are good friends, we talk all the time. He would agree that you need to focus on your top wait type first. He’s educating you on a problem and how you go about fixing it, it’s just that I want to make sure you are focused on the top problem first. Most of the time, when I end up, let’s be honest, looking at you all’s indexes, you don’t have any indexes. And when we talk about fragmentation, I’m like, “Dude, fragmentation is the least of your problems. Meet Mister Index…”
Brent Ozar: Joe says, “To me, PowerShell is in a [tweener].” I’m not exactly sure what he means there.
Erik Darling: Sounds dirty – sounds like something you’d pay extra for in Vegas, I don’t know.
Brent Ozar: He says, “It’s too cumbersome to replace the old NT shell and it’s not got sufficient tooling to replace c#.”
Richie Rump: It’s never going to replace c#, don’t kid yourself. It’s not designed to replace c#.
Brent Ozar: even if it had great tooling…
Erik Darling: To me, it’s an administrative plugin, you know. If you need to automate stuff for a failover cluster, bingo bango, because it has all that stuff built in, and it’s cool that it can do that. But really, replace c#?
Richie Rump: It’s not supposed to do that. It’s a shell scripting language. At this point, do I need to start calling PowerShell folks …? Because essentially, that’s what they’re turning into…
Brent Ozar: Now, we have to be fair though. Before you get too excited and drop the microphone – what language do you spend most of your time programming in these days?
Brent Ozar: Which is not really designed to do what we’re doing with it.
Richie Rump: No, there is nowhere where it says that. there are some things where it says situation nominal, all filed up. I did that in there today. I’m like, “Oh I did put that in there, alright, very good.”
Richie Rump: I don’t think that the language really matters. It’s understanding the fundamentals of development and programming that really makes a good developer what it is. The language is just a tool. The thought process and understanding the basics is really what’s important. So whatever language you use is whatever. I mean, I’ve seen some really great Cobalt programmers that they’re not using object orient at all; it’s just all top-down stuff. So it’s the thought process. It’s understanding how to get in there and what makes things work from a programmatic perspective and understanding the basics is really the key.
There was a book that used to read like every year called the Object-Oriented Thought Process, and it’s just so I can hammer the basics into my head every year. And okay, this is how you build object-oriented stuff. Nowadays in modern programming, we don’t really do object-oriented so much anymore. It’s more functional programming than it is object-oriented stuff. We don’t really tie ourselves to those object-oriented paradigms. And that’s a lot, because of our toolings, we can’t get to things so we can test them and things like that. So that’s a lot deeper than I probably should have gone into database Office Hours stuff.
Brent Ozar: It’s a good question.
Erik Darling: The big takeaway there is learn Visual Basic.
Richie Rump: Visual basic worked in a lot of different ways and for a lot of years. And you know a byproduct of learning Visual Basic is that you learn a .NET framework. And you can go from .NET framework to f# to c# and a whole bunch of other languages that use the .NET framework. So there’s nothing really wrong with Visual Basic. It’s a little lengthy to type out, but I jump from VB to c# without even blinking an eye, and it’s just a matter of how will I do this from a syntax perspective.
Erik Darling: What’s that? What does c# have that f# will never have? Jobs.
Brent Ozar: Colin says, “I have noticed a significant decline in jobs when searching SQL Server DBA on Indeed.com over the past two years. I know this is a sensitive subject. Can you speak to why that is happening?” You know…
Erik Darling: The cloud, maybe…
Brent Ozar: I haven’t noticed that, but I haven’t searched. So I would say, what is it that you’re worried about? If you’re worried about having a future or something, you don’t need a lot of jobs, you just need one. And to get the really good one you need to know a company. So you build out your networking and know people, and then that finds the next job that you want. If you’re worried about finding a job from a stranger out on a recruiting farm, dude you’re doomed. A lot of companies just don’t want to hire DBAs because they’re expensive. They’re really honking expensive. They’re hard to replace, they’re niche people…
Erik Darling: They’re cranky, they drink too much.
Brent Ozar: So a lot of shops are trying to use remote DBA services – but I wouldn’t read too much into that.
Brent Ozar: Let’s see here, James [Uganda] says, “The great Argenis Fernadez told me to change your job to data engineer.” Oh sure, dev ops, data reliability engineer – yeah, you name it
Erik Darling: the other thing is that our free scripts are so good people are finding that they don’t need DBAs anymore; they just run the scripts…
Brent Ozar: Sure, the system is self-tuning, ever since SQL Server 7, it just tunes itself.
Erik Darling: I didn’t want to say it but we are just getting replaced by the robots.
Brent Ozar: Joe follows up. He says, “I didn’t ask my question well. If I’m in a command line, PowerShell is too cumbersome and I hate it. But if I’m writing a tool or automation, why wouldn’t I just go to c#? I guess I haven’t found time to reach for the PowerShell instead of just going to c#.” If you know c#, just go to c#.
Richie rump: Yeah.
Have you used SQL Operations Studio?
Brent Ozar: And then the last question we’ll take, Ronnie asks, “Have any of you guys played with SQL Operations Studio? If so, what do you think about it?” Am I the only one? Yeah I’m the only one. So I played with it because I’m passionately excited about it. I use a Mac; I’m all about a Mac. If I could do some stuff just inside the Mac I would love it. It’s pretty, that’s cool. It has one big huge problem, which is that it doesn’t show actual execution plans graphically right now. It just so happens that that’s my job, tuning query plans, basically. So that doesn’t work too well for me.
The other big problem that I have for it is it’s licensed under the source license by Microsoft, which means you can contribute code but it’s the Hotel California – you can never get your code back out. You can’t compile this thing and distribute it out to other people. So if we wanted to like check our own code in, we can do that but then we can only compile it on our own desktops; we can’t even like give it out to somebody else. So there’s a GitHub issue to get them to change it to the MIT license, the same way that visual studio code is licensed. I have high hoped for that. if they’ve fixed the actual execution plans and switch to the MIT license, I’m going to be the world’s biggest evangelist to that thing.
Erik Darling: You should actually show that on the screen, that GitHub issue, so people can go click the thumbs up on it. That’s what gets GitHub issues…
Brent Ozar: SQL operations studio GitHub issues. Then let’s go into the search – so GitHub, if you haven’t used GitHub before, you click over on the issues tab, type in license and there’s this issue, a more permissive license, in which we lay out the reasons why this is kind of a big deal. It’s got 29 upvotes now, it would be cool if we could get more on there. And I’ll blog about that too coming up. Thanks everybody for hanging out with us on this week’s Office Hours and we’ll see y’all next week, adios.