[Video] Office Hours 2017/11/15 (With Transcriptions)

This week Brent, Erik, Richie, and Tara discuss Docker, writing to different servers in different locations, SAN Replication, reducing the size of a database, patching Availability Groups, getting rid of failover cluster instances, long-term backup storage options, VLF numbers, 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 – 11-15-17


Why would database mail go out late?

Brent Ozar: Alright, let’s see – we’ll go ahead and get started. Doug asks, “A few days ago, a rogue query…” I wonder if he called it Rogue One… “Caused tempdb to fill up, jobs to fail and notifications to be sent. We figured it out and fixed everything.” I like the way he says this, because it was probably his query, based upon this, “We figured it out and we fixed everything… Last night, the database server rebooted itself to install some Windows updates, and when it came back up it sent several more notifications with the timestamp from the day of the issue. What would cause SQL Server to hang on to notifications and send them after the reboot?”

Erik Darling: My guess would be if database mail couldn’t send, because maybe it’s on the same drive as tempdb or something.

Brent Ozar: That was my guess too.

Erik Darling: Kenneth Fisher, from SQLStudies, I think, he has a blog post about queries to help you troubleshoot database mail stuff. I’ve used that a couple of times. It’s a pretty good post, I’ll stick the ink for it in chat; give me a second to find it.

Brent Ozar: Find it in the show notes.


Brent Ozar: Kelly says, “Training videos question – are the high availability and disaster recovery videos going away? I can still get to the page, but the link isn’t on the DBA training videos page.” Yeah, our DBA training subscription now includes the senior DBA class instead, because they’re my modules. I like the newer ones better, so I like the senior DBA class better. You can still get to the HA and DR, It’s just that we’ve kind of deprecated those, so I don’t push them as much on the current subscribers.


What’s Richie doing?

Brent Ozar: Thomas says, “It’s fun to watch Richie when other people are talking.” I think it’s fun to watch Richie at all times, that’s why I have the webcam installed in his office.”

Richie Rump: Wait, what?

Brent Ozar: Richie, what do you do when we’re not on the webcast? When you code, do you dance around too?

Richie Rump: Well all morning it’s been like this, then like this… And I finally figured it out right before we started, which means I’ll have another half hour of, “Oh my god, what was I doing?”  And then this, and then all this.

Tara Kizer: And that’s due to working in the cloud, right? All the cloud stuff…

Richie Rump: You know, brain works and there’s – you know, what I’m working on, there’s been a lot of code actually written, and so you’ve got this – “Okay, if I want to do this, what needs to change around here?” And then you go off and you do the change, and then you see all the unit tests break, and then you’ve got to go back and fix all of them and create new unit tests and go through that cycle. And usually that takes about a day, if it’s a medium sized change type stuff. Yesterday, medium sized change, it was a full day fixing all that stuff.


What do you think about Docker containers?

Brent Ozar: let’s see – Thomas says, “Have y’all looked at Docker at all, and if so, what do you think about it?

Erik Darling: Not my favorite pants; I don’t like the pleats.

Brent Ozar: The belt’s not bad though. We don’t – so what we end up doing is, for the most part, people bring us in when the SQL Server is on fire, and we have to turn it around in three days or tell them what to do to turn it around within three days. And because we tend to be a little later on the adoption curve – like people don’t come to us with brand new servers, they’re usually in place for a year or two. Docker isn’t – it might be production ready for all we’re concerned with, but we tend to get brought into really high-value stuff; Docker tends to be throwaway projects, like you just spin up a SQL Server and make it disappear.

I would adore it for unit testing sp_Blitz, sp_BlitzCache, stuff like that – if we cared enough to write unit tests, which we don’t.

Erik Darling: That’s not true. I wrote a whole bunch of unit tests for sp_BlitzCache, so as soon as you want some units, I’ll send you them.

Richie Rump: I should be the one that actually knows more about Docker than anyone else, because it’s more of a dev type thing, and I know like nothing. I brought in a couple of presenters down in Miami to talk about it, and at the end of that, I’m like, “I still don’t have a good concept of how this thing works.”

