This week, Erik, Tara, and Richie discuss disaster recovery, Python vs C# for SQL Server, whether you should perform log backups on primary or secondary on AGs, why Postgres is so popular, using mount points for tempdb, attention events, database corruption, MERGE statements, restoring a table from a database, features they look forward to in SQL Server 2019, and how Santa is able to visit all those homes in one night…

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.

If you prefer to listen to the audio:

Podcast: Play in new window | Download

Enjoy the Podcast?

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

Office Hours Webcast – 2018-12-19

Can I reset the SA password?

Erik Darling: Josh asks, “I’ve come across an older database and I need to perform some maintenance tasks on, but the documentation we have mentions the only account is SA in this database.  I did not know the SA password. Is there any way I can reset the SA password or generate another account with sysadmin role? My senior DBA created this database a while back, but he has since retired, leaving me looking for advice on where to even start correcting the issue.

Tara Kizer: How are you even connecting in the first place? You must be using your Windows account and probably have sysadmin, so you’re good to go. But I would definitely add another SQL sysadmin account, just as a backup in case something happens with your Windows account. And then maybe plan for an SA password change. I would see if anybody’s logging in with SA, because maybe the application isn’t changing that password, obviously. Not a good idea if the app is using it.

Erik Darling: You know, what I’d do is go look under the old DBA’s keyboard for the sticky note that he has all his passwords on. That’s what I’d do because we know they’re under there. We know they’re there somewhere.

Richie Rump: So where do you guys stand on the SA account? Do you keep it or do you replace it?

Tara Kizer: One of my jobs that was really into security renamed it, but that doesn’t really change anything because it’s still the same SID, but it was renamed. I’ve never gotten to a point where I’ve just disabled the account, but it’s always something like a 20-character password that we just throw into LastPass or whatever and then nobody is supposed to use it. It’s just our backdoor entry as a just in case.

Erik Darling: Yeah, there’s been some weird cases where applying CUs and Service Packs have failed if SA account either is renamed or disabled. Even if you want to follow really good security practices and then you want to patch your software so it’s more secure, you have to do something unsecure to be more secure. It’s a weird tail chase on that sometimes.

Will adding filegroups help with disaster recovery?

Erik Darling: Alright, let’s go to the next one, which I haven’t read yet, so who knows what surprises will be in here. Brenting this one hard, “We have a database that is approaching 1TB in size. Twice we have had to perform DR to recover something that was deleted and the process to restore the database takes about four hours. The developer in charge of this department is requesting that we split out this database into file groups in hopes that this will help with DR. Is this possible? Do you have better ideas?” My number one idea would be to stop giving people access to this database where they can lose data. That would be the first thing I did because if you have – let’s see, what’s a nice word – jack somethings. Let’s just call them donkeys. If you’ve got some donkeys on your team who go around running queries without where clauses, running faulty updates and stuff like that, then they shouldn’t have access to this data anyway, especially if they’ve caused this many problems this quickly. With this kind of business continuity issue, where it takes four hours to restore a database to get some stuff back, I would ban them permanently from the database.

Tara Kizer:  I was just wondering what they mean by DR. I mean, it stands for disaster recovery, but did they failover to the disaster recovery site, ro do they mean this is a disaster, let’s restore right here…

Erik Darling: Well it sounds like they had to restore the database to somewhere, maybe another server or something, and then recover the data back to the original server, perhaps.

Tara Kizer: So I am a donkey and I have done a delete without a where clause on accident. I was able to do a side by side restore because I had the storage available to do it, and then I was able to move the data over. So making sure you have transaction log backups and taking a final tail log backup, that way you can potentially do this without data loss. However, as data is coming in while you’re doing this process, a side by side process at least, you could be mucking with things. You might have some foreign key issues even though there’s no telling. I was luckily in a table that I did this oopsy on that it didn’t have foreign keys anywhere and there wasn’t much risk on this table.

Richie Rump: I did that once where I didn’t have a where clause in the user table. The table name was users. So yeah, that was not fun. And since then, I’ve been paranoid with deletes, just paranoid. Which is good, I think you should be paranoid when deleting stuff.

Erik Darling: Changing data in general should be…

Tara Kizer: Exactly, and I’m sick of that as a production DBA because, usually, we’re changing data because of application bugs. And developers, oftentimes, will know what the bug is, or maybe they don’t, but if they do, the business hasn’t given them time to fix it. They’re all, let’s move forward with all these feature requests and these other critical things, but not this. So I’m stuck having to manipulate production data, and there’s room for human error. File groups certainly helps out with this situation because you can do restores of a file group at a time, but I don’t know that I would implement file groups because of this.

Erik Darling: Yeah, that seems like a weird step to take, especially given some of the pain that you might find in creating all these files in file groups and moving data over to…

Tara Kizer: Moving data, especially if it’s 1TB in size and you’re moving things around. Wow…

Erik Darling: That’s not a trivial task, even if you’re lucky to have the online rebuilds. You’re looking at a pretty tremendous amount of work. If your developer has a really quick and easy plan for that – oh yeah, just add file groups, it will be fine – go ahead, that’s your weekend then, pal. That’s not my weekend.

Richie Rump: So what would be some reasons to add file group type things?

Tara Kizer: Well, I mean, this is one of them.

Erik Darling: You listed all exactly one of them. I mean, aside from if you have a single file which is approaching the limit, which is what, 16TB. So if you have one file approaching that limit, then it might be a good idea to start adding in some more, but then you’re still looking at moving crap around.

Tara Kizer: Or, you know, if you have some disks that you want to leverage for this table, faster disks or slower disks or read only file group type things. But usually, we start thinking about those before we hit 1TB. Once you gte into the VLDB sizes, oh boy, it’s tough.

Erik Darling: Yeah, I mean, there’s a lot of choices that need to get made early on when you’re worried about a database scaling. You know, stuff like use bigints, get used to developing with an optimistic isolation level, get into compression, just general things that are really good ideas if you’re worried about a database getting really big and scaling up in size really quickly. So file groups are sort of part of that picture. Mostly, I would just whip my developers until they stop losing data.

Should I use Python or C#?

Erik Darling: Here’s a good Richie question. It’s not a good anyone here question, “Does Python have any benefits over c# in Microsoft SQL Server in particular…” come on, let’s just keep it to SQL Server. So, Richie, what do you think for interacting with SQL Server, c# or Python?

