This week, Brent, Tara, Erik, and Richie discuss moving views between databases, Cluster Quorum File Share on AlwaysOn, checking for corruption, rebooting/restarting SQL server after applying cumulative updates, how many drive letter to use for a VM, decimal precision and scale value settings on SQL Server 2016 vs 2008, and nested views.

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast – 2018-09-19

Should I put views in a different database?

Brent Ozar: Justin says, “If you have views in a database, would there be any reason to move those views to a different database?”

Tara Kizer: Interesting, odd question.

Richie Rump: Try and get more information.

Brent Ozar: I’m trying to come up with a reason why I would do it and I can’t think of a reason. Oh okay, I got one; if you wanted to restore the database. I used to do this on a log ship secondary. We would have complex views set up and then replace the database underneath from time to time because the views couldn’t be on the original database. That’s the only thing I’ve got, though. I can’t think of a reason why… Okay, so I’m going to stretch, SQL Server 2016 has database level things like MAXDOP and parameter sniffing. So you could put views in different databases if you wanted people to have different MAXDOP settings and you didn’t want to use resource governor.

Tara Kizer: Would that work though, because you’re going to be referencing objects in the other database? I guess that would work.

Brent Ozar: Yeah, it’s based off whatever database you’re currently in. Justin follows up with, “We have developers who have two databases. One has tables and the other has views pointing back to the tables.” Okay, no, no, no credit there.

Richie Rump: this reminds me of the early days of Access, where we had one database with the data and then we would have another database with all the screens and reports and all that stuff.

Brent Ozar: Thank god that’s over. Justin says, “We think it’s a bad idea.”

Tara Kizer: It’s not necessarily a bad idea, just it’s odd. I wonder why he thinks he needs to do that.

Brent Ozar: Version control is going to be harder; deployments are going to be harder. Yeah, it strikes us as a bad smell.

Richie Rump: It’s a bad idea.

Brent Ozar: I can’t come up with a scenario where it’s a good idea to do new development that way.

How should I configure quorum?

Brent Ozar: Rob says, “Hi, we’re about to build a two node Always On cluster with four named instances.” I think, and you may want to follow this up, Rob, I’m not sure if you mean an Always On Availability Group, or a failover clustered instance, your grandpa’s cluster. “As far as quorum, will I need to specify a file share on some server when I install clustering for quorum due to the even number of nodes?”

Tara Kizer: Yes. You need a third guy for quorum, and most people for Availability Groups are using a file share witness. On failover cluster instances, a lot of us use a quorum disk, you know, a SAN drive, a [Q] drive maybe.

Where should I run CHECKDB in my AG?

Brent Ozar: Pablo says, “Hello, folks. Can you suggest the best way to check for corruption on replicas on a high transactional server with Always On…” I assume he means Always On Availability Groups, since he means replicas. “Best way to do CHECKDB…”

Tara Kizer: I like to offload that task. I mean, if your system is 24/7, which a lot of peoples are, and you can’t take the hit of CHECKDB at any time of the day, then you offload that task to another server, backup restore, SAN Snapshot, something on another box. But you just have to keep in mind that you’re supposed to also CHECKDB your replicas, but that brings in licensing concerns because you’re offloading production work to another server, and also that other server for the backup restore.

Brent Ozar: And make sure you’re doing it wherever you do backups. Like, you want to be checking the one that you’re doing backups on, otherwise, your backups may be garbage.

Tara Kizer: you could break up the task. So CHECKDB is just a bunch of other check things, you know, like CHECKTABLE, CHECKALLOC. It’s a bunch of stuff. So I know when I worked for Qualcomm, we had our large systems where we couldn’t offload tasks to another server. Maybe because they were too big, we didn’t have enough SAN storage, I don’t know. But we broke that up into multiple steps and by the end of the week, all steps would have been performed. So a full CHECKDB would be performed every week, but it would be a daily task of small pieces.

Brent Ozar: I haven’t used this myself, but I’ve heard Sean and Jen McCown – god, their site’s hacked again. Alright, well so much for that… A part of me wants to recommend this, but their site’s hacked again. Whenever their site gets unhacked, Minion CheckDB has the ability – they have a set of open source scripts. I’m not sure if they’re free, open source, whatever, but a set of scripts where you can offload parts of CHECKDB to different servers. You can have this server check catalogues, this server check this file group. It looks really slick. I’ve never used it myself, but also, when you click on it, you get a choose your price sweepstakes, so clearly something’s wrong with their site at the moment, so we’re going to just close that browser and go back.

Richie Rump: Burn that VM to the ground.

Brent Ozar: Websites suck.

Do I need to restart after patching SQL Server?

Brent Ozar: [Joy Anne] asks, “Is a Windows reboot or SQL Server restart recommended after applying Cumulative Updates? I just realized that the CU9 security update requires a Windows reboot before I can apply CU10.”

