[Video] Office Hours 2017/1/4 (With Transcriptions)

On this first Office Hours call of 2017, Brent, Erik, Tara, and Richie discuss multi-server jobs, how to find motivated Junior DBAs, working with SAP databases, partitioning, AlwaysOn Availability Groups, tracking SQL users, SQL monitoring tools, data capture, source archiving, 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 – 2017-01-04  

Is it okay to use IF branches in stored procedures?

Brent Ozar: We’ll go ahead and get started. Dimitri asks, “In stored procedures is it okay to use IF branches to control flow or do those confuse the optimizer?”

Erik Darling: I’ve never found the optimizer to be terribly confused by IF branches. Sometimes I confuse myself with them. Somehow I get more confused by IF branches than the optimizer does. They can actually be sometimes helpful for parameter sniffing situations. So if you can branch your logic to call a different stored procedure based on if a big value is coming through or if a little value is coming through, you can call the sub-stored procedure based on that. So it could actually be helpful in some circumstances.

Brent Ozar: Just keep in mind the key thing that he said there, call a different stored procedure.

Erik Darling: Right.

Brent Ozar: IF branches that are coded inside the same stored proc, you’re still vulnerable to parameter sniffing.


Where do multi-server Agent jobs actually run?

Brent Ozar: Michella asks, or Mikella—Michella, one of these days you’ve got to tell me how to pronounce your name. “We’re using SQL Server Agent multiserver jobs.” Oh my god, there’s hardly—oh man, have any of you guys used multiserver jobs before?

Tara Kizer: I’ve always avoided it. I tested it out and it just didn’t make sense for us.

Brent Ozar: Yeah.

Tara Kizer: They’re probably the one company using the feature.

Brent Ozar: Right. She says “…on a SQL 2016 server. I have an SSIS package and a job that runs but it actually runs on another server. Where is the CPU and memory being used? How is the SSIS process packaging?” The way I like to think of it is imagine for a second a package whose query runs CHECKDB. CHECKDB isn’t going to run remotely. CHECKDB is going to run wherever the database lives. Now there are packages you could design, like create stuff and move it from one table or from one server to another via linked server queries if you want to pull data down remotely. So you can make work happen anywhere. But if you just flat out run queries, they’re going to run at the target. To learn more about multiserver jobs, the guy you want to know is Mike Hillwig. If you search for “Mike Hillwig multiserver jobs,” especially you throw in the word SQL Saturday in here. Mike has down presentations at several SQL Saturdays and you can get the slide decks from there. Really smart guy, super helpful there. Literally, the only person I know using that feature.


Should I use table variables?

Brent Ozar: Keith says, “I was collaborating with a colleague who I respect.” It’s very interesting that he prefaces it with that. So, okay, all right. We don’t know anything about that here. We don’t respect anyone that we work with, but I’ll continue. “We were optimizing a large stored procedure. He said he never uses table variables when he expects more than 100 rows to be added. He uses temp tables instead. I scoffed since the only differences I was aware of between the two were transaction participation, the ability to add indexes, and the ability to alter temp tables.” So, which one of us is going to break his poor, delicate heart?

Erik Darling: There’s a lot of stuff going on there.

Tara Kizer: Just don’t use table variables. That’s just my rule. People talk about 100 rows, “I use a table variable over 100.” Or maybe it’s 1,000. Some people say 1,000, use a temp table. I have run into severe performance issues when the table variable always had one row in it. I do not use table variables. I know that one advantage to table variables, that they survive a transaction so you still have the data in there that you can query, maybe you’re using it for troubleshooting. For that instance, I would use a table variable. Everything else, temp tables.

Brent Ozar: Yeah, the good news is you respect him for a good reason. He does know what he’s talking about. If you want to learn more about that, if you go to brentozar.com/go/tunequeries, one of the Watch Brent Tune Queries sections involves a table variable and I show you some of the horrific drawbacks there.


Where can I find a motivated junior DBA in Chicago?

Brent Ozar: Paul asks, “Where can my company find a motivated junior DBA in the Chicagoland area?”

Tara Kizer: That’s Brent, but he’s coming to San Diego soon.


Tara Kizer: We already had our reviews, it was last month.

Richie Rump: Shots fired. Shots fired.

