[Video] Office Hours 2017/04/19 (With Transcriptions)

This week, Brent, Tara, Richie, and Erik discuss SQL Server 2017 CTP 2, quorum configuration for a 4-node availability group, database sharding, premature optimization, parameter sniffing, tracking query plans within cursors, and what’s coming up in the new version of Paste The Plan.

Here’s the video on YouTube:

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

Enjoy the Podcast?

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

Office Hours Webcast – 2017/4/19

 

How much memory should Analysis Services use?

Brent Ozar: Justin says, “I’m having trouble with analysis services memory runaways…” Oh, are they – they’ve run away from home, they’re having a bad year…

Erik Darling: Should call Del Shannon about that…

Brent Ozar: You’re too young for that joke – we’re all too young for that joke.

Erik Darling: I love that song.

Brent Ozar: “When a handful of users issue heavy queries they return a few thousand rows, the analysis service gobbles up more memory than where I have the hard memory limit set. How do I limit how much memory analysis services use so that it doesn’t go beyond physical memory?” I don’t think any of us know the answer to that.

Erik Darling: No.

Richie Rump: No.

Erik Darling: Spent a long time asking that one.

Richie Rump: For the record, Runaways was a great comic and they’re turning it into a TV show actually.

Erik Darling: What’s the name of the TV show?

Richie Rump: I think Runaways.

Erik Darling: Oh, that’s not very creative.

Richie Rump: No.

Brent Ozar: My head immediately went to Runaway by Ladytron. For analysis services questions that seems like a great thing to ask at dba.stackexchange.com. if you want someone who specifically blogs about analysis services, two names come to mind, Chris Webb, you can go to his blog and try to contact him there. So if you search for Chris Webb and BI, or Prodata.ie, Prodata.ie is Bob over in Ireland and he knows a ton about analysis services, which none of us do.

 

What is different in the SQL Server 2017 setup?

Brent Ozar: Wes says, “what is different in the vNext CTP installation process on SQL Server?” The answer, we’ve apparently learned, is not much. Other than that there’s R in python [crosstalk].

Richie Rump: Ooh thank you…

 

What quorum should I use for a 4-node AG?

Brent Ozar: Tim says, “what quorum configuration would you use for a four node Availability Group where there’s two nodes in your primary data center and two nodes in DR?” I bet Tara has done this so many times…

Erik Darling: This question’s boring for her, she’s going to take a nap and answer it.

Brent Ozar: You’re muted…

Tara Kizer: Okay, Windows 2012 and 2016 kind of throw me for a loop, the AGs I’ve deployed were Windows 2018 R2 and then 2012 but not R2, and I know that the quorum stuff has changed a bit in 2012 R2 and maybe even in 2016, so I don’t have that stuff memorized, but at least just looking at the question I would have a file share witness at the primary site and configure the votes on the two DR servers as disabled so that they can’t participate in knowing that the cluster is up or down. Network glitches are going to happen between the two sites…

Erik Darling: What is that, set node weight zero or something?

Tara Kizer: Yes, you do cluster.exe command, you can see the voting in the AG availability dashboard in SSMS I believe. I know that with the newer operating systems they recommend always having a witness, even if you had three nodes and that was it at one site, they said go ahead and do the dynamic witness, I believe, but I don’t have all that stuff memorized to be able to answer the question as far as the other operating systems.

Erik Darling: Brent does.

Brent Ozar: I’m with her 100% that’s exactly right, that’s exactly what I would do, yes, same difference.

 

Should I enable remote DTC for Biztalk?

Brent Ozar: Joe says, “Do you have any big concerns…” Drowning, drowning is my big concern – “or things I need to secure when our BizTalk developer wants remote DTC available on all of our SQL Servers?” Wow, have any of you guys managed BizTalk?

Erik Darling: No, never touched it.

