Blog

This week, Richie and Erik discuss log backups, migrating from SQL 2008 R2 to 2014, shrinking databases, tuning long-running queries, execution plans, sequencing, encryption, and much more!

Here’s the video on YouTube:

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

Enjoy the Podcast?

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

Office Hours Webcast – 2016-11-09

 

Erik Darling: Let’s start with some questions. We have one from fellow human asking if there is a way to add an additional node used for read-only access to a SQL Server 2008 cluster. No, sir. No how. No way. Unless you log ship to another node—well that wouldn’t be really a cluster node, that would be a separate server. You could log ship or using mirroring and take snapshots of the mirror. That is the only way to do that really in 2008 R2. You need availability groups or one of those things, replication, or something else, but it wouldn’t really make sense to replicate to another node in the cluster. So, no, short answer. Long answer: you would need a separate server with some different technology in the middle.

 

Erik Darling: Fellow human asks, “Sp ms ad repel commands is blocking heavily in the distributor.” Cool, good to know. I’ll alert the media. Tara is not on yet, I think her answer might be to either make sure you have Read Committed Snapshot Isolation turned on and then if you already have that then you might want to just kill and restart it. I do not know much more about replication than that.

 

Erik Darling: Let’s move on and ask a question that I have a good answer to. There is one about backups. “If I take a log backup while a full backup is running and another log backup after the full backup finishes, how would I restore the database with all log backup files?” Geez. That is such a simple thing to test, right? Take a full backup, take a couple log backups. So the answer is log backups taken during full backups don’t truncate the log file. They have to wait for the full backup to finish before that happens. So you would just need the full backup and the log backup file taken immediately after the full backup. The one taken during the full backup doesn’t really have much in it.

 

Erik Darling: Another question, “I used CrystalDiskMark to determine the speed of the disks between servers, including one we just purchased.” They asked the SAN tech why the speed from our drive was “six times faster than the currently purchased drive.” Oh, boy. I don’t know. You are in a particularly weird situation. I don’t know if CrystalDiskMark is giving you false data. You could try a utility called Diskspd, see if you get any different results from that. I don’t know what kind of SAN you’re using or where they got that information from so it would be hard for me to comment on it.

 

Erik Darling: I have another question here from Tammy that I don’t have an answer to. “I’m looking for a way to differentiate SQL users without logins versus orphaned SQL users.” Don’t know. I’ve never had to do that. Couldn’t tell you.

Richie Rump: [Inaudible] using T-SQL, are those users that are in the database but not attached to the server?

Erik Darling: Something like that, yeah.

Richie Rump: I used to have a script way back when, in a galaxy far, far away that kind of identified those and deleted them.

Erik Darling: Oh.

Richie Rump: I don’t think I’ve got them. Because that typically happens when you do a backup and restore from one database to another.

Erik Darling: Right. You know what, that jogged something in my mind… Is that what it is? Oh yeah. So there’s a Microsoft stored procedure that can, maybe the code in there can lead you in the right direction. It’s called sp_help_revlogin. I’ll stick the link to it in chat for everyone if they want it.

Richie Rump: If he could figure out how to paste things from the…

Erik Darling: Control V is hard. Yeah, that’s the best advice I have for you. Check out the code in there, it might get you somewhere on the way.

 

Erik Darling: “In SQL Server 2014 the DAC port seems to be completely random to me. How can I fix the port to a specific one, especially with multiple instances on a machine?” I don’t believe you can. I don’t believe that is a configurable option. I think the remote DAC just shows up, my question would be, does the SQL Server port also always the same? Richie, anything good? See any good questions in there that you want to throw out?

Richie Rump: I think they’re all great questions.

Erik Darling: No you don’t. You’re lying.

Richie Rump: Our viewers do not ask poor questions. They’re all wonderful, just like they are. Unlike yourself, Erik.

Erik Darling: Unlike me, unlike me.

 

Erik Darling: We’re just going to keep going on down the line then. “I have a few SQL agent jobs that are disabled but ran as scheduled anyway. MS db sysjobs shows these jobs as enabled = 0. Do the schedules on these jobs need to be disabled as well?” If they’re maintenance plans, I believe so. Also, sometimes you can chain jobs and some SQL agent jobs can call other SQL agent jobs. So I would be on the lookout for that as well.

Richie Rump: Chain-smoking jobs? Is that what you’re…?

Erik Darling: Yes, chain smoking, but we don’t know what. Could be anything. Hopefully legal.

 

Erik Darling: Fellow human asks, “When migrating from SQL Server 2008 R2 to 2014, should the flag 4199 be enabled globally or not?” Well, that trace flag contains a lot of optimizer fixes, whether you’re running into issues where those fixes apply is heavily workload dependent. There’s not really a good yes or no answer for that off the bat. The best thing I can tell you to do is setup a 2014 test server. Try your queries with the trace flag on and trace flag off. Microsoft is changing a bit how that trace flag works where it’s just going to bucket optimizer fixes up to current on different versions. So 4199 actually contains different sets of fixes between versions. It’s just a bucketizer trace flag now for optimizer fixes or changes. It’s not like the steady set of fixes and changes that it was prior to 2014. The other thing that you’re going to have to take a good, careful look at is if the new cardinality estimator is helping or hurting your queries. So make sure that you test both things in conjunction. Make sure you’re not getting something weird because of the new cardinality estimator. Make sure you’re not getting something weird because of 4199. You have your work cut out for you. That’s a tall order of testing depending on how much code your application generates.

Richie Rump: I haven’t messed with the new cardinality estimator because I’ve been stuck in dev-ville. Have you seen anything in the wild where the new cardinality estimator would take something, a good plan, and then turn into utter garbage?

Erik Darling: I haven’t seen that just because we’ve had precious few people a) using 2014 b) using it in the higher compatibility mode and c) with anything to compare it to in a prior mode. The few times that I’ve seen 2014 acting like 2014 it’s just been sort of trying to assess how that’s working and not really being able to compare it to anything else that happened before for them. There’s stuff that I really like about it. I think it’s kind of a good idea that there is some correlation involved in your where clauses. So if you’re comparing columns in the same table, so you could assume there is at least some overlap between values in one and values in the other and they’re not just two distinct cardinality estimates. So I think that’s cool but that may not work for everything. Customer ID 1 may have never purchased anything on a certain date and Customer ID 2 may have made all their purchases on a certain date. So it’s good to assume that there’s some correlation but maybe not as much as they do.

Richie Rump: Right, so kind of our recommendation is try it but test it.

Erik Darling: Yeah.

Richie Rump: Along with everything else.

Erik Darling: What’s the difference between trying it and testing it?

Richie Rump: You could just try it and throw it in production. What could possibly go wrong? Testing it is like, “Oh well, we’re not going to throw it in production. We’re going to do it in a staging environment. We’re going to run some stuff up against it.”

Erik Darling:  Like jumping out of planes.

Richie Rump: Without a parachute. What are you? Captain America?

 

Erik Darling: There’s a question here from someone who has auto shrink set to on. They turned it off and their database grew. Stop shrinking your database. That’s it. That’s the only answer for you. Stop shrinking your database. If it’s a one-time thing, it’s a one-time thing, but you’re doing it repetitively. You want to keep shrinking it. You want to keep beating that database up. You want to keep beating your whole server up by shrinking your database. That’s not good.

Richie Rump: Because every time you shrink a database, Paul Randal kills a kitten.

Erik Darling: He does. Not quickly either. It’s like a slow, like days long thing.

Richie Rump: I actually did not know that. I thought it was more like a [makes sound] but no, he’s… oh, that’s terrible. Yeah, don’t shrink.

Erik Darling: Yeah, he starts with the tail.

Richie Rump: No, no, poor guys.

 

Erik Darling: Here we go. Here’s one that I like. “When tuning a long running query, what are some of the low-hanging fruit to watch out for in the execution plan?” Richie, do you have any favorites?

Richie Rump: A long running query? I like to see those scans. I like me some scans. I like me to take a look at and maybe I’ve got some nested loops with some very large numbers going into it. Maybe I’ve got the wrong join type there. Maybe it should have been some sort of match or hash join as opposed to that nested loopy thingy.

Erik Darling: Yeah, buddy.

Richie Rump: I’m going to take a look at maybe some functions, some aggregates there. Maybe there’s something going down there. Those are some fun things that I don’t get to do anymore.

Erik Darling: Yep, ain’t no scans in the cloud.

Richie Rump: But it’s better because we don’t have any of that stuff in the cloud.

Erik Darling: That’s true. I agree. You don’t have to deal with it. Everything just works fast. It’s so nice. Yeah, we have sort of an index on three things, mostly there.

Richie Rump: We’re not joining tables together. We are absolutely not doing that.

Erik Darling: No, and we have mostly the same data in all three places. Give or take.

Richie Rump: Kind of, eventually.

Erik Darling: Someday we will have it there. When I’m tuning long-running queries, the stuff that I like to look out for is spills to disk. That’s a good one because when you spill the disk you slow yourself way down, so look for hashes or sorts that spill. My big pet peeve is key lookups because key lookups infer that you are doing something horrible many, many times. Sometimes they’re fixable if you’re not getting too many columns back from the clustered index or the times you have to kind of balance, how important is this query, do I want a super wide index to cover everything so I’m not doing a key lookup?

Richie Rump: What if I don’t have a clustered index? What’s the lookup there?

Erik Darling: It goes to a heap and then you’re in double trouble because if the heap has forwarded records in it, you can end up doing a key lookup plus a forwarded records search.

Richie Rump: But wouldn’t it be a RID lookup at that point?

Erik Darling: Yes.

Richie Rump: I remember something from a Brent Ozar class once.

Erik Darling: Yes, good for you. From a pre-con long, long ago.

