This week, Brent, Erik, Tara, and Richie discuss sp_Blitz capabilities, what’s best to use for resolving SQL performance issues, CDC, tools for capturing wait stats, their favorite monitoring tools, temp tables vs table variables, partitioning with constraints, and whether you should get any certifications.

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 – 2017-01-18

 

How should I prepare for the MCSE exam?

Brent Ozar: Wes Crockett says, “Hi all. It’s my first time here. Really pumped that you guys do these events. I’m recently back in a DBA role…” Back in the saddle again. “…after a few years out of the DBA world.” Now I’m more curious about what you went and did and what you came back for because it’s just interesting.

Richie Rump: [Inaudible] thing didn’t work out for you buddy.

Brent Ozar: Yeah, because he went to go to a developer and realized that the frameworks change every like six weeks. He’s like, man, give me some ANSI SQL. I’m not too bright.

Erik Darling: Real programming sucks.

Richie Rump: Stop moving my cheese dammit.

Brent Ozar: He says his new employer wants him to obtain an MCSE. “Do you guys have a recommended approach to getting there? What books do you think would be valuable, self-study, whatever?” We should all tell our last time that we got a cert and then how we studied for it. Going from left to right, Richie Rump, when was the last time you got a cert and how did you study for it?

Richie Rump: I think I took the 2008 test, the SQL developer stuff. I think I took that. I think that was the last time I took that. Before that, I think it was the PMP that I took. But the SQL stuff, I just actually just took it. I didn’t study, didn’t do anything. I think I may have read one thing on XML and that was it and just went in and took it.

Brent Ozar: How about you, Erik?

Erik Darling: The last certification I got was an A+ cert in 1998 or 1999. I got it by putting together some computers—that was my studying. I basically plugged stuff in and wrestled with interrupt requests. Then I had an A+ cert. I could put CD drives in, and CD burners were super popular.

Brent Ozar: They were. Is this the reason why you just built your new desktop? Are you trying to renew that, are you trying to get you’re A+ renewed?

Erik Darling: Yes, I’m trying to get back on the A+ fast track. I need to get back in their good graces because my career is at a little bit of a standstill right now.

Richie Rump: You know, Erik, I hear the cloud has computers now. Maybe you want to look into that.

Erik Darling: It does, but they’re super slow. They are just the slowest darn things.

Brent Ozar: Tara, how about you?

Tara Kizer: I don’t have any certifications.

Erik Darling: Oh, yeah.

Tara Kizer: I once or twice took a practice test for the MCSE and I failed miserably. I mean, not failed miserably, but I failed. I think I got like a 60 percent on it. I’m just a terrible test taker. It would be hard work for me to pass these things and I already had an established career when I was doing this so I didn’t see any need to do this. So unless your company is requiring it or maybe the next job is going to require it, I really don’t see the need to get certifications.

Erik Darling: Yeah, I’d much rather just get books that cover topics that I’m interested in, or books that cover topics that actually apply to my job. For instance, this thing is constantly on my desk.

Brent Ozar: Calvin and Hobbes?

Erik Darling: Yes. I have every single Calvin and Hobbes book, every single one. One of them is signed.

Brent Ozar: Whoa—by Calvin or Hobbes?

Erik Darling: Both.

Brent Ozar: Oh, wow, dang. Yeah, I tried, I can’t remember if it was 2012 or 2014, which one it was that I was like, “Let me go see how they are now.” Fail. I’m like, wow. Because I’m reading the questions and I’m like I know some of these are wrong—are just completely and utterly wrong. So, yeah, not big fans of certs. Lee Townsend says he ran into way too many paper tigers with certs who couldn’t actually do the job. Kelly says, “Good answer, Tara. I agree. I don’t hire folks based on certifications. I prefer experience.”

Erik Darling: What sucks is that like because it’s a Microsoft exam, they really try to cram new features on you. It’s like your sitting there learning about Hekaton and change tracking and CDC and like all this other stuff that you know practically you are not going to use and you are going to have to apply in your job. That’s my other kind of gripe with them.

