[Video] Office Hours 2016/12/21 (With Transcriptions)

This week, Erik and Richie discuss latency, partition tables, SOS_SCHEDULER_YIELD, alerts, nolock, and what people look for in Senior DBA.

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-12-21

Should TempDB be on SSD?

Erik Darling: Let’s get this firing squad on the way. Our first question, “Should tempdb be on SSD?” Gosh, that depends on so many things.

Richie Rump: If you can, yes.

Erik Darling: There’s a good one. That’s a good answer. I would expand that a little bit and I would say if you’re on a VM, you’re kind of hosed and you’re not going to get that local storage—you don’t get the VM goodness if you start using local storage. So careful there. If you’re using tempdb heavily and you have local storage anyway, a bare minimal server, there’s really no reason for it not to be on SSD. Or if you really want to step things up, throw on a PCIe, flash memory or flash drives, and really let that thing zoom around. Anything else on that? I don’t think so.

Richie Rump: Nope. If you can, do it.

Erik Darling: Yep, cool.

 

What’s the #1 issue with latency?

Erik Darling: Next question down, “What is the number one issue with latency? Is it usually a sub-storage problem?” No. It’s usually a pre-storage problem. It’s usually you have a SAN, which is perfectly capable of giving you data and a crappy little pipe that is really incapable of serving that data to the server. SQL Server CPUs can consume a whole lot of megabytes per second, like hundreds of megabytes per second. Usually the problem is that you have like, you know, a 1 gig iSCSI or a 2 gig or a 4 gig fiber channel, or even like just a single 8 gig fiber channel. If you have multiple sockets and multiple cores, you’re not going to be able to serve data up fast enough to keep all your CPUs and stuff busy unless you have enough RAM to cache everything so that you don’t go and poop all over the storage every time you run a query.

Richie Rump: But, jumbo frames.

Erik Darling: [Laughter] Those will solve everything, large pages and jumbo frames.

Richie Rump: Everything. You NOLOCK with jumbo frames and…

Erik Darling: Large memory pages, what else is there?

Richie Rump: Done.

Erik Darling: You don’t even have to hit F5, the query just runs for you. Done.

Richie Rump: It just starts coming at you.

Erik Darling: Yeah, it’s crazy.

 

What’s the best type of column to partition on?

Erik Darling: Oh boy, partition tables, “What is the best type of column to parse on? Is it date?” No. It is the column—at least for me—it is the column that is most likely going to end up in your where clause so that you get good partition elimination from your queries. So if you don’t have date anywhere in your where clauses or your date isn’t in a quality or range that’s going to fit a partition, or something that will fit parts of partitions in your where clauses, don’t even bother with date. Do it on something that’s going to be in your doodads. It can certainly kind of lopside partitioning if you do it by like customer ID and you have some customers who order a ton of stuff and some customers who hardly order anything, but if date isn’t in your where clause, partitioning is not going to do you any good.

Richie Rump: But I will say 90 percent of the time I think it’s going to be date because you’re going to want to start archiving things and rolling things off and doing that kind of stuff. For warehouses, it’s going to be date. I’m going to assume that it’s going to be date. But like you said, bro, if you need the partition elimination because you have a—I don’t know—six terabyte table, then you better use that date in your queries. Otherwise, you’re just going off to the abyss and [inaudible] things and all of a sudden things just…

Erik Darling: You’re scanning the whole table anyway.

Richie Rump: Yep.

Erik Darling: If you’re going to try out something like partitioning, you may want to give yourself the easy button first and try partitioned views out rather than going straight to table partitioning because you get a lot of the same magic without a lot of the same sort of stifling rules and weird stuff that can happen. If you want to test out your theory, look at partition views. I have a post on it from a while back where I turned the votes table in Stack Overflow database into a partition view. There’s pictures and execution plans and all sorts.

Richie Rump: Of course you have a post, of course you have a post.

Erik Darling: I have to contribute something around here.

Richie Rump: Oh you have to contribute? What about me? What am I doing?

Erik Darling: I don’t know.

Richie Rump: I don’t know either. I live in a cloud. That’s what I do.

