This week, Brent, Tara, Erik, and Richie discuss replicating TRUNCATE TABLE command between nodes, how to cope with social isolation that comes with working from home, database shrinking, how to improve log wait times, how to figure out the mac stop and threshold cost setting for servers, and laziest methods to deploy objects and run scripts.

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-12-07

 

How do you convince the SAN admin that there’s problems?

Brent Ozar: Michella asks, “How do you convince a storage administrator that the database is having IO issues?”

Richie Rump: Chainsaw.

Tara Kizer: It’s a tall order. I’ve worked at large companies with large DBA teams and large SAN teams and talking with them about IO issues can be challenging because in their monitoring tools, they’re not seeing the IO issues that we’re seeing on the server. So they point the finger back at the server, at Windows, at SQL. We point the finger back at them. It can take a lot of tools to figure out where the actual issue is. Now the one problem I have with the monitoring tools that the SAN teams use is that they are averaged over a minute. So they’re not seeing the heavy spikes that we might be seeing because maybe it’s fine 45 seconds for each minute but that 15 seconds is where we’re being hammered and because it’s getting averaged out, they won’t see it. One thing, I’ve worked a lot with SAN teams as well as Microsoft to figure out where the issue is. You can open up a case with Microsoft for help in determining if it’s an issue on a server or somewhere outside the server. They use, it’s an ETW trace that they can help you with and they can interpret it. It will tell you, is the problem on your server or external to it. Now, if it’s external to it, you might have to get other people involved besides SAN teams. You might have to put some kind of—I forget what they’re called—but they can put some packet captures on there to see where the problem is because it could be fiber, could be a lot of different places.

Erik Darling: I would run CrystalDiskMark and I would show the SAN team the results I get from that. We have a blogpost on our site, if you just search for brentozar.com for CrystalDiskMark or diskspd, you’ll get some primers on a couple different ways to test your storage out. Now I would actually turn your question around and ask you why you think you have a storage problem. Then I would ask you to meet your SAN team in the middle on the connections to the SAN because that’s usually what the problem is. The SAN is usually cruising along, the SQL Server is usually cruising along, but you usually have a really crappy either single connection or like not a very good double connection to the SAN from the server. That’s what I would do there.

Brent Ozar: We also have a request for a shout out to Rich Hansel. So there you go, Rich. Howdy. Oh, wow. The hotel housekeeping staff said to send you the house shout out. I said, “Yep, you’re doing a much better job cleaning the room.” That’s totally not true.

 

Should I worry about trace waits?

Brent Ozar: Next question. “I am seeing trace write waits among my top wait stats. Should I be concerned about trace write waits?”

Erik Darling: Not really. I would only be concerned if I had like double the trace time waits as up time, because that might mean that a couple people are running traces. But if my trace write time to my up time is kind of one-to-one, I’m not that concerned. It usually means that there’s a monitoring tool or something else keeping an eye on your server, or it’s kind of one of those like benign waits that’s not really going to do much.

 

Do we still need maintenance plans with SSDs?

Brent Ozar: Man, Troy has a great question. I think we’re all going to like chiming in on this one. He says, “Our network engineers are replacing our spinning disk storage with solid state. Our vendor says with solid state we don’t need maintenance plans. Can I really get rid of my maintenance plans?”

Tara Kizer: I’m not sure what they mean by maintenance plans. Are they referring to the actual maintenance plans that SQL Server is providing or they’re referring to something else? Because I don’t know how disks are going to determine if you’re using maintenance plans or not. Regardless of my storage, I’m using Ola Hallengren solution for instance or some custom solution and I just stay away from maintenance plans regardless.

Erik Darling: We like to say that index fragmentation doesn’t matter anyway, so no matter what kind of drives you’re on, who cares. But don’t get rid of all your maintenance plans because some of them might be taking backups.

Tara Kizer: Oh, you’re saying that they’re probably referring to the index maintenance.

