This week, Erik, Tara, and Richie discuss altered tables and update statements, parameter sniffing issues, their issues with Azure, a resource for column store indexes, AlwaysON availability groups, killing sleeping connections, and fill factor value for indexes.

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-04-05

What are the pros and cons of Platform-as-a-Service?

Erik Darling: Richie, I think this is one that you’ll like. Platform as a service, pros and cons, because you like Cloud stuff more than I do. You like things that happen.

Richie Rump: I do?

Erik Darling: In the cloud, a lot more than I do. Like, things that happen in the Cloud. I’m annoyed with the Cloud constantly.

Richie Rump: I do, and if you’re coming from on-prem to Cloud, you probably will be annoyed as well. There’s a lot of things – I haven’t really played a ton with Azure SQL but you know, just a little bit that I have played with, there’s a lot of stuff like why isn’t that there and why isn’t that there and why isn’t that there.

Erik Darling: There’s a lot of our stored procs that just won’t work up there because there are you know, features in certain systems – we can’t use sp_BlitzCache up there because you can’t use a global temp table. I don’t know what you expect me to tell you but…

Richie Rump: But the same thing is true when you code for the Cloud as well. The way that you would program is significantly different if I were to just write a typical server application, so there’s plus and minuses for both sides. I don’t have everything the top of my head other than you’re going to need to try it for yourself. What is going – I thought it was actually Brent Ozar coming on. This is better. But this is better. I was getting scared there for a second. But PaaS – it is – you’re going to have to try it out along with your requirements and see if it’ll work for your environment and your application that you’re building.

Erik Darling: PaaS changes a lot. Like, Microsoft just pulls things in and takes things out. CLR got yanked out very unceremoniously recently because…

Richie Rump: And we’re going to put it back, and nothing.

Erik Darling: Yes, they discovered that there were security issues with letting people use CLR up in Azure then they were just like, oh wait, you can’t do that, all this stuff you want is gone. All that CLR just got pulled out and there wasn’t any like, case by case, like oh you can use it in … you’re special, like it was just like no CLR, figure it out. So pros and cons – con is you control nothing. You just have to…

Richie Rump: Yes, I could definitely see them say, hey you know what, the benefits for this feature really doesn’t outweigh the security concerns that we have and really, who’s complaining about I don’t have CLR in SQL Azure, so we’re probably just not going to put any resources on fixing this thing. It’s just – we’ll just rip it and we’ll just tell people it’ll be back at some point and see who complains the loudest and frankly I don’t see hardly anybody really complaining that I don’t have the .NET CLR inside of SQL Azure so now that we have something like Lambda functions, I can you know – once they start getting better integration with Azure SQL and Lambda functions, now I could write Python or I could write f# or I could write c# or JavaScript or whatever and have it interact with my SQL. So hey, functional languages man, it’s…

Erik Darling: Richie said f#. I want to hit the bell on that every time Richie says f#.

Richie Rump: Hey, functional languages. It’s all the new hotness man, all the cool kids are doing all the functional languages

Erik Darling: Never heard you say f# before like, without it being derogatory.

Richie Rump: I’m not going to talk bad about f#. The folks who know how to wield that broad sword are amazing. You can do a lot of cool stuff there.

Erik Darling: Interesting.

Richie Rump: Yes.

What resources get used when you ALTER TABLE?

Erik Darling: Alright, let’s see here. Ron, I’m sorry, I don’t get that question. Tara, do you want to – there’s a question there from a fellow named Ron. Make more sense to you, it didn’t make sense – thing, kind of went by me. J. H. does ask though, “What resources get used mostly when issuing ALTER TABLE and update statements, CPU desk memory?” Jeez.

What resources get used most? Well, certain ALTER TABLE things don’t use all that much resource. Like, if you alter a table and you add a column that’s nullable and you don’t have a default in there, it’s pretty quick. It doesn’t really use much resource. For updates, depends on the query. If you have a totally unindexed query and you let that run, it’s going to use a whole lot of everything. So then it has to, you know, first run that query, read a bunch of pages, figure out a bunch of stuff out, then write all that stuff and then you know, internally SQL figures out if it can just do an in-place update or if it has to do an insert and delete, and all this other stuff. So I mean, you’re looking at a bunch of resources depending on how well optimized the query and indexes are to begin with. If you’re updating a column that is in 50 indexes, you’re going to do a lot more work for like, you know, writing and what not than if you update a column that’s in five indexes. So that question’s a little weird, but alright. I see why you didn’t want to put that on Stack Exchange, you would have gotten down-voted into oblivion.

