[Video] Office Hours 2016 2016/05/18 (With Transcriptions)

This week, Brent, Angie, Erik, Jessica, and Richie discuss sp_Blitz® updates, merge replication, detecting index fragmentation, how to punish your DBAs, and Brent’s new chroma key backdrop.

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.

Jessica Connors: Let’s talk about some merge replication. Paul asks, “What are the ways to handle manual identity management…?”

Brent Ozar: God bless.

[Laughter]

Brent Ozar: Oh man, Paul, you have a crappy job. “What are the ways to handle manual identity management for merge replication?” So what you do is for identities, when you only have two servers and you’re dealing with merge replication, you start one a positive one and goes upwards. The other a negative one and goes downwards. The more servers that you add the more complex you have to get. For example, you can start one at one and then go up by two so that it’s hitting all the odd numbers, 1, 3, 5, 7, 9. You start another one at two and it goes up at two, so it’s 2, 4, 6, 8, and so forth. Then you do the same thing with negatives. The more servers that you have the more complex this gets and you start buying Bitcoin rigs in order to calculate prime numbers and all that kind of crap. You can also do ranges. You can start at the maximum of one identity and go in the other direction. It is just not pretty the more identities that you add.

Erik Darling: Use GUIDs.

Brent Ozar: GUIDs? Yeah, I actually like GUIDs a lot for that. Also, so you are telling me when I said that he had a crappy job.

 

What’s the most accurate way to detect index fragmentation?

Jessica Connors: Gregory has a question about detecting index fragmentation. Wants to know which is more accurate, DBCC CONTIG, might be config, or sys.dm_db_index_physical_stats. I know the DMV is supposed to be more accurate when measuring across multiple files but in my environment, that isn’t a factor. Which metric is more accurate, scan density or logical scan fragmentation?

Erik Darling: Wow. That is a weird question.

Angie Walker: Does it matter that much? I’ve never heard of that.

Erik Darling: How accurate do you need it to be? Are you worried about like 51.1 percent versus 51.2 percent fragmentation? Like I assume that you’re going to only act on this fragmentation at a whole number anyway. So, unless you’re worried about wildly different results, then I wouldn’t really sweat it. The dm_db_index_physical_stats function is the one that everything I’ve ever seen used to detect it at least in modern versions of SQL. Ola Hallengren scripts, maybe some other reindex tools that Brent likes.

Jessica Connors: Yeah, he said he uses Ola Hallengren scripts.

Erik Darling: Why do you have to do anything with them?

Brent Ozar: I want to say that isn’t DBCC SHOWCONTIG deprecated? I’m not 100 percent positive. Yeah.

Erik Darling: If it’s not deprecated then it’s like on the way out. It’s one of those like SQL 2000-ish commands, you know, DBCC PINTABLE.

Brent Ozar: Grandpa told you about.

Erik Darling: Yeah.

Angie Walker: Tell your developer to stop pretending that they’re the DBA. If you’re the DBA you do defragmentation index remaintenance and leave it alone.

Erik Darling: Don’t actually do it and save yourself a whole bunch of time.

Angie Walker: Read Erik’s blog about not reindexing at all.

Brent Ozar: But to get that information, when you scan that DMV, it actually reads the tables. It will do scans depending on what parameters you pass in. I don’t want developers looking at that during the day during business hours. You want to explain to them what it actually does behind the scenes like that it reads huge quantities of large tables. So I just would say, “Hey, dude, if you want I’ll capture that after hours, using Ola Hallengren scripts to just capture it after hours. But don’t touch it during business days.”

 

Can you detach and reattach a database in a restoring state?

Jessica Connors: Question from Bill. “Hey, is it possible to detach, rename the underlying files and reattach a database in a restoring state?”

Angie Walker: Not in restore… what?