Brent Ozar: Every time I sit through one of them I’m like I don’t know what percentage of DBAs in the world have to do the things that are shown on this exam. I don’t understand what their job is like. That’s not a real—that doesn’t measure what I do. Wes says that when he did his intermission in his career he went over and did systems engineering, QA automation, and data analytics. I would also be curious as to what brought you back to database administration.

Erik Darling: Money probably.

 

Will there be a Brent Ozar Unlimited certification?

Brent Ozar: Money, that’s probably true. Fiag says, “How about a project of a Brent certified DBA?” Not no, but hell no. We don’t like taking certifications. Writing those questions is even worse. God, it’s hard. I like doing interviews and just letting people talk about what they’re good at. If I ever did a certificate it would be something involved with doing an interview and going, yeah, she hardly drooled at all. Yeah, she’s good.

Erik Darling: We did kind of have fun, I think around about when I first started me and Kendra did those quizzes, it was like one on indexes and one on T-SQL. Those were fun because they were just like here’s ten questions, what do you think? No one got a badge or a smiley face or a gold star for taking it but it was just kind of, I don’t know, that was kind of fun because it was just a cutesy little thing. If it was like an actual cert I would run.

Brent Ozar: Wes says he came back for money for one reason but he also found a job in an industry he loves. I know, that always makes me feel like I’m less like working when I’m around another company of—we did the GroupBy conference and one of the presenters is a database administrator for, the way that he said it is the Formula 1 team with the red cars and the yellow prancing horses. I was like you lucky, diabolical…

 

Should I put SSRS on a separate server?

Brent Ozar: Tammy asks, “When we do SSRS installs, should we put that on a separate server? Right now some of our servers have everything on one. I don’t think that there are security concerns starting with 2014 and I kind of want them on the same server for licensing purposes but I don’t want to make a bad choice. We have VMs so I could easily add more servers if I want. Should I put SSRS on a separate box?”

Tara Kizer: I think of SSRS as a like a third-party product. Just because it says SQL Server in the name and it’s written by Microsoft, it’s totally different than SQL Server. Think of it as a third-party product. Would you install a third-party product that isn’t supporting the SQL Server, isn’t doing net backup, or supporting software for SQL Server? Would you put a third-party product on your database server? I wouldn’t. I want my SQL Server dedicated to the databases and nothing else except supporting software. I always put the reporting services service or any other services that I need on a separate box. Yeah, you’ve got additional licenses here but the best practice is to have a dedicated SQL Server. If you can support it on the VM and you can add resources, you’re not having a problem, sure, do it. But I’ve worked in corporate environments where we’re just very careful with how things are, how we’re designing things, and making sure that we have a dedicated SQL Server.

Erik Darling: If you’re licensing Enterprise, you have to license at the host and you can spin up as many guests as you want, but Standard Edition, then, yeah, you have to pay per guest. So I don’t know if that changes how you think about things.

 

Can sleeping queries cause blocking?

Brent Ozar: James says he ran sp_Blitz and he noticed that there was—and I’m guessing this is sp_BlitzFirst or BlitzWho. He noticed that there was a sleeping query with open transactions.

Erik Darling: That was me.

Tara Kizer: I logged in.

Brent Ozar: “Does that cause blocking and if so, how do I resolve it?”

Erik Darling: It could. If you do like BEGIN TRAN and you insert, update, delete and then you walk away, you’ll have a sleeping transaction that could cause blocking because you’re still holding onto those things, those little pages and locks and whatnot.

Tara Kizer: Run sp_lock and then the SPID number and see what resources are locked and if anyone else wants to get access to those same resources they’re going to be blocked.

Erik Darling: Another option is if you’re feeling Machanic-y, you can run sp_WhoIsActive and you can use the show sleeping SPIDs parameter of [inaudible]. Then that will show you anything that is asleep. It will show you what’s going on there. Also, I think we recently added that to BlitzWho. I don’t know if it’s gotten pushed out from the dev branch yet though.

