[Video] Office Hours 2017/01/25 (With Transcriptions)

This week, Brent, Erik, Tara, and Richie discuss whether an antivirus can corrupt a database or remove data files, the biggest node clusters they’ve worked with, whether cursors are a good idea for reporting queries, authogrowth setting for new servers, memory-optimized tables, index rebuilds and more.

Here’s the video on YouTube:

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

Enjoy the Podcast?

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

Office Hours Webcast – 2017-01-25

 

What’s the big deal with antivirus software on SQL Servers?

Brent Ozar: Graham asks, “I’m reading Denny Cherry’s book Securing SQL Server. He says for antivirus configuration he recommends not scanning the data files because if a data pattern matches that of a known virus the antivirus could corrupt the database or remove the database file. Has anyone seen this happen?”

Tara Kizer: I have not seen anyone say that that’s why to exclude it from the antivirus software. Usually, people are saying it for performance reasons you don’t want it to scan. There’s like 60 exclusions that you’ll want to add to antivirus but I haven’t seen the issue that Denny is referring to. But of course, I always have exclusions in place.

Brent Ozar: Antivirus apps don’t change the file, they just quarantine the file. They just block it so you can’t access it, so that shouldn’t cause corruption.

Erik Darling: Like MDF and LDF are open in SQL Server, I can’t do anything to them. Would an antivirus app be able to grab them and do something?

Brent Ozar: No. The time where it catches you is on startup. If you stop the instance and start it then you can have it where the antivirus has grabbed a lock on one of the files to scan it and SQL Server skips that database on startup.

Erik Darling: Oops.

 

What’s a good alternative to the T-SQL debugger?

Brent Ozar: Thomas asks—this is such a good question—“Do you know of a good working alternative to the T-SQL debugger built into SSMS?”

Richie Rump: Wait, T-SQL has a debugger? I should try that. That would be amazing.

Tara Kizer: I can’t ever get it to work.

Erik Darling: It accidentally comes up every time I accidentally hit F6.

Tara Kizer: Yeah, exactly.

Brent Ozar: For those of you who haven’t had the pleasure of using the debugger before, you will learn once that it stops the SQL Server while you’re stepping through debugging. So if you ever want a way to bring production down quickly, that’s a really surefire way to do it, especially if your code has locks in any of the code that you’re stepping through. Hello, ugliness. So to answer your question, I haven’t seen one either.

Richie Rump: Yeah, I’ve never used it. Typically, if I ever feel the need to use a debugger that means I probably need to break up this script into something smaller.

 

Why does patching suck on a 10-node cluster?

Brent Ozar: Stephanie asks, “We have a ten-node geo-cluster. SQL patching takes hours upon hours.” I’m not laughing with you, I’m laughing at you. “Do you see any issues with pausing the node then stopping the cluster service locally before starting a SQL Server service pack upgrade?” So what was the biggest cluster you guys have ever had in terms of nodes? I’m thinking like five.

Tara Kizer: At my last job we had a 14-node cluster, but this was on SQL Server 2012 where you could have five total replicas, four secondaries and one primary. So this 14-node cluster had several availability groups spanning different nodes. We had tier one and tier two servers at the primary site and then we had tier three servers and lower hardware at the DR site. We needed all the databases to be able to be queried on one instance for the business users. So they all had that as a replica too.

Brent Ozar: How did you do patching on that?

Tara Kizer: We did the tier two and tier three servers, those are the synchronous and asynchronous replicas, we did those ahead of time. Production wasn’t running on them. Then tier one we did at night at like 8:00 I think it was. It did not take us hours upon hours though. But again, we split up the work. I think that we hit all of the primary replicas at the same time.

Brent Ozar: One big outage. Yeah, just planning takes hours to get everything staged and ready to go, get everybody’s approvals ready to go.

Tara Kizer: This was Microsoft security patching, as far as service packs, same type of thing though.

 

Why do memory-optimized tables use so much memory?

Brent Ozar: Oh, Kyle, bless your heart for asking this question. Kyle says, “Memory-optimized tables seem to occupy a ton of memory.”

Erik Darling: Go on, sir.

