Blog

This week, Brent and Richie discuss their hardware setups, lazy spools, SQL 2016 Service Pack 1, Always On Availability Groups, Database IDs, GroupBy.org, SQL on Linux, and new features they would like to see in the new version of SQL.

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-23

What do you guys think of my blog?

Brent Ozar: All right, let’s see what we’ve got for questions here. Nate says, “I don’t know if you guys are open to this or not–” No, we’re not, really. Next question. Just kidding. “I’d love to have you check out my blog.” He says, “I’m a new blogger and I write about DBA stuff since that’s my current job and passion. I’d love to have some feedback from the pros.” I assume he’s talking about Richie. URL is natethedba.wordpress.com. Nate, the one piece of advice I’d give you is go get your own URL. It’s only like $10 a year. You can get them from GoDaddy, from AWS, Namecheap. There’s a bunch of providers that do it. WordPress will charge you—I want to say it’s $30 a year to be able to use your own vanity URL but the thing is that way it’s totally you and it looks way more professional. You’re going to be telling it to people for years so you might as well just get the URL that you want there.

Richie Rump: Yeah, don’t do Jorriss.com because you’re going to have to tell people that there are two Rs, two Ss, and they’re like, “What’s Jorriss?” In the 90s, it made sense, not so much nowadays.

What should I do about lazy spools?

Brent Ozar: Fellow human says, “In one of the previous Office Hours webcasts, Erik mentioned that lazy spools are often an indication of bad performance. Can you elaborate on why and how to avoid them?” That hasn’t been my experience generally that I see a one-to-one when there is a lazy spool I go after that. I think what Erik was saying, and I think I agree with because of course, he’s not here so I can speak for him, is that when there’s a plan and I see a lazy spool, it’s a crappy query that I need to do something about or a crappy plan, not enough indexes, whatever. I don’t focus on the spool, I take a step back and just start looking at, all right, what’s this query doing? How am I assembling data together and what can I do in a better way? Often it involves things like index tuning or query tuning, but I don’t focus on the spool itself.

Is 403 page reads per second a problem?

Brent Ozar: Next up, someone says, “My Buffer Manager page reads per second is 403. However, my page life expectancy is 22,089. What’s the problem?” First of all, I don’t look at either of those numbers but I’ll tell you what’s happening. What’s happening is stuff is staying in memory for a long period of time. However, you’re having to read 400 pages from disk. 400 pages times 8K is nothing. It’s not that big of a deal. I could read 400 8K pages in one second. I’ve taken speed reading classes.

Richie Rump: Yeah, no.

Brent Ozar: I wouldn’t comprehend any of it. Richie has seen me read documentation, even when I read only one page a minute, I’m like, “What’s this mean again, Richie?”

Richie Rump: Even his emails. Just, no.

Brent Ozar: It’s about the rate of emails that I get. What I would say is, you don’t necessarily have a memory pressure problem. SQL Server is just reading some stuff from disk. What you want to look at is wait time instead. If you search for wait time on brentozar.com you’ll get a whole bunch of blog posts about how to do that. My personal favorite way to do that is sp_BlitzFirst. Sp_BlitzFirst will tell you what your performance problems are right now.

We changed a bit to an int. Why did .NET break?

Brent Ozar: Next up, Nate says, “Pretty please, I promise I have a real question to ask too, maybe even blog about. We had a view which had a BIT casted to an int column. When we changed it back to a BIT, it broke the .NET app with an invalid cast exception. It’s using Entity Framework. Any ideas why that would happen? I thought the app code would handle implicit gracefully but I guess not.” What on earth are you doing that for?

Richie Rump: Well, you do have to change your code. If you have an Entity Framework entity, because of what it is, and it’s mapped to a table or a view, it’s not implicit. You’re actually declaring it in the background as an INT or string or whatever. So you’ll have to go into the code and then tell it, “Hey, by the way, this is an INT or a BIT.” So that’s probably where you’re broken. You’ll just have to change that mapping and those should be fixed.

Brent Ozar: I also have this horrible vision that somewhere data is coming back as like a 9 or a 14 instead of a 5^th just with somebody’s crazy cast.

Should I switch from a Surface Pro 3 to a Mac?

Brent Ozar: “I’m thinking of a Mac since the battery-gate with my Surface Pro 3, even though they finally fixed it. How has your battery been on the Surface Pro 4?” You have a 4, right?

Richie Rump: It’s been fine but what I’ve been telling people about my Surface 4 is I like it. I really love it, but this is my third machine. Like I actually have three machines that I actively use. I’ve got one I’m using right now. I’ve got this baby that I use at night and then the Surface Pro 3 is essentially a large tablet for me. Everything has been fine on it though. I had one problem and it was a hardware problem. I’ve had it since day one. I went right into Microsoft’s store, they exchanged it right out and I was back up and running literally within the next hour or so. But the way I use my Surface Pro is not the way I would normally use a machine.

Brent Ozar: You don’t run like three instances of Visual Studio on there.

Richie Rump: No. No, no, not at all. I mean, it’s all there. But I don’t actively write code on it or anything like that.

Brent Ozar: So you’re the same as me. That’s what you’re saying. It’s installed.

Richie Rump: In ten years when I’m not actually doing anything with technology, I will be you, yes, exactly.

Brent Ozar: Yeah, might take longer than that. Yeah, sadly. To take your skills to atrophy, to this level, it’s, you know, measured in decades.

Richie Rump: You forget, developer skills, they go quick. Right? A lot quicker than DBA skills.

Brent Ozar: Yeah, wow, you guys have to learn new languages every six weeks.

Richie Rump: Haven’t we had this conversation before?

Brent Ozar: Yes.

Richie Rump: I watched a Pluralsight course yesterday, Brent.

Brent Ozar: To give people in the background an idea, we develop stuff like, so you take Paste the Plan for example, which is developed in AWS using Lambda which is function as a server, aka server-less code. It’s not really server-less. Nick Craver has a heart attack every time we say that but there are real servers behind…

Richie Rump: It’s just bad marketing.