Richie Rump: It depends what you want to do with it. So if you want to write functions and then throw it in a DLL and then put that in SQL Server, then c# is the way to go. So that’s where you do the .NET DLLs and all that stuff. If you want to get into more of the data science stuff, the more new stuff that SQL Server is kind of getting its toes into, then Python would be the way to go with that. So it really depends. I think nowadays, more so the data science stuff is getting kind of hot, there’s a lot of people trying to get into it, Python is absolutely the way to go. Actually, I had a little app that I created that took some data and then plotted it on a chart. It was for my daughter’s soccer team. And I told a friend of mine, check this out, I went ahead and did this chart. And he asked me, well did you do that in D3, which is a JavaScript thing, and I said, no I did it in c#. And he’s like, well why would you do that in c#? And I’m like, well because it’s easy and I know how to do it in c# already. So that just goes to show you that people are thinking past c# for some of these other things. And it’s weird for me to think of c# as kind of a legacy thing, but it’s almost getting there because it’s been there for so long. But Python is definitely some of the new hotness and if you want to do more of some of the data science thing, it’s probably the way to go.

Erik Darling: Does c# offer anything for data science people, or is it completely just offloaded to Python and R and MATLAB and whatever else?

Richie Rump: Well, I mean, all that stuff is kind of not really focused on that mathematical stuff. You could kind of do all that stuff in c#, it’s just probably going to take you more lines of code to do that sort of thing. So once you – I have this theory on languages; once you learn one and the basics of how to program a language, jumping to another one isn’t as hard as learning it from scratch without having any programming knowledge.

Erik Darling: Unless it’s JavaScript.

Richie Rump: Well, I’ve worked with JavaScript for a long time, so it’s not hard for me. I mean, I kind of get it. We use it here. ConstantCare is all JavaScript.

Erik Darling: Yeah, I know, I’ve seen it…

Richie Rump: How can you see it? I ought to block your account from any of the source code.

Erik darling: I can’t do anything with it. I’m not even smart enough to break it.

Richie Rump: You know, each language has its own kind of flavor, what it kind of does best. Python is kind of the data type thing; c#, I think, is more of a Windows type environment great general-purpose language where you can do a lot of different things pretty quickly and pretty easily. The really hard part about c# is learning the .NET framework. The .NET framework is vast, it’s long in the tooth. There’s a lot of things going on in there and even the new one that they have, the .NET core, there’s a lot that has changed in three versions going on there.  So I could talk long over this, but this is not a programming podcast.

Erik Darling: Notice that Richie didn’t say PowerShell once. Just throwing it out there…

Richie Rump: It’s not a real language.

Should I do my backups on the primary or the secondary?

Erik Darling: So here’s a good Tara question. So we had a good Richie question, here’s a good Tara question, “In an Always On Availability Group, do you recommend performing your log backups on the primary or secondary and why?”

Tara Kizer: I do my backups on the primary because there’s no latency there on my backups. I don’t want any backups to have even a few seconds of latency in case there needs to be some kind of recovery there. Even on a synchronous commit secondary replica, there can be latency. The secondary can switch to async without even telling you to do some kind of issue. It’s documented in Books Online MSDN. So I don’t offload that task because backups aren’t causing me any problems on the primary.

Erik Darling: I hear that.

Why is Postgres so popular?

Erik Darling: Let’s see here, Graham asks kind of an interesting question, perhaps a bit religious, “Why is Postgres so popular, aside from the fact that it’s free?”

Richie Rump: There you go. That’s the reason. You just answered your own question. Thank you very much. I mean, I guess more of the question is why is MySQL so popular, because MySQL has more stuff that will remove data and all this other crazy stuff and settings you have to make sure are right. So why is MySQL so popular? Postgres is more along the lines of an Oracle or SQL Server with more enterprisey features, but MySQL has a lot of stuff that makes us cringe and stuff that’s kind of why would you want that in a database. And the answer to both is, free.

Erik Darling: Yeah, if I had the power, I would classify MySQL as a super fun site. That would be the first thing I would do, just get it walled off, dig a giant pit, throw it in, get some toxic waste in there. Postgres is far more feature-rich, far more dependable, reliable, as a database. And for the price, you kind of can’t beat what you get in return for it. I also think that it’s fairly nifty that you can go and read through all the source code. So if you run into an issue, you can be like, oh that’s where it is. You can contribute changes to it, and in a way, I feel like that’s a lot more interesting to a certain group of people who don’t just want to impotently raise the user voice item and wait for someone from Microsoft to maybe get around to fixing it.

Richie Rump: [crosstalk]

Erik Darling: Yeah, exactly.

Richie Rump: I mean, going back to MariaDB, or MySQL, MariaDB exists because of MySQL’s failings in a lot of ways, so I don’t know why MySQL came up, but I have just so many problems with MySQL, core problems.

Erik Darling: It was like barely a notch above SQLite, but missing basic stuff that linguistically, as far as the optimizer goes, like certain join types were not supported. It was just like, holy cow, what are you doing?

Richie Rump: Yeah, and not to say Postgres doesn’t have its failings, because there’s a fair amount in there.

Erik Darling: Like vacuuming…

Richie Rump: Or single threading and some of the query processing and stuff like that.

Should I use mount points for TempDB?

Erik Darling: Alright, Josh asks, “For tempdb, we are using mount points instead of drive letters or local storage due to decisions made by others previously. Are there any downsides to doing this or any concerns?”

Tara Kizer: There is a downside and I can’t remember what it was, but we were suing mount points directly off the root drive. And so we’d use F-backups, F-data, F-log, F-tempdb. And we had no issues with it, but Microsoft, when they did an assessment of our servers through whatever you get with the PFE stuff or whatever it is, we were notified that you’re supposed to create a folder first, and then have the mount point underneath that folder. But we never ran into an issue, but there was some sort of possible issue that could be encountered that way. But as far as using mount points in general, totally fine. I mean, I’ve been using SQL Server and Windows a long time, and back in the day, we didn’t get to have mount points and we had a lot of instances on failover clusters and the possibility of running out of letters in the alphabet. So mount points helped out with that.

Erik Darling: I’m glad I’ve never run into that problem.

Tara Kizer: Well, I mean, we were only notified of the problem; we didn’t run into the problem… Oh, the drive letter thing?

Erik Darling: Yeah, like, thankfully that’s a lot easier of a problem to detect than running out of ints or – it’s like, oh, we’re at Q, shoot…

Richie Rump: Is that where some Star Trek character comes up and starts randomly annoying you?

Erik Darling: I hope so, because it’s not a weird night without that.

Tara Kizer: I supported a four-node cluster with 11 instances on it back in the day.

Erik Darling: I don’t like the way that maps out.