Brent Ozar: I think you can. I think you can because I remember having to do something with this around log shipping. Like getting the examples right and all that I would have no clue about, like attaching in a database in a restoring state but just go try it. If you run into problems with it, this is a great example of posting it on Stack Exchange. Go to dba.stackexchange.com and post what you’re trying to do and someone else can walk through the exact syntax. If you don’t get a good answer within a day or two, shoot us an email but I’m pretty sure that you can do that.

Angie Walker: Scott also says you can.

Brent Ozar: Yeah, okay cool.

Angie Walker: Whoever Scott is on the internet.

Brent Ozar: He must be right, right? He’s here. We only have smart people in our Q and A. He’s here for the A. Not as much for the Q. Yeah, I think I’ve done that for log shipping databases.

Jessica Connors: Got ya.

Brent Ozar: Scott says, “I do it at least once a quarter.” This isn’t about your personal life, Scott.

[Laughter]

This is a work webcast. Congratulations, but…

 

Can I rename a SQL Server cluster’s virtual network name?

Jessica Connors: Let’s move on to Nate Johnson. He says, “How easy is it to rename a SQL Server specifically a failover cluster virtual network name, i.e. the physical node names will remain the same, the Windows clustered name will remain the same, but the SQL virtual instance name or the cluster name needs to change.” Thoughts?

Erik Darling: I have never tried to do that.

Brent Ozar: You don’t have to. You don’t have to. There’s an easy work around. All you do is create a DNS CNAME. You create a DNS CNAME of whatever new name that you want and you point it to the existing cluster’s virtual name. Leave the cluster virtual name exactly the way it is. Then just create a DNS CNAME that points to that. I’m saying this really quickly but DNS CNAME. So the letter “C” and then name. Ask your systems administrators to create that for you, they’re the active directory guys, or DNS guys. It’s so simple, takes 30 seconds. Then end users will never know that it’s a different SQL Server. And both names will continue to work forever.

Angie Walker: But what if it’s in the GUI, like in the failover cluster? Maybe that’s why he needs to rename it. Maybe they’re being crazy and then they’ll have two of the same.

Brent Ozar: Look at the GUI, get out of there. I love the GUI. I’m a big GUI kind of guy.

Jessica Connors: Nate is also in a pickle. He looks like he had, he’s broken replication.

Richie Rump: Good.

Angie Walker: Tara is not here.

Erik Darling: You fixed replication.

[Laughter]

Brent Ozar: Sounds like success to us.

Angie Walker: Where’s the hammer? Fixed.

 

We broke replication. Now what do we do?

Jessica Connors: He says, “We broke replication, transactional replication. We tried a reinstitute with new Snap and then canceled due to the CPU spiking. Now we’re missing schema and data. How to recover besides a full reinstitute? We’ve got the schema synced up manually but the missing data is turning out to be huge because some tables are millions of rows. Any recommendations?”

Brent Ozar: Let’s see in reinitialization, I think with a backup—I truly don’t do transactional replication—but what I would do is if you know that some data but not all data is there, you can insert it across yourself. You can do your own inserts and then just tell transactional replication, “Hey, we’re good. You don’t have to bother to reinitialize.” But you said that there’s millions of rows missing. Then yeah, then you have to put those rows back. That’s the part that blows.

Erik Darling: Unless you can get away with it and throw a filter on there to not put all the rows in. You could start small.

Brent Ozar: I like that, filtered publication. Only the stuff that is current to people.

 

Jessica Connors: Nate Johnson is on a roll.

Brent Ozar: Oh, on a roll.

Jessica Connors: He’s on a roll. He has another question.

Brent Ozar: Not in a good roll. This poor guy is like having to throw so many questions, “Another thing, quick, I’ve got to get these in before you guys close the webcast because this is on my homework.”

[Laughter]

 

Should I upgrade SQL Server 2005 to 2008R2 in-place?