Brent Ozar: Let’s think through that for a second. He says, “I thought they would be compressed but I guess I read the specs wrong. Is compression anywhere near the horizon for Hekaton? It would be nice to send Hana packing. Thanks.” Nope. So what Microsoft recommends is that you need 2x the size of the tables. So like if you have 100 gigs worth of Hekaton data, you’re supposed to have 200 gigs worth of RAM because Hekaton is append only. All new rows are coming in as new versions but also deleted rows and updated rows come in as new versions. So that every now and then SQL Server does the equivalent of garbage collection, getting rid of the old versions of rows. It needs a new table in order to go do that. Like a whole lot of other things in SQL Server these days, these features use memory because memory is cheap. Microsoft just figured out that memory is cheap and it’s time for us to recognize the same thing.

 

Does anyone use database snapshots with Always On AGs?

Brent Ozar: Oh my goodness. Roland says, “I have a database with 1.3 terabytes and lots of indexes on my AGs. Over on my secondary replica I have a snapshot of the database. When I start the index optimization on the primary replica my redo queue builds up very high. Last time it was up to 150 gigs in the queue. The only way I was able to speed it up was to drop the snapshot. Is there a best practice for how to do huge updates in snapshots along the same time?” Have you guys ever used snapshots in combination with an AG? I have not.

Erik Darling: Why would you?

Brent Ozar: No, everybody is shaking their heads. The only—I’m like stretching—if you want to get a point in time consistent view of the data maybe, like if you wanted to say, “I want to see my reports as of exactly 8:00 a.m. this morning.” The one thing you might be interested in is 2016 has parallel redo. If you go to groupby.org we have a session from Bob Ward on there. He talks about the differences that parallel redo makes in SQL Server 2016. It’s his SQL Server 2016 “It Just Runs Faster.” I don’t know of anybody using snapshots with availability groups though so that kind of stumps me there.

 

Should I stop SQL Server before I shut down Windows?

Brent Ozar: Boy, you guys have got great questions today. You guys are like the A+ audience. I’m looking forward to this one. We might be here for an hour.

Erik Darling: D- answers.

Richie Rump: Come back next week.

Brent Ozar: Yeah, can you just trickle these out? You don’t ask all your good questions at once, leave some of them. I tell you, that audience last week, man, they were red cards. They were not good. Just kidding because I know a lot of you were in there. Thomas says, “I’m moving a storage array and I’m shutting down all my SQL Servers. Should I stop the SQL Server services first or just shut down Windows?”

Tara Kizer: I’d just shut it down. Shut down Windows.

Brent Ozar: Yeah.

Tara Kizer: T-SQL Server takes care of us. It knows what to do when it comes back up and it has to go through crash recovery.
Brent Ozar: If you were worried about like a failover of clusters or anything, you could shut down your passive nodes first and then finally shut down the active if you wanted to just walk it all the way down.

 

Brent Ozar: William says, “The debugger, the first time I tried it out it was on my QA system. I soon afterward had several confused developers at my cubicle.” That’s probably true. If you want to take a break from work and get the team to go out for beers or something, you could just fire up the debugger and no one will be able to get any work done.

 

Are cursors a good idea in reporting queries?

Brent Ozar: Adam says, “I never use cursors in my report queries.” Well, Adam, our relationship is off to a good start. “But I’ve been playing around on Stack Overflow and I see some incredibly convoluted answers to problems. They strike me as they would be much easier solved by a cursor. Granted, these problems are situations where the schema is faulty or part of the solution would be better handled in the presentation layer.” He says, “Are cursors ever a good idea for reporting queries?”

Richie Rump: I haven’t run across one.

Erik Darling: No. I mean, like, you’ll find some like way out there crazy edge cases. I think Aaron Bertrand has blogged about using cursors in certain scenarios for like grouped medians where they’re somehow faster than a set based solution but really, for the most part, you’re not going to find that in everyday code.

 

What happened to all my drive’s free space?

Brent Ozar: Next up, Rosemary—Rosemary had emailed us earlier and I said, “Hey, you should ask that question during Office Hours.” Rosemary asks, “I’m using backup compression and I’ve got multiple backup files on one dedicated drive. I’ve got six 50-gig files, 300 gigs all together, but the disks that I’m dealing with only show 6 gigs free and available. I don’t understand why 800 gigs of drive space minus 300 gigs worth of backups means that I only have 5 gigs available. Where did my other 500 gigs go?”

Richie Rump: Erik took it.

Tara Kizer: What’s the size of the backup file on disk? I can think of two things here. Trace flag 3042. But that would only apply while the backup is running, so the algorithm that they use in the beginning of the compression process, it may estimate a much larger file size needed than the compressed size. We had free space issues on some mount points at a job that I was at, so we had to use trace flag 3042 so that it wouldn’t overestimate the size of the needed file. The other thing that I’m thinking of that it might be is the volume shadow copy file, I think. I don’t think you can see those if you have Windows Explorer set to show hidden files. I think they’re still hidden from the hidden, if I recall correctly. We ended up having to do something to see them in Explorer. Then I think we had to stop the service to prevent this large file from consuming space.