Richie Rump: A long time ago, somewhere in Atlanta, back in 2012.

Erik Darling: Let’s see, other stuff that I like to look for, probably lazy spools are a good one. Lazy spools are an interesting thing where SQL attempts to cache a query result in tempdb and maybe reuse it, maybe not. So if you see a table spool and it’s rebinding a lot, then that means SQL is re-caching data in tempdb. Stuff like that is a pretty good start, you know. The old missing index request is a good one. Let’s see, off the top of my hand, implicit conversion maybe, scalar functions.

Richie Rump: Implicit conversion, which is really hard to find.

Erik Darling: Yeah, it’s really tough. There’s no warnings about that one, anywhere. One thing you might want to try doing is giving things a look through with sp_BlitzCache and seeing if it warns you about anything because we do warn about a lot of this stuff using sp_BlitzCache. So if you want to grab like the plan handle or the SQL handle or something, run it through sp_BlitzCache and see what you come up with.

 

Erik Darling: Wow, okay, here’s a weird one. “Do you have any policy templates for things like, ‘No you can’t have SA for your application?’” Okay, so I’m not sure if you’re asking about policy-based management or if you’re asking for just like a polite response to why your application can’t use SA. If you follow up on that, I can maybe tell you that a) I’ve never used policy-based management or b) if an application is asking for SA, then put it in writing that this application can make all sorts of changes to the server and do anything it wants and that you cannot be entirely responsible for anything that the application does because if someone drops a database from the application, if there is some sort of SQL injection-y thing going on where someone does something horrible, it can’t be all on your shoes because that was what the application was granted and you fought against it and someone said yes anyway. So heads up there. Oh, that’s a long one.

Richie Rump: And no, you shouldn’t have SA.

Erik Darling: No, you shouldn’t have SA.

Richie Rump: You shouldn’t have SA.

 

Erik Darling: There is a very long question in here about, “What factors can change the execution plan, especially for compiled and prepared parameterized query?” That is a long question. Head on over to dba.stackexchange.com, post it there. Post the execution plan on our site. If you go to brentozar.com and under free stuff—I just want to make sure I’m saying the right thing—under free stuff, there will be a link to Paste the Plan. You can paste the good and the bad execution plan and you can post that on dba.stackexchange.com. You can get a much better answer than me trying to decipher this Bible paragraph of text.

Richie Rump: Thou shall give bad execution plan.

Erik Darling: Yes, and then a nested loop join. We’ve got a hash match.

 

Erik Darling: All right. What? “What’s the best way to downgrade a 2014 database for restoring to a 2008 R2 server without building out a 2014 server?” You don’t.

Richie Rump: Yeah.

Erik Darling: Yeah. The only thing you can really do there is restore the database on a 2014 box, it’s not going to be restorable to anything lower than 2014. Then you’re going to have to script the data move out or use some other functionality to move the data over to a lower version. You can’t directly restore or attach higher version databases to lower version. There’s a whole bunch of upgrades and changes that go in when a database is moved to a newer version or created on a newer version. It expects to be on that version or higher. So, no such thing.

 

Erik Darling: Here’s one, “Is shrinking a data file resource expensive and/or affect other dbs in a negative way?” Yes. It takes a lot of CPU and it takes a lot of disk IO to shrink a database, especially depending on how big it is and how big the shrink is. It takes up a lot of resources to that. So yeah, I wouldn’t haul off and just keep shrinking the database. If it’s low use and going to be archived and decommissioned anyway, then just stop messing with it. Just leave it alone.

Richie Rump: Leave it alone, man.

 

Erik Darling: There’s a question here about sequences over identity columns. Have you used sequences much?

Richie Rump: Yes, I’ve messed with them, sure. Yepper.

Erik Darling: What are your feelings on them?

Richie Rump: If you’re using a sequence, it’s mainly that it’s some sort of knowable type thing. I wouldn’t go to a sequence if I need an auto-numbering type system, like most databases have these days. Identity column is exactly what you need. Sequences, like say you want to skip numbers or something of that, or if you need to loop back, sequence does a looping thing too and it does it pretty well. So there’s a lot of cool things that you could do to it but it’s not for like a unique ID of a row or something like that. If you want to do something that’s kind of numeric but you need to do something funky with it, sequence is probably your way to go as opposed to an identity. But if I want to uniquely identify a row, identities is what you need to go with.

Erik Darling: So for sequences, I think they’re pretty cool, especially if you need to hand out unique numbers to a few tables. So like multiple tables can reference a sequence and kind of get values from it. So if you need to do that, that’s kind of nice. Although the circumstances where you’d need to do that probably are kind of weird.

Richie Rump: Yeah, I would question that architecture at that point and really ask, “Well, why do we really need to do this and why is the structure in this manner?”

Erik Darling: The other thing to be careful with sequences is sort of like when if you go to like bulk insert or insert a bunch of data to an identity column and it fails, you lose all those identity values. SQL is like, “I was going to give those out… but then no, I’m not going to give those out…” So with sequences, depending on how many you cache, if you reboot or if you lose a transaction, you could also lose a whole bunch of sequence values. So if you’re depending on ordered numbers, ding, ding, ding, one through, everything unique and in order then neither one is really perfect for that either.

Richie Rump: Nah. You shouldn’t be having your clustered index on those anyway, if that’s the case. If that is indeed the case, then you should probably have that as some sort of alternate key or something.

 

Erik Darling: Fellow human says that they had an outage last night and they were seeing lots of the following error, “Time out occurred while waiting for buffer latch.” There’s some page information and there’s some seconds and it says, “Continuing to wait.” I’ve never seen that error either. I would definitely post that on dba.stackexchange.com. I’ve never seen that one. I wouldn’t have a good answer for you.

Richie Rump: Hmm, was there a site that went down last night that got a lot of social media play?

Erik Darling: I don’t know. I don’t pay attention to the internet at night.

Richie Rump: Maybe.

 

Erik Darling: Let’s see here. “Is there a tool to check for find compatibility issues before changing compatibility level for a database?” Hmm. You can check the Microsoft data migration tool… yes, the Upgrade Advisor. Yes, there we go. Microsoft provides a tool called the Upgrade Advisor. It also has a couple lists of breaking changes and stuff, a couple websites that list breaking changes between versions that might be helpful to you.

Richie Rump: Yes, identity also loses, sometimes loses value, yes.

Erik Darling: Yeah.

Richie Rump: Correct.

Erik Darling: I said that. I said. I said if you try to do an insert and it errors out, you can lose values.

Richie Rump: Yeah, you can lose it, but then again, it’s an identity column and why should we really care?

Erik Darling: No, it is a surrogate key. If you want your data key to be an identity column, you have issues. Issues beyond compare.

 

Erik Darling: Going on down the line, “Is there a guide/list about basic errors which should be set up as alerts on a common server?” Yes, if you go to brentozar.com/go/alerts there is a list of errors that I believe Jeremiah compiled way back when. It covers the high severity errors which are 16 through 25, which is the stuff that you should probably be paying attention to. Some of the lower numbered ones do get a bit chatty, like some of the stuff below 19 can be a bit chatty with like failed logins. So you can make your own choice on whether you want to get all those emails or not. It also covers some pretty important corruption errors, 823, 824, and 825, which cover some of the hard and soft IO issues that you have going on. But you have to make sure that your databases are set to have page verification turned on I believe in order for those to work because they check [inaudible] alerts on the checksums there.

 

Erik Darling: All right. We do not have any other questions in the queue.

Richie Rump: I wonder if the folks online can figure out what kind of keyboard you’re using by your keystrokes when you’re googling, that would be interesting.

Erik Darling: No, probably not. It’s a very obscure keyboard. Just kidding. It’s not very obscure at all.

Richie Rump: You’ve got a standard Microsoft keyboard.

Erik Darling: No. I got this Logitech one that doesn’t have key wells. It’s like you can dunk it in water if you wanted to clean it. I like it because the keys are kind of thick and chunky and they have like good noisemaking but they don’t…

Richie Rump: Mechanical. You’ve got to go mechanical, man.

Erik Darling: Yeah, but I have a beard and I eat lunch in here. So it’s not happening. Because when I clean out this keyboard that doesn’t have wells, it’s already not pretty.

Richie Rump: I cleaned out mine this weekend. I pulled all the keys and vacuumed it and did all that stuff because the amount of cat fur that gets in this office because of cats just sleeping on my desk is crazy. And yes, thick and chunky keys. Those are important. It’s almost like dice, you like your thick and chunky dice, thick and chunky keys. They’re important.

 

Erik Darling: “If we suddenly needed to encrypt some propriety data on our SQL 2008 and 2014 instances, what would be the easiest way to do that?” Well, encryption covers a lot of different stuff. Shane just brought up that you can encrypt backups. If you’re on Enterprise edition you can use TDE to encrypt your data at rest but that doesn’t help your data in flight. Then, let’s see… no, that’s 2016, so that doesn’t count. Those are basically your options aside from buying a third party tool that encrypts stuff. Richie, how would you encrypt some sudden data?

Richie Rump: Like you said, it depends on the requirements that we need. If it just needs to be at rest and everything else is cool then typically TDE would be—and I have Enterprise, right—then TDE would be the way to go. It’s fairly easy to configure. Don’t lose the certs, right, those type of things. You could actually implement encryption on the app side, of which I’ve done multiple, multiple times. Fairly easy to do, keeps the encryption out of the server which means all the CPU cycles happens on an app server which is probably beneficial since you’re paying so much money for your SQL Server licenses. But then again, if you ever want to get data in and out, it’s got to go through the app server, so there’s definitely pluses and minuses to both.

Erik Darling: Yeah, one of the really tough things about encryption is that, especially prior to 2016, TDE can kind of break a few other things, like you can’t compress your backups anymore. You can still compress columns and data but aside from that you’re a little bit out of luck.

 