Tara Kizer: No, but I’ve had clients that have had it and it requires MAXDOP equal to one just like all the other Microsoft stuff and they had BizTalk on the actual – on their server that had old TP databases on it and MAXDOP one, so they didn’t have any CXPACKET waits because they had no query [inaudible]. And I was like ooh, these queries sure would really benefit from parallelism.

Brent Ozar: Yes, it’s probably not negotiable, it’s probably just something that BizTalk requires and my guess just based on, because of what the product is, is that it’s constantly doing transactions to say I picked up something from this SQL Server and I’m moving it over to this other, so I don’t think that’s going to be a good time in terms of performance, but I think it’s just going to be a requirement that it just kind of is what it is. If you have more technical questions or political questions or fashion questions, probably not haircut questions among this group [crosstalk] what’s happening here lately – so what we’re doing now, because we’ve hit the exhausted thing of question already, and look at you guys, and holy cow there’s like 70 of you in here watching what’s going on…

Tara Kizer: Nobody had questions, ask questions.

Erik Darling: Is the question button broken?

Richie Rump: Hey Brent, I’ve never had to install a database in the cloud, like ever.

Brent Ozar: Yeah?

Richie Rump: I don’t know what you’re doing here.

Brent Ozar: So you’re watching and learning. See after years of doing this, you grow to learn what parts of the window are the desktop and what parts of it are the setup application. These are the kinds of things you have to do when you still manage SQL Server on premises. Who the hell thought this was a good idea in terms of user interface? This is moronic. Whatever… Do I wish to continue? Yes. Click install to begin? Sure, I’d love to.

Erik Darling: Do you wish to continue living?

Brent Ozar: Yes, I would love to. I do love that SQL Server management studio continues to get better, so that’s awesome and fantastic – Visual C++ 2015…

Erik Darling: What I always wanted.

Brent Ozar: So for those of you who are listening to the podcast and not watching, we’re installing SQL Server 2017, or the preview, CTP2 which just got announced moments ago at Microsoft’s Data Amp conference.

Erik Darling: That’s Data Amp, not Data Dump, we’re all clear on that.

Brent Ozar: Am I the only one who saw Amp and thought of ampersand, like that they miscoded something with HTML? Just me, it was just me. So yes, so feel free to get your…

 

Why don’t column level encryption certs show up in sys.certificates?

Brent Ozar: Ooh so Thomas asks, “do you know why SELECT star from sys certificates doesn’t return certs for column level encryption? I think I have to do a four each to get it to return from everything.” Wow, I’ve never used certs for column-level encryption, have any of you guys?

Erik Darling: No, that’s a first for me.

Richie Rump: Never, no.

Brent Ozar: This is the podcast of crappy answers, I would say that they’re crappy questions but no, dear reader we love you very much.

Erik Darling: So is the issue – because I know that there was another system view where we had a bug. I think it was the database scope configuration where we had to like put an artificial top hundred or thousand in there to get them all to come back.

Brent Ozar: Oh that’s right, yes.

Erik Darling: Yes, so sometimes those system views aren’t your friends. So rather than do a four each, [crosstalk] try throwing like a top hundred or a top thousand on there and see if that brings rows back, because there have been bugs with system views before where things just didn’t show up when you selected data from them.

Brent Ozar: Or slap at least an ORDER BY in there too as well.

Erik Darling: Yes.

Brent Ozar: As you’re watching the screen go by you’re seeing CTP 1.3 stuff, it’s just because SSMS hasn’t been updated to CTP2, so there you go.

 

Should I shard to handle 50k transactions per month?

Brent Ozar: Don says, “our developer wants to implement database sharding…” Oh, he’s been reading hacker news. “I’m trying to determine if it makes sense, we would most likely have around ten databases with 50,000 transactions per month, I can’t see if there’s a value in this” [crosstalk] Yeah, because I’m sharing my desktop I’m going to fire this up. Someone remind me, so how many days are in a month?

Erik Darling: Around about 30.

Brent Ozar: 30, how many hours are in a day?

Erik Darling: 24, so…