Erik Darling: GIFs.

Richie Rump: GIFs, yes. I contribute GIFs. And I’m damn good at it too.

Erik Darling: GIFs and chat.

Richie Rump: But going back to the partition, you can put an index across the entire table and that will be fine but you lose that partition swapping.

Erik Darling: Yeah.

Richie Rump: I had a solution for a client where that was the actual solution—the partition swapping isn’t really for us, it’s really to partition elimination, except these few queries and these few queries we need them to run within the hour. So we threw indexes on there. We said the hell with partition swapping. If they ever needed to do it, they’d have to drop the indexes, swap it out, and then rebuild the index again.

Erik Darling: What Richie is talking about is non-aligned indexes where the indexes aren’t built aligned to the partitioning key. So you lose all the swap in and swap out magic. That stuff is well documented in Books Online if you want to go ahead and take a look there.

 

What does SOS_SCHEDULER_YIELD mean?

Erik Darling: That’s a long question, I’m going to save that for later. “What does SOS_SCHEDULER_YIELD mean?” It means that your query has yielded its spot on the CPU to another query because it did not have what it needed to run. What happens is a query will start running and it will get a thread. That thread will be on a scheduler or a CPU and it will sit there and it will say, “I’m going to run, I’m going to run, I’m going to run.” If it doesn’t have everything it needs to keep going within 4 milliseconds, it exhausts its quantum and it is shuffled to the back of the queue and SQL Server says, “Wait your turn. Something else is going to try to run now.” So something else will go and try to run and it’s just kind of on and on until everything actually runs.

Richie Rump: I love that term, quantum, that’s awesome.

Erik Darling: Yeah, exhaust the quantum. SOS_SCHEDULER_YIELD isn’t usually a big deal unless you have sort of like long average waits on it. So if you see long average waits on SOS_SCHEDULER_YIELD, it can be a sign of pretty bad CPU pressure. If you see a lot of short average waits on SOS_SCHEDULER_YIELD, it usually means that your CPUs are just dead dog slow. I just stopped myself from saying like four curse words in a row saying dead dog slow instead.

Richie Rump: Unlike the new beast that you’re putting together. That will not be dead dog slow.

Erik Darling: No, that one is going to be happy dog. I have most of the parts for it. This is exciting.

Richie Rump: Oh, no.

Erik Darling: This is exciting. If we run out of questions, I’m going to start showing people computer parts, that’s what I will do.

Richie Rump: No more questions, people. Computer parts are fun.

 

What permissions do you need for user-defined types?

Erik Darling: “I have learned the hard way that you have to give permissions to use user-defined types. Do you know why SQL Server would require you to have permission to use user-defined types?” No, I do not know. I am not great at security stuff. You might want to ask a fella named Denny Cherry about that or go buy his book, Securing SQL Server. There might be something in there about it. I don’t do a lot of security work. I hate roles, permissions, and all that garbage.

Richie Rump: Or, K. Brian Kelley, one of the two. Those are our two security people, go to.

 

Does it make sense to reorganize GUID indexes?

Erik Darling: “Is there an actual time where rebuilding reorg indexes would benefit queries that always have where equals GUID? I know your stance and don’t disagree but need to be able to explain why.” So if your where clause is on a GUID that brings up an interesting architectural question. Let me start from the beginning. If you have a GUID column in your table, a lot of people will say, “This GUID is unique so it’s going to be my primary key.” They also make that their clustered index and that’s where they shoot themselves in both feet with large, large cannons because with GUIDs, unless you use new sequential ID or your app uses a sequential GUID, you’re inserting data all over the place. Think about if you say were partitioning and you used a sequence and you only had the numbers 1 to 100 over and over again, that would be great for like hash partitioning because you could just dump everything into your hash partitions over these 100 numbers. But with a GUID, you’re just sticking random data in all over the clustered index, just getting shuffled around. It can be painful. In those cases, you typically want to lower fill factor until fragmentation kind of levels off. Beyond that, you want the GUID to be the non-clustered primary key and then stick your primary key on an ascending column that kind of gives you an append-only workload.

Richie Rump: Yeah.