Brent Ozar: It is bad marketing. But new frameworks come out with that like every sixty days on how you go about accomplishing this stuff.

Richie Rump: Yeah, and not even that but even some of the new features that have been around a little bit. What I’ve been playing with this week is ECMAScript 6 which is the next version of JavaScript. So I’ve been messing with that in Lambda this week. It just makes things a lot cleaner. All the spaghetti code I had to write with Paste the Plan, now with using ES6 syntax will be a lot cleaner. I’m smiling a lot more this week than I did when I was writing Paste the Plan. So that’s good.

Brent Ozar: Golly, I would also say don’t change from Windows to Mac—don’t take that lightly. Changing either way sucks, jumping platforms. I’ve tried to switch back over to Windows repeatedly because Windows 10 is amazing. Windows 10 is freaking fantastic.

Should I use SQL Server 2016 SP1 in production?

Brent Ozar: Someone says, “Now that SQL Server 2016 Service Pack 1 is out there, would you feel comfortable using that in production? Of course after testing in a test environment first.” I’m actually working with a client today on 2016. That’s going to be one of my recommendations is to go to Service Pack 1. People get really excited about having Standard Edition to have the same capabilities for developers that Enterprise Edition has. Forget that. There’s really cool DMV and execution plan changes that are awesome. In this case, it’s exactly going to help the client get better execution plan insight when they’re going and doing query performance tuning. So if you’re on 2016, I would totally go to SP1, just make sure you test it first. If you’re trying to consider a new deployment today, I would totally vote for 2016 and with Service Pack 1. It’s just great.

Richie Rump: Do you want to mention Erik’s post on 2016 SP1?

Brent Ozar: Yeah. Erik has an opinion, and I agree with entirely, Erik said that when Microsoft put the Enterprise Edition—and for those of you who are listening, I have air quotes around my head—Enterprise Edition features in 2016 Standard Edition in Service Pack 1, they really kind of took the leftovers. They didn’t take the best features. They just took these fringe features that are well tested and everybody kind of knows how they work but nobody really wants to use them. Great examples of that are Always Encrypted, partition tables, you hardly ever see that stuff out there and, yes, it’s going to make ISVs’ lives a little easier, but it really doesn’t help DBAs at all. DBAs, all the features that we wanted, online index rebuilds in Standard Edition, creating indexes online, merry-go-round reads, there’s a lot of things that I think we should have had in Standard Edition if we’re going to get features and we didn’t get those.

Richie Rump: Yeah. As a developer, I should be really jazzed about all the cool stuff they threw in Standard but without raising the memory limits, it’s meaningless.

Brent Ozar: Yeah.

Richie Rump: It’s almost like, “Hey, look all the stuff that you can get and you can do.” And I’d be like, “Yay, I get to play.” Then all of a sudden it’s like, but you only can stay on Standard and like why is it so slow? I don’t understand.

Brent Ozar: Yeah, if you need partitioning, the odds that you’re going to be able to still perform well on 128 gigs of RAM are not good. Usually, stuff like partitioning is designed for much larger datasets.

Richie Rump: Yeah.

Brent Ozar: I’m actually—like when I saw it drop I’m like, okay, I have a vision two years from now the amount of partitioning work we’re going to be doing is skyrocketing because everybody is going to go throw partitioning in as soon as they can and then they’re going to find out, “Oh, it actually makes queries slower instead of faster.”

Richie Rump: Yep, and, “Why I can’t do partition swapping because I’ve put an index across the entire table,” and, yeah. Yeah, that’s going to be fun.

Brent Ozar: Yeah, not a performance fix.

Brent Ozar: Someone says, “So is 2016 better than 2014 and worth going to?” Absolutely, for DMV improvements that are much better. Query Store is my personal favorite. I believe that feature alone is worth going to 2016 for. Thanks, Conor Cunningham, I love you, even though you don’t love me right now because I wrote a blog post that made you angry.

Why do I have user problems when I fail over an AG?

Brent Ozar: One person, let’s see here, says, “Doing some testing with Always On Availability Groups and I’m having trouble with SQL users in the replicated database losing their mapped link after a failover. Is there an easier way to reestablish this link after failover? It must be a common issue.” It is and in this case Always On Availability Groups has a lot of similarities to database mirroring. Database mirroring exhibits the same problem. The guy who wrote the book on database mirroring, his name is Robert Davis, aka SQLSoldier, if you search for SQLSoldier sync logins—not as in sank my battleship—but as in synchronize logins, he has a script out there that will help you go about doing that.

How do I get my RPO and RTO in writing?

Brent Ozar: “Any suggestions for getting RPO and RTO for high availability disaster recovery from an organization?” Yeah, absolutely, go to our site. Go to brentozar.com. Put in your email address or click on “free stuff” up at the top of the blog. We have a worksheet, an HA/DR worksheet for this exact purpose. What this does is it lets you fill in the current state first. You fill in how much data you’re going to lose when the server goes down, how long you’re going to be down for based off of things like your backups and whatnot. Then hand that to the business and say, “Here’s current state. If you want it to be nicer, you pick what you want.” The second page of that worksheet has prices to help you make that a lot easier with the business.

What causes database timeouts?

Brent Ozar: J.H. says, “What are some factors on the database side of things that cause timeouts?” So there’s really two parts to a timeout question. One is timing out before you connect to SQL Server. The other part is timing out waiting for your results. For timing out connecting to SQL Server you really have to watch on the application side of things because often SQL Server never even hears the call. For timing out on query executions, SQL Server doesn’t have a timeout. It will sit there and run your crappy query for years or decades. So then that’s also an application setting on how long you want to wait for your queries. So on both cases of those I start working with my developers and saying, “All right. If you’re having problems connecting, then let’s go set up ping monitors, any kind of monitoring utility from your application server over to the SQL Server so we can start to see when you’re not even able to ping the SQL Server. Then we can work with the network guys to figure out how to fix that.”

What should I do with statistics IO output?

Brent Ozar: Oh, there’s someone who asks an interesting question. “Set statistics IO on gives you great information where tuning in terms of reads.” I know where you can go with that information. Richie, tell them where they can go with their set statistics IO output.

