This week, Brent, Erik, and Richie discuss disabling telemetry in SQL Server 2016, proper place for log files, Availability Groups, measuring data returned from a single query, whether CHECKDB should be done on tempdb, limiting tempdb usage for each session, transferring large amounts of data from on-prem to the cloud, and Iron Chef spoilers.
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 – 2017-05-24
Should I put tempdb’s log files on a separate drive?
Erik Darling: Alright, Mary has a question about where you put your files. Mary says she, “knows it’s a good practice to put the log files on separate drives from data files, but what about for tempdb? Where should that log file be placed for optimal performance?”
Brent Ozar: I don’t know if I’ve ever seen tempdb’s log speed be a problem, and if I did, I don’t know how I would find it.
Erik Darling: I guess my thing is that like, you know, logging in tempdb is pretty well optimized. There’s a lot of – like tempdb doesn’t care about, you know, taking snapshots the way it does for like, you know, regular log files. It just cares about being able to rollback, it doesn’t care about needing to roll forward because when SQL Server restarts, tempdb just starts fresh anyway. So again, I’m with Brent, I’ve never seen tempdb’s log be a problem, but usually, when there’s an underlying drive problem, it’s affecting all of the data files and the log files. So you know, I would just say if you have tempdb I/O issues, then your problem isn’t where the log file is, your problem is something bigger.
Brent Ozar: And have you – so when you built servers, did you put tempdb on a separate log – temp db’s log file in a separate drive or did you just put it with the data files?
Erik Darling: you know, with the San it just didn’t matter all that much. I guess, you know, from – we’ve more of an availability issue, so like if tempdb’s log or data file ever started growing massively, I wouldn’t want one or the other to be impacting the other groups. I wouldn’t want the log file to grow out of control and mess up the data files, and I wouldn’t want the data files to grow out of control and squish the log files. So I would separate it for that but I never separated it for performance. It was more of a cautionary thing. By the way, this is what fell that was incredibly distracting [crosstalk ] flamingo that my kid made me in her pre-K class, and it fell. What the hell is that? Because it was right near my head.
Richie Rump: Flamingos don’t fly…
Erik Darling: I know, they just fall. It just crashed.
Richie Rump: It was like the opening of Miami Vice, right.
Erik Darling: A lot like that.
Brent Ozar: I missed that, that was nicely done, that was nicely done, I have to hand it to you.
Erik Darling: Wind blowing through my hair and all that.
What should I use as a witness in my AG?
Erik Darling: Clark asks, “in SQL availability groups”, because there’s no other kind, “are there any benefits other than cost of running a file share as a third node versus just having three or more SQL nodes to have a quorum?”
Brent Ozar: Oh man, I just did a quorum module here at SQL Intersection. Normally I don’t like having any extra nodes in the cluster that aren’t involved in SQL Server, just, you know, more complexity if something goes wrong. I would just use a file share witness. Just use a file share witness, point it at that and then Windows, starting with WIN2012R2, Windows will automatically take away a vote from the file share as well, so you don’t have to worry about it going into an even number of voters. I’m simplifying a lot there, but just that I would rather use the file share.
Is a proc’s last execution date stored somewhere?
Erik Darling: Alright, well there we have it. Deborah asks… Let’s see, Deborah asks, “is the last execution date stored persistently some place? I know about looking at sys.dm_exec_procedure_stats and its limitations.”
Brent Ozar: Ooh, what do you want to use it for? What are you looking to find out? It is – I like where she’s going with proc stats, but you probably even remember the field names by heart, given how many times you’ve been in there.
Erik Darling: I would – I don’t know…
Richie Rump: Well what have you been playing with lately?
Erik Darling: Because I’m trying to think like persistently and I’m trying to think prior to query store, and I don’t have a good answer on prior to query store. Even query store flushes data out, so that’s not even 100%.
Brent Ozar: If you’re looking to audit whether or not something’s used or not, like the easiest way to do it is create a table of your own and then add a line to the end of the stored procedure that just either inserts a record into it or updates the existing record into it. But the instant that you know that it’s actively used, you need to get that line the hell out of the code.
Erik Darling: Makes sense to me.
Which Itzik Ben-Gan book should I read?
Erik Darling: Alright, Robert asks a funny question, and it’s funny because of the way we feel about exams in the first place. Robert asks, “do you have any feedback on the book Exam Ref 70-761, Querying Data with Transact-SQL first edition by Itzik Ben-Gan? Do you have a site you go to for feedback on technical titles?” I usually just read Amazon reviews.
Brent Ozar: Yes, yeah.
Erik Darling: As far as that book goes, that’s – I mean I don’t think it’s been updated and it’s a fairly old book at this point.
Brent Ozar: Really old, yeah.
Erik Darling: So Itzik – we’re going to have to move some stuff around here, don’t mind me…
Richie Rump: Don’t fall off your chair…
Erik Darling: Look pal, if I didn’t do squats, I’d be screwed. This book is much newer, I think it’s from, what, 2014?
Brent Ozar: I think so.
Erik Darling: Yeah, 2014, it’s much newer, it’s full of great stuff. It’s probably a lot of the same stuff but just updated for newer versions as well. As far as other books, jeez, I mean I usually just read Amazon reviews or, you know, see what other people are reading. [crosstalk]
Brent Ozar: Yeah, yeah, I was going to say that in – plus too, we’ve been around in the community long enough that we kind of go by author names, probably. Or you go – yeah, the author name that you hit in this one, he is awesome, Itzik Ben-Gan is a genius, and so I would just tend to gravitate towards the latest book that he’s written, that’s the one I would go for. But in terms of – you asked something else which is the exam prep; none of us in here are currently certified, I don’t think…
Richie Rump: Oh no, I’m sorry, my PMP certification just lapsed.
Brent Ozar: Yeah, certification dropouts, and if Tara was here she’d – the problem with cert exams is that they measure what the marketing team wants you to know, not what you do in your daily job. So if your goal is to pass an exam, yeah, get an exam prep book. But if you want to learn SQL Server bust because you’re interested in that topic, get Itzik’s latest book, and it will be phenomenal.
Erik Darling: Even as far as, you know, just whatever books goes, Itzik has two other great books, they were written for 2012 but they hold up pretty well today. There’s like this thing, I believe it’s T-SQL Fundamentals and High-Performance Windowing Functions, or window functions – I always forget if the “ing” goes on there or not. But high-performance window or windowing functions for SQL Server 2012, and they’re both fantastic reads, like you’ll learn a lot even if you already, you know, have a pretty good footing in T-SQL. If you want to get those two and then work your way up to that bible, it’s a pretty good few months you have ahead of you of reading stuff.
Richie Rump: Well that, and there’s always the documentation, I usually go straight to there and there’s a whole bunch of stuff that you could actually glean from just reading the docs; that’s me.
Erik Darling: I have much worse luck just reading technical documentation that you do Richie. When I read technical documentation, I look at – I look at the full syntax where they have, like, all the brackets and parentheses and N comma stuff and I just – I blank out and I just scroll right down to the examples and think oh, that makes sense…[crosstalk ]
Richie Rump: So lame.
Why are you wearing an AWS hoodie?
Richie Rump: I have a question for Brent. So you’re in a Microsoft conference-ish, you have Microsoft employees there, you’re walking around with an AWS hoodie on…
Brent Ozar: And it was worse than that, when I got dressed this morning, the shirt underneath said Google Cloud, and I went to – I walked to the door and I took the one look at the mirror before you go out and I’m like oh, oh no I don’t think I should push that quite that far. But I’m like, it’s a really comfortable shirt and it’s a really nice hoodie, but – and for some reason, Microsoft doesn’t seem to send me free clothes anymore, I don’t know why that is.
Richie Rump: I don’t know why that is either, let me just scratch my head a little bit, I don’t know. If you’d have worn that Google Cloud thing and all of a sudden people are like, oh there’s troll boy, trolling again.
Brent Ozar: Right, yes, yeah it’s almost like I feel bad because I didn’t have any other sweatshirt, I have like a kCura sweatshirt and it’s in my other suitcase, but… So yes, that made me remind me that when I go to conferences, I need a different set of hoodies.
Richie Rump: When I spoke at the local day of Azure and spoke on Azure functions and I was wearing that exact hoodie during the whole time.
Brent Ozar: Yeah, it’s really nice.
Erik Darling: This is what you get for picking sides and wearing tech gear. You always have to worry about what you’re wearing, it’s worse than like gang territory.
Brent Ozar: Yeah, I think the only way to win is like wear Atari or Nintendo gear.
Erik Darling: Plain black t-shirts.
Richie Rump: How could you be wearing Nintendo and not an Xbox One, how dear you Brent, how dare you.
Erik Darling: Already screwing up…
Brent Ozar: That would be a problem.
Richie Rump: And you have a Macbook, oh, look at that, you are so – oh look at that, and an iPad, so anti Microsoft…
Brent Ozar: Watch, iPhone, yeah, anti community, you name it.
Erik Darling: Brent’s going to get shot at one of these conferences.
Richie Rump: Shot bad looks, that’s what’s going to happen.
How do I calculate the size of query results?
Erik Darling: Alright, we should get back to questions, I guess, maybe. Don asks, “is there a way to calculate the total number of bytes returned from a SQL query in SQL Server 2014? Our developer is saying that my query is returning too much data and it is causing the web page to load slowly.” I would tell your developer to implement a paging strategy then.
Brent Ozar: yeah, and when you say paging strategy, talk more about what you mean.
Erik Darling: I mean when people want to look at a reporting query, typically they don’t need a million results. No human is going to sit there and consume probably more than 10,000 rows of data and be able to look at it and come up with anything meaningful from it. When I talk about a paging strategy, I mean return 50 rows at a time and if you need 50 more rows to figure something out then you click load more rows and get 50 more rows and then 50 more rows as you need things. You know, your developer, DBA, isn’t necessarily wrong that your query is returning too much data and I’d stop trying to measure it in bytes, I’d measure it in what you actually need and what you actually consume when you look at this…
Brent Ozar: Rows times columns.
Erik Darling: Yeah.
Brent Ozar: If you wanted to really calculate the size, the really crafty way to do this is take the exact query and write about the FROM, put into, and then put a table name. So like SELECT stuff into whatever the table name you want is, and then you can look at the size of the table afterwards, but just – I’d take the simple way, too, rows times columns. How many rows are you bringing back and how many columns are you bringing back?
Richie Rump: Right, and the other side of that is, how frequently are you calling that? So if you’re calling it every time the page loads, ask yourself, do I need to call this ever time the page loads? Can I cache this data somewhere so I don’t have to make that SQL call? Someone once told me, the cheapest SQL call you can make is the one you don’t make, I think it was Erik, he’s smarter than Brent.
Brent Ozar: Great post, Erik…
Richie Rump: But caching helps a lot because you’re no longer querying the database. Then you can actually say, yeah maybe our cache is huge now because we’re pulling back way too many rows as opposed to calling direct from SQL and now you’re doing all that stuff across the water.
Why did you rent a Toyota Prius?
Brent Ozar: Richie’s going to be jealous of my rental car this week, Toyota Prius.
Richie Rump: No…
Brent Ozar: Every now and then I switch and play for the other team.
Richie Rump: Yeah, just so you know, I actually valeted my car just so the valet knew I was driving a convertible Mustang. That’s…
Erik Darling: Can you please take my convertible Mustang?
Brent Ozar: Can you please park it out front, and I’m going to walk around it a few times.
Richie Rump: And, you know, when you come and get it, could you swing it around a couple times so people see it?
Erik Darling: Do some donuts.
Brent Ozar: And say, the Florida Mustang is here.
Richie Rump: Not even a GT people, I mean seriously.
Erik Darling: Sad, sad day.
Should I do CHECKDB on tempdb?
Erik Darling: Alright, Grahame says he read somewhere, it doesn’t say where, he does not cite his sources, but Grahame says he read somewhere that “CHECKDB should be done on tempdb, why?”
Brent Ozar: We used to have it in part of sp_Blitz too because I’m like, hey look, anytime you find any database errors anywhere, you probably want to know, and tempdb’s often on a different set of drives. These days a lot of people are doing tempdb on, you know, local storage and flash storage, stuff like that. It doesn’t do in-depth checking the way it would on a user database. The reason that we finally pulled it out of sp_Blitz was, if you use the default maintenance plans, SQL Server actually skips – the maintenance plans skip tempdb [crosstalk].
Erik Darling: Ola Hallengren’s system databases stored procedure, excuse me, also skips tempdb, and it’s actually kind of funny, if tempdb ever does go corrupt, I think – what was the way to fix it? You have to shut down SQL, delete the files and then start SQL backup, because even though tempdb reinitializes everything, if you don’t delete the files, it will just keep using the …
Brent Ozar: Using the same one again.
Erik Darling: But that doesn’t help if your storage is zapped because…
Brent Ozar: Right, if you have crappy SSDs, yeah, it will keep getting re-corrupted.
Erik Darling: So I don’t think I’d haul off and start running CHECKDB on tempdb, but you know…
Brent Ozar: If you get error corruption errors out of tempdb then that’s when you start…
Erik Darling: Because I don’t know if you remember, back at DBA Days what happens when tempdb is corrupted.
Brent Ozar: You should explain, that was phenomenal.
Erik Darling: So back at DBA Days, we broke tempdb and we ran a stored procedure that would overwrite some page data in tempdb and just kind of insert stuff in a loop and crap things up real bad, and when you do that, SQL Server stops and restarts. And if you happen to make that stored procedure an agent job that runs at startup, SQL Server will actually not start back up, just to kind of restart that box. So when tempdb does become corrupt, you know immediately because your SQL Server ceases to exist, it just goes – waves goodbye.
Brent Ozar: And if you want the code to do that, search on the site for Dell DBA Days tempdb and Erik’s code should do it to, kind of, auto-corrupt databases is out there; we had a lot of fun with that.
Erik Darling: Also the one that randomly changes database settings is out there as well, that was a lot of fun to write and run. We got to do that stuff again.
Brent Ozar: Some days we use our forces for good, but just not usually.
Erik Darling: Not when we have millions of hardware dollars from someone else. then we’re like oh, [crosstalk] setting off fire extinguishers in the server room.
Richie Rump: That was the barbecue pork, that’s…
Brent Ozar: Which probably caused us to make more bad decisions.
Richie Rump: There was a lot of bad decisions after the big beans. The big beans were probably the bad decisions and then it just cascaded all the way down there.
Erik Darling: We were just all meat-drunk and strange things happened.
Can I limit tempdb usage per session?
Erik Darling: Alright, Renee asks an interesting question. This is a question that I would actually like to answer it myself. “Is there a way to limit tempdb usage by default for each session, kind of like an available percentage that can be used? Resource governor doesn’t give such an option, or at least I didn’t find it.”
Brent Ozar: You know how you would do it? So the answer’s no, I mean you know, but how would you do it? My first thought was I would count the allocations. So if you run sp_WhoIsActive Adam Machanic’s excellent free tool sp_WhoIsActive, it will show you tempdb allocations by session. You could read his code to see where e gets it from. I don’t – think it’s in exec requests or something, and then you could kill sessions that are using more than a certain amount of space. Man, that sounds like a bad idea, that sounds so bad because especially, what if they’re doing inserts, updates and deletes and they just happen to allocate some space as that’s running? That seems horrifically bad, but I love it.
Erik Darling: Yeah because, I mean you know, especially if, you know, if they need a temp table or they do a hash join or anything stored stuff in tempdb and you just start killing sessions because that happens. It’s like the user isn’t even doing anything bad on purpose and you’re just sitting there murdering them, murdering their queries. Brent’s video paused in like the least flattering face.
Brent Ozar: Potato. Wi-fi potato. This week’s Office Hours is brought to you by Disney. Disney’s wi-fi signal at the Swann hotel is top quality pigeons ferrying matchbooks full of TCP IP packets back and forth.
Erik Darling: In other words, it is barely brought to you by Disney.
Richie Rump: Just so you know … and a dolphin, are not owned by Disney.
Brent Ozar: They are now, they are now. Disney bought them.
Richie Rump: Disney did buy them, I don’t know why they haven’t torn them down yet then.
Brent Ozar: I don’t know, especially when you see this wi-fi signal.
Can I kick people out of SQL Server for being idle?
Erik Darling: Deborah asks another question. “Is there a way to set a timeout from the server side? We have developers on management studio, or another tool, who never seem to log off, they just don’t go away.” This developer herpes will not leave your server, will not go away.
Brent Ozar: Well the bigger question is, is that a problem? Because it’s kind of okay if people log in and never log off [crosstalk]… I used to have a Blackberry Enterprise server and it would leave connections open but they would have locks on them and so I would just go through and kill them every night at midnight. If the client is Blackberry Enterprise server and there’s locks open, just kill it. But that’s also because I didn’t really care whether my executives got my email or not, because really, the less they know, the better off that they are.
Erik Darling: If they don’t get the email that the server’s down, the server isn’t down; no one’s waking you up at four in the morning now. I would actually have that as a [crosstalk]. If the server goes down, kill the best connections so no one can get in [crosstalk].
Should I use filestream to host files in the database?
Erik Darling: Alright, let’s see here, let me think of a way to paraphrase this one. Ah yes, so John asks, he recently joined a company that’s using file stream to serve up files in SQL Server. He read that this is not a good idea, but he’s not familiar with other options and how you would serve files via SQL Server.
Brent Ozar: Well you know, you had to do this a lot in your last job, you know. You were dealing with tons of files and databases, what did the vendor end up doing there?
Erik Darling: Well storing blobs in the database is a pretty terrible idea. No one wants you editing PDFs and stored procedures, it’s not good. So usually what people will do is they will just start having pointers, file system pointers, in tables and then the application will use that pointer to open up a file as appropriate. So if you have, you know, a viewer of some sort, let’s just stick with PDFs and say you have a PDF viewer that works by hitting the database and looking at stuff, it will just follow the path to the PDF file that’s stored in the database. It will do some lookup there and rather than using file stream to, you know, pull the file up, it will just go to the file system where that path points to and grab the PDF from there.
Brent Ozar: Now, Richie, you’re working on projects these days that do a lot of files and file handling and we need to keep around for Paste the Plan. So how are we doing it for Paste the Plan?
Richie Rump: That’s it, that’s exactly how, we create some sort of pointer, tells us where it is and then the application goes and does whatever it needs to get to it. Now, our first cut of Paste the Plan actually stored all the files inside the database, then we decided that was a horrible, horrible idea, but it was a proof of concept, so it kind of worked for that. And that was one of the first things we removed and just put in where – the pointer to where it is. This is the path to where the file is, and then you can go and get it yourself.
Erik Darling: You finally read our own blog posts about getting blobs out of the database…
Richie Rump: I mean it’s – see the thing is it’s super simple, right [crosstalk] in this case we’re using DiNoDb, which is no SQL, it’s just super simple to get something up and running and shove it into a database. I don’t even have to convert it to binary stream or anything like that, you just dump it and it’s there.
Erik Darling: Magical.
Richie Rump: Yeah, but when you’re talking about – especially the cloud, now you’re talking about I got to pay for storage and I got to pay for transfer data, I got to pay for all this other stuff – just storing that kind of stuff just doesn’t make sense, so we store that in S3, a system designed to store files, and we just kind of pump it out of there.
Erik Darling: Cloud storage ain’t the fastest thing in the world.
Richie Rump: We don’t need it to be, so…
Erik Darling: But someone else might.
Richie Rump: Someone else might, you got to do your own testing on some of that stuff, but we don’t need it to be the fastest thing in the world.
How do I upload 2TB of data to Azure?
Erik Darling: Akright, Ben asks, “what is the most efficient way to transfer 2TB of data from on-prem to Azure Cloud IAAS?” Infrastructure… [crosstalk]
Richie Rump: Ah man, I asked Cecil this question yesterday because we – away from the podcast, away from the keyboard, we wanted to take all of our raw audio files and our project files and we wanted to put them in the cloud so I don’t have this hard drive which is sitting right here, always there, in case it falls or whatever. I can’t figure out a really great way. We were just talking about it, we don’t have a really great answer. I think there’s some command line tools that kind of help out with some of that stuff. There’s some .NET libs that help out with some retrial logic and all that other good stuff. But I don’t have a good answer on how to upload large amounts of data into the cloud.
Erik Darling: 2TB, man that’s…
Richie Rump: Now, AWS has a great way to do it. They have their – I think it’s called a Snowball, where they actually send you…
Erik Darling: That’s so cool.
Richie Rump: Yeah, a box, you plug it into your network, you put all the files over there, you send the box back and then they plug it into the cloud. So that is their, kind of, solution to – their sneaker-net solution to that.
Erik Darling: I miss sneaker-nets, token rings, all that fun stuff…
Richie Rump: And Brent quit, Brent quit – oh there he goes.
Erik Darling: Yeah, Brent is giving up on this one, sticking a fork in it. But that’s okay because so are we. It’s close enough to 12:45 and, well, I don’t know [crosstalk]
Richie Rump: because I wanted to know the answer to that, I want to be able to upload files pretty regularly to the cloud, I’m going to start trying some tools and trying to figure that out. The problem is that it’s complete – my upload speeds are trash and it just slows everything down here locally, so I got to do everything overnight.
Erik Darling: I can only imagine. But I know that there’s some, like, Data Migration Assistant, I think it’s called. Azure Data Migration, but I don’t know much that – I’ve never used it, so I couldn’t tell you that it’s like the greatest thing in the world. Anyway, that brings us to close enough to 12:45, thank you for joining us, thank you for, well, joining Brent, sort of, partial Brent, kind of.
Richie Rump: Brent from the future.
Erik Darling: Brief Brent. We will see you next week, hopefully with less technical difficulties. Goodbye.