Brent Ozar: If it wasn’t so true…


Brent Ozar: DBA is even a stretch. He says, “Seems all the good ones are happy and not looking for work.” I’d say that’s usually generally true too. Junior DBA—such a weird term too because by the time someone is officially a junior DBA, like they hold that in their job title, guess what their next job is going to be?

Tara Kizer: Yeah.

Brent Ozar: It’s not junior.

Tara Kizer: I’d recommend posting it to a job board maybe at the local universities, wherever the business information systems building is, or maybe even computer science. My degree is mathematics with emphasis in computer science, so the math building too might draw some hits there. But junior DBA, it’d be difficult, maybe look internally also. There could be a developer looking to make that move. Developer to junior DBA salary-wise might be bad but look internally. Sometimes there is people looking to move to a different area.

Erik Darling: I’d probably just throw it on Stack. Just throw whatever job posting you have on there at least to get some eyeballs on it. You may not find the person directly from there but I’m sure someone looking at that will be like, “Oh, junior DBA job, I know someone who…”

Richie Rump: When I hear “junior DBA” I’m like you’re willing to train. If you’re willing to train, I was really tight with the folks at my placement center at the University of Miami, where I went to school. They know the students. They know who is good, they know who is not. They’ll send you resumes of the ones—because they want their students placed. They’ll send you the good ones and then you just kind of go through them and you interview them. You’ll get the best and the brightest, especially now. So January, they’re ready to hire, these kids are looking for work. They’ve been four years in school. They’ve been told that there’s no jobs out there for them. They’re hungry. I would go that route if you can wait until the April/May time period.

Tara Kizer: I actively participate in online forums and a lot of people are wondering, how can you even find a junior DBA job? There just aren’t any out there. So the fact that you guys have one is rare. I suspect that you should be able to get some good hits. You just got to get the word out.

Brent Ozar: Yeah, just don’t advertise that we need someone who is already a junior DBA. That’s where it’s not going to work. The other thing I would do is make a list of what they have to already know, that they can’t learn on the job, things that they have to already know. Tailor the job description to basically say, “You’ve been doing this for a couple years. You may be a developer. You may be a sysadmin. You may be a college person. But here’s the things that you’re comfortable doing. Here’s the things we’re going to train you on.” As soon as you’re done writing that job description, ask yourself, “What did this candidate do last year and what job do they want next?” Because over and over again I get these job descriptions where they say, “Must already know these 14 technologies, is going to become a junior DBA.” I’m like, no, no, that person is already on their way, going up that ladder.

Erik Darling: “Oracle. MySQL. Perl. Python. PHP.” I’m like who…?

Richie Rump: “SQL Server 2026.”

Erik Darling: “Must have a decade of experience with SAP HANA.” You’re like you can’t possibly.

Brent Ozar: I was literally—A call I was on earlier this week where someone said, “I don’t understand why it’s so hard for me to find a DBA who is skilled in both architecture and systems administration and development and database administration.” He goes, “I have that personal background.” I said, “What’s your title?” He said, “CTO.” I said, “Exactly. When you have that you’re done with that part of your work.”


What’s it like to work with very large SAP databases?

Brent Ozar: Speaking of SAP, let’s see here. Jonelle asks, “What’s your experience like working with SAP databases in terms of very large databases?”

Tara Kizer: Do you guys have experience with SAP on SQL Server? All the companies I’ve worked for have had it on Oracle.

Brent Ozar: Yeah, yeah. Horrible. Erik, how about you?

Erik Darling: No, SAP is one of the few that I haven’t done much. I mean like, okay, SAP like byproducts like Business One and stuff I’ve worked with, with like sort of sub-products of SAP. But I wouldn’t call any of those databases very large. I think they were sub-terabyte anyway. So they didn’t really…

Brent Ozar: We had a DBA at one company where we had SAP in SQL Server and BusinessObjects and the data warehouse and all that and I kind of loved it because they were like, “You don’t touch anything in here. You don’t change the settings. You don’t touch anything.” The objects were indecipherable. They were all these alphanumeric-type things. I’m like, “Great, I don’t have to do anything.” Any index design you have to do has to come through SAP anyway, so I kind of liked that. The big question to ask there is the same question we ask on a lot of stuff, what’s your RPO and RTO? Put it in writing. Because usually the whole company runs on SAP and whenever it’s time to do the restore, if it’s a two-terabyte database and it takes three hours to do the restore, if something goes wrong, everybody needs to be under the same page that our business is going to be down for three hours.