Erik Darling: When you do those three things: the non-clustered primary key, the clustered index on an ascending column, and the lowered fill factor on the GUID index, you do yourself a lot of favors as far as page splits, fragmentation, and stuff happening all over the place. In those cases, you will have to rebuild or reorg less frequently because you will have diminished fragmentation to a point where it’s much less of a problem.

Richie Rump: Yeah, and if you’re getting your GUIDs from some other source, other than the database, such as .NET, there’s a way that you can order your GUIDs so that they’re in the same order as SQL Server. Because SQL Server handles its GUIDs a little differently than .NET does. I did a post on that many, many years ago. It’s on Jorriss.com. I still get lots of hits for this thing. I don’t understand why we need to figure out the order of GUIDs to align with SQL Server nowadays. Back then, I had an Entity Framework 1.0 problem but we don’t have these problems anymore.

Erik Darling: Some people might. We have people on SQL Server 2000. Some people might have Entity Framework 1.0 problems, we don’t know.

Richie Rump: There’s a way you could do some bit swapping to align those GUIDs so that they’re in the same format as SQL Server.

Erik Darling: Sounds dirty.

Richie Rump: It was kind of messy trying to figure out how all of this stuff worked. Then everything starts aligning up and you won’t get like these big blocks of, oh, these are from SQL Server coming from a load, and oh, these were not. These were coming from the .NET application. They’ll still be in different order somewhat, but they won’t be as stark.

Erik Darling: There we go.

 

How should I fix RESOURCE_SEMAPHORE waits?

Erik Darling: Next question. “I’m getting the resource semaphore wait type while a bulk insert via SSIS is running. Do you have a rule of thumb on how to handle that besides more memory?” Stop everything else from running? You can use Resource Governor to limit, if you’re on Enterprise Edition, you can use Resource Governor to limit the amount of the memory grant your query can ask and get. If you’re on—I guess, geez, let me rattle off the editions—SQL Server 2012 SP3, 2014 SP2, or 2016 all, you have min grant percent and max grant percent query hints where you can set minimum and maximums for memory grants at the query level. I don’t know if that works with bulk insert though. I couldn’t tell you on that. I’ve never tested that. That’s a really interesting question. Let me know if you come up with anything.

 

If you could only watch one SQL Server metric, what would it be?

Erik Darling: We have a question here. “If you could only watch one baseline for a SQL Server database, what would it be?” Wait stats. All of them. Just wait stats. That’s what I would look at. I would run sp_BlitzFirst which you can get from firstresponderkit.org. I would just take 30 second samples of that and I would log it and I would trend it and that’s all I would watch is wait stats. I would say, “Look at the things I can make better with wait stats. I don’t care about anything else.” It’s all about what the server is waiting on. I don’t care if there’s a CPU spike for ten milliseconds. I don’t care if memory dives when I run DBCC CHECKDB or take a big backup. It happens. Watch your wait stats.

 

Is XML shredding easier on SQL 2016?

Richie Rump: Next one is a good question.

Erik Darling: Are you talking about the XML question?

Richie Rump: Did you put that there?

Erik Darling: I wish I was that thoughtful. “Have there been any improvements with XML shredding on SQL Server 2016 vs 2014?” Absolutely positively not. The last cool thing that XML shredding got, well, it wasn’t even XML shredding, it was just XML in general was in 2012 when we got selective XML indexes. Since then, there has been bupkis. That is why every time I work on sp_BlitzCache I have to drink an entire bottle of Lagavulin.

Richie Rump: The other question is, why are you doing XML shredding inside of SQL Server? I understand, Erik, why you’re doing it because it’s [crosstalk]. There’s no choice for you because that’s how they put the plans in, right? But from an architectural perspective, if you’re putting stuff in XML in SQL Server and then you’re shredding it inside of XML, why are we doing that? That should be done on an app server, really. I could do that in the cloud with Lambda or Azure Functions, right?

Erik Darling: I used to have to do ETL stuff where I would get an XML flat file and I would import that in but I would shred the XML in a staging table and then dump the relational data into a table and never touch the XML again because XML sucks.