Tara Kizer: I mean, it goes through the reboot restarts, or the restarts at least, during the installation, the restarts of SQL Server as needed. But yeah, I mean, I would definitely reboot. Now, if it’s my own desktop machine, I don’t. I mean, if I can get away with not rebooting and it’s not asking for a reboot, I do not. But on production, I’m in a maintenance window, I’ll probably reboot it at the end, even if it doesn’t ask.

Brent Ozar: Plus, usually, you want to do Windows patching at the same time. If I’ve got to restart SQL Server, I want to fix the Windows patches, which seem to come out more frequent than the SQL patches.

Should I put everything on the C drive?

Brent Ozar: J.H asks, “When setting up a SQL Server 2016 Always On within two VMs, is it theoretically okay to have one large C drive, like 1TB, on each VM, that holds everything? SQL Server install all system databases, tempdb, user databases, log files, et cetera, or should I separate them out onto smaller drives?”

Tara Kizer: I don’t ever put database files on the C drive. The C drive is the number one place to run out of disk space because of Windows security updates. You could put a large drive there. Definitely, the SQL installation and all the shared stuff – shared files, not the shared system databases. I would not put your database files on the C drive. There’s no reason to and it’s so easy to create a drive.

Brent Ozar: Yeah, the filling up thing is the one thing that scares me. The other thing that scared me before is when you want to do VSS Snapshots, it has to quiesce everything on that drive, you know, so you may just want to snap databases and not the OS. And there was another that hit me and I was – so if you ever have, for some reason, I’ve had situations where I needed to just get copies of the user databases and log files somewhere else, like I wanted to do an upgrade of a Windows or SQL Server and I wanted to go test it somewhere else, so much easier to take a snapshot of just where the data and log files live and present that over instead of having the whole C drive.

Tara Kizer: I would have the minimum of three drive letters for a VM. You know, C drive, maybe a D drive for the user databases and some of the system databases, and then another drive for tempdb, putting tempdb on its own drive.

Brent Ozar: I’m with you, because plus too, it’s tuning. For the SAN admin, it’s easier for them to tune for different access patterns. So, like, tempdb, I might want that on blazing insane fast storage if I have a tempdb problem, otherwise ,I might want it on garbage. I just might want it on junk storage if I don’t care about it.

Tara Kizer: Richie just got a better offer, I think. See-ya…

Brent Ozar: In the middle of the webcast, like, forget it… That, I think, might be the first time we’ve ever seen Richie disappear when it didn’t involve a blue screen of death, because usually Richie drops it when he has blue screens of death. Richie, your computer stayed on and you left. That might be a first.

Richie Rump: Yeah, I had a delivery notification.

Have precision and scale changed across versions?

Brent Ozar: Robert asks, “Is there any difference in decimal precision or scale between SQL Server 2016 and 2008? I’m getting a different value on the two servers and they’re both set to the same precision and scale settings.”

Tara Kizer: That’s interesting. I would imagine, if the version number is the reason, that people would have blogged about this or, you know, hit it.

Brent Ozar: It should be easy to do a repro query there, just like repro the exact same query on the two boxes and then post it to, say, dba.stackexchange.com.

Tara Kizer: Declare a variable, just in a Management Studio test, and set it to whatever data type and size it is and see what you get on the two boxes. I just wonder, is it a query issue instead?

Richie Rump: Or data issue.

Brent Ozar: Or regional settings. I’m trying to think if there would be a way that regional settings would hose you, like if it’s different currency formats. I’m not sure what that would be.

Is there ever a good reason to nest a view?

Brent Ozar: And then Joe asks, “Is there ever a good reason to nest a view?”

Tara Kizer: I tell you, as a performance consultant, I cannot stand nested views, and I’ve had a client where I gave up after like five times. I was like, okay, now we’ve got to open up this view, now we’ve got to open up this view. I was like, I’m out. I mean, we have limited time on this call. There’s just no way I’m going through this.

Richie Rump: I mean, nested views make sense from a developer perspective, but when you take a look at performance-wise, it makes zero sense. I mean, it’s like going back to the old days of oobject-orientedprogramming. We had this and we’d build on top of another one and build on top… And it makes a lot of sense to developers, but they don’t ever go under the hood and see the garbage that it’s doing underneath. Just don’t do it.

Brent Ozar: And it’s one of those things where it works on my machine when it starts, you know. You have really limited data sets, you’re just getting started with the application, nobody’s using it. So it seems like everything’s cool, and then just later when you get to rreal-worldscale, performance goes to hell in a handbasket. It’s one of those that I would kind of coach people towards, hey, if you have a choice, I wouldn’t do it. I’d rather you do something else.

Alright, well a short list of questions here today. Y’all didn’t have any other questions, so we will bail out early and go start using our unlimited drink package. So we will see y’all next week at Office Hours. Adios, everybody.