Brent Ozar: I love it. You may not be doing the snapshot, that’s someone else doing the snapshot copy. I was going for like a page file. If you have a hidden file that Windows is managing RAM, that will show as like the page file will burn up a ton of space. In Windows Explorer, just set your settings so you can view hidden files and that should hit that one.

Tara Kizer: I think in her original email, because I did see the email come through, it said that they have Windows Explorer set up to show hidden files and the page file should show up in the hidden files. I seem to remember some kind of file had to do with the shadow copy service, you couldn’t see those.

Brent Ozar: You’re absolutely right, yeah, it won’t show up there.

Erik Darling: Would a utility like WinDirStat or something see that?

Brent Ozar: I don’t know. I’ve never done it with VSS snapshots but I wouldn’t be surprised.

Erik Darling: Yeah, whenever I’ve had like a weird drive space consumption issue, WinDirStat, it’s a free-ish or mostly free application I’ve been able to use to track down weird files that are consuming space. There’s always a Stack Overflow on [inaudible] see what it is.

 

What’s the worst that could happen if I add clustered indexes?

Brent Ozar: Danny says, “We have a database full of tables using unique identifiers as primary keys. There are non-clustered indexes on all these unique identifier columns but there are no clustered indexes anywhere on any of these tables. My knee jerk reaction is to replace all these non-clustered indexes with clustered indexes ASAP. Is this a no brainer or could I be setting myself up for disaster doing this?”

Erik Darling: Well you only get one non-clustered index, you know. I mean one clustered index.

Brent Ozar: I was like, wait, what?

Erik Darling: You don’t get multiple clustered indexes. There’s a lot of sort of religious debate around whether you should have a clustered index on a GUID or not. My feeling is generally no unless you have some like really—so like one place where GUIDs shine for clustered indexes is if you have like a really high insert workload, you can run into something called last page contention where you’re always trying to burn up, like if you have an identity column or another kind of sequential clustered index key. So you’re always trying to insert data to the very end of the index, then that’s your hotspot. You can switch over to using a GUID. It’s so easy, just switch over to using GUID for your clustered index. But generally, you could just add some surrogate key, some identity column to the table or something to have a clustered index on instead and keep your non-clustered indexes in place. But that’s going to be a heck of an IO intensive operation. I’d probably opt for creating a new table with the clustered index that I want on it, insert my data over to it, and then create my non-clustered indexes on it.

Brent Ozar: I’d just make sure you’re fixing a problem. Like you can make things worse when you stick on clustered indexes. It’s not normal, but just make sure that you understand that you’re fixing a problem. Like look at what’s the top wait stat on that server before you go [inaudible] with things.

Erik Darling: Another thing you might want to do is if you run sp_BlitzIndex in mode four for that database, you’ll get a lot of information about your heaps back. You’ll see if there’s heaps with a lot of forwarded records in them or heaps that are occupying a lot of space. So like if you have a workload that deletes from heap tables, the pages may not get released from the heap back to the database. So you may just have a bunch of empty pages which SQL is still going to unfortunately scan and look at it when you read from the table. So I would run BlitzIndex in mode four to that database to get some more information about the heaps to see if they are actually problematic.

Brent Ozar: Problematic—like automatic, but different.

 

What should I do about this 30GB log file?

Brent Ozar: Fred says, “I have a database with a 30-gig log file but the data file is 25 gigs. I’ve avoided clearing or shrinking the log. What do you think is going on and what should I do?”

Tara Kizer: Do you really need the disk space? It’s so close in size I’d probably just keep it that way. Is your recovery model in bulked log or full? If so, make sure you have transaction log backups occurring frequent enough for your workload.

Brent Ozar: Full backups are not going to clear that out. This is a 1 terabyte solid state drive. It’s smaller than a phone. It costs $300 at Amazon so just be careful what you’re doing there with a 30 gig log file. Oh my god, it’s the end of the world. Not necessarily.

 

Does rebuilding an index take the database offline?

Brent Ozar: Raul says, “Can someone confirm that an index rebuild requires taking the database offline in Standard Edition?”

Tara Kizer: We’ll confirm that that is not required.

Erik Darling: It will take something offline.