Brent Ozar: And, when it first came out, there were some gotchas around persistent storage, like the idea which you don’t persist data and log files inside the Docker container, you persist them somewhere else, like out on a network; and that can lead to some performance gotchas there as well.

What else do we think about it? I adore it, the idea of containers are pretty awesome, I just usually want the SQL Server to stick around, I want the app servers to just completely rebuild all the time for version testing.

Richie Rump: Yeah, and I’ve been playing with serverless, which is completely like the next phase od Docker-less and containers. So containers is like all running on a server and we just throw all that idea away and we just run it whenever it needs to run, and that’s it. So that’s the main reason why I haven’t really messed with containers, because I’m past containers – I’m on serverless; that’s what I’m talking ‘bout.

Brent Ozar: I want to go off on a rant on serverless. Somebody says this week, they’re like, “Well you wouldn’t call Ubers carless…” And I’m like, “Well actually, you do. If you use Uber all the time, you don’t own a car, and now there’s a car involved, you just don’t have to own it or service it.”

Brent Ozar: Wes says, “I know that Kendra Little uses Docker quite a bit for her demonstrations.” Kendra’s a Mac person, like me, so in theory you would think I would love SQL Server running in a Docker container. The problem is that I really like to show things using SQL Server Management Studio, at which point, if I’m going to run Management Studio, I might as well be running Windows, because I’ve got to run it at this point.

Richie Rump: But now, with SQL… Never mind…


How do we feel about SQL Operations Studio?

Brent Ozar: John Sterrett pops on Twitter and defends Microsoft. He says, “Operations Studio still in preview. They’ve got to start somewhere. I’m sure actual plans will show up soon.” Yep… We’re going to get them next year, right Richie?

Erik Darling: That’s how I feel when I [run] all my queries; I’m sure the plan will show up soon. If I just hang on a little longer…

Brent Ozar: I’ve got to start somewhere. You might as well start with select star from table.

Richie Rump: You’re proving my point here. It takes time for this stuff to come through, and I’m not going to get all excited for something that’s pre-data – sorry.

Brent Ozar: Especially when it’s free, you know. I know what they’re going to come back to, because I already submitted my first feature request through GitHub. I’m sure they’re going to be like, “Great, the source code’s over there, Brent, good luck.” At which point, I’m going to say, “Do you really want me to submit a pull request, because I don’t even know what this is written in.

Richie rump: Is that all c++ stuff? I’m assuming…

Brent Ozar: I have no idea. I would think it would be – I doubt it’s c++ because it’s cross platform. I bet they went something really high level and abstract, like Java. It does feel like visual studio code. What’s that written in? It’s JavaScript?

Richie Rump: Well, visual studio is written in c++, but it could be…

Brent Ozar: The code, visual studio code?

Richie Rump: Oh, code, yeah that’s HML type, JavaScript…

Brent Ozar: I’m Googling. What is visual studio code built on? Electron, GitHub’s Electron.

Richie Rump: Yes, thank you. I know some web thing that they put inside the desktop.

Brent Ozar: The rest of us there, our eyes are kind of rolling back.

Erik Darling: I feel okay with that web thing.


How can I write to the same database in two data centers?

Brent Ozar: Ian says, “My boss wants our applications to read-write through different servers located in different places to the same database. Where could I find something to help me talk him out of it? We’ve already got distributed reads with availability groups, but he wants distributed writes too.”

Tara Kizer: It’s really easy. At one of my jobs, they asked this question – the business asked that question every one to two years, and all the IT department had to do was go back to the SAN vendor, what is the price point to be able to achieve that? And that was it. It was always at least a million dollars. I think the price was like two million dollars that we were always quoted. So it’s a lot of money to be able to achieve what you’re talking about, SAN replication, probably synchronous and it has to be in the same data centers, because there’s going to be latency otherwise.

Brent Ozar: The other gotcha is what happens when it breaks? So if you’re going to write to two different servers in two different places and replication breaks, because it will, it has to. Sooner or later, something’s going to break and you have different data in two different data centers. Is that okay with the customers or not? And if the answer is no, then there you go.


Do you prefer MongoDB or MariaDB?

