This week, Brent, Tara, and Richie discuss a simple way to fill tempdb, tools for retrieving data from transaction logs, tools for diagraming server architecture, their favorite counters for checking if SQL server on VMWare has sufficient memory, SSRS subscription issues, change requests, thread pool issues, best practices for SELECT * into, failover SQL Server vs failover SAN, backups, and more.

Here’s the video on YouTube:

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

Enjoy the Podcast?

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

Office Hours – 1-31-18

 

Brent Ozar: Brent asks – not me, but obviously a different Brent. “I was wondering if you know of a simple way to fill tempdb?”

Richie Rump: You’ve come to the right place with the right question.

Brent Ozar: Wow, these are questions we love. He says, “I’m testing out a monitoring tool and it never shows me more than 10% of space used, except I have queries saying that they’re out of space on tempdb.” That’s lovely. How would you all fill up tempdb if you were given that mission?

Tara Kizer: Doesn’t Erik have a script or something like that from the Dell DBA days?

Brent Ozar: Yes.

Tara Kizer: So whatever that does.

Richie Rump: Exactly.

Brent Ozar: What you do is you set up a table with like a char 8000 field, or a char 8000 field, depending on how you like to pronounce your data types. I’m – I hate char because I’m like, why would you want to burn your data? I’m a caring guy, so I say varchar, but a char 8000, not varchar, char 8000, which forces it to take up a whole page for every single row, and then you can insert a bunch of rows into that. Or if you want, just do select star from sys messages into tempdb, just keep naming it with different table names continuously, and you’ll fill up tempdb pretty quickly. Sys messages is a bunch of error messages in all kinds of different languages and it’s spectacularly large. Think Erik taught me that one too, I’d never seen that before. I’m like, “Where are you getting all this data from?”

Tara Kizer: One thing to note about the error, the error is coming when the users are running the queries, so by the time you go to check space, because they’ve run out of space, they have rolled back. So when you’re looking, it’s not going to be full anymore.

Brent Ozar: Richie, did you just pull up a different Rubik’s cube?

Richie Rump: No, but I do have another Rubik’s cube but it’s smaller.

Brent Ozar: My god.

Richie Rump: This is what I do during builds, by the way. Just waiting for stuff.

 

Brent Ozar:  James May says, and I don’t usually use last names, but James May I have to use because that’s awesome, and I’ve been watching Grand Tour. Says, “We are paying someone to back up our data warehouse to tape.” Richie does that with pen and paper too, his services are [inaudible]. He says, “They don’t think they also need database backups and they don’t test the tape backups unless we pay them. How would you communicate to the other stakeholders that this is really bad?” Well, how would you guys do that? How would you guys approach it?

Tara Kizer: Well, in my opinion, if you aren’t testing your backups and you don’t have access to those, these are not real backups. I don’t care what you’re paying another company to do, but I think that this is ridiculous. From a DBA standpoint, I would be sure to be doing SQL Server backups and then fine, move those files to tape, but I need the actual SQL Server backups because I don’t know that this other company is doing the backups correctly. Especially if they’re not going to test unless we pay them.

Brent Ozar:  I got to think they would give like a free periodic test too. You would think they would do some kind of part of their service agreement, one free test every 30 days.

Richie Rump: I’m thinking you could do something creative. I’m not thinking anything on top of my head, but have like, everybody in the room sign their name on a piece of paper and bring it back in, and then you know, mark one with the X and then send it back out and like, hey, that one’s corrupted, you just don’t know of it yet. Or have them guess like, which name it has the corruption.

Brent Ozar:  You can tell Richie’s been watching minute magic videos. It’s like, he’s putting the piece of paper underneath cups and moving them around, now guess where your data files are.

Richie Rump: David Blaine for life.

 

Brent Ozar:  Paul Olsen says, “If the view definition is defined with read uncommitted isolation level” – I’m guessing he means with no lock hints on the queries, “But a query uses read committed isolation calling the view, does that affect the lock hints and the query plan versus a query calling the view with the read uncommitted?”

Tara Kizer: In a view, can’t you set the isolation level to read uncommitted? Or would that – the wrong syntax or?