Brent Ozar: Oh, that’s a good point.

Erik Darling: You can run BlitzWho if you’re feeling particularly attached to us. It shows sleeping SPIDs there as well.

Brent Ozar: I love that sp_WhoIsActive also has the get locks parameter. It will show you exactly what people have locks on, it’s fantastic.

 

Do service packs always cause reboots?

Brent Ozar: J.H. says, “When I apply a SQL Server service pack or hotfix on some machines it reboots, on some machines, it doesn’t. Is there a reason or way I can tell when it’s going to reboot and when it’s not?”

Tara Kizer: It’s just whether or not the files were locked. If the files were locked, it’s going to require a reboot in order for the installer to make changes to those files.

Brent Ozar: Yeah.

Brent Ozar: Robert has a big long list with a checklist, a question with a checklist. Your best bet there is definitely to post that on Stack Exchange. As you see the slides rolling past, this one right here, it says if you have a multi-paragraph question. The reason why I say that, because this isn’t really multiple paragraphs, you’ve laid it together pretty nicely, but I bet you the smart answerers over there are going to do a beautiful job of injecting more steps. I like where you’re going. It’s very good but just throw it out in dba.stackexchange. You’ll get even better details.

 

What’s better: Profiler or Extended Events?

Brent Ozar: Raul asks a question. “What is the best to use for resolving SQL performance issues?” He gets a little tricky there because he ties us down to only two things and I’m not sure I would use either of them. It’s either Profiler or Extended Events.

Erik Darling: You’ve got to be careful with those, buddy.

Brent Ozar: Yeah, why do you have to be careful?

Erik Darling: Observer overhead, sir.

Brent Ozar: Yes. Schrödinger’s Profiler. Yeah, so the more instrumentation, yeah, the slower your SQL Server is going to go, especially with—Erik has a great demo with functions, how scalar UDFs and multi-statement table valued functions as they’re running, when you turn on profiler or even set statistics IO—boom—just unbelievable overhead. So what would you recommend using instead? Anything we would recommend starting instead of profiler or extended events? Because it sounds like he is after queries.

Erik Darling: Sure, I would start with our tools because that’s the cheapest way. You can check out sp_BlitzCache and you check out sp_BlitzIndex and you can check out sp_Blitz. Those will give you a pretty good general idea of the state that your server is in. Then from there, if I really needed eyes on my server constantly, I would just by a mature monitoring product. I wouldn’t sit there trying to roll my own or sit there trying to get an extended events session right or hope that I can get profiler kicking and working. That’s just me though, I’m lazy.

Brent Ozar: So many jokes there about an immature monitoring product but I’m not going to go there. It’s like Beavis and Butt-Head watching your servers.

 

How do I protect the distribution database?

Brent Ozar: Tim Smith says he has a four-node SQL 2012 availability group. Two of the nodes are in DR, two of the nodes are in his primary data center. “What’s my best way to create a plan for the DR for our distribution database?” He’s put the distribution database in here too, which is that even supported in an AG?”

Tara Kizer: He does say two-node failover cluster instance so the distribution database is not on the four-node AG. He’s got the distribution database on the two node regular failover cluster instance. Because yes, you’re right, distribution database is not supported in availability groups so it has to go into a failover cluster instance. I’ve had environments which have the availability groups across, onto a disaster recovery site and we had replication involved. You’ll have the distribution database at the primary site and the distribution database at your DR site. When you do the failover, you’re going to want to run scripts to recreate replication. Once you do that, you’re going to point it to the distribution database on the other instance. So you’re going to run replication scripts to redirect it. I don’t have those scripts memorized but once you’re ready to do that failover, I think we dropped replication on the first, did the failover, and then added it back pointing it to the new location of the distribution database.

Brent Ozar: I made a face like it sounded horrible but as I’m thinking about it, I’m like, it’s disaster recovery. It’s not like you do this every day.

Tara Kizer: It’s the same process you would do if you moved the distribution database to another server at the primary site. Think about what those steps are.

Brent Ozar: Yeah.

 