Tara Kizer: I know, I think six of them were production and five of them were staging and they wanted to have the staging on the same cluster, but we had gotten up to the letter P, I think it was, in the alphabet… I know, it wasn’t what I wanted to do…

Richie Rump: P for poor decisions.

Erik Darling: Alright, I got some other fun questions around, “What would lots of attention events and a profiler trace during a bulk insert indicate?” I don’t know. Got me on that one.

Tara Kizer: from what I remember, attention events can relate to a timeout from the application.

Erik Darling: Well, I hope it’s not too closely related.

Why isn’t my restore working?

Erik Darling: Alright, Joseph asks, “My database backup does not restore. When I try to restore the header, it shows incomplete. Does this mean the backup is corrupt? What could be the possible reasons for it to be corrupt?”

Tara Kizer: Oh yeah, it‘s corrupt.

Erik Darling: Throw that one out. Time to take a new one, format…

Tara Kizer: Fix your disk, whatever’s going on there. You’ve got some kind of, probably, disk corruption. I would be running CHECKDB if you have any databases running on that same drive.

Erik Darling: Backup to something more reliable, like wet paper, you know, lots of choices there.

Tara Kizer: Also, run restore header only to see what kind of error it has.

Erik Darling: Or restore file list only. Maybe there’s multiple files in there and you’re getting it the wrong one or something like that.

My MERGE statement takes 28 minutes…

Erik Darling: Steve says he, “Started a new job and there are a bunch of merge statements that take 28 minutes. What would you do?”

Tara Kizer: I’d stop using merge, I mean, period, end of story.

Erik Darling: Yeah, Aaron Bertrand has many good articles about merge and the problems with it, but I have to say that some of my favorite articles are written by a guy named Michael Swart. Let me see if I can get to the internet. I started typing in Michael, instead of Internet Explorer where I was going to type Michael. I’m on my VM where I don’t have Chrome installed.

Richie Rump: What?

Erik Darling: What am I supposed to do?

Richie Rump: That is weird, man.

Erik Darling: Why is that weird?

Richie Rump: Even Microsoft said, no, we’re going to go to Chrome.

Erik Darling: I read about that. There we go, so Michael J Swart has a great post about UPSERT patterns and antipatterns, and this is because he’s written a bunch of other posts about how kind of god-awful crappy merge can be and ways he’s gone around that where, you know, merge is an antipattern. And when you finally get through all of the antipatterns that he’s discovered over the years, there are some better ones where you go to the patterns and does some begin and try and some other things. So I would really just run screening for merge in general. I personally much prefer to do either insert or update, you know, figure out of the date is there. If it’s there, update, if it’s not there, insert. Merge is just syntactical sugar for a bunch of garbage that Microsoft stopped working on years ago because there were just so many issues with it that I think they just kind of gave up too. Merge, what garbage.

How can I restore just one table?

Erik Darling: Brandon asks, “What is your process if you have to restore one table from a database? Do you restore the whole database and pull out what you need, or is there secret tips?” There are third-party tools like Quest LiteSpeed that can do object level restores. SQL Server used to have it but it got yoinked. I think it’s probably one of the most requested features, to be able to do object level restore. But right now, you need to use third-party tools in order to do it, otherwise there’s no other good way to do that.

Tara Kizer: We used to have that functionality way back in SQL Server 6.5.

Erik Darling: It’s hard to believe that they were able to achieve such a technological feat then, and then they ceded all work on that to people who were, I don’t know, developing other tools and other backup methods. I don’t know, weird all around.

What are you looking forward to in SQL Server 2019?

Erik Darling: Michael says, “Merry Christmas…” Merry Christmas to you too, Michael, I think, maybe, if you’ve been a good boy this year. “Is there anything in SQL Server 2019 that you are really looking forward to?” You guys got anything?

Tara Kizer: I haven’t really paid much attention to it.

Richie Rump: I haven’t paid much attention to it either because I live in a different world now; a strange and unique world where I have to tidy up my databases using a vacuum process. But I would love to see, and we haven’t seen it since SQL Server 2012, is more T-SQL improvements. The more I use Postgres, the syntactical sugar and some of these other functions, it’s just a little nicer than what we have in SQL Server. So if I had my druthers, they’d be going back to the T-SQL land and improving the language a bit.

Erik Darling: So like specifically, what from Postgres would you like to see over in SQL Server?

Richie Rump: Oh, I  couldn’t tell you offhand right now because I’m just wondering if my vacuum process just failed or if it’s fixed. There’s some inherent functions that makes things a little easier as well as casting. All I need is two dots to cast, and things like that. Like, that’s a lot better than doing a cast or a convert function on something like that.

Erik Darling: Sure. I think, for me, for SQL Server 2019, I’m most looking forward to batch mode for row store indexes. I think that’s going to be the absolute biggest improvement. It’s going to be interesting though, because it’s really only good for larger data sets. And so for people who are doing OLTP type stuff, they might not see any love from that just yet. But I think that’s what I’m most looking forward to. Froid, so the stuff with inlining of scalar valued functions, that’s going to be huge for a lot of people. That’s like an absolute reason to upgrade, like not having scalar valued functions crap all over your workload all day long once per row, just like leaving little turdlets along the way. I think that’s going to be pretty awesome. I don’t know, lots of cool stuff. I think what’s really telling to me are the problems that are still out there in the wild not getting solved. Like parameter sniffing is still, you know, a huge, huge problem. People supplying like optional parameters is still a huge, huge problem. So I think what’s interesting is the stuff that’s still on the table for later versions that hopefully will get addressed at some point so that I can retire.

Tara Kizer: Has it been confirmed that the inlining of scalar functions is in 2019?

Erik Darling: It’s in CTP 2.1 and 2.2, so…

Richie Rump: And hopefully won’t get pulled, right? Right?

Tara Kizer: Have you tested it?

Erik Darling: Yeah, it’s worked okay in the stuff I found. I mean, there’s limitations to it, but like everything else.

Is there Office Hours next week?

Erik Darling: Julie asks, “Is there Office Hours next week?” I don’t think so.

Tara Kizer: Probably, because I think we only have Christmas Day off. I won’t be there, I have a client.

Richie Rump: I won’t be there because I’ll be off.

Tara Kizer: Oh, finally taking some vacation?

Richie Rump: It was requested by the big boss, the biggest boss.

Erik Darling: Yeah, I don’t know, maybe, we’ll see. If it’s just going to be me, the answer is no. I don’t know who else is going to be around though, we’ll see. It’ll be a surprise. It’ll be a Christmas miracle if we have Office Hours next week.

Where can I learn more about Postgres?