Will a scalar UDF in a WHERE clause cause the query to go single threaded?

Brent Ozar: James asks, “Will a scalar valued function on a where clause run single threaded and will my estimates versus actuals be correct?”

Erik Darling: That’s going to suck, James.

Brent Ozar: That’s one of Erik’s favorites.

Erik Darling: Yeah, James, that’s not going to go well. Stop doing that. The scalar valued function itself may run in parallel, that’s the funny thing about them is that the code inside a scalar valued function can go parallel, but it will force the entire surrounding query to run single threaded, which is generally not what you want to happen. On top of that, it’s probably going to run in a weird nested loop joint thing and you’re probably going to get that crappy one-row estimate and SQL is not going to know how many times it has to execute to get the data back. It’s not going to go well. Don’t put functions in your where clause, man. I’m begging you. Even table valued functions I’d keep out of there.

Brent Ozar: Especially with the estimated versus actually. Those are going to be wildly incorrect.

Brent Ozar: William says—he has a job posting—he says, “Junior DBA, must be Oracle certified, SQL Server certified, and clearance.” Yes, good one. I see that a lot.


Where can I find more information about table partitioning?

Brent Ozar: Dimitri asks, “Where can I find some literature…?” The library is always a good place to start. But then he gets more specific and says “…about the benefits and drawbacks of table partitioning?”

Tara Kizer: I like Kendra’s recent article. I know that we have some information on our website but she covered the topic very recently on her website. But we do have a lot of information on our website too.

Brent Ozar: And what is Kendra’s site?

Tara Kizer: Littlekendra.com.

Brent Ozar: Yes, if you search for partitioning over there, Kendra is one of our favorites on indexing design, has written a ton of partitioning in the past. It’s getting more popular these days now that SQL Server 2016 SP1 has all the developer features from Enterprise Edition in it.

Tara Kizer: Right.

Brent Ozar: I expect to see a whole lot more of those partitioning questions.

Erik Darling: There’s another pretty good article written by a guy named Dan Guzman who posts on dba.stackexchange a lot. His website is dbdelta.com. He has a post on table partitioning best practices. It’s pretty good as well if you’re just looking for sort of an opening primer. It’s not old, either. It’s from like 2014. So it’s not like ancient—ancient partitioning secrets in there.

Brent Ozar: Wow, yeah, if you google for “dbdelta partitioning” he has a category on his post just on partitioning. Man, I tell you what, that’s making the weekly links. That looks actually good.

Erik Darling: I win a prize.

Brent Ozar: You win a prize.

Erik Darling: I want another stress ball.

Richie Rump: You get to come to work next week.

Brent Ozar: What does the loser get?


Should I implement partitioning in OLTP?

Brent Ozar: Dimitri says, “For a background on the partitioning, my boss wants to implement it in an OLTP environment with queries and procedures that hardly ever use anything that would be considered a partitioning key. I need to convince him that it may not be for us before we spend too much time testing it.”

Tara Kizer: What are you trying to achieve? One of the things why I like table partitioning is to get rid of data or move data quickly out of the OLTP database. I had a seven-terabyte database and we had to make sure that we only had six months of data so we would run deletes in a loop in batches of 10,000 but eventually, this database got so large and the incoming data was a lot. I don’t remember what it was per day but we couldn’t even keep up with the deletes. Not only that, the ghost cleanup record process was so far behind so we ended up implementing table partitioning and we could get rid of the data in like a few milliseconds as long as it could get the schema lock. Sometimes it couldn’t and the job would take several hours but once it finally could get that lock, it just got rid of the data.

Erik Darling: A sort of cheaper, from technical and managerial points of view, would be just to use partitioned views instead of actual table partitioning. You get a lot of the same benefits without the sort of craziness of partitioning keys and begging the optimizer for partition elimination and stuff like that.

Tara Kizer: He has a follow-up comment that the goal is to improve performance. That’s exactly what Kendra talks about in her blog post from a few months ago. I would take a look at that. It has a video.

