Blog

This week, Brent, Tara, and Richie discuss what they think about having no more service packs for SQL Server 2017 and newer versions, shrinking log files, how many servers can one DBA manage, log backups, mirroring vs Availability Groups vs log shipping, and their next company retreat location.

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 10-4-2017

What if I have a really long question…

Brent Ozar: Doug says – Doug has a whole paragraph; my goodness. He says, “We have several SQL Servers running agent jobs and they each record their own history in tables. I’m trying to centralize everything… I don’t see anything that links job steps together in any system table or DMV; is there?” I don’t know; I’ve never tried that.

Tara Kizer: What are we linking with? Job steps to what?

Brent Ozar: I don’t know, job steps through the same job ID?

Tara Kizer: Yeah, I mean that’s in there [crosstalk]

Brent Ozar: I would see – ask what you’re going for on Stack Exchange. Go to DBA.StackExchange and then break out what information you’ve got and the DMV script you’ve got so far. Also, the other thing with Stack Exchange is, as you’re pasting in the question, or as you’re typing in the question, they fill in for you other related questions. And I know I’ve seen a bunch of questions about how to write query for job history in there.

What are your thoughts about no more service packs?

Brent Ozar: John says, “What are y’all’s thoughts about no more service packs for SQL Server 2017 and newer?”

Tara Kizer: Well I think that we’re going to find out in ten years when everybody has upgraded to 2017, finally. What’s the adoption going to be anyway? So I don’t think we’re going to see much of an impact at all? I mean, who’s going to be running 2017?

Brent Ozar: I didn’t think of that until you had said it in the company chat room, and then I went back through and looked today. If we go to – and I’ll fire up a web browser and go pull it up – if we go to SpotlightEssentials.com, Spotlight Essentials is Quest’s free monitoring software, and they post the data that they gather. If you click on Collective IQ up at the top, it shows you all kinds of stuff. Like right now, the current adoption rate for SQL Server 2016 is 9%; 9% out of all the servers that they monitor, and that’s more than a year old now. 2016 is more than a year old, so…

Tara Kizer: And I would bet that most people aren’t running – this is a free tool, right? So I would bet that a lot of people who are running SQL Servers aren’t even using free monitoring tools because a lot of people running SQL Servers don’t even have IT staff, so they wouldn’t even know to look for some free monitoring tools or what to do with these things. So this, probably, is just for people that have IT staff, and it’s probably a small percentage of the SQL Server world.

Brent Ozar: That’s true, because people with money, budget and time and all that, they’re more likely to buy monitoring tools. They’re also, I think, more likely to go early cutting edge on adoption stuff. Yeah, Richie feels the pain of constant upgrades. Richie’s in the midst of upgrading PasteThePlan and had to deal with all kinds of dependencies this week.

Richie Rump: Yeah, it’s interesting, y’all complain about an SP, you know, once every six months or whatever. Meanwhile, I have – PasteThePlan has a good 12 to 15 different packages that it’s using via Node Package Manager and every single one of them need to be updated; every single one of them. So yes, that’s what I did for the upgrade…

Brent Ozar: And it breaks everything. Like upgrading one of those breaks everything else.

Richie Rump: It could. I mean, it definitely could. I got lucky with this one; there wasn’t much breaking. But that’s why you have unit tests, to make sure that when you upgrade, the tests run and everything kind of works. But it gets kind of dicey when you’re just doing blanket updates all over the place.

Brent Ozar: My feelings about no more service packs – I love it, it’s fantastic. Cumulative updates make everything so simple, there’s just one train of updates. Before, people were like, “Should I be on this branch or this branch? Which one’s the most current?” Makes it easier for vendors; they just test their stuff on one path straight through the code. I’m just a huge fan. They’re now coming out monthly, which I’m a huge fan of if the quality is good. So cumulative updates will now come out every month for the first year. Most people don’t install them in the first year, so it’s not heartbreaking. It’s only going to be the cutting-edge people. But every month for a cumulative update – when I read through the list of what gets fixed in some cumulative updates, I think it’s kind of scary. But then you read what gets broken in some CUs, like CU 4 recently broke NOLOCK – I say recently it was about a year ago, 2016 CU 4 broke NOLOCK. I’m like, that’s not the kind of risk you want to take.

Richie Rump: Oh, and there was a bunch of stuff that happened when 2012 was released.

Brent Ozar: Service Pack 1 was a mess.

Tara Kizer: I wonder if this change in policy by Microsoft is going to even delay people further from upgrading because it’s going to be 2017 and all newer versions. So if you work for a company that only will apply Service Packs, maybe they’ll even avoid 2017 or higher for a while.

Richie Rump: Yeah, or maybe just say, “You know what, if it’s been around a year, we’ll install it.” And the one year is our, you know, “We’ll start playing with it then. Until then, we’re just going to let them get the bugs out.”

Brent Ozar: Which makes sense, I mean, if at the one year point it switches from every month cumulative updates to every quarter, so it’s just easier to manage.

Richie Rump: But that’s not us here.

Brent Ozar: Yeah, we’re rough.

Why is my transaction log so big?

Brent Ozar: Chris Fair says, “I ran sp_Blitz and I got the message that my transaction log was larger than my data file. This database is in bulk-logged recovery mode and we only do a log backup once a day at noon…” I love the next sentence that comes out of his mouth. “I’ve been here a month and I don’t know the reasons behind either decision. Backups have been only 1GB in size, yet the log file is 67GB. I know you generally frown upon shrinking log files, but in this case, is it okay? And I’m only going to shrink the log file.”

Tara Kizer: It’s okay, as long as you fix the reason why it’s so big. The reason why it’s so big is because you’re only doing one log backup per day. So fix that, run maybe hourly or even more frequent, and then see what size is needed. So shrink it down. I’d probably shrink it down to 0.5GB, and then with those hourly or more frequent log backups see what size it grows to. It might still grow a bit past your 1GB data file, but maybe you need every five minutes log backups. I’ve had now four clients that have done one log backup per day, and it just is crazy. I don’t even know what the point of this is.

Brent Ozar: If you’re only going to recover to one point in time, that’s what simple model is for, simple recovery model. If it’s like a data warehouse where you’re only loading stuff once per day, yeah, maybe simple recovery model is okay. But odds are, Chris, they brought you in because the last person was either incompetent or there was no last person. So good news, you’re asking very good questions. I bet money, the last person didn’t even know about sp_Blitz toop, because that happens all the time. Even when we come onsite with customers, sometimes they’ll be like, “sp_Blitz, tell me more…” [crosstalk]

I have over a thousand SQL Server 2008s…

Brent Ozar: Chris says, “No question – here’s my sadness. I have 39 2008 SP3 servers, I have 1326 2008 SP4 servers…” And then goes on with the rest of his version numbers. But you have enough time to sit here and watch webcasts when you’ve got over 2000 SQL Servers. You can’t be that bad off.

Tara Kizer: I imagine he’s working for a large company, and I would hope there’s a large DBA team for this amount of servers. The most I’ve had at a company was 700 servers, but we had five people at one point, I think at the lowest, and then eight people near when I left.

Brent Ozar: We got this blog post that I did a while back: How Many Servers Can One Person Manage? Where I talked about – I’m going to show a little graphic up here on the screen. If all you manage is hardware, so if you work for Facebook, Amazon, Microsoft, Google, whoever, if all you manage is hardware, you’re running through racks and data centers, one person can manage thousands of servers. When you layer on an OS, Windows, Linux, whatever, as soon as you layer on the OS and you’re responsible for installation, patching, security, configuration and requests from end users, one person can typically manage hundreds of boxes. When you layer on SQL Server, or really any database, one person can effectively manage 50 to 100 instances of SQL Server.

But you’ll notice, as you get to this layer you’re often giving up the previous layers. If you’re managing SQL Server and you want to manage, say 100, instances by yourself or per DBA, you’re probably giving up hardware, maybe even OS, to other teams, and then one person can effectively manage more instances. When you layer on high availability and disaster recover, clustering, replication, log shipping, always on availability groups, one person can really manage 10 to 50 instances of SQL Server. And then when you layer on performance, one person can effectively manage between one and five applications. This is where it gets a little weird. Sometimes, if you’re kCura Relativity DBA, one kCura relativity DBA may be able to manage performance across 50 SQL Servers, because they’re all running the same app. Performance tuning is exactly the same across all of them. But it just lays out a good idea of how many servers one person can manage. Sometimes when I see that, people will go, “Trust me, I manage more than that.” Well yeah, but you do a crappy job; I looked at your servers…

Tara Kizer: And Christopher followed up, they do have one DBA guy. Christopher’s not a DBA guy, he’s a developer. One DBA guy that also handles the hardware – he’s also the sys.admin. So I can’t even imagine what a hot mess these servers are in if there’s this many SQL Servers.

Brent Ozar: And imagine having a failover from one data center to another.

Tara Kizer: They don’t even have that in place – or it’s some kind of VM snapshot or something.

Updates just introduce more problems

Brent Ozar: Raul points out, “Updates sometimes just introduce more problems and complexity.” Yeah, I’d agree with that. I mean, a lot of times, you look at a lot of the cumulative updates, they’re fixing amazing stuff, don’t get me wrong, I’m really happy with what Microsoft’s doing out there with the product.

Richie Rump: That’s one of the things I like about sp_Blitz, is it will tell you if you’re running a dangerous build. If you apply one of those patches or whatever and it has a problem, running sp_Blitz will tell you you’re running something that’s bad; you probably should upgrade.

Brent Ozar: We even warn you if you’re on 2016 and you’re not on Service Pack 1, if you’re in Standard Edition, we’re like, “Yo, dog, get up to 2016 Service Pack 1, it’s amazing.”

Should we be in simple or full recovery model?

Brent Ozar: Steve asks, “We back up databases and logs every hour. Is there any reason for us to be in full recovery model?”

Tara Kizer: Is this the one that was in simple recovery model, same one? No?

Brent Ozar: No, it’s a different guy.

Tara Kizer: I mean, if you’re doing hourly log backups you need to be in bulk-logged or full recovery model in order to do that. You can’t do log backups in simple, so which recovery model you’re going to pick depends upon the business. It’s not for us to decide. I mean, does the business require you to do point in time recovery to, say, data loss RPO goal of say five minutes. You need full recovery model. I don’t even like bulk-logged recovery model on critical databases. I mean, it reduces my recovery point, so why would I want that?

Brent Ozar: I know what it is, and I’ve been through certification tests and I’ve seen what the purpose of it is, I’ve just never actually used it. I know there’s somebody out there who makes good sense of it, but it just has never been …

