This week, Brent, Richie, Tara, and Erik discuss enhancing queries, database corruption, availability groups, parameter sniffing, sending mail via SQL server, whether SQL 2016 is solid or not, other versions of SQL, physical servers vs virtual servers, and much 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 Webcast – 2016-09-21

 

Is there a way to improve a query without editing it?

Brent Ozar: J.H. says, “Is there any way to enhance a query without altering it? Like adding query hints or rewriting SQL when the optimizer is using parameter sniffing? Like I want it to go parallel when it needs to. My developer is complaining that his query is running slow and he doesn’t want to change his query or code.” How would we go about doing that?

Erik Darling: Are you properly indexed for this query is my first question? One way that parameter sniffing is kind of aided is if you have the right indexes in place so it’s not making constant bad query plans for your query. You have a pretty set one that’s pretty decent.

Brent Ozar: I like that. What else? There’s a technique Tara uses. She’s said it several times in webcasts. I’m always like, “Yes! Yes!”

Tara Kizer: A plan guide.

Brent Ozar: Yes!

Tara Kizer: A plan guide is similar to adding index hints directly in the query. It just allows you to have the object outside of the stored procedure code, if it is a stored procedure. Then you can add a plan guide to a query that you don’t even have access to change that’s hidden in an application. It mentions not going parallel when usually it does go parallel, I would look at the query cost and if the query cost is lower than your cost threshold for parallelism and you still want it to go parallel, then use the option MAXDOP. Add option MAXDOP to your query and compare the performance though.

Erik Darling: They don’t want to change the code so they’re kind of stuck not using hints.

Tara Kizer: I wonder if when they say they don’t want to change the code, maybe it’s a complex query and it’s going to take a long time, but just adding option MAXDOP, maybe that it is acceptable.

Erik Darling: Maybe.

Tara Kizer: You could still do a plan guide in that case. It supports those options.

Brent Ozar: Option MAXDOP won’t force it to go parallel, will it? If the cost is too low?

Erik Darling: No. There’s a trace flag.

Brent Ozar: What?

Erik Darling: There’s a trace flag. I always use it. Don’t you read anything I write?

[Laughter]

There a cool trace flag: 8649. If you want to use it as a query hint it’s option QUERYTRACEON, all one word, 8649. What it effectively does is drops cost threshold for parallelism for that query to zero. So it pretty much forces a parallel—well it’s a pretty good way to force a parallel plan if parallelism is possible for that plan. So like if you have other things going on in your query like scaler UDFs or whatever else that are keeping your query from going parallel, it’s not going to overcome that because the optimizer still has those limitations. It will make the parallel plan look pretty cheap in comparison to the serial plan.

Tara Kizer: So what’s the issue with option MAXDOP?

Brent Ozar: I don’t think it forces parallelism if the query cost is too low for the cost threshold for parallelism it won’t go parallel. It just says if you do go parallel use up to this many cores.

Tara Kizer: Oh, kind of sucks then.

Brent Ozar: It totally sucks. We need an option MINDOP is what we really need. I want go at least this parallel, not that MAXDOP isn’t awesome. MAXDOP is awesome but MINDOP would be even cooler.

Erik Darling: Conor Cunningham is sending a team to your house right now to talk to you about MINDOP.

Richie Rump: I need to talk to this developer because if you want to go ahead and write queries and then shove them in the application and then don’t want to touch them, we need to have a little discussion. We need to have a little talk.

Brent Ozar: You want a job there, right? You want to be able to go to work there.

Richie Rump: Yeah, we’re going to have it out. Because the data changes over time and sometimes the queries need to change too, so if you’re not going to throw that into a stored procedure and if you’re going to want to take it into your own application yourself, you’re going to have to be willing to change those queries as it goes along too. There’s no magic bullet or anything that some DBA can just poof on it—well, sometimes—but you have to be willing to be able to change your own code as things change in the data.

Erik Darling: Or at least be open to hints.

Richie Rump: Yeah. That’s changing code, right? I mean, I’ve got to change something.

Brent Ozar: If you want to change the code you could even use Adam Machanic’s function make_parallel. He has this function that will force your query to go parallel by creating a big fake table. Totally horrific idea. Horrible idea. But, you know, that might entice the developer into changing the code because it’s just so cool.