Why does CDC hurt when I do this?

Brent Ozar: Adam says, “We have CDC set up using a secondary file group. On some databases, the file size eventually grows to match the size of the primary data file. What could be causing this?” Well, Adam, I think the answer is in your question: CDC. I don’t have any experience with CDC. Do any of you guys? No.

Tara Kizer: I’ve used change tracking but not CDC.

 

Where do you post BrentOzar.com jobs?

Brent Ozar: Michella asks, “Where do you post brentozar.com jobs?” They’re in the SQL Server agent. No, we’re not hiring now but when we do we post a blog post. Normally, we like snipe Tara out of nowhere.

Tara Kizer: Just out of the blue.

Brent Ozar: Then generally we post it out on the blog. We let people apply in public. We’re not just trying to get you to read the blog, it’s just the easiest way. There’s like 100,000 of you that read that thing. Most of you aren’t qualified. That’s okay. We’ll find one or two good ones and we’ll go from there.

 

What are good tools to capture SQL Server wait stats?

Brent Ozar: Cody says he recently attended a seminar talking about performance tuning with wait statistics. Something touched on but they didn’t go into depth with it was what good tools are out there to capture wait stats. When you guys are looking for something to capture wait stats, what do you think about using?

Erik Darling: Extended events. Just kidding.

Brent Ozar: Profiler?

Erik Darling: Again, I stick with our stuff. I’ll go with BlitzFirst, you run it, you can either do since startup and it will show you your wait stats since startup or you can do it for a 30 second or some other second sample and you can get wait stats over a sample of time. So it’s a really nice way to look at wait stats. We make it really easy to read them and figure out what’s going on with them so I think it’s a pretty good way. You can probably find a bajillion scripts out there on the internet that all look at waits stats though, if you don’t want to use our scripts, which I don’t know why you wouldn’t.

Brent Ozar: If you went back to work as a production database administrator and your boss said, because as part of the deal for having you go back to work as a DBA, he said you can have two things, whatever monitoring tool you want and unlimited soups, salads, and breadsticks. Which monitoring tool do you pick and why?
Erik Darling: Good question.

Brent Ozar: I know, right? I’m putting you on the spot.

Erik Darling: Me? I say the same thing every week. Every week I say I would go with SentryOne now Performance Advisor. That is my all-time favorite monitoring tool. I like the way it looks. I like that it has that dark theme so it doesn’t blind me when I’m staring at it at 4:00 in the morning. I just really like the way the charts and graphs don’t give you a bunch of goofy stuff that you can’t do anything about and you can highlight sections of the graphs and it will bring you to the SQL that was running at the time or bring it to the SQL that is currently running, you can view execution plans. It’s got plan explorer built into it so you can view execution plans right from your monitoring tool. You don’t have to do a bunch of other goofy stuff to get your plans somewhere else, which is a shortfall of many, especially web-based monitoring tools, is that you have to download the execution plan to something else.

Brent Ozar: Tara, how about you?

Tara Kizer: Which tool?

Brent Ozar: Yeah.

Tara Kizer: Yeah, I like SentryOne’s Performance Advisor but I’ve also used the Quest tools, Foglight and Spotlight. I think they’re all great tools, the ones that I’ve used.

Brent Ozar: I’d be like, any, just give me a tool, I don’t care which one.

Tara Kizer: Yeah, a tool.

 

What’s better: temp tables or table variables?

Brent Ozar: Dan says he’s having a great debate of temp tables versus table variables. He hates to beat a dead horse—good—then stop. No, seriously, this will be still a debate topic ten years from now. He says, “I believe it’s best to use temp tables instead of table variables but my team members disagree. Recompiles and stored procs always come up.” You can fix that, put your temp tables at the top. Put your temp tables at the top and they won’t necessarily recompile. For more details on that, go read Paul White’s temp table recompile. Google for temp table recompile…

Erik Darling: Oh, temp tables and stored procedures.

Brent Ozar: Yeah, oh my god, he’s got a great blog post, like four of them about plan reuse with those that are shocking. I really dig them.

 