Tara Kizer: Your index is going to come offline for Standard Edition, so it won’t be able to be used to tell the performance of queries. The database remains online, you’ll just have some blocking for the index that’s currently being rebuilt.

 

Should I rebuild all my indexes after I upgrade SQL Server?

Brent Ozar: Roland says, “I’ve read you should do a rebuild on all your indexes after you upgrade SQL Server. Why is this recommended?” Wherever you read that, go hunt them down and bookmark that site because you never want to go back there again. You want to be able to recognize bad advice when you come to it. You did exactly the right thing by asking why is it true, because there’s so much bad stuff out there on the internet many of which comes from our own site. But in this case, not this time.

Tara Kizer: Did you guys ever do update statistics after an upgrade?

Brent Ozar: Yes.

Tara Kizer: I didn’t always, it just depended on the system whether I wanted to spend the time doing it or not, how critical was the server or database.

Erik Darling: What kind of stinks is a lot of the times when you read people saying that you should update statistics after you move, they’re like you have to do it with a full scan, but when you have a multi-terabyte database, you can’t just like willy-nilly, “I’m going to full scan all these statistics, no problem.”

 

A long question walks into a bar

Brent Ozar: Eric asks…

Erik Darling: No, I didn’t.

Brent Ozar: Eric has a big, long one.

Erik Darling: Hey, thanks.

Brent Ozar: Yeah, the other Eric. Only one other Eric. Holy cow, Eric. That’s actually a good example of a question to post over on Stack Exchange. As you see these slides scroll by if you’re on YouTube or on a live broadcast, there’s instructions on how to go to dba.stackexchange. Anytime you find yourself typing multiple sentences, and especially multiple lines, multiple questions, probably want to go tackle that over at Stack Exchange.

Erik Darling: Sounds like there’s a vendor product involved so you might want to check with the vendor on that as well.

 

Know of any good job management applications?

Brent Ozar: Paul says, “Do you have any suggestions for job management applications? We want to manage all our agent jobs from a central server but all the apps I’ve found don’t really do logical mapping, meaning if the job fails on this stored procedure, start at this particular step again.”

Erik Darling: Yeah. Unfortunately, it’s going to cost you money. SQLSentry Performance Advisor does have a whole job chaining and job thinging thing in their product. I haven’t used it extensively because I never had a need for it. I was always pretty cool with having different profiles for that stuff but it’s pretty cool. If you go check out the—I said SQLSentry again—SentryOne Performance Advisor. If you go to the SentryOne website they have the job managing part of the performance advisor product with all sorts of pretty screen caps and an explanation of it in there if you want to look at it. Other than that, I don’t know.

Tara Kizer: I’m trying to think of the tool that we used at my last job because we weren’t just managing SQL Server. The dev ops team wasn’t managing just SQL Server so we had another—some kind of Windows third-party package that we purchased. It wasn’t cheap. I know other people have used it. It came up in the MVP mailing list recently and I think it was Adam Machanic that mentioned that they use it as well. So we had to kick off different things and then kick off SQL Server jobs. I just can’t remember the name of it. It’s an expensive product too.

Brent Ozar: JAMS has one, Unicenter is another one.

Erik Darling: I had a client once that built their own.

Richie Rump: Yeah, I mean, why not just build your own? How hard could it be, right?

Erik Darling: Yeah, it just needs a front-end and some stuff behind it that does things. Use the cloud.

Brent Ozar: Error handling we’ll leave for version two.

Richie Rump: We don’t need that. That’s optional.

Tara Kizer: I’ve got the answer to the tool I was using. It’s called Tidal.

Brent Ozar: Is that a streaming music service by Jay Z?

Tara Kizer: It might be a Cisco product. I didn’t look it up. Just the name.

 

Someone’s asking me for a UTF16 collation…

Brent Ozar: J.H. has an interesting one. He says someone asked him to create a database. He says, “According to our documentation, the databases should have the following settings. Character encoding of UTF 8 or UTF 16 and the collation sequence is case insensitive.” You should just have him give you the exact collation that they want, because this still leaves all kinds of things up in the air. Just say exactly which collation do you want. If you look in Books Online, there’s a list of collations, it’s stunning. That should keep them busy for a while.

Erik Darling: Can you set UTF settings like that on a database? 8 and 16? I’ve never seen that part before. The only thing I’ve ever read is that the bulk bcp had some problems with like UTF 8 or 16, stuff that got solved recently but I don’t know. I’ve never seen that setting for a database.

Brent Ozar: No clue.

 

What should I set autogrowth to?