Richie Rump: Oh no, but why would anybody do that? An honest question on Stack Exchange?

Erik Darling: Well, honest is different from good. Honest and good are two totally different things.

Richie Rump: I see how you backed away very quickly from the In-Memory OLTP question. That was pretty amazing there.

Erik Darling: Well, because the person left. I’m not going to ask the question If someone left.

Richie Rump: They leave but they ask a question saying, hey you know, I’m going to actually watch this later, maybe.

Erik Darling: Maybe, but they didn’t say, hey I got to go, sorry. I just got a someone’s left.

Richie Rump: And then again, In-Memory OLTP, just – it makes them freak out, which is pretty amazing.

Erik Darling: Yes, so like you know, I was honest with my wife about the fact that I spent $400 on a bottle of Yamazaki 18, that was not good. So there’s like honest and good. Two different things. I didn’t mean to insult your question, I just wanted to draw like my boundaries between honest and good.

Richie Rump: I’m so sorry, that you spent that much money on the bottle. I’m sorry for that.

Erik Darling: Oh no, it’s one of those, I put it in my little bar cart and I just say that I have it. I don’t let anyone touch it. My precious, my precious – just one of those little White whale holy grail things that you get. Of course I say that about a lot of different bottles of scotch and they end up gone pretty quick. What can you do?

Why is parameter sniffing an issue in only some of my procs?

Erik Darling: Let’s see, we have a parameter sniffing issue in some stored procedures but not an issue in other stored procedures. Any ideas? Write the stored procedures where you have the parameter sniffing problems, just like the ones where you don’t have them and then they’ll go away. That sound about right, Tara?

Tara Kizer: No.

Erik Darling: No? No, you shouldn’t? What would you do if some of your stored procedures had parameter sniffing?

Tara Kizer: So, I would see what the problem is here, compare the execution plans is it an optimization and is it optimizing for a value that isn’t representative of the other values. I just worked out a client this week that is having sort of a parameter sniffing issue between two servers and it just – one server’s locked in a clustered index scan, the other one is locked in a non-clustered index seek and you know, using index hints has worked around the problem but doesn’t necessarily solve – it doesn’t tell us what the root cause is, so anyway, to answer the guy’s question, I’d be comparing execution plans and seeing what I could do to get it to use the better execution plan for the rest of the grammar values.

Erik Darling: Sounds like a good idea. At what point would you resort to something crazy like plan guides?

Tara Kizer: When a Microsoft engineer’s on sight and looking at the system request. That’s when I started using plan guides. It really was onsite for an update for ESCO, because it was such a major upgrade we decided to pay someone to watch me. Plan guide or index hints or any of those, option for – it doesn’t matter what you do. Plan guide is just an easy – I shouldn’t say it’s easy. It’s a hard opter to put in place because it has so many requirements that doing the optimize four trick or index hint trick in the actual query can be easier. Plan guide is just another object that has those options in it.

Erik Darling: Sounds good.

Richie Rump: Yes, sometimes I just, you know, depending on the query, just have a recompile every time and just let it do it and automatically it gets better, and if I need to put more effort into that and if that’s not the solution then so be it, but I think sometimes especially when I’m doing the time performance tuning, we need to have that exit goal – okay, what point is good enough, because I could spend another week on this problem, but sometimes the fastest, easiest way is maybe not the best way, but it’s the way that meets the requirement to get out of the performance tuning.

Erik Darling: Yes, you know what, big thing for me with the parameter sniffing is always making sure that my indexes are lined up so that SQL doesn’t have an option between too many different plans. Tara ran into a weird one where it was the same everything and the query on one server chose a totally different index and everything from a query on another server. But for me, if it’s just happening on one server then I’m most concerned that like you know, it’s like choosing a weird key lookup plan when it should be choosing like a different plan. You got to get rid of that key lookup to get the same shape plan in place.

What’s your experience with Azure?