Brent Ozar: Right, how many minutes are in an hour?

Erk Darling: I’m going to say 60…

Tara Kizer: 14 40, just kidding.

Brent Ozar: So then we’re doing one transaction per minute. One per minute…

Erik Darling: That’s assuming you do 24 hours a day.

Brent Ozar: Yes that’s true.

Richie Rump: Well that’s an average, we’re just talking an average.

Brent Ozar: Yeah, there could be five every five minutes just all at once, boom, five. I think you could hire – you could outsource this to a small elderly man with a chisel and tablet who could sit there and track the database.

Richie Rump: It feels like premature optimization, talking about sharding.

Brent Ozar: Define that term, what’s premature optimization mean?

Richie Rump:  So it’s when you apply optimization techniques that handle particularly large performance problems when you don’t have large performance problems, you have small problems.

Brent Ozar: Agreed – I’ve seen times when servers get 50,000 transactions per second, let alone not per month there, it might be a little early.

Brent Ozar: Don says, he says, “it would be performance but he can’t justify it, I feel like I’m missing something.” Yes, you need to read the same hacker news trendy stuff. He’s actually not trying to solve problems at your office, he’s trying to get a job over at Tumblr or at Yahoo or something like that.

Erik Darling: Going to write his own framework in Rust and Go and Swift and Julie and whatever else is popular…

Brent Ozar: Serverless, he’s going to be like Richie saying everything is serverless when there’s real servers…

Richie Rump: We need no boxes. The interesting thing about performance is that you could always have a number that proves that you improved something, right, so there should be some sort of hard numbers that you could go to and say hey, by sharding we improved X this amount, so what’s the number you’re trying to improve? Or again, the hacker news thing, because it’s the cool in-thing, we need to shard…

Brent Ozar: Yes, but transactions per minute, one per minute, it’s incredible. Hold on a second, let’s simulate one minute.

 

How much TempDB is being used right now?

Brent Ozar: Joshua says, “Given our strong views on tempdbs being pre sized-out without auto-growth…” I don’t know if we really have that stronger feeling about it, I don’t know if we’re really too passionate about it but, “what is your preferred favorite way to see how much tempdb is used at the moment?” Oh, so where do you guys find how much tempdb is used at the moment? Here comes the tempdb police. Do you use a script for it? I don’t even know if we have one.

Erik Darling: I don’t think that’s built into anything. Whenever I’ve had to like trend tempdb utilization long-term I’ve always used extended events to do that. I have a couple, not a couple, I have a blog post about at least tracking tempdb log file usage up on the website somewhere, but I’ve used similar stuff. Monitoring tools are great for that, you know. Why sit there and hit F5 over and over again or have some script running a WHILE loop when you can have a monitoring tool do all that stuff for you and, you know, give you data and log file usage and pretty charts and graphs and numbers and red lights and green lights, that’s what I’m into…

Richie rump:  Well if you run sp_Blitz first you won’t get any of that information, so that won’t help you.

Erik Darling: Right.

Brent Ozar: I can’t believe we don’t have that in something, like I don’t think it’s in sp_Blitz either, we should probably have that in sp_Blitz, like hey you’re using 90% of tempdb or something like that, that seems like it would be [crosstalk]

Tara Kizer: Could use the GUI reports to see the dist usage report, but the only problem with that – I definitely have used that being a lazy DBA of course, but the only problem with that is it will not show you the version stored, so you could see it completely empty tempdb, and yet your tempdb is growing and growing and you’re like what the heck is going on here, and then you start Google searching it and you finally realize that read committed snapshot isolation is using version stored and tempdb and it’s not shown in that graph, so you have to – you can only run a script to see what’s going on for those, so you have to combine those two things if you’re using RCSI at least.

Erik Darling: Well I mean that’s not the only thing that uses the version stored, you’ve got, you know, if you use certain types of triggers, use, what is it, after-triggers [inaudible] of version stored, so you have to be careful there, it’s not just an RCSI problem.