Richie Rump: Yeah, it’s one of these things where, hey, let’s do what each piece is good at, right? SQL databases, not very good at XML. So why don’t we do something where CPU is a little freer and less expensive? That’s in the app tier.

 

Can I exclude high severity alerts by IP address?

Erik Darling: There is a question about alerts. “I’ve set up high severity alerts for severity 20 errors. Our security team does tests where they send bad packets by design. Can I filter alerts to exclude certain IP addresses so I don’t get alerted every time they scan?” I don’t think so. I don’t think the alerts are that smart. I think you either get them or you don’t. I would set up an email rule to look for that specific error and have that just send something off to a different folder rather than try to get the SQL Server itself to do that.

 

Is NOLOCK okay when…

Erik Darling: Next up, “Is it okay to use NOLOCK on insert into an archive database where the select from live db is stagnant and won’t change?” Yes, I don’t care. NOLOCK is a problem when the data underneath it is volatile and you end up with incorrect results. That is my beef with NOLOCK. Just like I said in the blog post that I wrote, the main problem with it is that people misunderstand what it does. They think, “I’m not going to take any locks. This is going to be awesome. I’m not going to block anything.” That’s not the case, it’s just that you don’t respect locks taken by other queries. If that data isn’t doing anything underneath, then NOLOCK your heart out. See if it makes a difference.

Richie Rump: Yeah, I had a large system I worked with and everything we did was NOLOCK. That’s because it was only additions to the database. It was no updates. None. We understood our workloads and guess what, we went ahead and used NOLOCK because we could use it with confidence that we weren’t going to get anything crazy with it.

 

What should I do about 15-second IO waits?

Erik Darling: “When dealing with a hardware-encrypted SAN and seeing SQL has encountered however many occurrences of 15 second I/O waits, any suggestions for investigation or remediation?” Yeah, again, this is often not the SAN. Well, this is often not the storage. This is often not the disks. This is often the connection between the server and the disks that’s the problem. It’s you’re either trying to suck too much or push too much or do both at the same time through too small of a pipe. If you have to read a bazillion gigs or even just like ten gigs of data across a really small one gig pipe, it’s going to take you a while. So look at your storage connections, not your storage. Look at the pathing.

 

What does DBCC SHRINKFILE lock?

Erik Darling: “What does DBCC SHRINKFILE actually lock when moving data around?” Pages, just like everything else.

Erik Darling: “Can table compression be done online?” Yes, you can rebuild an index online in Enterprise Edition. Why don’t you just try that? Online equals on with the compression syntax. Just try it. I promise you can try that on a development server and figure it out. You are a smart person. I know that because you’re here and you want to listen to Richie talk.

Richie Rump: Yeah, sure, yep. Exactly. What he said.

Erik Darling: Follow up question on partitioning, “Does partition elimination still happen if you have [inaudible] a non-aligned index?” No.

Richie Rump: No.

Erik Darling: You’re lucky if it happens with an aligned index, forget a nonaligned index.

Richie Rump: Yeah, there’s quite a few hints I had to drop when working with that particular system. Just make sure you use this next, please, thank you very much.

 

Any experience with Git as a code repository?

Richie Rump: “Any experience with Git as a code repository?” Why, yes. Erik, tell them all about it.

Erik Darling: When I hit the sync button, I break something. When I hit the commit button, I fix something. Sometimes when I make a pull request, everything works. Other times, I fat finger something and I do the wrong—you know what, Git I’m sure is fine. It’s just me. I’m the problem.

Richie Rump: Yeah, it’s you. I think the big problem with Git is you need to use it every day. You get used to the Git workflow. If you’re not into that kind of every day, it’s kind of rough. Do I have to commit first? Do I stash it? What’s push versus pull? And getting all that.

Erik Darling: One thing that hit me in the butt when I first started was I didn’t realize that after I made a pull request, if I made more changes, I had to sync those changes. I just thought like, I made a pull request, I’m working in my repository, everything go up to the cloud where it makes everyone happy and they get new warnings about their plan cache crap, but no. I had to keep syncing stuff and the pull request was like five commits behind. I was like, “Where’s all the good stuff?”