Brent Ozar: Thomas asked a question earlier when we were talking about open source. He said, “Do you prefer Mongo or Maria?” So Richie, if we put a gun to your head and said we’re going to use a different database – we’re not using SQL Server to begin with, but if we made you choose between MongoDB and MariaDB, which one would you choose?

Richie Rump: Probably Maria. You know, it’s an offshoot of MySQL, it’s more standard and whatnot. It depends on really what we’re writing. I mean, if it’s more of a kind of object type think and NoSQL, then you go Mongo. Otherwise, if it’s more of a relational type thing, you’d go Maria. I still haven’t figured out how to report off of MongoDB yet. No one’s really found it out yet.

Brent Ozar: I should put that – well this isn’t really a family – no, it is a family webcast. I was going to open it just for the Youtube crowd, because I’m like well I don’t know if Youtube would censor us for having – so for those of you who are listening, there’s a classic joke on how do you query a NoSQL database. So just search for, “Did you go tell me to query MongoDB.” Was it MongoDB or was it just NoSQL?

Richie Rump:  I don’t remember.


How should I reduce the size of my database?

Brent Ozar: Dill says, “We have a database about 20GB in size. Our largest table is a heap. That table accounts for over half the size of the databases and the indexes are taking about 4.5GBs of space.”  He writes on for several paragraphs and he says…

Erik Darling: That should have been your first warning. We need to have like a beginning and end metric on here. Like if the beginning of a question and the end of a question are a certain distance apart, they…

Brent Ozar: The punchline of it is, “I’m trying to reduce the size of my database.”

Tara Kizer: It’s only 20GB though. Why are we bothering with this?

Richie Rump: Don’t put files in your databases. No blobs, bro… I mean, come on, man.

Brent Ozar: So these are 32GB thumb drives. I’ve got a handful of them. I’ve actually got a whole bag of them here. There’s probably two or three terabytes worth of USB drives in here. Just go and put another USB drive in the SQL Server and you should be okay.

Erik Darling: I’ll sell you a really cool thumb drive…

Brent Ozar: Wow, that’s got to be full of viruses.

Erik Darling: You have no idea. This thing is awful.

Brent Ozar: When you picked it up, I thought it was going to be a dental floss. That’s what it looked like.

Erik Darling: You could write your backups to this tape.

Tara Kizer: At the end, Dill wonders why the database size decreases after he shrunk it, and it’s not going to decrease because a heap is the table and the clustered index is the table. The size isn’t going to change here. I mean, maybe a little tiny but, but this isn’t for size reasons. Switching from a heap to a clustered index pertains to – okay, go ahead…

Brent Ozar: there can be, though, because heaps don’t de-allocate empty pages when you do deletes, but that, in most cases, is such a tiny percentage.

Tara Kizer: Switching from a heap to a clustered index is more about is your system high writes or high reads, are you having problems with forwarded records and deletes. Most OLTP tables, or really all OLTP tables, in my opinion, should have clustered indexes. It’s staging tables, ETL type stuff where you may want to have a heap.

Richie Rump: Or if you kind of know your data patterns. If you know you’re doing only write only, you’re not doing a lot of reads against it, then…

Tara Kizer: That’s what I tell my clients when I’m looking at BlitzIndex output in the heaps and there’s extremely high forwarded records, you know. They ask should we rebuild the heap or should we add a clustered index? And that’s when we scroll to the right and the BlitzIndex output and we look at the usage, you know, is it heavily writes compared to reads? And if it is heavily writes, then yeah, just go ahead and rebuild your heap on a regular basis.

Brent Ozar: Michael says, “I work with an ERP system that is all heaps, 12,000 plus tables.” SAP, I’m going to guess.

Tara Kizer: Oh, is SAP all heaps?

Brent Ozar:  I think so. I think SAP is all heaps. Man, it always seems like the bigger the system, the more Enterprise-y, the worse the database is.

Tara Kizer: And I recently had a client that was using SAP’s Business One, which is completely different that SAP. You know, SAP supports RCSI, Business One does not. There were a lot of issues with Business One, and it all looked like it had been around since SQL Server 2000 days. I mean, just some bad stuff in there.