Jessica Connors: I’ll take one more from Nate. He says, “It’s taking months longer than anticipated to prep SQL migration and new hardware to a 2005 to 2014 upgrade. Can I convince managers that a 2005 to 2008 R2 in-place upgrade is a good idea to buy us more time and compliance?”

Erik Darling: No. In-place upgrades are the devil, especially major versions like that. Run screaming. Run screaming into the night.

Angie Walker: Plus, why do you want to go to 08 R2?

Brent Ozar: I think he wants to get under warranty support because he’s worried about 2005 being out of support and I get that.

Erik Darling: So you would have to upgrade to 2008 R2 and then to Service Pack 3 so you’re looking at some pretty big jumps.

Brent Ozar: Yeah, that makes me nervous. With no backout plan if anything goes wrong.

Angie Walker: So maybe to let him know what would be—if right now he has a problem, he’s on 2005 and he calls Microsoft. Are they going to tell him sorry? Shut the door in his face? Or are they going to make him pay extra money for help?

Brent Ozar: God, 2005, I don’t even think they take support calls anymore. It’s not like, this is too, a couple of us were joking around recently. It’s like, how often do you really call Microsoft anyway, right? Do you really get on the phone with Microsoft a lot? Does it really matter that you’re out of support? That’s horrible for me to say but if it’s a production application—I mean, we only want to work on stuff that’s in support. We don’t take work on out-of-support databases unless it’s an extra charge kind of thing. Yeah, you look at Erik fist pumping. Yeah, that’s tough. I would actually stand up and do VM, just a virtual machine in the meantime assuming that you’ve got those. If you could make that work performance-wise, that’s what I would go for until they get the new hardware you want.

 

Should adding logins or taking backups go through change control?

Jessica Connors: A quick question from Samuel. He says, “On production do you agree that adding a login or taking a backup should require change control?”

Brent Ozar: Don’t you love it when people ask, “Do you agree?” It’s like this big leading question. “Wouldn’t you say that…?” Leading question, your Honor. All right, so we’ll go through, we’ll ask each person. So just as I’m looking at my monitor, Erik, would you go through change control if it involved adding a login?

Erik Darling: Adding a login, it would depend on for what. If it was a new developer or a new DBA I probably wouldn’t go through change control for that. If it was for a new application then I certainly would or something else that needed access to the server from outside, I might. What was the other part?

Brent Ozar: Backing up database? Was that what it was?

Erik Darling: If you want to go through change control every time you take a backup, you’re going to spend a lot of time with change control.

Brent Ozar: The only time, when you say take a—like if it’s an out-of-band backup maybe, like if somebody wants to take a backup in the middle of the day and it’s going to slow down performance, but otherwise I would hope that if you have to file a change control, yeah, I think we’re generally we’re going no across here. But in terms of adding a login, yeah, it just depends on what it’s for. If it’s a read-only login that is deny datawriter then I’m kind of comfortable with it as long as it’s not personally identifiable data. You know, it’s not like credit card data or social security numbers.

Erik Darling: If it is an out-of-band backup, you want to be looking at taking a copy only backup, not a regular full backup.

Brent Ozar: Oh, that’s a good point. Richie, how about you?

[Sirens in background]

Are the police coming for you? Is that what that is?

Erik Darling: Uh, no. They’re coming for me first of all.

Richie Rump: It used to be for you remember all the sirens you used to get? But now it’s Erik.

Brent Ozar: The guy in Brooklyn.

 

Should I add multiple transaction log files for better speed?

Jessica Connors: All right, sp_Blitz question. Bruno says, “sp_Blitz warns me that there are multiple log files on the drive and that this is not a performance booster because log file access is sequential not parallel but isn’t this considered a best practice to minimize contention problems?”

Erik Darling: If it’s sequential, then there’s no contention.

Brent Ozar: There’s a super rare edge case that Thomas Kejser blogs about where they end up with a bunch of really small log files in order to… [Laughs] Erik is throwing his headphones. Just wildly rare, ridiculously, crazy rare, where they have a whole bunch of really small log files in order to minimize log buffer rights. That’s like in the million transactions a second territory. That’s like a totally different world than what most of us out there deal with.