Erik Darling: Maybe, I don’t know.

Tara Kizer: So is the question if they can get rid of the index maintenance plan? Or maybe, you can still use a custom solution. So I’m not sure what exactly they’re saying.

Brent Ozar: I hope it’s not that they’re talking about getting rid of backups. That would be bad, or CHECKDB.

 

Does Metallica’s new album rock?

Brent Ozar: Next comment is, “Metallica’s new album rocks if you care.”

Tara Kizer: I do care. Metallica used to be my favorite band.

Brent Ozar: As long as I can still hear the Black Album, that’s all I really care about because I could keep hearing that forever.

Richie Rump: Jay Z’s Black Album? Or…?

Brent Ozar: Anyone who has a black album, I’m into it.

Richie Rump: I’m listening to The Hamilton Mixtape right now, so I’m not paying attention to you guys, so, sorry.

Brent Ozar: I believe it. I would sing songs off of there but I haven’t gotten that one yet.

 

What happens when I truncate a table in an AG?

Brent Ozar: Next up, Vladimir asks, “What happens when I do a TRUNCATE TABLE with Always On Availability Groups? How does the TRUNCATE TABLE command get replicated between nodes?”

Tara Kizer: It’s still a logged transaction, it just deallocates the pages, I think. So you’ll still get the truncate on all your replicas.

Brent Ozar: And it’s quick, it’s still quick.

Erik Darling: Well, faster than a delete anyway, usually.

Brent Ozar: Yes.

 

Should I add more memory incrementally or all at once?

Brent Ozar: Ryan asks, “When I add more RAM to the database server, like from 64 gigs to 128 gigs, is there any reason not to increase the max memory parameter all at once? Should I inch it up incrementally or just go straight up to the number I want to end up at?”

Tara Kizer: Just go straight up. SQL Server is going to incrementally start using it.

 

Does Agent have problems with daylight savings time?

Brent Ozar: Someone asks, “Doesn’t SQL Server Agent have the smarts to avoid daylight savings time problems?”

Erik Darling: Like what?

Brent Ozar: Elaborate on the problems you’re having. I would be curious to hear more about that.

Tara Kizer: I used to work for a job where our servers had to use GMT time zones that we could do easy conversions for all the time zones where our customers were. So there was a lot of code that you just had to go into—we were actually using UDF to do the time zone conversions at the time. A lot of work had to go into it once the daylight savings time rules changed. Now that was all custom code, there was no issue with SQL Server. I think that maybe around the DST savings time there was a hotfix and then it got put into a service pack. So just make sure you’re not running really old builds of whatever version of SQL Server you’re using.

Brent Ozar: Because those are really common for us. Whenever we code Agent jobs to put our own date logic in there. So that could be some of what’s going on with that.

 

How do you cope with the social isolation of telecommuting?

Brent Ozar: Fellow human says, “I’m starting a new job where I work 100 percent remotely.” Congratulations.

All: Yay, yeah.

Erik Darling: Welcome to the dream.

Tara Kizer: Pajama [inaudible] coming up.

Brent Ozar: “How do you guys cope with the social isolation of working from home?” Oh, that’s such a good question. Who wants to go first?

Tara Kizer: I don’t cope. I just stay inside now. [Laughter]

Brent Ozar: You like the isolation. You embrace the isolation.

Tara Kizer: I go hiking a lot. So my hiking game has definitely increased since working from home.

Brent Ozar: You can leave the computer for a period of time and not be on call. That is what I think is the most [inaudible].

Erik Darling: We’re talking to customers pretty much all the time over WebEx though. It’s a little bit less isolated. Plus, you know, I got a wife and kid here so if I’m ever feeling lonely, I can just stick my head out the door. But generally, I don’t know, I always kind of liked the alone time anyway even when I’m kind of locked away in my office pounding away at stuff. I kind of enjoy the solitude.