How should I handle NoSQL databases?

Brent Ozar: Richie, Alexander wants to ask you, he says, “What do you think about companies that adopt different sorts of NOSQL? How do you handle it all? Like sometimes they want Cassandra, Mongo, whatever. So why are they picking different kinds of databases and what should you do about it as a DBA?”

Richie Rump: Well those different databases do usually one specific thing really well and they don’t do other things at all. If they do it at all, it’s done pretty poorly, so that’s why you’ll have—if I need something for really fast reads, Mongo is really good for that. But if I need some relational data and then you join between different tables and stuff like that, it kind of falls over a little bit on the reporting side. That’s why you’ll see a lot of, “Hey, I need this tool for this, this and this tool for that.” I know we talked about Elasticsearch, that’s a great tool if want to go and search large amounts of text and things like that. I’m not going to put my relational data in there because it just doesn’t make any sense to do that. So how do you keep up? Well, what do you need to know is really the question. If you need to know about a document database, go look for document databases and start playing with some tools. If you need to know about graph databases, go look at a graph database and go look at some tools. I don’t like to get into the details of it until I’m told this is what we’re going with or if I’m doing some sort of POC. Until then, I just want to keep a high level of understanding what’s going on with each tool and then, hey, we’re going to go with this, then I start going pretty deep in the product.

Brent Ozar: The other thing to keep in mind as you start to spelunk around with lots of multiple tools, some of these tools fold up. If you want to read an interesting thing, search for rethinkdb, why rethinkdb failed. It was a company that was building an open source product and they just folded up recently. It’s a really interesting story behind it, around how hard it is to build an open source tool and make it work, scale, make it make money.

Tara Kizer: What’s the name again?

Brent Ozar: Rethinkdb, all one word, all lowercase. Are you trying to think about something other than SQL Server?

Tara Kizer: You said it was an interesting story, I want to look at it.

Brent Ozar: It really is.

Richie Rump: They had great founders. They were really super smart. They had a great team behind them. They had all this open source stuff coming in all over the place and just this vibrant community and they just couldn’t make it work financially. They even had NASA. NASA was putting it in their stuff. So it’s not like people weren’t using it, just sometimes the financials just don’t work out that way.

Brent Ozar: It’s amazing to read the postmortem because so many people chime in in comments and like, “Yeah, I use rethinkdb all the time but I don’t pay them a dollar because everything is out there and it’s free and it works great.”

Richie Rump: Yeah, we interviewed the founders on Away from the Keyboard last year, and the community manager. We posted the community manager after they had folded and we were like, “Do you want to re-listen to this before we post this, just to make sure you’re good with it?”

Erik Darling: Were things looking sunnier for them then or were they kind of like, “No one is giving us money for this free thing.”

Richie Rump: No, everything was like the sky is the limit. I mean, you’re not going to talk about the bad stuff to a bunch of podcasters. I mean, why would you want to do that? “No, we’re hurting pretty good. Maybe we won’t be around very much longer.”

Erik Darling: If I were working for me, man, I would be out of here.

Brent Ozar: Oh, god. Don’t use our product.

 

Do I have to license SQL Server for TFS?

Brent Ozar: Paul asks, “I just got out of a meeting.” Well, thank god for that. “Where our uptime engineer told—” Up time engineer, what a great job title. I love it. That’s cool. Richie is our down time engineer. “He told everyone that you can use Developer Edition without paying for licensing because we use TFS for development. TFS is a business application and requires licensing.” Some of Microsoft’s licenses, like SharePoint is an example, will include one SQL Server as part of it. So what you want to do is read more about that product’s license. They’re usually production licenses. They’re usually not Developer either.

Richie Rump: I think TFS was Standard but I haven’t been in that game for years, so. But it’s usually just like you said, the one and that’s it.

Brent Ozar: And it’s not like you really want a big, bulky SQL Server for that either. You can live with a small one.

 

How do I protect Analysis Services?

