This week, Brent, Erik, Tara, and Richie discuss compatibility levels, SQL server SPN problems, Always On Availability Groups, failover clustering, capturing DML stuff, disc defragmentation, replication snapshots and reindexing, load testing, PowerShell, and whether you should pursue the path of a DBA.

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 – 2017-02-08

 

How do I know it’s safe to delete an index?

Brent Ozar: We’ve got a bunch of questions in here already, we might as well go ahead and get started. We’re a little early, but why not? You people have good questions in here. This is an interesting one. Roland asks, “How can I be sure of no negative effects when I’m deleting an unused index? Is there any way that the optimizer is using those stats for a better plan but not using the index itself?”

Erik Darling: The only way to do that is to test, man. There’s not a better way. This is something I think I talked about in a blogpost but I can’t remember. I don’t know if it’s out yet. When you’re adding or removing indexes, or whatever you’re doing with indexes, people always focus on the one query that they’re working with. So if they’re adding a missing index they’re like, “Oh, it makes this query better.” But they never like sort of regression test other queries or other things that go on on the server as well. So really the only way to do is to know your workload, try to know your queries. I’m not asking you to know every single index they use, but try to know which queries run on average. When you go to add or remove indexes, make sure that you can replicate your normal workload to see if there’s any regressions within it. It’s hard but that’s why you get paid a lot of money, pal.

 

Can I get different plans in different database contexts?

Brent Ozar: Kyle says, “Can you stand just one more compatibility level question?” He says, “I’m asking about compatibility levels between databases on the same server. I had a query that went totally wonky when it queried across databases. One of my databases is 2012 compat, the other was 2016.” So could you get different query plans for having databases in two different compat levels?

Erik Darling: Yeah, cardinality estimator is different.

Brent Ozar: You can have the same query run in three different databases, as long as you fully prefix your objects with like SELECT * FROM StackOverflow.dbo.Posts, you can run that in several different databases and get several different query plans. Kyle mentioned that you’re on 2016, it gets even worse. 2016 has database-level settings like MAXDOP too, and that can give you different query plans per database that you’re in.

Erik Darling: There’s forced parameterization at the database level too.

Brent Ozar: Yeah, that’s good.

 

Where should I start looking for SPN problems?

Brent Ozar: Paul says, “We often lose our SPN for SQL Servers and then they need to be rebuilt.” Oh dear god. No, you don’t have to do that. “Where should I start looking for SPN problems and is this a common problem?”

Erik Darling: It is a common problem. It’s actually one that I fixed—haha.

Brent Ozar: I’m not so sure about that.

Erik Darling: Well, you know, it feels like I fixed it. For your service account user, the thing that did the trick for me, not having to rebuild them was to give it permission to delegate Kerberos authentication, it’s one of those. That’s what always worked for me. When I restarted SQL, it would re-get the SPN without a problem.

 

Are active/active Availability Groups a good idea?

Brent Ozar: Erik says—not that Erik—but he does spell his Erik the same way. He says he had a colleague mention that he wanted to have an active active AG—love this question already—so that we could make use of unused resources. “Basically, one node handling a database and another node handling the other database. Then synchronizes them across. I have an inclination that this is something I want to avoid. Any help would be appreciated.” I don’t know if he wants help with… what’s the question in here?

Erik Darling: Should I? Will I? Tara, take it away.

Tara Kizer: There’s nothing wrong with it. I mean, you’re going to need to license both servers. You need two availability groups since they’re going to be writing on one node and then writing in a different database another node. You may want to have a third node just in case if one node can’t handle the load of both databases if you lose a node. A third node would be recommended for a scenario like this where two nodes are active. You want to have a passive node in this scenario.

Brent Ozar: What would make you choose Always On Availability Groups over failover clustering if you had the choice between those? Is there anything that would make you choose one way or the other?

Tara Kizer: For me, I like failover clustering but we also had disaster recovery requirements so we had to pair that with database mirroring. So availability groups, we were able to get rid of FCIs and database mirroring and just use one technology. So just using one technology was my goal. Plus, we got to get rid of replication for read-only reports. So got to get rid of three things for just one feature.

Brent Ozar: Plus, we fired that guy we never really liked.

 

What’s the easiest way to capture DML on one table?