Erik Darling: Then he also has all that CLR stuff from that other manhandling parallelism talk. He just makes something wink for like two milliseconds and then, I don’t know, it’s ridiculous.

Brent Ozar: It’s neat. It’s a CLR object that takes your query and then parallelize it and then combines all the results back in. He’s got some slick tricks there.

Erik Darling: I think the whole point of it is to make sure that you have even row distribution on your threads. It’s like, how do you even get inside there to do that? Maniac.

Richie Rump: He’s our mad scientist.

Erik Darling: I get to eat his brain someday.

Brent Ozar: And he’s so nice and humble about it too. He’s like, “Everyone should use this.” This is totally approachable. It’s safe for everyone.

 

How do I handle corruption in the model database?

Brent Ozar: Upendra asks, “We have a model database that gets corrupted.” Oh my goodness, this happens often? “Tempdb depends on the model database and thus the SQL Server instance is not coming up. Is there any way we can bring the instance online as quickly as possible without rebuilding the model database?”

Tara Kizer: Simple. Just copy in model MDF and LDF from another server that you have. While the service is stopped, replace those two files. It should come online but you need to figure out why it’s getting corrupted. You probably have an issue where other databases might start getting corrupted.

Brent Ozar: That’s terrifying.

Erik Darling: The first thing I would do is run DBCC CHECKDB on other stuff because who knows what else is happening.

Tara Kizer: Yeah, and check your event log. You might have some disk issues.

Brent Ozar: Model is so tiny relative to the rest of your databases. So if the model database is repeatedly corrupt when you start SQL Server, that’s where I get really worried. That’s terrifying. Where would you guys look to find corruption issues in term of if any events were happening, where else would you look other than the SQL Server error log? Any place else?

Tara Kizer: System log and event log. If it’s a cluster, maybe the cluster log as well.

Brent Ozar: God, it could be [inaudible 00:06:27].

Tara Kizer: But really, DBCC CHECKDB and see how bad it is.

 

How should I handle AG listeners with legacy applications?

Brent Ozar: Next up, Jon says, “We have a legacy PowerBuilder application. It connects using a High Availability Group Listener.” I don’t know where high availability group keeps coming in—I keep saying even, they call them “HAGs” I’m like, no, no, no, we don’t use that term. “Availability Group Listener and it doesn’t support the new parameter MultiSubnetFailover=True. Are there any ways that we can handle multiple subnet roundrobin for legacy connections?”

Tara Kizer: You can drop your, whatever it is, there’s a parameter in the cluster that you can drop to help with that I believe. I forget what it’s called, the IP…? I don’t remember what it’s called.

Brent Ozar: Register…

Tara Kizer: Yeah, the register one…

Brent Ozar: Yeah, register all IP…

Tara Kizer: That’s one of the recommendations by Microsoft. We didn’t use PowerBuilder but we had other legacy applications. That solution did not work for us but it is one that Microsoft says that could work for you. Another option is to change your connection timeout to a really high value so that it has time to check both or all IP addresses that are attached to the DNS record. That also did not work for us. So we tried a whole bunch of things and we ended up having to go with the standalone SQL instance name instead of using a listener which meant that on failover there would be an issue. So a DNS alias was used instead. That way the DNS would only need to be changed, rather than the connection string. I don’t know what change was made but at my last job where we had this issue, the Windows administrator ended up changing the DNS record so that it was referring to itself and didn’t have the two IP addresses, or something like that. They ended up doing something weird and it solved the issue. I just don’t remember what it was. It was DNS magic.

Erik Darling: I was talking to a client recently about CNAME aliases and they seemed to be more in favor of an ANAME. I didn’t know what that was because I’m not too network-y or DNS-y, but that might have been it. I don’t know.

Tara Kizer: Yeah, maybe.

Brent Ozar: I know there’s also magic that your Windows admins can do to make DNS resolution always point to the closest server. I don’t know what any of the technical terms of it are, but like yeah, it just automatically gets the nearest server to you. The problem of course is whenever your SQL Server failsover it is not going to return the secondary server first.

Erik Darling: Oh.

 