Erik Darling: Partitioning is not a performance feature.

Tara Kizer: Yeah.

Richie Rump: No.

Brent Ozar: No. And folks, feel free to get your questions in now. We go through first come first serve. We’re running out of questions here for today, which is fine too. We can just sit around and talk about our favorite things about partitioning. That was like the feature I was most excited about when SQL Server 2005 came out. I was in the middle of doing a data warehouse. I’m like, why isn’t this making anything faster?


Why aren’t my statistics the same on all AG replicas?

Brent Ozar: Paul says, “We’re using AOA.” Which, that’s the first time I’ve seen Always On Availability…

Tara Kizer: Call it AG or AOAG, not AOA.

Brent Ozar: “We are seeing statistics change on the secondary for our clustered indexes while the statistics on the primary replica are not changed. How does stats updates work for AOA?”

Tara Kizer: Pick me! Pick me!

Brent Ozar: Tara Kizer.

Erik Darling: Take it away.

Tara Kizer: I have a lot of AG experience, I cover this in a topic that we had at our precon. You get temporary statistics on your secondary servers. You do get the same statistics from your tables but you’re also getting temporary statistics because the databases are marked read only Microsoft couldn’t update statistics in that database. Your query patterns are different on your OLTP database and on your secondary so it may need different statistics. Microsoft invented the temporary statistics and those are in tempdb. Microsoft SQL Server isn’t in control of your statistics on the secondary so you can’t update those or create them.

Brent Ozar: I love it because you can have queries that are slow on two different replicas for two different reasons.

Tara Kizer: Yeah, that’s true.


Can I master a whole bunch of trades at once?

Brent Ozar: M.M. says, “You may have just given me a hint by your answer about the CTO but I’m curious about your perspective. On a large corporation that has a DBA being the all-encompassing server person setting up all file transfers, SFTP, active directory, third-party software, drivers, firewalls, server security… all general admin for more than 20 servers.”

Tara Kizer: If it’s a large company why does it just say single all-encompassing server person? Usually in large corporations everything is very divided out. You have your DBAs, your sysadmins, your SAN people, not just one person that handles everything.

Erik Darling: Yeah, that sucks. There’s no way one person is that good at everything that they could be a general admin.

Brent Ozar: Well you suck at everything. You only spend two, three hours a week per platform or application. You’re not going to grow at that. You’re not going to get better.

Erik Darling: If there’s a big problem with one of them, that’s all you’re doing.

Richie Rump: And yet yesterday I set up a database with all the trims and bells and whistles and everything and it took me ten minutes and I know nothing about all that stuff. I am the cloud!

Brent Ozar: We’re going to be dealing with the aftermath for the next ten years.


Richie Rump: Yep, cloud fixes everything.

Brent Ozar: We are probably the poster child for the cloud in that we have no sysadmin, none of us want to be admins, we all want to cash out at 5pm—really 2pm is really when we want to cash out.

Erik Darling: Whenever happy hour starts.

Richie Rump: 2pm west coast time.

Brent Ozar: When the mimosas wear off but before the tequila is available.


How can I track changes in SQL Server?

Brent Ozar: Clinton asks, “What do you guys recommend to track users that are making changes in your SQL Server environment?”

Tara Kizer: What kind of changes?

Erik Darling: Yeah, what kind of changes? There’s like adding tables, columns, indexes, stuff like that and then there’s changing data. They kind of get different answers.

Tara Kizer: If it’s due to changing data and you don’t want them changing data, don’t give them that access. Have them change the data through stored procedures that have been tested. That way they can’t do any type of rogue queries and you could add in some logging in those stored procedures.

Brent Ozar: If they get the business on their side and the business says, “No, trust me, they need to change the data whenever they want,” then it’s not your problem and you just got to let it go. When the business comes and go, “Who made these changes?” “I don’t know. You told me anyone could make them. I’ve got to let them make them.” I’m a big fan of Colombo.


Should I use autogrow on TempDB?

Brent Ozar: Michella says, “For tempdb should I set autogrow on or not?”

Erik Darling: It depends.

Tara Kizer: I think Brent has a different opinion than say maybe Erik or me, I’m not sure.