Brent Ozar: J.H. says, “What’s the easiest way to capture DML stuff—inserts, updates, and deletes—for a specific table?” He heard that change tracking and change data capture doesn’t track things like username, T-SQL statements, date and time, and host and IP address. Wow, you want to capture a lot of stuff for a specific table. Have you guys ever tried to audit username, host name, IP address and all of that for inserts, updates, and deletes?

Erik Darling: Not I.

Tara Kizer: The performance overhead on this kind of stuff is just not worth it usually. Why are you trying to audit this stuff?

Brent Ozar: Oh, he has a follow up question. This is, boy, this really makes it interesting. He says, “It’s in dev and there aren’t too many DMLs, so I’m not really concerned about performance. Would triggers be good? Or something better or easier?” It’s in dev, I have an easy way to do that. You restore the database every day. Every day you restore from production. Then anybody who is complaining about their inserts, updates, and deletes, whoever starts complaining that their data is lost, that’s how you find out who did it. Dev is not where you keep your data.

Erik Darling: If it were production, for that granularity I would go with a third-party tool. I would want someone who already has it figured out. Rolling your own for that is a pain in the butt.

Tara Kizer: And for a dev environment, I might even just use a server-side trace, not much activity is occurring there. Just go ahead and trace it.

Brent Ozar: Yeah, it’s easy to do. Every now and then I meet DBAs who are like, “I want to control everything with an iron fist and not let my developers do anything.” I’m like let them go put data in. They’re trying to do test cases and I know Richie puts garbage data in the database all the time. I’m not sure he puts in any other kind of data in.

Richie Rump: Yeah, “Brent sucks. Brent sucks a lot. We don’t like Brent. Go away, Brent.” You don’t actually read the dev database so I can put whatever I want.

Brent Ozar: Yeah, then it becomes production. Next thing you know, wait a minute, our website…

 

Have you ever had disk fragmentation affect performance?

Brent Ozar: Nestor says, “Did you guys ever experience performance problems that were due to disk fragmentation aka the hard drive?”

Tara Kizer: I would never know because I don’t ever worry about disk fragmentation. So how would I even know if I’m experiencing performance problems because of it? I don’t worry about it. Is it a problem? I don’t know. Who can defragment their disks? Who’s got the downtime to do that? I’ve always supported 24 by 7 systems that have really high SLAs.

Erik Darling: You can run into some bad problems too. If you’re on a SAN and you’re thin provisioned and you run disk defragmenter, all of a sudden you are very thickly provisioned in some situations. The last time I defragmented a disk and it did something, it was like Windows 95 and it made Riven like that much faster.

Richie Rump: Yes.

Erik Darling: Like the fog looked a little bit better in Riven after I defragmented my disk, I think. But that might have been the restart. I don’t know.

Richie Rump: Images came up just a little bit faster, right?

Brent Ozar: Don’t you think that back in those days the display of the disk defragmenter thing in Windows, that was the big reason to run it. It was really cool.

Erik Darling: … changing colors, it was nice. And like all the computer fixing suites had like their own defragmenter. Norton had one and McAfee had one and they’d all do something different.

Brent Ozar: The good ole days.

Erik Darling: Fragmentation has been a Windows lie for decades.

Tara Kizer: I wonder if the only time to worry about disk fragmentation is when you’re encountering an issue with sparse file. When you have the sparse file issue with CHECKDB isn’t that compounded by the disk fragmentation?

Brent Ozar: Yeah.

Tara Kizer: There’s a workaround for that but that’s the only thing I could think of.

Brent Ozar: Yeah, and when you talk about fixing it, if you’re really fragmented that bad that CHECKDB won’t run, then you’re really talking about restoring over to another SQL Server if you want to minimize downtime. Do log shipping or database mirroring over somewhere else. Who wants to do that? That’s a hell of a way to fix fragmentation.

Erik Darling: Yeah, plus you’re probably on some ancient, awful server with like misaligned disks and whatever, you probably have Windows 2003 or something.

 

More about the active/active Availability Group concept

Brent Ozar: Erik says, “I have a follow up to the active active availability group question.” He said he would rather just leave both of these databases on the primary and not have to split them up across nodes. “My real question is are there any benefits across splitting these databases onto separate nodes or would the performance be the same if they were all on the same node?”