Richie Rump: I think I’m the only really isolated one where I don’t talk to customers. This is our once-a-week, everybody, where we actually get face-to-face time with one another. This is probably about it. I have a wife and kids here. They’re here all the time. Twitter is a good outlet for me when I just need to see what’s going on and chat with some folks. The company chat room is pretty active, especially when I start stirring the pot and then people know that, “Hey, you guys are too quiet.” But I’m with Erik, I prefer the solitude. I can buckle down. I can really think about things. I kind of need silence for that, or at least really loud music to drown everything out. So it doesn’t bug me much at all. But then again, I do my own podcast where I talk with people about their lives and stuff. I’ll go to user groups. I have my own user group. So there are outlets for me that are outside of really work stuff.

Brent Ozar: For me, I’ve been telecommuting since I think 2003. For me, it was really hard initially because I’m very much a people person. I love being around people. So I started with co-working. If you search for co-working spaces in your city, you can get a desk in an office with a bunch of other people. What I love about co-working is they’re not tech people. They’re wedding photographers, artists, salespeople, independent freelancers, just all kinds of things. But I do so much work with clients and training-type stuff that I can’t really be in an open [inaudible] anyway. So I just like, the instant 5:00 hits, I’m out. I like walk out and around. I’ll take a walk, go get coffee. 4:00, really. It’s 5:00 somewhere.

Richie Rump: Actually, Brent is a people person unless you put him in a conference with 32,000 of them. Then all of a sudden he does not become a people person anymore.

Tara Kizer: “Hope they don’t recognize me.” Brent’s got the most famous face in all of the SQL Server world.

Brent Ozar: Wear a hat down real low.

Erik Darling: Get you some of those Kool Moe Dee shades.

Richie Rump: Maybe a Geordi VISOR, that’d be awesome.

 

Can I add tempdb data files on the fly?

Brent Ozar: Alexandra asks, “We only have one tempdb file and then we have 16 cores on one production server. Can I just add another seven files during business hours, and if so, what’s the recommended size for each of them?”

Erik Darling: I would say yeah, go ahead. I add tempdb files whenever I wanted. Never really bothered me. As far as the recommended size, as long as it’s going to match the size and the autogrowth of the current tempdb file, or else that one tempdb file is just going to kind of black hole all the queries if it’s larger. Then if you make the other ones bigger, they’re going to absorb the work. So same size and autogrowth as the current file and then add them whenever you want.

Tara Kizer: If you don’t have enough storage to add seven more files of that same size of the first file, what I tell my clients is add seven files, divide your total size of your first file by eight and add seven new files with that size, same autogrowth as the first file, and then go back and shrink your first file down so it’s the same size as the other seven.

Erik Darling: Database shrinking? Oh no!

Tara Kizer: Sometimes we do say shrink. Shrinking is bad but there’s some instances where you need to do it.

Richie Rump: Shriek. They meant shriek, databases shrieking.

 

More about the Agent job issue (and bugs in general)

Brent Ozar: We have a follow up on the Agent job question. He says, “My SQL job was supposed to run every ten seconds all the time but when daylight time fell back, it did not run for an hour.” Oh, that’s really interesting.

Erik Darling: Oh.

Brent Ozar: I would want to know more about what happened with it because it could be a lot of interesting things around like job history tables or whatever. But if you wanted to, I would post that at dba.stackexchange.com and include as much logging detail as you can because I haven’t run into that. When times fall back, it still continues to run every minute for example but it would be interesting to see.

Tara Kizer: You might need to open up a support case with Microsoft on that one. It might just be a bug.

Brent Ozar: I’m pretty sure it’s not. I’m pretty sure it’s just you.

Richie Rump: We’ve never seen bugs in SQL Server. That never happens.

Brent Ozar: One of my bugs got fixed today.

Tara Kizer: Wow, which one?

Brent Ozar: The one when you save an execution plan and then you go to click file, open recent, it’s not there.