Erik Darling: There’s a long question there from Ian. I think that’s a Tara question, so if you want to read that and answer that in the background, we’ll get to something else. J has a follow up question about Azure. “Do you have similar experience of the features that…” – no, I don’t touch Azure. I don’t want to touch Azure. I don’t want to be around Azure. You know, I’ll take Amazon RDS and have like a complete list of stuff that doesn’t work and isn’t compatible that’s pretty stable over time.

Richie Rump: Yes, I think that’s what gets people especially in the Microsoft community kind of confused about us, is that you know, we’re pretty agnostic about the Cloud. I do a lot of work in AWS, we obviously wrote the white papers for Google Cloud, SQL server in there, but Azure – I dabble in Azure but by far, I use AWS more than the other two. So we could give you a big general idea but when we get to some of the specifics, we haven’t really dived into some of those, probably outside of Google Cloud and AWS, which I think surprises some of our more Microsoft-y type followers.

Erik Darling: Yes, Azure has kind of been weird from the get go and we try to get our labs set up in Azure the way we have it set up in AWS a long time back and after three days – and I think Jeremiah, who’s smart doing stuff in Azure, he said I don’t know, no, I throw my hands up. So there’s that.

Richie Rump: It feels like Azure has this kind of enterprise-y security layer that they’ve kind of put in their Cloud where AWS doesn’t really have that.

Erik Darling: There’s a portal and a beta portal and a preview portal and then PowerShell. It’s like, I don’t know what to do.

Richie Rump: Yes, when I’m messing with Azure, I’m always you know, messaging our Microsoft community guys like how do I do this, how do I do that? With AWS, you do this, but how do I do it over there and typically, AWS is a little simpler to get into and out of as opposed to Azure, there’s layers upon layers that you kind of have to go through. Yes, it’s peculiar. I would definitely say it’s definitely peculiar, but not to say that it’s bad. It’s just additional steps to kind of have to go through in general, to get stuff done.

Erik Darling: I would agree.

Any good references on columnstore indexes?

Erik Darling: Gregory asks if we have any good references or thoughts on column store indexes. I sent a link out into chat through a website from a fellow name Niko Neugebauer, I believe that’s how you pronounce it, I couldn’t be a 100% on that, but it’s Nikoport.com and he’s been writing for years and years about column store and he has more good thoughts on it than I do. As far as order of columns, the number of columns, it doesn’t matter in column store, the way it does with row store indexes. For non-clustered you know, there are obviously some limits on column length at this point. I’m not sure if you can have max column types in there just yet. I think I read something – no, that was hekaton. Yes, because when I go to column store to find my stack overflow database, I always have to ditch the body column from the post table, so it does have some limitations but as far as the number of and the order of – and you can’t column store index a computed column either; another funny thing. So there are limitations, I would let the limitations guide me more than some arbitrary number though.

How should I train someone on Always On Availability Groups?

Erik Darling: Tara did you want to answer that long question about the always on stuff?

Tara Kizer: From Ian?

Erik Darling: Yes.

Tara Kizer: Sure. So Ian is saying he needs a – only DBA – the company is large enough to need another DBA and ASCII training, an emergency backup for our main instance, they’ve got AlwaysOn, Analysis Services Integration Services, Reporting Services – they’ve got everything apparently. Trainee is knowledgeable on SQL server but it’s another office, it’ll be remote and it’s a second language, so he’s asking how to train this person. Ian is asking the question you know, that he has been doing it for eight years and hasn’t documented it. It sounds exactly like what I’ve had to do in the past. You know, I’m not a documenter, that’s for sure, everything I store is in my head and people need to learn that they’re going to have to ask me because I don’t write stuff down. So I’ve had to train people in the past. One time I was going on maternity leave for three months and we hired a consultant to replace me while I was gone so I had to get that person up to speed on – it was like five years worth of knowledge of troubleshooting performance issues in production and that’s hard to do because it’s so specific to applications at the time. So anyways, what worked well for us is shadowing. I realize that this other person is remote but you can do a WebEx session or go to webinar, Skype, whatever you’re – Google hangouts or whatever and share your screen, talk through everything that you’re doing for one, two hours per day. Don’t do a whole day because that’s just going to burn everybody out, but you know, share your screen and talk with the person. You know, what are you doing day to day, because that’s really what they need to know is day to day, and also talk about the things that are – come up as emergencies. What are the types of things and have that person be responsible for documenting going forward and that’ll be part of their training, you know, writing it down.