Brent Ozar: I always forget about triggers.

Tara Kizer: Me too, I was like oh. [crosstalk]

Brant Ozar: Wes Crocket says, “if you suffer from premature optimization, see your doctor, it may be a sign of a more serious…”

Tara Kizer: Be careful here…

Brent Ozar: It’s interesting…

Richie Rump: What’s your doctor’s name again?

 

How do I migrate with minimal downtime?

Brent Ozar: MM says, “we’re moving from SQL Server 2008 R2 to 2014, we got a bunch of large databases, a bunch of 800GB databases and no downtime is acceptable. Our company does a thing where they let us configure and then they take the server back for a few days so the data will be stale…” What on earth is happening here? “Would you recommend changing our current databases to bulk-logged from simple and then restoring our differentials?”

Tara Kizer: No.

Brent Ozar: No, if soon as you restore to a newer version of SQL Server, they’re going to be changing the database in order to attach it in the new version and that will make permanent changes, you won’t be able to restore a differential over the top of that again, you’ll have to re-restore from scratch.

Erik Darling: There’s really no good way to do that, unless you use, you know, like a sort of cheat-y workaround like SAN Snapshots or something, which can, you know, at least provide a somewhat faster [crosstalk] option than, you know, a native backup in the store.

Tara Kizer: I wonder if this person can get away – I know they said no downtime is acceptable, but what about like five seconds of downtime? You really can failover to another server, newer version in a very short amount of time if you have, say database mirroring, you can go in one direction up two versions even. You know, the failover command’s really fast and, you know if [functs] are scripted with everything just F5 that script and it will be super fast.

Erik Darling: Yes, I think the problem with them though is that the company takes it back so they’d have to mirroring back up over again. So I mean that could be like their permanent cut-over solution, but…

Tara Kizer: Yeah.

Brent Ozar: I would just like to point out that – so for those of you who are listening alone and not watching, you’re going to miss out on this, but I can show you the brand new, newest feature of SQL Server 2017, SQL Profiler 17. It’s the same thing, just they named it SQL Server 17.

Erik Darling: It got a new thing, it got a new icon [crosstalk].

Richie Rump: It’s green too Brent, just in case someone asks.

Brent Ozar: Oh I see, I wouldn’t have known that. I will also say that Management Studios installation is slower than SQL Server’s installation, by a long shot.

Erik Darling: Management Studio is like a third of the size of SQL Server, the SQL download’s like two point something GB and the Management Studio download is like 800MB, it’s the most bizarre thing in the world.

Richie Rump: It’s not slower than Visual Studio’s install, I’ll tell you that, I’ll guarantee you that.

Brent Ozar: Oh god, so at first glance, everything looks pretty much the same here. Just for laughs, we’ll go create a database, new database, just to see if we get any new stuff in here… No, it’s still pretty much the same thing it’s been before.

Erik Darling: I like the little wrenches though.

Brent Ozar: Ah that does seem a little new, otherwise everything kind of looks the same. Ooh look at you, SQL compatibility level vNext, doesn’t say SQL Server 2016, auto-shrink is still there, thank goodness, I know Erik you were worried about that, and Tara, just as you were asking, auto-close is still there.

 

Why is a query fast on one server and slow on another?

Brent Ozar: Kyle says, “we’re having a weird performance issue, our prod database takes twice as long to do a PeopleSoft job and it has lots of cursors, but a fresh restore of that to a different instance on the same server, everything seems identical, performance is different.” So is there a time when – or what would cause queries to be different in terms of performance between one server and another, one session or another. It’s that time of the podcast where we bring out that answer…

Tara Kizer: Go for it.

Brent Ozar: Go ahead Erik.

Erik Darling: Oh me, okay, Erland Sommarskog has this thing written called, what is it, Slow in the Application, Fast… I’ve said it so many times it’s like it became unfamiliar to me, like I don’t want to be on autopilot. I’ll post a link over in chat. It’s basically a rundown of all the stuff that could ever possibly be different and cause the same query to have different execution characteristics from one place to another or one run to another. Let me go grab that…