Brent Ozar:  I don’t know. I know you can use with no lock, but I don’t know if you can set the isolation level for the whole view. I know that when – if you change the whole databases to fault isolation level to RCSI, read committed snapshot isolation, if you use with no lock, you still get dirty reads. Like, that hint overrides it. So that’s my guess is that the hint will still override it.

Tara Kizer: Read uncommitted is considered – well, over right read recommitted, but only for the view. If you’re calling the view in an outer query, the rest of the stuff should be read committed.

Brent Ozar:  Oh man, so of course, I really want to do this. So let’s fire up – I got SSMS here, why don’t we? Hey, come on, let’s do it, let’s do it live. Where’s my SSMS? Alright, so I’m going to fire open SQL Server Management Studio. Why not? We’ll all learn together. And then we will go create a table, create a view on top of that, so let’s go create a query. Pop into the Stack Overflow database, use Stack Overflow, go create table, dbo.webcastattendees, ID int, identity 11, primary key cluster, which I probably don’t have to do here, but I’m there. Attendee name, varchar 50, because I’m lazy, and I can’t type anything correctly today. Insert into dbo.webcastattendees, attendee name, values, Tara Kizer, man, what a mess I am at typing today. Boom boom, Richie Rump, and James May. Alright, so we got those folks in there, now let’s do create a view, create view, dbo.view, Jeremiah told me to never use VW, as SELECT * from dbo.webcastattendees with no lock. Let’s go execute that guy. So then I’m going to begin tran update dbo.webcastattendees, set, attendee name equals- we’ll set everybody to Jane Doe, and we won’t do a commit, so we’ve got a transaction hanging open there. Now, over in another window, I’m going to say select star from dbo.view, like I’m ever going to be able to retype that with your name again. Got to go back and copy paste that. There we go. Execute, and it works. Alright, so that’s kind of cool.

So with no lock in the view is overriding my default isolation level, so if I roll this back, roll back, and then I’m going to go back and alter the view, create or alter view, and take – you can’t create alter the view. Are you kidding me? How ghetto is that? Man, I love the create or alter command. It’s so awesome. It works. Just Intellisense doesn’t get it. Okay cool, that’s fine. So now let’s begin tran again, and update everybody to Jane Doe. Now let’s go back and select from the view, and it’s blocked. So yes, we now know that the no lock hints in the view for sure override whatever’s in your default isolation level. Alright, don’t we all feel smarter now? I feel smarter. And I guarantee you, I’m never going to use that again in my life. Never. Not going to happen. Just like all of my high school experience.

 

Sree asks, “Are there any tools to retrieve the data from transaction logs for stuff like deleted records and drop tables? I usually keep 24 hours of logs and full backups for three days. Is there any free way to read the transaction log?

Tara Kizer: Quest – it’s not free, but Quest has the log reader available in their – what’s the name of their tool?

Brent Ozar:  Toad and Lightspeed.

Tara Kizer: Lightspeed. Lightspeed, yes.

Brent Ozar:  It used to work with native backups. Now it only works with Lightspeed backups. But you have to be using it – yes. That was recent because I just went to go do a demo with it, and I was like, “Wait, what happened here?” There’s FM dump db log too. Like, the list of undocumented commands to use it as a giant pain in the rear. It works, it’s just slow is all get out, and what it doesn’t show you, what I bet you want Sree, is you want to see who did the delete. You want to see like, what their login name was, what the delete command is, and it doesn’t give you all that reconstruction quickly.

Tara Kizer: And if you just want to retrieve the data, just do a side by side restore and do point in time recovery. You have the backups, so as long as you know what time – you know, around what time it happened, you’ll be able to do point in time recovery. And do it side by side so you don’t have downtime associated with the restore. But you can read. Make sure you put it into the mode where you can still query the database as you’re doing the restores. That way you can keep applying and not have to start over with the full backup restore.

Brent Ozar:  With standby.

Tara Kizer: That’s it.

 

Brent Ozar:  Marcy asks – and hi Marcy, haven’t seen you in a while. “Hi, I’m not a stupid person” – Marcy’s not a stupid person. “But Viseo really challenges me” – really, Viseo challenges me as well. She says, “Do you guys have a tool you like to diagram server architectures?” Richie, there’s one you use.