Erik Darling: “What do you recommend for data masking? Social security numbers, accounts information?” Probably just hashing the columns, that’s about what I would do. Like if it’s just a column-level thing, I would just either hash it and have a [inaudible] somewhere. Or create a computed column that hashes the columns that you’re interested in encrypting and don’t let anyone at the root columns.

Richie Rump: Right. The other way you could do it is physical partitioning, right? Create separate tables for the different levels that you want.

Erik Darling: Or views.

Richie Rump: Or what?

Erik Darling: Views. You can use views so people only access the views and the critical columns aren’t included in the views.

Richie Rump: Yeah, absolutely.

 

Erik Darling: All right, before we take off, fellow human said, “Did you answer my question about buffer latch problems?” No. I said to post that on dba.stackexchange.com. I have not seen that error personally and I wouldn’t want to speculate. So I would post that over there to get some more eyeballs on it.

Erik Darling: With that, we are at the 45-minute mark. Richie is going to go continue to run around nude on his lawn yelling “Cubs win!” instead of working.

Richie Rump: Yes, with only my W flag because that’s the only thing I’m wearing now for the past seven days.

Erik Darling: Only the W flag and your imagination. All right everyone, see you next week. Thanks for coming.

Richie Rump: See ya.

This week, Brent, Richie, Tara, and Erik discuss vendor licensing, rebuilding indexes, analysis services, using trace flag 174, and the team’s PASS Summit impressions and highlights.

Here’s the video on YouTube:

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

Enjoy the Podcast?

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

Office Hours Webcast – 2016-11-02

 

How do I license SQL Server in virtualization?

Brent Ozar: Well, we got questions piling in here we might as well ask. Let’s see here, we have a licensing question.

“I need the Microsoft doc for licensing all cores on a VM host with …” This is just a rambling bunch of words. “I need Microsoft documentation for licensing all the cores on a VM host with Enterprise, allowing all SQL VMs on the host to be licensed.”

I think what you’re looking for is the licensing guide. If you search for “Microsoft SQL Server Licensing Guide,” it’s one of the rare pieces of documentation that is wonderful. It has pictures. It’s fantastic. I love the licensing guide. It’s really good. Have any of you guys had the chance to read through the licensing guide?

Tara Kizer: God, no.

Erik Darling: I only read through what I have needed to and it’s been precious little, thankfully. There were some questions about VMs and stuff and I was like, “Okay.”

Brent Ozar: It’s great. It’s got great character development. The plot is wonderful.

Richie Rump: The ending kind of sucked but, you know.

Erik Darling: If the guide doesn’t suit you, there’s like a 5-day licensing class that you can take from Microsoft. You can learn the joys of licensing.

Brent Ozar: There’s a company called Directions on Microsoft. One of the guys who does it is @GetWired on Twitter. They just have a boot camp on licensing. If you think our training is bad, just wait.

Richie Rump: Best 5-days of sleep I’ve ever had.

Brent Ozar: That’s probably what you need to watch before … Are you going to take a nap or anything before the World Series game, or are you just 100% wired?

Richie Rump: I am completely wired. I could barely hold it together. I don’t know what to think. My head is saying they’re going to break your heart, they’re going to lose in spectacular fashion by being up 3 runs, a grand slam in the 9th inning with 2 outs and 2 strikes and Chapman on. That’s what my head is telling me is going to happen, but my heart is saying they’re going to do it tonight. I am this split person. I am a shell of a man. I am a complete and total mess right now. I am wrecked. I am ruined.

Brent Ozar: That’s Wednesday. It’s normal.

Richie Rump: Pretty much. Yeah.

Erik Darling: Is that something in AWS or is that the Cubs? I’m sorry, I didn’t get that.

Richie Rump: Oh. I came into work in Azure today… and they just, whoa…

 

What should I do with all these Fusion-IO drives?

Brent Ozar: Next question.

“Hi, guys. We see high latency reads and writes currently, so our company purchased Fusion-io drives for a 2-node cluster running system manager in a data warehouse. We want to take advantage of the Fusion-ios. What should we do with them? Would you recommend buffer pool extensions? Right now we have 256 gigs of RAM with no memory issues. Or in-memory OLTP, an attempt to move …” Well, in-memory it doesn’t matter what storage you use there, so we can kind of short-circuit that. “What should I do with my fancy Fusion-io drives?”

Man, wow, that’s kind of cool. If you had a couple of Fusion-io drives, what would you do with them? In a cluster, keep in mind.

Erik Darling: Sorry. I didn’t catch … Are these local drives, or are these …?

Brent Ozar: Yeah.

Richie Rump: Yeah.

Erik Darling: They’re local? Oh, yeah. Stick tempdb on those, then. Maybe stick some lock files on them, if you have. Some logs that you want to make sure that you feel speedy with.

Brent Ozar: Well, but it’s a cluster, though. That’s where it gets complicated. If you’re-

Erik Darling: Oh, right, right. Cluster. Just tempdb then. Skip the log files.

Brent Ozar: If you were going to decide what you’re going to do, and really that’s the only option that you have, are there any symptoms that you would look for before you would say, “Hey, let’s go get Fusion-io drives and use them for tempdb?”

Erik Darling: Yeah, you know. tempdb horribleness, the kind that does not get resolved by adding multiple files or turning on trace lags or doing anything fancy. Looking at the slow reads and writes, that can happen sometimes when you’re piling things in. Or just looking at my workload and saying, “Okay, I used the crap out of tempdb. I use a bunch of temp tables, I have triggers, I do all sorts of horrible things that don’t fit in my memory that I have, so I’m just going to make tempdb as fast as possible.”

Brent Ozar: When you guys are looking for where should you invest, hardware or changes in code, indexes, whatever, what kind of places do you go look? What metrics do you go look at? I’ll point to you, Tara.

Tara Kizer: Okay. In my past few jobs we’ve always had a performance test, load test environment and, you know, we got production and we would be able to baseline the systems. We’d be able to do a load for what the load is going to look like 2 years from now, and then be able to determine when we’re running out of hardware. Just the typical things with performance monitor counters, disk, CPU memory, looking at were SQL server is waiting on things. This is assuming we’ve already optimized as much as we could in the database, so just having that load test environment where we could do synthetic production loads and being able to do loads a year from now, 2 years from, if you have that type of environment and tools.

Brent Ozar: If you don’t have that kind of environment, if you just got yourself a couple of fancy Fusion-io drives, it sounds like you just got the opportunity to go build one of those environments. Your load test environment doesn’t necessarily have to exactly mirror production if you got a nice set of Fusion-io drives.

Erik Darling: Yeah, not many people are throwing Fusion-io into dev testing QAs.

Brent Ozar: It sounds like you probably have buckets of money, so hey, if you’re able to just get Fusion-io drives for no apparent reason, that’s freaking awesome.

 

Can I use SQL Server 2016 if the vendor only supports 2014?

“We are looking to upgrade 16 SQL server 2012 instances. They all hold third-party databases and we’re going for either SQL server 2014 or 2016. Is it better to install each of these instances all to 2016, bring everything to 2016 and run all the databases in 2014 compat mode where necessary, or should we just only go with what the vendor supports?”

I think what the root of the question is, if a vendor says they only support SQL server 2014, can I install their databases on 2016 but run them in 2014 compat. mode?

Tara Kizer: If it were me I would not be doing that. You’re potentially breaking the license you have with the vendor, but even still, if you have a test environment some of these vendor applications won’t even start up, because they are checking the server version to see what your version is and you don’t get to pass go. If you wanted to go down the route of 2016 with 2014 compatibility mode you’re going to need to test to see whether or not the application will even load, because they might be checking the server version.

If it were me, for vendor applications I go with whatever they say is they can support. I don’t want to chance running into the issue in production, and you won’t have a fallback plan. If you’re going to go to 2016 you can’t restore back to 2014, it’s a very painful process.

Brent Ozar: Yeah, that’s a good point.

 

“Would you guys recommend running once-a-week stats updates even though you rebuild indexes daily?” Specifically, this person says they use Ola Hallengren’s rebuilding of indexes daily. That doesn’t mean you’re rebuilding all of the indexes, does it? What does that mean?

Tara Kizer: I think he’s got the schedule flipped. I would update stats daily and do the rebuild, reorg, weekly, maybe even less frequently, such as monthly. Quarterly, even. Or how about never?

Brent Ozar: Why is that?

Tara Kizer: You’re not doing it for performance reasons. You are getting to update statistics, which is why people think that you get better performance when you rebuild indexes. Just do your update stats, because rebuilding indexes is a really tough way to get updated statistics. Rebuilding indexes and reorganizing is more about overall database health. It does help with storage, compacts it down, but if you’re just talking about performance, update stats is where it’s at.

Erik Darling: I think that workloads, at least OLTP ones, don’t typically benefit from rebuilt indexes if you’re just doing a lot of single 10 or short-range Seeks on things, you’re not going to see a huge difference if you rebuild indexes. It’s mostly for those behemoth, terabyte plus data warehouse style workloads, where you’re taking that whole table and you’re joining it to that whole table and then you’re squishing it and aggregating it. That’s just few filters in the WHERE clause on that.

Brent Ozar: Next up, someone says they’re rooting for the Indians just because of the movie “Major league”. I saw, Richie’s making a great face.

Richie Rump: I just hope it ends like the movie “Major League”.

Brent Ozar: Ooh.

Richie Rump: Where they win the championship but they lose the World Series. See, that’s what I want to see.

Tara Kizer: Ah.

Brent Ozar: I think Richie’s probably going to be insufferable either way. If they win or if they lose, we’re going to be paying for it in the company chat room for the next week.

Richie Rump: Oh, yeah. There are just going to be tears shed one way or the other tonight.

 