Richie Rump: Thanks, Don Pardo, you could go to statisticsparser.com.

Richie Rump: Dang, I was almost there.

Brent Ozar: So close. The Don Pardo thing was what did it. I was like, ah, that’s good. He says, “Am I correct in assuming that each read is 8K so when I’m talking to my developers I can tell them how much data their current query is reading and start asking does it make sense that this thing is reading say 10 gigs worth of data when they’re writing their queries?”

Richie Rump: Yes.

Brent Ozar: Those are great, good—and it’s always a consistent number. It doesn’t change.

Richie Rump: Yeah. And if you go to statisticsparser.com it will not only parse it out for you so you can actually read it and know that that’s millions and not just kind of having to do the, “Oh, here’s a comma, here’s a comma, here’s a comma,” but also aggregate it for you as well. So it will give you the total number of page reads over a query.
Brent Ozar: Super helpful.

Can database IDs be different on AG replicas?

Brent Ozar: Someone says, “How do I resolve the issue where a database…” blah, blah, blah, “I have a database with one database ID say 6 on the primary and 8 on the secondary in Always On Availability Groups.” That’s totally okay. It doesn’t affect anything at all. Database IDs can be different on every server.

Thanks for helping me with Twitter.

Brent Ozar: Someone says, “Brent, you are awesome because two years ago I was new to Twitter and posted a question not using the #SQLHelp, instead I just named certain people. I was mocked by others while you helped me understand how to use #SQLHelp by reading your blogs.” I’m glad I could help. Twitter is not intuitive. Twitter is the opposite of intuitive. It’s like those old IRC—Richie, were you ever on IRC?

Richie Rump: Uh, of course, Brent.

Brent Ozar: Yeah, I thought so, and BBSs and all that.

Richie Rump: Oh, BBSs, oh. Let’s upload more stuff so I can get more download credits.

Brent Ozar: Or that deal—there was a ZMODEM hack where you could cancel your download at the last second and it would not fill up your account points. It would do it right after you sent the confirmation. Yeah, no, I’m all for that. Everything about databases is cryptic, online is cryptic, so whenever I can help people. Just make sure you can pay it forward.

Psst – introducing Brent’s newest project

Brent Ozar: My current attempt to go pay it forward is GroupBy.org. If you go to GroupBy.org, all one word, no spaces or minus signs, at GroupBy.org we’re building a platform where people can teach each other. Where you can go submit a session. You can vote on which sessions you’d like to see. You can leave feedback to presenters so that they can help make their own abstract better. So it’s like a live conference submissions process. We’re just going to take the top-rated submissions from readers and then go let them present them online on January 13^th.

Richie Rump: It’s such a great idea.

Brent Ozar: Thank you.

Richie Rump: It’s such a great opportunity, man.

Brent Ozar: It’s going to be so much fun. Plus, it uses the exact same platform that you’re looking at here. Those of you who are online, those of you who are listening to the podcast, same exact deal. So you’ll see talking heads. Like I’ll be in there as a cohost for whoever is doing the presenting. So if something goes up in flames, we can still have a good time talking about it. I could post the questions that people ask verbally, just really helps presenters relax as they see the talking heads and not freak out.

Richie Rump: Yeah. I could totally see how me presenting with Brent Ozar’s face right there is not going to freak people out at all. No, no. I mean, it’s totally normal. It happens to me all the time.

Brent Ozar: I could wear a mask. Yeah, see, now that you say that, I’ve kind of always thought of myself as being like—no, but yeah, no, I think you have a point there. I could be actually worse. And it won’t be me every time, too. We’re going to rotate in and out hosting duties. So we’ll do just like go contact people in the community who like doing that kind of co-hosting duty and different, other people can do it. Because no one wants to see me every webcast. It needs to be moving around to different people.

Richie Rump: Dude, you’ll totally get some troll in here, you know, like myself. “Wroooong! That is incorrect!”

Brent Ozar: Or just hold up little signs like, “This slide I’m voting a 2.”

Richie Rump: Yes.

Brent Ozar: Someone says, “When will the registration option be available at Group By?” (Update: it’s available now.)

Are you seeing much interest in SQL Server on Linux?

Brent Ozar: Next one. “Are you guys seeing much interest in SQL on Linux? Allan Hirt told me he was in August at his class in Chicago.” Nope. Nope.

Richie Rump: I’m surprised someone actually has interest in SQL on Linux. Please, tell me more.

Brent Ozar: Yeah, I don’t get it. I get that there are people who are Linux-only shops and they want an easier way to run databases. I find it kind of suspicious that people who have standardized on a free, open-source operating system are going to spend $2,000 a core on Standard Edition and $7,000 a core on Enterprise Edition.

Richie Rump: Yes.

Brent Ozar: There are some pretty good open-source databases out there. I’m not saying SQL Server sucks. It’s amazing. I love SQL Server. It’s fantastic. But if you’re really drinking the Linux kool-aid, which is a delicious kool-aid, and I endorse it highly, great stuff, I don’t see a lot of people going, “I sure can’t wait to give Microsoft my database money.” I just don’t see that.

Richie Rump: Yeah, let me just throw out this Postgres thing that does some things really, really well and let’s get into this SQL Server thing and it’s not really running on Linux but on this kind of faux hybrid OS-y thing that runs on top of Linux. Let’s go with that.

Brent Ozar: Yeah, it’s a virtualization layer basically. Drawbridge, which is now known as SQLPAL 2, SQL Platform Abstraction Layer 2. Anthony Nocentino has a great blog post about it that will be in next week’s weekly links, how he reverse engineered what Microsoft is doing in order to run SQL Server on Linux. Early testing, for example, Klaus Aschenbrenner has done some early testing with Hekaton and found that it’s almost twice as slow. Now, I have no reason to believe that Microsoft won’t fix that stuff as we get closer to production but I don’t see a lot of people going, “I’d like to use it on Linux and have it be slower, please.” I just don’t see that.

Richie Rump: Yeah, let me use an in-memory database that’s slower and it costs me more.