Brent Ozar: I bet we have the same.

Tara Kizer: Well, the same, I mean in the install doc, you recommend going ahead and growing out tempdb to whatever that mount point or drive has for storage. I would want it to be 20 percent less than that due to monitoring tools, alerts, but I go ahead and grow it to what it needs to be and then I like to monitor what the growth is and make sure we’re using the larger size for reboots that occur. But I like to be notified. I want to know if the tempdb is filling up and allow it to autogrow so that extra 20 percent due to my alerts, let that autogrow and hopefully I can get the alert in time to possibly add storage.


What SQL monitoring tool do you recommend?

Brent Ozar: Jonelle asks a controversial question. “Which SQL monitoring tool would you guys recommend? I’ve seen SQL Sentry and SolarWinds at the last PASS and I’m torn which one to go with.” What do you recommend we look for when we’re choosing a tool?

Erik Darling: Whatever you can read and use. Whatever you’ll come back to and actually use is what I would recommend. If you open up a tool and you are nothing but confused and it’s not giving you the information that you want and it’s a hassle for you every time you do it and it’s just not giving you the information you need to solve problems, or see if there are any problems that you can root out, then it’s just not the monitoring tool for you. Back when I had ye old real job, I used, well it was SQL Sentry back then but it’s SentryOne now. They had a perfectly good monitoring tool, they had great reports. I liked it. There was a bar graph. When you saw little spikes and hiccups and stuff you could highlight and it would jump to whatever SQL was running at the time. So there’s some really nice features in there. That’s the one that I’m personally most comfortable with and that I would probably recommend but everyone else probably has their own spread of experiences.

Brent Ozar: Tara, which one did you use the most?

Tara Kizer: My last job was using SQL Sentry’s Performance Advisor. The job before that we were going to purchase licenses for all of our 100 servers to use it there. We had been a Quest user, but we kept having issues with Quest, the performance analysis tool, just on some servers, and these are very complex environments. But I really did like Quest’s Performance Analysis and I think it’s been renamed Foglight, and have used Spotlight. I know that you guys will say Quest’s Spotlight and then SQL Sentry Performance Advisor just as a sample of two tools, there’s other great tools out there, but I don’t think that Performance Advisor is comparable to Spotlight. Performance Advisor to me is comparable to the Foglight tool instead, the performance analysis, whatever they’re naming it these days. But I like both tools. I have not used the Idera Diagnostics Manager. We’ve had salespeople fly in and show it to us. It looked great, it just isn’t what we went. As far as SolarWinds, I was doing a proof of concept on that product two jobs ago and it just did not have the features that I needed. Unfortunately, at that job we had to support SQL Server 2005 all the way up to the newest versions and it did not support 2005 so that was a strike against it right away, but it also didn’t have the features that I needed out of a performance monitoring tool. This was about three years ago and so things could have changed since then. I know that they still aren’t supporting 2005 of course, but maybe the features are more comparable to these other tools.

Brent Ozar: Yeah, if you want to read more about it, search for “Erik Darling monitoring tool” and he’s got a blogpost on it that talks about it too.


Thoughts on Entity Framework?

Brent Ozar: Bert asks, “Thoughts on Entity Framework?” Richie, I’ll call on you for this one.

Richie Rump: Yo no sé. No, no, no. [Speaking Spanish] I like Entity Framework. I like ORMs. I like any tools that make development easier but just like any tool, you have to understand how it uses it and then how it uses SQL Server. That’s where most development and development projects get into problems where DBAs take a look at the code and say, “What the heck is going on?” It’s because the developers don’t understand how the queries are being built so they just kind of throw whatever works into their query on the dev side and what comes out to SQL Server is total trash and total garbage. There’s really no thought on how any of that stuff works. I suggest go to Julie Lerman’s site, she’s got a couple books, she’s got some Pluralsight courses on how Entity Framework works. That will give you a great comprehensive idea of how you should be architecting your application with Entity Framework so everything is working correctly. But I like it. These guys probably don’t.