Tara Kizer: I would tell Kyle un-pair the execution plans on the two servers, you’re going to see a difference almost certainly, especially – well this is a restore from production so I would assume at least CS statistics are relatively up to date, but it just depends when the whole backup is from.

Richie Rump: I assume nothing, nothing.

Tara Kizer: What you’re experiencing is likely parameter sniffing.

Erik Darling: Where are you Kyle?

Tara Kizer: And if you need help with it, hire us, we deal with parameter sniffing almost every single week.

Brent Ozar: oh my gosh, because it’s everywhere, everybody has that same question.

Tara Kizer: And it’s complex, it’s a hard topic, very, very hard.

Brent Ozar: The answers are different very often between servers, all kinds of fun stuff.

 

How much drive space should I leave free?

Brent Ozar: Thomas says – and Thomas is feeding us a bunch of questions and there’s others so I should probably hit those others, but his first one’s actually really good, not that the other ones aren’t good, but you know, let’s be honest Thomas…

Erik Darling: Room for improvement.

Brent Ozar: Room for improvement – Thomas says, “what percentage of disk space do you recommend be free for optimal performance on disks with data files?” That’s kind of tricky, I’m going to leave off performance, I’m going to just how much free space, Tara, are you comfortable leaving on drives with data files on them?

Tara Kizer: I mean the companies that I’ve worked at, we have to have 20% free space so that we’re not getting email alerts, so that was our target, to make sure we’re under 80%, that we were not getting alerts from our monitoring tools, but there’s – performance doesn’t matter, you could be 99% full and performance is still the same if it were 5% full.

Erik Darling: Until you hit 100%.

Tara Kizer: I don’t know that performance necessarily goes down, from the user’s perspective it does but, you know, errors are going to start being thrown.

Erik Darling: But those errors come back fast, so that means they’re performing really well, fast performing errors, highly optimized.

 

Is it time to start using SQL Server 2016?

Brent Ozar: let’s see here, Clarke says, “now that SQL server…”

Erik Darling: Excuse me [crosstalk]…

Richie rump: Is that a hell hound you have under your desk there?

Erik Darling: I think – I live on a street with a biker gang, there’s all sorts of [inaudible] and stuff.

Brent Ozar: Awesome. Clark says, “now that SQL Server 2017 is close, would you feel comfortable telling a client to upgrade to 2016?”

Erik Darling: I’ve been telling people to upgrade to 2016 since 2015. [crosstalk]

Tara Kizer: Someone asked earlier to either upgrade from 2008 R2 to 2014, I mean why aren’t they going to 2016, you know?

Erik Darling: It wasn’t even that, it was 2012, it’s just like why are you doing that?

Richie Rump: You know, enough time has passed, I think SQL Server 2012 is worth a look. I mean don’t install it right away but at least take a look at it.

Erik Darling: We’re almost to service pack for 2012 so it’s bordering on stability.

Brent Ozar: It’s bordering on deprecated – pretty old there. I’m amazed by this, so now with the brand new SQL Server 2017, it still calls the config manager SQL Server 2008. Hopefully, someone is writing this down and taking things into account so they can go file connect bugs and go fix it.

Erik Darling: I bet if you go into the ad remove program control panel you’ll still have all that junk from 2008 installed on there too, like the backwards compatibility DLL CLR stuff. That was always the worst part about cleaning up SQL install, like do I need this, what is this?

Brent Ozar: Who installed this?

Erik Darling: There you go, 2008 setup support files, thank god. 2008 C++ redistributables, thank god, what would you do without those?

Brent Ozar: Good stuff, good stuff, garbage. Not garbage, just…

Erik Darling: Trash, throw it in the bin.

Richie Rump: Hot garbage.

 

What do you use for desktop computers?