Erik Darling: You also have to put them all on separate drives, all sorts of other stuff too.

Brent Ozar: Yeah. Solid state drives.

Erik Darling:  In Oracle, it is a little bit different. Oracle will use the multiple log files at once but that’s not a contention thing, that’s a redundancy thing. So you can have multiple log files to get the same synchronous data pushed to them but even that’s not going to do much. SQL uses them serially, it’s not going to paralyze the log writes.

Jessica Connors: So sp_Blitz does check for too many virtual log files?

Brent Ozar: Yes, yes, it does. I want to say we alert you around 1,000. I don’t think 1,000 is bad but we just want to give you the heads up before you hit some ridiculously bad number. So at 1,000 we’re like, “Hey, maybe you should think about shrinking this log file. Doing something about that.”

 

How can I gradually move databases from one AG to another?

Jessica Connors: Question from Gregory. He says, “Is there a way to gradually move databases from one instance to a separate AlwaysOn Availability Group without any application changes? I would like to use an alias DNS record but I think the movement would be all of the databases at once. I was hoping the alias record would just be the name of the AG listener but I don’t think both servers can use it at the same time to allow gradual migration.

Erik Darling: Couldn’t you use multiple connection strings on that?

Brent Ozar: Yeah, yeah, yeah. Keep going. Keep explaining there.

Erik Darling:  Oh, god, I don’t—I just know that that exists. So with availability groups you can use connection strings for different activities. So you have one for reads and one for writes and one for everything else. I think you might be able to do something where you set up and assuming it’s .NET, so this is a multi-subnet failover. Yeah, so something that you would use when it’s a .NET application and you can use that stuff. You should be able to use different connection strings to push different activities to different servers and/or databases. Which is probably what I would do if I had a real job.

Brent Ozar: Each connection string can use a different DNS CNAME and then you can gradually move those apart to different listeners if you like.

Erik Darling: Yeah. Thanks for smartening that up for me.

Brent Ozar: Oh, and then, when a big database or a big query loves a small database very much…

Erik Darling: It goes to jail for a long time.

[Laughter]

 

In virtualization, how do I use local SSDs for TempDB?

Jessica Connors: Question from Sean. How can tempdb be placed on a local SSD in a failover cluster? System is two-node Hyper-V slow SAN SQL 2012 Standard.

Erik Darling: That kind of dive bombs for me when you say Hyper-V or really any sort of virtualization because then you have that machine tied to that host. Then you would have to on every host have that thing set up for that VM and it gets messy. Get a better SAN. Sorry.

Brent Ozar: Yeah. So often you’ll see people saying, “Virtual machines can be just as fast as physical ones.” Not when you do stuff like this. There’s still some tricks that you only get in physical machines. Some people will say, “Well what I could do is in an AlwaysOn Availability Group put a different VM on each node and have him use local storage.” Yeah, but then when the host dies you’re screwed. That whole thing failsover.

Erik Darling: Let’s be honest, whenever you hear about someone saying, “Oh I made this machine so much faster by virtualizing,” it’s always like, “I took a SQL 2000 box and I put it on modern hardware and now it’s miraculously faster.” It’s never, “Oh, I had two equivalent boxes sitting on SQL Server,” let’s say 2012, keep it sort of recent. “One was virtual, one was physical and they were both the same speed.” No one ever says that. Everyone is always showing off about some old really old application that they upgraded the hardware on. It’s like, so? I don’t get it.

Angie Walker: Yeah, usually much simpler setup than this too. I mean one standalone server with tiny data. Not as much of a problem.

 

How should I punish DBAs who put production databases in simple recovery model?

Jessica Connors: All right. Justin wants to know how to punish his DBAs. “What’s the appropriate punishment for a production DBA who puts all the production databases into simple recovery mode?”

