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.
If you prefer to listen to the audio:
Enjoy the Podcast?
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 5th 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.
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 13th.
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.