Tara Kizer: Oh, I don’t even use that feature.

Brent Ozar: … every Connect item I’ve ever put in, that would be the lowest priority.

Erik Darling: It was also the easiest one.

Richie Rump: Yeah, the MRU list, that one is the easiest one. Sorry, guys.

Brent Ozar: Chris says, “I had a job where I was the only IT person and now I have a whole team of goofballs and I think it has lowered my stress dramatically.” Well you know, you can have people who are goofballs who aren’t in IT as well.

Tara Kizer: All the weight is lifted off your shoulders when you have lots of people doing the work.

 

Why is my query slow in the app, and fast in SSMS?

Brent Ozar: Larry asks, “We have a simple select from a view. It has two where conditions. When I run the same query from the query window, it chooses a different index.” So he’s saying when he runs it in the application, it performs differently than when he runs it in SSMS.

Tara Kizer: Brent, are you going to say your article, “Slow in the App, Fast in SSMS?”

Brent Ozar: Go right ahead.

Erik Darling: Mark it.

Tara Kizer: Oh no, you guys go ahead. I don’t know the name of the author. I mean, I know how to answer the question but take it away because I don’t know who has that web page.

Erik Darling: Erland Sommarskog.

Tara Kizer: Okay. What the issue is likely is that when you connect from an application, you have different set operations than when we connect in SSMS. So when I’m trying to troubleshoot why it’s slow in the application and fast in SSMS, I’ll run a, let’s say just a very, very quick profiler trace. Only leave the existing connections in there, that event, and then start it and then immediately stop it. Then find your user ID and then grab those set operations that came out of the profiler trace. Dump those into Management Studio. Then run your query in that same window. So that query window has the same set options as your application user. So when the set options are different, you will have your own execution plan and it can be different than what the app has. You can also get this through a DMV query, I believe, the set operations, but I’m just so used to doing a profiler trace with existing connections and only running it for one second. So it’s easy for me.

Erik Darling: Also, if both of the plans are in cache and you can catch them with sp_BlitzCache, if you scroll all the way to the right in BlitzCache, it will give you the set…

Tara Kizer: It does? Wow, I didn’t know that.

Erik Darling: Yeah, no one ever scrolls that far.

Tara Kizer: I only ever scroll to the right to look for the cache time and last executed and the execution plan. I pretty much ignore columns, I should start looking I guess.

Brent Ozar: I forgot it was in there until this week.

Erik Darling: You should see what happens when you put in expert mode equals one.

Tara Kizer: Oh. See I didn’t even know it had an expert mode.

Erik Darling: Yeah.

 

Why are my tempdb data files in use?

Brent Ozar: Bert says, “I’m trying to reduce the number of tempdb data files I have. I had four, then I tried six, and now I want to go back to four but every time I try it, SQL Server tells me the file is in use, even after a reboot.”

Tara Kizer: Probably have to run alter—whatever it is—empty file. Yeah, it’s shrink command. You have to specify the empty file. It will empty the file and it will no longer be used and then you’ll be able to remove that file.

Brent Ozar: Yeah, there’s also a—one of you guys told me—blowing the plan cache. You can have a plan cache issue. Was it you, Erik? Or was it Tara?

Tara Kizer: It was me. I encountered that at my last job. So yeah, there’s a blog post on that. But the shrinking wasn’t working I believe. We couldn’t shrink it down and had to [inaudible] the procedure cache.

Richie Rump: You can’t blow in it like an NES cartridge?

[Laughter]

Erik Darling: You can, but you may have mixed results.

Richie Rump: You have to do it again, and again, and again.

 

If we have high log write waits, what do we do?

Brent Ozar: Someone says, “Thanks for the Christmas card. If we have high log wait times on an app and we can’t change the code,” it’s relativity, they say. So I’m teaching a class in here this week and immediately one of the guys from [inaudible] pops up, “what?” “Any suggestions on how to improve this?” So it’s long wait times, and he says log wait times. “I’ve read it helps to commit less often but since I can’t change the code, is there anything I should do?”