Brent Ozar: Michael follows up with a name. He says, “Don’t say it publically, but it’s Acme…” No, just kidding.

Richie Rump: Michael just went, “Nooooooooooooo…”

Brent Ozar: It’s [Joris] Business One…

Richie Rump: Dude, he’s the guy that bought it.


Should I put a max size on the log file?

Brent Ozar: Let’s see, Lee asks, “I have a vendor that put a max size on their transaction log file…”  I shouldn’t be laughing, Lee. “I am wondering what kind of problems this could cause for our SQL Server. We just got it installed a week ago, so no problems yet.”

Erik Darling: Well what’s the max size on it? That is my first question.

Brent Ozar: I don’t have the answer, I’m sorry.

Erik Darling: Well I know you don’t.

Brent Ozar, He says 90GB.

Erik Darling: Oh, well, if you run out of that 90GB then you’re going to start seeing some funny errors.

Tara Kizer: You’re down.

Erik Darling: Maybe it’s to keep you honest. Maybe it’s to keep you taking log backups, I don’t know. That would be a fun way to put a gun to someone’s head and say, “No, you take log backups. If you don’t, nu huh buddy, don’t you rebuild that index, you only have this much space. If you go over this, that’s it.

Tara Kizer: I would wonder, you check the size of you biggest index and make sure that you’re not over 90GB because if you try to rebuild that guy, you’re definitely going to run out of space there.

Brent Ozar:  And, whatever you do, right before you go on vacation, don’t type BEGINTRAN, lock your workstation and go home for the weekend; that would be bad.

Tara Kizer: I would be nervous about having an application where the vendor put that in place, because I wonder about their best practices and how they set up the server. I would start looking at your SQL Server instance settings and see if everything’s at the defaults.

Brent Ozar: I have a bad feeling that somebody, one of their customers, filled up a drive once and so this was their answer, “Well we can’t ever let that happen again.


Are Availability Groups ready for prime time?

Brent Ozar: Thomas says, “I’m migrating three instances that are currently on a cluster. I’m not allowed to upgrade from 2012 yet on these three. Are availability groups ready for primetime with the latest service pack and cumulative update?” Oh my goodness, I think he’s talking about 2012. 2012 availability groups [crosstalk]… Yeah, so Tara’s our availability groups person, by far…

Tara Kizer: All of my availability groups are on SQL Server 2012, and we did not run into the issues that you were about to mention. I can’t remember what those issues were. But doesn’t one of our things say that if you’re going to use availability groups, to start on 2014 or higher, because of something with 2012. Whatever it was, it was like – I ran fairly large systems on availability groups on 2012 and did not have issues with it. I know Stack Overflow did; that’s different.

Brent Ozar: My only thing, and this is kind of a personal preference, my only thing is when you lose the primary, until another primary comes up, the databases disappear from object explorer. You can’t query them on any other replica, so your readable queries just fail. Now, is that a showstopper of a deal? Not really. If you’re just using it for failover, you’re going to bring up a secondary anyway, so it’s not such a big deal.

Tara Kizer: And the readable queries are only down for say 30 seconds, because these failovers are quick in AGs, so maybe even 15 seconds. [crosstalk] As long as it’s automatic, if it’s an automatic failover, yes.

Erik Darling: I think the bigger deal for me would be which Windows OS you’re on. Like I wouldn’t want to be on Windows Server 2008 or whatever…[crosstalk]

Tara Kizer: You say that, but that’s what my systems were when I first deployed; and still, we were okay. Now my last job, we were on Windows 2012 R2, I believe, for those availability groups. But I would wonder for Thomas’s question, why migrate off the cluster? What issue are you having that you want to get rid of the failover cluster instance here? For me, I wanted to get off of it because my environment that first went to availability groups, I wanted to get rid of transactional replication, which we were using for reporting, and hey there’s this new feature called availability groups. It offers HA, DR and a readable secondary. And I was like, “Oh, I have failover cluster instances, I had asynchronous database mirroring for the DR and then I have transactional replication for reporting.” So for me, I was like. “Three features gone and one of them is transactional replication.” So what are you trying to solve by migrating off FCIs?