Brent Ozar: I love it. I’m a huge Entity Framework fan—and I know that’s not trendy and I know a couple of people have typed in, “It made me throw up in my mouth.” Here’s the deal, do you like getting paid? I like getting paid. We only get paid if the developers ship features, if they can ship things that customers want to use. Is the code perfect? No, but it’s not solved by simply saying, “You have to write everything in T-SQL.” It’s not like they’re suddenly going to write amazing T-SQL without training either. The root cause on that is usually just a training issue, that people don’t know how to use the tool that they’re using. So DBAs are like, “You should write everything in a stored procedure so I can police it.” Dude, you’re already overworked. You’re working like 60 hours a week trying to keep a plane in the air. I’m a huge fan of stored procedures too but you can’t code everything by hand. “Everything should be done in assembler.”

Richie Rump: “Back in my day, we only had vacuum tubes and we liked it.”

Erik Darling: The old punch cards.

Brent Ozar: “Vacuum tubes didn’t work, we would suck on the keyboard.”

Tara Kizer: That’s one of the classes I had to take for my degree, assembly languages, it was actually one of my favorite classes. I really enjoyed it.

Richie Rump: Assembly language is why I got out of computer science. That was the class. I’m like, “No way I’m ever using this ever again. See ya.” Hello, School of Business.

Brent Ozar: Let the record show Tara Kizer is the only classically trained employee here. She keeps the rest of us in line. The rest of us are like, “The internet is a series of tubes.” We have no idea what’s going on…


What are the gotchas with Change Data Capture?

Brent Ozar: Ronnie asks, “Have any of you ever used change data capture and if so, what do you think about it and does it require a lot of management?” I have not used CDC. How about you guys?

Erik Darling: No, I think I’ve talked about it before, we had one client in the past who used CDC and it broke. They wrote their own CDC on top of CDC. So my one experience with CDC was not normal CDC. It was like, oh god, what happened to CDC?

Richie Rump: I prototyped it once. For the customer that I was dealing with, it didn’t fit their business case for it but just like any other tool, you have your business case for it. You have the set of features of what you want it to do, go off and test it and see if it works for you. There’s other tools out there that may work better for you but since it’s free in the box, go ahead write a POC for it, see if it works.

Erik Darling: What’s the worst that happens?

Tara Kizer: I’ve only used change tracking but not CDC.

Brent Ozar: I don’t think I’ve heard any horror stories. I haven’t heard anybody go—sometimes you’ll hear these horror stories—“You should never use that feature.” I haven’t heard that story, so.


Can I use Developer Edition for our developers to track bugs?

Brent Ozar: Dimitri asks, “Licensing question. Can we use SQL Server Developer Edition for our own internal bug tracking software or do we need Standard Edition?” As a guy who does a whole lot of licensing talks around here, I would tell you that that is production for you. I went through a licensing audit at one point, the licensing auditor had an elegant way of answering this. He said, “If I can turn off the server and the only people who will come running are developers saying that they can’t work at the moment but they didn’t lose any work, then you can kind of get away with calling it development.” But if there’s actual work going on in here, which is what bug tracking is, then it’s production.

Erik Darling: You might be able to get away with SQL Express depending on how big the database is if you’re looking for free, but that depends a bit as well.


Followups on earlier questions

Brent Ozar: Paul follows up and he says, “The Always On Availability Group stats I was referring to are the clustered index stats. The secondaries are updated after hours.” You know what I would do is I would post a picture of DBCC SHOW_STATISTICS. Do that on both replicas and go ahead and post that up on a place like Stack Exchange and you’ll be able to get better info. I think there’s a piece that we’re missing here.

Tara Kizer: Also look at your latency, query both to see what your latency is.

Brent Ozar: Yeah.

Brent Ozar: Steven says, “We use CDC and it works very well.” Breck says, “CDC is a royal pain.” So just like anything else in databases, you get opinions on both sides.

Richie Rump: Can’t please everybody. That’s why you need to test it yourself.

Brent Ozar: Can’t really please anybody.


What’s the best way to source control database objects?

Brent Ozar: We’ll take one more question. Phil says, “What’s the best way to source archive database objects? My company uses SVN for source archiving but I find it very tedious and the free SQL Server add-in doesn’t work properly.”

Tara Kizer: I really like Team Foundation Server, we used that at my last job, very very heavily. I’ve used SVN and other products as well but I really like TFS.