Erik Darling: He has to do his MySQL.

Brent Ozar: That will also do the trick.

Angie Walker: Does the business care? Come on. Maybe you don’t need full recovery model. Who cares about logging every transaction if you don’t need point in time restore? Or anything more than a week’s RPL goal maybe?

Brent Ozar: Data warehouses, when you’re going to reload from scratch every day. Normally I would give Angie a gold star but the only thing I have is a gold screen protector.

Angie Walker: I will accept it.

Richie Rump: See you start with a box and then you put nails… Oh, I’m going to stop right there.

Angie Walker: Bad developer.

[Laughter]

Jessica Connors: All right. Mike has a question and he saw you in Houston last week.

Brent Ozar: Oh, cool.

 

How often should I rebuild indexes?

Jessica Connors:  Mike says, “I’m hoping to convince my customers to let me pull the plug on index rebuilding MX plans temporarily in order to accumulate x amount of time of usage stats. What is a minimal amount of time you feel is sufficient? In Houston you said to get a few weeks but do you think that one week would be enough? One particular customer will be having anxiety attacks about not running index maintenance though it’s going to be a tough sell so I’m hoping maybe a week would be good enough.

Brent Ozar: Mike, I have phenomenal news. SQL Server 2012 had a series of cumulative updates come out yesterday that fixed the index usage stats bug. So starting—dun dun dun—2012 SP whatever it is, if you go to SQLserverupdates.com, if they’re on SQL Server 2012 the latest update that came out yesterday for both the service pack and the one before the current service pack, so it’s like CU3 and CU12. Now let SQL Server’s index usage statistics persist. Missing index recommendations do not persist but at least the usage stats do. So if they’re on 2012, have them go get that. 2014 that fix is not out yet. Because the 2012 one came out, I feel I would be kind of like I’m not going to pick that battle with my customers. I’ll just go back and apply the 2014 CU when it comes out.

Erik Darling: Since if you install the service pack you may have to reboot anyway, which will clear out your stats anyway. What I would do is I would just scale my index maintenance back to only reorganize stuff which doesn’t wipe stats out. So that you have stats persist and they still get their precious fragmentation lessened and you get to collect usage statistics. Only rebuilding clears those out to clarify.

Angie Walker: Since there’s a fix for it, we can officially call it a bug now, right? Like it’s not just…

Brent Ozar: Right. That’s what I call it, yeah.

Erik Darling: See now I have egg on my face because I’ve been telling people that 2012 wasn’t going to get the fix because the original announcement about that change said 2014 and 2016 only. So, omelette everywhere.

 

Where do I download SQL Server for Linux?

Jessica Connors: Speaking of 2016, since SQL 2016 for Linux is not scheduled for general release until 2017 have you heard of any preview versions available?

Angie Walker: That is a no.

Brent Ozar: The way that Microsoft does its preview-type stuff is often people who have NDA or who get early access programs get access to early betas. We are not in that program, we’re not in the one for Linux. We were in the one for 2016 as you can tell when we bring out blog posts and we have screenshots available five minutes within the release coming out. In terms of other people though, what you want to do is get in contact with your Microsoft sales rep. If you don’t have a sales rep or a TAM, a technical account manager, then you’re probably not going to get into the program.

Jessica Connors: Wah-wah-wah.

Brent Ozar: Wah-wah-wah.

 

How do I measure storage bandwidth?

Jessica Connors: From Dennis, he says, “Do you guys have a good way to measure a 10GB iSCSI bandwidth to the SAN?” This does not sound like a database engine question.

Brent Ozar: How do you guys like to do storage testing?

Erik Darling: CrystalDiskMark or Diskspeed.

Brent Ozar: There you go. CrystalDiskMark, yeah, it’s excellent, it’s an easy button for storage testing. If you go to our site and grab CrystalDiskMark there’s a post on exactly on how to use it. It’s current with the current version of CrystalDiskMark too. They changed the screenshots around.

 