Richie Rump: Yep, got to merge everything back. There are some good courses on Pluralsight on that, on Git. What I like, it’s not even on Git, it’s on Mercurial, it was Joel Spolsky’s tutorial on Mercurial. I thought he did a really excellent job. Mercurial and Git are really side by side. They’re very, very similar to one another. So you’ll be able to pick both up very quickly. I like that one and I forget what the name is but it’s Joel Spolsky. He has a tutorial on HG or Mercurial. Those were great. I also use a tool called SourceTree. That seems to make things a little easier from a GUI perspective. I like that a lot, I know there’s some folks on this team that don’t like it as much. It makes things a little easier for me so I can see things visually about what’s coming up and I’m not at the command line all the time. Although, I do use both. I do use both the command line and I use the visual SourceTree. So, there you go. So, hey, I got some dev-y things in here. What’s up?

Erik Darling: Yay. Good for you.

Erik Darling: Fellow human says, “I have set up eight tempdb files for 24 cores.” Good.

 

What do you look for in a senior DBA?

Erik Darling: Another fellow human says, “What do you look for in a senior DBA?” I don’t look for anything in a senior DBA.

Richie Rump: Grey hair. I usually look for the grey hair and the worry lines. The worry lines are a dead giveaway that they’ve been through hell.

Erik Darling: Swollen liver is another good one. If their liver is large and firm, I think that’s a good sign. Bloodshot eyes is another good one.

Richie Rump: A little twitch. You know, you get that little twitch going on. Just every once in a while, just kind of twitch a bit.

Erik Darling: You know what, it’s either a facial tick or a hand tremor. That’s how I know.

Richie Rump: Oh yeah, the hand tremor, they’re the best ones.

Erik Darling: Yeah. When they eat potato chips and it sounds like maracas, that’s when I know that’s a senior DBA.

Richie Rump: I like to say, give me your opinion on ORN and if they turn red, you know it’s a good one. You know it’s a good one.

Erik Darling: It’s a good question because in my line of work—not your line of work, but in my line of work—I’ll often get on the horn with people who are like, “I’ve been working with SQL since 6.5 and I’m 20 years doing development.” I get on and within 15 minutes I’m like you may have been doing this for 15 years but I don’t think you’ve been paying attention. There’s something missing here.

Richie Rump: Yeah, you’re right. Not so much on the dev side because in dev you’re constantly learning new things, so everybody is in year one, two, three, or four and then we learn something else. But in the DBA data world, you could have that same person doing year one for 20 years. So they have 20 years with experience for the very first year. They don’t go past that because they just get comfortable in what they’re doing and they’re like, “Let me check the backups, let me do all this stuff, and then that’s it. It’s very surface-level knowledge. If you want to kind of get a really good understanding of how SQL Server works, you have to go past year one. You got to get underneath. You’ve got to get under the covers. You’ve got to get underneath the architecture. You got to do what Erik does up there and try to understand what all these yields and all this stuff—so if you want to be a good DBA, then you’re in the right place here trying to figure all this stuff out.

 

Should I enable RCSI on SQL 2012 SP3?

Erik Darling: Let’s see here. “Running SQL 2012 SP3, is it recommended enabling recommitted snapshot isolation?” No, because you need to test your code carefully to see if it works. We have great blog posts and linkfests on recommitted snapshot isolation. You need to just go read a whole lot more about it. Nothing I say here is going to make sense to you. You should go read and learn.

Richie Rump: But we love it here.

Erik Darling: Yes, when it’s appropriate.

Richie Rump: Yep. And if it’s greenfield, just turn it on when you first create the project in the database, just turn it on.

Erik Darling: Yeah.

Erik Darling: “Are there any downsides to not discarding temp tables or table variables when used in stored procs?” No, they get destroyed when the session is done.

 

Hardware Show and Tell

Erik Darling: You know what? I just want to show people hardware for a minute. I want to show people all the stuff that I’ve been getting. This is my CPU. I’m going to cover up the price so no one gets weirded out. This is my I7 6850.

Richie Rump: That doesn’t look like an I7. That looks like a box.