Erik Darling: Let’s see here, a loyal dedicated attendee asks, “What are some good blogs you suggest to learn about Postgres?” Do you know any, Richie?  I don’t…

Richie Rump: No, I don’t. In fact, it’s really hit and miss with some of the Postgres stuff. So actually, I don’t have any learning materials to kind of learn it other than to just dive into it, which is my preferred method of learning something new. It’s not like the SQL Server community, where there’s a lot of great bloggers out there and they’re consistently blogging and they have been blogging for 10-plus years. It’s different.

How does SQL Server pick stats sampling rates?

Erik Darling: Let’s see, Joseph asks, “When you run a stats update and you don’t give it a sample percent, what does SQL Server use to figure out what percentage to sample at?”

Tara Kizer: I can’t remember, does update statistics have a default that if you do sp_updatestats, the built-in stored procedure, it will use whatever…

Erik Darling: The default is a weird calculation. Paul White wrote about it and he has an answer on Stack Exchange that I can’t find easily, but he talks about the equation that gets used to figure out – it’s like…

Tara Kizer: It’s not just the 20%?

Erik Darling: No, it does something internally where it does a calculation based on, like, how many rows are in the table or something like that and it does some weird percentage of them. If you ever look at the stat man thing and you see the table sample thing in there, it’s like that percentage gets figured out in a real weird calculation.

Richie Rump: The answer is 42.

Erik Darling: I wish. I wish there were magic numbers in there and not bizarre calculations.

Richie Rump: We’re not going to tell you 42 what, it’s just 42.

Erik Darling: Yeah, units, query bucks maybe.

How can Santa visit all the homes in one night?

Erik Darling: And to finish things off, Tammy asks, “How is Santa able to visit all those homes in one night?”

Tara Kizer: That’s a good question. That’s why my son started questioning the whole Santa thing in kindergarten.

Erik Darling: The bad news is that Santa has done what just about every other industry has done and he has outsourced all of the work to other people. So it’s mostly just parents who deliver the presents to their own homes and hopefully don’t steal any from other homes. [crosstalk]

Richie Rump: The answer is teleportation. So it’s Nightcrawler. Nightcrawler is Santa.

Erik Darling: If you wanted to give a real gift to the world, Santa would share his teleportation technology.

Richie Rump: Or, if you’re more in a Harry Potter universe, he “disaporates.”

Erik Darling: No thank you, skip that. Skip that whole thing. No good. Alright, that’s all the questions we have. Thank you for joining us. Maybe see you next week, maybe not. It will be a surprise, just like what’s under the tree, and hopefully it will smell better. Alright folks, thank you, goodbye.

This week, Erik and Richie discuss TSQL classes, scalar functions, index reorganize vs rebuild, daily image backups on a fringe SQL server on VMWare, number of databases per AG, best practices for ETL operations, 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.

If you prefer to listen to the audio:

Podcast: Play in new window | Download

Enjoy the Podcast?

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

Office Hours Webcast – 2018-12-12

Erik Darling: So Marci asks, “Do you guys have a T-SQL advanced developer class that focuses on writing good SQL?” No, we don’t. I think the only person I know who has a class like that is Itzik Ben-Gan. I don’t know how hard it is to get in and do that stuff. We don’t have anything on writing it. We just have stuff on tuning it. So if you want stuff from scratch, you might learn something from mastering query tuning. There’s some pretty good advice on fixing queries in there, maybe not on how you should write them.

I think, at least for me, like once I got sort of deep enough into query tuning, there was enough carryover between, like, okay, now that I know how I would tune a query, I’m going to write this query in a way that I don’t have to tune it later. So I think once you learn how to fix a query then writing them from scratch, you kind of just naturally get better at that. So mastering query tuning might be a good fit for that, but if you’re really just looking for, like, how do I write query-type stuff, I would, instead of wasting a ton of money on training, I would probably just start with a few books. The same fellow who has the classes, Itzik Ben-Gan, has a few books on T-SQL querying that are just wonderful. One of them is a collaboration with a bunch of smart people like Adam Machanic and stuff.

So I would just get those books and not read them cover to cover, but as you’re doing something, check the contents and see if there’s a chapter or a couple of pages on what you’re actually doing. And then see if you can get any help from the book. Writing queries, you know, it’s a tough thing to teach people how to do because everyone has such different starting points with it and such different needs, depending on what their data is and what they’re looking for. There’s a big difference between writing a few small joins between tables and, like, writing really complex analysis-type queries.

Richie Rump: Right, I mean, I think really the key to writing good T-SQL is writing performant SQL. And once you’ve gotten into that, this is how the engine works and this is how certain queries react to the engine, that’s how you start writing good T-SQL; understanding that cursors are bad and SARGability and those type of things. That’s how you start writing good T-SQL. If you want to get into more advanced T-SQL stuff, I wouldn’t consider that good T-SQL, just that’s kind of authoring. And getting into things like windowing functions and stuff like that, that’s all something else. It’s not necessarily good T-SQL.

Erik Darling: It’s kind of just stuff you learn how to do along the way.

Erik Darling: Christopher asks, “For scalar functions, have you ever used the option returns null on null input? I know scalars are bad, but any possible examples of drawbacks to this?” I guess if you were depending on the output of that function to concatenate into another string, then you’re looking at an additional check to see if that’s null to replace it with something so it doesn’t make your whole string null. But, you know, scalar valued functions right now are just so bad for so many things that I wouldn’t worry about that. It’s like if, you know, I can’t even think of an upbeat analogy for this holiday season that would make sense for this. It’s not going to help you or hurt you either way. Just if you’re going to return a null value, possibly sometimes, then be careful about how you’re using that null value, because nulls have a lot of surprises in them.

Erik Darling: Theo says, “TDE is hacked. Is it still safe to use certificates?” Don’t ask me this question. I am not good at security. I had a bungle of a time just using one password to change some passwords this morning. It was awful. It was not a merry Christmas. Santa left me a lump of one password coal. You don’t want to trust me with this question. Sorry, Theo, ask someone else.

Erik Darling: Brian asks, “Do you know why an index reorg would be so much slower than rebuilds on the same index or similarly sized indexes? We noticed that maintenance was taking longer on the reorgs. As a test, I changed it to rebuild…” Yeah, so reorgs are single threaded. It’s like a little PAC-MAN that kind of goes along the leaf level of your index and sort of puts things in order. Rebuilds, if you’re on Enterprise Edition, have the ability to go parallel and all this other cool stuff. Reorgs also can do something called lob compaction, which can take a long time.