Tara Kizer: No, the performance is different. If an instance runs on one node, another an instance runs on the other node, you’re using separate hardware so all your DML, if it’s synchronous, it’s all two-phase commit. But it’s got different plan cache, different buffer pool. You’re utilizing the hardware of two servers.

Brent Ozar: Buffer pool is a great point, yeah.

 

Would RCSI cause a problem in simple recovery model?

Brent Ozar: William says, “What kinds of problems would you encounter if you enabled both RCSI and simple recovery model?” I would like to point out that he used the word model which is Tara’s favorite hot button.

Tara Kizer: Yay!

Brent Ozar: Wow, that’s an interesting question.

Tara Kizer: I don’t see how they’re necessarily related. RCSI doesn’t really care. Your snapshots are being stored in tempdb’s version store. So I don’t see how—full recovery, simple recovery model, who cares?

Brent Ozar: If you have a long running transaction, a BEGIN TRAN, it doesn’t matter whether you’re in simple or full recovery or whether you have RSCI turned on. That log file is going to grow.

 

How do I know if SQL Server is using a lot of CPU?

Brent Ozar: Victor says, “We had high CPU usage on a SQL Server. I don’t think it was actually the SQL Server service but it’s postmortem. How can I rule out the SQL Server was the problem to my manager?”

Tara Kizer: What kind of monitoring did you have in place to have collected the data? Did you have WhoIsActive collecting data every minute or something like that? What kind of monitoring do you have in place? If you don’t have anything in place, you can’t answer this.

Brent Ozar: I feel like an idiot for not thinking about that. I was just like immediately like, “Well, you can’t.” The other thing, if you caught it within four hours, CPU usage is in the ring buffer—but I’m sure, you didn’t wait for Office Hours. It didn’t just happen two hours ago.

 

Is Resource Governor a good way to fix slow queries?

Brent Ozar: Doug says, “I’ve been using Resource Governor to prevent poorly written queries from specific reporting servers from taking down an entire SQL instance. Is that the proper use for Resource Governor or is there a better way to fix this?”

Tara Kizer: I get worried about slowing down problematic queries because now they’re going to be running longer and taking out locks for longer. Possibly wrecking more havoc because they’re running longer.

Erik Darling: I guess if you’re only concern is that they don’t take over the server, then that’s probably a pretty valid use for it, but you know, I would expect repercussions at some point.

Brent Ozar: Often the thing people complain about is hammering the disks too and it didn’t control I/O until 2016. So if it was written really poorly, it was still just hammering the disks.

 

Does a replication snapshot try to rebuild indexes online?

Brent Ozar: Philip says, “Does a replication snapshot try to rebuild indexes online?” Philip, I don’t even recognize the words in your question. It’s a long question. Now you have me intrigued. Normally, Phillip, I would have stopped here. He says, “I ask because we get application disconnections during a replication snapshot with 2012 Standard Edition. I would want Enterprise but not my choice and it wouldn’t matter because I could reindex online. I think it might be reindexing because of a message about reindexing I saw pop up on the replication monitor while it was processing the snapshot.”

Tara Kizer: It has to create the indexes if you told it to on the subscriber. So it’s not reindexing the publisher database. The only effect that takes place on the publisher as far as something bad is when it has to do the schema locks towards the end so you could experience some downtime, depending upon how sensitive your application is while at the very end of that snapshot process when it does that. Or maybe it’s during the entire snapshot process but the snapshot usually is pretty fast, even on large databases. It’s applying the snapshot on the subscriber that takes a while.

Erik Darling: I would probably just want to stop doing index maintenance and see if you still get the same messages.

Brent Ozar: Oh. That’s true too.

Erik Darling: I would just stop doing it. If your snapshots work then…

Brent Ozar: I thought you were going to say stop doing replication.

Tara Kizer: That too.

Erik Darling: I would stop doing both in a perfect world but if one’s breaking the other then you stop doing one. If the other one works, then cool.

 

How can I tell if linked servers are slowing me down?

Brent Ozar: I like Nick’s question. Nick says, “How do I go about getting metrics on linked server usage and performance? Someone set one up without my consent and now I need some data to back up my reason to launch them into the sun.”