Tara threw out there the letters RPO and RTO; we’ve got a blog post on that – The Nine Letters That Get DBAs Fired. So we talk about what RPO and RTO is, we talk about how to CYA using our disaster recovery spreadsheet, where you check out what your RPO and RTO are and hand that to the business to that everybody’s on the same page. That’s our post, The Nine Letters That Get DBAs Fired.

Tara Kizer: My client this week, as well as last week, their RPO and RTO goals have been one day across the board for all eight boxes. It’s like wahoo, it’s so easy. [crosstalk] At five minutes it gets a little challenging. How much money do you have?

Brent Ozar: Everybody, usually when we show them that spreadsheet is like, “I don’t want to lose data and I don’t ever want to go down.” What’s your budget? “$5000.”

What’s the right HA/DR technology for me?

Brent Ozar: Raul asks, “If your organization has the means and budget, their RTO, recovery time objective, is 45 minutes. Would you go for AGs or mirroring?” I know what Tara’s answer is.

Tara Kizer: Well yes, I’d go with availability groups, just because it has more flexibility and is not deprecated. But as far as AG or mirroring, I don’t care one way or the other for the specific question. I want AGs if I want multiple replicas, because with mirroring you only get one mirror, and you have to pick asynchronous or synchronous; you can’t have both. So availability groups give me HA and DR and the option to have reporting as well. So, either one is fine. As far as a 45-minute RTO goal, that’s fine. [crosstalk]

Just go for log shipping at that point. I’m a fan of log shipping, but it’s harder to failover. So if your organization is going to be testing failovers, that is why we moved away from it at my organization. We moved to mirroring and availability groups because we did failover during disaster recovery sites and ran production out of there on purpose. And so we were trying to reduce our downtime for these failovers, and log shipping’s a lot harder to do.

Brent Ozar: It is. In that HA and DR worksheet that’s – on The Nine Letters That Get DBAs Fired, it’s got this three-part worksheet and down the left-hand side you find how much data you’re willing to lose, and then across the top is how long you’re willing to be down for. 45 minutes, you’re basically in the one-hour category, and if you go down through there, the methods are very different depending on how much data you’re willing to lose. If they’re not willing to lose any then it’s kind of tougher. You’re really down to either synchronous AGs or synchronous mirroring, and even those have some gotchas there. But if you’re willing to lose, say an hour, maybe if you’re willing to be down 45 minutes, maybe you’re willing to lose half an hour, an hour’s worth of data, log shipping can be pretty cool.

You mentioned that failover has to be automatic and seamless to the clients. There’s a thing called a DNS CNAME that you can use to point around the server from one place to another; it’s just way more work than availability groups. I say way more work, that’s probably a stretch.

Tara Kizer: Also, client redirection needs to transparent, that means on failover the user can keep working and doesn’t see any interruption, and neither availability groups nor mirroring give you that, or anything really.

Brent Ozar: I read that as like we can’t change the client app, but it’s going to be down for 45 minutes. It is not going to be transparent, they’re going to see right through your 45-minute delay.

Brent Ozar: Raul says – oh come on Raul… He follows up with, “Log shipping requires a lot of DBA intervention plus failover testing.”

Tara Kizer: So do the others.

Brent Ozar: If you’re saying that you think you don’t need testing for AGs or mirroring, think again, they are kind of tricky. Alright, well that’s actually all the questions that we have, folks. Y’all got done in 16 minutes. I’ll give you guys another minute or two to throw out any questions you’ve got there, otherwise, we’ll let you go early to lunch today.

Richie Rump: I realize that I have an ampersand on my shirt and so do you. It’s weird. It’s like ampersand day.

Tara Kizer: I do not.

Richie Rump: You didn’t read the memo.

Brent Ozar: Tara’s the only one in the group with taste.

Tara Kizer: Actually, this is my pajama top, so I don’t know about that…

Brent Ozar: Somebody asked when I was in the Isle of Man last week, they were like, “Do you guys actually get ready for work and get dressed?” I was like, “Well we have a webcam and we work with clients on webcam, so we have to put shirts on at least.”

Tara Kizer: It was funny when we were out in Austin to work with Dell, the Quest people now, and we all dressed in whatever we wanted to wear and a lot of the people at Dell were a little bit fancier and Claudia one look at my attire and said, “I wish I could dress like that here.” I don’t know if she was insinuating…

Brent Ozar: I wish you could dress like that here too.

Is it possible to roll back sp_changepassword?

Brent Ozar: J.H. asks, “Is it possible to rollback sp_changepassword?” No, not that I know of.

Tara Kizer: Rollback if he wrote down the password somewhere, the old one.

Brent Ozar: Michael asks, “What’s a good way to filter bad data in a big ETL job? Someone fat-fingered a date of January the first of 2107 instead of 2017.”

Tara Kizer: Well, data validation, I guess, it’s probably part of your packaging.

Brent Ozar: Richie, do you still have that sp data profile?

Richie Rump: Yeah, I haven’t touched it in a while, but that won’t tell you – it will tell you the min and max of some stuff…

Brent Ozar: Just that you have wacko edge case data.

Richie Rump: Yeah.

Brent Ozar: Alright, well thanks everybody for hanging out this week and we will see you next week on Office Hours. Adios everybody.

Note: this is coming to you from the past due to technical difficulties.

This week, Brent and Erik discuss Availability Group issues, database diagram tools, AWS vs Azure, diagnosing async io waits, troubleshooting deadlocks, extended events, using same login credentials for everyone in the company, adjusting and 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 9-13-17

Why is my AG database log file growing?

Brent Ozar: Leah asks, “I need help; I am out of ideas…” Boy, are we the right people to ask. She says, “I have multiple availability groups using SQL Server 2016. Every database is in its own availability group. However, I have one database that has a full transaction log file and it’s growing. It says the log reuse wait description says availability replica, but all replicas show everything is healthy. What can I do, other than remove AGs or throwing the database in simple recovery model?”

Erik Darling: Kendra has a great post. Kendra, about why your availability group transaction log can be full without you having messed anything up.

Brent Ozar: Let’s see if we can find it. Let’s see, Kendra Little transaction log – is it on her site or ours?

Erik Darling: No, it’s on hers.

Brent Ozar: LittleKendra.com… For those of you listening to the podcast, you can’t see that we’re typing. Why is my transaction log growing in my availability group… Oh, she has an episode on it; fantastic, on her IDERA SQL DBA podcast. Oh, it’s nice. She doesn’t transcribe the whole thing, but you can either get it in video or in audio. She does a great job on the podcasts there. You should also totally subscribe to her podcast while you’re there too.

Off the top of my head, I can’t remember what would cause that. Obviously, a long-running transaction, but that would be different, it wouldn’t show availability group in the list there.

Erik Darling: Stuff like running out of disk space on the replica – like if your transaction log is full over there and it can’t grow anymore to receive new transactions, it’ll be stuck. And then – I had another good one – like if stuff gets – it can’t clear out row versioning or – no, that was tempdb…

Brent Ozer: I was thinking, if you added a file on an invalid drive letter, you know, where the secondary doesn’t have it, then the replication will stop working. I would also check and see, sometimes you think that things are healthy when you look at the availability group dashboard, but it’s not; because the availability group dashboard stops updating when you run into worker thread exhaustion. You might actually not be in sync over on the other side. If this is a Standard Edition AG, you’re not supposed to query the secondaries; you know, it’s not readable on the secondaries. If this is in Enterprise though, I would actually go in and look at the tables on the secondary, just to make sure the data’s actually up to date, because it may not be.

What’s a good database diagram tool?

Brent Ozar: James says, “I have a 1.2TB database and I need a database diagram. Do you recommend any good third-party tools?”

Erik Darling: Oh, this is a Richie question, because Richie actually knows several of these. I can’t remember any off the top of my head that I like.

Brent Ozar: I hear people say Erwin, ER/Studio, Toad Data Modeler. Those are the three that I hear the most often. I don’t know anything about pros and cons to any of them, but I know they’re expensive; usually like three, four, five thousand bucks.

Erik Darling: I mean, James, not to punt again, if you search back through our episodes about database diagrams, there’s specifically one where Richie talked about several different that he had used over the years that he had various experiences with. So, I would search back through Office Hours episodes and see if you can find searching for database diagrams because Richie had a great answer about them.

Brent Ozar: Or – so this isn’t a good one to post on Stack Exchange, because they’re like never post-shopping list questions; but the person I’d ask is Lewis Davidson. Lewis Davidson has a blog over on SQLBlog.com. If you search for SQLBlog Lewis Davidson, I bet his contact info is over there; and he wrote the book on relational data modeling for SQL Server. Nice guy, has an unbelievable sense of humor. Super dry, like he’s talking and you don’t even realize the number of jokes he’s dropping in there.

Should I use AWS or Azure?

Brent Ozar: Next up, Brent – not me but the other Brent; I shouldn’t use last names – says his, “Company has mandated that we’re moving to the cloud. Now they’re counting on me to come up with which service to use; AWS or Azure. What should I consider?” Oh, that’s good.

So, you have to work with all of them because we go through and do things like builder days. What do you think about – if you were going to go pick one, what were the kinds of things you’d think about?

Erik Darling: Oh, which sales person can fight the best. No, but I think what you should do and the way you should decide which cloud service you want to pick is you should go to the Azure portal and try to figure it out.

Brent Ozar: That is just mean.

Erik Darling; I know, but the good thing is you get a quick answer out of that.

Brent Ozar: Yeah, come up with a list of like four or five things that you need to do; spin up a new VM, take a snapshot backup of it – I’m trying to think of the other common things that I would do. Networking security, holy cow. Spin up two VMs in two different areas of the US, or internationally, wherever you want to do it. One on the East Coast, one on the West, and then get them to talk to each other, ideally without putting them both on the internet. Holy cow, that’s a pain in the rear…

Erik Darling: I hope you like subnets.

Brent Ozar: And I don’t want to sound like I’m shilling for anybody, but this is one of the things actually that Google does really well. Any of your VMs can talk to any of your other VMs, regardless of what data center they’re in. You don’t have to deal with any of the complex VPN stuff be default. The other thing I would ask of your developers is, are there any services that are ion one cloud that you’re thinking about using that are not in other clouds?

So, for example, Microsoft will try to pull you in with stuff like Azure Data Warehouse or Azure Data Lakes that are proprietary to them. Amazon will try to pull you in with things like Redshift, S3, you know, services that they use that other people don’t. Google will try to pull you in with their Kubernetes type stuff, but just ask with your developers to see which ones they use.