I used to run into this all the time when I was dealing with relativity indexes because there were some quite large tables in there and there were some quite large indexes and a whole bunch of lob columns, because that software is built by crazy people who let end users add columns to tables on a whim. And everyone says, I need the biggest possible column, I have no idea what’s going to show up here. So they would make all their columns nvarchar max. And then when reorgs run and they would do lob compaction, then boy howdy, that was a long time.

So the bigger your table is, the longer those reorgs take, especially if you’re doing lob compaction. I set sort of an internal threshold at around 50GB to 100GB where I’m saying screw the reorg, I’m just going to rebuild this thing if I need to. But even then, I’m not going to rebuild all that many indexes. I’m not going to do all that much index maintenance. I’m going to stick a link into the Webex howdy-doody think as soon as I find it. But I’m not that bright. I have to open up a web thing. What do they call that, goes on the internet and shows you things that people did?

Richie Rump: Facebook.

Erik Darling: Browser…

Richie Rump: The Dark Web.

Erik Darling: Yes, the Dark Web. Groupby.org… Because no one ever believes me when I say this stuff. I’m going to give you a link to Brent’s session at GroupBy about why defragmenting your indexes isn’t helping, and hopefully that will – nope, that didn’t copy or paste. This is a good day for me. Everything’s going well. Okay, so I’m going to stick that link in there. But yeah, that’s the bottom line on why rebuilds are generally faster. Reog is just, I don’t know, just kind of lightweight and wimpy. Any further thoughts on that, Richie? You don’t have to, it’s okay. There we go, I think Richie deserves some Christmas lights too.

Richie Rump: I created an index yesterday…

Erik Darling: Wow, did it make your deletes so much faster?

Richie Rump: No, it was a small table.

Erik Darling: Why did you create an index on a small table? Why are you wasting Brent’s disk money?

Richie Rump: Because it needed it.

Erik Darling: Okay, well fine then.

Erik Darling: Let’s see, I’m going to read this question first because it sounds suspicious to me. “One of our SQL admins wants to do a daily image backup on a fringe SQL Server on VMware, but I’m trying to convince him this isn’t sufficient since you want to be able to recover to at least the last hour. What are some concerns?” So I have no idea what kind of backups your sysadmin is looking to take. If it’s a snapshot of just the whole server and all the drives, it can be kind of a bummer. There are some backups – there are some VM backups, like Veeam, that will play nicely with your log backups or differential backups, so you could restore your database and then restore more backups on top of it to get you to a better point in time. But I would need more information on that before I started going off on any sort of tangent.

Erik Darling: Let’s see, Graham asks, “How many databases is an appropriate number in an AG? In my case, each database is less than 20.” So that depends a lot on how many CPUs you have and on how busy these databases are. With each database that you add in, you’re looking at adding in additional synchronization overhead, and that synchronization overhead takes CPU threads. And those CPU threads come from a kind of limited pool that SQL Server spins up based on how many CPUs you have. So really, what you want to do is think about just how much hardware you have on this server.

There’s a great post by – I’ll put the link into the chat questions – by a guy named Warwick Rudd about Availability Groups and things that you can just do massively, massively wrong with them. And it’s called What Not to do When Adding Databases to an Availability Group. And he kind of touches on a lot of this stuff. So what I would do is think, okay, if I have eight cores in this thing, I only have 576 or so worker threads that I can send out to run queries and do backups and keep these databases in sync. It’s generally not a good idea to hit THREADPOOL waits because what I’ve seen on a bunch of client servers is you have this AG and everything’s going fine, then THREADPOOL comes along and all of a sudden your error log starts filling up with messages about how the AG can’t synchronize because it couldn’t initialize a thread. So be really careful with the number of databases you stick in an AG. You can end up several creeks that probably smell like Richie’s daughter’s shoes.

Richie Rump: You don’t want that, that’s terrible.

Erik Darling: No, I seem to have lost my light. I was really liking the way this was looking, aside from the heat. Whoops, I put that in the wring window. Man, that’s hot.

Erik Darling: Okay, alright, let’s see, what’s a good new question? “Hi, what would be the best for ETL operations; a heaped table or the same table with a clustered index?” Usually, when I want to do ETL, when I’m loading data in, I don’t want any indexes. Indexes only slow me down. Usually, I just want to work with the heap and I just want to dump data in as fast as humanly possible. That’s one of the few really good things about heaps. One of the few upsides with heaps is you can just jam data in as really quickly and not have to worry about order or creating extra pages. Even a heap with a non-clustered index on top, you’re looking at dumping a bunch of data in the table and then having that and then having to dump a bunch of data in the index and it all just kind of stinks. It is not fun. So I would just generally stick with the heap on that.

Richie Rump: Yeah, I agree. And typically, I would do that, like have a heap, and then most likely I would need an index or two because I’m doing some sort of transformation on that data. But that would not be a clustered index, just because you’re constantly flowing that data through. As soon as you transform, you’re going to dump it somewhere else and then you’re going to drop that data at some point, so yeah, yippee.

Erik Darling: Let’s see here, “Have you all implemented group managed service accounts?” Absolutely not. 0% chance of that happening. Sorry, Rob, nope, I don’t do that. If you have questions about stuff like that, Denny Cherry has a book called Securing SQL Server. It’s got all sorts of good information about things that have to do with security that I am an idiot with, like one password. So please…

Erik Darling: Jim has a question, “We are getting insane page latch EX waits because of a multi-statement TVF that splits strings, but using string split directly or converting the function to an inline TVF is seven times slower.” So, Jim, this is a big question which involves code. There’s like code involved with a function that you have and other function options that you’ve tried. I would post this on dba.stackexchange.com because you could give lots of good examples. As far as why you have the page latch ex waits, it’s likely, if you have a multi-statement table valued function that’s using a table variable, you’re putting a bunch of stuff in there, that’s going to go to tempdb. That’s what the page latch ex waits are most likely going to be stemming from.

As far as a fix for that, if you’ve already tried the stuff that I would try offhand, you might want to try a different CLR version of split string. I know Adam Machanic wrote one a few years back. I don’t know where it is offhand and I think it would be detrimental for you to try to go search for it right now because all his stuff from SQLBlog has gone and moved and stuff. Post that on dba.stackexchange.com…

Richie Rump: I think he did a TechEd session about that CLR.

Erik Darling: He might have, but I don’t recall off the top of my head.

Richie Rump: Yeah, I think it’s on YouTube somewhere maybe.

Erik Darling: Probably. A lot of his stuff ends up on YouTube because he’s a very, very smart fella. Is that the right URL? That looks like the right URL. There you go, Jim, off to dba.stackexchange.com with you. Enjoy that. It will be a fun time. Everyone loves dba.stackexchange.com – it’s full of so many smart people. You might even get an answer from Paul White. Good times.