Tara Kizer: I would look at the wait stats and look for the OLEDB wait stat. If that’s really high up on the list and there’s a lot of waiting time on it, linked servers are usually the culprit of OLEDB but you need to figure out what queries are running.

Erik Darling: BlitzCache will actually warn you if you have expensive remote queries in it. I wrote that one, that’s the only reason I know. If Jeremiah had written it, I would have no idea. It will check your plan cache, I forget what all the rules are but it’s like if you have an expensive remote query operator in your execution plan it will throw up a warning about that.

Richie Rump: See, I question his decision to launch him into the sun. That seems expensive. You got to get that person all the way out of the atmosphere and then breaking the gravity of the earth and into the sun. Besides, nobody can see them at that point. They’re burned up. So I think maybe stringing him up somewhere where everybody can see what happens when you do something stupid, I think that may be a better use of your time and your money.

Erik Darling: When really all you need is Aqua Net and a lighter. Can save yourself a whole lot of rocket fuel.

Richie Rump: I was just thinking duct tape and a flag pole but okay, man.

Brent Ozar: Here, ladies and gentlemen, you can see the difference between New York justice and Miami justice.

 

Is Always On recommended in virtual machines?

Brent Ozar: Anna asks, “Is Always On recommended in virtual machines?” Sure.

Tara Kizer: I’d support it in both. And what do you mean by Always On? Are we talking about availability groups or a failover cluster instance? Yes, virtual or physical.

Brent Ozar: Here comes Allan Hirt.

Tara Kizer: The Always On part drives me crazy. I don’t care about the space, the capitalization, things like that. “Always On” is just a marketing term.

Brent Ozar: And what does it refer to? Because there’s a couple of differences there.

Tara Kizer: Usually people are trying to refer to availability groups but Always On also has failover cluster instances underneath it.

Brent Ozar: Just like the word snapshot, there’s like half a dozen uses for it.

 

Can you mark a table read-only?

Brent Ozar: John asks, “Is it possible to mark a table as read only as opposed to marking an entire database as read only?”

Erik Darling: No, but you could make it so that no users can actually do anything to it.

Brent Ozar: No, you can do better than that.

Erik Darling: Really? What would you do?

Tara Kizer: Put it in its own file group and mark it read only? Is that what it is?

Brent Ozar: Yes.

Tara Kizer: Can you mark a file group read only in SQL Server or are you doing it at the file level?

Brent Ozar: You’d mark the whole file group read only. It’s elegant, users have no idea that anything changed, their just inserts, updates, and deletes fail.

 

Does replication split updates into deletes and inserts?

Brent Ozar: Roland says, “Are there some resources on how multiple independent updates on a table are aggregated into one transaction by an indexed view being replicated to another server?” The hell are you asking, Roland? Oh, he says in a follow up, “I think that my updates are being split into deletes and inserts.”

Tara Kizer: That’s true of replication but it doesn’t have to be, that’s just by default. Updates do get switched to delete and insert but there is a setting where you can change it to be updates.

Brent Ozar: Are you kidding me?

Tara Kizer: No.

Erik Darling: Is that a setting or a trace flag?

Tara Kizer: I don’t remember.

Erik Darling: I think it’s a trace flag that does it. Yeah, change data capture actually does the same thing when you update it, it shows an insert and delete. I’m not sure if that’s for every update because I know that behind the scenes the optimizer will make a choice to either do a simple update or an insert and delete combo. There’s like the whole split, sort, collapse chain of operators in an execution plan. So I don’t know if it’s every single one but for the ones that do do that, it’s kind of annoying.

Brent Ozar: I think between all of us working in our company chatroom I learn something sad about SQL Server every day. Many days I’m also happy I learn cool things, but most days I learn something sad too. That’s my sad thing for today.

 

My Windows team wants to put data and logs on separate drives…

Brent Ozar: Justin says, “My Windows team, they do our SQL backups.” This sounds like the beginning of a poem. “They want to put all of our high I/O databases onto a single drive and their logs on a separate drive. Would this be bad for performance? Right now we have multiple drives and our databases are spread out by I/O.”

Tara Kizer: What are they trying to solve though?

Brent Ozar: I like that question. I wonder if they’re trying to do like snapshot backups or a specific SAN vendor snapshot backups that makes the data and logs be on different drives. Justin says, “Our backup software has issues.” Yes.