What’s the difference between a unique index and a unique constraint?

Jessica Connors: All right. Question from Jonathan. He says, “From what I understand there is no internal difference between a unique index and a unique constraint. Which one do you all prefer and why?”

Brent Ozar: Isn’t there a difference?

Erik Darling: A unique constraint is backed by a unique index, right? All constraints are backed by indexes basically.

Angie Walker: Correlation not causation or something.

Richie Rump: I always thought it was syntactic sugar. I could be wrong.

Brent Ozar: It is. If you go to Stack Exchange, if you go to dba.stackexchange.com, there’s a question “When should I use a unique constraint instead of a unique index?” Like for example, a unique constraint cannot be filtered, a unique index can be filtered. There’s a bunch of little, super tiny differences between. Unique constraint cannot be disabled however unique index can be disabled. I’ll post this, I’ll just put it out on Twitter what the differences between these. I’m going to tweet that right now. Tweet.

Angie Walker: Andrew proved that even short questions can be answered on Stack Exchange if you don’t get around to our webcast.

Brent Ozar: Yeah, the answers are huge. The answers are like pages long on this. People go into details.

 

How do I rename my Active Directory domain?

Jessica Connors: All right. Let’s take one from Corey. He says, “Management would like the local AD domain name changed to reflect their new brand. We have a single SQL 2008 R2 database for document management. We have about 30 users. Is this realistic? Any gotchas? My background is not SQL, just a basic network admin.”

Brent Ozar: It depends on whether or not you have a cluster. If you have a cluster, this is the part that sucks. But if you don’t have a cluster, it’s totally easy. It’s a total piece of cake. What a lot of people do is just stand up a new domain and then move stuff across from one to the other or a new forest. Erik’s got an idea.

Erik Darling: No, I don’t have an idea. I just finally understood the question.

Brent Ozar: Oh.

Angie Walker: Yeah, answering it for me—I was like, “Does he really just want to change the server name?”

Erik Darling:  It was confusing to me too. I was like—then, no, that’s bad.

Brent Ozar: Yeah, so it is a giant pain in the rear. I visited a company this past Monday I guess that had to go through that and it was ginormous work for all the DBAs involved.

Jessica Connors: The green looks good on you. You stand out. Brent has this green wall behind him. He just painted the wall behind him green. It’s this bright, bright green and it actually looks really good.

Brent Ozar: Thank you. It depends a lot on the webcam and like how much sunlight is in here for a while. So sometimes I look like pale, alien pale. So had to get the right lights working. I look alien pale in the daylight too as well so. Yes, we did that for the green screen effects that we use in our training videos. Doug does the same thing down at his place, has a wall set up so that he can make the background disappear. It’s called chroma keying. It’s really fun.

 

Can I ignore the QDS wait types?

Jessica Connors: Nice. Bruno had the sp_Blitz question earlier. I think he has a follow-up. He says, “Can I ignore all the QDS wait types?”

Brent Ozar: Yeah, stands for Query Store and you can totally ignore all the sales in the Query Store.

Jessica Connors: All right.

 

Should I index my temp tables?

Erik Darling: There’s a question from Dan that looks kind of interesting. I can read it to you if you like interesting.

Jessica Connors: Read it.

Erik Darling: He has a temp table in the store procedure and it has three nonclustered indexes on it but the execution plan is showing a table scan rather than hitting any of the nonclustered indexes. Now what you could be looking at is you have these nonclustered indexes that may well satisfy your predicate like a where clause or a join or something but they may not have enough included columns in them to tip the balance over to use those indexes rather than just scanning the entire table to get all the data. So if you’re selecting all the columns in the table but your where clause is only on a column or two, you may see that the optimizer chooses to scan the entire table because it’s cheaper than hitting the nonclustered index and doing a key lookup back to get all the other columns up.