What Perfmon counters should I be collecting?

Brent Ozar: Next question. “Can you guys direct me to a good blog on what perfmon counters I should be collecting?”

As it happens, I’m famous for that. If you go to brentozar.com/go/perfmon, so brentozar.com/go/perfmon, I got a video on there that you can go watch, and my list of favorite perfmon counters and what they mean.

 

How do you simulate production loads?

Mandy asks, “Tara, you mentioned having a performance test environment at your previous jobs. What technologies did you use in order to simulate production load on those test boxes?”

Tara Kizer: I don’t remember all the tools that were employed, but I know LoadRunner was one the tools that were used in my last 3 jobs. My last job, a lot of stuff was just written directly into Visual Studio. LoadRunner was the main tool.

Brent Ozar: Did you guys have a dedicated team of people to manage LoadRunner?

Tara Kizer: Yeah. These were not small companies, so yeah, we had dedicated teams for load test environments. They knew how to use LoadRunner, they knew how to code, they knew how to use all these tools.

Brent Ozar: Richie, what about you? What stuff have you seen used in the past?

Richie Rump: For perf, I haven’t been on teams big enough to have perf testing. It’s always been, “Is it working functionally and we’ll get it out, and if there’s a problem, well, we’ll have to fix it as it comes on.” Perf testing is a whole different other ball of wax. Usually you have, like Tara said, dedicated team does all that for you. It’s just a different skillset that your normal devs usually don’t have.

Brent Ozar: LoadRunner is a good example. It’s an expensive piece of software, it’s a different … I don’t expect DBAs to know how to use it, or developers to know how to use it. I don’t know how to use it. When I go into companies, sometimes every now and then I see a team over there in the corner and they’re like, “We’re the load test team.” Because if you think about it, if you have to reproduce production load, what you really have to do is redo that test every time there’s a new deployment of the software. You add new features in, you learn that people are using it in different mixes, so you can’t just build one test and then call it a day, it ends up being a full time job.

 

Are there compatibility issues with 2016 Analysis Services?

Next question. “We may not have the budget to upgrade all our SQL servers to 2016 yet, or next year. Do you guys know if 2016 analysis services can work with R2’s analysis services? Right now we do cube development on one box and aggregation on another.”

I know they changed the models between tabular and whatever the other one’s called. Not tabular?

Erik Darling: Not tabular.

Tara Kizer: We’re not the right people to ask for this question.

Brent Ozar: No. Who I would check with is Chris Webb, with 2 Bs. Chris Webb does a lot of analysis services blogs. Andy Leonard does a lot of integration services blogs. If you search for “Andy Leonard SSIS”, I forget the name of his company that’s out there. He doesn’t do, I don’t think, analysis services, but he can point you to the right person. Who else does analysis services stuff?

Erik Darling: I would just set up a couple of VMs and try out my use case on it.

Brent Ozar: That’s because you live like a crazy man.

Erik Darling: I know. Because if someone tells me, “Yeah, it’ll work just fine,” then I’m like, “Okay, cool,” but then as soon as I start doing something I’m like, “Wait, I’m doing weird things that this person couldn’t possibly know about or couldn’t have possibly accounted for.” That’s why I always like to, you know … If I get that initial okay, “Is Service Pack 3 cool? Yeah? Okay,” but then when I go to install it, “Wait, I’m doing something weird.” I just like to make double sure that it fits my use case as well as general, “Okay, that works,” but who knows what things people have tested and what things they haven’t tested.

Brent Ozar: Development edition is totally free, too. You can go download it, or if you go to SQLserversupdates.com I’ve got a link where you can go grab it from.

 

What should I do about high memory usage?

Next up, we have high memory usage, I don’t think he’s referring to marijuana, but, “Since increasing from 256 gigs to 512 gigs our monitoring metrics show a big increase and there’s no complaints from end users. We upgraded to SQL 2012 and memory is our largest wait.”

I’m going to ask you a follow-up question there before we tackle that. When you say memory is your largest wait, what do you mean by that? What tool are you using to gather that data, and what’s the specific word on the screen that says, “We’re waiting on memory”? I want to see a little bit more about that. I have a hunch, but I don’t want to push anybody under the bus until I see it.

Erik Darling: Also, if you increase the amount of memory you have, you’re probably going to increase the amount of memory that you’re using.

Brent Ozar: And if users aren’t complaining …

Erik Darling: Because servers use every bit that they can.

Brent Ozar: What do SQL servers use memory for?

Erik Darling: Everything.

Tara Kizer: Everything.

Erik Darling: Everything. What doesn’t it use memory for? That’s a better question.

Tara Kizer: I just wonder if they’re looking at Task Manager and just seeing available megabytes. The SQL server went ahead and used all that memory and that’s normal. SQL is going to use that 512 gigabytes, or whatever you allocated to SQL server. Make sure you also updated your max server memory setting if you changed it when you were on the lower amount of memory.

 

Automate restores to dev (wat)

Brent Ozar: There’s an interesting one that’s not even really a question, but it’s a good topic. “Automate restore to dev.” Rather than playing Alex Trebek and saying, “Please format your answer in the form of a question,” … That actually is a good- If you guys were going to regularly automate restores from production to dev, what tools would you use and why?

Tara Kizer: You know, I’ve got a really old blog on this topic because I’m not sure that I would use that method now, but there’s a script out there. It grabs the latest backup from a folder, pulls it over, I think it even zips it, it was some kind of old utility back then. Restores it on the server and it’s got everything in there. These days, though, if you’re using a SAN, we do SAN snapshots, and then just pop it over and we’re good to go. That was mostly for CHECKDB process, not necessarily for dev.

Brent Ozar: We have a follow-up, he says, “For prod load testing you should automate restores to the dev.” Yeah, but that’s only the data, you still got to have your workload on it. Unless your workload is restores, in which case …

Tara Kizer: Yeah, we did that manually. We did it about twice month, refreshing our load test environment, but it would be dependent upon the load test team when they needed those refreshes. I should say it’s done manually, but it was all automated, they just had to click a job to do it. Basically the same thing, grab the latest backup, pull it over, do the restore. We were using availability groups environment, so you set all that up and magic, it was all there.

Brent Ozar: Once you know where your full backups are going it’s pretty easy to pull the most recent file out of that folder.

Richie Rump: It still may not be a good idea to use production data, because production data still might not reach all of your use cases that you need to test, especially on the outer rim, stuff that shouldn’t happen but probably will at some point. The data is probably not going to be in production. As well as, there may be some sensitive data in there as well that you may have to clean, obfuscate and/or remove. [inaudible] data, although it’s good and you test your queries in running it and stuff like that, it probably doesn’t have everything you need if you’re running adequate testing against it.

Tara Kizer: One of my struggles with the load test environment was, they had all these wonderful scripts in LoadRunner, and they said, “This is a production-like load,” but when I would dig into the details and I’d run a trace in a load test environment and run a trace in Production or Extended Event or whatever tool you’re using, and I would see that the stored procedure execution count would be so much higher in the load test environment than it was in production. I was like, “You know, this is not realistic, this is not what we’re seeing in production.” I don’t have an answer for it, it was a really hard thing for them to figure out. How do you make sure that your load test environment has a real production-like load and not just what we think production looks like?

Brent Ozar: I totally saw that with … I was involved in one project where we did load testing, and the load test had thousands of people concurrently editing their profile, but it was the same user over and over again, so we ran into all kinds of … Yes, you’re going to have blocking problems when the same user is updating their profile across 5000 sessions.

 

Why does an SSIS package run slower as an Agent job?

I got the next question. “We got an SSIS package that takes way longer to run under an SQL agent job as opposed to running it in BIDS and Visual Studio. What would I look at to determine why a query runs fast in one place and slow in another?”

Erik Darling: We’re at the 30-minute mark, so we should probably bring it up.

Richie Rump: Yeah, look at that. Amazing.

Tara Kizer: I’m surprised it’s that way, it’s usually the opposite. When you run it through BIDS, Visual Studio, it’s got the GUI, it’s generating all kinds of information going across. I haven’t seen it in that order, usually it’s an agent job that runs faster. I wonder if maybe the SQL’s agent job is pointing to a resource that’s maybe further away than when you run it in BIDS. Maybe there’s a remote share that’s on your subnet but it’s further away for the agent job. I don’t know, I think that there’s probably some kind of remote resource that we’re talking about here.

 

Why does SolarWinds say I’m waiting on memory?

Brent Ozar: We got a follow-up on the memory question, and it is exactly as I suspected. There is a popular monitoring tool called SolarWinds, and this person says, “We’re using SolarWinds and memory CPU is listed as the metric.”

By all means what you want to do next is go talk to SolarWinds, talk to their support team, and ask what you should do about that wait. Maybe more specifically, ask exactly what that wait type means. It’s a very common question that we get amongst SolarWind users. It does not mean that you’re waiting on what you think it does.

Erik Darling: Also ask why it’s red.

Richie Rump: Brent wouldn’t know that at all, by the way. He just …

Brent Ozar: I only know because people tell me. They are like, “This big huge bar here, it’s red, that must mean something,” and I’m like, “Well I’m red-green colorblind so it doesn’t mean anything to me.”

 

How was the PASS Summit?

Next question. Someone says, “How was PASS? I really enjoyed your topic.”

Well, thanks! We should actually each talk about the thing we liked the most out of PASS summit, or what it meant to us. Richie, you’re next to me, or Sammy Sosa is next to me on the webcam, so I’ll go with you.

Richie Rump: Sammy Sosa.

Brent Ozar: Sammy Sosa.

Richie Rump: Wow. PASS was different for me. It was the first time I came with a company and not just by myself, so it was totally a blast because we don’t actually get to hang out very much.

Brent Ozar: That’s true.