Brent Ozar: Yeah, yeah. Someone else says, “I realize it’s not a replacement for Windows, it’s more for people running a lot of Linux concurrently.” Linux isn’t starving for database choices. What Microsoft will tell you is that people who are using Oracle are looking for a way out. I would just say, as an independent database consultant, if you’re going to go rewrite your app, because essentially if you’re using PL/SQL, you’re going to be doing an app rewrite to move from one database to another. If you’re going to rewrite your app, why not go with open source? Why not go with something that has no licensing costs to it? Sure, you’re going to still pay support but Postgres has got some pretty good looking legs on it right now.

Richie Rump: [Whistles] I mean at that point if you’re thinking about changing your platform, why not go to the cloud? I mean, there’s a lot of stuff out there that’s looking really, really good right now and just because I get to play with it every day, I’m not just saying that, right? It’s true. There’s some good stuff out there right now.

Brent Ozar: Yeah, but AWS has Aurora, there’s all kinds of interesting scalable databases. I am a huge fan of Azure SQL db. I think if you’re going to write a .NET application that wants a relational database up there and you can build it from scratch inside one database, no cross-database queries if you can avoid it, Azure SQL db takes a lot of crappy work out of database administration, stuff that we just don’t like to do.

Brent Ozar: Follow up question, “Can you teach a fish to climb a mountain? Linux is a different animal. I don’t see that getting anywhere.” Well, so, because I happen to be an Alaska guy, I’m a real big fan of Alaska, I’ve seen salmon going upstream, so I do know that that can happen. And they taste fantastic.

What do sleeping queries mean?

Brent Ozar: Next question. “When queries are in a sleeping status, does that mean that the application is not closing out the connection to SQL?” Yep, exactly. If you look with one of my favorite diagnostic tools, sp_WhoIsActive, if you look with sp_WhoIsActive, that’s something that’s not included with SQL Server, it’s an additional diagnostic tool that Adam Machanic out of the Boston area wrote, it will not only show you if it’s sleeping, it will show you if it has open transactions too as well.

Why are partitioned table queries slower?

Brent Ozar: Next question. “Why are queries slower on a partition table? You just mentioned this.” If you go to brentozar.com/go/partitioning, we have links that explain it. Kendra Little did a few example blog posts using the Stack Overflow database. If you split a table up into a bunch of little, bitty child tables underneath, SQL Server has to recombine those results when it goes to join them to another table. So you can end up with even more expensive sorting. There have been a couple of clients where 80 to 85 percent of the cost of their select queries was simply reassembling the data from multiple partitions and then resorting it into a way that you could have had an index on a non-partitioned table. “Well, you can put indexes on a partition table.” Yes, but then you can’t do partition switching. You can’t drop a partition or load a partition at once.

Richie Rump: Yep. That’s why we always—I had a rule when I was working on that 60-terabyte [monster monitor] database that we always had to include the partition key. That way we didn’t get any sort of craziness, joining things back together and things like that.

Brent Ozar: You mean included in the select query, like in the where clause.

Richie Rump: That’s correct, yep.

Brent Ozar: As long as you can do partition elimination, as long as you can tell SQL Server, “Only look in these partitions” and it’s a minority of the partitions, you can get good performance improvements. And I’m not saying I don’t love partitioning, partitioning is awesome. You just have to find the right use case for it. Maintenance, making table maintenance faster is the great use case for partitioning. Making selects faster, not quite as much.

Richie Rump: But if you do the maintenance incorrectly, you could totally hose up a database. Let me tell you.

Brent Ozar: Oh, I need to have a blog post from the team on what were your worst database memories. Worst outages we’ve ever had.

We got an error…

Brent Ozar: Let’s see, we’ll take two more questions. One of them is, “We tried to install SP3 to SQL Server 2012 and got an error hexadecimal value, invalid character, blah blah blah. Tried redownloading the SP, same error.” I would just open a support call with Microsoft. Things like that they may actually even know about the problem and have a fix for it.

What feature do you wish SQL Server would add?

Brent Ozar: Last one we’ll take is, “What would be the number one feature you would want to see in the next version of SQL Server that some other database platform has but we don’t?” So I’ll let you think for a minute, Richie, because I have mine, and both me and Erik have blogged about this. I want non-logged tables. I want to be able to say that this specific table I don’t need to log it at all. Whatever data is in there I’m going to live with. If something hoses up mid-transaction whatever I have for records in there is totally fine for me. The great examples of that are sessions, state tables, reporting tables for like data marts that I could just clean out at the drop of a hat. You can’t just fix this by putting a database into simple mode—model. Tara will kill me if I don’t say “model.” Simple recovery model will still log all your inserts, updates, and deletes to disk. Whereas if like Postgres has non-logged tables, that would let me solve some problems with clients really quickly by just saying, “This table is garbage. Let’s turn off the logging.” Like if I had that over the last year how many real customer problems would I have solved? It’s probably on the small number of fingers on one hand, it’s just so cool, so I’m kind of jaded there. How about you, Richie?

Richie Rump: So I could go the, hey let’s get some of the windowing functions to spec fully because they’re mostly there but they could—but I’m not going to go there. There’s a feature that they just released for Aurora that I’ve been playing around with, probably finish playing around with this week, but you could load data from S3 into Aurora, so I’m going to say that, being able to load data from a JSON or XML file and just by one line on the command line and it just goes up and does it.

Brent Ozar: All right, so now I can think of another one. I would also love to have on Always On Availability Groups, I would love to have—and this is just pie in the sky, this is never going to happen. I would love to have one replica that doesn’t have indexes. I would love to have a replica that is just purely the clustered index, not any of the nonclustered indexes, or different nonclustered indexes somewhere else. So it would kind of give me the best of transactional replication plus Always On Availability Groups. It is never going to happen. I say that, I would love to have it happen, I just don’t see it happening. J.H. says, “How about table level restores?” All the third party backup software can already do that anyway so I’m kind of like, eh, I’m used to seeing that out of LiteSpeed, Redgate, and Idera. So if you want that, you can get that today for like $1,000 a server.