Are you guys getting away from Office Hours?

Brent Ozar: Fellow human says, “Due to the difficulty I had trying to figure out how to register Office Hours on your website, are you guys getting away from Office Hours every Wednesday? Manually searching for the link did not work. I could also be old, blind, and deaf.”

Tara Kizer: We also have that issue internally. We’ve had this issue. Like, “Where’s the link?”

Brent Ozar: Where the hell is this thing? If you go to… and I don’t even know where the heck this thing is. It may not be in the menu anymore. No, it probably isn’t. All right, I’ll make a note to myself. You’re not old, blind, and deaf. Note to self—add Office Hours to menu. No, we’re definitely not getting away from it. This is fun. I kind of like this. It’s a lot of fun. Plus, it’s nice that it’s just our chance to get together and laugh at your questions. Just kidding, just kidding. Totally taking your questions seriously.

 

Is SQL Server 2016 solid and ready for public use?

Brent Ozar: J.H. says, “Is SQL Server 2016 solid?”

Erik Darling: I don’t use it in production.

Tara Kizer: Yeah, we’re not production DBAs anymore.

Brent Ozar: Have we seen anybody using it in production?

Tara Kizer: Not as far as our clients go but as far blogs and the MVP mailing list. Clearly, some people are using 2016. Is it solid? I think it’s solid but you’re going to need to test whether it’s solid for you.

Erik Darling: My Developer Edition has been pretty solid so far. One thing I will point out is that Stack Overflow has been using it for a while. I’m pretty sure that if it were gelatinous in any way, Nick Craver would be letting us all know about it.

Richie Rump: [inaudible 00:10:57] in the morning, every morning.

Erik Darling: So I mean it’s probably all right. They released a CU for it. So you got some stuff solidified by that. Try it out with your workload. What do you have to lose? Developer Edition is free.

Brent Ozar: I don’t think we’ve heard any horror stories. I haven’t read anything where people said, “Oh, god, there’s all these problems.” Management Studio has been a hot mess off and on for a couple of releases. That thing has been buggy as all get out.

Richie Rump: Still.

Brent Ozar: They just released an update to it yesterday. Did you get the update yesterday?

Richie Rump: I haven’t installed it but there were a couple of people I saw on Twitter that were complaining about it still.

Erik Darling: See with Microsoft what you have to watch out for are the service packs. The service packs are always what bite you. RTM, rock solid, you know what you’re getting. Service pack one, boom.

Brent Ozar: So what problems went out there in 2012 and 2014 service pack 1?

Erik Darling: All the stuff that you emergency blogged about with breaking availability groups and the clustered index corruption and all that other fancy stuff that just went horribly, horribly wrong.

Brent Ozar: Yeah, it’s amazing. Everybody thinks service pack 1 is a safe release and that just hasn’t been the case lately.

 

Which SQL Server is most common in production today?

Brent Ozar: J.H. follows up with, “What version do you think is most in production right now?” This is actually kind of interesting.

Tara Kizer: There was a survey recently, wasn’t there? Maybe like six months ago? It was SQL Server 2008 R2, wasn’t it?

Brent Ozar: Yes. Go to spotlightessentials.com. Spotlightessentials.com is Dell’s totally free Spotlight. What’s interesting is they post a lot of the data out of there. So if you go to spotlightessentials.com and then you click on “Collective IQ” up at the top. Collective IQ has a lot of data that’s available to the public. The number one version out there right now? SQL 2008 R2 at 38 percent of the install base. Now, this is only the SQL Servers that people care to monitor with Spotlight Essentials, so there may be other servers that you don’t care to monitor. But that’s number one with a bullet. SQL 2012 is second place with 27 percent, then 2008 with 11 percent. 2016 is actually even lower than SQL Server 2000. It’s ringing in at zero percent right now whereas 2000 rings in at two percent, so that’s kind of scary.

Erik Darling: It’s worse than android fragmentation.

Brent Ozar: It’s worse than android fragmentation.

Erik Darling: [inaudible 00:13:32]

Brent Ozar: I know, right? The 2016 piece?

Erik Darling: Yeah, well, six-year-old software. 2008 R2 was 2010 so, you know. Your server software is now going into first grade and probably dressing itself.