Erik Darling: Another thing I’d want to do is try to find instance sizes that are sort of commensurate to the machines that I have running now and check out pricing there and see how – just see what’s available that you could probably get a close match on. Because one thing that you’re going to find in the cloud – Brent talked about networking, I’m just going to talk about networking speed. For the instance sizes you want, it might make sense for the – they might be a close match to the kind of machines you’re running now. The networking and the disks that you get might not be all that hot.

Brent Ozar: When you go through and look at pricing in comparison too, I would start here; EC2instances.info. Now this is only for Amazon, obviously. There’s similar kinds of sites for Google and Azure. I think it’s easier to find pricing for either Google or Azure. For example, with Google, there’s no predefined machine types that really hone you in. you get a lot more knobs and buttons that you can play with for sizing. But in here, I love this EC2 instances comparison, where you can choose what kind of licensing you want, whether or not you’re worried about how many compute units you get, whether or not you’re doing IPB6; all kinds of fun stuff. And then you can sort by that. So you can say, I want VMs with at least 60GB of RAM and at least eight cores, and then I can sort by pricing; how much is Windows pricing on demand? All kinds of neat stuff.

Erik Darling: But what’s really important to pay attention to is that network performance column, because not all of those are so great. So like up to 10GB, high, doesn’t even do all that high. And then you have moderate and low… Be careful.

Brent Ozar: They really are slow. They are desperately slow; same thing with storage throughput. If you go to columns and, say, EBS optimized throughput, you can see a lot of these are just dog slow. If you run CrystalDiskMark and compare it to some of these, man, your data would be better off on a USB thumb drive.

Erik Darling: If you need really large sizes and you’re deciding to go with Azure then you might want to hold out for Azure Managed Instances, because those are pretty beefy sizes and…

Brent Ozar: It’s wonderful; so Azure SQLDB, it doesn’t sound like you’re interested in, but Azure Managed Instances – so what are the kinds of things that would draw you to managed instances that SQLDB doesn’t have?

Erik Darling: Just size and some of the AG stuff I guess.

Brent Ozar: Cross database queries is a big one.

Where can I see past GroupBy sessions?

Brent Ozar: Kevin asks, “I couldn’t watch all of the GroupBy sessions, where can I see them now, please?” I like that he asked please at the end of it. Go to GroupBy.org and there’s a big thing up at the top that says “Watch past sessions”. That’s exactly where you should click. All totally free too.

Should I have different SIDs in development?

Brent Ozar: Amy says, “Is it okay to have the same login SID for databases that are on different SQL Servers; production, backup, test and dev?” Oh wow, I never thought about that.

Erik Darling: Me either…

Brent Ozar: Yeah, it’ll be fine. After all, you’re going to probably restore – oh god, I’m going to get in so much trouble for saying this – what you’re probably going to do in many environments is you’re going to restore from production over to other places like test and dev. And then you get stabbed by some security auditor who goes, “Developers should never have production data.” This is the way the world works.

Erik darling: It’s the agile way.

Brent Ozar: Amy says, “Yes I am, thank you.” I figured someone would stab me for that.

How should I diagnose ASYNC_NETWORK_IO?

Brent Ozar: Samuel asks, “What are the next steps in diagnosing async network I/O waits when I’ve confirmed that networking is not the issue?”

Erik Darling: Doesn’t it sound like it should be the network?

Brent Ozar: Yes – so many [crosstalk]

Erik Darling: Oh, that’s good. I would start with checking your SQL Server code and see if you’re running any scalar functions or cursors in there that might be sending results to your application one row at a time. The next thing I would check is your actual application code and see if they’re doing any processing on a dataset that comes in, in like a four-reach loop; where they’ll take a row and do some processing and then another row and do some processing. Because typically what async network I/O is, is when SQL is basically shooting data at a client and the client’s not saying that it has all the data because it’s doing a bunch of stuff client side.

Brent Ozar: You could run sp_whoisactive. Go run sp_whoisactive and look at that wait stat column. In there, it will show which queries are waiting for async network I/O; and then you can go back to those developers too. It shows you – I love sp_whoisactive so much. It shows you which machine it’s coming from, all kinds of fun stuff.

Should I hint my isolation level?

Brent Ozar: Next up, J.H. says, “A developer was experiencing deadlocks and I recommended to do their changes, inserts updates and deletes, in smaller batches. Now that did turn out helping, but we’re still experiencing some. Do you recommend locking isolation hints?”

Erik Darling:  Not necessarily.

Brent Ozar: What would you start with first if you were facing deadlocks?

Erik Darling: Well, I think it would depend on where the deadlocks are coming from, a bit. Usually if it’s a couple of stored procs, I would go and look at those, and I would maybe look for missing index requests, and I would maybe look at smarter ways to index for updates. Because a lot of – a reason that I come across that happens quite a bit for when deadlocks occur is that there’s an update or a delete. And there’s a where clause for it, but the where clause doesn’t have an appropriate index. So, SQL ends up escalating all sorts of locks from row to page locks or to table locks, and you end up with these kinds of strict locks that get held for the duration of the modification, and then anything else trying to do work around it kind of gets bonked. So, I would just make sure, for my modification queries, updates and deletes, there’s a where clause that I’m kind of appropriately indexed for that.

Brent Ozar: I’d also say too, make sure that you don’t have too many indexes. A shop I was at yesterday had 60 to 70 indexes per table. And so whenever someone wanted to change data, we were having to grab locks across all kinds of indexes because every index included all kinds of fields from the table, fields that changed all the time; so you had to do way more locks than necessary. It usually seems like – I often joke with people that you should aim for five or less indexes per table and five or less fields per index. It’s not that that’s a magic number, it’s just that the more indexes you start to have, the more problems you end up with; stuff like blocking and deadlocks. And the less indexes that you have, the more you run into that problem too.

Erik Darling: So, one really good and quick way to get yourself a diagnosis on this is during or after – probably after one of those deadlocking things occurs, run sp_BlitzIndex and see if you get warnings for aggressive indexes. Aggressive indexes will point out indexes that have had significant row or page locks and how long they’ve had them for. So, you can figure out which indexes have these long locks being held on them and which ones might be the reason for the deadlocks occurring.

Brent Ozar: I remember the first time Kendra showed me that you could get locking stuff out of the index DMVs. And I was just like, witchcraft, how is this even possible? I used to try and focus too much on what are the queries involved in the blocking and the deadlocking, but man, you don’t even need it once you know which indexes are involved. Then it’s easier to look at the tables and see, how’s our indexing strategy on those?

Can you show me Extended Events?

(Note: you’ll need to watch the YouTube recording to see the demo in action.)

Brent Ozar: Don says, “Good morning. I tend to use SQL Profiler to do an audit on queries or find the queries that are getting called from a web page. The developers won’t tell me and I’ve got to try to figure it out. With SQL Profiler being deprecated, is there a specific extended event that I can use to do this? Or how should I use the new way?”

Erik Darling: Don, I have such good news for you. Assuming that you’re on a fairly modern version of SQL Server and using a fairly modern version of SSMS, when you go into extended events and you go into sessions – when you right click on sessions and hit new session, one of your options under the general tab is you can use a template. And there are a whole bunch of templates in there that are equivalent to Profiler. So, whatever you would normally fire up for Profiler, you can probably find a pretty close match for Profiler equivalence. So, you can do anything that you have in there, and then it gives you the definition of the extended events session once you go onto other parts of it; and you can see which events are tracked. And so, you could even narrow stuff down and pull stuff out and concentrate on stuff that you’re familiar with in Profiler.

So that’s what I would recommend. If you’re using Profiler to do something specific now like that, start with the templates there because they make your life a lot easier. You can get a lot of commensurate information up front from across those.

Brent Ozar: Session name – I’m like why can’t I do this demo? And then you can hit the script up thing…

Erik Darling: Don’t be afraid to hit the configure button and kind of poke through, because there’s going to be a whole bunch of extra columns and filters and stuff that you might want to – I have a car being stolen behind me – but there’s all sorts of stuff that you might want to pull out or add into this session that you saw on Profiler that you’re not seeing just off the bat in the [inaudible].

Brent Ozar: I mean, watched live data isn’t always completely accurate, it doesn’t show things exactly when it comes in…

Erik Darling: Sometimes it crashes management studio.

Brent Ozar: So, there’s a lot of gotchas with it. It just is nowhere near as bad as it used to be. The other thing I’ll say too is, if you don’t want to learn it, you don’t have to. Like you can still continue to use Profiler. It’s heavier, it isn’t nearly as cool and won’t gather all kinds of cool stuff; but it still works if you only need to use it part time, like once a month or once a quarter, it still works fine, and it’s still in SQL Server 2017.

Erik Darling: Go back to management studio real quick.

Brent Ozar: Oh man, you’re not my real dad.

Erik Darling: Come on, we’ve got so much time to kill. So, if you want to get results and watch live data or end the data file faster, just turn max disk patch latency down to a lower number. Right now, it’s at 30 seconds, so it could take 30 seconds for stuff to go in. you could change that to one or two seconds, which is what I do sometimes in demos to get the data into the file faster; which isn’t necessarily a good idea on a production server, but if you just want to see the data faster, that’s a knob you can tune to see it faster.

Brent Ozar: There’s also, as long as we’re going through this, allow event loss. So, you might look, as you’re going through the wizard, oh I shouldn’t allow any loss of events whatsoever; no, don’t do that. Let SQL Server drop some events because it’s not mission critical and it will be faster that way.

Everybody knows the SA password. What do I do?

Brent Ozar: M.M. says – I assume that’s not Eminem. Times are hard in the rap business…

Erik Darling: I heard that new album, man…

Brent Ozar: He goes, alright I give up, I’m going to go into databases instead. “The company I’m working for uses the exact same ID and password for…” Oh, now I see why you went anonymous with your name. “Uses the exact same ID and password for the SQL proxy, command shell credential, owner for all jobs. All the developers know the password, my developers work in production. They log in SSMS as that ID in order to create jobs. I think this is bad and I want to change it. Is it okay for the proxy and credential to be the same?” Man, that would be the least of my worries.