Erik Darling: Get faster drives.

Brent Ozar: One of the reasons why stuff like relativity will have high log wait times is you’re writing a lot of stuff from the database. You’re doing lots of inserts which take a long time to write. You could get faster transaction logs. You could—I have an awful idea, but I’ll wait to see what Erik is going to say.

Erik Darling: My whole thing for that is just the faster disk because you’ve got lawyers going through and updating one thing at a time and that triggers the audit record insert and then there’s just a whole bunch of other stuff going on under the covers when they change something, redact something, and make a production. There’s a lot of stuff that happens whenever you change one thing. You’re not just changing one thing. There’s a lot of little other things going on. So the faster drives is always a good one for me. But I’m sure Brent has something nefarious to say.
Brent Ozar: I have a really bad answer and you shouldn’t quote me on this and surely it’s going down in the recording, but if you’re on SQL Server 2014…

Erik Darling: Agh.

Brent Ozar: Yeah, right? … delayed durability, which means your inserts, updates, and deletes are consider committed as soon as SQL Server hears them. They don’t even harden to disk. Now, this is so bad for two reasons. One, you can lose data when you’re not expecting it, like when SQL Server fails over. Whenever there’s a failover, anything that didn’t make it to the disk to log file, you’re out of luck. Second, you could lose data even on a graceful shutdown. SQL Server does not flush the log file to disk when you do a graceful shutdown. So this really is like a last resort if you didn’t care about the data. And when it’s relativity data, I would find it very hard to believe you would not care about that data.

Brent Ozar: Jackie asks, “How do you guys calculate…?”

Richie Rump: Wow.

Brent Ozar: Right, I know, see? And aren’t you proud to work with me? This is why I’m not allowed in front of people very often.

Richie Rump: It’s like how do NoSQL SQL Server. Hmm.

Erik Darling: You know what? They’ll put out that as a feature but they won’t put out un-log tables as a feature. They’ll un-log everything but you can’t just choose a table for it. What a–.

Brent Ozar: Either you care about nothing or you care about everything. There’s nothing in-between.

 

How do you calculate MAXDOP and Cost Threshold for Parallelism?

Brent Ozar: Jackie says, “How do you guys figure out the MAXDOP and cost threshold settings for servers?”

Richie Rump: 20-sided dice.

Erik Darling: That’s not a bad answer.

Tara Kizer: We have recommendations but it’s going to be dependent upon your server. We recommend cost threshold for parallels be 50. Five is the default and it’s a really old value and we think Microsoft should really raise it but they like backwards compatibility. MAXDOP, maybe half of your processors, up to eight, usually don’t go over eight. Then if any queries that you have are suffering with those server settings, then you can add the OPTION MAXDOP to that individual query to affect it, maybe to use less processors or maybe even more.

Brent Ozar: You’ve got lots of options once you start playing around. You can even do things with like resource [inaudible] guides but of course that takes much more work. So the 50 and up to eight works really well.

Erik Darling: You know what, there’s actually a good script on dba.stackexchange.com, like how to calculate MAXDOP. I think that guy Ken wrote it and it’s just a script that you run and it basically gives you like however many cores are in a single socket up to eight. So if you have dual sixes or dual fours, it will give you six or four.

Brent Ozar: If you happen to find that, make yourself a note and send it to me because I’m doing like an all-Stack week’s link for next week.

 

How should I deploy changes to my database?

Brent Ozar: Next one, “I use a SQL command script to run deployment scripts.” Whoa, my god, my condolences. Richie immediately is even like backing up. Tell me more.

Erik Darling: Are you on Express Edition? Why are you doing that?

Richie Rump: Oh no, don’t tell me more, not at all. No, I don’t want anything to hear about this.

Brent Ozar: “I hear there are deployment tools that do sync and compare and I’m looking for the laziest method to deploy objects and run scripts.”