Tara Kizer: Nix the backup software.

Brent Ozar: Yeah, or switch to native SQL Server backups. It’s just so much easier, write to a file share then backup that file share. Do like Bill Clinton, [imitating Bill Clinton] “I feel your pain. Let’s get together and fix your pain.”

 

How do you load test a new SQL Server?

Brent Ozar: Guillermo says, “After setting up a new instance of SQL Server, do you do any kind of load testing? What kinds of tools or methods do you use for this?” Erik is in the midst of—just finished writing a white paper for Google about this. What’s your white paper? What’s the methods in there?

Erik Darling: Goodness. The stuff that I really like to do is before you even install SQL Server, you want to get something like CrystalDiskMark or Diskspd fired up and make sure that you’re getting adequate disk throughput because if you’re not, before SQL Server is on there, it’s not going to get better after you install SQL. SQL doesn’t do anything magically to your I/O. After that, I want to throw something like CPU-Z on there and make sure that my CPU speeds are as advertised. I want to make sure that I don’t have something like balanced power mode or something else yanking them down, something ugly like that. Once I validate my hardware works as is, then I’m going to move on, I’m going to install SQL. I’m going to start running backups, DBCC CHECKDB, maybe some index rebuilds just to really test server throughput. From there, you can expand on to testing like actual query workload on there. What you really want to make sure is the basic maintenance tasks that you run on a new server run the same as they do on your old server. If you have a match on those or you do better on the new server, then you’re in good shape for going forward.

 

Should I learn PowerShell for my career?

(Editorial note: to the PowerShell folks who are inevitably reading this and about to convulse into spasms, ready to pound their keyboards with the force of a thousand soldiers, make sure you read the question and really digest it. The last few words of the question are really important.)

Brent Ozar: Brian asks, “My sysadmins tout their knowledge of PowerShell. I poked around with it…” [Erik makes blowing a raspberry sound] There’s your answer. I can stop reading there. “I poked around with it but as a DBA I’ve never had a substantial need for it. In your opinions, how important is it for me to learn PowerShell from a looking forward career perspective?” Well, we’ll go in a row. So, Richie, do you need to learn PowerShell for your career?

Richie Rump: F PowerShell.

Brent Ozar: Erik, do you need to learn PowerShell for your career?

Erik Darling: What he said.

Brent Ozar: Tara, do you need to learn PowerShell for your career?

Tara Kizer: No. However, I have attended a PowerShell class at one of my companies. My last job did use PowerShell quite a bit. The DBAs wrote scripts to do various things to help us do some tasks that we had to do, let’s say quarterly. The PowerShell script helped us do those tasks much more efficiently.

Erik Darling: Yeah, PowerShell has some neat stuff where you can spread out to multiple servers and do things. It has some all right integration with active directory and failover clustering, there are some cool commands that you can run and do things with. But generally for the things that I see most people applying PowerShell to, it’s like there is already a good enough hammer for that.

Tara Kizer: I think that maybe PowerShell would be useful for larger corporations that have a lot of SQL Servers and you need to loop through those servers to do the same repetitive tasks across all of them.

Erik Darling: But you’ll run into problems in there too.

Richie Rump: There’s an opening for Oracle PL/SQL report writer, maybe I’d rather be doing that.

Erik Darling: When I had to use PowerShell, like when I tried to use PowerShell across an enterprise, I got screwed so many times by just like there’s different versions of PowerShell on some machines. PowerShell isn’t set up to be remotely accessed on some machines. You always had to do this annoying check to make sure you could actually execute a remote script or something. Just like the whole thing was like…

Richie Rump: Yeah, I got a few things about PowerShell. I actually had to write some—what was that, a week ago? A couple weeks ago? I don’t know. The problem is is that the documentation isn’t that great compared to things like C# and some other stuff. It’s really hard to get in there, okay, how do I do X? Or how does this thing work? It’s not that intuitive. The other thing is if I wanted to do X, which way to do that? The blogging community, there’s like 15 different ways to do that. There’s not one generically, hey, this is the right way to do it. It’s always the next version is the right way to do it, but you don’t know, like Erik said, which version is on which machine. So things get all out of whack. It gets all out of hand. The other thing is the PowerShell community, I’d lump them in with the Apple and the Crossfitters.