Brent Ozar: He follows up by saying his hardware’s going out of warranty on the cluster.

Tara Kizer: I would stick with FCIs, honestly.


Should my VMs only have one core?

Brent Ozar: let’s see here, Samuel says, “I am trying to convince our sys admins to build our production SQL Servers with more than one core as standard. They want to build with one core and increase CPUs as necessary. In your experience, what would you…”

Tara Kizer: One core? Don’t most laptops have more?

Brent Ozar: This is actually what VMWare’s best practices recommend for running SQL Server…

Tara Kizer; That was a long time ago though, wasn’t it? Is that still?

Brent Ozar: No, it still is. And, the most recent Virtualizing SQL Server book that we reviewed on our blog said the same thing.

Tara Kizer: So obviously these aren’t Enterprise Edition servers, because you can’t even license less than four quarters on Enterprise Edition.

Brent Ozar: If you license by the host you can. So you license the host and then run all the… I know, I understand where these VM admins are coming from, it’s just that they’re wrong.

Tara Kizer: I would run my stuff in the cloud if I only need the one core. I mean honestly, why even host stuff for such small stuff these days… [crosstalk]

Erik Darling: Docker, I don’t know.

Richie Rump: I guess my question would be, one, are they living in a state where marijuana is illegal? Two, have they been drug tested recently? And three, do they smoke it as they’re sitting in their cube? I don’t know, but it doesn’t seem right.

Erik Darling: They must be like mining Bitcoin with all those other CPUs. They must have a side hustle on that server if everyone’s just getting one core; that’s absurd.

Brent Ozar: I would just ask them, do you expect the servers to be unresponsive during backup and CHECKDB? Because I want to be able to keep taking backups and running CHECKDB without the entire server falling over.


What do you recommend for long term backup storage?

Brent Ozar: Dorian asks, “What do y’all recommend for long-term backup storage; Disk, tape or the cloud?” I recommend that the sys admins handle that. I suck so bad at that.

Erik Darling: I don’t have a recommendation of one or the other. I would just say, as long as it’s redundant – like however you can make it redundant, that’s what I would prefer, because you don’t want to lose one and then be out all your old backups.

Richie Rump: Do they still have zip drives? Are they good for backups?

Brent Ozar: Super stable, yeah. [crosstalk] When you hear the clicking, that means they’re working.

Richie Rump: “Click, click, click, click – everything’s fine with the drive…”

Brent Ozar: Oh we’re old.


How do you patch Always On databases?

Brent Ozar: Sri asks, “How do you patch always on databases?” So Sri, just to be careful, I’d be a bad person if I didn’t mention this: call them availability groups databases. Just because there’s people out there who are kind of argh about spelling and all that. “Do you go through a Windows update, or do you just download and install and use the cluster services?” How do you guys like patching availability groups?

Erik Darling: I don’t.

Tara Kizer: It’s fine. It’s fine, just failover and patch, and whether or not you use Windows update or something else, that’s dependent upon your organization. Some of my organizations, they did use windows updates, sometimes we’d use system center, whatever it is, SCCM. And it would pre-download the updates on the server and then I would just have to click on the little bubble that would appear and install it and do my reboots and everything. So yeah, whatever your organization uses for Windows updates is fine by me.

Brent Ozar: I just wouldn’t push them out automated without a human being there, because they have no respect. They’re like the honey badger, they don’t care for backups running, they don’t care if CHECKDB is running.

Tara Kizer: You can get sophisticated – you guys aren’t going to like this answer – but you can get sophisticated with patching and use PowerShell to be able to determine – somebody launches a PowerShell script and does all the failovers to one node, launches the patches on the other node and does the reboots and the failovers. So we were working on them at my last job, it had gotten deployed fairly, not too soon after I left; so I’m sure they’ve got that all squared away. So you can get pretty sophisticated with patching.

Brent Ozar: I like that answer because someone else is doing it.

Tara Kizer: Yeah, I didn’t do it.

Erik Darling: It’s all automated.

Tara Kizer: I think we had in place for like maybe three months, and whoever was on call when the patching was happening, that’s who would have to click on it. I was like, “Please don’t break.”