Brent Ozar: Fayed says, “what’s the configuration of your virtual machine, the installation of SQL Server was pretty quick?” It is an Apple Mac Pro. It’s a trashcan desktop with 64GB of RAM in solid state. It’s nothing, it pales in comparison to Erik’s desktop though. Erik has this thing- it requires a pull-start, you know, it’s got a chord, but once it’s started…

Erik Darling: It does, I was very excited building that and every once in a while this funny smell will come out of it, like this like burning plastic smell and I’ll go down there and I’ll look and then when I go and I look at like task manager, it’s not doing anything, there’s like no activity. Every once in a while – I think it’s just the thermal paste on the CPU every once in a while will just like hit a pocket or something.

Richie Rump: Someone didn’t apply it right.

Erik Darling: Oh I applied it right, I watched like 75 YouTube tutorials on how to apply thermal paste [crosstalk]. It’s like two dots and then a stripe and then a smiley face and then…

Richie Rump: What I love is how Apple apologized for the Mac Pro a couple of weeks ago, we’re sorry it’s so slow, we’re going to fix it, we promise.

Brent Ozar: And it’s so old, no updates in three years. You hadn’t noticed, you people still said it was fast, there you go.

 

How do you tune query plans for cursors?

Brent Ozar: Kyle says, “do you guys have any suggestions to track query plans within cursors?”

Erik Darling: Well BlitzCache will show you that. We don’t do much analysis on the cursor plans themselves because they’re a totally different branch of the XML, it’s a totally different path, so like in the normal SQL Server execution plan there’s a statement simple, and then from there it branches down into like RelOp and all the other stuff that goes on within a query plan. If you look at a query plan with a cursor in it, it does the same thing except it goes from statement simple to statement cursor, then it has this whole other pass. We would really have to do a lot to materialize that second path and do like real meaningful analysis on it, but BlitzCache will pull cursor plans out of the cache and you can still look at them just the same way if they’re in a stored procedure or if they’re ad hoc code, we just don’t throw all the fancy warnings for them, except for the one case where, what was it, Brent wanted that one for the weird cursors, where they’re not forward only and they’re key setter dynamic cursors that make things awkward.

Brent Ozar: Make you go forwards and backwards.

Richie Rump: Next week Paste the Plan will be able to read cursors.

Brent Ozar: What is coming up new in the new version of pastetheplan.com?

Richie Rump: yes, so that’s the big one right, so we can now read cursors. We have new lines that actually have arrows on them to show the flow of data in the plan. We have a download button so you could actually download the plan as opposed to just copying the XML, so those are, I think, the big three. I have a blog post that’s been written, probably going to go out sometime next week and when we do the actual load of it but it’s all done and it all works. There’s a lot more stuff we want to put into it but we do this kind of on our down periods, so…

Brant Ozar: Believe it or not we don’t just sit around in webcasts answering your wonderful questions.

 

How do I find the worst cursors to tune?

Brent Ozar: Let’s see here, Colin says – man how am I picking these questions? Why don’t I read these before I say them out loud? Colin says, “we have a legacy application that contains hundreds of cursors and I’m looking for the worst cursors in order to optimize them. Do you know of any tools that help with that?” I don’t know, I wouldn’t look at the cursors, I would just look at sp_BlitzCache, show me the worst queries.

Erik Darling: Because there’s only so much you can do with the cursors.

Brent Ozar: Right.

Erik Darling: Unless you replace them altogether. I mean if you want advice on replacing the cursors, the T-SQL level-up course has a module on replacing cursors in it, which might be helpful to you…

Brent Ozar: Which is free now if you go to BrentOzar.com and you click Training up at the top, if you sign up for our free level of training, it includes Doug’s excellent T-SQL level-up where he gives you examples of how you go about rewriting a cursor, he has all kinds of animations inside there, it’s pretty slick.

 

Is the How to Triage SQL Server session online?