Brent Ozar: Hurling on the floor of the minivan. I’m of the opinion for the most part, 2008 was really good. It serviced a lot of needs. People were pretty happy with it. It’s not terribly broken. The part that gets broken is if you want to do Always On Availability Groups, man, you really need to jump all the way to 2012 or 2014. That can be a big, scary jump for people. But those are awesome versions.

Erik Darling: I wouldn’t deploy a new availability group on 2012.

Brent Ozar: Why not?

Erik Darling: Well it’s still v1. You know? It’s the first availability group rollout. It doesn’t have all the cool new features and optimizations and other stuff. Like it doesn’t have all the extra stuff that 2014 added and 2016 added. If I were doing a brand new install—one thing that I would wonder about for the reason that people are on such old versions is like, are they just using vendor software and the vendor hasn’t okayed newer versions? Why are they stuck there? That’s the big question for me. Why are you stuck?

Brent Ozar: Yeah, the problem that I had with 2012 is whenever you lose the primary, the secondaries, the databases just disappear out of object explorer, so it’s hard to tell like for example, a query how far behind the replica is. Your [inaudible 00:15:11] reports fail. Then you have to go force one of them to be online or wait for automatic failover to happen. In 2014, even when the primary is down, your secondaries, you can still go query the databases. You can’t get there through the listener, you have to know which server name to connect to directly but at least the replica is there for you to run queries against. Another thing that’s interesting out of Spotlight Essentials over there, 59 percent of all SQL Servers being monitored are virtual as opposed to 41 percent are physical. I would read some of that as the people who would use a free monitoring tool are also probably the kinds of people who have virtual servers. Whereas the kinds of people who have big, beefy psychical SQL Servers might be more likely to use a paid monitoring tool.

 

How do I troubleshoot mail-sending code in SQL Server?

Brent Ozar: Eric asks, “I’ve been trying to troubleshoot a stored procedure that is sending mail.” Look, stop trying to send me mail, Eric, I don’t appreciate it. “Are there scenarios when SQL Server fails to log mail activity in either sysmail log or sysmail mail items? Because that is what I am seeing.” Have any of you guys used SQL Server to send mail and had to troubleshoot it afterwards?

Erik Darling: Yeah, tons.

Tara Kizer: Yeah, more for like DBA processes than an application process. I wonder if Eric has tried the sysmail_allitems. You might find data in there. I don’t know that I’ve ever seen it fail to log. I’ve definitely encountered issues with database mail. You could try restarting just database mail rather than the whole SQL Server service. Try sysmail_allitems.

Brent Ozar: I like that. The problem I always have with stored procedures sending mail is you’ve got to be really sharp about debugging that stuff. There was this one time when we tried to send out emails to customers and we had a little bit of a loop in the code. I woke up to over 50,000 emails in my inbox. I thought that was bad until I realized that everyone in the company had over 50,000 emails in their inboxes. We’d filled up the Exchange server’s drives.

Tara Kizer: We did that with SCOM. SCOM was sending out twelve emails per alert on a lot of our systems then we had some kind of massive outage. So hundreds of servers were suddenly sending emails. The Exchange admins were like, “What did you guys do?”

Brent Ozar: Denial of service attack against our email server.

 

Are there tools to help with a SQL Server to PostgreSQL migration?

Brent Ozar: Fellow human asks, “Hi all. We’re planning on migrating from Microsoft SQL Server to Postgres. Do you know of any good tools?”

Erik Darling: Excel.

[Laughter]

Tara Kizer: SSIS.

Brent Ozar: Yeah, SSIS has connectors to both. That’s like a big bang migration. It’s not like you gradually feed—it’s like you’re going to take a big outage and you’re going to push all the data across and then stand up on the other side. That’s kind of risky. I would be really interested in hearing why you’re migrating. What is it that you’re migrating for because we always find that kind of thing really interesting.

Erik Darling: Probably licensing costs.

Brent Ozar: That’s what he says.

Erik Darling: That’s the most common.