Tara Kizer: I don’t know, SQLCMD is easy. You’re just typing in a command and you’re running a SQL file through it. But as far as what other people have used, my last job used Visual Studio’s schema compare to come up with the scripts and then we would just run those in Management Studio. But I have used the SQLCMD method, not because I chose it but because that was what was delivered from development. That works fine too. It is a super easy way to do it, but who’s writing the SQL script? If you’re the one writing the SQL script, are you asking, how are you generating all the commands to compare, to development, to production so you do the deployment? Visual Studio’s schema compare does that as well as Redgate has a schema compare as well.

Richie Rump: Redgate is the really good one. Visual Studio is the free one. I’ve used both with good success on both. So have at it.

 

Is there a contemporary music artist you all like?

Brent Ozar: Same person continues their question with kind of a horrifying question, “Also speaking of music…” We weren’t speaking of music, but that’s okay, I’ll allow it. “Is there a contemporary artist you all like?” That is definitely a no.

Tara Kizer: Contemporary. [Laughter] I don’t know if we agree on anything.

Richie Rump: You should have seen us in Austin, man. It was not pretty. It was not pretty at all. Red Hot Chili Peppers came on and like half the room cleared out.

Tara Kizer: I had to leave.

Richie Rump: It was almost fighting words.

Tara Kizer: That’s like the worst band ever. I like that kind of music, that genre, I just do not like the Red Hot Chili Peppers.

Brent Ozar: Metallica might not be that bad of a shot. Richie? No, no on Metallica.

Richie Rump: No, I have to draw a line somewhere.

Erik Darling: I’m with you. It’s like pre-Black Album is all I can deal with.

Tara Kizer: That’s how I am too, but I say they were my favorite band, they are no longer. I kind of liked Richie’s playlist that he shared with us a few months ago in a Spotify list. It was a bunch of 80s music, so I’ve actually listened to a bunch of that stuff. It’s a really good list.

Richie Rump: Actually, that was Erik’s. That wasn’t mine. I was doing the alt stuff. That was Erik’s.

Tara Kizer: No, no, it was yours. Maybe it wasn’t 80s then. I just can’t remember what genre it was.

Erik Darling: I have never shared a Spotify playlist in Slack because I don’t want you guys poking around my other playlists.

Richie Rump: Mine is really easy now, it’s just like been all Hamilton stuff because the kids have been singing it nonstop so the past couple weeks have just been that.

 

Can you explain what reads means?

Brent Ozar: This one is interesting. “Can you explain to me what specifically this means?” I believe this has Richie all over it.

Tara Kizer: Stats IO output.

Brent Ozar: Scan count, some number, logical reads, some number, physical reads, some number, and read ahead reads, some number. I think those are lotto ticket suggestions is what that is.

Richie Rump: Yeah, we may win something here.

Brent Ozar: Yeah, so you read a table multiple times. Logical reads means you read those 8k pages out of memory. Physical reads means you read some of those pages out of disk. Read ahead reads means SQL Server saw you reading a bunch of pages off of disk and started reading in front of you even further.

Tara Kizer: I recently read that read aheads were an Enterprise Edition feature, is that true?

Brent Ozar: Standard will read ahead, just not as far as Enterprise Edition.

Tara Kizer: Oh, okay.
Brent Ozar: So in terms of what it means, you only read 28,000 pages, like the logical reads thing. So that actually doesn’t scare me that bad. That shouldn’t be terrible. Scanning isn’t necessarily bad either, so I wouldn’t just go based on that.

Erik Darling: Well the scan count isn’t actually scans, it’s just index accesses. So it’s not like you scanned the whole index that many times. If a query goes parallel, you can have multiple things reading from it and you get a whole mess of scan counts that aren’t actual full index scans over and over again. It’s just chunks of the index.

 

Why is CPU time low but elapsed time high?