Brent Ozar: M.M. says, “I’m going to setup log shipping as a disaster recovery for our production server.” That’s amazing, we’re doing sort of a weird version of that this week. “We have a separate cube server with SQL Server Analysis Services, do you know what’s required for DR for Analysis Services? We’re not using VMs and my databases are large.”

Erik Darling: I have no idea.

Tara Kizer: No idea. I’ve never even used SSAS.

Brent Ozar: Everything I know from Analysis Services is just from listening to the guys at Prodata talk. It’s like www.prodata.ie, they’re out of Ireland. Just awesome, wonderful, friendly people—Carmen and Bob. The basic idea would be you just stand up another analysis services box over there and when you failover you rebuild your cubes. No sense in keeping the cubes in sync across.

 

Can I do partition switching with foreign keys?

Brent Ozar: Richard says, “I want to use…” Richie likes this. Is it just that you like his name or you like his question?

Richie Rump: I like his name, that’s it. You’ve got a great name, buddy. It means powerful ruler and I’m pretty sure you are powerful in whatever you do.

Brent Ozar: His last name is Seltzer so his middle name must be Alka. “I want to use table switches in SQL Server partitioning with sliding windows.” I’m pretty sure I know Richard too, come to think of it. He wants to do partitions with sliding windows but apparently, he can’t do that if the table is apparent in a foreign key constraint. “Is there any way to do switches or fast clearing out of partitions without dropping and re-adding constraints?” Have you guys done partitioning with constraints?

Erik Darling: Not that kind.

Richie Rump: Yes, but we didn’t do sliding windows. We didn’t slide in the data.

Brent Ozar: Every time I had to do one of those projects I didn’t use foreign key integrity and it didn’t occur to me until just now. Like I never used parent child foreign key integrity. We trusted the ETL process to manage that and make sure it was clean and pure, which of course is a bad idea but you know, I had really smart, nice, trustworthy ETL guys who lived in a van down by the river.

 

Where are best practices for SSRS 2016?

Brent Ozar: Tammy says, “I have another SSRS question. I was looking for best practices for the more recent versions of reporting services and I couldn’t find anything recent. Can you recommend a resource?” No.

Erik Darling: Doug Lane.

Brent Ozar: Even that, not recent.

Erik Darling: Totally, yeah.

Brent Ozar: Do we even know anybody who is using…? We just had the GroupBy conference. Bert Wagner—if you go to groupby.org—Bert Wagner did a session on high performance SSRS and right at the beginning he’s like, “Look, I haven’t used SSRS in the last year or two. This is just general performance advice” because people were asking how is it different in 2016. He’s like, “I don’t know.” I don’t know anybody who is doing 2016 SSRS. Not that it’s bad I just don’t know anybody doing it.

Erik Darling: Tableau.

Richie Rump: In a tableau world…

 

When should we apply service packs and cumulative updates?

Brent Ozar: Interesting. Lee says, “Microsoft wants us to apply service packs and cumulative updates and security releases right away. I’m curious to your thoughts on that.” What did you guys do when you were database administrators or you were managing databases?

Erik Darling: You know, you’ve got to patch those dev and QA servers first, man. You can’t just stick that on prod. That’s my advice.

Brent Ozar: You let it bake for some period of time.

Erik Darling: Yeah. If you’re super cool and you have clusters or AGs then you can patch some secondaries first or some dev servers first and let that burn in and then flip around and do your other stuff. The right-away thing, cool for dev and QA and whatever UAT environment you have, not cool for prod because Microsoft updates will still break stuff. Something seems to go awry with just about every one of those SPs and CUs.

Brent Ozar: The best thing was breaking NOLOCKs. One of the CUs broke NOLOCK. If you had it in development, you would never know because it’s not like you’re worried about concurrency in a dev environment. People are just running queries and you expect locking to happen so even when you bake it in for a while it still becomes an ugly surprise.

 

I want to upgrade and change my cluster…