Erik Darling: Julie asks, “What controls the subject line for failed jobs set to notify the operator? I have two servers with the same job but the subject line is different.” God, it’s been so long since I had to look at that. That is an interesting piece of trivia. You know, I don’t know offhand; weird. I couldn’t tell you.

Richie Rump: Wow, I think it’s safe to say you would not be certified, sir.

Erik Darling: No, I wouldn’t.

Richie Rump: You failed the certification exam because you missed that bit of trivia.

Erik Darling: I’ll live. I’ll find a way to survive without that. This is why I wish Tara was here, because she would know that because she still probably has agent jobs, like, set alarms for her, send her messages while she’s on hikes, I don’t know. I don’t know offhand.

Erik Darling: Let’s see, Chris asks, “Any experience with SQL Server centralized management server to push policies…” Good heavens, push policies? No, no I am sorry that I sent you the link to the think about Availability Groups because that was a mis-copy and paste on my part, that went to the wrong line. But no, generally I have – something else that I just haven’t done any work with because that’s not the type of DBA that I care to be at work. Apologies for not knowing more about using a central management server to push policies out. That brings us to the very end of our questions.

Richie Rump: Really, no service broker questions for you?  Because you’re a big fan of that service broker stuff…

Erik Darling: We don’t have anything on service broker or replication and that’s fine because these lights are about to make me pass out, so I’m going to…

Richie Rump: Hey, if you’re ever cold in New York over the winter, you now know what to do.

Erik Darling: Yeah, you know what, I’m going to start giving these lights out to the homeless because I’m frying. I think I’m going to pass out if I don’t take these off.  So have a very merry Christmas, everyone.  Happy holidays. I hope to see you as long as these lights haven’t killed me. Adios.

This week, Tara and Richie discuss deletes on heaps, Docker, using containers in production environment, advice for new DBA, downgrading server hardware, agent jobs, setting SQL Memory Min and Max to the same value, how to prove CPU pressure to management using the Power BI report using sp_Blitz, corruption, patching, backups, sargability, 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.

If you prefer to listen to the audio:

Podcast: Play in new window | Download

Enjoy the Podcast?

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

Office Hours Webcast – 2018-12-5

Tara Kizer: Alright, let’s get started – and I don’t have the answer to Carlos’s question right off the bat and I can’t remember – I’m going to ask anyway, “If doing a delete on a table versus truncate table for a heap, will they behave the same for [free net] pages? Will index physical stats DMV look the same afterwards?” It’s a vendor database. He doesn’t own the schema. I can’t remember for heaps. I’m a clustered index table type person and I know a lot of the ins and outs of heaps, but that’s one I don’t have memorized. Do you remember, Richie?

Richie Rump: I don’t think there’s a huge difference between the two. If this was on a test, I’d be like, yeah I think they look the same.

Tara Kizer:  I know that there’s some issue with deletes on heaps because we have an alert in BlitzIndex for it and I didn’t quite understand what it meant. And Erik has moved it as a lower-priority item further downwards. Forwarded fetches on heaps are bad. I think it has something to do with no other tables can use that space, but the heap table can reuse that space. I’m sure we have blog posts out there on it. If you run sp_BlitzIndex mode four – maybe mode zero but probably mode four – it will let you know which tables have the deletes and see that there should be a URL on that row. Click on that and you’ll learn more about deletes on heaps.

Richie Rump: Wow, I did not know that. See, even I can learn something.

Tara Kizer: I look up Blitz Index stuff, you know, day to day as part of the client engagements. It’s a row that I kind of ignore though.

Richie Rump: I’ve been looking into BlitzCache code the last couple of days.

Tara Kizer: Yeah, I’ve seen your questions in Slack. I’m like, uh-oh…

Richie Rump: Like, what is going on here, people?

Tara Kizer: That’s one I will not open. I’ve contributed a couple of changes to Blitz and I don’t think any other ones I’ve opened. But BlitzCache, that’s a hard no. There’s no way I’m looking at that.

Richie Rump: Yeah, I found one of the queries for non-SARGable stuff and I’m like, I don’t think this will ever catch anything. I’m pretty sure that this is wrong and I’m going to go the route that I would know will capture something.

Tara Kizer: Alright, Marco asks, “Some quick-start documentation for SQL DBA that they want to start on it…” oh, for Kubernetes and Dockers.

Richie Rump: You’re talking to the wrong clan here. I don’t think any of us have actually played with Kubernetes or Dockers. If I wanted to learn about Kubernetes and Dockers, not necessarily for a SQL DBA, but just as a technologist, I’m probably first go to Pluralsight because a lot of those presenters, they distill the information pretty well. And they’ve been in the technology, so if I need to learn something really quick and I didn’t want to read a whole bunch of documentation whitepaper-y type stuff, I would go to Pluralsight first. And then if there was something that the speaker was talking about that I wanted to dive into a little bit more, then I would go off to the different places on the internet and look for other people to talk about stuff. That’s how I roll.

Tara Kizer: Jeremy asks, “What are your thoughts on using containers in a production environment?” I don’t really have any thoughts. I don’t really care.

Richie Rump: It’s the same question I have for any technology in a production environment; why? What is your reasoning for using containers in a production environment? Is it for ease of setup? We have multiple servers all over the place where you just want to deploy really quickly and have that? If that’s the case then yeah, but there’s got to be a reason, not because it’s the cool new hotness and everyone’s doing it. That’s not a good reason. There needs to be a firm technical reason for why you want to have some sort of container in a production environment. So yeah, the answer to that question…

Tara Kizer: Joshua asks, “Brent Ozar Unlimited offers training on multiple paths. Other vendors do the same. Is there a path you would most recommend to a new DBA with admin focus and not development focus? Which skills would you focus on and why?

Richie Rump: Well, I would suggest you go development and not DBA because I’m a developer and that’s where I would want to go.

Tara Kizer: I mean, we do have the fundamentals of DBA course. I think those are all Erik’s modules, I’m pretty sure. Start there as far as… I don’t know…

Richie Rump: How to Think Like the Engine. Start there…

Tara Kizer: Well yeah, that’s true.

Richie Rump: How to Think Like the SQL Server Engine, start there and then wherever else Tara is going to lead you.