Richie Rump: There’s another comment, “Am I mistaken but isn’t no-logged table the same as in-memory tables? Or do those get logged?”

Brent Ozar: It’s not. With in-memory tables, first off, you have to accept all the restrictions of in-memory tables, which rule out a whole lot of capabilities of regular, plain old tables. Plus, if they’re durable, they are still written to disk. They’re just written to disk in a different format. You can say nondurable in memory tables but those are simply erased whenever you go restart that SQL Server. I do want the data there, I just don’t need it logged.

Richie Rump: Yeah, another great way to put session information inside of SQL Server because that’s always a great idea. Yeah, let’s do that.

Brent Ozar: And you know, so I actually came around. When they said that they’re going to allow Hekaton in Standard Edition I was like, okay. So finally, I will accept session state in Hekaton on Standard Edition. I’m not happy about it, not excited about, it still seems like flushing money down the toilet but if you had to flush money down the toilet, now you can just flush it down with Standard Edition money instead of Enterprise.

Richie Rump: Yeah, so if you want to flush any more money down the toilet, you can go to Richie Rump Palmetto Bay, Florida 33157. There you go.

Brent Ozar: There you go. And on that bombshell, we will end. Good night, everybody. Thanks for watching. We will see you next week. Adios.

This week Richie, Tara, and Erik discuss replication, mirroring, memory grants, sp_BlitzCache, failover cluster instances, VLFs, restoring databases, qualifying schema and object names, multiple instances, and table variables.

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-16

Erik Darling: “Should mirroring be removed where the mirrored database server will be offline for several days?” Yeah, because your transaction log isn’t going to be able to truncate for those several days. So if you take log backups, it’s not going to help because mirroring is going to be like, “Hey, I need to be able to send these transactions else-whistle before the transaction log can be cleared.” So if it’s going to be down for several days, yeah, I’d probably want to remove mirroring. Unless you can afford to have your log file balloon out to gigantic sizes.

Erik Darling: “Does this new version support online index rebuilds yet?” You know, it doesn’t appear to—at least Aaron Bertrand’s roundup said that it doesn’t. I don’t know if anyone else saw that it did somewhere else. I haven’t tried it yet and I have a puzzler of a time trying to use Standard Edition because all I have is Developer Edition which is Enterprise anyway.

Richie Rump: Come on, can’t you burn two hours of your valuable time to figure this out? Come on, man.

Erik Darling: I could, but then I wouldn’t be able to release cool things in sp_BlitzCache for people so that they can know when they’re using table variables and heaps and functions that ruin their performance.

Erik Darling: “Right after VSS snapshot taken of all server volumes, vendor then backups the databases on the SQL Server instance with a destination of something like file equals one [inaudible]… this breaks the log chain. Contacting vendor rep, but why is this happening?” I don’t know. Ask the vendor. That’s dumb that they do that, break the log chain. Couldn’t tell you. If I could ever see inside the mind of most vendors who work with SQL Server, I would probably just go mad.

Erik Darling: “Recommended MAXDOP on a four-core SQL Server to start. They have CPU count equals four and hyperthread ratio of one.” If you have a four-core server, a single socket four-core server, I would not change MAXDOP. I would not set it to anything. It’s not really going to do you much good. I would still want to change cost threshold for parallelism though. You might want to be a little extra aggressive with that and make sure that if something goes parallel it’s because it really needed it. You might even find 50 is a bit low because parallel queries, they use extra threads, they use extra resources. If you have a single socket four-core server, I’m willing to bet you also don’t have much RAM either. So you could be in for a load of performance hurt if too much stuff starts going parallel. You could start taking thread pool. You could start seeing all sorts of resource semaphore waits. So I would mind my beeswax when it comes to parallelism there. I wouldn’t totally disable it. I wouldn’t set MAXDOP to one. I would leave it at zero and just set cost threshold high enough so that things aren’t just wanting to be going parallel.

Erik Darling: “Blame Paul Randal since he kills cats for every shrunk database apparently.”

Richie Rump: It’s true.

Erik Darling: Yes. That is true. Slowly and savagely. It’s nasty really. I don’t know how he lives with himself.

Richie Rump: Just don’t shrink a database and you won’t have to worry about that anymore.

Erik Darling: Yeah, exactly. It’s like all the weird scenes out of The Cell where it’s just like intestines and weird…

Richie Rump: I’m not familiar with that and I will continue to be so.

Erik Darling: You never saw The Cell?

Richie Rump: No.

Erik Darling: It has JLo and Vince Vaughn in it. How have you not seen The Cell? She like plays a psychiatrist who goes inside a serial killer’s mind to help find some girl who he is like holding hostage.

Richie Rump: That’s probably why.

Erik Darling: Yeah, all right. Sorry you have no taste in movies.

Richie Rump: Did you just drop a 90s film reference? Like a really obscure 90s film reference?

Erik Darling: It’s not even obscure. There’s a sequel to it. It’s not obscure. It’s totally—it’s got Vincent D’onofrio in it.

Richie Rump: Yeah, like they made a sequel to Anaconda. Whatever.

Erik Darling: I don’t know. It’s true. Did they make a sequel to that shark movie with Samuel L. Jackson and LL Cool J?

Richie Rump: Deep Blue Sea.

Erik Darling: Yeah, was there a sequel to that?

Richie Rump: I would assume that SyFy probably did something like that.

Erik Darling: Yeah, probably. That’s what turned into Sharknado probably.

Erik Darling: Let’s see here. Someone is using Windows “21012 R2, SQL Server 2012,” also R2. There is no such thing as SQL Server 2012 R2. There is only 2012. “How much RAM for OS, how much for SQL? 32 gigs on the server.” I would probably give 8 gigs back to Windows that it can do things. That’s it.

Tara Kizer: Can you guys hear me?

Erik Darling: Yes.

Tara Kizer: Finally.

Richie Rump: You’re still a little faint but we can hear you.

Tara Kizer: All right. I’m as high as it can go. It doesn’t happen on WebEx.

Richie Rump: Hear that everyone? She’s as high as she can go.