Erik Darling: Sounds good to me.

Can I add an AG listener online?

Erik Darling: Suzie asks if she – well, I’m going to assume Suzie is a she. Suzie is quite a gal’s name. “Can I create a listener for an existing AlwaysOn availability group on the day or do I need to wait for after hours? Can it cause any issues?”

Tara Kizer: I mean, you certainly can. Will it cause any issues, probably this one time it will cause an issue, so you know, I would run the command later in the day. But you should be able to add a listener without an issue, they’re just – you know, weird things happen when you’re working in production, you know, you’re rolling the dice.

Richie Rump: Yes, I mean, I would always opt to do stuff like that, you know, later or during a maintenance window but creating a listener just seems like one of those sort of like nebulous things where it’s like, okay, I’ve got to create this and then nothing’s actually going to use it until I tell it to…

Tara Kizer: In theory that’s how things should work but I don’t know about everyone else, but I have bad luck in production.

Erik Darling: So Suzie, the verdict is it’ll probably be okay but you probably also want to wait.

Do you have a server health checklist?

Erik Darling: James asks, “Do you have a health server checklist template?” Now James, you’ve been in Office Hours a lot. You’ve been to our training, you’ve been to our online training. You should have our first responder kit with the set up checklist in it, James. I know you have the first responder kit. I would definitely want to download, if you don’t have it handy, download that and look for the set up checklist. It’s a pdf, it’s not really a template, but you can go through and, you know, check things off as you go, make sure that it’s all done. Other than that you know, our healthy server template is running sp_Blitz. Making sure that nothing crazy comes back in there, so those are our versions of healthy server checklist templates.

Do sleeping connections cause negative impacts?

Erik Darling: J. H. asks, well, says, “I have a bunch of sleeping connections not doing anything for days. Are these connections taking up resources or are there any negative impacts? Is it good to kill these connections on a daily basis? What reasons to kill them or not to kill them?” Well, off the top of my head I could give you a good reason not to. Now, I’m going to pop a long command in there for sp_WhoIsActive that’s going to give you a bunch of information about what a query has been up to. If you run that, and you see that one of those sleeping transactions has a bunch of tran’ log writes, then it’s also blocking – you might also see a blocking – if you see it has a bunch of transaction log writes, just know that if you kill it, it may have to roll back whatever transaction log stuff it wrote out. So a good reason not to just kill them is you may have an unforeseen rollback. A good reason to kill them, they might be blocking stuff.

Tara Kizer: If these sleeping connections don’t have an open transaction and they aren’t blocking anything then I don’t bother killing them. They aren’t using many resources, or using a teeny, teeny, teeny bit of resources. It’s like 8K of memory or something like that, you likely have sleeping connections due to connection pulling so applications will go out and connect to a server and say the app admin has configured it to go ahead and do 200 connections, but maybe you have only 50 connections are needed. Those 200 connections are still going to be there and if you go ahead and kill them, the app’s just going to recreate those 150 connections again. So take a look at your system. What are those sleeping connections doing and do you have connection pooling in place? Talk to your web ops people and see what number they’ve configured for the pool.

Richie Rump: There’s a task I need to set up. Pooling was on my list, I completely forgot about it.

Should I use fill factor 100% or something else?

Erik Darling: Let’s see here. Oh, there was a fun question. Where did that question go? There was a fun question that I wanted to ask.

Tara Kizer: I’ll let you find it. How about Renee’s question that just came in? “Would you recommend to use a fill factor of 100% for indexes or would you go with a different value?” I like that question.

Richie Rump: I would absolutely go with zero. I think zero is the way you need to go.

Tara Kizer: Zero and 100 are equivalent when we’re talking about fill factor, so he is still right. Default to 100%. Don’t change it at the instance level. You need to take it by case by case basis. If you’re talking about GUIDs and you’re trying to avoid page splits then maybe reduce your fill factor. The recommendation is to keep your fill factor 100% except when page splits are a problem, like is the case with GUIDs. But I had a client a few weeks ago that said that they had lowered the fill factor from 100% down to 75, 80. There was something like that said 78, or some weird number and it was because what they had encountered page splits years ago, and that was causing problems and I was like, is it causing problems now because your reads probably are slower with these lower fill factors. It’s just not something they realized, that read performance goes down when you lower your fill factor down. Yes, it helps out with your inserts to avoid page splits but usually, select performance is way more critical than the insert updates and deletes because the selects are what users are using generally.