Brent Ozar: Reducing the project costs. So it’s kind of funny. From a tools perspective, what you seem to find is people who are migrating to a cheaper platform in order to save money, there don’t seem to be a lot of good tools there because tool vendors don’t want to make tools for people who don’t want to spend money. I’m certainly not saying Postgres is bad. Postgres is awesome. But your tools that you’ll be looking at will be open source rather than a lot of paid tools.

Erik Darling: PgAdmin is ugly.

Richie Rump: Yeah, oh, gosh.

Brent Ozar: Aqua Data Studio will let you query either SQL Server or Postgres but it’s just a querying tool and it’s ugly.

Richie Rump: Yeah, I think when you’re doing that kind of migration, you can do a big bang but you can do a thing in vertical chunks if you’re kind of moving the app along at the same time. To do the big bang, you’re going to want to do it over and over and over and over again. You’re going to want to test the heck out of it. I’m guaranteeing you, there’s going to be some data that’s going to be all screwed up and you’re going to need to go back and you’re going to fix it in the source system. Then you want to test it in the target system then. It’s just that continual loop of debugging your data as well as your process, your loading process.

Brent Ozar: You’ve got to make sure that the app, whenever you go move over to the new apps that you designed, that it has all the features that you want. Because what happens after go live day is two days in some user says, “I could have swore I used to have this feature. How come it’s not on the new side? Can I just use the old tool until you add the feature back in?” It’s like, “No, sorry, we cut the data over.”

Erik Darling: That and performance, right? You’ve got to make sure performance stands up on the other side as well. Postgres, I mean depending on what version you’re going to, it may or may not have any parallelism in it whatsoever and the parallelism that you do get in Postgres these days is not as complete as Microsoft SQL Server. Not saying that that’s a robust reason to stay, but that is one thing to consider.

Brent Ozar: They’re saying, “We’re facing issues on moving procedures over to functions and some data types are not supported.” Yeah, it’s really a code rewrite. Whenever you move from one database to another it’s going to be a code rewrite. You’re going to touch ever line of code.

Erik Darling: God, I was working with a client a few weeks ago and they were like, “We want to move everything to MySQL.” I was looking at their code and I was like, “See all those common table expressions? You’re going to have to rewrite all of those.”

Tara Kizer: And are you really saving money if you have to do a complete rewrite, if the application is already out there?

Brent Ozar: Yeah, that’s so rare.

 

Are physical servers better than virtuals for huge databases?

Brent Ozar: Upendra says, “Are physical servers better than virtual servers for huge databases?” Whenever you say “huge” make sure you qualify that with a size because what seems huge to one person doesn’t seem huge to another. Let’s say for the sake of argument that one terabyte is the mark that we’ll use, databases over one terabyte in size. “In my case, my architects are recommending physical servers for huge databases. What would you guys recommend?” If you guys are going to build a new SQL Server for a one terabyte database by default, do you with a virtual server or a physical server and why?

Tara Kizer: I don’t think it really matters these days. Maybe a few years ago DBAs were uncomfortable running production databases on virtual servers but these days I don’t see any reason why they can’t be virtual. As long as everything is configured properly, your hardware supports it, I don’t think it should matter. For a lot of companies, it’s more about being able to maintain the servers, that’s why they go virtual. They might even have one guest and one host for all the production servers. In that scenario, yeah, you don’t need to have virtual but they’re doing it for maintenance and support reasons.

Brent Ozar: Right.

Erik Darling: Yeah, I’m on board with that. It depends on why you’re virtualizing, if it’s ease of management or if it’s sort of a consolidation thing, then I’m with it—but no one virtualizes for performance. You might be able to eke out close performance but that virtualization layer is always going to be sitting in the middle telling your stuff what to do.

Richie Rump: Yeah, the largest database I’ve seen on virtual is 60 terabytes. We were running a 60 terabyte databases on virtual.

Brent Ozar: And it was Standard Edition. Just kidding.

Richie Rump: Yeah. No, actually.

Erik Darling: It was Web.

[Laughter]

Brent Ozar: Express.

 

Should you pre-size data and log files?

Brent Ozar: J.H. says, “Should you pre-size a data or log file to use up 100 percent of your hard drive space in order to avoid the slowdowns when files grow or is there any reason that I should leave a little space available on a volume in order to breathe?” What are some reasons that you guys wouldn’t grow out a data or log file to the whole drive size?