Richie Rump: So I guess there’s a couple things like, server architectures meaning the actual server, like, how many servers do I have in that – I think Kendal had a tool to do all that. It was a PowerShell SQL doc or something like that.

Brent Ozar:  No, but the diagraming, what’s that 3D architecture…

Richie Rump: That’s the other part of it. That’s data architecture, not server architecture, right? So we get deep here because there’s people who’s jobs that all they do is they do data modeling, and there’s a couple tools that you could try, all of which are very expensive. I’m so very sorry. But ERwin, is like the granddaddy of them all that does all this stuff. My favorite, which is

ER Studio, that is – was purchased by Idera recently I believe, and then folks really like PowerDesigner, and I think SAP bought them a few years back. Those are the big three if you want to do some heavy duty data modeling, and it literally, it does everything that you want to do. You could create trigger templates and do all this other cool stuff with it, and every single one of them – those three are phenomenal.

If you’re more budget conscious, I’ve used Toad Data Modeler, and that is free for up to 25 objects, so if you want to do something small or just kind of try it out, that’s another tool that’s out there, and there’s a bunch. There’s a whole Wikipedia article on data modeling tools, and your head will just spin with all these data modeling tools that are out there. But those are the big three and then I’ve also used Toad Data Modeler as well.

Brent Ozar:  And if you – I want to say it was you that turned me on to this Cloudcraft too. If you want just diagrams of where servers are and web servers, app server storage, this thing is fantastic. And then the other one I draw IO, I can’t remember if I saw that one or not. Yes, no, I haven’t seen that. As soon as it opens JavaScript, I’m like – which I love JavaScript, don’t get me wrong, but as soon as any kind of thing pops up – no, Cloudcraft was the big one there.

Richie Rump: Yes, Cloudcraft is what I use for all my AWS kind of mapping stuff and diagraming.

Brent Ozar:  Beautiful. They’re gorgeous.

 

Brent Ozar:  Let’s see, Rick says, “Have you ever tried Oracle SQL data modeler?” No, we don’t pay Richie enough to use Oracle tools. It’s not…

Richie Rump: They pay me at post crest levels. That’s what they pay me right now. My SQL.

Brent Ozar:  Rick says, “Oracle data modeler is free.” Why do I have such a hard time believing that?

Riche Rump: [crosstalk] Oracle?

Brent Ozar:  Yes, or taking the blood of your children, or – and I love Oracle, don’t get me wrong. They’re wonderful, nice sailboats, nice people.

Richie Rump: Great jackets.

Brent Ozar:  Great jackets, which is a miracle I’m not wearing one of those right now.

 

Brent Ozar:  Tammy says, “What are your favorite counters for checking if SQL Server on VMware has sufficient memory? We had our infrastructure team add more memory to a server without asking” – what? You should hug them – “when a customer complained that an application was performing poorly. It made it look like the DBA team wasn’t doing its job. We use a monitoring tool to monitor servers and we never saw an issue.” Bring them bourbon. Hug them. That’s great. That’s fantastic. They are in your corner, that’s good. It could be worse, they could be taking it away.

Tara Kizer: Does your monitoring tool show you the wait stats? I believe SolarWinds does. Yes, so whether or not you can read it is another story, but see what it shows in the past. Was there memory pressure? The server team maybe added more memory because it looked like the server had low memory, but if SQL Server’s configured properly, it’s always going to be high memory utilization from the server level. I mean, they’ll just have to keep adding memory if that’s the metric that they’re looking at. I would look into SQL Server to see if it was under memory pressure. Just approve that you guys were doing your job and the due diligence if that’s what you’re after.

Richie Rump: But wait, we could use that scenario, right? So maybe you’re under provisioned a little bit and so you just keep bumping up the memory and they throw memory in and then you just bump it up and you just keep – I don’t know what’s going on, you just give me more memory.

Brent Ozar: Yes, I used to – I was so horrible. I would know from overhearing that one of my sysadmins was going to look at VMware utilization on a certain date to like, do utilization checks, and I would load up CPU benchmark tools on a bunch of VMs. It’s looking pretty underpowered – terrible person.

 