Erik Darling: Rocket man. So Tara, there was a replication question.

Tara Kizer: Yay.

Erik Darling: Yeah, I know. It’s a good one, maybe. “Does the snapshot folder for replication need to be on its own drive?”

Tara Kizer: It does not but you may run into a problem with disk space. We had all of our stuff separated into mount points and the default location for the snapshot folder ends up being put with the system databases, maybe not tempdb, but the other stuff. You know, also where the error log is. In my environments, that mount point was always created small since everything there is usually small, so it caused replication to fail at one point when we were doing a snapshot. We just didn’t have enough disk space for it. So we ended up moving it to its own mount point so that it could not impact the system stuff also. So not required but it might be recommended, depending upon the size of your snapshots.

Erik Darling: All right then. Well, hope that answers your question, replication person. Your life is hard enough with replication in it.
Erik Darling: “I have someone hogging the cache and killing page life. I am aware of a couple DMVs that can point out possible offenders but could it be possible that the offending query doesn’t show up in these DMVs?” Geez, that’s a weird one. So if you could clarify a little bit. When you say that you have “someone hogging the cache and killing page life,” I assume you mean that their query is asking for a memory grant or asking for memory that is draining the buffer cache, the data cache, from SQL Server. If you’re on a decent version of SQL, like 2012, I think SP 2 or 3, or 2014 SP 2, or 2016, you have a lot of good information about memory grants, prior to that you don’t have a lot. What you could try doing is if you’re on a more modern version of SQL Server you can run BlitzCache and you can order by memory grant or average memory grant. You can see if any queries are asking for great big memory grants and if they have unused memory grants. I added a couple months back. Prior to that, not really a lot of good information on that. It would kind of be hunting and pecking to try to see how much memory you’ve got assigned to a query. Anyone else? Want to chime in on that?

Tara Kizer: No.

Erik Darling: No? All right. You’re fine.

Tara Kizer: You got it.

Erik Darling: “While you are on MAXDOP and WIN 20000 R2, 8 procs, cost threshold 50, MAXDOP 4. Why do I have nine ECIDs for one SPID?” Was anyone paying attention when I did the parallelism [inaudible] at the precon?

Richie Rump: No.

Erik Darling: No.

Tara Kizer: I was tired.

Erik Darling: Everyone was tired that day. So the reason you have 9 is because parallelism doesn’t just control cores, it also controls threads. So you can have up to four threads per parallel operator per core. So if you have two—let’s just call them clustered index scans—happening on two different processors and they’re both using four cores, they’re both using four threads on each core rather, you will see four threads a piece and then one thread coordinating. So that’s probably why you see nine. That’s what I have to say about that.

Erik Darling: So, “sp_BlitzCache?” That’s someone’s question. Have you no decency, sir? Have you no decency?

Richie Rump: So, sp_BlitzCache. Go!

Erik Darling: Oh my word. How do you end up in Office Hours and not know about BlitzCache? How does that happen?

Richie Rump: That is what we like to call in the business a softball.

Erik Darling: Man. All right. Well, I’m going to send that link to the First Responder Kit out and you can go and you can look at all the awesome and interesting things that we have in there. There’s Blitz, it’s BlitzIndex, it’s BlitzCache, and it’s BlitzFirst, and now it is also includes BlitzWho because we separated out some of the code from BlitzFirst into its own stored procedure. BlitzyMcBlitzFace is not in there, yet.

Richie Rump: Damn it.

Erik Darling: We’re working on it. We’re trying to figure out how to get it to just return weird ASCII faces but I’m just not good enough at using replicate to do that.

Richie Rump: And I’m not going to help you.

Erik Darling: No, no one is helping. I keep emailing Kendra and Jeramiah like, “Dude, you’ve got to help me with BlitzyMcBlitzFace” but they’re like, “No. We have lives now.” I’m like, okay, fine.

Erik Darling: All right. Tara, here’s one for you. You’re going to love this. “I have a two-node failover cluster, SQL Server 2014 Enterprise and Windows 2012 with a default instance. I want to add named instances to the same machines. Do I need a SQL cluster and separate drives for each new instance?”

Tara Kizer: You need to install a new failover cluster instance. So you install SQL Server and installation is going to recognize that you’re on a cluster and it’s going to install a new instance using the cluster—go through the wizard with the cluster stuff. Then you do need separate drives for each new instance. I’m not sure that you want to put more than one more instance on a two-node failover cluster though. That wouldn’t be recommended, really only one is recommended for a two-node failover cluster, but maybe two. I wouldn’t go for three though or any higher.

Erik Darling: “Is pull better than push for replication performance?”

Tara Kizer: I don’t know the answer to that. I’ve never used a pull subscription, always push. I think that most people who use transactional replication use push. That’s the default. I have no idea about performance though.

Erik Darling: All right. Good stuff. I don’t know the answer either, clearly. No replication going on here.

Erik Darling: We have a question about BlitzCache. “SQL Server 2014 Standard. When I run BlitzCache during the day I’m seeing that everything in there has been created within the last minute. Am I right to assume from that that we are constantly recompiling our code?” No. If you are constantly recompiling your code there would be nothing in there. If you’re constantly compiling your code, you’ll have a lot of single use plans in there. So where you’re asking about temp tables, that will cause a statement level recompilation, that won’t cause the whole thing to recompile. And that will only happen when the temp table changes enough to trigger a recompilation. So it’s typically like six uses or a lot of modifications within the temp table. There’s a great post by Paul White called “Temp Tables and Stored Procedures.” I’m going to grab that link for you in case you don’t already have it. I’m going to drop that into the Q and A section so you can read that. Since you’re on 2014, one thing you can do to help stave off recompilations based on temp tables is if you’re adding indexes to them you can add indexes as part of the inline syntax.
Erik Darling: Wow, another replication question. Tara, you ready?

Tara Kizer: I actually might have some information on that one.

Erik Darling: All right, all right. I hope I’m reading the right one then. “Distribution database. 8 million rows. Retention set to four days due to Informatica PowerExchange and specific requirement. Can we set allow anonymous and immediate sync in publication? Is it good practice?”