Richie Rump: People go in the office and see each other and stuff like that. We see each other maybe 3 times a year and that’s it, so getting a lot of face time with everyone I think was really the highlight of my PASS. Kiss up.

Erik Darling: Dear coworkers …

Richie Rump: Remember it come from me, Erik.

I think the other part for me was, I thought the keynote was good and the sessions were good, but they’re always good. There are always good SQL Saturdays, because this is our community, but it’s outside of the community that I actually had a blast with some of the parties. Playing board games at 1:30 at night was a highlight for me, so that was a blast with some of the fellow PASS folk. That was great. I had a blast. I had a good time. Not a lot of Cub wins but that’s okay, we’ll fix that tonight.

Brent Ozar: What I got out of that is, Richie didn’t really like the conference, he just likes to hang out afterwards and play board games. We’ll remember that for Richie’s career planning for next year. Tara, you’re up next.

Tara Kizer: I really liked hanging out with my coworkers because we don’t get to see each other very often, so that was nice. Great free time.

Richie Rump: [crosstalk] Cubs sessions.

Tara Kizer: Networking, meeting people that we talked to online for a long time and seeing them in person, that was great. The sessions that I did see were good. One of the challenges I had is that when there was a session I wanted to see, there were like 4 sessions at that time that I wanted to see, and then there were entire times where there was nothing I wanted to see. I just wish it was more spread out. I felt like the people had the same problems because I kept talking to people and they’re like, “Yeah, what are you going to see at 3 o’clock,” or whatever time it was, I’m just using that as an example, “Oh, there’s nothing interesting to me.” It was the same thing over and over again.

Brent Ozar: I almost wish they had tracks of, one 400-level track, one BI, just specific tracks where you know that if you’re interested in one topic there’s always going to be a different session available for it. Erik, how about you? This was your first time at PASS.

Erik Darling: Yeah, it was my first time, so I really enjoyed getting to meet people who I had only interacted with on the old internet for a while. That was really cool. Kalen Delaney, Grant Fritchey, Steve Jones. Joe Sack was a big one, I loved talking about … Actually, my favorite part was probably hanging out with the Microsoft people and talking about all the new stuff coming down the pipe that they’re working on, you know? Mostly because it’s stuff that I complained about in blog posts, so it’s, “Maybe you did read that. Cool. Good for you, you weren’t lying to me at all.”

Brent Ozar: It’s amazing where, if you get to stand around in the Microsoft booth you get access to all this NDA-ish type stuff where they’ll talk through and answer questions and, “Does this really help you? Are you interested in this? Tell us about your use cases.”

Erik Darling: Yeah. All sorts of lions and tigers and bears out there, it was great.

Richie Rump: I saw the booth. I saw it.

Brent Ozar: Just walking past it?

Richie Rump: I was walking, I kind of peeked my head and, “Oh look, there it is.”

Erik Darling: He was on his way to play competition Parcheesi or something.

Richie Rump: Vast. Vast, and I was not the Cave this time.

Erik Darling: Okay.

Brent Ozar: I would have to say that, too. I used to think, “I’ve enough access where I can talk to Microsoft whenever I want, or I can go email them or whatever.” That was my favorite part of the summit this year too, just the number of Microsoft big guns who were there, who were really fun to talk to. It’s just amazing how you can… Slava Oks I’ve never met in person. His laptop was there and the guy was just coding, and I’m like, “Oh my god, that’s the clock hands guy.” I read his blog post. I don’t understand any of his blog posts, but I read them. My god.

Erik Darling: Not because his English is bad, because it’s too good.

Brent Ozar: Yeah. I’m not smart enough. It takes me four reads to get through those.

One of the attendees says, “I love Brent’s shirt ‘Surprise, I’m drunk’.” Yes, that-

Erik Darling: Except that doesn’t [crosstalk] no one surprised.

Brent Ozar: That’s true. I got to start shopping for next year.

Richie Rump: Too bad you didn’t wear that on Tuesday morning, Brent.

Brent Ozar: Man, jeez, yeah. Or Monday evening. Holy cow, so we did … I should say, this is why the team really says that they like hanging out with their coworkers. This is the whole deal. Monday night we did an 8-course tasting menu with wine at MistralKitchen. I think we were the only people there, we closed out the restaurant, and then we ended up with, they were like, “Well, what else do we have? Let’s give you guys a whiskey tasting menu.” Then Erik and I did some real damage to our livers at that point.

Erik Darling: Tuesday morning the T-shirt should’ve said, “Surprise, we’re still drunk.”

Brent Ozar: Oh, god, that was rough.

Erik Darling: Yeah.

 

Does TDE cause problems with AGs and replication?

Brent Ozar: Let’s see here. The next technical question is, “We’re looking for a geo-replication architecture and we wanted to include Always On Availability Groups across multiple sites, plus replication across multiple domains, plus transparent data encryption.” I think that they’re just putting things into a stew at this point.

Richie Rump: I think this a headhunter trying to get Tara. “We have all these things, perhaps you’ve heard of them.”

Brent Ozar: Sounds like a Bingo card. We need little Bingo cards for this.

“Does TDE cause any issues for such an architecture?” Tara’s making the face.

Tara Kizer: I haven’t used TDE with replication or availability groups, so I don’t know what kind of challenges that might have. I know TDE adds performance overhead.

Brent Ozar: Normally the thing I ask with TDE is just that, “Okay, so what’s the goal here?” If you’re just trying to encrypt data at rest there’s a lot of communication that happens around from place to place where TDE is not involved. When I see replication, that means we’re going across from one SQL server to another. I don’t even know if the distribution database is actually encrypted with TDE, I don’t-

Erik Darling: I don’t know if it is, but I don’t think … That whole time the data is going from New York to Dublin it’s not encrypted.

Brent Ozar: That would be a lot-

Erik Darling: I don’t think.

Brent Ozar: Yeah, so I don’t think TDE would cause a problem. I don’t think TDE is going to cause a problem with replication either, because it’s only on disk that stuff is encrypted, so it shouldn’t cause a problem. I would just say, make sure you know the benefit that you’re getting there, especially with TDE with AGs. If you ever rotate your keys with TDE it’s re-encrypting the entire database. That is a ton of log traffic that’s going across from one replica to another, and while the database is re-encrypting … I haven’t looked at this on 2016, but I know in all the prior version you could not take any backups while the database was re-encrypting, so that’s kind of sketchy when you talk about large amounts of data there. Not that I don’t like TDE. TDE is fine. There’s nothing wrong with it. There’s lots of things wrong with it, but you know where I’m going with that.

Erik Darling: At least in 2016 you can get backup compression with TDE finally.

Brent Ozar: Elaborate – how does that work?

Erik Darling: You have to specify the max transfer size. You just have to set it above the 65536. I think that’s the right number. 65 whatever. You just set it above that. I blogged about it a while back, if you’re really that interested you can do a search for it. I think it was “TDE backup compression” on ye olde Ozar site.

Brent Ozar: It’s not enough to just enable backup compression, you also have to change this obscure setting, max transfer size, even if you don’t need to change max transfer size. There was a Microsoft-certified master, Bob Pusateri, who did a session at PASS on backup compression and I’d sat through it at our local user group, he’s here in Chicago.

I’m like, “Backup compression? You just turn it on and off. That’s all there is to it. You want something more than that, you’ll get a third party,” yeah. It was great. He had all kinds of, there were trace flags that would output to the Messages tab and show you the effect of different max transfer size and buffer count settings. It was really impressive. If you went to the PASS summit and you got the recordings, or if you’re going to watch the recordings via streaming, I would actually recommend that session a lot for those of you who have databases of a terabyte or more.

 

Should I use trace flag 174?

We have one other one. “Is there a benefit to using trace flag 174? This increases the prod cache. We are in an OLTP shop with tons of ad-hoc queries.”

Well I have an opinion immediately. [inaudible] fragmentation says …

Erik Darling: That is the last thing that I would do for that. I would first, if you’re not already doing it, switch on Optimize for ad-hoc workloads. Just get a plan stub so you’re not blowing things out with single-use plans. Then after that I would also probably turn on forced parameterization, so that if there is a chance the parameterized plan can get reused then it’ll do that. Especially if they’re all ad-hoc stuff and they’re just getting passed in the string, it’s not getting parameterized code, SQL is not going to parameterize it because it just sees, “Oh look, new string, I’m going to do something new for this,” and you just have a billion of the same plan over and over again.

Brent Ozar: Especially if you have a lot of ad-hoc queries, adding more memory to the plan cache is exact opposite of what I would want to do. I don’t want to give ad-hoc plans more RAM in the cache, especially if you’re an OLTP shop with lots of ad-hoc queries. Those ad-hoc queries are probably relatively easy to compile, especially if they’re reporting type queries. I’d rather have that memory available to cache data. That’s much more where I’d want to go. We don’t get a whole lot of buttons we need to tweak around the plan cache and its size.

Erik Darling: You might even just free the prod cache every once in while.

Brent Ozar: Dang! Look at you… What’s your logic there?

Erik Darling: Well, I just don’t care about it. It’s probably a bunch of single-use plans hanging around waiting the get aged out. This part has nothing all that important in there. If I take a look at my plan cache, or say if I run sp_BlitzCache and I look at the execution count column, and I just see a bunch of single uses, or 1 or 2 uses, I’m just going to clear it out and let things every once in a while naturally refill.

Brent Ozar: Got it.

Erik Darling: I’d rather give that space over to something that could actually benefit me, and caching single-use plans is not it.

Brent Ozar: Do not try this at home, ladies and gentlemen.

 

Are there any good SQL Server podcasts?

The last question we’ll take is, “Are there any good podcasts relating to SQL servers?”

Well, of course, there’s this one. Then other ones, I’m going to get my iPhone out from right here just to make sure I don’t miss any, because I subscribe to a bunch of them.