Jessica Connors: There you go.

Erik Darling: Start with a clustered index on a temp table rather a nonclustered index. That’s usually my rule of thumb.

Jessica Connors: All right.

Brent Ozar: I like it.

 

Can I force my AG users to query the listener rather than server names?

Jessica Connors: We have another question from Gregory. “Is there a way to enforce the use of AG listeners and AlwaysOn Availability Groups versus just connecting directly to an instance?”

Brent Ozar: Oh, wow. No. No, I don’t think so. Wow. What a good question. I don’t think so. Could you force…? Yeah, I can’t think of a way that you would be able to do that. No, other than being the obvious jerk and changing the names of your servers all the time so that people can’t guess them that way but yeah, I don’t think there’s a way to do that. Greg, you should totally ask that on Stack Exchange and then whether you get an answer or not, email us, because I would like to hear about that and see if anybody comes up with answer.

Erik Darling: That’s pretty [Inaudible 00:25:07]

Brent Ozar: Or, so I’m going to go out of the box. Maybe like if you use F5 Global IPs or Impervas or Guardiums, those network appliances that sit in between your SQL Server and the network but other than that, that would be a firewall’s stateful packet inspection.

Jessica Connors: All right, let’s do one more. From our buddy, Nate. He’s my friend today because there’s not many people asking questions. It’s like three people who are having at it.

Angie Walker: A high honor you guys, to be Jessica’s friend.

 

Have you ever worked with Microsoft Dynamics NAV migrations?

Jessica Connors: Yeah, I’m going to add him on Facebook after this. He says, “This might be a shot in the dark but have you guys ever worked with a client who was running Microsoft Dynamics NAV. If so, do you have any experience with migrating or upgrading the backing to SQL Server that’s running the NAV databases?”

Brent Ozar: How about you guys?

Erik Darling: Nothing.

Angie Walker: We don’t really help with migrating and upgrading clients’ databases specifically, not on a regular basis.

Brent Ozar: I don’t know that we really help with anything. I don’t even know if we help on some things.

Angie Walker: Well, I help with some things.

[Laughter]

Erik Darling: Big hand wavy.

Angie Walker: I pull all of the skeletons out of the closet and then I set them up for Erik and Doug and Tara to beat them down a little bit.

 

Why do low-cost queries sometimes go parallel?

Erik Darling: There’s a good question in here that I want to get to. It’s from Samuel and he asks, “I have a query that is less than the cost of parallelism but it’s still giving a parallel plan. Why would this be?” It’s because it’s not the cost of the parallel plan that has to exceed the cost threshold. It’s the cost of the serial plan that has to exceed the cost threshold for parallelism and the parallel plan has to be cheaper. So what you’re seeing is a parallel plan that is cheaper than the serial plan that crossed the cost threshold.

Brent Ozar: Dun dun dun. So you can see what the cost of the serial one was by going option max stop 1 in a query and you’ll get an idea unless it’s a stored procedure, then it gets a little trickier.

Erik Darling: Yep.

Jessica Connors: Cool beans. I’m hungry, my stomach is growling.

Brent Ozar: Oh, where are you going for lunch?

Jessica Connors: I’m getting some UberEATS. Unless you want to go somewhere. You’re right down the street today.

Brent Ozar: Oh, no. I’m good. I think I’m going to just phone in and have something else delivered here as well.

Jessica Connors: Yeah, UberEATS is awesome. Three minutes. Hot meal.

Richie Rump: I’ve got to go to FedEx and drop off a computer. Thanks, Brent.

[Laughter]

Brent Ozar: Oh, Richie. Richie chose poorly, not chose wisely. Yes, his machine. I hope they don’t ruin your stickers. All right, thanks everybody and we will see you next week.

Previous Post
SQL Server 2016 Maintenance Plan Improvements
Next Post
SQL Interview Question: “Tell me what you see in this screenshot.”

3 Comments. Leave new

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