This week, Erik, Tara, and Richie discuss performance and load testing tools, sysadmin update SQL server restart, defragmenting column store indexes, Amazon Redshift, installing Microsoft updates, Always On Availability Groups, Redgate SQL Clone, as well as their thoughts on the current DBA job market.
Enjoy the Podcast?
Office Hours Webcast – 2017-6-28
Can you recommend a load testing tool?
Erik Darling: Gordon asks, “can you recommend a performance or load testing tool?” Can I ask a follow-up question? I that free or how much money are you willing to spend on that? Tara, you have some load testing – you don’t have load testing, you used to crack the whip on a whole team of people who did load testing; tell us about that…
Tara Kizer: Yeah, see the last three jobs, I’ve worked for companies that have a dedicated load testing team. So yeah, we spent money on the tools, we used Enterprise level tools. I don’t remember all of the tool names, but one of them was LoadRunner. I have used some free tools on the side when I couldn’t get testing time in the performance environment – Adam Machanic’s SQLQueryStress tools. You know, an easy one for a DBA to generate some load on the system because these tools are complicated. So it’s not like I could just jump in and run a test, I had to wait for the performance team. One of the tools that my last job used was Visual Studio. I’m not exactly sure what they did in there, but definitely one of the applications used Visual Studio for the load testing tool.
Erik Darling: Dell Benchmark Factory is another cool one, but these tools are expensive because that stuff is complicated and you’ve got to set up, you know, reads and writes and all sorts of other stuff going on. You’ve got to turn a whole bunch of weird knobs and – actually, that’s one of those jobs, I think that would be too hard for me.
Tara Kizer: It seems interesting…
Erik Darling: Oh yeah, it seems interesting but I think that I would just get lost in the minutiae of it. Like, it would … have this many users doing this, and this many users doing that; there are too many options there for me. I would need someone to keep me in line, I wouldn’t be able to just do that on my own.
What’s a good book for learning OLAP?
Erik Darling: Let’s move on then. Bill asks, “what is a good book or resource for learning OLAP and OLAP cubes?” I have no idea, I’ve never done any such thing. We tend to stick to mostly core SQL engine stuff here. We don’t really do a whole lot with OLAP or analysis services or any of that crazy stuff. So I actually don’t have a good answer there…
Richie Rump: I’ve spent my career avoiding OLAP, so yeah.
Tara Kizer: I once installed analysis services, but that’s about it.
Erik Darling: I’ve never even heard anyone say, oh I read this great book on OLAP cubes recently.
Tara Kizer: There probably are some great resources out there, we’re just not the right people to ask. Maybe post a question on Twitter, use the SQL help hashtag and I’m sure someone could give you an answer there.
How do you stop Windows updates from restarting SQL Server?
Erik Darling: Alright Tara, here’s one that’s right up your alley. So Brent gets this right, now it’s my turn to screw it up – Michaela asks, “how do you stop sysadmin windows updates from restarting SQL Server?”
Tara Kizer: Why do I get to answer this, just because my company has had monthly patches?
Erik Darling: [crosstalk] DBA since you were 12, you could answer…
Tara Kizer: No, I did graduate from college before – and I did not go to college early. I did see that, I look – I am older than you, Erik. I’ve worked for companies that had a sysadmin team and we used the System Center SCCM tool to manage all that stuff, and you just uncheck the box if you don’t want it to reboot. Or maybe we would just have it download the updates to the server, and then we would manually install them and reboot them. So it just depended upon the environment; there were some environments that we would have it installed, reboot the whole thing. Some environments, we needed more control of it. Other environments we needed to, you know, make sure the failovers were done correctly because we were using multiple instances on some clusters. I don’t know, as far as – if you’re just using Windows updates and scheduling that way, I’m not sure. SCCM is very, very powerful tool, and expensive again, Enterprise level product.
Erik Darling: Yeah, you might even be able to set something in group policy that would prevent that from happening, but I am not nearly anywhere near as good at group policy as I should be to tell you exactly how to do that.
What’s the best practice for maintaining columnstore indexes?
Erik Darling: Let’s see here, Dorian asks, “what are some best practices for defrag and update stats for columnstore indexes?” You know, it’s so new, I don’t think there are best practices yet.
Tara Kizer: I have no idea…
Erik Darling: I do know that if you have [crosstalk]…
Tara Kizer: Do you really need to do a defrag on columnstore indexes?
Erik Darling: Well sometimes, if you have uncompressed row groups or, I believe, if you have deleted records that haven’t been removed from row groups yet, you might need to rebuild them, but I think generally I would tend to treat them like regular indexes and just kind of leave them alone. You know, update stats maybe, if you do a data load, but I think with columnstore the amount of data you’re loading is usually significant enough to, you know, trigger an update stats the next time the query runs.
Dorian, what I would do is I would head over to NikoPort – let me grab that URL for you. Oh look, it’s the first thing that popped up when I typed in Nik…
Tara Kizer: You know you’ve gone to is a few times.
Erik Darling: I go to it once a week when I send people. So Niko Neugebauer is a really smart guy who blogs a ton about columnstore. I’m sure that if you dig through his, now 107 part blog series on columnstore indexes, you will find something about defragmenting.
Tara Kizer: He’s the one that presents that Office Hours? Sorry, not Office Hours, GroupBy?
Erik Darling: Yeah, he did GroupBy, he does PASS and everything else. I mean he is a hit on columnstore.
How good is SQL Server’s audit functionality?
Erik Darling: Alright, let’s move that on and see here. Jeez, I’m going to say this is a no, but Wes asks, “have you used audit tracking functionality in SQL Server, what are your views on it?
Tara Kizer: I have not. Anytime I start thinking about auditing, I’m wondering about performance at that point. I mean, yeah people want to audit things, but at the cost of performance?
Erik Darling: yeah, that’s one of those things that I go right to third party vendors for, because they’re usually much more cognizant of hurting performance than Microsoft is sometimes.
What’s a good resource to learn COALESCE?
Erik Darling: Robert asks, “do you know a good resource to learn more about the COALESCE function?” Well, I’m curious what your confusion…
Richie Rump: Books Online works pretty well.
Tara Kizer: That’s everything you need to know.
Erik Darling: Yeah, that’s sort of everything you need to know. I wonder what in particular, to you, what’s unclear about the COALESCE function. Is it the difference between that and IS NULL? What is your question about COALESCE? What did you see that led you down this strange road?
Can I add another node to a cluster with different storage?
Erik Darling: Let’s see here, Gordon asks, “is it possible to add another node to a cluster but specify different storage?”
Tara Kizer: Well I mean, it needs to be shared storage. You can point to multiple SANs in one cluster, but they both have to look at them.
Erik Darling: I’m going to go out on a limb and say that if you point to different storage, failover is going to be tricky. Can you imagine?
Tara Kizer: Yeah, I mean, in an Availability Group cluster, I mean, when people start talking about nodes and cluster, I’m assuming they’re talking about a failover cluster instance. Those terminologies still exist in availability groups, but I mean…
Erik Darling: Just no one talks about availability groups using that.
Tara Kizer: Exactly, yeah. They still are nodes in a cluster; people just don’t use those terms.
Erik Darling: Until Allan Hirt comes around and spanks them, then we know. Alright, so Gordon, you might want to clarify that a little bit.
Are there any new training videos coming soon?
Erik Darling: Let’s see here, James asks if, “there are any new training videos coming soon?” Yes… Shocking.
Tara Kizer: Maybe like one million from Erik. He did like 100 in a day or something.
Erik Darling: They were short. 17, 18 videos that are all sort of like more – not like performance, like DBA focused stuff. Like to-do list stuff, like setting up database mail and Remote DAC and how to use things and how to set up index and statistics maintenance with Ola Hallengren. So there’s lots of good stuff in there. But they are shorter videos, they’re not long videos like T-SQL Level Up Two.
How big is Brent Ozar Unlimited?
Erik Darling: There are, what, four people who work at Brent Ozar, Michaela asks. How many people working? Well, four plus Erika.
Tara Kizer: She’s part of the company though, right?
Erik Darling: She is, but she’s not as visible as the rest of us.
Tara Kizer: Not as visible, yeah. So five, four visible [crosstalk]
Erik Darling: Hand of the free market.
Tara Kizer: We don’t even get to see her in the company chat room hardly ever. Like I don’t know, twice a month…
Erik Darling: I know, like once in a while she’ll pop in on the Friday before a long weekend and tell everyone to go drink. This is why you’re popular.
Tara Kizer: I love when she does it because I’m three hours behind you guys, so I get off really early.
Erik Darling: I know, it’s like 7am for you. Lucky you, lucky, lucky, lucky you.
Should I shut down SQL Server before rebooting?
Erik Darling: Alright, let’s see here, “Deborah asks, “interested in your opinion of rebooting Windows server without first shutting down SQL Server.”
Tara Kizer: I have rebooted hundreds of servers, maybe thousands of server; I don’t ever shut down SQL Server first. This is a planned maintenance, I may talk to the web ops team or whoever it is we’re calling these days and tell them to shut down the applications. That way it can go through a graceful shutdown. But as far as SQL Server, SQL Server in Windows handles that; I’ve never had a problem.
Erik Darling: Yeah, me either. I’ve also just – like, I mean because it goes through the same shutdown process either way.
Tara Kizer: Exactly, the only thing that I would be worried about as far as SQL Server goes is if you have an index maintenance job currently running when you reboot, to make sure you have those jobs – especially I’m talking about the index rebuild job – make sure that occurs at an hour that you will not be rebooting servers manually, because I have, in a maintenance window – we had an index rebuild occurring for a 7TB database and I didn’t know it was running and I restarted SQL Server as part of our planned maintenance… Woo let me tell you, that was painful.
Erik Darling: Yeah, that’s rough. [crosstalk]
Tara Kizer: It was offline for a while. Yeah, I don’t remember exactly what the error log said, but we were watching it go through the different phases and the percentages. I think it was down for, I don’t know, [crosstalk] for like three or four hours or something like that. I mean, you know, there should have been like a two minute failover or whatever, because that would have been a clustered server.
Erik Darling: Should have been.
Tara Kizer: I rescheduled that job until a later time when I would not be awake to do planned restarts.
Can I connect to Redshift with SSIS?
Erik Darling: Alright, James asks, “do you guys know anything about Redshift AWS Data Warehouse? Does it connect via SSIS?” Richie, have you done anything with that? I know you’re not an SSIS man, but I know…
Richie Rump: I like Redshift a lot. I do not know if the SSIS connects directly to it. I’m assuming somebody’s working on something with that. Redshift is pretty cool, I dig it a lot, but as far as SSIS connecting to it, yeah, I’m kind of out of the SSIS game, thank god. I don’t want to deal with that anymore.
Erik Darling: Thank god indeed.
Tara Kizer: You mean you’re not going to be taking Andy Leonard’s new SSIS training with us?
Richie Rump: I did Andy Leonard’s training, I’ve done it. Yeah, he was doing SQL Saturday, Atlanta, did a pre-con. And I’m like, whatever – because I was on a contract where I needed to do SSIS, and I’m like whatever’s in that dude’s head needs to be in my head. So my plane landed a little bit late, so I walked in and it had already been going for an hour. And I walk across by Andy and he goes, hey Richie, how you doing? Have a seat. And I’m like, he knows my name?
Erik Darling: That’s pretty funny. See I would do that training but it’s advanced SSIS training, and I am the least advanced SSIS user you could possibly find. You would have to like grab a fetus to find someone with less SSIS experience.
Have you ever seen an Availability Group with…
Erik Darling: Alright, let’s see here. Paul asks a bit of an odd question. “SQL Server 2016 Always On Availability Groups, can you provide a real-world example of a multiple AGs with multiple SQL instances using two or more nodes?”
Tara Kizer: Multiple AGs with multiple SQL… Yes, it wasn’t 2016, but it doesn’t matter. It was 2012 at my last job and we had a 14 node cluster that had, gosh, it would have been 14 SQL Server instances. It was virtual machines and we did one instance per server, and then the AGs spanned at most, back then it was 2012, at most five server, because you could only have four secondary replicas, and the AGs – it was to copy data to DR, it was to copy data to a failover server and it was to copy data to reporting systems. So we had AGs everywhere in this 14 node cluster. I think I’ve answered his question, I’m not quite clear.
Erik Darling: It sounded good to me.
Should I install cumulative updates right away?
Erik Darling: Kaushik, I hope I got that right, Kaushik? You got me on that one; my name’s probably tough for you. “Can I install cumulative updates in production as soon as Microsoft releases them, or wait a certain period?”
Tara Kizer: Yes, please do. That way, you can test it for the world, for us, because most of us are going to wait. Depending upon the bug fix, I don’t install the current one unless it’s been out for like a month or three months. I’m not talking – I’m talking the hotfix as a cumulative update. Now that’s my answer for SQL Server. Now for the Windows type stuff, the past three companies, they’ve all been installing those, you know, a few days after they get released, the security patches, the security updates at least. Those are really big in security, but SQL Server, I don’t install them as soon as they come up, that’s for sure, because there are bugs in them. There can be, I should say.
Richie Rump: Yeah, but there’s a difference between a hotfix and your cumulative updates.
Tara Kizer: No, I mean as far as SQL Server, they are the same. So you have your service packs and then you have your CUs, and another word for a CU is a hotfix.
Richie Rump: Okay, because it was my understanding that you had your hotfix and then the CUs bundled all the hotfixes together.
Tara Kizer: I mean, yeah I guess, but you don’t get the hotfixes separately, the hot fixes…
Erik Darling: Unless you’re real special. [crosstalk]
Tara Kizer: Maybe you get something special, yeah. I have done that.
Erik Darling: Yeah, I mean, so my advice there would be: don’t run out and install it immediately in production. This is something you want to roll in dev, proc, QA first, make sure everyone’s, you know – because not only can you run into bugs just from installing the service pack, but there can be bugs within the service pack that are specific to what you’re doing. So it’s something that you want to pretty thoroughly kick the tires on before you, you know, go rolling that up to prod. Another thing is that Microsoft has kind of changed the way stuff gets released, and cumulative updates now are going to be much more common and service packs are going to be much more rare. So you’re going to have to trust in the cumulative updates a little bit more than you used to in the past, but still make sure that you’re testing that, especially if you’re using AGs or anything else that’s an HADR component of SQL Server because stuff can go real wonky.
Should I change from FCIs to AGs?
Erik Darling: Let’s see what else is going on… So Tara, let’s talk more about AGs. “I hear you guys referring to Always On…”
Tara Kizer: What’s the name, real quick?
Erik Darling: Chris Adaline. Because the PITA, which we all know is an acronym [crosstalk]…
Richie Rump: Pita bread, that’s what it means…
Erik Darling: Yes, enjoy your humus, sir. “Is it worth looking at? We’re using clustering now but there’s no storage or site redundancy with it.”
Tara Kizer: I like availability groups, I love them, I’ve had great success with failover cluster instances. I was, at the time when we were looking at availability groups, we were using failover cluster instances for high availability, we were using asynchronous database mirroring for disaster recovery and log shipping to the pass as well, and we were also using transactional replication for reporting, the reporting system; so three different features for one system. Add in availability groups and you can get rid of those three features and it provided everything we needed for HADR and reporting.
So I really like them, but it does take clustering knowledge, it’s going to take a lot of your time, troubleshooting is hard. You need to understand what – if you’re going to be running reports on the readable secondary, you need to understand statistics and the indexes are from the, you know, main system. So there’s a lot of things that you need to know and you need to make sure that you have understanding of clustering and quorum and votes that you don’t cause unexpected outages. There are GUIs for availability groups, and so people have set them up and caused production outages because they did not understand them, whereas failover cluster instance, it’s very complex to do, and so you usually have a lot of knowledge when you’ve set up a failover cluster instance.
Erik Darling: Well, I’ve set them up so I don’t know… Maybe right, you may be wrong. I think it really depends on your level of experience and your comfort with SQL Server. If you are very comfortable with failover clusters and you’re not terribly comfortable with SQL Server or – so you haven’t used, like, mirroring or anything of that nature, I would stick with the basics. I would just go with log shipping or something simple that is easy to non-lifetime DBAs to wrap their heads around, Tara… so if you haven’t really done much with availability or stuff like that, I would say keep it simple with log shipping. Mirroring I’m a little bit meh on, just because, you know, it is deprecated, but it’s still pretty cool.
Tara Kizer: Yeah, I would want to know first, what problem are you having with clustering right now that you’re looking to replace? I wasn’t looking to replace failover cluster instance, I was looking to replace transactional replication and it just so happened that it also replaced HADR features for us. So that was my goal.
Erik Darling: So I think another topic worth covering on this, just because it is sort of in the same neighborhood as this, is geo-replicated clusters. Because you can extend a cluster to DR, but that’s…
Tara Kizer: Who the heck does that? I know people do, but who are these corporations that can afford that. When I worked at, you know, my LinkedIn profile’s public… When I worked at Qualcomm, every two years someone would ask if we could do this, and they also want to know about being able to write at both datacenters. Like okay, $2 million, send it over our way and we’ll get this set up.
Erik Darling: No problem, we’ll have it done this weekend.
Tara Kizer: SAN vendors were always extremely excited every time the discussion came up, ooh wow… We can retire early.
Have you used Red Gate SQL Clone?
Erik Darling: Oh man, that’s fun stuff. Dorian asks, “have you used Red Gate SQL Clone? What do you think about it in dev and QA environments?” I haven’t touched that one yet.
Tara Kizer: I was a big Redgate fan, they must not have had that when I used to use it.
Erik Darling: Yeah, it’s kind of on the newer side. I really like a whole bunch of Redgate tools but I haven’t used that one.
Tara Kizer: I would imagine it’s great, since everything else from Redgate, and the other companies, they’re great too – the big ones at least.
Richie Rump: Yeah, I was on the beta [crosstalk]; never opened it up.
Tara Kizer: there’s also the new DBCC CLONEDATABASE for SQL 2016 that comes with the product.
Erik Darling: That’s for 2014 now too, that came out in some service pack or CU… Or it may have come out there first actually, I forget. Either way, DBCC CLONEDATABASE is another sort of way to do that, which makes a statistics only copy of your database that you can stick on – I have a blog post up about that with some warnings about security and about not rebuilding indexes, because it will clear out all those stats that you just cloned. Hurray.
Is it true that UDFs are single-threaded?
Erik Darling: Alright, let’s see here. We still have so many hours left, oh my God. Paul asks, “is it totally true that UDFs are single threaded and if so, when using a stored procedure, is the whole stored procedure restricted to single thread?” Well yes, it is true – well actually it’s funny, because the UDF can go parallel, but the query that calls it is forced to run serially. That only causes the query that calls it to run serially, that doesn’t cause the entire, what do you call it, stored procedure to run serially, just whatever query it’s a part of. So that’s about that.
Who does the character drawings?
Erik Darling: Wes asks who does our character drawings? It’s a fella named Eric Larson, he has a website, Eric Larson artwork – he spells Erik wrong but Larson correctly. That’s who does them, and he’s wonderful. He just drew Tara’s new devil horns character…
Tara Kizer: Yes, I’m very excited about that one.
Erik Darling: I would be too, I need to get me a new character. I need to get some action shots, I’m all…
Tara Kizer: Yeah, you’ve been here long enough, you should have a few by now. I finally got my first. Richie’s always had his action shot since the very beginning, I think.
Erik Darling: yeah right, lucky Richie.
Have you used Rubrik?
Erik Darling: Let’s see here, “have you used a tool called Rubrik for SQL backups? If so, do you recommend it? What kind of testing do I need to do before I start using it?” I’ve never heard of that one, anyone?
Tara Kizer: I feel like that one came into our help account too, I think, and the answer was no, we haven’t used it, so didn’t answer it.
Richie Rump: I’ve used a tool called Rubik’s…
Erik Darling: Funny guy over here. So what I would say about that is, I would put it on the same level as any other SQL database backup tool. Make sure that you’re not taking dirty snaps, make sure that it quiesces drive activity, make sure that your testing restores. Also, keep an eye on the error log, because you want to make sure that it’s not – keep an eye on the error log, there will be I/O frozen and I/O thawing messages, and I would keep an eye on the error log to make sure that it’s not taking, you know, more than like a second or a couple of seconds when it goes to quiesce the drives. If you see big gaps in there where I/O is frozen for databases and then, like you know, 10, 20, 30 – I’ve seen up to two minutes later I/O gets thawed for a drive; you have a problem. You don’t know if it’s part of the VSS snapshotting process or if it’s part of the data copying process, but that’s what I would do.
Tara Kizer: I had a recent client, I forget what product they were using, but it was doing the – it was quiescing, freezing the I/O, unfreezing it. I think that only took, I say only, but let’s just say five seconds, but it would cause such a slowdown for the rest of the day because that system had such high write throughput that it just started gaining like a backlog. You could see the system performing fine, and then once it happened it would take, you know, an hour before the system was running well again. They were trying to do that process very frequently. I know Microsoft recommends not to do it any more frequent than every four hours.
Erik Darling: Oh yeah, because – was that the write log people or was that…
Tara Kizer: Something like that, maybe.
Erik Darling: Because I remember hearing about the write log things [crosstalk] that’s brutal. So I just got a notification code. [crosstalk]
Have you used 2016’s data masking?
Erik Darling: Alright, let’s see, what do we have next here? “What do you think about the new SQL Server 2016 data masking and Informatica dynamic data masking?” I’ve never touched…
Tara Kizer: They say Informatica, then I’m out. I’ve supported [crosstalk] Informatica for SQL Server and it was not fun.
Erik Darling: It’s funny, when I first started working here, a buddy of mine was working at Harvard doing a whole bunch of ETL with Informatica tools, and he was like yeah, can you do any testing on that? I was like, no. [crosstalk]
Tara Kizer: And the way Informatica collects the data on SQL Server, it adds a transaction replication publication, no subscription. So Informatica connects directly to the distribution database and figures out what data it needs. So that took a while to get used to. It just caused all sorts of problems for us.
How’s the current DBA job market?
Erik Darling: Alright, let’s see here. Ben asks an interesting question; at least I think it’s interesting. “What are your thoughts on the current DBA job market? What are the best places to find opportunities? Is there a particular website you like or where do you g when you need to find a job?”
Tara Kizer: I don’t know, I don’t know what I would do these days, but I know from LinkedIn I get a lot of job opportunity emails. Not just recruiters, but LinkedIn automatically sending out messages, and at least for the San Diego market, it has slowed down as far as the number of database positions out there. I just look at the list real quick and these days it’s been sysadmin type stuff that’s coming through, whereas about a year ago, there were several database administrator jobs here as well as database engineer, database developer type. I don’t know what the markets are across the rest of the cities of the United States and stuff, but it definitely has changed here, and it was a booming market here about a year ago.
Erik Darling: Not so booming anymore?
Tara Kizer: I mean, just based upon LinkedIn notifications and then the less recruiters sending me emails. I get a kick out of those emails. I mean, I post them in the company chat room to get a kick out of them too. It’s interesting information.
Richie Rump: [crosstalk] Kind of developer stuff…
Tara Kizer: You are a developer…
Richie Rump: I know, but it just seems that developers – it just hasn’t stopped…
Tara Kizer: Always, gotcha. [crosstalk] there are more developers than there are DBAs at a company, usually. I mean, there’s usually – large companies, a heck of a lot more developers than there are DBAs.
Erik Darling: It’s like a wise man once said, the world needs ditch diggers too.
Richie Rump: Yes.
Erik Darling: And with that, we have reached our time limit and the end of the questions. Thank you so much for joining us, we will see you again next week. See you then.