My favorite one is probably SQL Server Radio. Comes out of Israel, with Matan Yungman and Guy Glantser. They’re very funny, they take on all kinds of weird little topics. Somewhere around I think an hour in length, and cover a lot of interesting different technical things. It’s almost always Guy and Matan, that’s SQL Server Radio.

There’s SQL Down Under, with Greg Low out of Australia. The problem is he doesn’t update it very often. I mean, he goes months, sometimes even a year or more, without updating it. The past episodes are really good if the technology applies to you. He has access to Microsoft people, so he gets podcast interviews with Microsoft program managers, for example.

There’s also SQL Data Partners with Carlos Chacon and Steve Stedman, where they take a different approach than the other podcasts. They take a presenter on, and basically talk the presenter through a training presentation. They talk to the presenter about what his material is and basically turn the presentation into an interview, so you learn technical things along the way. Steve Stedman is also the guy behind the Database Corruption Challenge. If you are a production DBA who likes playing around with recovering from corruptions, search for Database Corruption Challenge.

Kendra Little has Dear SQL DBA. Dear SQL DBA is also available on YouTube and as a podcast form, where Kendra takes a question from the audience and goes really deep, like half an hour or more on the same subject, so she goes really far in depth. As opposed to us, we just ramble a lot.

Richie Rump: And her hair is purple, too, so that’s a bonus.

Erik Darling: Also, it was great meeting Kendra for the first time at PASS.

Brent Ozar: Oh, that’s funny. Hi, how are you?

Did I miss any SQL server podcasts? I think that’s it.

Erik Darling: Nope.

Brent Ozar: I ask because we would be remiss if we didn’t say, Richie’s Away From The Keyboard podcast. Richie, talk to the good folks on the audience about what your podcast is about.

Richie Rump: We talk to technologists, but don’t talk about technology. We’re trying to get inside the head of the individual and talk to them about their likes and dislikes, and their hopes and their dreams, what do they think about the future. It’s a good time. What we want to do is, we want to see what they do when they’re away from the keyboard. We have a new episode coming out today after I clock out of work.

Brent Ozar: If you’re listening to Away From The Keyboard for the first time, the current episode that’s on there, Jesse Liberty, get Part 1 as well as Part 2. It’s a really good interview, really compelling guy.

All right, well thanks everybody for hanging out with us this week. We will see you next week at Office Hours. Adios, everybody.

This week, Brent (and only Brent) answers your questions about table partitioning, best practices for VMware configuration, rebuilding indexes, transitioning from physical SQL failover cluster to VMware or Hyper-V, database backups, security issues with cross-database chaining, and why you should never use vague adjectives when describing database sizes.

Here’s the video on YouTube:

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

Enjoy the Podcast?

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

Office Hours Webcast – 2016-10-19

 

Brent Ozar: All right. We might as well go ahead and get started. It’s eight minutes after the hour. Now, we’ll start going to the questions, folks. Don’t forget, get your questions in now because we may end up coming fast and furious and I just go first come, first served, so if you get them in later, you probably won’t get caught.

First question is, “We have done the table partitioning, but we did not get the performance. Why?” Yeah. That’s actually really common. Most of the times, when we see partitioning implemented, it makes queries slower rather than faster.

In order for partitioning to make your queries faster, one of two things has to happen. You either have to be loading and deleting your data at the whole partition level. Meaning, like you delete a whole partition at once, you load a whole partition at once, and you don’t have any overlap between the other partitions. If you do that, you can utilize a technique called ‘Sliding window loads’ that you let you load a separate table, and then swap in just one partition. That only makes your loads and deletes faster though. It has nothing to do with SELECT Queries.

That’s the first scenario where partitioning goes faster. The second scenario where partitioning goes faster is when your WHERE Clause in your SELECT Queries, when the WHERE Clause specifies the exact couple of partitioning keys that you use or the one partitioning key that you use so the SQL Server can narrow down your SELECT to just one partition. If SQL Server can’t do partition elimination, then you’re still scanning all of the partitions. It’s not going to be any faster. If you go to ‘Brentozar.com/go/partitioning’, we’ve got a whole series of posts explaining how you can tell whether or not your database makes sense for partitioning or not.

The big clue is if you’re doing OLTP type work, if you’re doing inserts, updates, and deletes of individual rows and your SELECTs, the WHERE clause doesn’t have your partitioning key in it, partitioning is going to make things worse instead of better.

 

Next up, we have a question that says, “Is there a document that outlines the best practices for VMware configuration for SQL Server?” VMware actually has several, so if you search for ‘VMware SQL Server best practices’ or ‘VMware SQL Server performance tuning’, VMware has put together several documents. The thing is you’ve got to read them with a critical eye because they’re not perfect. For example, VMwares, the last time I looked at their best practices for SQL Server, it advised you to start with single processor VMs and they mean one core. There’s a couple of big problems with this.

The first problem is if you’re licensing by VM, the smallest license you can get is four cores. Sure, you can go buy a two core pack of licenses, but the minimum you’re allowed to buy for installation is four cores, so if you’re licensing by the guest, you’re just leaving performance on the floor. The second part to that is I don’t know about you, but I like doing backups. I like doing check DB, and if I’ve only got one core, that can make it very easy for the SQL Server to feel unresponsive or overloaded when I’m trying to do say a transaction log backup which I hope to God is happening on a fairly regular basis. You have to read through these guidelines with a little bit of a critical eye and start asking questions, but they’re a great place to start, so search that just for that on VMware.

 

Next up, Heather says, “I have a replication question. I’m getting an error about the length of lob data to be replicated exceeds the maximum of 64K, but I’ve already configured max text replication size to negative one. Where else can I look? I’m on 2008 R2.” I actually don’t do any replication work myself at all, so I would hit good, old Books Online.

Then, if you don’t find it in Books Online, watch the slides that are rotating through on the screen. Those of you who are only listening to the podcast, you don’t have this luxury, but there’s a link on there for Stack Exchange. If you go to ‘DBA.Stackexchange.com’, there are several people on there who are great with replication. Tara Kizer, even our own Tara Kizer who’s now on the screen does a lot of replication works or has done a lot of replication work in the past. She can answer questions on there as well.

It’s funny and there are certain black holes in my knowledge area. Replication is a big one. SSIS is a big one as well. CLR is another big one.

 

Next question, “We have databases created from various master databases”.

Not exactly. They say “We’re looking to move to Amazon RDS. Databases that have the same family GUID isn’t allowed in RDS. Is there any way to change the family GUID?” I’ve never seen that.

I’m not exactly sure what you mean by that and I’ve never seen that restriction before. Do me a favor because I’m personally curious about that because I do a lot of RDS work. Email us and email us with a couple of different things. I want you to include the exact error that you get when you go to restore it over in Amazon RDS, and I’m assuming that you’re doing in a restore because that’s the only way that I would see a problem with family GUID, so yeah. Just give us the exact error. If it does turn out to be something that RDS truly doesn’t allow, the crappy way that you’d have to do it is to do an insert into the brand new database using something like SSIS or BCP.

Those are horrible options. I would much rather have you be able to do just a restore, so email us the exact error message. Don’t put it in here in the Q&A panel because I won’t be able to dig deep enough, quickly enough.

 

Our next question, “I rebuilt my INDEX, but it’s still fragmented”. There’s a few possible different reasons for this.

The most common one is it’s a really small object. It has less than say a hundred AK pages in it or in cases like that, SQL Server may not just be able to cram those pages absolutely full because you can’t fill up all the pages. You just don’t have that much data. It can also have to do with the size of your objects. There’s all kinds of interesting things it can have to do with.

What I would start with this, if you’re doing INDEX REBUILDs, make sure that you’re using something like Ola Hallengren’s maintenance scripts. Ola Hallengren’s maintenance scripts have a size threshold on there saying only rebuild objects over a certain number of AK pages. The other reason I’m passionate about that one is if you have an object that’s only a few hundred AK pages, it doesn’t matter whether or not the thing is fragmented. You can catch it in a RAM because it’s so tiny. Fragmentation doesn’t matter on objects that small of size.

If it’s not a small object, just off the top of my head, some of the other things that can cause for example are Heap. If you have a Heap and you’re doing ALTER INDEX REBUILD on certain versions of SQL Server, they don’t support rebuilding a Heap. I want to say it was 2008 R2 where we got ALTER TABLE REBUILD. Ola Hallengren’s scripts don’t catch Heaps. They don’t rebuild Heaps on purpose.

There are other INDEX REBUILD tools out there as well. Minion has one. MinionWare has a Minion Rebuild or the Minion Reindex that does catch Heaps, but it also has that threshold parameter for how larger objects should be.

 

Next up, “What is your personal recommendation for tempdb setup, multiple files or SAN versus local storage?” In terms of the number of files, the easiest way to check is in our setup guide.

If you go to ‘Brentozar.com’, then down at the bottom, there’s a form you can fill out to get all our download kits, including things like our setup guide. That way, you’ll see exactly how we recommend setting up new SQL Servers from scratch and tempdb is inside there as well. I can never remember offhand whether we have four or eight files in there that are listed and I don’t really care which one of those two you’d choose. Either four files or eight files. Then, in terms of whether I want shared or local storage, if I’m dealing with a VM, I don’t really have a choice.

It’s all going to be on shared storage. If I’m dealing with a Failover Cluster or a physical box, generally, I want cheap, local Solid State. I want cheap, local Solid Stage for two reasons. One is it keeps the SAN bandwidth clear just for user database traffic. Often, I find that that bandwidth between my SQL Server and its storage is the biggest confining factor in terms of storage performance.