Brent Ozar: Sam says, “For new SQL Servers, what do you typically set your autogrowth settings to, out of interest? Do you consider VLFs in your decision?”

Tara Kizer: I absolutely consider VLFs in my decision just because I’ve run into excessive downtime as a result of having high VLFs.

Brent Ozar: Go on about that, what’s that like? The excessive downtime? That’s awesome. I know where you’re going with this.

Tara Kizer: This company applied the Microsoft security patches monthly. We were doing this with just about five SQL Servers on this one night. The most critical database of those servers, it was down. We had rebooted the server and SQL Server was up but the database was in crash recovery and I kept looking at the, I think it was the error log, it was frustrating. This application had really high availability SLAs. We were in a maintenance window but we weren’t allowed to have that kind of downtime. It came up after about 45 minutes. I was almost in the process of opening up a case with Microsoft to figure out what was going on. The next Monday I contacted Microsoft via email and that’s when I learned about VLFs. This was a little over ten years ago I’d say, but 45 minutes, and this was like a 7 terabyte database. The transaction log wasn’t even that big for that system. It probably was like, I don’t know, 200, 300 gigabytes. I monitor VLFs ever since that outage because it was such a big deal for this application being down that long. As far as what I set for autogrowth, it just depends on if I’m doing a new database and I know that this database is going to be small, medium, or large, then I’ll set the increments to adjust that. If I think that this database is going to be pretty sizeable, I’ll usually use 1 to 5 gigabytes for the transaction log file. But that depends upon how fast your storage is and how quickly it can grow it out since you can’t use instant file initialization for the log files. Data files I’ll set even higher since we do use IFI on those.

Erik Darling: I think the most important thing though is just to get away from percent autogrow.

Tara Kizer: Yeah.

Erik Darling: Because as your database grows, that’s going to be a larger and larger chunk of file to grow out. So as long as you set it to a fixed increment, I think you’re just always going to be in much better shape than percent autogrow.

Richie Rump: 1 meg.

Tara Kizer: 1 percent or 1 megabyte, I don’t know which one is worse.

Erik Darling: Yes.

 

Why are my VSS backups failing?

Brent Ozar: Graham asks a question we happened to be talking about the other day in the company chat room. “We have a 600 gig database that’s being backed up with a third-party app.” I’m not going to name which one it is because I don’t want these poor people to feel bad. “Our backups have been failing because the VSS writer has been freezing IO for 60+ seconds and the vendor support has not been helpful. What’s the best approach to address this?” I’ll say treat your servers like cattle, not like pets. When I’m dealing with a VM, I’m not even going to do VM-level backups. I’m going to back up my SQL Servers. I’m going to do regular SQL Server full and log backups and use a different technique like log shipping, database mirroring, anything like that to be ready to go to failover to. But I’m the kind of person who never wants to restore the OS. If something goes so horribly awry that I can’t trust the OS and I have to step back in time, I don’t trust that OS period. Who knows if last night’s snapshot is going to be any good or not. So I don’t like doing snaps on SQL Server VMs.

Erik Darling: The only time I’ve ever done anything close to that was when I had to [inaudible] machines and even then I was nervous as hell about—I was like, is this going to…?

Brent Ozar: Yeah. Graham is saying, “This is for this vendor’s SQL Server backup agent.” Yeah, I’m saying I don’t use those. I’m not a big fan of those.

 

Why is rowmodctr so far off?

Brent Ozar: Kyle says, “After letting stats maintenance lapse…” Well, we all have our downfalls. “I noticed that sys.indexes.rowmodctr of some stats is recording far more changes than the number of rows in the table. Like say the table has 100,000 rows and it shows 300,000 changes. Does that mean that nothing is querying the table because SQL Server hasn’t automatically updated the stats?” What a cute question.

Tara Kizer: [Laughs] Cute.

(Note from Brent – I don’t know why I used the word cute here, but that question was awesome.)

Erik Darling: You could also have it hit a threshold where—actually, never mind on that.

Tara Kizer: He doesn’t specify the values and the threshold on 2014 and older is 20 percent. So maybe you haven’t hit the 20 percent threshold. On 2016, it’s lower than 20 percent and Erik has a blog article on it. Can’t really tell you a fixed number for 2016, they changed how the thresholds.