Erik Darling: As long as all of management is aware of it and they are okay with it, I mean – maybe let’s not bark up that tree. If you wanted to, you know, write them a nice email, maybe have a brown bag meeting where you talk about all the potential security problems that presents. Being a DBA and being a developer and handling data like this is a trust position. So, if you don’t necessarily trust everyone accessing the environment, you may not want them to have access to all of your customer data or whatever data it is coming in. If it’s medical data especially, that could be rough. But I would make sure that everyone is aware of it and aware of the potential downsides of it. One thing that I will caution against though is shooting off the handle and changing a password, because you could make a bunch of stuff break like that.

Brent Ozar: One thing I’ll throw in there, I have up on the screen this old blog post I wrote – I say old, I didn’t realize it was only a year and a half ago. How to talk people out of the SA account.  I give a graceful way to do it and then in option two I give the really ugly way to do it. It involves, after all, if everyone knows the SA password, or whatever password, you could go in and change things in the production database and play stupid. “I don’t know who dropped that table, it could have been anybody. Man, it’s a shame we can’t figure it out, but with all you knowing the password, I guess there’s just no way we’ll ever know.” “Well you’ve got to stop people from doing that.” “I can’t, my hands are tied. I’ll see you again next week. I predict we’ll have an outage on Thursday at two.”

Erik Darling: All your queries are going to run really funny in about five minutes. You know what he could do? That stored procedure that we wrote at the last DBA Days, Chaos Slop. Change his settings.

Brent Ozar: Oh god, that was funny. Describe what that does.

Erik Darling: So, the nice way to put it is that at random it will – if you schedule it as an agent job, every time it runs it will assign random values to a whole bunch of different, important to performance, variables. Like max memory, cost threshold for parallelism, MAXDOP – so you could just get wild variances in execution plans and, you know, querying a plan cache. All sorts of bad stuff happens when you start changing that stuff on the fly. And Brent left a process running overnight while Chaos Slow was also running. So just the wait stats were awful, it felt like me on the cruise ship where I was just… It was inspired by the Amazon Chaos Monkey thing that randomly goes and breaks things.

Brent Ozar: It’s amazing Dell never let us into their data center. That was pretty spectacular.

What’s your favorite data visualization tool?

Brent Ozar: Wes asks, “Have you ever used any data visualization tools and if so what are your favorites?”

Erik Darling: Excel…

Brent Ozar: That’s exactly what I was going to say. It’s easy, it’s cheap and it produces results that I can manage. But I don’t know anything about visualization beyond that though. And if you’ve ever seen any of my reports, you’ll understand why. “You can see by this 3D pie chart filled with Pac Men…

How do I fail over to DR with AGs?

Brent Ozar: Sri says, “In a multi-site Always On Availability Group…” I like how you spelled it out absolutely perfectly there, SRI, you’re already ahead of most shops. “I have an even number of nodes. I’ve got four nodes and I’ve got a quorum file share in my primary data center. What happens when my primary data center dies? Like if I have a flood, a disaster? Will we be able to manually bring up our DR site?”

Erik Darling: take it away, Brent.

Brent Ozar: Alright, so assuming – it’s easier almost, with the camera with my number of fingers. So, I have two nodes in each data center and then I also have the extra file share witness over in my primary data center. When you lose the majority of voters all at once, the two that are still remaining over in the DR data center, they don’t know that they aren’t just left alone in the cold. Like maybe their internet connection died and they can’t see the majority; so, they will gracefully shut down their cluster services. Now, you can force them online. There’s a set of steps that are documented in Books Online about forcing the cluster online, fixing quorum, then forcing SQL Server to start the AG with allowed data losses on. But this is super dangerous and the checklist to do this is like a page and a half long in Books Online, and every one of the lines links off to other articles in Books Online. It is something that you want to rehearse the Bajeesus out of before you go and do it the first time.

The thing that really worried me when folks do this is, whenever you failover to that other data center, when you decide to pull the trigger, there is no easy way to get the data back from that other data center. The simple way to think about it is order IDs. Say you’ve got a table with an identity field, starts at one and goes up to a bajillion. In your primary data center, y9ou had order ID number 100, and that one hadn’t made it over to DR yet. Well, DR was only up at, say, order ID 90. When we failover and we start inserting into the orders table, we’re going to be using that order ID 100 again, and I have to be able to figure out are these two different orders or are they the same order where fields have been changed? Tools like Redgate’s data compare and schema compare will help you identify that there’s differences, but not which one is right or whether it’s two separate rows or one row. So, it’s super, super dangerous. It’s something that you actually want to step through as a company before you go live and understand what the ramifications are.

Erik Darling: See that was a much better answer than what I was going to give. I was going to say maybe you should think about log shipping.

Brent Ozar: Yeah, so much easier. Because you lose the primary data center, the first think you have to do when you go to failover to DR is fix the cluster. Before you can even get started you have to fix the cluster to bring that online. With log shipping, who cares? You just go live over on the other side.

Erik Darling: This is part of the reason why I have such a hard time finding HA and DR stuff sexy, is because it’s like nope, everything is lost. It’s like a dude saying he’s not wearing any underwear; you’re like yeah, who cares?

Brent Ozar: It’s amazing how I look at it and I go, if you do it wrong, it’s way less reliable instead of more reliable. It’s very easy to get less reliable. Now either I just lost internet or Erik did, one of the two. Let me see – yeah, I’m still pulling up internet pages, so it must just be Erik’s frozen in time.

Let’s see, SRI says thank you and Vlad says you are visible. Okay, cool.

How do I catch people using the DAC?

Brent Ozar: So Samuel says, “I have an issue of a process logging into my SQL Server using the DAC overnight, the dedicated admin connection. Can I run a trace, like an extended events or profiler query to capture this user? You actually don’t need to run a trace or an extended events type thing. The easy way to do it is, just log sp_whoisactive to a table. If you Google for – we’ll go ahead and do it real quick on ours – if you search for log sp_whoisactive to table, Tara Kizer has a great post explaining how you go about doing this. there’s two on here. Part two is the better one that goes – I say better one, it’s just newer. We go into more details there. So, you can set this up as a job to run every five minutes, every ten minutes, however often that you want. Then there’s also a parameter that you may want to play around with, show sleeping SPIDs, I believe it is.

Let’s go fire it open just to see if I’ve got this right – sp_whoisactive. Show sleeping SPIDs I think, equals one. Look at that, I even remember. So, this will catch even if someone just logs into the DAC and doesn’t do anything. If you wanted to get more specific about which query is which, like which query is using the DAC, you could go into more detail through things like sys.dm_exec_sessions. But I would just do this because it’s a great technique to understand how to use for all kinds of reasons. I know a lot of shops – a lot is probably a stretch. I know some shops who will set up a job to log sp_whoisactive to a table, they’ll set up an agent job to do it and schedule, like every minute. And they’ll only enable it when they know they’re going to have or they’re having problems; they’ll teach their helpdesk staff to go enable that job.

So, since you know roughly when that’s happening, you could simply schedule the job to run during those windows, and you’ll be able to catch the culprit pretty quickly.

Are there drawbacks to changing MAXDOP?

Brent Ozar: Finally, the last question we’ll do today, J.H. says, “I recall seeing a YouTube clip of Grant Fritchey saying maybe not always change the MAXDOP is the default. Is there a time when it changes?” Yes, absolutely. So first off I’m going to throw a link up here on the page so you all can see it. If you go to BrentOzar.com/go/cxpacket – and I’ll copy paste that so I can paste that in again whenever the page refreshes… I’ve got a video on there where I explain what’s going on with MAXDOP, what’s going on with cost threshold. And I haven’t seen that clip from Grant, but I can tell you, because I can probably agree with it, there are probably cases where you don’t want to throttle down how many cores your query gets. However, the gotcha with that is it’s usually talking about fairly small servers. So, think like eight to 12 cores as opposed to 16, 32, 64 cores. it’s extremely rare that on a 16-core server or a 32-core server that you actually want one query going parallel across all of those threads. Because remember, it’s not like just one query is going to go parallel. Odds are you’re going to have a bunch of queries going parallel and it can cause a problem.

The typical scenarios where you want to go with a larger MAXDOP are things like data warehouses, where you have a really repeatable load every day and then you have repeatable nightly scheduled reports that come in and do big huge awful ugly queries. What’s cool about data warehouses is you can change your MAXDOP over time, say run experiments from one week to the next to figure out where your sweet spot on MAXDOP is. This is one of the experiments we ran at Dell DBA Days. Both Erik and Doug had run experiments on this. One of them was an experiment on CHECKDB MAXDOP, for example, where we found that the sweet spot for some workloads or some databases was closer to 14 or 18 in terms of MAXDOP.

Alright, well that sums up today’s office hours. Thanks everybody for hanging out. If you have a question that we didn’t get to, feel free to go over to DBA.StackExchange.com. We’re over there all the time posting answers on that, and we’ll see you guys over there. See you all next week at Office Hours.

This week, Richie and Erik discuss altering database columns, separating log files, encryption, NOLOCK issues, developing new skills and experience, troubleshooting deadlocks, Oracle, calling C# method from a stored procedure and the type of headset Erik uses.

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 9-27-2017

I need to update a 100GB table…

Erik Darling: The first one is from a fellow with the atomic symbol for boron in his name, Jorgan. And his question is, “I have a table with the size of 100GB…” My goodness, very nearly a lady. “In 100,00 rows – I need to alter one column from .2110 to .2410. Do you have an idea to minimize table lock time?” Well usually I stay away from altering a column like that. Usually what I do is add a column that’s nullable and then I update that column in chunks, so that I don’t have to deal with altering an entire column.

I don’t know what version of SQL Server you’re on, but there are some sort of enhancements when changing things like that about columns. But that’s what I would do, then you could use sp_rename to flip the columns around. And that’s a pretty simple metadata change, so that’s probably what I’d stick with for that.

Richie Rump: Yup, that’s what I do.

Erik Darling: As long as that column isn’t like the primary key of a clustered index or something, it’s a fairly easy switch. You may need to rearrange your indexes a little bit though.

Should I put TempDB data and logs on separate drives?

Erik Darling: “When installing SQL Server 2016…” Welcome to almost the past. October 2nd, SQL Server 17 comes out, so J.H. I don’t know why you’re installing this old and busted version of SQL Server when the future awaits; the future is out there. “When installing SQL 2016 on a VM, is it still good practice to put tempdb data and log files on separate letter disk drives? Since it sounds like all the letters are coming from the same underlying storage, is performance better?” No. Brent, has a great post Brent, about how separating log files doesn’t help you with anything. More importantly, on a SAN, you’re right, it’s all the same underlying pool.