Second reason for that is shared storage is a whole lot more expensive than the cheap, local SSDs you can get for servers these days. Even enterprise-grade servers are around or even enterprise-grade shared storage stuff is around a thousand dollars a terabyte. Intel’s recent PCI express cards are actually a thousand dollars for two terabytes, so it’s a pretty good steal. You can’t get shared storage that cheap.

 

Next up, “Could you see any performance drops on a composite primary key with four columns?” Sure.

Absolutely, and I hate the answer “It depends” because I always want you to understand what it would depend on. What it’s going to depend on is your workload, and unfortunately, that’s kind to be on the scope of something I can answer fast here. We actually have a two-hour session on that in our in person training classes. If you go to ‘Brentozar.com’ and you click on ‘Training’ up top, we’ve got whole upcoming classes in San Diego, Seattle. We’ve got one, although the Seattle one is sold out.

I believe we’ve got another one upcoming in Pittsburgh. I believe it’s Pittsburgh.

 

Let’s see. Next up, “Have you used either Redgate SQL Toolbelt or IDERA SQL Doctor, and is there one you like over the other?” I have a really weird job.

I love my job. It’s just weird, and because I have to constantly parachute into somebody else’s SQL Server, I can’t rely on them having tools installed and I can’t mandate that they go buy tools. Having said that, when I had a real job five years ago when I was a DBA, I liked Redgate SQL Prompt a lot. SQL Prompt is like IntelliSense but way better. For example, if I have relationships to find in the databse, SQL Prompt will automatically complete them on my joins.

It has gotten expensive. I want to say the SQL Prompt is now around 500 bucks and it’s tough for me to justify that. There are other auto complete tools out on the market these days. For IDERA SQL Doctor, it really does something very different. It does health check stuff against your database, so they’re not really inside the same market.

In terms of third part monitoring tools, the three big ones that are out there are Dell Spotlight, IDERA SQL DM, and SQL Sentry Performance Advisor. Those are the three that I place at the top tier up there. Nothing at Redgate. Just a product doesn’t have the level of guidance that I want from a monitoring tool. It just gives you a whole bunch of monitored metrics. It doesn’t tell you what’s good or bad about each metric. It doesn’t prioritize them quite the way that the other three tools do.

All three of those other tools have roughly the same performance impact. They all gather roughly the same data. They give you the same level of warnings. It’s just whichever one you’d prefer over the other. I think they’re all interchangeable at this point, and every now and then, one leapfrogs ahead of the others, and the rest of them go catch up.

 

Now, let’s see. Got a couple of multi-paragraph questions. We’re not going to answer those. Next one, “Are you having a sale anytime soon for the training videos?” We have our two big annual sales.

One is on our company anniversary in May, and then our other one is on Black Friday. Black Friday in November is the first Friday after Thanksgiving. It’s a big, traditional U.S. shopping holiday. On that, we’ll have some awesome, crazy deals. In the past, we’ve done stuff like five one dollar seats that people could get for our training classes, a few one dollar everything bundles, and usually, we discount classes by 50%. One day only, so you got to keep your eyes posted on your email for that.

Let’s see. Next one, “I have a two node SQL Failover Cluster and I’m migrating my workloads over to VMware. What’s the best way to do a migration from a traditional, and I’m assuming you mean physical SQL Server Failover Cluster to VMware or Hyper-V?” I don’t like doing physical to virtual, so there’s this concept of P2V where you can just simply transform a physical box up into a VM. I don’t like that for clusters.

I do like it for standalone older boxes like when I have SQL Server 2000 or 2005, but I don’t like it for modern Failover Clusters. For modern Failover Clusters, what I would prefer is go build a new VM or even if you want clusters of VMs, and then log ship from your existing physical environment over to your new virtual environment. I say log shipping. You could also do database mirroring. It’s just as good, just as effective. It gives you all kinds of ways to migrate over very quickly.

 

Let’s see here. “What’s the fastest and least resource-intensive way of copying very large data?” Okay. Listen here. Never use the term ‘Large’.

Don’t use big data. Don’t use fast or slow. Those are all vague adjectives. Use numbers. One terabyte, ten terabytes, a hundred terabytes, four gigabytes.

Use real numbers because what looks very large to you is every database that you’re dealing with for the rest of your life. For the rest of your life, people are going to keep giving you larger and larger databases. This is the funny thing about being a DBA. You’re always going to be faced with intimidating systems that seem large to you, and if you look around in the room, they will seem small to other people in the room, so you want to use very specific numbers in there. Now, granted, sometimes in your career after you drop a few databases, piss a few people off, maybe you’ll have to temporarily regress to a smaller database, but otherwise for the rest of your life, you’re always going to be dealing with the biggest, fastest databases you ever seen.

 

“What’s the fastest and least resource-intensive way of copying a lot of data with an identity field from one SQL Server database to another? Should I do bulk copies? Should I do bulk inserts?” The trick with this usually is how much downtime are you allowed to have. Normally, when I’m having to move data from SQL Server to another for some kind of migration project, they want as little downtime as possible, in which case, I want to build a change detection mechanism so that I’m syncing data in the background. Sometimes that involves adding a column with a date stamp on it as of the last time this row was changed so I can keep doing selects out, copying the data out, and only get the data that’s changed.

I prefer that much more than a big bang, like trying to shove everything across the wire as quickly as possible because often, even when I do it as quickly as possible, the stuff like BCP, the end users are still not okay with whatever the outage is.

 

Someone says “This tempdb is awesome”. I’m sure you mean the question, and not the answer. “I have a follow up. If you’re dealing with a Failover Cluster, can you configure your tempdb on local cheap Solid State drives?”

Yes. That has been supported since SQL Server 2012. It was even doable back in earlier versions. It just wasn’t officially supported, but it’s been supported since 2012. It’s even part of the Installation Wizard.

As you go through in step through the installation and you point tempdb over at the local drive, it just gives you a little warning in the Installation Wizard saying, “Hey, buddy. Make sure that you have the same thing over on the other node”.

 

Fellow human says, “Can you take a backup when the database is in read-only mode?” Yes, but you can’t take one if I remember right, when it’s in restoring, so just as long as it’s in read-only. Someone says, “I love Redgate”.

Congratulations. I love my wife. We all have our loves. Next up, “We use mount points for storing our SQL data on our instances. Is this a better solution than VMDKs for storing our data?”

It sounds like you’re using virtualization. Mount points, that typically means that you’re dealing with storage that’s attached, and I’m going to be a little vague here, over a network method, meaning like maybe you’re using I-scuzzy. Maybe you’re using Fibre Channel. These options can be better in terms of performance. They can also be worse. They can also prohibit you from doing things like Failover Clusters in some cases.

It depends on your version of VMware. Same thing with VMDKs, so it’s a little bigger of a scope. What I’d take a step back and ask, “What’s the primary problem that you need to solve? Are you facing a specific wait type in SQL Server? Are you trying to make your backups go faster? Are you settled with a specific SAN infrastructure?”

Faith, look at the problem that you’re trying to solve because the answers are always different based on the problem that you’re solving. It’s like saying “Is Chevy better or Ford?” It just depends on what the individual model that you’re looking at. Next question, “Thanks for moving the First Responder Kit to GitHub”. You’re welcome.

“I see the scripts on there are MIT used licenses. Does that include the documents and documentation in the First Responder Kit?” No. The MIT license is only for the stuff that’s in GitHub, so that’s where that piece comes from. No. You can’t take our setup checklist and edit it and put your name on it.

 

Next question, “I’ve installed SQL Server 2016 Development Edition on my laptop, and it freezes every time. Somebody told me that it’s because I also have 2008 R2 and 2014 installed. Is that true? I have Windows 10.” In SQL Server Management Studio, when it first came out and even until, I don’t know, say over the last few months, we’ve had a lot of reports of it freezing.

I’m the kind of guy who I really believe in VMs. That’s the same reason I use a Mac and I have since Windows Vista came out, so I don’t know. Like 2006. Everything I do is in VMs anyway because I don’t want to hose up my base OS. Whatever my base OS is, it has to work.

I have to be able to get my emails and surf my cat pictures, so I just always recommend putting SQL Server in VMs, and as long as I’m doing that, I crank up a different version for our different VM for every SQL Server version just so that that way, I know that I don’t have any incompatibilities between versions because there are some patching problems like if you accidentally install a newer version, and then you want to go install the shared components from older versions.

 

Next question, “Hey, guys. I run …” Guys? There’s only one of me this week too.

“I run SQL Server in a VM environment with a NetApp back-end. We were on SnapMirror and FlexClone. I’m looking to layer Veeam on top of that for backup. What are your thoughts on that in terms of an approach?” Generally, when it comes to backups, I want the simplest, easiest thing possible because when I have to go troubleshooting that, I want to spend as little time as possible in order to get it out the door.

What I would ask is, “What’s the pain that you’re trying to solve? Why are you injecting so many third party pieces into there?” I love each of those pieces individually to solve specific problems, but if you’re just doing it for fun, maybe ease up and not add so many pieces in.

 

Next question, “I know that shrinking is bad, however, my storage team is cheap on space. Is there another option to reclaim unused space?”

The thing with the storage teams is they can’t shrink your Windows volume. Your Windows volume isn’t going to get smaller, so even if you shrink a database, it’s not like that’s really going to give it back to the storage team. Whatever your volume size is, that’s what it is. What I talk to the storage team about is “If we’re looking to shrink my volume, that means I go build new volumes and maybe log ship from one place to another database mirroring, all that. Exactly how much space are we talking about reclaiming, because usually, the manpower required in order to save 50 gigs of the space just isn’t worth it?”

Our recommendation on how to talk to a manager who wants to move 65 SQL Servers to use MySQL when the licensing is due, I don’t. I’m a huge fan of the TV show ‘Columbo’. This was a detective show with Peter Falk, and Columbo like to play really stupid. He would look like a disheveled mess. He had a wrinkled raincoat, and his hair look like a big mop, just all kinds of weird stuff going on, and when people would talk to him, they would think he was really stupid.