Brent Ozar: And vegetarians.

Richie Rump: Vegans, really. Not vegetarians. At least I can get along with them. Hey, let’s have some fish. The problem is is that everything is PowerShell. It’s never, “Hey, can we try another technology, maybe do it better?” No. The answer is always PowerShell. I’m Thor PowerShell wielding a PowerShell hammer and I’m going to beat you into submission. I’m a multi-toolset guy. I like to use a lot of different technologies depending on what I’m using. Just the other day I was using DynamoDB and Postgres. I work for a SQL Server company, people. Seriously. I just don’t get the one tool fits all thing.

Erik Darling: “I wrote some PowerShell that runs a SQL query.” Like, what? All right.

Brent Ozar: I have this thought and I’ll keep beating the dead horse. I’m really motivated by retirement, like I want to cross the finish line to retirement so I can go drink and do whatever it is retired people do, just vegetate on a beach. If something will make my salary go up, I’m interested. I’m vaguely aware. If something won’t propel my salary upward, I’m kind of like, yeah, it’s cool, but there’s so many things I’d rather learn. I want to learn Spanish and French. There’s all kinds of things, or how to bake bread.

Erik Darling: Oracle.

Brent Ozar: Oracle, yeah. Postgres. DynamoDB. All kinds of stuff. So I’m kind of like is PowerShell going to make me more money? It’s a good scripting language I guess but if I’m going to learn a programming language at this time, it’s going to be C#. It’s going to be something that I can reuse across a wide variety of stuff. I didn’t say JavaScript.

Richie Rump: JavaScript.

Brent Ozar: That ranks up there with SAP. I get that you make a boatload of money doing it, but it is hard.

Richie Rump: So he’s motivated by retirement and yet he still employs me, ladies and gentlemen. I’m not quite sure I understand.

Brent Ozar: You’re inspiring. Being around you is so calming and soothing because nothing gets worked on. It’s almost like I’m retired already.

Richie Rump: Hi, I’m Richie Rump. Have you met me at all?

 

Is every DBA supposed to know ETL and data warehousing?

Brent Ozar: Graham says, “I’ve been looking at job postings for senior DBAs and I see a lot of data warehousing and ETL experience required. I’ve also seen a job that asks for 8+ years of experience with SQL Server 2012. Are these job postings from people who want the flavor of the month or are ETL and data warehouse work really becoming the purview of DBAs?”

Tara Kizer: I just see those and go, “Next.” I just look right over them. I don’t give them any attention whatsoever.

Brent Ozar: Wait a minute. Why are you looking at job ads at all?

Tara Kizer: They just come through LinkedIn all the time. I find it entertaining for the most part. I’ve always looked at the recruiter emails over the years and I was very happy at the time with where I was. But as far as the 8 years of experience with SQL Server 2012, they just mean SQL Server and they want you to have experience with 2012. But the ETL/ data warehouse thing, the company is just looking for a jack of all trades. They want to pay for one person and not two. This is just a money thing. You don’t have that experience and you don’t have any interest, just don’t apply for it. I wouldn’t be applying for that job, that’s for sure.

Erik Darling: The other thing to keep in mind is that the people who write job postings are either HR people who have no idea what they’re asking for. Or, they’re tech people who are supremely pants-less when it comes to what they need for SQL Server. So they will just throw whatever crap out there they think might be necessary. They’ll just like open up the installer and be like, “Yeah, we need database engine. We need R. We need SSIS, SSRS. We need data quality. Yeah, data quality sounds good.” They’ll just throw anything in there, years of experience, you need AG, performance tuning, backups.

Richie Rump: I think there’s a mentality too to cast the net wide so I could theoretically get more candidates and then hopefully I’ll get the one I want in that wide net. Typically, that usually doesn’t work well for, especially guys like me. I’m like, “No.” Because I see the same thing that Tara does. You want a jack of all trades and that’s not me. So have a nice day.

Brent Ozar: They’re writing that position to replace somebody who was stressed out and left. It was the one person, the one DBA who had to put together 15 things with duct tape and he’s like, “I’m so tired of this, working with this. I’ve got to get out of here.” Then they’re like, “Okay, we’ve got hire somebody to replace him,” not thinking that there’s a reason that guy left.

 