Erik Darling: “Someone’s on PowerShell? Cool, I’m drunk; unavailable.”

Richie Rump: Those PowerShell people are kind of like crossfitters and vegans. You know that they use PowerShell because they’re going to tell you, right.

Erik Darling: They’re all about it.

Richie Rump: We could solve global warming with just a little bit of PowerShell.

Brent Ozar: It’s like the worst Tinder date ever. You know, you’re swiping through and you see a vegan crossfitter who works with PowerShell.

Erik Darling: Like throw atheist in the mix and you’re just never going to hear the end.

Brent Ozar: Dating advice from Brent Ozar Unlimited: avoid the vegan PowerShell crossfitters who don’t go to church.

Erik Darling: Avoid people with beliefs, really. As soon as someone has beliefs…

Richie Rump: I mean, at that point do you just delete the app, or just throw away the phone? I mean, whichever is easier, I guess.

Brent Ozar: I told you to never right swipe me here.


How many VLFs is too many?

Brent Ozar: Jim says, “I’ve got databases with VLF counts ranging from 65 to around 1000. What’s a good number of VLFs and what’s a bad number of VLFs?

Tara Kizer: A bad number is when the number is really high.

Brent Ozar: And what is really high?

Tara Kizer: I can only tell you what I experienced when I actually learned about VLFs. So this is about ten years ago doing Microsoft patching every month for that company. And every time – I had to reboot a lot of servers, so I would just connect to management studio and see if the instance was up. Then I would move along to the next server and about 15 minutes after I rebooted this box and I verified the instance was up, the app seems like, “Oh we can’t connect to the box still.”

So I went to object explorer and looked at the databases, and in horror I saw that this database was in crash recovery still. And I looked at the error log, and here it is, the crash recovery, phase one, 30%. You see all these logging messages, and this system happened to be an extremely critical system that had five nines of availability SLAs. And we were in a maintenance window, so it was kind of okay, but it was not expected for that kind of downtime in a maintenance window. But it took 45 minutes. I was on my hands and knees, praying to the crash recovery gods and I was really close to opening up a case with Microsoft, but I kept hesitating because even for, what’s it called, a CRITSIT these days? A CRITSIT, it takes an hour before you speak to someone. I was like, “I hope this thing is up before they answer the phone, you know.”

So this was like at night, and on Monday morning I contacted our Microsoft engineer, our PFE, and he’s like, “Oh what is DBCC LOGINFO show?” And woo, 45,000 VLFs on this database. This was like a 7TB database and I think that probably the auto-growths were fine at some point, but in the past they had been really small. So it got into this situation, and so once you fix it, you’re good to go, usually, as long as you fix the auto-growth. So 45,000 is a bad number.

Erik Darling: I ran into kind of a mirror situation where I was trying to restore a 5TB database that had about a 500Gb log file. But apparently, all the VLFs were like the biggest possible VLF that you can have, so that took a really long time to read through too. It’s not just the number of VLFs, it’s the size of the VLFs; there’s a whole lot of stuff to think about.

Richie Rump: [crosstalk] I remember when I was backing up a database – no, I never did any of that stuff. No, doesn’t ring a bell.

Tara Kizer: As far as numbers, I can just tell you from experience of, you know, looking for bad VLF databases, that if your transaction log file is around about 100GB, you may be able to get away with 200 VLFs, just based upon the shrinks and auto growing back out, but there really is no number. But 200, I wouldn’t use that as my threshold unless all of my transaction log files sizes were really small.

Brent Ozar: I think in sp_Blitz, I want to say we warn you at 1000, but even that is just to give you the heads up of, “Hey, you should consider doing something about this at your next convenient time.” It’s not even that urgent.

Tara Kizer: Yeah, I actually had a recent client that had 75,000 VLFs, so that was one of their pain points was to ask about – I was like, “Oh, I know this topic. I can tell you exactly…” Because crash recovery was taking 30 to 45 minutes, I was like, “Oh I’ve experienced this. I know exactly what that is. I can help you.”


Can high CPU use cause a cluster failover?

Brent Ozar: Kush asks, “If you have a two node active passive failover cluster, AKA a single instance failover cluster, does high CPU use on the active node cause a failover?”