Tara Kizer: Unfortunately, I don’t have the answer to the actual question but I do have a comment on your retention of four days. I have worked at a job where we did use Informatica PowerExchange. I’m very familiar with this issue. It causes blocking in the distribution database. Informatica reads the distribution database directly rather than being an official subscriber in replication. We had to set our retention down to, I think it was 12 hours. So the Informatica team that we had said that—I think the minimum that they were saying was two days. Actually, I think they wanted four days but we had agreed to two days. We ended up having to go down to 12 hours due to how busy our system was, how much data was—you know, inserts, updates, and deletes on the publisher. So you do not have to keep yours to four days. We definitely did not use it. If you ever need to do a resync, you’re going to have to do a snapshot obviously, but how often does that happen? And with Informatica Power Exchange, they create the publication so if they’re not setting allow anonymous and immediate sync this is really a question for Informatica rather than for us. Contact them to see what they allow for the publication properties.

Erik Darling: Curse you, Informatica.

Tara Kizer: I did not like supporting that environment.

Erik Darling: I don’t blame you. From what I hear, that’s a tough one. Informatica is like difficult for like really smart people. I can only imagine how lost I would be if I had to deal with that.

Richie Rump: Yeah, I didn’t like Informatica at all. It was no fun.

Erik Darling: It’s just weird because it’s a widely touted ETL tool. People are like, “Yeah, Informatica. Get all your data…” blah, blah, blah. I’m like, okay. I will write my own damn BCP. But I’m dumb, so don’t always do what I do.

Erik Darling: Eric asks… Eric, you spell your name wrong, first of all. “Someone has been trashing tempdb on a dev server.” So my first question is, who cares? “How can I catch someone after the fact that has been killing tempdb?” Well, you can’t. Not really. You can check the plan cache and see if anything in there is weird but there’s not really a way to sort by tempdb usage after the fact. I don’t know guys, what do you think?

Tara Kizer: After the fact? I don’t really think so but if this is a recurring problem start logging the data. Start logging sp_WhoIsActive maybe every 30 seconds, maybe more frequently temporarily. At my last job, we had a 500 gigabyte tempdb database and I think our mount point had like 800 gigabytes. This was for business analytics type stuff. Crazy, crazy queries pulling massive amounts of data. We got an alert that our tempdb mount point was running out of space. I took a look and sure enough it had grown past our half terabyte, up to 800 gigs or whatever it was set to. All I had to do was going into sp_WhoIsActive and look at the past couple hours and you can see under—I forget what the call name is but it’s like tempdb allocations, I think. Immediately I could see a query that had a massive amount of tempdb allocations. It just jumped right out. I did an order by that column and it was really easy to see. So I would highly recommend that in any production environment that you do log sp_WhoIsActive every 30 seconds, every minute let’s say, keep maybe ten days of data. Then if you need to do this like in a dev environment, you know, just do this temporarily or just don’t log the data very frequently.

Erik Darling: Again, this doesn’t help you after the fact, but if you want to bust someone for doing it, I wrote a blog post a while back about tracking tempdb growth with extended events. So if you want to fire that on up, you can see if you catch anything interesting happening. I’m willing to bet that someone is just like doing something dumb like rebuilding indexes and sorting in tempdb. That’s always fun.

Erik Darling: There’s an Entity Framework question, Richie.

Richie Rump: Uh-oh.

Erik Darling: Let’s see, “I am seeing Entity Framework running ‘set showplan all’ on and then seeing ‘showplan permission denied’ in database in traces for user errors. Also seeing ‘must declare the scalar variable at 469’ errors in Entity Framework. Are these settings I can ask the devs to change or is someone…?”

Richie Rump: I have not seen anything that sets showplan on. I would assume that it’s somewhere in the code somewhere. That would be my first guess. You really shouldn’t need showplan, right? I mean unless we’re actually doing something in the application with our execution plans, then probably I would need it. But yeah, I’d dig into that just a little bit and take a look at the actual code. Not the code that’s going into SQL Server but the code that from Entity Framework and your data access layer. I’m assuming someone probably copied something somewhere and it’s explicitly putting that line into probably the Entity Framework pipeline to go to SQL Server. So that would be my gut check because I don’t know of anything in Entity Framework that does showplan.

Erik Darling: No, especially showplan all. I would be careful if someone is running like profiler or DTA on the server. They might be doing something kind of weird and trying to like gather stuff while it’s running. They might be profiling the code in some way.

Erik Darling: Let’s scroll on down. “What version of Windows 2016 Server should be used for SQL Server 2016 Enterprise?” Ugh.

Tara Kizer: “What ver–?” I don’t understand.

Erik Darling: I guess because you have like Standard and Enterprise and Core at all that stuff. I’m pretty agnostic on that one. I don’t really have a lot of opinion on that.

Tara Kizer: I’ve used—not for 2016—Standard and Enterprise and a lot of times we would use SQL Server Enterprise with Windows Standard. We just didn’t need the Enterprise features of Windows. But you need to compare the editions for Windows 2016 Server and see what you require for your business. One of the things is going to be how much memory do you need on that server.

Erik Darling: Good point on that one.

Erik Darling: VLFs. “On a scale of 1 to 5, with 5 being how much attention we should pay to it…” That is not how you scale a question. You should have a most and least in that. “What is your opinion, is there a way to measure its benefit?”

Tara Kizer: So VLFs are a problem for database recovery. When you restart SQL Server, it goes through crash recovery and if you have a lot of VLFs on your database, that database may not come up for a while. I’ve had a database that have 75,000 VLFs in production, it was a mission-critical database. I didn’t know about VLFs, this was probably about ten years ago at this point, but the database took 45 minutes to come online. After contacting Microsoft, I learned about VLFs, so we had to reduce them. Once you fix your autogrowth, you’re not going to encounter the VLF issue anymore. It’s a problem with restoring a database, and for most people, it’s going to be what happens to the database when you restart the SQL instance as it goes through crash recovery. I’ve seen some comments about it possibly affecting performance but I don’t know how to even check that. I’ve never been told that performance issues were due to high VLFs. But as a result of the 45-minute outage we had, I then set up monitoring for VLFs. Run it maybe once a week, a VLF script across all your servers and databases and then report back in for anything that’s showing high numbers. Once you fix it though, it should not be encountered again on that same database.