Can CHECKDB be slower on an AG secondary?

Brent Ozar: Tim asks, “Can CHECKDB be blocked? I was running it on a secondary in my availability group and it took 9+ hours but when I restored it somewhere else it only took an hour.”

Tara Kizer: I would look at the load on the original server. CHECKDB is a very I/O intensive resource process. If you’re running it when other loads are occurring, it’s going to slow it down. Even if it’s off hours, a lot of people have a lot of maintenance type stuff that has to occur at night and your test server probably doesn’t have any load. I’m surprised that a test server would complete in an hour when it’s nine hours in production because usually a test server has much lesser hardware and could take a while.

Brent Ozar: Slower storage.

Erik Darling: One thing that Brent wisely has brought up in the past is when DBAs set up their jobs, they set them all up on every server to run at the same time. So it’s always like all your backups start at midnight, CHECKDB always starts at 2:00 in the morning. Your index rebuilds all start at 4:00 in the morning, so that SAN at every like two hours just gets a new set of maintenance tasks.

Tara Kizer: Our SAN admins always knew when CHECKDB was running just based upon the I/O load.

Brent Ozar: Because you’re like, well, it’s all different servers. How bad could it be? Not knowing that they all have something in common.

 

Should DBAs be in the 90th percentile of the SAT?

Brent Ozar: Tim might have seen the strangest thing that I’ve seen in a while. He says, “I’ve seen a job listing that mentions that they want a candidate to be in the 90^th percentile of ACT and SAT testing for their DBA.”

Richie Rump: I.e. we want somebody young.

Tara Kizer: I never even took them. I had no plans of going to a four-year college right out of high school. I was going to go to junior college. That was always the plan to save money and it’s not required to go to junior college so they would not be able to hire me with that.

Erik Darling: I never took the SATs.

Brent Ozar: I was top 1 percent of top 1 percent. I was a National Merit finalist, got full rides anywhere I wanted to go and I just wanted to go as far south as I could. I went to Houston just so I could get away from the snow. I’m like, “This place is amazing. There’s Mexican food and all kinds of stuff.”

Richie Rump: Houston is far from amazing, Brent.

Brent Ozar: Oh, it’s fantastic. To live there.

Richie Rump: It’s terrible. I spent 24 hours with you and I’m like, “Get me out of here.”

Brent Ozar: It’s probably easier—yeah—and at the time I was a Cure fan. I had long, dyed black hair. It was an unusual time of my life.

Erik Darling: So Texas was the place for you.

Brent Ozar: It was. Well, I continuously got pulled over. I had a Camaro, like an IROC Z kind of Camaro with T-tops and like black louvers and all that over my windows. I was continuously getting pulled over because they thought I was trafficking weed back and forth between Michigan and Texas when I would go up to see my family or whatever. I fit some kind of stereotype, I just don’t know what it was at the time.

Richie Rump: It wasn’t a stereotype here in Miami, I’m sorry, Brent. You would be pulled over too just because you’re weird.

Brent Ozar: Although I—well, yeah. See, that immediately makes me want to go down to, “Well I wasn’t that European guy coming over to Miami wearing a thong.”

Richie Rump: We welcome them with open arms because they have money. Please come.

Brent Ozar: They do have a lot of money, yes. You opened your arms kind of outstretched though, like you’re looking for a hug. I don’t know that you want that with the big European guys in speedos.

Richie Rump: How much will I get for this hug?

Erik Darling: The good thing is they’re covered in so much oil they’re easy to squirm away from.

Brent Ozar: That’s gross.

Richie Rump: You were speaking of retirement, Brent. That is my retirement goal. That’s to hit 300 pounds so I fit in my speedo. That is my retirement.

Erik Darling: Tara looks so disgusted right now.

Brent Ozar: Our next company retreat is going to be somewhere that requires parkas and fur coats.

Erik Darling: Now Tara is really looking at those job postings.

Tara Kizer: I’m in a down jacket today and have been for the past two weeks probably. I live in San Diego and I still have to wear a down jacket so I’m just going to freeze in August for the retreat.

Richie Rump: Yes, and I, like some of our clients, am not wearing pants.

Brent Ozar: Many, many of our clients.

 