Erik Darling: Never seen high CPU use do it. I’ve seen out of memory do it. I would imagine that there’s some crazy erase condition when CPU gets pegged at 100% that would cause a failover cluster node to say, “No, let’s not do this here anymore.”

Tara Kizer: I wonder, because I’ve been called on clusters that were pegged at 100%, even after an hour. And this was like on a Saturday, that’s why I remember it, because it’s my personal time. And it was still pegged when I logged in, so I don’t know that there’s anything – because after an hour, you’d think if there was going to be a failover, it would have happened by then. So I don’t think that high CPU is one of the triggers.


Should I run 4 tool versions on the same machine?

Brent Ozar: And then last one we’ll take, let’s see, Joe says, “I have management studio 2017, management studio 2014, SQL Server Data Tools 2014 and SQL Server data Tools 2016…”

Erik Darling: Why is this the last one?

Brent Ozar: “Should there be any issues if I am careful upgrading my code? Will I run into any problems?”

Erik Darling: Not if you’re really careful and you have a lucky rabbit’s paw and it’s next Tuesday on Mars and… I don’t know, you hit a homeless guy twice on the way to the office…

Brent Ozar: hold your left leg out of the window…

Tara Kizer: At my last job, we actually had to have machines like this because we had SSIS packages and we had SQL Server 2008 servers and 2012 servers. So we had to install various versions of thee data tools, and not necessarily different versions of management studio, but the data tools, visual studio – and it was – sometimes people would have the wrong visual studio and it would work on their machine but not when they deployed it to production. So you basically needed a chart to tell you, on this server, this SSIS package needs to be used with this visual studio and data tools. It was a problem.

Richie Rump: I’m so glad I’m out of the SSIS game. It’s the worst.

Brent Ozar: And two words for you: virtual machines. Build VMs with each one with its own different version. [crosstalk]

Richie Rump: Well, nowadays you’d just say build the Docker container, right, that has all that stuff on it.

Brent Ozar: You might say that.

Richie Rump: I’m just saying that. I’m supposed to say that, I’m a dev, I’ve got a contract saying I’ve got to say this kind of crap.

Brent Ozar: Enjoy your Docker pants. Alright, well thanks everyone for hanging out with us. Adios everybody.

Previous Post
Updated Stack Overflow Database Dump Importer v1.4
Next Post
Announcing the Free First Responder Kit Power BI Dashboard

4 Comments. Leave new

  • Brent said: “there can be, though, because heaps don’t de-allocate empty pages when you do deletes”
    Nope – that’s not true, mate!
    A heap will definitly de-allocate pages if the table gets locked. This will happen in two scenarios…
    – The delete operation will escalate to a TABLOCK (all deletes AFTER the escalation get deallocated)
    – You use TABLOCK to prevent reading the IAM by other processes
    I’ve blogged about it here:

    Wouldn’t it be a great opportunity for you to learn german?
    See u in London 2018…

    Best from Frankfurt
    Uwe Ricken

    • Uwe – yep, however, most people don’t use TABLOCK hints when they do deletes, and most people don’t hit lock escalation during deletes.

      I happen to demo it in next week’s Mastering Index Tuning class next week online, too – I load a heap and then delete batches out of it, and show that it still holds gigs of space, and takes time to select count(*) even though the result is 0.

      You’re always welcome to join, but you don’t need to learn English – your English is already better than mine. 😉

      Looking forward to seeing you again, man!

    • Uwe – if you’re using RCSI or SI, not even table level locks cause space to be released. Fun!

      I’d love to learn German if you’re giving lessons!

      • Hey Erik, you are welcome. When you are in London in 2018 I’ll give it a try 🙂
        RCSI and SI makes sence, right.
        Because other processes need to read from the version store.
        Due to the fact that Heaps are doing an allocation scan it makes sence not to delete the pages because the 2nd process will read the IAM.
        I will be on a short term vacation this weekend and will try it out.
        Thank you very much for pointing me to this issue. This was “unexepected” and not on my focus 🙂

        With reference to you I will change my blog post after the tests.

        Best regards from Germany, Uwe


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.