Brent Ozar: Niraj asks a question that I feel like I want to type with a latex glove. “I need to perform an in-place upgrade from SQL Server 2008 R2 and it’s a cluster. Want to upgrade the cluster to SQL 2012. By the way, I’d like to convert them to Always On.” I’m assuming he means Always On Availability Groups. “Any suggestions?”

Erik Darling: Don’t.

Tara Kizer: I am not a big fan of in-place upgrades in production. I’ve certainly done it in test environments to save us time but never, ever in production. Usually when I’m upgrading from one version to the next I’m also switching hardware, or at least to another server. But if you’re not going to be switching servers, just install a new instance, then do the configuration you want—although if you’re going to be moving from a cluster two node to form a failover cluster instance to availability groups, you’re obviously going to need to do a bunch of work on that server if you’re doing in place. You’re going to have remove the cluster, add it back, and then setup availability groups so I would not recommend doing that all on the production servers. You have no fallback plan.

Erik Darling: The other thing is that since you’re on that version of SQL Server, you’re probably on Windows 2008 R2. That’s just like, you know, a bucket of crap for a failover cluster. Windows Server 2012 and 2016 are much better and god help you if you try to do an in-place OS upgrade.

Tara Kizer: We did those in the test environment just to save time and we’re just like two thumbs up if it works.

Erik Darling: Terrifying.

 

How can I tell which tables are getting used?

Brent Ozar: Robert has a question I love because I used to struggle with this myself. Robert says, “I inherited a legacy system with a lot of dead code and tables. Sys.dm_exec_query_stats fails me when tracking down which objects are being used. What’s the best way to determine what is being used?” Oh, there’s so many ways to do this. I would probably push back and say, why? What are you going to do? Because best case scenario you drop some tables. Worst case scenario, you break somebody’s application.

Erik Darling: Yeah, especially the code. It’s not taking up space so it’s not hurting anything just sitting there. I wouldn’t go ahead and mess with it. If you really want to get into it then set up some sort of auditing or some other monitoring just to see what runs over a 30-day or 90-day period and then get rid of everything that doesn’t. But even then, it’s not really guaranteed that anything is going to change for the better. You get rid of stored procedures that never run, they seem pretty harmless to me, like fingernails.

Richie Rump: Yeah, there’s a development philosophy where you want to delete as much code as possible. So early and often you want to delete code, so you’re always looking to delete code, delete code, delete code so that when it does go out, you’re not having all this commented-out code and/or code just doesn’t run anymore. Typically, I do a lot of that. I’ll go ahead and I’ll write a bunch of stuff—oh, that didn’t work at all. So you go in and you make sure it works and then you do a check in and deleting all the code. Typically, that’s how that would work, but a lot of people, they don’t do that. They just kind of just say, “We moved something and then the old stuff remains.”

Erik Darling: For tables though, one thing that I used to do whenever I inherited a server, I would look for table names that have like BK or back or a date on them, because that’s usually someone like saying, okay, I’m going to do something stupid over here. Let me backup the table as is and then if I need it again, you know. Because like especially when I was doing the ediscovery thing, you would sometimes have to make changes to the relativity’s configuration table, which is just a big long table with like a name and a value and then like a SQL variant column to put in your configuration. There were times when you had to make changes and you didn’t want to mess anything up so I would always select everything over into a table with like table name back with a date so I would know which table is which. So that’s one thing that you can look for sometimes, there’s a pattern that pops up where people make backup tables that are safe to ditch after a while.

Brent Ozar: He follows up and he says his biggest problem isn’t space but Visual Studio DACPAC deploy times. Yeah, it’s not—DACPAC isn’t known for speed and efficiency of deployment. That’s your bigger problem there. If you want to performance tune that process, I’d actually look at switching processes. Because even if you have a bare minimum of tables, as your data file grows, you can continue to be screwed because sometimes the DACPACs will simply in order to affect one alter table, they’ll create a new table and shove all the data into it then drop the old table. That can break all kinds of things, replication, security setups that you’ve done, so I’d want to be careful about that. Thanks, everybody for hanging out with us this week. We will see you guys next week in Office Hours. Adios.