What does a typical DBA’s day look like?

Brent Ozar: Last question we’ll take. Chris says, and this is so interesting, “I’m a database developer looking to move into a more traditional DBA role but I’m not quite sure what I would do on a daily basis. What does a typical day for a DBA look like?” We’ll work across and go from—in say your last job, Tara, as a DBA period, whatever DBA means to folks. What did your typical day look like?

Tara Kizer: I was usually working on project work. I had things assigned to me to go do. Sometimes I would be doing performance tuning, production stuff that wasn’t running very well, trying to see why it suddenly went from good performance to bad performance. You know, bad execution plans and things like that, looking over the WhoIsActive data. Usually, I was just assigned tasks. As an on-call DBA, when I was on call I’d have to be checking all the alerts and resolving whatever issues those were. Last job was a bit different. I’d say at other jobs though performance tuning was a great deal of my time and unfortunately, troubleshooting replication was a sizable percentage at times. That was unfortunate. But working on emergencies constantly. Just production outage type problems where maybe it wasn’t a server down but a significant blocking issue that the application seemed like it was down. Fighting fires a lot of times.

Brent Ozar: Erik, how about you?

Erik Darling: I think my last job was of course, I was a DBA at a Relativity shop, which changes things I think a little bit from traditional DBA roles where you’re just full time supporting a third-party application. So you’re maybe not doing query tuning as much. You are doing server and hardware tuning and coming up with plans and maybe separate heavy use cases out to other servers and stuff like that. You will do like index tuning because kCura is awesome about you adding indexes. They just say go crazy, whatever fits your workload, if it fits your macros approach to indexing, which is nice. So you can have control of that. On top of that, Tara’s different from me because she was always part of big DBA teams. When I was a DBA, I was by myself. So whenever there was an outage, whenever there was a restore that needed to get done, whenever something broke, it was just me. Me constantly. So that’s why it kind of depends on what sort of environment you end up in, the breadth and girth of the things that you’ll be exposed to and end up doing in a day.

Richie Rump: Chris, I’m going to talk to you. Just you. I like my coworkers but they lie. What you do when you walk in, you see, did my backups run? Then you figure out I need to schedule my backups. Then you run your backups. Then you leave, then you go and do it all again. That is a DBA. You don’t want to do that, man. You want to continue where you’re at. If you’re a database developer now, learn more about SQL. Learn more about the internals and how it works, how to performance. Do that and then get more into the development side. Get more into JavaScript, C#, those types of things. Get more project work because when you’re on a project, you don’t have a pager. You do not want a pager. Don’t do that. All right, man, don’t throw your life away man. You are too good. I love you, man. I’m talking to you. You’re the best. You’re the fairest. You see what I did there? You’re awesome. Don’t do this to yourself, man. You have too much to live for.

Erik Darling: I agree with Richie.

Brent Ozar: If you like database development, if it’s something that you’re enjoying and having a good time with, because it is a fun career. There’s a lot of cool stuff around being a data developer but I was intoxicated by the—I wanted to be in the middle of the room. We would have a bunch of sysadmins and network people and developers and executives and there’s an addictive thing to being the guy who troubleshoots the problems. But the flipside of that is, you are the guy who troubleshoots the problems. It’s the on call, when I finally switched to going into consulting and I didn’t go on call was probably the best month of my life when I realized that I’m like, “I can leave the phone over there.”

Tara Kizer: Being the guy that troubleshoots everything, that’s who I was two jobs ago and even when I wasn’t on call, I really was on call still. I always had to be the one that they called even when I wasn’t on call.

Richie Rump: You don’t want to be in the room where it happens. It’s all a façade. You don’t want to do that. Don’t be in the room where it happens. Just let them do it.

Brent Ozar: It’s so different at different times of your career. The first three or four years of your career suck to be the one who’s in the center of the room. You have to gain enough experience where you’re comfortable and go, “No, I can step in front of that train and I know exactly how to get this thing to work.” But, yeah, the first few years are rough. Just when you think you know what you’re doing, then all of a sudden some consultant comes in and finds out you don’t have backups and autoshrink is on, and you’re not wearing any pants. Thanks, everybody, for hanging out with us this week. We’ll see you guys next week on Office Hours. Adios.

Erik Darling: Later.