Erik Darling: It’s right there. You can see it, it’s in there.

Richie Rump: That’s getting better.

Erik Darling: Here’s what I’m really psyched about. This is the one I’m super into: a 128 gigs of RAM. I’m going to change the world with that, ma. Actually, you know what, the rest of it is kind of boring. I’ve got some hard drives. I got my Icy Dock. I got my motherboard. I got my case over there that’s like 50 pounds. I’m not going to bring that out. But these are the things that I’m most psyched on, my big honking processor and my big honking RAM. If I drop one of these, I’m going to kill myself. So I’m going to put them down now.

Richie Rump: No, drop them. Drop them. Go ahead.

Erik Darling: You’re funny.

Richie Rump: This is what came in the mail for me today.

Erik Darling: Wow.

Richie Rump: Wolverine and his motorcycle. Look at that.

Erik Darling: Cool. Check him out. Those big, dead eyes.

Richie Rump: Yeah. He’s going to scrape your eyes with that one.

Erik Darling: That’s true. People who like the computer parts stuff, as soon as I have all the parts, I’m still missing some, I still don’t have everything in the mail, I’m still missing my graphics card and my M2 chip and some other stuff but as soon as I have everything, which is going to be some time after the first, I’m going to have blog posts with all the pictures of me opening stuff and putting it together and probably gauging my eyes out while I try to figure this god awful mess out. “What motherboard?” Oh, god, please don’t let me drop anything. I got this ASUS motherboard, which is pretty cool.

Richie Rump: Which doesn’t have onboard video, does it?

Erik Darling: No, this one does.

Richie Rump: Oh, that one does.

Erik Darling: I got one that does because I saw your comment about that in chat and I was like, “Oh, that’s probably not a good idea.” I got a couple of my disks. I don’t have all of them yet. I need four more of these, 960 gig SanDisks. Those are all going to go in this six-slot SSD.

Richie Rump: Oh, gosh. That’s crazy.

Erik Darling: I know, I’m building a server. This is my chance to build a home server. I am never again in my life going to have this good of an opportunity to do this with Brent’s money.

Richie Rump: I’ve even started setting up mine.

Erik Darling: There you go.

Richie Rump: Little bonus.

Erik Darling: After the first, I have to order the four more hard drives and get all that stuff in. Because like when I went to place the order, the low price on most of it was out of stock on these like shady [inaudible] I was like, what the hell. So I had to go and look at real prices for people who actually had stock to send me. I was like, oh, that’s 50 bucks more. So with tax and then shipping on some stuff, I didn’t get exactly what I wanted from the PCPartPicker build, but I was still pretty psyched about the amount I could get in one shot and I can just finish up with buying the rest of the parts next year.

Richie Rump: What are you going to use for the software for your virtual machines?

Erik Darling: For VMs I’m going to run Windows Server 2016 because I really want to play with some of the stuff in there. There’s some cool new features. Windows Server 2016, I’m going to poke at. I’m not going to do too many spoilers here but the stuff I want to mess with.

Richie Rump: That’s going to be your base OS, right?

Erik Darling: You know, I haven’t decided on that. I’d like it to be because I feel like if I’m building a machine of that size, server software is kind of like the legit choice because I’m not going to be playing games. I’m not going to be doing goofy stuff on there. I don’t need Skype for business.

Richie Rump: That you know of, yet.

Erik Darling: Yeah, who knows? My plan is to make this as server-y as possible so that I don’t even have the temptation to be like, “Let me see how Team Fortress 2 runs on this.”

Richie Rump: Oh, you know you want to. I know you want to.

Erik Darling: That’s why I bought a 512 gig graphics card. I want no temptation here. I want nothing. I don’t want it entering my head.

Richie Rump: You know what, in a week your tech budget refreshes again.

Erik Darling: I know, that’s when I’m going to buy the rest of the drives. Don’t tell Brent.

Richie Rump: That’s when the video card comes in and Erik just went, “Bleep it.”

Erik Darling: Right? I’ve decided to start rendering video and it turns out I do need… Sorry.