Now, there are some, kind of, manageability reasons to do it. So like, if you have tempdb data and logs all on one drive letter, then one thing kind of grows out of control, you could affect everything else. So if tempdb blows up and then your data file blows up, well if your data file wants to grow after that, it’s not going to be able to grow. You fill up the drive, you’re kind of stuck. It’s no fun, right. So there are manageability reasons for separating them. Like you put tempdb on a drive that’s 100GB, you put your data files on another drive that’s say 500GB, put your log files on another drive of 250GB or something; if any one of those databases or files grows up and blows things out then you don’t have everything impacted. You just kind of separate out the impact. You minimize the impact across the board.

So for performance, no, you get nothing out of that, but there are some manageability perspectives that make sense. What do you think, Richie?

Richie Rump: I think if you run sp_Blitz and you have them all on the same drive letter, it’s going to flag you for it.

Erik Darling: Especially if you’re backing up to the same drive.

Richie Rump: And if you have tempdb on the same drive.

Erik Darling: Also, it will complain if you have anything on the C: drive. Why would you do that to yourself?

Richie Rump: Let the OS have its OS stuff and have SQL…

Erik Darling: [crosstalk] Leave the OS alone with its stupid page file. Dreadful. I wish I could say dreadful the same way Gordon Ramsay says dreadful. Every time I watch Masterchef and he says something is dreadful, I’m like, “Oh it’s so good.” Then when I say dreadful it’s just like…

Richie Rump: “I’m an idiot sandwich.”

Can I change certificates with Always Encrypted?

Erik Darling: Alright, let’s see here. David asks, “With Always Encrypted, is it possible to change the location of the certificate without decrypting and re-encrypting the columns? I’d like to change it from current user to local computer.” Gosh, I don’t know. I haven’t used Always Encrypted all that much and I have not ever tried to move a certificate in that way. So, I would head over to DBA.StackExchange.com and ask, because I’m sure someone over there – there are a bunch of folks who work for Microsoft who answer questions on that site, and I’m sure there are some folks who have even used Always Encrypted that could answer your question better than I ever could, because security is not really my forte. There’s no query plan for certificates, so I kind of suck at that.

What’s Richie doing this week?

Richie Rump: Actually, encryption is one of my things I like to mess with, and I haven’t even played with it yet. Actually, this week and a little bit last week I’ve been messing with 2016 SQL Server. It’s weird for me, I’m actually on SQL Server this week; it’s strange.

Erik Darling: How do you feel about that?

Richie Rump: I feel okay. I like SSMS; it’s better than pgAdmin. How’s that?

Erik Darling: pgAdmin is miserable.

Richie Rump: It’s bad. No, I used to write SQL functions inside of Postgres, but now I’m running Postgres functions in SQL, so now I’m all sort of confused.

Erik Darling: That’s a snap back to reality, huh? One thing, whenever I’ve used Postgres in the past that I always missed was the ability to get the graphical Showplan. Getting the explain analogs thing was just like, what? I’m like awful at it. Reading through, I’m like…

Richie Rump: It was the same thing with Teradata, when I had to do an explain plan through Teradata, I’m like, “Oh god no.”

Erik Darling: I mean, that’s what it used to look like for SQL Server; like you’d do set statistics profile on and you get explain plan with weird stuff spread all over the place. I still like it a little bit because it shows right up front the estimates first, actual in the rows. So, it’s really great to spot bad cardinality estimates and stuff, but other than that, I get lost in…

Richie Rump: That’s only because you’ve gotten so good at reading XML, that’s why.

Erik Darling: That’s true. XML is like – I can actually read XML like regular human writing.

Richie Rump: Which is so weird. It’s like, “Move on to JSON like the rest of us, Buddy. Come on now.”

Erik Darling: Someday, someday I’ll get there. As soon as query plans are in JSON, I’ll be there.

Richie Rump: That would be cool.

Has NOLOCK changed recently?

Erik Darling: So, M.M. asks, “We are migrating from 2008 R2 to 2014…” Why? Why are you living in the past, man?

Richie Rump: Jumping point, right? They’re going to go 2016 or 17 soon?

Erik Darling: Maybe, I don’t know. I never trust it when people say that. I’m always like, you just want to give me a little Band-Aid so I stop griping too much. “Our developers have NOLOCK everywhere in dozens of jobs, not my choice. Are there any breaking changes with NOLOCK?” Well, there was a cumulative update that broke NOLOCK, where even with a NOLOCK hint, it would not observe the locks that other queries took out. But that’s been fixed, so as long as you’re on 2014 with whatever the latest service pack and CU is, you’ll be fine. I’ll leave it up to you to work on getting the NOLOCKs out of there, unless no one cares…

Richie Rump: Yeah, you need to get thee NOLOCKs out of there.

Erik Darling: Unless no one cares… If everyone’s like “Cool, fine, do whatever, I don’t want to deal with optimistic isolation levels,” then NOLOCK your… [crosstalk]

Richie Rump: I’ve used NOLOCK, but it was on a system that – there were no updates on it, so who cares.

Erik Darling: So why did you put NOLOCK on there?

Richie Rump: Because we were having locking on reading issues.

Erik Darling: Really?

Richie Rump: Yeah man, that was a 60TB thing, man. A 60TB database, man, there was crazy stuff going on all over that thing. It was fun. I mean, it’s fun to see all the weird stuff that goes on when you’re dealing with that size of data.

Erik Darling: Seriously, I mean my previous limit was somewhere around 10TB, so I would love to see what happens at 60TB. I would love to see what a – all the stuff, like statistics time and I/O would break on that. [inaudible] over big int pages.

Richie Rump: Yeah, there was a lot of weird things that went on, and it was like a batch loading type schedule, but no updates. So there was some stomping going on, especially if you were in the same partition and all the other fun stuff that you have to deal with.

Erik Darling: It’s funny, like big servers and big data stuff like that, it’s like quantum mechanics or something. Like getting real close to a black hole and just watching what stuff does.

What should we do about our badly formatted disks?

Erik Darling: Alright, we should probably answer a question at this point. Frederick asks, “I just found that a previous DBA was not careful with the virtual disk volumes. All disks were left at the default 4K block size our infrastructure team gave us. Should we worry about changing that for servers that have been in production already for a year or two?” Dude, you having problems? Are you having I/O issues? Is there something about those drives that’s not working correctly? The 64K block size thing was, I guess, a bit more important with direct attached storage. Since you’re talking about virtual disks and VMs, well, you’re probably sitting on a SAN anyway, and there’s so much junk and obfuscation going on between what you consider a disk and what the SAN considers a disk, that it doesn’t matter anymore.

Usually, when you see that kind of stuff, that’s because that is what the SAN vendor recommended. So what I’d want to do is check with whatever SAN vendor documentation, maybe open a ticket with them to make find out what best practices are. Maybe I’m wrong. Maybe in the forward going future you should set the block size to 64K, but most block sizes I’ve seen with SAN vendors, they’re like, “No just make up the small ones; we make our IOPS numbers look better with that.”

(Edit: to clarify, there can be a couple of different issues between block sizes and NTFS allocation units, but the basic idea here is that tweaking this doesn’t usually get you across the finish line. With either one, the amount of work required versus the payoff is rarely worth it once the server’s in production.)

How should I build my AG skills?

Erik Darling: Grahame asks – or rather Grahame states rather prolifically, “I want to build my skills with AGs and failover clusters but my organization doesn’t want to implement them. I’ve done proof of concepts in a test environment, but I want to get real world skills with AGs, how should I go about this? Offer free DBA services.” You know, a good DBA ain’t cheap and a cheap DBA ain’t good. So, don’t offer your services for free, because no one will want to deal with that.

What I would probably do is start looking for a new job where someone’s looking for those skills. If that’s what you’re interested in and your current job isn’t going to go for it, well it’s time to move on. You have to follow what you’re interested in. This is what happened to me back a couple of jobs ago when I was working for a market research place. I was doing SQL and I was doing ETL and I was like writing reports and I was doing all sorts of crazy stuff with it, but at the end of the day, I was only like DBA-light. I wasn’t getting to do all the things that I wanted to do.

I wasn’t getting to implement things that I wanted to implement and it was really difficult for me to argue structural and procedural changes with people because I was not that, and they didn’t need that. They had a bunch of sys admins who handled that stuff. So that’s when I started looking for a different job, then I started working for an e-discovery shop that needed a DBA, and I started doing all sorts of DBA things that I was very happy doing and learning about. Getting to work with failover clusters, getting to set up mirroring and log shipping and all sorts of other stuff. Unfortunately, no AGs because – well, I wouldn’t say unfortunately no AGs, because I was the only DBA, that would have been a nightmare.

But if your current job has no interest in letting you pursue or pursuing the technologies you’re interested in driving that, then it’s just time to move on. It’s as simple as that. I’m sure you have some emotional attachment or whatever, but it’s time to go, man.

Richie Rump: Well the other thing that I’d add in too is, as much as you may not want to hear this, there are a lot of speakers out there that don’t actually do the technology they teach. They do a lot of training on it and they do a lot of messing around in labs and doing all that stuff, and they don’t get into the real world-y type stuff. And I think that’s perfectly okay, because there’s actually things that you learn on the fringes when you start poking at the machine and doing different type scenarios on it. You are really seeing how things work, as opposed to if you were in a real job, you probably wouldn’t be pushing the edge on some of these technologies and seeing where the boundaries are and what happens in certain conditions.

So, if you’re interested in that, why don’t you go put an hour presentation together and submit it to your local SQL Saturday, or give it to your local user group. I started out doing a lot of that kind of stuff when I wasn’t happy with my gig and I was the manager and doing architecture and a whole bunch of stuff that I didn’t want to do and I wanted to do more technical stuff. That’s how I got my technology chops back, going out, doing demos at home, putting the presentation together and then sharing them with other people and then having conversations with them afterwards. What was good, what was bad, how does this really work for you and doing those kind of fun stuff.

Erik Darling: Because there’s stuff that you can’t talk about when you’ve only worked with something in a lab. Like it’s really hard for you to talk about best practices and like setup for DBAs, because working in a lab is totally different from working out in the real world, aside from you’re using the same technologies and the same command; but it’s usually totally different.

What I would do, if I were you, because not many people do this, is I would talk about what happens when things go wrong with AGs. Like what happens when you start unplugging things, turning things off, when will things gracefully fail over? Do demos like that because that’s engaging interesting stuff. No one wants to see another presentation of you walking through a wizard or running a script to set things up and then being like “Hey, it works.” That’s boring for everyone. It’s like when people do demos like DBCC PAGE and DBCC IND, which show you stuff on a data page… Great, cool, I know where my record is, slot arrays, woo I’m having fun.