Brent Ozar: Well, there’s a trick. He says that the rowmodctr is higher than the number of rows in the table which means it should have tripped the 20 percent. I can think of two things that would cause this. One is that the stats are only updated whenever you do a select, which could be that you’re right, that it hasn’t tripped the 20 percent threshold. The other thing is if these numbers are tiny. The metric was technically last time I looked 20 percent plus 500 rows. So if we were talking about a 100-row table, then that would also come into play there. Otherwise, I like that nothing has queried the table.

Erik Darling: Yeah, that would be my first instinct.

 

How do I reclaim space from a heap table?

Brent Ozar: Paul Olsen says, “How do I reclaim the data file space used by a heap table?” Did you delete it?

Erik Darling: If you mean you deleted data from there and you have a big empty heap, your options suck. You could create a clustered index on the table, which will fix things up. You can rebuild the table in, I think it’s 2008+, but you have to be real careful with that because when you rebuild a heap, you rebuild all the non-clustered indexes on it as well. That can be a really bad word situation.

Brent Ozar: You can truncate the table too but of course then you lose all the data in it. That deallocates all the space immediately, but if you have any rows, you’re screwed.

 

How should I capture DML events on user tables?

Brent Ozar: Steven says, “If you wanted to capture DML events on user tables how would you go about it?” There’s probably two parts to this. For readers who don’t understand what a DML event is and then also how would you go about capturing them.

Tara Kizer: I wouldn’t do it. The systems that I’ve supported have too much data coming in being changed to ever audit like that. Use could use triggers. Doesn’t CDC help you with that too? I’m not positive on that though. But triggers is how I’ve seen a lot of people do it but I can’t afford the performance of that kind of micromanagement.

Richie Rump: But triggers on DML?

Brent Ozar: Every insert, update, delete.

Tara Kizer: You send it to another, a logging table.

Richie Rump: Oh.

Erik Darling: If you do an after trigger…

Tara Kizer: I’ve implemented it…

Richie Rump: Yeah, just log it in the app, be done with it. Don’t worry about ad hoc stuff.

Tara Kizer: Then you’re missing DBAs coming through and wanting to find the data or whatever.

Richie Rump: Yeah.

Erik Darling: My first instinct is to go with an after trigger if it has to be done on the database.

Tara Kizer: Yeah, definitely.

 

Why did my logical reads change from 2008R2 to 2014?

Brent Ozar: Roland says, “In a simple join of two tables…” What in the holy…? I’m going to say this out loud, “In a simple join of two tables, the second table supplies always the same number of rows as the predicate is always the same as the same pk.”

Erik Darling: Who put the bomp in the bomp…

Tara Kizer: I need to see the data at this point and expected output.

Richie Rump: Stack Exchange would be great place for this question.

Brent Ozar: The only thing I’m going to say is we’re going to nibble out one part of this. He says, “Switching from 2008 R2 to 2014 the logical reads changed. How should I approach this problem?” What changed in SQL Server 2014 to change execution plans?

Tara Kizer: Cardinality estimator.

Brent Ozar: What is that and what does it do?

Tara Kizer: I’ll let you guys take that one. I just know the answer.

Erik Darling: It’s like one of those guys in Vegas who can count cards basically.

Brent Ozar: Hmm.

Erik Darling: So the cardinality estimator figures out how many—it tries to figure out how many rows are going to be returned by an operation based on statistics and histograms and cardinality and joins and all sorts of other fun things like that. In 2014, the cardinality estimator was completely redone for the first time since SQL Server 2000 or 7 or some old, ancient version. A lot of stuff changed. There’s a great white paper by Mr. Joe Sack about the cardinality estimator. I will grab a link for that as soon as I’m done talking unless one of my lovely cohosts would like to do that now. That is where I would go and start reading about it because that’s I think where you’re going to find the key to this. I would look at the execution plan as well as to see if something changed. It sounds like perhaps something got replaced with a nested loops join since he’s talking about one row is being read over and over again. That would be my first instinct but I would check out the execution plans and go read Joe Sack’s white paper about the cardinality estimator.

Brent Ozar: If you want to test that it is the cardinality estimator, on your database change the compatibility level. Change it back to say 2012 or 2008 R2. That will flip you back into the old cardinality estimator and you may get immediately a different execution plan.

Erik Darling: There’s trace flags on the blog too.

Brent Ozar: If the thing is already in production you shouldn’t be flipping the compat level back and forth. The reason why is because it’s going to clear your plan cache. So Erik’s trace flag idea is much better when you’re troubleshooting one query at a time.

Brent Ozar: All right, that sums up our webcast for this week. Thanks everybody for hanging out with us. I hope you had an excellent time. We will see you in the next Office Hours.