Tara Kizer: It changes my monitoring. At my last three jobs we’ve used SCOM. We’re monitoring not just SQL Servers, I mean the SCOM admins are monitoring not just SQL Servers but the entire Windows environment. If we grow the files out to fill up the mount point or drive, then our monitoring breaks in the sense that DBAs are now getting alerted all the time. Yeah, you could have some rules in place to have different alerts for the SQL Servers, but then you have to monitor the growth inside the files. I like generic alerts so we don’t have to make exceptions on certain servers. But still, monitor the growth inside the database files and do your manual growths during a maintenance window if the autogrowths are killing you. I’ve never really had a problem with autogrowths.

Brent Ozar: You want to set your autogrowth size to something that isn’t going to kill you. With data files, you just turn on instant file initialization and that problem kind of goes away. With log files though, we still have to pause when the log file grows out. Just figure out how your growth size can be where users won’t freak out. That’s what you set your autogrowth size at. Any opinions “One Metric Erik” and “Enjoy PastethePlan.com?”

Erik Darling: No, you guys covered it.

Brent Ozar: The other reason I’d say I wouldn’t want to pre-grow out is development environments. Sometimes you want to restore from production over to development. You don’t want to take up more space than you need to. So if you’re tight on space in development, you may not have that full drive file size space.

Erik Darling: I think my one exception would be tempdb, but anything else I would just let that grow. Look at how full the files are now and look at how much drive space you have. It’s like, okay, how long is it going to take me to grow into that drive space?

 

Are there any issues renaming SQL Server clusters?

Brent Ozar: Mike asks, “Do you guys ever have any issues renaming a SQL cluster?” Let me back up. I have never renamed a SQL cluster. Have any of you guys? Erik says no.

Tara Kizer: I can’t remember. I think maybe one time for a very valid reason. I don’t remember though.

Brent Ozar: This is a great question for Stack Exchange. I know I’ve seen a post by somebody back from when I used to work at Quest, there was a post on how you go through and rename a cluster. It was horrific. It had like 50 steps in it. I’m just like names don’t mean that much to me. If I wanted to change the name that bad, I would just add a DNS record for whatever people wanted to point to, for the new imaginary name.

Tara Kizer: It mentions the named instance. You can’t rename a named instance. You’d have to reinstall. I don’t even know if this question pertains to a cluster, just SQL Server in general you can’t rename an instance.

Brent Ozar: If you want to change the name of an instance, the way that it looked to people, you can add an alias. You can push out an alias via group policy and have everyone get it. That’s a giant pain in the rear though. I only know one person who’s ever done that.

 

Can we limit the emails we get during maintenance windows?

Brent Ozar: Terry says, “We have alerts set up for SQL severity errors 16-25, however we have an app that tries to break into SQL Server and when this happens we get a whole lot of emails for severity 16-20. Is there a way to turn off emails during a certain time frame or from a certain IP?” I bet what you really want is delay between responses. There’s a parameter on your alert emails that says delay between responses, set that to like 60 seconds or 300 seconds so that you only get one email per minute per severity level. Now of course that’s still going to happen on every single SQL Server so every SQL Server will fire off an email during that time, but yeah.

Erik Darling: You could do the bad idea route. I know that under operators there’s like an on duty schedule or there’s an off duty schedule where you can set “don’t send me emails between these hours,” but then you’re not getting emails from any alerts from those hours. That may not be what you’re after.

Tara Kizer: You could also just use a monitoring tool rather than using SQL Server’s built-in alerts. Monitoring tools can handle this type of situation.

 

Do you like SQL Sentry Plan Explorer?

Brent Ozar: Next question. “I know you guys like Idera based on the webinars.” We like everybody. We like all tool vendors, except for that one. You know who I’m talking about. No, just kidding. We like all of them. We’ve done webinars for everybody. We actually stopped doing webinars for vendors just so that we can start being totally independent again. “But SQL Sentry just released Plan Explorer’s full version for free. Do you have any opinions on it?” I think all of us have used Plan Explorer.

Erik Darling: I used to pay for it.