Brent Ozar: Doug says, ”Brent I enjoyed the presentation yesterday, how to triage SQL Server…” cool, glad you liked it. “Is that session online?” Yes, if you search for PASS DBA Fundamentals, DBA Fundamentals, their user group has it online already over in their YouTube channel.

Erik Darling: Cool, I should go watch that, I need to learn how to Triage [crosstalk].

Brent Ozar: I figured by year three you might be able to pick it up…

Richie rump: Apply a tourniquet…

Erik Darling: We’ll see. [crosstalk]

 

You didn’t answer my question last week…

Brent Ozar: Oh my goodness, J.H says, “what resources do long running UPDATE and ALTER TABLE statements take the most?”

Erik Darling: This question sounds familiar.

Brent Ozar: Was it on Stack?

Erik darling: No, it was last week, there was something about like what would take more resources, an UPDATE or an ALTER TABLE or something like that, it was the same question, I think, it was a very similar question.

Brent Ozar: He didn’t get a good answer from us last week. It’s probably not going to happen this week either then.

 

What don’t you like about AGs in SQL 2012?

Brent Ozar: Tim says- alright, this is I think more talking about versions – he’s building a brand new 64 core one terabyte availability group cluster and they won’t let us go to 2016, we have to stay on 2012. Oh, Tim…

Tara Kizer: I know that you guys don’t like availability groups in 2012, but I mean I set up an extremely mission critical availability group, cluster, span two sides, readable secondaries, it had all the features in place, and I know that that cluster is still in production today and it’s doing just fine, but why don’t you guys like AG 2012?

Erik Darling: The V1 thing.

Tara Kizer: It works great though.

Brent Ozar: Well so like when the primary goes down, the secondaries, the databases disappear from object explorer and that’s the one time when I just want to see how far behind they are, just to run quick queries against them and go see how this is going, it blows their buffer pool too so any read-only queries are then going back and hitting storage from scratch, so if you have…

Tara Kizer: So say it again, you’ve lost the primary and you said the secondary databases go – I haven’t seen that before.

Brent Ozar: Yes, so the thing we ran into constantly at Stack was, the secondaries in other data centers, they lose connectivity to the primary, all the databases simply disappear from object explorer, they’re not readable, any queries that are in flight fail and the buffer pool data is simply gone, so whenever it can see the primary again, then it rehydrates itself from disk as queries are executed and it’s available again, but I’m like hell, you can’t offload any queries to secondary data centers. Now, talk about a first world problem, right.

Tara Kizer: And how often are you having the primary go down anyway? You know, that should be a rare occurrence.

Brent Ozar: Oh, internet connectivity dropped all the time, I mean we would like lose it a couple few times between data centers [crosstalk].

Tara Kizer: That sounds like an architecture issue.

Brent Ozar: Not every company has as much money as your employers do…

Tara Kizer: Well Stack does, Stack does…

Brent Ozar: Well in the beginning – well and plus anybody who’s running enterprise edition, you know, if you’re running, that probably should be up.

Tara Kizer: And then 2014 though, if the primary goes down, the scondaries are still able to continue with their queries and buffer pool and everything’s still fine.

Brent Ozar: Yes, totally good. I think that was my only big beef with 2012 as opposed to 2014. Nobody else? 2016 is the bomb just for all kinds of reasons…

Erik Darling: See [crosstalk] Brent’s dropping out, his VMs working too hard.

Brent Ozar: That’s exactly what it was, CPU…

Brent Ozar: Let’s see, Wes says- a bunch of people have just got things in there that aren’t really questions. Well you know, we’re perfect on time, now’s probably when we should leave. Now we’ll go back to just restoring databases and playing around with SQL Server.

Erik Darling: yeah let’s get out of here.

Brent Ozar: Adios everybody, we will see you guys on the next Office Hours.

Previous Post
SQL Server 2017: query_store_wait_stats
Next Post
Announcing Erik’s First Video Class: T-SQL Level Up, Level 2

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Menu
{"cart_token":"","hash":"","cart_data":""}