Tara Kizer: I don’t learn very well in the method of taking classes. When I really jumped my skill set it’s because I was being very active online in forums. And for questions I didn’t know the answer to, I would look them up and see if I could figure out the answer, and then I would play around in Management Studio and see if I could figure it out. So I learned a lot by doing and so a lot of my experience came from test environments and later production environments. Obviously, if you’re a junior DBA and you don’t have the access to production, possibly that might be harder to do. But I really get my skills from doing rather than listening to people talk about it.

Tara Kizer: Teschal asks, “We have a few tables that have foreign keys that are not trusted and I run this command, alter table with check constraint, but still see that the key is not trusted after the alter completed successfully.” That’s an interesting question. Check the query. I imagine it’s our script from our foreign key trusted post, but not sure. Not sure if that’s a bug in the script or something else went wrong. Ask that question on Stack Exchange also after checking the script.

Tara Kizer: Chuck asks, “Current server has 160GB of RAM allocated for SQL instance. We feel that the server was purchased to spend money out of the budget…” I love servers like that, “Now the server has to be replaced with limited budget. How does one go about determining how much RAM the server actually needs?” I wouldn’t be wanting to give that up. As a production DBA there’s just no way. I mean, I might be okay with having lesser cores, especially if it’s Enterprise Edition, because that’s where it really costs a lot of money for a server, the licensing aspect. But RAM, I don’t know that I’d want – it just depends on what your system needs. It’s not something that we can really answer. I’ve worked for companies that have performance load testings, and that’s something we could test in our load test environment where replaying a production load or a synthetic load there. And we could see we could reduce the RAM there – I don’t think I’ve ever done that test though because I’ve never gone down, only up. But 160GB isn’t that big of a server these days. I would look at your processors instead if it were me to reduce costs.

Richie Rump: I think that’s going to be a more valid question as more and more servers start inching up to the cloud, because once we start lowering our memory and our CPU, we start saving money. So I think it’s a valid question and really it’s up to, hey, what is your app doing?  How frequently is it doing it? How do your queries look, because if you have ill-performing queries, they’re going to have more resources, more memory, more CPU, more of everything? So you know your system and you could take a look and see what it’s doing when and just monitor it and see, hey, what are the times that all this stuff is going on and do we need more or can we actually back it down a little bit?

Tara Kizer: Yeah, especially in the cloud where you can’t really just add RAM. You have to go up in size and that gives you more processors and more RAM. That’s a really valid point that you made.

Richie Rump: Yep, I make one once a day. Thank you, guys.

Tara Kizer: That’s it, you’re done for the day.

Tara Kizer: Alright, I’m going to murder this person’s name. [Shayan] asks, “A SQL agent job executes two stored procedures. Is there a default timeout setting, how to make sure it can run for 60 minutes without timeout?” There is no default setting in SQL Server. That is imposed by applications. I’m sure – I don’t know if there’s even a setting you could change in the agent jobs. Well, you can. If you wanted to, you can have it stop after a certain amount of time. But you’re asking the opposite; how to make sure it can run to completion, basically. And you don’t have to make any changes to do that. I’ve had some jobs run over 24 hours and they just keep on running. Now, those are going to be like CHECKDBs on very large databases, but you’re good to go. Just set up a job and it should not have any problems, continuing to execute at least.

Tara Kizer: Alright, Julie asks, “Setting SQL memory min and max to the same value, why would you, or not, want to do this? Corporate policy…” So I think that in some version of SQL Server, and I’ve never encountered this, but I know about two and a half years ago, I think it was Doug that mentioned, we had a client that had min and max set to the same thing. and so I learned that day that he was remembering some kind of bug where it would wipe out the plan cache. Some odd thing was happening and it was just a bug where min and max were set to the same. So I would imagine whatever version that is, that it was a bug and it has been fixed, but I don’t know that there’s any benefit to having min and max set to the same thing.

On the systems that I’ve supported where we’ve changed the min value – normally I don’t change the min value. But we have changed the min value, it’s because we were in a clustered environment and on failover we wanted to make sure that the – I’m talking about a multi-instance failover. Say we have two nodes and two instances, one on each node, active, active, and a failover occurs for whatever reason, we wanted to make sure that that instance that had to do the failover because the server crashed or whatever, it could get some amount of memory. So by setting the min, that would allow it to steal some. I don’t know that there’s any other benefit to it. SQL Server’s not going to automatically allocate that min right off the bat anyway on restart. It takes some time to build up to that.  I don’t know that there’s any reason not to do it except with in-mind that there may be some bug in some version, and I could be completely murdering this anyway.

Tara Kizer: Sheila asks, “How can I prove CPU pressure to management using the PowerBI report using the Blitz procs? Performance keeps getting worse and the only difference is a query using five execution plans; third-party code.” I mean, CPU pressure is easy; you just pull up perfmon or a task manager and you can see it. The little graph there also in SQL Server for wait stats, you’re looking for SOS schedule yield waits. That’s a good indication. So using BlitzFirst. And Blitz will let you know towards the bottom of the output if you do have high waiting time on SOS schedule yield waits, but that’s the wait stat to keep in mind. As far as PowerBI, I’m not the right person to answer that. I think Brent’s really the only one that could answer that part of it. The fact that a query is using five execution plans isn’t really a problem.

I mean, certainly adhoc queries that have thousands of execution plans, that can be problematic. But one with five execution plans is not. I would be looking at other things. I would be looking at the wait stats, CPU utilization, I/O, that type of stuff. But start with the wait stats and that’s what tells you where to go next. Look at your indexes. BlitzIndex mode zero, looking for high-priority items. And in BlitzCache, I would be sorting by, so BlitzCache sort order equals, and then I would start with CPU; average CPU. Maybe look at memory grant, depending on what version of SQL Server that you’re on. I don’t know that the PowerBI stuff is sufficient to actively troubleshoot the issue.

Tara Kizer: Alright, Pam asks, “We’ve recently experienced corruption in two databases on indexes. Our developers are very fond of using the NOLOCK hint, and I’ve read in past versions there is a bug that can cause corruption. Does this still hold true for SQL Server 2016?” I would imagine it does not. I am not a fan of NOLOCK hints. Where data accuracy matters, you cannot use it, period, end of story. I’m not aware of it causing corruption in the past, but I’m sure that you’re right. Brent and Erik would probably have that type of stuff memorized. I would seriously doubt that it would still exist in 2016 or even any recent version of SQL Server because that’s pretty serious, because a lot of people are using NOLOCKs out there. I mean, I don’t know what the percentage is in the world, but I’d say it’s a pretty big percentage.