Brent Ozar:  Thomas asks, “I’m having an issue with subscriptions with SSRS.” Uh oh, well, I’ll keep reading, even though we’re all incompetent on that. He says, “I’m able to log in to report manager, when I open it as an admin, I select the report, it runs fine. However, when I try to connect to subscription, I get an error saying that the credentials aren’t stored. When I update the stored credentials, the report won’t run. I’m using Windows authentication.” None of us know SSRS. I should have read deeper into that question first.

Tara Kizer: I’ve used quite a bit of SSRS but never the subscriptions anyway, it was always on demand stuff, so yes, I couldn’t answer it.

Richie Rump: I use SSRS in beta, back in like, 2000. Does that count?

Tara Kizer: My first interaction…

Richie Rump: Before 2000, it wasn’t in 2000, but…

Tara Kizer: Yes … 12, 13 years ago.

Brent Ozar:  Wow, wow.

Richie Rump: And I was hacking the hell out of it because they were getting all the reports in XML and we were just taking that and just throwing it on the web and taking pieces out we want and yes, those were the bad old days.

Brent Ozar:  So I’m going to – never mind. I was going to say I’m going to have you go to work on that when we get off the call and just publish some of our stuff in reporting services on the web. Can’t be that hard, said you’ve done it before.

Richie Rump: I have better tools now Brent, please.

 

Brent Ozar: Thomas asks a follow-up. He says, “What do you guys use for automated reporting?” We don’t. We don’t do reporting anymore. And it’s not that we’ve got anything against reporting. Like, I’m of a belief that people should do reporting; we just don’t specialize in it. We’re super specialized in one thing; incompetence. We’re really, really good at incompetence, and answering questions for free. Actually, not even answering questions for free, because you see by these answers, we don’t even have the answer to that question.

 

Brent Ozar: James says, “After running sp_Blitz, I’ve got 1000 things to do, but my boss wants change control requests for each one. How do I talk him out of that?”

Tara Kizer: I mean, as a production DBA, I hate having to do change requests. I just want to do the work and, you know nothing to track. I just don’t want to do all that paperwork stuff. But, from the company’s perspective, I think it’s a good thing to have, that we can go back in time and see if a performance issue can correlate to certain dates, and just having accountability in production. I don’t think I would talk him out of that; I would just go ahead and submit the change requests for each of the items. I don’t think that there’s going to be that many is sp_Blitz that you need to take action on for it to be so cumbersome to fill out these requests.

Brent Ozar: I would also, too, just make sure that you lay out pieces of paper or Word docs or whatever on your desk as you’re doing it, so when people come in you go, “I’m sorry, I’m busy with change requests.” Make it real clear… And you print them out and you bring them to his office when you’re done, “Alright, here, I’ve been working on these the last two days; here you go.”

 

Brent Ozar: Mike says, “Is it possible to run out of CPU threads and yet not be at 100% CPU?” Tara wrote a blog post about this, actually.

Tara Kizer: Yeah, what was the name of it?

Brent Ozar: Network Issues or Thread Pool…

Tara Kizer: Yeah, so check out the blog post. I think it’s from two weeks ago. Just search on thread pool if you can’t find it. When you have thread pool waits, usually the ones that I’ve seen on client’s systems, or just from us demoing it, CPU utilization is going to be very low when you have run out of worker threads because all these worker threads are sitting there doing nothing but waiting on the blocking to clear. So they’re not even doing anything. They’re not even using CPU, so you probably will see even low CPU utilization. Unless the top blocker is doing a scan of a large table, then that – you might see high CPU utilization, but oftentimes you will see low CPU utilization when you’ve run out of worker threads.

 

Brent Ozar: Wes says, “I’m using a customer supplied query that does a select into; all of the things in the from statement have fully qualified names in a derived table. I’ve seen conflicting views on the columns being listed in the…” I’m not even sure what you’re asking. Try rewording that and see what you mean here.

 

Brent Ozar: Tom says, “When I try to run CHECKDB, my database is 1.6TB. I get an error about being out of space on tempdb. Tempdb is 50GB…” Well, yeah, that kind of makes sense…