Erik Darling: Let’s see, Rowdy brings up a good point. He said that Grahame should hit up SQL Saturdays in his area. That is an excellent suggestion from Mister Rowdy Vincent. Rowdy, I hope all is well.

My company is moving to Oracle…

Erik Darling: M.M. follows up, “Red tape and money is the reason for SQL Server 2014, and the company has decided that they need to move to Oracle.” Wow, you’re going to be rich. M.M., you are going to be an Oracle DBA, welcome to planet money. Wow, good for you, man, my hat’s off.

Richie Rump: Congrats on the higher tax bracket.

Erik Darling: I hope that someone from Oracle does Office Hours because I can’t answer a damn question about Oracle. It’s interesting that money would be the issue since Oracle Enterprise Edition is $47,500 per .75 cores on your server, so good luck paying that bill. And that’s just to get the database on there. If you want to do anything like the partition add in, the performance add in, the analysis add in, like if you want to look at performance, it’s that ASM or whatever, it’s like money out the Wazoo.

Richie Rump: Think of all those steak and lobster dinners the senior managers had to have, Oracle had to pay them to do that. So they’re going to have to get repaid somehow for those steak and lobster dinners.

Erik Darling: Steak and lobster ain’t free, unless you go to Red Lobster, then…

Richie Rump: Is it really lobster?

Erik Darling: Lubster.

What should I do about 4-5 deadlocks per day?

Erik Darling: Let’s see here, next question is from Chris, and I’m going to try to read this one as I go, which is always a mistake. I’m going to fall into the Brent trap and start reading this and then realize… [crosstalk] ‘I have Spotlight alerting me to around four or five deadlocks on a specific database per day. They don’t happen at a specific time of day, nor do they happen all at once. Should I be concerned about these deadlocks or since they are rather random and appear to be handled by the application, should I cautiously keep an eye on them and leave them alone?” So when you say they’re handled by the application, I assume that you mean that there’s some retry logic in there, which means the transaction is just like, “Oh, got a deadlock. Hit the deadlock error message, I’ll try again in five, ten, whatever seconds.” In which case, I would leave it alone. Richie, how do you handle deadlocks in the application there?

Richie Rump: That’s pretty much it. I mean, if it’s a user facing one, you have the user do a retry on it. Typically, if you’re really curious, take a peak, see what’s possibly going on in those tables during that time, if you’re really concerned about it. But if there’s no user errors and the application is kind of handling it, then just keep an eye on it. And if they start increasing then, you know, you know what to do; you’re a DBA, go do that.

Erik Darling: Really, unless users are complaining, I can’t really see a good reason to worry about it. Worry about the stuff the users are complaining about.

Can I use SSMS 2014 to query SQL 2016?

Erik Darling: Next up, J.H. asks, “I heard…” You heard through the grapevine, “That SQL Server management studio is no longer bundled with SQL Server 2016. Can I use an existing SQL management studio 14 that I already have on another machine to work on SQL Server 2016?” My question for you, J.H. – now I don’t want you to think that I’m calling you lazy, but if you have management studio 2014 and SQL Server 2016, why don’t you just try connect it?

Richie Rump: Well, you know, scratch that. Flip it, reverse it, right. So if you install 2016, you could kind of use it on anything prior…

Erik Darling: Right, backwards compatibility goes backwards…

Richie Rump: Right, so I haven’t tried it going forward yet. On most cases, I’ve found that that will work, but there’ll be some odd kind of buggy bugs. Now this is stuff that I was messing with like in previous versions, not necessarily in 2014 to 2016; but nine times out of ten, things will work unless it’s some sort of new stuff.

And there’s a whole bunch of new stuff they just announced in the last couple of days too. So there’s really no reason why not to upgrade your SSMS stuff, unless you’re like I don’t want the bugs… [crosstalk]

Erik Darling: Who doesn’t want bugs?

Richie Rump: Brent keeps telling me he doesn’t want bugs. I’m like, “What kind of crazy person are you?” It’s in software, I mean, you’re going to have them.

Erik Darling: We’re only human.

Richie Rump: Unfortunately, but yes, I would just go ahead and upgrade because they’ve been really good at getting updates out for SSMS and getting fixes and patches. The only problem is, it does take a while to install. They haven’t fixed that problem yet, but they’ll get round to it.

Erik Darling: It’s funny. So SSMS is still free, right, it was always free, but you download it separately. There’s like a separate download link and package for it. It’s up to 17.2, at least the last I checked. But what’s really funny about SQL Server management studio is that 800MB download. SQL Server is 1.2GB or 1.3Gb to download. So management studio is like two thirds the size of SQL Server. Like a relational database engine that does a whole bunch of crap versus management studio, and they’re like just a little bit…

Richie Rump: Yeah, and I bet you it’s all the visual studio crummy crumb stuff, right. I bet you it’s just all that.

Erik Darling: Oh yeah, it’s garbage in there. It’s all maintenance plans.

Why does Oracle get such a bad rap?

Erik Darling: Wes asks, “Why does Oracle get such a bad rap?” They don’t have a bad rap, they have an amazing rap, that is an amazing platform. The bad rap comes from how expensive it is. I mean, it’s worth it because you get a bunch of amazing stuff out of it, and people who work with Oracle are smart as hell. You have to be like a genius to work with Oracle. The commands and the stuff – it’s hard, man. I look at that and I’m amazed. If you want to be amazed by question and answer stuff, go to the website AskTom. It’s an Oracle Q&A site, specifically for Oracle database, and the queries that get put up on there and the stuff people want to do is bananas. And the answers they get are like, “Why can’t I do that?” Like there’s stuff that you can do with Oracle that’s just so developer friendly, it’s so amazing. I mean, I’m not going to say never – never say never – but it will be tough for Microsoft to implement and make performant, even though that’s not a real word, all the stuff that Oracle has.

Richie Rump: And I think it’s historical too, right? Oracle, historically, had some pretty bad interface tools, you know.

Erik Darling: They still do.

Richie Rump: Yeah. So I remember, back in the day, taking a look at those tools and saying, “Okay, where’s Toad?” Because that’s a hell of a lot better than the out of the box stuff. I think that has kind of gone along with them, even though things have gotten better in that aspect. And then everyone goes straight to cost, right?

Erik Darling: The cost is outrageous.

Richie Rump: So when you’re dealing with a high priced enterprise product like that, the folks who cannot afford that kind of will poo-poo it so that they can say, “Oh we made a better choice because it’s so expensive and you don’t get as much as you pay for.” So I think that’s why – a lot of the rap there is because of the cost. And frankly, not a lot of folks can actually afford that.

Erik Darling: Yeah, I mean $47,500 for .75 cores of Enterprise is nuts, but you get crazy stuff out of it. Oracle RAC is amazing, Flashback is amazing. The stuff you can do with Oracle really is great, but it’s so expensive and it’s so hard. Like Oracle DBAs I have unlimited respect for because they have a tough gig.

What headset does Erik use?

Erik Darling: We’re going to answer a quick one before we do anything else. Brian asks, “Wondering what headset Erik uses?” Good question.

Richie Rump: Audio Technica?

Erik Darling: It is an Audio Technica BPHS1. That’s Brian, Paul, Harold, Simon one. So that’s what I use.

Should I call C# methods from stored procs?

Erik Darling: Alright, let’s move on to something interesting. Eric asks – Eric who spells his name wrong asks, “Is it good practice to call a c# method from a stored procedure?” Is a tricycle furniture? That’s my question today.

Richie Rump: Alright, what are you trying to do and how are you calling the stored procedure? So are we talking about a CLR function that’s written in c#? It could be. I know Adam Machanic, he’s historically done a lot of interesting things with CLR and written some really performant stuff in .NET CLR in SQL Server. Now, if you’re somehow going outside of that in some sort of weird fashion to call a c# procedure, that’s probably not a good idea.

It all depends on what you’re trying to do and how performant do you need that c# thing to be. Now, mind you, there are a lot of functions, especially in 2012 On that are based on .NET stuff. Essentially you call a tSQL function and it calls .NET in the background. So sometimes when you write tSQL, it’s calling c# for you, which is actually kind of cool stuff. Format is one of those functions, I believe.

Erik Darling: I would hate that. Like just as a DBA, I would be like, “What the hell are you doing to me; why?”

Richie Rump: I mean it’s all in the background, you don’t know. I mean, from a software perspective it makes sense, right. You already have all the formatting stuff, so just plug it in.

Does SSMS 2017 break SSIS 2016?

Erik Darling: Kelly asks a question that I don’t have an answer for. “Does SSMS 17 still break SSIS 2016?” Kelly, unfortunately I have still never opened SSIS, to this day. Still never cracked open [bids] or whatever people who do that do.

Erik Darling: Oh, Grahame says, “Yes it does.” So, thank you, Grahame, for letting us know. Grahame, a master of availability groups and SSIS, apparently… Appreciate that, Grahame. You could do a whole presentation on that; people would be amazed.

What’s the best way to call PowerShell from T-SQL?

Erik Darling: David asks, “What is the best way to call a PowerShell script from tSQL without using xp_cmdshell? Is it CLR?” That’s one way. My question would be what the context is, because if you go into SQL agent, you can have a job step that’s a PowerShell script. So, if you really need to call it from tSQL, like in a stored procedure, then maybe not the best way. But if you had to call it from – I would say set up the PowerShell scripts. The agent jobs step is a PowerShell script executing tasks, then use that instead because that is probably the best way the doesn’t involve xp_cmdshell.

Richie Rump: Yeah, if you need to call it in a stored procedure, that would scare me because PowerShell just is not the fastest thing. Even if you run it on your local machine, it is kind of not fast at all. So I would think why are you doing that? Is there something where you could actually batch stuff up in an app and put it back in a database? Maybe that’s a better way of doing things.

Erik Darling: “PowerShell is getting active directory membership, the stored proc is called from SSRS.” Boy, boy howdy… That has bad idea genes written all over it. David, at the risk of offending you, I’m going to send you to DBA.StackExchange.com, because it sounds like there’s a lot more going on here than just how do I PowerShell.

Richie Rump: Yeah, there’s some good stuff going on there and it’s kind of freaking me out there. [crosstalk] Scary stuff, man.

Erik Darling: Beep, beep, Richie.

Richie Rump: They all float down here; all the PowerShells float down here.