Richie Rump: Too damn high. And as a DBA going to a developer, you need to ask them, why are you using a NOLOCK? And they probably don’t understand actually what’s going on in the backend with the NOLOCK stuff. And then you could let them know, you can get wrong data and you can get duplicate data, and there’s all this other fun stuff that they probably don’t know about. And then you could start thinking, if we really need this kind of stuff, maybe we need to go to RCSI on a database and get rid of all this NOLOCK stuff.

Tara Kizer: Exactly, fix the root cause of the problem. NOLOCK should not be the turbo button in SQL Server. It still takes out locks anyway.

Richie Rump: It’s really just a misunderstanding on the developer’s side of what NOLOCK does. I’ve done it intentionally, hey this is a reason why we need to use NOLOCK, boom, boom, boom, boom. And in this particular instance, we had a heap and there was no inserts going on at the time and there was a reason why we did it and we decided to do it. But I wouldn’t do it without a specific reason. And going through that thought process of what NOLOCK does and what it buys you while the system is going on in a certain time set or whatnot.

Tara Kizer: John asks, “I need to set up regular patching of my SQL Servers. To be safe, is it just getting the good backup, or is there a best practice, tips, tricks that you would recommend?” I always know that my backups are okay. Yeah, I’ve worked in larger organizations with tons of SQL Servers, lots of monitoring, all this type of stuff, and we’re restoring our backups, we have this stuff automated for our important systems. So I never specifically run a backup before I start my patching. I mean, certainly you could do that, but when you’re talking about 16TB databases, you’re probably not going to run a backup before you start patching, unless you’ve got some kind of snapshot utility going.

But making sure your backups are valid, and then most of my systems would have log backups running every five to 15 minutes anyway. So that is a backup occurring before patching starts. But start in your test environment. You need to make sure that your system is okay with these service packs, cumulative update packages, because some service packs and CUs have caused problems with certain things. So do your testing.

Tara Kizer: Donald says, “Explain SARGability once more please.” So SARGability is a made up word in the database world. It stands for search argumentable , so SARGable. There’s even a Wikipedia page, it’s not just a SQL Server thing. This is a relational database thing. So we want our where clauses and join conditions, our search predicates to be SARGable to allow SQL Server to be able to pick an index seek, for instance, instead of a scan. When our search predicates are not SARGable, meaning that they’re non-SARGable, SQL Server may have to scan the index. If an index even exists for the column where the SARGability issue is happening, it may have to do a scan there. Now sometimes we won’t see a scan because in the search predicates – or it could be other filtering that can occur, maybe you have those things indexed – and so by the time it gets to this search predicate that is not SARGable, it has less work to do. So it’s not scanning anything. It’s just doing that work after it has already filtered down to a certain amount of rows

So an example of a non-SARGable search predicate is using RTRIM, LTRIM, cast convert. Some date things don’t have SARGability issues, but converting out to get the year, that would have a problem at times. Functions, implicit conversions is an example. Anytime you wrap a column with a function, the built-in functions, that is going to be a problem. And we’re talking about where clauses, join conditions, where your filtering is going on. I don’t care what you do in the select statements, although if it’s in a view and it’s in the select statement, that becomes a problem because your outer query may be using that column in the select as a where clause. So it gets a little tricky when you start nesting things down. But there’s lots of information out there on SARGability. You can even start on the Wikipedia page to learn some more about it. it’s a very common problem out there.

Richie Rump: And in fact, this is what I was adding to ConstantCare this week. So we’re going through all the query plans and then we’re seeing, okay, do you have any non-SARGable predicates that are in there? And I had to work backwards. I had to work form the XML and create the queries to generate that XML so we could test all this stuff out. So it’s a problem and soon ConstantCare will tell you that you have a problem and you should seek help.

Tara Kizer: Mark asks, “Can you recommend any free cloud SQL Servers for people to try their hand in cloud DB?” I don’t know if there’s anything out there, because I know even Microsoft Azure, the Microsoft MVPs get very, very limited free stuff.

Richie Rump: I think they get $150 credit a month, something like that.

Tara Kizer: Something like that, very low. And I guess a lot of the presenters use that up in, like, the first day. So they have to be very careful. Yeah, you have to pay to use the cloud and there isn’t really a test version of the cloud or production version of the cloud. You’re paying. Of course, you can get a smaller server. There are some, you can get a pretty small server.

Richie Rump: Yeah, so I mean, I think what most people do is they have their own Azure account and it’s tied to their own credit card. They’ll go ahead and spin up a database. They’ll load their data, or maybe their database is already there. They load it form a backup then go ahead and run that stuff. And then when they’re done, they’ll turn it back off. Then that way, they’re not incurring the monthly cost of Azure DB or RDS or whatever craziness that’s going on. And they also had a very low tier. And so actually, I think a lot of our costs when we do training are we put them up at a higher tier, so in the cloud they have their own SQL Server databases. I mean, that’s one of the advantages of training with us, because we give you all the environments in the labs and stuff like that and you’ve got hands-on stuff which is pretty darn cool if you ask me.

Tara Kizer: Theo asks, “We have a SQL Server that gives a memory error at random times, like failed to release continuous memory, blah, blah, blah.” Oh boy, if I was getting that error, I would be doing the hardware checks. I would probably be opening a case with Microsoft, checking with the vendor of the hardware. You might have corruption issues. Memory errors like that, as far as I know, that’s a bad one. You might have a bad module. I would definitely look into it more and hopefully you’ve got some kind of high availability setup for the system so that you can failover to another server while you work on whatever problem. It sounds like a hardware problem to me, and you might have some database corruption. Maybe you don’t. Hopefully you have DBCC CHECKDB running at times to know whether or not you have corruption.

Tara Kizer: Mark says, “If we disable constraints of a foreign key, we could insert faster, right? But if we enabled again, but not with check, then what is the process or workflow of the insert? Is scan all table to when it’s a position or match the constraint?”  I mean, certainly if you enable constraints, it’s going to help things out. But the constraints are there for a reason. And having trusted constraints can help you out with your queries, your select statements, because a join elimination can occur. I had a client this week that is having issues with this because they don’t have foreign keys in place or some of them aren’t trusted. So Brent’s got a module in his performance tuning class, mastering index tuning class, where he talks about this issue. And he shows you a query in Stack Overflow database. It’s between post and post types.

And if you have the foreign key trusted between the two tables and you do a query with a join to post types, it doesn’t even have to check the post types table in the execution plan because it can eliminate that. It knows that the data is fine in that table, so it doesn’t even have to bother hitting that table. So I would do some research on the join elimination topic.

Richie Rump: Right, you know, but if we’re doing loads into a table like that to make them faster, you do your cleaning of the data first in a staging environment, and then you don’t have to worry about when I put the index back on, is all the data right?