This week, Richie, Erik, Angie, and Tara discuss deadlocks, replication, SQL Server 2016 features, and more.
You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.
Transcript:
Does NVARCHAR(255) Cost More Than NVARCHAR(30)?
Angie Walker: All right. So Steve has a nvarchar(255) column that he knows will never have more than 30 characters. Will it take extra space to store this column and will there be a performance penalty?
Tara Kizer: It’s already using double since you’re using the nvarchar and not just varchar. It’s Unicode so it’s going to be using 60 bytes instead of 30. But is it going to use more space? No. I’ve seen developers want to do this where they want to have every single column standardized to the same data types and size. I don’t understand that reasoning except for laziness. It doesn’t take long to figure out what each column should be. I mean what happens if it ever…
Erik Darling: My only issue with it as like from a development standpoint is SQL error messages for if you try to insert too much data into a column suck. It’s like “string or binary data might be truncated.” And you’re like, oh, I’ll just go figure out which column that was because the error message doesn’t tell you. Nothing tells you. So you have to go back and guess a million times. So I understand why developers are like, “Let’s just make this a 255 because it’s a variable column anyway. It’s not going to mess anything up.” But, you know, figuring out the right length should be a priority at some point because you may have inserted incorrect data into a column if you give it too long of a length if it’s more than it will ever be or more than it could ever reasonably be. But I understand why developers want to do it because tracking down those errors sucks.
Why Is Perfmon Wrong or Missing Counters?
Angie Walker: Okay, Jason says their performance monitoring tool is showing that OS CPU is around 25 percent. The instant CPU that they’re getting from a DMV is running around 80 to 90 percent. “It has been suggested our counters are messed up and to unload and reload these counters. Any experience on doing that?”
Tara Kizer: I have many, many years ago. I don’t remember what it was but back in the day there were lots of issues with performance counters. I just don’t remember what you have to do. You could just restart SQL Server or the box to possibly fix this issue. That was one of the solutions back in the day. But yeah, you’ve definitely got something messed up here because the ring buffers should show the same CPU utilization as what Performance Monitor or Task Manager is showing.
Erik Darling: Yeah, I’ve had to reset them a couple times. Actually when you clone a machine and you turn into a VM a lot of performance counters get screwed up. Like a lot of them just don’t even show up anymore. So I had to like mess with loading and reloading them. If I remember correctly, they were pretty simple DOS commands.
Tara Kizer: Yeah, yeah.
Erik Darling: But they weren’t like… you know.
Tara Kizer: Yeah, I don’t remember what they are.
Erik Darling: I lost my notes on that, sorry.
Richie Rump: Sounds like a blog post, Erik.
Erik Darling: You know, Richie, your blogging has been pretty light lately. If you want to take that, it’s all you.
Richie Rump: Yes, yes.
Erik Darling: I leave that in your capable hands.
Richie Rump: But I’ve been working, so, there’s that.
Erik Darling: Yeah, see all those pictures from Disneyland, hard worker.
Richie Rump: Disneyworld.
Erik Darling: Whatever.
Richie Rump: Disneyland is a different place.
Angie Walker: Folks, if you want your questions answered. You have to type them.
Erik Darling: I see one here about deadlocks.
Angie Walker: I saw that one.
Richie Rump: That movie was great. I loved that movie.
Angie Walker: The difference between Jessica and I reading it, I know when you guys aren’t going to want to answer some of these.
Tara Kizer: You can go ahead and ask it.
Do File Growths Cause Deadlocks?
Angie Walker: All right. So for deadlocks from Adeels Webb, “When there is a file growth we see deadlock and the object identified is one of the indexes. Is there a way to debug this related to storage or IO?”
Tara Kizer: I would be looking definitely at your IO. I mean how long is that growth taking and if it’s on the data files, do you have Instant File Initialization setup because if it’s not setup then it has to zero out the file and that can talk awhile depending on how slow your storage is. The log file, you can’t use Instant File Initialization but the data files can. So take a look at the perform volume maintenance tasks inside the local security policy on your box and see if the SQL service account is a member of that privilege. If it’s not, you should add it and restart SQL.
Erik Darling: Or in your maintenance window. Not like…
Tara Kizer: Yes, not now.
Erik Darling: Not like right now. Don’t tell your boss we told you to do it right now.
Tara Kizer: I would also be looking at performance monitor counters average. Look at the logical disk counter. The average disk seconds reads and writes and if your values are over say 20 milliseconds you potentially have a storage, an IO slowness issue. The values are going to be in decimal though. So it’s .020 I believe is 20 milliseconds.
Angie Walker: Whatever she said, I don’t…
Erik Darling: Sounds good to me.
How Do You Pronounce VARCHAR?
Angie Walker: Yeah. Here’s a good one from Greg. “Is it ‘var’ ‘car’ or ‘var’ ‘char?’ What do you guys say?”
Tara Kizer: I actually say “var” “char” but since you guys had already said “var” “car” I went with it.
Richie Rump: Wow.
Erik Darling: I always say “var” “car” because the variable is character.
Tara Kizer: Yeah, exactly. Yeah, I do say “var” “char” but I see why people say “var” “car.” I think most people do say “var” “car.” I’ve worked with a lot of developers and DBAs in the past 20 years and most people say “var” “car.”
Erik Darling: In Boston, it’s [speaking with a Boston accent] var car though.
Angie Walker: I also recently heard that it should be “vare care” because variable characters. So like you were saying, Erik, but I was like that sounds funny. Like it sounds like a Care Bear character maybe.
Tara Kizer: I think the real question though is do you say Sequel or S-Q-L? That’s an important one.
Erik Darling: Sequel, I don’t have time for S-Q-L.
Tara Kizer: Microsoft is the one who created the product and they specifically said on the Wikipedia page it was pronounced Sequel and not S-Q-L. People from different languages had a problem with it because when they see the letters S, Q, L, it does not pronounce “sequel” to them. So Microsoft changed the Wikipedia page to say it can be pronounced either way. But the original product was pronounced “Sequel” Server.
Angie Walker: Interesting.
Tara Kizer: There was this big debate on the Wikipedia page, I don’t know, like ten years ago or so.
Erik Darling: Which brings us to another interesting question, is it “wi-ki” or
“wee-ki?”
Angie Walker: Wiki. What about “DAY-ta” or “DA-ta?”
Tara Kizer: “DAY-ta.” “DA-ta” drives me crazy.
Richie Rump: Captain Picard called him Data. So it’s “DAY-ta.” Captain Picard is always right.
Angie Walker: I like “DAY-ta” too. It also sounds kind of funny “DA-ta” base because of the off “a” is…
Are There Any Alternatives to Transparent Data Encryption?
Angie Walker: All right. Adeels has another question. “What do you guys think about TDE with mirroring or replication and are there any alternates to TDE?”
Erik Darling: Not within SQL Server. TDE is what you get and that’s an enterprise-only feature. If you use it alongside any other feature, it’s going to be interesting because TDE breaks a lot of stuff. TDE, it encrypts TempDB and it also breaks in some file initialization because you have to write out a bunch of junk. So it’s totally fine to use the features together, just be aware of how they operate next to each other.
What Happens If I Run Standard Edition on 20 Cores?
Angie Walker: Sean says they have a server with 20 cores but they’re only running Standard Edition that only supports 16 cores. Is there a negative performance impact with this configuration?
Erik Darling: There is if you have to cross NUMA nodes for some things.
Angie Walker: So when would you see that scenario? Do they have to have a specific of NUMA nodes for it to come into play or they already have too many NUMA nodes?
Erik Darling: So is it, how many CPUs? I get that it’s 20 cores but how many…?
Angie Walker: So we have two CPUs.
Erik Darling: Oh, wait, wait, wait. Hold on, yeah… VM 1 uses 20 cores…
Angie Walker: No, the one below that.
Erik Darling: Oh.
Angie Walker: The one from Sean. Yeah, I didn’t read the giant one.
Erik Darling: It depends, Sean. Two CPUs, so no, probably not. But it’s not ideal. It’s not something that I’d aim for. It sounds like someone bought like the dual ten core CPUs thinking that they were going to be really fast and awesome but they’re probably like a really low clock speed or something which is what dual ten core CPUs are. So for something like Standard Edition, we usually recommend getting like dual two four six or eight, whatever the highest clock speed is. Because you go up to 16 and you get the fastest processers to push your workload through. Dual ten core chips usually have a much lower clock speed and kind of stink.
Angie Walker: Yeah. I’m just going to follow up, Dennis, sorry, sad face, that license question. That’s a big license question. We’re not Microsoft. We don’t charge you so I would talk to your Microsoft rep or your software vendor rep. Ask them how you’re going to get charged. Someone else, sorry. But there was a question.
How Should I Document SQL Server and Scripts?
Angie Walker: Oh, I think Richie I want to hear from you from Brandon’s question. “Do you recommend any tools for documenting changes in SQL scripts? How about documenting SQL Server? Right now they use Excel and a lot of worksheet tabs to document their SQL Servers.”
Richie Rump: So I don’t have any recommendations for any tools to do this. We always had, at least every organization I’ve been in, we’ve had pretty rigorous change control. So all the scripts would be not only put into a version control system like Git but they would also go down to test, preproduction, and then finally a production area. So we usually didn’t have that big of a problem because all the changes were being tested as it went down the train, the pipeline. So as far as documenting SQL Server, I know there’s a couple products out there. I would just try them out and see how they work for you.
Erik Darling: Yeah, Red Gate has a tool called SQL Doc that works all right.
Richie Rump: Yeah, I’ve written kind of my own tools as I kind of saw fit. But we didn’t have any really big documentation requirements either. So it depends on your requirements and your budget and how much you’re willing to put into it.
Erik Darling: I bet someone out there who really likes PowerShell and really wants to tell you all about PowerShell has written something that would document SQL Servers.
Richie Rump: Yeah, there’s a guy here in Florida, a couple hours up the road here. He spent many years working on a PowerShell documenter, so that’s probably a good one to check out.
Erik Darling: There we go.
Richie Rump: Kendal Van Dyke. Kendal Van Dyke’s, what’s that? SQL Power Documenter or something like that?
Erik Darling: Power Doc is it?
Richie Rump: Power Doc, that may be it.
Erik Darling: Maybe.
Richie Rump: Because everything was being…
Erik Darling: There’s someone walking by your window.
Richie Rump: Dude, it’s the mailman. Oh my gosh.
Angie Walker: He’s already that way.
Richie Rump: He’s at my door. So…
Angie Walker: Wait your dogs are going to start barking.
Erik Darling: There he goes again.
Angie Walker: Okay.
Erik Darling: All right, who’s next?
Can I Monitor for Changing Execution Plans in 2008R2 and 2014?
Angie Walker: So, Jason. He wants to know if there’s a way to monitor when SQL decides to change plans or use a bad plan. He knows in 2016 they’re introducing Query Store but what can he do for 2008 R2 or 2014?
Tara Kizer: I’ll tell you what I implemented at the job I was at for 12 years. We had a very very critical system. We’d have severe performance issues if bad plan would happen for a critical store procedure. Every single time I’d just recompile the store procedure and the entire system would start performing better because the bad plan would cause really high CPU. So what I did is I used the ring buffers DMV that was mentioned in an earlier question and I wrote a store procedure to query that and to monitor CPU utilization because I knew that CPU utilization would remain at say 30 percent during the day when this issue didn’t occur. But it would go above 60 percent, 80, 90 percent. So I would monitor CPU utilization and then check the number over like three minutes. If it’s at a high number across several samples, I would then look at the plan cache. What was using the most CPU in the plan cache and then recompile that object. Then it would wait a minute and then check to see if it improved. If it didn’t improve, it would then recompile the next one at the top of the CB list. That caused it to not have to wake me up in the middle of the night or not have to manually recompile store procedures. So you do have that option, the ring buffers DMV gives you CPU utilization and you just write code to do this work for you.
Erik Darling: If monitoring the ring buffers is difficult or beyond your gasp, sp_BlitzCache can help a lot with that. So what you’ll see, you run sp_BlitzCache by CPU. You may see a line for your store procedure and then you may see a separate line for the text of the store procedure that has higher average CPU or reads or something or max worker time or something like that. As like just sort of different averages that make you think, “Okay, this statement may have gotten the wrong plan or something is amuck because this store procedure has these numbers but the statement has these numbers.” So you could see some differences there. If you’re feeling really fancy, in 2014 you can also use Extended Events to capture when plans recompile. I wouldn’t grab query plans along with it maybe because that’s a dodgy enterprise in Extended Events but it certainly is an option.
Tara Kizer: But maybe storing the results of BlitzCache into a table and then comparing the average CPU average reads and if it’s off by a certain percentage, then you possibly know that a plan difference is enough to have caused an issue and that you have a bad plan.
Erik Darling: Yep. There’s some like really interesting parameters in sp_BlitzCache that I’ve never used. Like you can set up variances for like the difference between those things. If it’s over a certain amount it will warn you about it but usually there will just be sort of a general warning for parameters missing where it will tell you all about that.
Does Replication Work in Amazon EC2?
Angie Walker: Good information guys. So Mike says that they’re addicted to replication, sorry, Mike. There might be pills for that now. But they’re moving to AWS EC2. “Any comments on replication in the EC2 section of the cloud?”
Tara Kizer: I don’t have any specific experience with this but I would probably just be concerned about latency between the publisher and the subscriber. Or I should say between the publisher and the distributor and the distributor and the subscriber. Because the publisher doesn’t connect directly to the subscriber goes to the distributor. Make sure that it’s flowing nicely because any kind of backlog on the distributor subscriber or publisher can cause for you to take production down if you run out of log space where all the replication log records are being stored in the publisher log file.
Erik Darling: Yeah, one thing about EC2 instances is that you have to pay for a pretty large box before you get over the initial networking bandwidth of 125 megs a second I think. So if you’re really pushing a lot of data across, I would pay a lot of attention to the type of box and monitor how much network bandwith and utilize and all the other stuff and just6 any latency, I would really want to keep really close eye on the latency whether it’s network or just between all the boxes.
Does Brent Still Work Here?
Angie Walker: I saw that Richie. Did anybody read the permanently storing objects in TempDB blog?
Erik Darling: No, I only read my own blog posts, sorry.
Tara Kizer: Who wrote it? Was it one of ours?
Angie Walker: It went live today.
Tara Kizer: Okay, then I didn’t read it.
Erik Darling: Brent wrote it.
Angie Walker: I didn’t get to it yet. I’m backlogged on all of Erik’s while I was gone.
Erik Darling: Not “me” Brent. Not Erik. Real Brent wrote it.
Angie Walker: The real Brent wrote it.
Richie Rump: He still works here?
Angie Walker: Yeah. Well… define work.
Erik Darling: He shows up in chat once in a while.
Angie Walker: Oh no, that’s Erica.
Richie Rump: Yeah, that was Erica.
Tara Kizer: Oh, I did read this before it got published. Is there a specific question on it?
Angie Walker: Just what do we think about it.
Tara Kizer: Oh, I mean, I don’t think that TempDB should be a place where you store objects permanently. If you need to store objects permanently, setup a database for it.
Erik Darling: I think, great post, Brent.
Angie Walker: Or Brett.
Erik Darling: Great post, Brenda.
What’s Your Favorite Missing Feature in SQL Server 2016?
Angie Walker: All right. Well since we really have no more questions and seven, eight minutes left, we’ll follow up with Brandon’s “if there are no questions” question. “Does anybody have a favorite feature from SQL 2016 or is there anything that you wish made it into 2016 that didn’t?”
Tara Kizer: I think we can probably all agree to the Query Store. We’re all looking forward to using that but it appears to be an enterprise edition only feature which…
Erik Darling: No, they changed it.
Tara Kizer: They changed it again?
Erik Darling: They changed it.
Richie Rump: Yeah, they announced it. They changed it and they changed it back.
Tara Kizer: They changed it again? Okay.
Angie Walker: So it’s for everybody now?
Erik Darling: Real ding dongs.
Tara Kizer: The product hasn’t RTM’d yet. So June 1st, so they could change their mind again.
Angie Walker: That’s coming close though. Less than 30 days.
Tara Kizer: Yeah.
Erik Darling: So what I’m consistently mad at Microsoft about is their restore stuff. Microsoft spends a lot of time and money investing in like Oracle competitive checkboxes. But we still have the same, clunky, all-or-nothing restores. [Inaudible 00:16:54], right? It’s like if you want to restore like a table, you have to restore the entire file. There are third-party tools you can do object-level restores, you know? Like Dell LiteSpeed and probably some other backup software. Tara, does Red Gate do that object-level restore stuff?
Tara Kizer: I know it used to.
Erik Darling: Okay, so, maybe it still does. But I get continuously annoyed. Especially because Microsoft has embraced this, you know, “We’re going to support you using petabytes and petabytes of data.” But if you take a backup of that and you have to restore a table because some ding dong broke 50,000 rows in part of a table, then you still have to restore your entire database. There’s no object-level restore natively with SQL Server. There’s no way to natively read through a log file in SQL Server without memorizing those crazy fn_dblog and dump_dblog commands where you have to pass into default 64 times. There’s no good, intuitive way to figure out when something bad happened and restore it to that point. Oracle offers stuff like Flashback where you can flashback a table to a point in time. You can flashback an entire database to a point in time. You can do all this stuff and get really easy, really restorable data. You just get all your stuff back really easily. I think it’s sort of obscene that Microsoft is still making you restore a 5TB database just to get one table back.
Richie Rump: And that’s Erik’s favorite 2016 feature that’s not there.
Angie Walker: Yeah. What about you, Richie, since there are still no questions, is there anything from a developer’s side of things that you wish there was? Or you don’t really care about?
Richie Rump: It’s not like 2012. 2012 we got a lot of good goodies. In 2014, there was nothing for us and then 2016, it’s the Query Store, right? A lot of people talk about the JSON stuff. I am not thrilled with it. I haven’t really played with it too much but it’s just going to make things easier to go in and out but I don’t like the XML data type or XML stuff in SQL Server so why should I like the JSON stuff in SQL Server? It just doesn’t feel right. It’s just something else that us as developers can screw up. So it’s probably one thing that I’ll be keeping an eye on over the next few months is the JSON data type and kind of how we could use some of that responsibly and not like in the way we’ve seen some XML data types go awry.
Erik Darling: Yeah, like what developer bones got thrown in 2016 like what DROP IF EXISTS and the string splitter. That was it. Like there’s been no like further improvements to window end functions like making a using range over rows, you know, not be horrible. So no like further improvements to T-SQL to make it more ANSI compliant or add in more like the ANSI standard stuff to it. So it’s pretty underwhelming to me from at least from a development standpoint.
Richie Rump: Yeah, and if us as a community aren’t screaming about it, then it’s going to be low on the totem pole. So I think there was a fair amount of people screaming about the JSON stuff because practically every other database has JSON compliance.
Erik Darling: Practically every other database has a way to concatenate comma delimited strings without using XML path in some convoluted voodoo language too.
Richie Rump: You can always just use .NET for that, dude, come on.
Erik Darling: Yeah, I have .NET, Richie. Me.
Richie Rump: That’s why I’m here, right? That’s why I’m here. No other reason just not to write .NET.
Erik Darling: Just to make my string concatenating life easier.
Richie Rump: That’s right.
How Do You Verify Your Backups?
Angie Walker: We finally got a new question. It’s from Adeels again. He says he understands that restoring and verifying backups is the way to go. So good for knowing it. But he says it’s not always physically possible. Is doing RESTORE VERIFYONLY good enough or do you have another recommendation?
Erik Darling: Good enough for what?
Angie Walker: I think he’s trying to say if he can’t test his backups by restoring them somewhere else, is it okay to just do RESTORE VERIFYONLY and say that your backup is good and not corrupted or something?
Erik Darling: I mean all that does is test the header and makes sure that it’s a usable backup file. It doesn’t actually test the contents of it for anything. So, it’s fairly reasonable to assume that you can restore that backup. That the header and the format of the backup file are correct. The data within that could still be bonkers.
Angie Walker: So still run your DBCC CheckDB, right?
Erik Darling: Run your DBCC CheckDB, turn your page verification on, make sure your backup checksums are on. Lots of stuff to do there. Make sure that you’re getting alerts for your 823 824 and 825 errors. Other things.
Angie Walker: We have that on the web.
Erik Darling: We do.
Angie Walker: On the blog.
Erik Darling: If you go to BrentOzar.com/go/alerts. We have that all setup for you.
Angie Walker: Yeah, some good stuff out there.
Erik Darling: At least the alerts end.
Angie Walker: We’ll tell you how set them up.
Erik Darling: Yeah, basically.
What Tool Should I Use to Read Execution Plans?
Angie Walker: Sean wants to know if there’s a better program that’s free to analyze execution plans.
Erik Darling: Where you have been? SQL Sentry Plan Explorer.
Tara Kizer: That’s what we use. That’s what we use here. It’s what we used at previous jobs too.
Erik Darling: What do you using, Toad? I don’t know.
Richie Rump: That’s still a thing?
Erik Darling: I guess, yeah. I mean there’s still a Toad World website. I don’t know. Maybe someone with MySQL uses it.
Richie Rump: Maybe some of those Oracle guys still use it because that’s when I used it.
Erik Darling: Everyone uses SQL Developer with Oracle. The fancy pants one.
Richie Rump: Not in the 90s, man.
Erik Darling: The 90s are over, Richie. Sorry.
Richie Rump: No.
Erik Darling: Sorry.
Richie Rump: Next thing you know, Nirvana broke up, right?
Erik Darling: No, they’re still together. Don’t look at MTV.
Richie Rump: Whoa, man.
Angie Walker: Just go back and watch I Love the 90s on VH1 on demand or something.
Erik Darling: Kurt Loder will be there. All your friends will be there.
Richie Rump: Daisy Fuentes.
Angie Walker: On that note, folks…
Tara Kizer: I don’t think Angie is old enough for these references.
Angie Walker: Hey, I used to watch I Love the 90s. On that note, we’re going to have to end this episode of Office Hours. Thanks for watching, listening, or reading on the blog. See you all next week.
Erik Darling: Bye.