Richie Rump: …5k at 60 frames a second, it’s amazing.

Erik Darling: And this gaming mouse for some reason. I could just plug this one in. Come on, cord. I got this one here. I just use this for clicking around.

 

How do I prove it’s a SAN problem?

Erik Darling: We’ll do one last question, we have one minute left. “I need to prove that the systems and SAN people that it is their infrastructure that is causing the disk latency up to 1200 milliseconds and I’m going to…” Disk queue length? Come on, man, use a metric that’s been relevant since 2003. We are on VMware, what is the best way to do this? Go to brentozar.com/go/cdm. Download a tool called CrystalDiskMark and you can use it to benchmark your disks. What you’ll probably find is that the top line, the top lines up there, are going to be just about what your disk network is capable of pushing. That doesn’t mean it’s what your disks are capable of pushing, your disks are probably sitting there going [inaudible]. But it’s always going to be—this is like the third time this has come up in one go around—it’s always the connection between the server and the SAN. It’s never the SAN or the server. Some dingbat put a one gig iSCSI between the two and you’re getting reads that are like the wagon line out of [inaudible], it’s the slowest thing in the world.

Richie Rump: Yeah, a few weeks ago we ran that on a server and it was like 150.

Erik Darling: Yes.

Richie Rump: Tara ran it on her own machine and it was 450. A few days later, Nick Craver from Stack Overflow sent theirs out there and it was like 10,000. I’m like oh my gosh, these guys, man. They’re amazing.

Erik Darling: Yeah, local SSD is amazing. I can backup Stack Overflow in like three minutes because I’m getting like 500, 600 megs a second just local SSD. I’m like, “Look how crappy your server is with a $300,000 EMC SAN.” Shhh. Don’t tell them. Look what you could get if you spent $500. It’s fun. It’s funny.

Erik Darling: Someone says, “GUI or core?” GUI. I can’t type. Look at me. Look at these hands. These are not typing hands. Get out of here. All right. We’re at 12:46, we overshot by a minute. I’m keeping Richie from lunch and who knows what else. Thank you all for joining us. We will not see you next week, we will see you the week after. There is no Office Hours next week. Don’t show up. You’ll be sadly disappointed. Goodbye and merry Christmas and happy New Year and drive safely and to all the good drunk…

Previous Post
GroupBy Registration is Open for 3 More Days
Next Post
Build Me A Build: What Would You Do?

7 Comments. Leave new

  • Hey guys – just a quick comment. Careful with reaching quick conclusions from CrystalDiskMark, which runs DiskSpd underneath the hood: http://sqldatapartners.libsyn.com/episode-75-storage-testing

  • This link should have the transcription, but it’s not a super awesome one (it’s auto generated): http://sqldatapartners.com/2016/12/21/episode-75-testing-storage-devices/

    • Hmm – can you do me a favor and reread what Erik said? From what I can tell, what he said and what you’re saying on the transcript are the same thing: CrystalDiskMark tests the storage area network (SAN) between you and your storage. Both of you seem to be agreeing that it has nothing to do with your workloads.

      If I’m missing something there, definitely clarify, but I’m just not sure where the differences are. Thanks man!

      • I think what you’re “missing” is that you think that I said that Erik was wrong 🙂

        Erik pointed out correctly that CrystalDiskMark/DiskSpd/SQLIO is only good to benchmark your connectivity between your host and your storage device, nothing else. However it is a widespread myth that CrystalDiskMark/DiskSpd/SQLIO is good for thorough testing of your storage. It’s not. In any of its flavors, with any of its parameters.

        So – you might call my reply astroturfing, and I’d sort of agree there! 🙂

  • Walden Leverich
    January 4, 2017 12:56 pm

    Following up on the tempdb on SSD question, and specifically the PCIe comment… How do you see these drives made redundant in the real world? Are people putting two (or more) of them in and using Windows RAID to mirror them? Since they’re not SATA/SAS connected you can’t use traditional RAID cards. Or do people just assume they won’t fail, or are at least as unlikely to fail as memory and CPU which isn’t redundant either?

Menu
{"cart_token":"","hash":"","cart_data":""}