Richie Rump: I think that’s kind of a tough thing in general to try and figure out is at what point do I stop optimizing prematurely, right? What is going to be a good median to say hey, this is a good performance tuning whatever I do to start off with versus this is a problem now and I need to go and fix it.

Erik Darling: The stupid thing about fill factor for me anyway, is – I mean, it’s like – one of the first things is like introducing like enforced fragmentation into your indexes, you’re leaving a certain amount of empty space in there that SQL is going to have to read and store on disk and in memory. The second thing about fill factor for me is that it’s not maintained on insert update and delete. It’s only enforced when you rebuild your index. So like, in between rebuilds, your index will probably do better on page splits but unless you rebuild your index fairly frequently, all those new pages, all those split pages, they’re just going to fill up to 100% and split again, so it’s like one of those like well, I chose a 70% fill factor for my GUIDs, well I still have lots of page splits because all these new pages keep getting split. So like, you would have to be out of your mind to constantly rebuild an index to try and put that back in place. So I usually just leave fill factor somewhere between 90 and 100 and just never bother with it again. I prefer 100 because then I don’t have to change anything.

Richie Rump: Yes, five is usually a bad idea.

Erik Darling: Five is terrible.

Richie Rump: So if you’re considering that, don’t.

Erik Darling: Don’t use a fill factor of five…

Unless you’re doing a fragmentation demo, then it might be okay.

Richie Rump: Unless you want to call us later and then we’ll explain to you why that’s a bad idea.

Erik Darling: Yes. I can do that, in like two words.

How do I talk people out of including every field in an index?

Erik Darling: Alright, I found a question that I thought was interesting. It was from Ben. “What’s the best way to convince someone that a new index with every field as an include is not necessarily a good thing?”

Tara Kizer: I know that you and Brent will say that when you have it – when we see a client that has a – maybe the key is fine but then they have like, 48 includes, to go ahead and chop off the includes and check out the performance of the query, let it do a key lookup to the clustered index or also test it with the covering index with all those includes. Now, of course the key lookup version is going to be slower but it is the perfect difference between the two…

Erik Darling: Well, it comes down to the query. If it’s like on a single primary lookup or something, then I don’t care if it does one key lookup for 40 columns, go ahead. It’s when there’s like thousands – hundreds of thousands of them, I’m like, no, not the key lookups.

Tara Kizer: But when you’re looking at the missing indexes and there’s a missing index with say, 48 includes, do you go ahead and recommend that – when it has a really high benefit per day, do you go ahead and recommend that index or do you chop off the includes for them?

Erik Darling: I vary a little bit on that. If I can track down the query that’s asking for it, and I look at this query and I say do you need this query as it lives and breathes and they say yes, we cannot change this query, we need this query as it lives and breathes then I say okay, then you need this index to make this query better. You can try it with just a key column first and see how that does or you can go for the whole kit and caboodle and see – you know, take the hit on having 48 includes in there. But if I can’t, then – if I can’t find that query and ask them those questions, then I’ll just give them the choice of you know, key columns plus includes and then just monitor how it gets used in BlitzIndex.

Richie Rump: When I start off with an index, it’s usually the key columns that I need and then the actual clustered key inside the includes and then that’s it. I put the clustering key in there just in case someone changes that later. It’s not going to do anything, right? And then as we start going on and hey, there’s a performance problem here or there, that’s when I’ll start throwing includes on top of that and not just let’s throw everything in there so I’ve gotten this really big index now for no reason. I’ve got no queries running up against it. It’s just taking up space, it’s on disk, it’s just taking up space in memory. Why is all this stuff there? So I don’t put included in there until I need it. That’s it.

Erik Darling: Alright, well we are – we have hit the 12:46 mark. I don’t see any other questions pouring in that I can answer so I think we should call it on Office Hours. What do you guys say?

Richie Rump: Yes, I’m good.

Erik Darling: Alright, cool. Take care everyone, thanks for showing up. We will see you next week. Goodbye.