Erik Darling: Alright, we are at the 45 mark, which means it’s time for us to go. I’m going to get back to being a monkey and writing stuff and Richie, good luck. I have interesting news, and I’m going to close the meeting as soon as I say this, so hopefully it freezes on your face. I just bought my first C++ book.

Richie Rump: The man’s crazy. He doesn’t know what he’s doing.

This week, Brent, Tara, Richie, and Erik discuss a most excepted solution for SA and securing an instance, how many VLFs should a database have, merge replication issues, query tuning tips, database restores, and Larry Ellison’s self-driving database.

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 9-20-17

(Sorry, no titles for questions this week – Brent’s off in the Isle of Man. Insert joke here about him not being manly enough to be a permanent resident.)

Brent Ozar: Hannah asks a question… I guess we’ll get started with the technical stuff… Hannah says – of course, it’s a tough one to get started with, “In regards to SA, the SA account and securing an instance, what’s the most accepted solution? Should I disable SA and only use Windows Auth, or should I change the SA account?” What did y’all do when you were managing servers?

Tara Kizer: I renamed the SA account and then set the password to something ridiculous that nobody could ever type in unless you got to source control. We had to use SQL Authentication due to the applications. I would have preferred to be Windows Authentication only, but whether or not you had to set up the instance to mix mode authentication to allow SQL Authentication is dependent upon the applications that are connecting. So since I had to have SQL Auth enabled, I wanted to do something about the SA accounts, so we renamed it and then just set a ridiculous password and put that in source control, or the password manager, of course; but never use the renamed SA account to login.

Erik Darling: I was in the same boat as Tara, where that old [inaudible] account was a SQL login, so I couldn’t just use Windows Auth only. But I would at least make an attempt at keeping SA disabled. Other people with SA privs would come on and turn it back on to do things with, which was kind of aggravating. Room full of developers just changing SA settings, and I was like no, put that down, stop. Someone’s popular…

Tara Kizer: No, it was just spam calls; so many spam calls.

Brent Ozar: David says, “I scramble the SA account daily. I had a vendor that kept resetting it and using the [crosstalk].” That’s excellent.

Brent Ozar: Next up, Chris says, “Hi guys, curious what your thoughts are for how many VLFs my database should have. I hear lots of different recommendations, what’s a good number to watch out for?”

Tara Kizer: [crosstalk] I have these numbers in my head, just based upon all of the databases that I’ve gone through and shrunk and fixed the VLFs on. Because I have run into massive downtime because of extremely high VLFs, back when I was an intermediate DBA and learned about VLFs afterwards. Anyway, he says to keep them under 100 or 1000 VLFs. What about – for 100GB database, generally they have less than 2000 VLFs. 2000 is too high in my opinion for 100GB database. 100GB database, I’d expect around less than 200 VLFs. Shrink it down as far as possible, like even to 1MB, then start growing out into bigger chunks. But 2000 is too high, in my opinion, for a 100GB database.

Erik Darling: Yeah, I think the main thing with VLFs, though is that it really is an odd circumstance where they cause a big problem. So, if you’re at the point now where you’re kind of twiddling thumbs and looking for something to fix, go for it. Otherwise, I would probably concentrate on something else.

Tara Kizer: Exactly, that’s what we did at my last three jobs. Because we ran into this massive downtime because of high VLFs, it was just about overall health check of the systems. And so, my last job, two or three times per year we had this checklist of things we would do to ramp up for certain holidays that would cause extreme load on our servers. So, we had this checklist and one of them was check VLFs, so it’s about the health check.

Erik Darling: It’s one of those things like usually when you fix it once, you don’t have to go back and go crazy with it. Like I ran into a 5TB or 6TB database, when I first got it there were like 25,000 VLFs, which even I was like oh, I should do something about that. But even when I got it down to a normal number, I still had a restore take 21 hours with it because the VLFs ended up being…

Tara Kizer: Oh right, yeah that’s the balancing act, yeah.

Erik Darling: Ah that was cute.

Brent Ozar: Roy says, “Am I the only one who hates all the cloud talk around SQL Server? I prefer the simplicity and strength of database integrity, you know, ACID. There are developers struggling to keep up with the latest visual studio, much less constantly being bombarded with the cloud migration suggestions.” Richie, this has you written all over it.

Richie Rump: It kind of does, doesn’t it?

Erik Darling: Richie’s ears started whistling.

Richie Rump: Oh look, my ears are turning red, not that Brent could tell. But yeah, I kind of do, and there’s going to be a big push from Microsoft to kind of get everybody into Azure, because that’s going to be their next big business venture. It’s not going to be from licensing fees, it’s going to be from getting everybody in the cloud. So, it’s not going to go away anytime soon. The only question is, are you going to go there or are you going to stay on-prem? And I can only imagine they’re going to make it harder for us, as time goes on, to stay on-prem. Either through higher licensing costs or something other, because they really want us all to get to the cloud.

I think you still could do ACID in the cloud without a problem. Azure SQL should be able to do that, but things are in flux right now in the Microsoft world with all this stuff with .NET and visual studio constantly being re-upped, revamped and the next version of .NET not being where people expected it to be, and that constantly being turned over. So just hold on tight. If you’re going to stay where you’re at and wait to see what happens, then do that. If you’re going to grab the cloud hard and hold on, try that. But I don’t think there’s anything wrong with sticking with on-prem SQL Server until Microsoft really figures out what they’re going to do in the cloud.

Erik Darling: I think SQL Server’s going to be a tough zoom. I mean, they’re not going back to less frequent releases. From what I read, there’s going to be probably every two years, maybe even every year releases. I mean, if they throw in a 2017 and they called it 2017, not 2016 R2. So maybe even yearly releases, so that’s going to be tough to juggle even as a consultant. Like what version are you on, what can that do?

Richie Rump: For your lying DBA, right, they guy just working on-prem, not a very large stuff that’s going on, is there going to be stuff in the engine that’s going to be worth that upgrade? I mean that’s going to be the real question; do I need to upgrade this year or not, I think, is going to be a question that we’re going to be hearing a lot going down the line as we start to see more and more frequent updates. And a lot more stuff being put into the product, and stuff that we may not need or use.

Brent Ozar: I’m tired of it just because it feels like a sales pitch. Very often it feels like – I know they’re going to get monthly revenue from me, I get it. And they’re shoving it down DBA’s throats to go, “Buy now, save later.” And it just feels kind of old with that, but I get why they’re doing it, but it just feels like a spammy overreach right now.

Erik Darling: Obviously, part of what they want to suck people in for is so they can keep going towards the automated tuning thing. So, all the, “We can fix this problem for you if you just give us the keys to the car. We’ll just go in and watch the engine run and figure out what needs to get fixed and we’ll just keep doing that.” So, like we talked about last week, with the managed instances, a lot of that is just farmed out query tuning. So, they do the automatic query tuning, they do the automatic index tuning, they fix regression. So, all the tooling that they’re building in with query store, with query store wait stats, with the memory grant feedback, all the cool new batch mode stuff. So, all that stuff that comes back is like, oh okay, we just –  the more people we can get into this, the better we can get the automation to be.

Brent Ozar: Alex has a question. Alex, I’m just going to tell you, whenever you see more than multiple sentences in there, go ahead and put follow the instructions on the screen. Go to DBA.StackExchange.com. It’s a great place to go in and post really long involved questions. We do the same thing for ourselves. Erik posted a question today over at Stack, we answer questions over there all the time, but whenever you find multiple paragraphs involved, go ahead and post that over as Stack.

Erik Darling: We answer questions there too.

Brent Ozar: Sri says, “I’ve got merge replication problems, erroring at night due to virtual memory. I’ve increased the RAM on the VM to 32GB, unfortunately, I’m still having problems with SQL Server running low on virtual memory, please restart the merge agent. What should I do?” That’s actually a good one for Stack as well, I think probably. Have any of us used merge replication?

Tara Kizer: Just transactional. On this week’s client, I’m using it and I’ve had one other client using it.

Richie Rump: I’ve used it once, that was it. That was my first and last time, that was it.

Erik Darling: Oh wait, but if it’s virtual memory, couldn’t that be that the VM is getting sucked dry?

Brent Ozar: Yeah, it could be anything from outside, external memory pressure…

Erik Darling: I might try doing that…

Tara Kizer: But this is the error from within merge replication though, so it’s referring to virtual memory at the OS level, isn’t it? Wouldn’t that be the page file?

Erik Darling: Right, but if it’s a VM then something else is sucking memory away from the VM then.

Tara Kizer: Right, but I mean the virtual memory in the error is not necessarily referring to the virtual memory on VM.

Erik Darling: I think what I might try – the first thing I would do, off the top of my head, is try turning lock pages in memory on, to see if I could keep memory on the box and keep memory for SQL stuck in there.

Brent Ozar: Also, go Google for this, because I Googled and there’s a whole bunch of people with the same issue. So, go Google it and there’s a bunch of advice out there.

Erik Darling: Brent Google Ozar.

Brent Ozar: Joseph says, “Please don’t laugh. Have you ever seen a SQL Server database repeatedly fry an SSD drive?”

Tara Kizer: I wonder if it’s the drive that’s the problem and not the server.

Brent Ozar: I wonder if it’s not the server. Like a crappy RAID controller. Tell us ore about what fry means, because I’m curious. What kind of symptoms are you seeing? He says, “RMA.” Is that remotely Madonna assisted or?

Brent Ozar: Samuel asks, “How accurate is the tempdb estimate while running CHECKDB with estimate only?” Wow, I’ve never done that.

Tara Kizer: I haven’t either.

Erik Darling: I’ve done it, I just don’t remember how accurate it is.

Brent Ozar: He says, “Can you simply resize tempdb with the estimated value?” Tell us more information. Tell us how big the database is, like how many GB or TB, then how large is your tempdb, just ballpark. That would be interesting…

Erik Darling: Also, how big is your largest object in the database? I mean, I’ve run that before and I want to say the thing that they use is kind of like what they use to estimate – when you run that stored procedure that estimates like page RO compression; where it takes a rough guess at how much it will compress by looking at some of the data.

Brent Ozar: J.H. says, “Is it accurate when enabling TLS 1.2 support we would need to install a specific patch?” Your best bet there is to go post that on Stack Exchange as well. We don’t get involved with client-side encryption stuff at all.