Erik Darling: I once had a database with a 500 gig log file that had 5,000 VLFs in it and it took 21 hours to recover.

Tara Kizer: Oh, wow.

Erik Darling: Thankfully this wasn’t like a production outage 21 hours. This was like me restoring it to offload DBCC CHECKDB. But as soon as my script went into the restore portion, it sat there for 21 hours. Just an amazing amount of time. So it’s totally something to pay attention to if you restore a database and you see that it takes a long time. I’d probably pay more attention to it then or if you’re like planning on doing some setting up for log shipping or mirroring it might be something to look into. But overall, I think Tara is right. Check in on it like once a week or once a month. Again, once you fix it the first time, it’s hard for it to get out of hand again.

Erik Darling: “SQL 2012–” but this really can be asked for any question. I don’t think this is just for SQL Server 2012. “For app developers, do you recommend always using fully qualified object names? Example, database, schema, object, most specifically schema.” Yes, yeah. I would qualify schema and object.

Tara Kizer: Schema and object, yeah, but not the database really.

Erik Darling: Yeah, not database.

Tara Kizer: Unless you need to cross over to another database. You’re going to get the database attached through whatever database you connect it to from your connection string but if you need to cross database, then that’s when you do three-part name.

Erik Darling: Yep.

Richie Rump: It depends how you are architect the database though right? I mean if you’re not using schemas then what does it really count?

Tara Kizer: Well, yeah. I like the best practice of having always using dbo. if you’re not using schemas.

Richie Rump: Yeah, I got into an argument at like a code camp or something with some Oracle guy that was telling me that I should always qualify with schema and all this other stuff. I’m like, “Look, what does it matter unless I’m actually using schemas? If I’m not using schemas, who really cares?”

Tara Kizer: Yeah.

Richie Rump: He huffed off in his Oracle, huffy, I’m-better-than-you way.

Erik Darling: Well, in Oracle everything is a schema, right?

Richie Rump: That’s right.

Erik Darling: You don’t have as much database separation as you do within SQL Server. SQL Server you have an instance and you create databases. In Oracle, you have a database and that’s your instance. If you want another database, you have to install another instance. So within a database, you may create lots of different schema to separate user workloads out but you still sort of end up with just this one monolithic pile of garbage. Not that I’m saying Oracle is garbage, it’s just tough to maintain all that.

Erik Darling: Obvious follow up on the cluster question. “Why aren’t multiple instances recommended?” For the same reason you don’t recommend multiple instances anywhere. It is a pain in the butt, the entire butt, to troubleshoot performance stuff when you have stacked instances. Anything else you guys want to add on that?

Tara Kizer: I don’t like to stack instances on a single server. So let’s say it’s a two-node cluster. If you have two instances and they were both running on the same node, that’s the same thing as stacked instances anyway. But now let’s take a look at a two-node cluster where we make it active-active. I know that that terminology isn’t used anymore—you know, isn’t the right terminology—but it is still used. So you’ve got an instance running on each node. I actually like that environment because we’re using the hardware. The caveat though—and Microsoft highly recommends that you do not do this—you run active-passive instead so only one instance on a two-node cluster. So the caveat is if you have an outage, you lose a node, both instances are now running on the same server, can that hardware support the load of both instances? I always tell people, well, so what if we have an outage. The companies that I’ve worked for, we had these expensive contracts with the vendors to replace hardware within like four hours. So it’s not like we’re going to be running on the same node for two days. We’re going to have it replaced and fixed in probably less than an hour. But let’s just say it’s a major hardware issue, it’s going to be within four hours or so. So, I don’t care about that. I like to use all my hardware that I can.

Erik Darling: Cool.

Erik Darling: I think the last question that we have time for, “Is there ever an instance where a table variable is better than a temp table?” Do you guys have anything?

Tara Kizer: As far as better, I mean the only time is if you want to persist the data. If a transaction gets rolled back, the data gets rolled back in the temp table but if you put it into a table variable, that data would still persist. It survives the rollback transaction, but I just use temp tables everywhere until I have a tool that won’t allow me to. For instance, SSIS, and I can’t remember the specific issue, but I had a stored procedure that was using a temp table and it would not—there was some kind of bug in the SSIS and I had to switch that specific code to use a table variable to get it to work and it killed me to do this because I hate them for performance reasons but it was the only way we could get the package to work. It’s a known bug apparently.

Erik Darling: So for me, there is one other sort of interesting side to table variables, that is that they will never cause a recompilation. So SQL doesn’t generate statistics for table variables so if you use them in a stored procedure, you will always, each and every single time, you’ll get the same heinous cardinality estimate but you will never trigger a recompilation. The tricky, sucky thing is that the only way to get an accurate cardinality estimate out of a table variable is to use a recompile in it. So it’s just like you have this feature, it’s like, it won’t cause recompile but if you want good performance, you have to recompile. So my use of table variables is pretty much limited to code and tasks where performance does not matter. A perfect example is Ola Hallengren scripts, he sticks everything into table variables and runs off those, it doesn’t use any temp tables just because performance doesn’t matter. He’s just iterating over one object at a time anyway. So that’s the kind of stuff where I do the table variable, maybe, but not even then guaranteed. That brings us to 12:45. Welcome back, Tara. It was lovely having you here.

Tara Kizer: Yay.

Erik Darling: Answering these replication and clustering questions.

Tara Kizer: There were a lot today.

Erik Darling: There were a lot last time too and I just didn’t read them.

Tara Kizer: Sorry.

Richie Rump: Yeah, he just walked right over them. Just like blah, blah, blah.

Erik Darling: Like nope, nope, nope, nope, not happening. All right. Cool. See you guys next week. Thanks for coming. Goodbye.

Tara Kizer: Bye.