Brent Ozar: There’s an interesting one. Kenneth asks, and I’m going to rephrase his question a little. Kenneth says, “I have a query where CPU time is only a couple seconds but elapsed time is 30 minutes. What are some things that could account for that?”

Erik Darling: Compiling.

Tara Kizer: [Laughter] That would be a lot of compiling, dang. We’d need to see what you’re waiting on, probably being blocked I would imagine, but you’re waiting on something. During that 30-minute window, check what you’re waiting on.

Brent Ozar: Disk could be reading from disk, but I like the locking thing a lot.

 

Can you use Standard Edition for development?

Brent Ozar: Sam asks, “Can you use SQL Server Standard Edition for development? If so, do you simply license your developers with MSDN?” Developer Edition is free, so I would just use Developer Edition instead. Stick with that one.

Richie Rump: But you could license with MSDN too. I think those images are up there as well. So if you say, “We really don’t want any developers touching any Enterprise Edition stuff,” you could do that.

Erik Darling: I can’t confirm but I’ve heard that if you are a Standard Edition shop and you have like a thinner edition on a laptop, Microsoft is not going to make you license it like Standard Edition, but you know, it’s up in the air a little bit. I wouldn’t bet on it.

Brent Ozar: But Erik’s answer is due to the fact that he’s covered in tattoos and when the licensing person came to audit, they just kind of were like, “Yeah, no, no, you’re good. You’re all right.”

 

Brent Ozar: The follow up on the SQL command deployment scripts, someone says, “I hate SQL command for deployments because it only returns one line of output. If there’s an error further down the script, you’d never know.” That’s a great point.

Tara Kizer: I haven’t seen that occur though. I’ve seen lots of outputs. I’m wondering if it’s maybe just the command. So you can use the -o, I think it is, parameter, to put that into a file. I haven’t seen that before.

 

How can I prevent a SELECT from blocking?

Brent Ozar: J.H. says, “Any methods to prevent blocking from a select statement?”

Erik Darling: From a…? Oh, so like, do you mean like the schema stability lock that it takes?

Brent Ozar: It might even be just pessimistic locking, where the lock is taking forever.

Erik Darling: Yeah.

Brent Ozar: What are some of the ways you could get around that?

[Crosstalk]

Erik Darling: Not even no lock prevents…

Tara Kizer: That’s true. I wonder what they mean though. They’re probably talking about the select is blocking other connections and maybe it’s not just a schema stability lock.

Erik Darling: Could be, not sure though.

Tara Kizer: Need more info.

Brent Ozar: If you search for isolation levels on our site or if you go to brentozar.com/go/rcsi, which is funny, it’s just a module I just got done presenting in class here. We have a whole section just of resources on that that you might be really interested in. The big takeaway is readers don’t block writers and writers don’t block readers.

Erik Darling: But writers will still block writers.

Brent Ozar: Yeah.

Brent Ozar: We’ll take one more question. I’m going through the ones that are 15 lines long and past that. Someone says, “Yes, do not use locks.” So follow up on the select thing. This person is doing an insert. He’s doing an insert into a table using select from. So you could do with no lock on your select thing. You just know you’re getting dirty reads, so you could see rows twice or not see them at all, or your insert could fail.

Erik Darling: I would like to know how many rows you’re selecting because if you’re selecting a lot of rows, that could lead to higher level locks. There’s a great a blog post by Michael Swart about batching modifications. I’m going to find a link to it and I’ll put that in chat. It’s a really good read about how to batch things properly. So if you’re selecting a lot of rows, you could be getting some weird locking patterns from that as well.

Brent Ozar: Perfect. All right, thanks everybody for hanging out with us this week. Head over to GroupBy.org. This is a totally free conference where you get to pick the sessions. Voting is going on right now. So go to groupby.org, pick what you want to see, and then we’ll be taking the top ten or top five to seven and we’re running those in January. Thanks, everybody. See you next week.