Tara Kizer: Can’t you do a snapshot on the drive where you have space and it will use that instead of tempdb – something like that?

Brent Ozar: It does, but then there’s gotchas with, like, when memory grant is too large and has to end up doing some spills to disk. It ends up spilling to tempdb. I would just change to doing physical only. So, if you do CHECKDB with physical only, it’s not as good as real CHECKDB, but it’s just a start and doesn’t require all the tempdb data space.

Tara Kizer: Or, do the Paul Randall approach where you break things up and you do a little bit of work each day. So you do some tables, you do CHECKTABLE, CHECKALLOC, and whatever the other one is; I think there’s three of them. And then you just do a little bit of work per day, and then you won’t need as much space.

Brent Ozar: That’s my personal philosophy as well; only a little bit of work each day. Don’t need as much space.

 

Brent Ozar: Wes says – he follows up with, “What are the best practices for SELECT* into?” Oh, SELECT* into…

Richie Rump: I deciphered his code in his original question.

Brent Ozar: Oh, no, no so the problem with SELECT* into is that as people add new columns, you’re going to get those in your code whether you want them or not, and some yoyo is going to add a JSON or an XML column or a varbinary max. The idiot will be Richie – he’s going to be doing it on purpose to break your code, so I don’t really blame him. I kind of like him for that.

Tara Kizer: Yeah, just definitely put the columns that you want on there to avoid memory grant issues.

Richie Rump: Yeah, the way I usually work with SELECT* is if I’m doing ad hoc and I’m just querying the database for just getting information, like on my own. I’ll use SELECT* all day long. The moment it needs to go and be checked into anything, all the columns need to be there.

 

Brent Ozar: Tammy says, on her follow up for the VM team who gave her memory as a Christmas gift without looking but they were trying to pull her pants down and make her look stupid, she says, the follow-up, “They were looking at page life expectancy and page reads.” Yeah…

Tara Kizer: They might have been doing the wrong thing there, because page life expectancy is going to plummet any time you do, you know, index maintenance. So maybe you’re doing index maintenance, and that’s when they looked, and the morning after that – because it is going to be low, that does not mean you have memory pressure.

Brent Ozar: And it could be, like, that they just looked at something – there’s a lot of old posts out there that say if it’s below 300 then you have a problem. If your storage is fast enough, you could even have a low page life expectancy and not be waiting on the storage. I like you – you mention that you’re using SolarWinds. I would keep using SolarWinds and wait stats tracking over those two counters. Those two counters aren’t very good. I’d just say, “Oh, sysadmins, you’re right. Oh yeah, you keep an eye on those numbers for me and if you see them low just keep the memory coming.”

 

Brent Ozar: James asks, “If you had to choose between a failover SQL Server versus a failover SAN, which would you go with?”

Tara Kizer: I don’t like having to make the decision, personally. It means that the company isn’t going to be investing much money into high availability, which is extremely important to a business. So how robust is the SAN – I mean, how often does the SAN fail compared to, say, a Windows crash or a SQL Server crash or something like that. I would want a failover server, if I had to choose between that and a failover SAN. I have a feeling, though, that that choice isn’t being made, because a failover SAN is going to be a lot more expensive than a failover server. So do you really – I don’t know. Failover server for me…

Brent Ozar: Or you were talking about a really crappy SAN; like a $1000 SAN or something.

Tara Kizer: I don’t want that anyway.

Brent Ozar: Those NAS toasters…

 

Brent Ozar: Paul says, “Can you actually see the objects from SQL Servers that are going to disk when a server is paging? I want to know which stored procedures were pushed to the page file.” Oh, that’s tricky.

Tara Kizer: sp_whoisactive, right? If you’re logging that to a table, look for the ones that have page I/O latch [inaudible] waits.

Brent Ozar: Well, but that’s just reading pages from a disk. I think he wants to know when the whole thing swaps to disk – like the C drive… Your face is…

Tara Kizer: Does that even happen anymore? I always get the sense that everyone doesn’t think that the page file is a big deal these days with SQL Server.