Brent Ozar: Rex says, “Pedro Lopes of Microsoft has a solution to your VLF problems…” I’m going to say thanks just so I can stick that in everyone’s Q&A, if you want to go hit that link. There’s actually a really good post by Tracy Boggiano this week that will be in the weekly links this coming week. She has a script to automatically fix your VLFs. She checks VLFs, then shrinks your database down to nothing and regrows it out to an appropriate amount, and just runs it in a SQL agent job, which scared the hell out of me until I remembered it was Tracy Boggiano. Then I was like, I actually kind of trust. And it’s a big script. It’s huge.

Erik Darling: I was going to say, if you wanted to like take into account all the weird stuff, like is it okay to shrink a log? I’m like, what time is it?

Brent Ozar: Yes, and she’s got a lot of logic into there, and I was like – working hard on that.

Erik Darling: That’s cool.

Brent Ozar: Graham says, “I have high CXPACKET and latch waits. My application is slow, the database size is 90GB, the server has 32 cores and 64GB of RAM, and I can’t put any more RAM on the server. I see a lot of parallelism in query plans from my top ten most expensive queries. Should I play around with MAXDOP and cost threshold?

Tara Kizer: I mean, you need to make sure that they’re set to – yes, not play around but, I mean, make sure that they’re set appropriately for your server, probably eight for MAXDOP and 50 for cost threshold for parallelism. But the fact that queries are going parallel doesn’t mean that there’s a problem. I would tune the expensive queries.

Brent Ozar: And what would you look at in the queries? What are the kinds of things that you look for?

Tara Kizer: I mean, that’s more of an Erik question, but I mean, for my level – because he gets into the individual operators. I’m just like, I’m out. So, I’m looking for like, glaring problems, you know? The warnings, implicit conversions, compilation timeouts, missing indexes. I’m looking at the scans, but scans aren’t necessarily a problem. Expensive key lookups – you know, I always pull my plans into – what is it called – Plan Explorer, Century One to Plan Explorer, and then if the query is large enough, I’ll go into the top operations tabs so I can see exactly which one is the highest estimate percentage, because I can’t always find it if it’s so big and a lot of our clients have some really large plans. At least I’ll know what to be on the lookout for and then go back to the graph.

Brent Ozar: He says it’s an ISV app where he can’t change the queries. I would still index though.

Erik Darling: Yes, index tuning would be the biggest part of that.

Brent Ozar: Joseph says, “See howsmyplan.com.” Alright, I got to go look this up. If this is – there was a SME – oh yes, this is – I think Daniel, I forget his last name. Dan Janick has a plan analysis tool howsmyplan.com. I haven’t seen it improved a lot lately, like he just launched it once and that was kind of the end of it, but it was interesting.

Brent Ozar: Let’s see. Hannah says, “Is it possible to add schema binding to a current existing view or do I have to completely recreate it. My goal is to add a clustered index to speed up performance. The view returns four billion rows.”

Erik Darling: I mean, you have to alter the view to add schema binding, but you don’t have to like recreate the view.

Tara Kizer: Aren’t you adding a where clause when you query this view?

Erik Darling: Also, I’d be curious to know why you think adding a clustered index to the view would help you return four billion rows.

Brent Ozar: Well, imagine you got [crosstalk]

Richie Rump: Security reasons? I don’t know. That’s Brent’s question.

Brent Ozar: Say that the big cost of the view is joining you know, one million rows,cross-applying or cross-joining another million rows and producing billions of rows.

Erik Darling: You can’t make an index view with apply.

Brent Ozar: No, I mean, like an inner join, whatever, million times a million, inner join, one equals one. This is horrifying for so many reasons because it’s going to write four billion rows to disk. It’s going to write four billion rows, whatever space that takes in order to do the clustered index. Hannah, we like you, we want to know more about this.

Tara Kizer: It doesn’t matter how many rows the view returns when you just query the view without a where clause. Once you apply the where clause, it’s not churning four billion rows and then applying the where clause. It’s not like a linked server where it pulls all over the data first, then filters.

Brent Ozar: No, but if the view has four billion rows and you put an index on it, it’s going to take enough space for the [crosstalk].

Tara Kizer: I’ve tested index views, I’ve never ever gone down the route of deploying those to production because indexing the underlying table has been sufficient in all cases for me.

Erik Darling: Yes, unless you’re doing like really, like, you know, kind of big, involved aggregations, I’ve never seen really great returns on index views either. [crosstalk] so many rules.

Brent Ozar: Yes, it’s messy. It was for us, it was the overhead of joining tables together was so high and we were trying to get stuff down into milliseconds, but super rare.

Richie Rump: Yes, the only time I’ve had to deal with you know, four, five billion row tables, I’ve actually used table partitioning and then used the partitioning key to get into the data where I needed to, but on a view…

Brent Ozar: Somehow your audio went flawless again.

Erik Darling: Somehow.

Brent Ozar: And you’re not – I’ve seen you, you haven’t even gotten near the computer. You’ve been standing over by the couch. It’s a random number generator, maybe it’s the pacemaker that’s in your pocket maybe.

Richie Rump: It works better now.

Brent Ozar: You’re totally good. It’s flawless.

Brent Ozar: Samuel says his database size – this is about that CHECKDB with estimate only. Samuel says, “My database size is 100GB, the largest table is 90GB. My tempdb was 10GB. Don’t ask.” Yes, that’s going to be a problem. That’s going to be a problem. Especially if you have any indexes on that thing, you’re going to need – it’s not like you’re going to need 100GB, but I wouldn’t like, try to get by with 15 or 20.

Erik Darling: Yes, but read that again. His database is 100GB and his largest table is 90GB.

Tara Kizer: It’s basically one table.

Erik Darling: But I was going to say, does it have audit in the name? Like, log, what’s – because…

Richie Rump: Images, maybe? I don’t know.

Erik Darling: Because what I’m curious about is – because I see this a lot with databases of this type, where you will have that auditing or that logging table, and that table will be a heap, and that heap may have a whole bunch of empty space in it. So, I would be curious if that’s part of why that table is so massively weird, or this has some crazy like XML column in it. I don’t want to talk about it.

Brent Ozar: Mike says, “If I restore database to a temporary server to run as production for a day or two, do I need to restore any of the system databases with it?”

Erik Darling: No? I have never had to do that.

Brent Ozar: And you think about how you want to move your jobs around. Like agent jobs or stuff like that, but SSIS packages, security on them.

Richie Rump: I mean, if it’s restored to a temporary store just to do some dev testing, like unless you have objects that you rely on in system databases, then no. Even then, I would just script those out and stick them on the server.

Tara Kizer: I mean, but if you can take the downtime and you install on that second server using the same paths as the original server, stop SQL Server and copy all database files over, I mean, you know, the system databases over, and then both servers have to be stopped for this and that gives you a quick way to get all the logins and jobs and everything. That way you don’t have to script stuff out, but that requires the downtime to do it.

Brent Ozar: Srejath says – and I’m going to take this one because I know this – Srejath says, “Larry Ellison reveals self-driving database.” No. He says he’s going to build one. He doesn’t have one yet. Similarly, I would like to announce – I don’t know, I was going to come up with something. I can’t do it on the fly without being horrible. He says, “Fully autonomous Cloud database service, sounds cool, what’s your take?” My take is that it’s just like every time that Larry Ellison says things like, “My database is unbreakable. This database is self-tuning. Oracle is amazing, it’s really cool.” But you notice how it’s always surrounded by highly paid experts, you know, gently massaging it, feeding it cheese, whatever. It is a highly tuned database that requires highly paid professionals. I am sure that Larry is hearing the calls of people saying, “Come to SQL Server. Any knucklehead can manage this thing” and going, well, I better make it easier to manage. So, he’s going to paint a rosy picture of what it’s like down the road, it’s going to take years, or lots of releases to get there. But will Microsoft announce something similar? They already have. As your SQL DB, they make the same kinds of claims about automated index tuning and automated query tuning, and it doesn’t work yet.

Erik Darling: The other thing is that Larry has been making similar claims since I think, Oracle 9, that you know, doesn’t need a DBA, fixes itself, has like, you know, little settings you can change to just make everything fly right. So, you know, just another one of those things that will not happen down the line.

Brent Ozar: Samantha says, “He also says, buy our Cloud credits.” Yes, I mean, they got caught behind the game on in terms of the Cloud, it’s just like with Microsoft got caught behind with Internet Explorer. Netscape Navigator came, took out over the world, Microsoft had to backpedal fast and they were able to get past it, build out a web browser and did fine, but I don’t’ know how Oracle is going to do long-term in terms of sales, but they’ve got a fantastic database. I’m sure they’re going to do well.

Brent Ozar: Roy says, “What are we going to do when Ola Hallengren stops upgrading his maintenance solution? Like when he retires?” Great news is, he’s got it on GitHub. He put the whole thing out on GitHub, so you can make changes to it. We’ve already branched it ourselves and made changes to send back to Ola, so it’s cool. If he retired, I think we would probably be willing to take it over. It would be a lot of fun. I think that where we would fork it into like the DBA tools project, I think the community’s adopted it so much that it’ll keep going.

Tara Kizer: I felt bad when I retired from my eight solutions because I was one of the competitors back in the day. I had actually had mine out there longer but it was so hard to keep it up to date with all the versions and editions and all the various things, and eventually I gave up and we stopped using my solution at my work and deployed out all those codes to – I mean, there was about 700 servers we had it deployed to, and I knew from my emails and the blog comments that my solution had been used tons of servers around the world, and I’m just like, sorry. I just don’t have the time to do this anymore.

Erik Darling: Yes, I mean, like I’m going to be curious how Ola decides to keep up with releases. Just in 2017, there are a couple of really cool new editions to views for log files and for data files where it will tell you how much has changed. So, you can actually make a decision to flip to a full backup if enough stuff has changed. Like you don’t want to take a differential now, you’re going to go ahead and take a full backup because enough stuff has changed. So, I’m interested to see how he decides to keep up with that.

Tara Kizer: Brent’s frozen, and his audio. Maybe he’s gone.

Richie Rump: We got him.

Erik Darling: I was sitting there and I was looking at him, I was like, I’m saying something stupid. He’s just – he’s making that like school mom face. I’m like, crap, I’m saying something bad.

Richie Rump: Make fun of my audio one more time, Brent. One more time.

Erik Darling: Make fun of my video one more time.

Tara Kizer: That’s all the questions we have anyway, so we can either end the call here…

Erik Darling: All we know is that North Korea has bombed Chicago.

Richie Rump: Wow, let’s not mess with that.

Erik Darling: Well, we’re out of questions and just about out of time anyway, and I think Brent is actually silently called the end of this. So, let’s boogie people.

Tara Kizer: Alright, see you.