Then later in the show, in every 30-minute show, they would discover that the guy is actually a genius, but he would always, “Oh, I don’t know anything about that. Gosh. I don’t know. I’m just not that smart. I’m a regular old detective. My life makes all the decisions”.

When my manager comes to me and says, “We’d like to move 65 SQL Servers over into MySQL”, “Wow. That sounds really interesting. I don’t know anything about that, but I’d love to watch. If there’s anything you want me to take a look at, that’s totally cool”, so wham. I mean, I just step aside from that whole thing, and that’s the end of it.

If they ask me “Do I know anything about it?”, “No. I never worked with MySQL. If you want to send me to school, I’m totally down with that. Never seen that Linux thing. It’s kind of scary.”

Now, over in real life, what do I do with these things like Amazon’s DynamoDB? I use Amazon RDS. There’s all kinds of alternative platforms that I do find interesting, but when it comes to the company wanting to do something, if you want to do it, great. Go do it. Manual is over there.

 

Oh, man. Tough question. “What are all the security issues with cross-database chaining?” You hit one of those issues that it’s one of those where I have to go hit Books Online. Every single time, I want to go read this, but I’ll give you a book recommendation instead.

The book recommendation I want to give you is Denny Cherry’s ‘Securing SQL Server’. If you go to ‘Amazon.com’ or your favorite book seller, search for ‘Securing SQL Server’. I want to say it’s in the third edition now, and he goes into a minute detail on that. The thing I always say about this book is it’s easy when you hear “Oh my gosh. There’s a whole book on security. That must be incredibly boring”.

It’s not. It’s actually a good read. It’s enjoyable to read. Denny brings a security topic to life. Terry says, “Brent, you look lonely there all by yourself”.

The sad thing is is even when the webcast stops, I’ll be sitting here talking to myself. Me and the dog. This is just pretty much what my life is which is also why I do training videos. I have no problem staring into a camera and talking continuously. Another person says, “That’s the same when you talk about virtualization and not putting anything in the HOST layer”.

He says “That’s the same thing Allan Hirt does. Nothing goes on to his host OS. Now, my host OS is basically my web browser, slack, any kind of communication type queries or communication type applications, and that’s about it.” Columbo also says always … He always says “One more thing”.

Right at the end of the court trial, someone remembers, and I love … This is the show, so it has plus so many good [in a way]. Right at the end of the show, Columbo is, “Oh, and one more thing. Can you explain how …”, and then he solves the entire case and gets the person to admit that they were guilty of the murder.

 

Our next question, “What technology do you use for your personal virtual machines?”

Because I use a Mac and because I used to manage data centers or virtual machines, because I always did that with VMware out in the data center, I use VMware Fusion. It’s not that it’s any better than Parallels. I hear similar complaints about both VMware and Parallels. I just use VMware Fusion because I’m used to it. Then, I just think about VMware Fusion is it’s fast.

It has this thing called ‘Unity mode’ where I can make the background disappear and I can just Alt, Tab back and forth between management studio and between my native browser app type stuff and it just all seems like it’s running on one OS. It’s really slick. It’s also just in terms of a technical behind the scenes type thing. I have one of those Apple trash can Macs. Get this camera down on there. The little Mac that looks like a trash can. 64 Gigs of RAM.

I mean, you can get stunning amount of stuff in a desktop these days, and really inexpensive. Not bad. The Apple Mac is expensive, but the RAM is not so bad. Next one, “I explained to our team that we could stop worrying about syncing SIDs across environments if we just created the log ins and specify the same SID. They thought this would be difficult and would prefer to use change users’ log in. I said if it’s needed, database is for an availability groups. Who’s right?”

Generally, I like syncing log ins because I can’t predict who’s going to go make the log in. People will go do it when I’m not paying attention like there’s a third party app that will go create its own log ins. Installer programs, all kinds of stuff like that will go create their applications, so I just sync across the board. I don’t use this to change users’ log ins. I use Robert Davis’ script.

Robert Davis wrote the book on database mirroring. It literally is a book on database mirroring. If you search for Robert Davis sync log ins, he’s got a stored procedure that goes through and syncs log ins across servers, uses linked servers. Works great for database mirroring, log shipping, replication, and always on availability groups, et cetera. Nothing wrong with either of your answers.

I mean, I’m a fan of yours if we specified the SID if I can always do that. I just can’t always do that.

 

Next question, “My backup Windows are long because we are using a native SQL Server tools to do backups, and then we’re offloading these to tape with Backup Exec. Could I remove the native SQL Server backup and let Veeam and Backup Exec do the backups directly? What’s the best solution in your experience?”

The problem with some third party tools is that it can be sometimes the scheduling is taken out of the DBA team, and sometimes when that happens, the scheduling is done at times you wouldn’t expect. NetBackup or Backup Exec are great examples of that where sometimes when a backup team takes over, the backups of SQL Server are done in a queued fashion dependent on other server backups. I’ve gone into shops and they’ve said, “Oh my God. Our full backups are running at nine AM or some other horribly inconvenient time”, because they let the storage team or the backup team take over those backups. Generally, I see things as being the most successful when the DBAs manage that themselves and when they do the backups directly to disk, not to tape, because during an emergency, I need to be able to restore those as quickly as possible.

If you go grab our First Responder Kit, if you go to ‘Brentozar.com’ and fill in your email address down at the bottom, one of the things in our First Responder Kit is my HA and DR checklist that lets the business choose how much downtime and data loss they want. It also includes fees like pricing so that they understand how much it cost to get near a zero downtime. If the business tells me I have to be able to recover from an “Oops, delete” or from corruption within an hour, typically, I have to have those databases on disk, not heading off to a tape, jukebox, or a virtual tape library just because it takes too long. Next question, “What do you mean by Books Online? Can you give the URL?”

If you search ‘Stop laughing other attendees’, you had this question too when you were young. If you search for ‘SQL Server Books Online’, that’s where the documentation for SQL Server is, and on Microsoft, they actually publish all of the manuals online so search for ‘SQL Server Books Online’, and a whole world of exciting new reading awaits you. I used to make all kinds of fun of Books Online. It’s gotten so much better over the last five, six years, and I’m sure it had nothing to do with me making fun of them either. I mean, Microsoft has invested a lot in the documentation, and I really find Books Online pretty impressive these days for a lot of subjects.

 

Next one, “Do we get a free Brent Ozar mug for completing all of the virtual training classes that you offer? I’m dying for one, but my company won’t hire you.” It’s funny. The thing stopping us from sending out coffee mugs is taxes. Taxes and shipping is a giant pain in the rear.

If we started saying our virtual training classes come with a coffee mug, then there becomes this whole nightmare of tax logistics and customs for people who are overseas, so we’re just like “Screw it”. We don’t bother with it, which is also why we just give them away to clients.

 

Larry asks, “You mentioned a go-to book for replication several Office Hours episodes ago. What was it?” It’s out of date, but if you go to Amazon and search for ‘Replication’, there’s really only two.

One of them is really old by a guy named Hilary Cotter. The other one I want to say was written by Kendal Van Dyke, but if you go to Amazon and search for replication, it’s really easy to see or see well specifically SQL Server replication because there’s only two books, one of which is vaguely recent. Terry says “You should put a weather map up on your green screen background”. It’s actually really funny that I do that for recorded videos, but I do it in post-production. Not weather maps, but other stuff obviously.

Doing it live burns so much CPU on the computer. It causes just a video to slow down and all that, but I stopped doing that. I try every now and than because there’s third party tools available. They will do it faster and faster, so once they get to a good enough software where they can do it fast, I’ve always wanted to say “Coming to you live from inside your database”. Let’s see here.

 

Next up, someone says “Can I buy a mug now because the shipping is just such a pain in the rear?” We thought about doing it through Cafepress or Zazzle. There’s a few companies which is actually where we get the mugs from, but sometimes the quality issues are a little wonky like they print the logo a little sideways or too high up or down, and then we have to go wrestle with them with that.

 

Someone says, “TechNet is also Books Online. For the unfamiliar, that made me feel silly when I finally put two and two together on that one.”

It’s actually not, so there are a little articles over on TechNet that are not considered part of Books Online. Books Online really is just a product documentation and there’s additional extra stuff over on TechNet. Sometimes I’m a little less happy with TechNet there. Fellow human says, “Redgate also has a replication book”. That’s a great point.

If you go to ‘Redgate’ or if you search on Google or Bing or whatever, search for ‘Redgate community books’ or ‘Redgate books’. Redgate is one of the software vendors that I talked about earlier. They have a book catalog. One of the things that can be a little deceiving with their book catalog, it has tabs on it. You got to click on the DBA tab at least the last time I was over there. When you click on that, most of their books are available completely free.

You do have to give your email address and name, and social security number and blood type. Totally not true. You don’t have to give your blood type, but then, you get the PDF immediately so you can go start reading it, and they have a really good editorial team so I’m sure the book on … I haven’t read that book on replication, but I’m sure it’s good. All right.

 

Then, the last question we’ll take is “Will you post a schematic for 3D printing the mug?”

Sorry, I shouldn’t be adding to this mug distraction. Although, a thing that I would always want to do with 3D printers is I would love to have a bubble head instructions that we could print up so that people could have our bubble heads on their desk. Yes, we actually had bubble heads at one point, so that would be a little fun thing that I’d love to do. All right. Thanks, everybody for hanging out with me during this Office Hours.

We will see you next week. We’re actually coming to you from PASS. We’re going to see how the bandwidth does on that. Now, the whole team is going to be in Seattle for the PASS summit, and so if any if you guys will be there, we will see you there. Adios.