Brent Ozar: Richie is making a face. Richie, what do you like?

Richie Rump: I think the rest of the development world has moved onto Git and/or Mercurial so I like that. Redgate has a tool that really integrates well with other stuff, so that’s an option as well. Everything I have now is in Git, just everything, Git and GitHub.

Tara Kizer: I find Git so hard to use though, especially if you’re only talking about SQL Server database objects, that’s where I draw the line. If you’re talking about a lot of other stuff, then maybe use something like that, but SQL Server database objects, I would be Redgate or TFS.

Richie Rump: Oh, I could give you the horror stories of TFS. Oh, so many horror stories. Personal scars. Here, let me show you my scars here.

Tara Kizer: When I say Redgate or TFS I’m talking about the Redgate schema change feature and then use something on top of that.

Richie Rump: Yep, done that.

Brent Ozar: There’s two ways you can do change comparisons, there’s either before the fact or after the fact. If you do before the fact, the developers are checking in changes before they get deployed to production. For things like that, stuff like TFS works extremely well because it’s well integrated into the developer’s workflow, Git is as well. But that’s generally not schema detection and change detection that gets applied to production. People are just building change control scripts and then deploying them over in production. The other way of doing it is where DBAs often come in, which is after the fact. We go monitor the server once a day or every four hours, whatever, check to see what objects changed and then go check those changes in. I’m a huge fan of Redgate stuff for that, like postmortem, here’s the changes that happened to the database. It doesn’t tell you who did it, doesn’t tell you when, doesn’t tell you why, but as a DBA, I don’t usually need to know that. I just need to know that they changed. It would be nice to know who did it but I don’t have that luxury. Christopher says, “We just yell ‘fire in the hole’ when releasing scripts.”

Tara Kizer: I’ve worked in environments like that.

Richie Rump: Fire in the hole. I’ve worked in environments where the DBAs are not in charge of the database, right? I mean essentially they’re in charge of setup and backups and storage and then that’s it. The development team is in charge of whatever goes into it.

Tara Kizer: I’m okay with that as long as those developers are in the on-call rotation because if I’m in the on-call rotation, I want the people that are making changes, I want everyone on the on-call rotation that can have that kind of access, because I don’t want to be called for some kind of deployment issue that you caused.

Richie Rump: Yeah, exactly. They had a special development dev ops team that kind of monitored that whole thing. I don’t think the DBAs wanted to touch that database anyway.

Brent Ozar: And there’s a different experience here from like Tara is coming from a background of working with replication and replicated databases where if you make schema changes you can be in deep trouble if you do some of those changes.

Thanks everybody for coming and hanging out with us. Bert King says, “Happy New Year.” Happy New Year to you all too. We’ll see you next week. Adios folks.

Previous Post
Thanks, Google
Next Post
#TSQL2sday: System-Maintained LastUpdatedDate, LastUpdatedBy Columns

3 Comments. Leave new

  • Hello Brent,

    Just received the Weekly Links, and the “dbdelta partitioning” link is missing !
    (interesting posts from Dan Guzman indeed, Erik.)


  • I have been using CDC for a couple years now. It works great for our purposes to identify data that changed that doesn’t have a timestamp on the record (it was here before me, not my fault!). We have only had one time when CDC Capture job stopped working. We have our “days back” set to 7 so we were able to turn the capture job back on and everything caught up.

  • As for SAP on SQL Server. Seems pretty popular, there were installations with databases over 25TB years ago, before compression started being recommended by SAP.
    There is a lot of information on Microsoft (and SAP, but most of it requires company login) pages, best one is white paper on MSDN pages:
    Biggest issue with SAP on SQL Server imho is their conservative approach to MAXDOP, not noticing Cost Treshold for Parallelism. There was one recommendation that said that MAXDOP is bad because it creates more than one Execution Plan.
    Managing database within SAP apps is ok for small shops, bigger ones will hit some walls I suppose, like our did (SAP’s checkdb procedure is rather limited when compared to Hallengren’s solution, similar with more complex indexes). I guess some of that is part of app standarisation for different DBs below, but things like In-Memory? Of course that it would hit their own product 😉

    Oh well, SAP says that they will stop supporting 3rd party database vendors on 2025, so whatever 😉


Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.