Tara Kizer: Yeah. The problem that I’m having with Plan Explorer, I used it yesterday with a client. On the new version, I can’t find the default window that comes up. I’m still running the old version on my desktop. I was comparing what I was seeing on the client’s computer to what I see. The screen that shows the—it’s a stored procedure, let’s say with many statements. The default says, “This query is taking this much. That query is taking that much.” You can immediately drill into which one to start your troubleshooting on. Where did that go in the new version?

Erik Darling: I still have that in mine.

Brent Ozar: I had to grab a hold of all of the dividers and move them around. One of them all of a sudden when I moved it, it showed up. I was like, “How did that happen?”
Tara Kizer: Maybe that’s it. Maybe whoever built that installer had it all the way up.

Brent Ozar: Yeah, weird. But yeah, we totally like it. It’s cool. Totally. We brought out this thing called Paste the Plan. If you go to pastetheplan.com you can paste in execution plans in there. For example, if you want to use Plan Explorer to anonymize your plan and then share it with the public, you can totally do that too. We like Plan Explorer.

 

How can I get a new execution plan without dropping the stored procedure?

Brent Ozar: Fellow human asks, “I am facing—” I’m going to rephrase this a little. “I’m facing parameter sniffing issues. How do I get a new execution plan without dropping and recreating the stored procedure? Right now they’re dropping and recreating a stored procedure.” That’s a great question. The fact that we’re all trying to keep a poker face should not dissuade you in any way. He says, “Some stored procedures take four minutes.” So what would you guys do when you’re facing parameter sniffing issues and you want to get a new execution plan for a stored procedure?”

Tara Kizer: Sp_recompile that’s been available since I’ve been working on SQL Server. Never drop and recreate the stored procedure because than you’ve lost permissions. You clearly know what permissions to add back to the stored procedure. An alter does not lose the permissions but that also doesn’t get you a new execution plan. So just sp_recompile but I’d be looking into what’s going on specifically and what version of SQL Server is that on.

Brent Ozar: They said they’ve done sp_recompile and they’ve also rebuilt indexes. Wow.

Tara Kizer: If you’re using sp_recompile and you still have poor performance, you need to take a look at what the execution plan was optimized for. Take a look at the showplan xml. Go to the bottom and then scroll up a little bit. It will tell you what parameter value it was optimized for. Which execution plan gets put into plan cache depends on who runs the stored procedure after you run sp_recompile. Sp_recompile is no different than if you do the drop and create. It’s just who’s running it first, that’s who it gets optimized for.

Brent Ozar: If you go into an execution plan and you find a good set of parameters that produce a good plan, what do you do next?

Tara Kizer: Erik will do the optimize for and I’ll do a plan guide.

[Laughter]

Erik Darling: I won’t do optimize for. I will never do optimize for. Now I’m onto KEEPFIXED PLAN. I’m all over that, the stored procedures. That thing is beautiful.

Tara Kizer: But we’re not sure if that option is going to be available in future versions. There’s hardly any information about it out there.

Erik Darling: Still works.

Brent Ozar: It’s like fashion. We have different fashions based on month of the year and quarter of the year or seasons. I used to be totally anti plan guide until they saved my life. Then I was like, “Plan guides are amazing.” I used to be totally anti query hint until I saw how easy that was. If you search for—and check your watches, ladies and gentlemen—we’re 30 some minutes into the webcast and we finally announced it—the epic blog post “Slow in the App, Fast in SSMS” by Erland Sommarskog. Search in Google for “Slow in the App, Fast in SSMS.” It’s an epic treatise by Erland Sommarskog that talks about the different treatment options that are available to you whenever you’re facing parameter sniffing issues.

Erik Darling: Use Paste the Plan. Post it on Stack Exchange. Maybe we’ll answer it. Maybe someone much smarter than us will answer it.

Brent Ozar: That’s true. Yeah, because usually when you get wildly different execution plans, there’s other ways you could fix it without having to force it. Usually if you tune the query or tune the indexes, there’s a huge difference you can make to get a reliable execution plan permanently.

Erik Darling: Temp tables. No local variables. Da-da-da-da…

Brent Ozar: Listen to us rant about query plan problems. Thanks everybody for hanging out with us this week. We will see you guys next week at Office Hours. Adios.

Tara Kizer: Bye.