Brent Ozar: I’ll specifically set the – in our setup checklist, I’m like, “Set the page file at zero, or whatever is required to do a mini dump, and that’s it.” So it’s like, depending on the version of Windows, it’s like 100MB or 2GB. But no, usually too, if you go to disk, it’s not because of your stored procedures. It’s because some other process piped up and wanted RAM on the SQL Server, and SQL Server can’t see what it was.

Now, I see it when people do SSRS analysis services and integration services all on the same box, like some kind of clown car. And then at the exact moment that integration services fires up and it’s trying to repopulate a cube by querying the database, all three of these things want memory at the same time, and you know, you can only fit so much poop in a five-pound bag.

 

Brent Ozar: James says, How would you back up a database that is permanently in standby or read-only?” You can back up a read-only database; you can’t do it in standby though. Assuming it’s like a mirrored secondary, you can’t touch that. But I’d back up the primary. But you can back up read-only ones.

Tara Kizer: Yeah.

Brent Ozar: Then a bigger question would be should you backup read-only databases. Michael, some yo-yo is going to flip it on, like flip it on and make it writable and do something with it and flip it back, and then I’m the one who’s going to get fired for it. So I’m like, if it’s production, I’m going to back it up. It may not be every day, maybe once a week or once a month or something.

 

Brent Ozar: Rob says, drumroll, “I will soon install four named instances on a three-node Always On Availability Group cluster. No failover clustered instances, just Always On Availability Groups. You have to run the SQL install on the primary, then the same install on the secondaries, using all the same drive letters. Sound about right?”

Tara Kizer: No, you just, you know, at the time of restore when you’re prepping the date of the secondaries, you just use the WITH MOVE command, and it will work form there; no?

Brent Ozar: No, no, no, as soon as some yo-yo goes and adds a data file or a log file, you’re screwed, because if the path doesn’t exist on all of the instances, immediately Availability Groups break. So you can fix it by running a subsequent restore WITH MOVE or a backup restore WITH MOVE type command…

Tara Kizer: So you can do it until somebody adds a file.

Brent Ozar: Yeah, yoyos. Like, in terms of which one you install on, you can do them all simultaneously, if you wanted to; that’s totally okay.

Tara Kizer: Who’s adding files after the fact anyway? How often does that happen? It’s very, very rare. So you could just do that in a maintenance window, add it and then restore the secondaries again.

Brent Ozar: Well if you’re building them from scratch, though, just make them all identical and…

Tara Kizer: I’d want them identical, definitely.

 

Brent Ozar: Last one, Sreejith asks, “We’re performing benchmark testing for SQL Server on Linux and Windows. We did every effort to keep everything the same, but the Linux transactions per second and response time doesn’t keep up with Windows. We suspect Linux as a possible culprit. What should we look out for on Linux?” You should probably look out for Linux.

Tara Kizer: Nobody’s using this in production, right?

Brent Ozar: Nobody – we offered a Linux class temporarily, and we had to close it because nobody wanted to go. There are people talking about using SQL Server on Linux, I just haven’t seen anybody using it to solve a production problem yet. I’d open a support call with Microsoft.

Richie Rump: Yeah. But take into consideration, how much development time have we had on SQL Server on Linux? A couple of years at the best? And how many we had on Windows? 30? Pushing 30? So yes, it’s going to be slower because performance tweaks are the last things that we do in software development. It’s sad but true, but that’s kind of what happens. So I expect it to be slow before it comes out. I’d expect it to be a lot slower before V2 or V3.

Brent Ozar: And there are people at Microsoft who are going, “Hey look, this is going to be faster because memory management is slightly different. And like, I don’t know that I would bet on that horse. I mean, there’s a lot going on with performance tuning, above and beyond that.

Richie Rump: And I hope they’re right, but I don’t think it’s going to happen. But I’ve been wrong before. I didn’t think the Cubs would win the World Series, so…

Brent Ozar: Before? You’ve been wrong today.

Richie Rump: I’ve been wrong a lot today.

Brent Ozar: Oh, the fun of testing. Well, we are going to go back to testing our software, or breaking our software; whatever. We will see y’all next week at Office Hours. Adios, everybody.