Episode 102: Monitoring Availability Groups

One of the newer features in SQL Server is availability groups, which can help solve a number of business problems.  As administrators, availability groups introduce some complexity as we are tasked to make sure the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) can be meet for these servers.  The complexity comes because we have different instances that must work together, but they don’t always see eye to eye.  In this episode, we chat with Tracy Boggiano of channeladvisor about how they go about monitoring their availability groups and the pros and cons of the out of the box tools.  Our discussion touches on process of  the availability group process and Tracy has posted her scripts on her bog for you use as you look at reviewing your environments.  I think you will enjoy this discussion.

 Episode Quote

“You just need to allocate the resources and play with the stuff in your staging environment and make sure you have resources”

“I much prefer having a query and using PowerShell and just running in the multiple instances

“We use a third party monitoring solution for our monitoring rather getting a whole bunch of SQL agent alerts”

Listen to Learn

– Monitoring availability groups
– Data synchronization on availability groups
– Asynchronous and synchronous mode
– A review of RTO, RPO and SLA
– Errors and agent alerts

Tracy on Twitter
Tracy on LinkedIn
Tracy’s Blog

About Tracy Boggiano

Tracy BoggianoTracy is a Database Administrator for ChannelAdvisor. She has spent over 20 years in IT and has been using SQL Server since 1999 and is currently certified as a MCSE Data Platform. She also tinkered with databases in middle school to keep her sports card collection organized. She blogs at tracyboggiano.com. Her passion outside of SQL Server is volunteering with foster children as their advocate in court through casaforchildren.org.

Transcription: Monitoring Availability Groups

Carlos: Tracy, welcome to the program.

Tracy: Thank you for having me.

Carlos: Yeah, it’s great to have another companera here with us. You are going to join us on October for the Companero Conference and so we appreciate that, and for you being on the show today.

Tracy: I appreciate you having me.

Carlos: Yeah, and I’ve been telling Steve we’re going to get through all those ChannelAdvisor folks. Now, we get to cross another one off our list.

Steve: Are there any DBAs there that we haven’t talk to yet?

Tracy: There is a couple left. Yes.

Steve: Ok.

Carlos: In fact, one of them I actually, so I met when I was down there in. He is a big fan of PSSDiag and so I talk with him about maybe coming on and seeing if he could convert me.

Steve: Oh, that would be interesting.

Carlos: Yeah. I’m like, it seems like a little. Anyway, that’s for another episode.

Carlos: Oh boy! Yeah, I’m not sure. Not today’s episode.

Steve: Speaking of which today’s episode is on monitoring availability groups.

Carlos: Yeah, that’s right. I think this is going to be near and dear to anybody who is trying to setup availability groups and ultimately that idea that you have DR scenario/situation and you want to make sure that the data is getting over to the secondary node. That you’re not going to lose that data that you kind of meeting your SLAs and so being able to ensure that just makes a lot of sense. And so, Tracy, it might be helpful why don’t we take a minute and just review that data synchronization process as we talk about availability groups? What are the components and what are some of the pieces that we need to be looking at?

Tracy: Ok. Well, the first step is that the log has to flush to the disk, and you have a log cache that it caches the records into that it has to send over to your secondary server. And it is stored in this area called the log capture area and it sends across the network, and at some point it gets an acknowledge commit whether you are in synchronous at asynchronous mode and that depends on what mode you’re in. Once the log is received on the other side it is stored in another cache into disk. And on the other side you have a redo thread that sits there and replace the pages back into disk. And you have a performance counters that capture your logs and queues, size, and the rate that it’s sent, and you also have the redo size and redo sync rates that are all captured and performance counters on both sides so you can monitor those.

Carlos: Right. Now, so we talk about asynchronous versus synchronous, right? So when I send it over, writes to that second node and then writes to the log, that hardening process. If it’s in synchronous, once it writes the logs, is that when I get the acknowledgment back? Or does it actually go through the redo process before it will acknowledge that?

Tracy: Once it hardens to the log it is sent back as committed. And when you’re in async it sits and hardens on your primary it’s considered committed.

Carlos: Ok.

Steve: So when we are working with that what are the things that we really want to monitor or keep tracking to make sure things are working well.

Tracy: One of the first things you want to make sure you don’t have is a lot of network latency. A lot of times this technology especially if you’re in async mode you’re looking at a DR situation or you had your secondary site in a secondary location like an AWS or different data center. You don’t want to have too much network latency. The other being that you want to make sure that your secondary storage aren’t slow and not able to process the data as fast as you need them to. Some companies like to go skimpy on their secondary servers and not have them as their primary servers. And they can get behind just because they don’t have
enough memory or CPU resources. So you want to keep an eye on those redo queues and make sure if it’s actually able to keep up or not. And that’s where it’s important to keep up with your SLAs and make sure that you’re actually meeting those or not.

Carlos: Right. Now, this is very interesting concept because you think, “Ok well, all that secondary server has got to do is process the log”, but on busy systems that can be quite a bit of work. And to keep up with that and again, ChannelAdvisor, you guys get to play with all the cool toys that some of the higher transaction rate systems you actually needed a pretty hefty server, secondary server, just to be able to keep up with that even though it wasn’t actually doing any “OLTP” workload.

Tracy: Yes, we had a system with a 256GB of memory on the primary side that we still needed 64GB of memory on the secondary side just to process the redo log to keep it current. I mean, we still are able to take 25% out. You know, we try to go less than that and we were trying to run in the cloud which can be a little bit more expensive and we weren’t able to do it. We started off at 16GB and it just wasn’t performing.

Carlos: Now, was that synchronous or asynchronous?

Tracy: Asynchronous.

Steve: So then when you say it wasn’t performing there, was it just that the continuous load was so great that it couldn’t keep up? Or was it that there were big sets of transactions that were kicking off at some point in time that were causing it to backlog and not catch up?

Tracy: We just had too many transactions going. We were hitting, 20,000-30,000 transactions per second and it just backing up on the secondary side. One we bumped up the memory it’s plainly a memory problem just trying to read all those pages into the buffer pool so it could update it. And once we bumped it up to 64GB it was able to keep up.

Steve: So then with that secondary backing up, if it’s backing up to the point that it can never caught up, what is the outcome there in the availability group? Does it eventually crash because of that or is it just very delayed?

Tracy: It’s just very delayed. It’s behind to the point you are not able to failover if you’re meeting your SLAs. In this instance we were just testing our AGs for ourselves. It wasn’t a production instance for us as far as we wanted to failover to it. It was just us testing our AGs on a production instance but not for a disaster recovery situation. But for us to see what we would need in order to setup a DR situation and we discovered that, “Hey, we’re going to have to allocate some resources to it.”

Carlos: Alright, now that’s another interesting question because you are failing over and it was just your test, so when we talk about not keeping up what is your window there? Is it 1 minute, 5 minutes, an hour, a day?

Tracy: Our service level agreement is set to 4 hours. But we were finding that the server is up to a day behind.

Carlos: So again, just to put in some of the perspective some of that, right? I wasn’t like you were asking for milliseconds or something like that. I mean 4 hours is significant, right?

Tracy: Yeah. I say you just need to allocate the resources and play with the stuff in your staging environment and make sure you have resources. We played in staging but we don’t have the transactions per second on the current staging that we have in production.

Steve: So I know when you’re monitoring presentation there are a few acronyms that you brought up along the way being RTO and RPO and SLA and how those apply in the availability groups scenario. Can we maybe talk a little bit on those and cover what that means to the availability group with the RPO and RTO and how
those apply to SLAs.

Tracy: Yes, the RPO is your Recovery Point Objective and that tells you how much data you are able to lose so that measures how many megabytes, or gigabytes, or data you are allowed to use so how much data has change that you can lose. Your RTO is how much time you can lose, so that’s how long it is going to take to do that redo on the other side. So if it says it’s a day behind that’s a day you have to wait for that redo log to play. These two are measured differently. You could have a gig of data behind but it will only take an hour to replay it or it could be the reverse, it could be a day behind and a gig of data to replay. It depends on how your system is setup. And those two combined to make your SLAs, your Serious Level Agreement, what your businesses agreed to allow you to lose data and how much time they are allowing you to recover that data.

Steve: Ok, great, so when we look at the built-in dashboard for availability groups. How well does that really do the job for you to be able to monitor and know what’s going on?

Tracy: When you are looking at a single AG it pretty much does the job for you. If you’re in our environment where you have 100+ databases, 100+ servers and you’re looking to have AGs on all of those, not so well. But you have to look at, you have to connect everyday and then to see what’s going on.

Carlos: So kind of broad brush strokes, it’s cumbersome. You have to connect to every single one of them.

Tracy: Yeah, you have to connect to every server and bring up that gone down through the dashboard in Studio Manager and bring it up. That’s why I much prefer having a query and using PowerShell and just running in the multiple instances and see which ones are on trouble, and going and checking those out. But overall is you have a couple to manage. The dashboard shows you what you need to know.

Carlos: Ok, so it’s pretty good there. It’s kind of meet your needs from an individual availability group or perspective but just that once you start getting with more than one then you guys are going to start rolling your own.

Tracy: Yes.

Carlos: So I guess talk us through some of the ways or some of the things that you’re looking at to accomplish that?

Tracy: Well, we’ve used the queries that the dashboard runs in the background. And one thing we’ve created is some PowerShell command lines that we can run and then it returns in a data grid and then we can sort and filter. And we can just run that again to any of our AGs that we have and fun data. We also have more focus onto our team who loads a lot of stuff into Grafana for us so we can see data.

Carlos: Yeah, you guys likes to meddle, who likes to tinker.

Tracy: He has our performance monitor, counter talking about before our logs and queues, our redo queues and the rates all logged into Grafana for us so we can go there and view them by instance if we need to. So we’ve got a couple of different solutions that we have that we run with right now.

Steve: Yup, so when we talk about your PowerShell scripts that you’ve got there. Are those something that’s internal and private or is that something that’s available for other people who take a look at.

Tracy: I actually have a blogpost to my website that you can pretty much plug in any SQL script you want into and run against multiple SQL Server and turn it into grid, so that you can run any troubleshooting script you want and it will run across any list of SQL Server that you provide and return it in a grid.

Steve: Ok, we’ll have to include that in our show notes, link to that.

Carlos: Well, another kind of interesting we’ll call it a hack because I like to call things hacks today. But that little cheat if you will of have a SQL Server 2 something. You pull up the manager, or the wizard, or the dashboard, whatever and then you pull up profile, I’m assuming or extended events, if you so choose and then you just figure out how it’s getting all its information and then you use that down the line.
I think that’s a great way to be able to grab that stuff and understand what the SQL Server team has put together to look at.

Tracy: Yeah, I saw what I did. First, I pulled up the dashboard. I’ve seen it only comes with like 5 columns and it’s like that’s not very useful. It has a add/remove columns out to the side so I started looking at the columns names and I was like, “Hmm, these are the things that are useful to me and that’s what I went into the DMVs to find.” And that’s what I added to my queries.

Carlos: Interesting. So I am curious and maybe it’s different because I know you guys are on AWS. So in Episode 76, we had Jimmy May on and he was talking about some of his testing experience and the improvements they’ve made particularly in their redo log and whatnot. It just made me think are there scenarios or real situations or is there anything that you can do so let’s just say maybe the scenario would be, I guess it’s not really a cluster, so patching. This may not work but I want to use the patching scenario. Maybe not exactly what I’m looking for but you need to failover. You need to move from one node to other for whatever reason. And let’s say that you’ve gotten behind, what are you going to do or what kind of things are you looking for to help you get back up to speed? Does that make sense?

Tracy: Yeah. In our situation, the AGs we currently have it run in production are synchronous on premise ones because we don’t have our DR stuff that’s setup currently for AGs. We have a different solution for DR at the moment. So those are synchronous and up to date but if you were doing an async environment because we have done this to do migrations. And to migrate from 2014 to 2016, we use AGs, so we set up AGs as asyncs and then when we got ready to failover we set them in syncs. Like the day before so that we get caught up and then we did the failover. It would be the same though with patching. You would go ahead and patch your server, set it to sync, but it force it to catch up, and once it’s caught up you’d failover to the patch server.

Carlos: Got you, ok.

Steve: Ok, so then I guess one of things that I’m interested in is that with the monitoring that you’re doing it sounds like you’re doing your own beyond what the built-in is just so you can get across multiple servers all at once. But are there additional things that you’re monitoring that aren’t part of what you would see through the normal dashboard there.

Tracy: The only thing I say I’m doing differently is the rate. The rates in the dashboard aren’t updated and laugh because Microsoft only updates those when data is being sent across. But if you’re not actively sending data the rates aren’t updated so you got some monitoring around your rates. It may not always be accurate so I do the monitoring base on the performance counters DMV rather than the DMV that just stores the rates for availability groups. So I hava a as part of my downloads for my presentation I have a thing that captures it, captures it again and actually calculates the rates that you can see the difference between what DMV for availability groups say and what rate actually is. Just in case you need to know what the rate or what the real rate of actually use as occurring. But other than that it’s pretty much straight what the queries are running on the dashboard.

Carlos: Ok, great.

Steve: Now, there is another feature or option that is available to us to be a little more proactive if you will as far as letting SQL Server notify us when it has a problem. A lot of us are going to be familiar with the alerts. I don’t know if they are called agent alerts but these are the 19-25 or 20-25 and then those like 18-32, 18-24 etcetera because of problem breathing from disk and things like that. SQL Server can say, “Hey, I had this problem, just thought you should know.” We have a
couple of those for availability groups, right?

Tracy: Yes, I think there are about 5 of them. There’s an error 1480 that lets you know if the server failed over. There is an alert for when the data movement has suspended for some reason. I’ve seen it suspended because the disk is out of space for example. There is also one that will take when it resumes. So if it decides to resume by itself or somebody actually puts a button and resumes it. It will tell you if the AG goes offline for some reason and if you’re in synchronous mode it will tell if the AG is not ready for an automatic failover. You got to set up for automatic failover. All those numbers are available using my, you can look those up online or download my slides.

Carlos: Sure, and I suppose something like that, not keeping up some tinkering will need to be involved to kind of make sure that you have the right secondary formula because I would have imagine you don’t want to be getting a lot of those emails. You get a couple of like, “Ok, more memory than the secondary”, or something, right?

Tracy: Yeah, that’s why you wouldn’t configure your secondary for automatic failover if you didn’t want to know if it wasn’t ready for automatic failover for example.

Carlos: Well, fair point. And I guess the automatic failover. Those are probably going to be a little more similar in size.

Tracy: Yeah. You definitely don’t want to have those set up exactly the same. That way when it failed over you got the exact same performance going on because those are going to hopefully happen at 3:00 in the morning. And you necessarily wouldn’t get paid when something fails over automatically either because hopefully that’s what you wanted so you could sleep. That’s why we invented AGs, to sleep.

Steve: Yeah. Nice, so when you’re monitoring your availability groups are there any weight statistics that you normally take a look at or keep an eye on just to see how things are going?

Tracy: There are a few that you can keep an eye on. The one to watch out for is there’s one the HADR_SYNC_COMMIT to tell if it’s taken a while to commit on your side or not. I’ve seen that one pop up when it’s just waiting to commit. We know so a lot when we were low on memory. The other one is write log. That will typically occur if it’s taken a long time to write a log on your secondary. Other than that I haven’t seen any in particular there pop up in our environment.

Steve: Ok, so with the sync commit is that when it’s doing the synchronous or asynchronous mode and it’s doing the commit and you wouldn’t see that if you’re running an async mode.

Tracy: I’ve seen it in async and sync. It’s just whenever it’s doing a commit.

Steve: Yup, got it. Alright. And then as far as any extended events, is there anything we should be aware of or keep an eye on there when we’re working with availability groups.

Tracy: Mostly extended events it creates always on health session by default for always on and everything you need is pretty much in there as far as events that you need including all those agent alerts that we’re talking about.

Carlos: It creates a separate event, separate monitor or it just includes them in the default extended event trace.

Tracy: It creates a separate one for you called always on and it includes all the events that you need to know including all those alerts, the agent alerts we were talking about. Those are all included in there and as part of the demos that I do at the presentation. I’ve got queries that query those out and we actually as part of our internal monitor just query those directly rather than receive SQL agent alerts. We use a third party monitoring solution for our monitoring rather getting a whole bunch of SQL agent alerts.

Carlos: Yeah, that’s right. I mean once you get so so many instances you’re going to want kind of a complete solution there.

Tracy: We don’t want so many emails.

Carlos: Yeah, exactly. Well awesome, Tracy, great information on availability groups. We do appreciate it.

Tracy: No problem.

Carlos: Should we go ahead and do SQL Family?

Tracy: Sure thing.

Steve: So Tracy, how did you first get started with SQL Server?

Tracy: Well, I was a developer at a company that wanted some reports and Access wasn’t good in it as far as querying stuff so I imported some data into SQL. Well, I had to learn a lot of stuff. I then I just kind of got hooked ever since. I took some training classes on it and next thing I know I was a DBA. I’m not a developer anymore.

Steve: Very nice. It’s interesting to see how often Access is the lead into SQL Server for people.

Carlos: Yup. Tracy, I know you guys are using a lot of features over there at ChannelAdvisor but if you could change one thing about SQL Server what would it be?

Tracy: Right now, I wish hackathon would quit writing so much to my error logs. I’m getting gigabyte log files every night on my system drives and they’re driving me crazy.

Carlos: Oh wow! That is a lot of data.

Tracy: And it’s just informational messages.

Carlos: And there’s no flag or nothing to do to turn that stuff off.

Tracy: Nope.

Tracy: I’ll stick with that one for right now.

Carlos: And so what’s your say about that? Surely you’ve, he knows about it.

Tracy: I don’t know. Brian has mentioned though. I’m not sure what Microsoft said about it yet.

Steve: Alright. What is the best piece of career advice that you’ve ever received?

Tracy: Just to always keep learning stuff. Feel change a lot in IT. What you know now is going to change. As you can tell SQL Server. We’re getting releases like every year. And now we go learn a new operating system.

Carlos: Yeah, never stop learning. Tracy, our last and favorite question for you today, if you could have one superhero power what would it be and why do you want it?

Tracy: I want to be able to teleport.

Carlos: Teleportation, ok, and why is that?

Tracy: I spend a lot of time in my car doing volunteer work and it will save me a lot of time.

Carlos: There you go, very good, awesome. Tracy thanks again for being in the program with us today.

Tracy: Ok, thanks for having me.

Steve: Yup. Thanks, Tracy.

Episode 100: Role Reversal

Something a bit strange happened in episode 100–almost like something out of the twilight zone, but don’t take our word for it.  Check out our latest episode as tell some of the stories that led up today.

SQL Server Podcast

Transcription: Listener Q&A

Kevin: Hello friends, welcome to the SQL Data Partners podcast. The podcast dedicated to SQL server related topics which is designed to help you become more familiar with what’s out there, how you might use those features or ideas, and how you might apply them in your environments. I’m your host Kevin Feasle. Today on the podcast we have two special interview guest, Carlos L. Chacon and Steve Stedman. Carlos and Steve, welcome to the program!

Carlos: Oh, thanks for having us, Kevin.

Steve: Thanks, Kevin, this is exciting.

Kevin: Absolutely, so we’re on Episode 100 of the SQL Data Partners podcast and oddly enough you also have a podcast. It’s weird how that works, huh.

Carlos: Yes, there is an interesting turn of events here.

Kevin: Carlos, what made you decide to start podcasting?

Carlos: Wow, that’s a great question and I guess I will say that the front if I knew how much time and effort it was going to take I don’t think I would have started it. So I knew that I wanted to engage other folks and start talking about SQL Server in a kind of a long form way. I’ve been doing a bit of blogging. Ultimately, looking to help my consulting practice or re-launch it really in a way. And so that kind of content marketing, so taking the long view of having content available to people kind of interact with find, search engine optimization, things like that. I’ve been doing some blogging. I tried to do some videos and just found that difficult. At that time there were only two SQL Server podcasts in iTunes which is the main place where people go to find podcasts and then there are lots of apps that will carry the podcast that are in iTunes and Google Play has come out there. Ultimately, I thought, “Gosh! There is only these two.” And Greg’s SQL Down Under hadn’t new episodes hadn’t been there for a while and so I take in John Lee Dumas’s course on podcasting and thought, “Hey, you know what. Why not, right?” Let me jump in, let’s see what happens here. I guess I will try to do 10 episodes. So before I actually officially launched I’ll do 10 recordings, see if I like it. See if I can actually get 10 people to sit down with me and talk. And what’s weird, so I started the podcast when I was in Costa Rica. I took my family over there for two months and we were down there. And while I’ve done my first interview actually in Argentina at a SQL Saturday, I kind of officially started doing interviews in Costa Rica. So that’s the kind of the long answer to why I started it. I though there weren’t very many people at that time doing podcasting and I thought I would give it a try and kind of see what happened, and wanted to commit to do it for one year.

Kevin: So when it comes to things that are time consuming, things that are kind of beneath the iceberg, what are the most time consuming parts of creating a podcast episode?

Carlos: Steve, I think you will attest to this. The first is just getting use to hearing your own voice.

Steve: Sure, and realizing when you’re doing that that you don’t always have to do it over again and just because it doesn’t quite sound the way you were hoping it sounded.

Carlos: Right. You don’t have that ability to do the editing, right? In the written word you could edit it, “Oh, that doesn’t sound quite right, let me go back. Let me tweak that.” As you do that audio-wise, just hearing yourself repeat the same thing over and over gets a little cumbersome. You know, trying to remove all the “uhms” and “ahs” and whatnot. In the beginning I wasn’t using an editor, I am now. That actually happened in Episode 29. That started happening and so I would have to edit my own. So first is scheduling the guest, picking the topic, creating the agenda, actually having the interview, making sure that I had questions so the prep work associated there, then editing it, writing show notes, getting links together for the show notes page; so those are some of the pieces that are involved. But the biggest piece in the beginning, again, I had those 10 episodes and I had told people that in August 2015 is when I would first start. So August came around and people started asking me, “Hey, have you launched that podcast yet?” So literally, again I was then in Costa Rica, that week I spent getting everything ready, and did a lot of editing. And that was really probably the biggest piece in the beginning that just took so long was just listening to everything again trying to figure out, “Ok, is this ok to keep?” Again, you don’t
know what people are expecting. You don’t want to disappoint the people that you’ve interviewed, you know, all those things. Those are the components I wanted to.

Kevin: Yeah, I remember really early on when we first got the other I think it was Episode 13. You had a little piece of paper where you’re writing down, ok this mini minutes, this mini seconds, that’s where somebody said something really bad. You got to cut that word out.
Carlos: Right. No, exactly, yes so I guess it’s interesting that way that processes changed a little bit. We’ve gone some good feedback from the show and now the processes, we actually just record it, you know how it is. I take it off for transcription and then I get the transcription back and I edit the transcription and then Julien our editor, great guy, will actually then edit out anything that I don’t want there. I mean, in addition to all the “uhms” and “whatnot” which he does I think a great job of. So that’s some of that how that processes changed a little bit. Because when I was doing it, yeah, I wanted to write that down because I wanted to try to speed that process up.

Steve: Just to add a little bit more on the time consuming parts of it. I mean, the one that Carlos does most of the time is the scheduling of the guest. I know that one takes up a lot of his time. But then once we have the guest scheduled it’s a couple of different recording sessions that we go through in order to get an episode out. We’ll have the session with the guest which can be one or more guest and that’s usually at least a week before the podcast airs. And sometimes this is much as 3 or 4 weeks at a time when we have a lot in the queue there. But that’s usually about, I don’t know, a half hour to an hour of preparation time we go through there to be ready to talk about whatever the topic is. And then it’s usually about a half hour to an hour of actual recording time, and that’s gives us the section that’s the part that we are talking with the guest. And then about a week before the podcast airs, usually that’s the Thursday before the podcast airs; we do our intro and closing. And that’s where we go in and we talk about the SQL Server in the News. We talk about any mentions that we’ve had out there and then we go in and sort of digest what we talk about with the guest at the end as well. I think that’s usually about an hour of time to put that together.

Carlos: Yeah, that’s true.

Steve: And then once we’ve done that part, or maybe Carlos you can jump in with any additional time but it’s kind of handed off to the process through the editor and through the assistant that we have in getting that all published.

Carlos: Right, yeah I mean, I guess thinking back just because we do have that process now which helps quite a bit but there is still each of those individual pieces to take some time.

Kevin: Oh, I can imagine.

Steve: And then once it’s out then there’s promoting it. And I don’t know I always get around doing it myself but we try and do what we can to promote the podcast through Twitter, LinkedIn or places like that so people know that there’s new episode.

Kevin: Cool, so next question. I’ll start with you, Steve. What episode was your favorite?

Steve: Wow, well, if you would ask me a couple of weeks ago I would have had a different answer but I think Episode 99 I thought was one of the favorites that I’ve gone. If I said that previously before Episode 99, it would have been the indexing episode that we did with Randolph West. But just the whole impostor syndrome conversation that we had with Mindy in Episode 99 that was different than a lot of things we talk about before and I love it.

Kevin: Yeah, I just listened to it yesterday. It was great. Well done, Mindy!

Carlos: Yes, she did a great job.

Kevin: Very much so. So Carlos what was your favorite episode?

Carlos: Gosh, you know that is a tough question.

Kevin: Choose among your children.

Carlos: Yeah, that’s right, exactly. So generally because I am a more the merrier type of person the ones that I have really enjoyed been the ones where we’ve had kind of a panel type discussion. Right, so I think about Episode 59 where we had Andy Mallon and Mariano Kovo on. I think about episode when we had the panel from the DBA Tools folks on.

Steve: Oh, that’s was Episode 91.

Carlos: Yeah, 91.

Kevin: That one was a lot of fun too.

Carlos: Right, so those, even the one that we did which ironically enough you and
Jonathan had that great interchange and I didn’t get it in the program but the ones that we do with the SQL Saturdays where we have multiple people kind of giving their input or thoughts around. I mean, again, not that the individual interviews aren’t fun but by getting different perspectives just makes the conversation flow much easier. Different things come up that Steve and I haven’t talk about beforehand and it enables the conversation to go in different places.

Kevin: Nice. So Carlos, I’ll start with you this time. What has been the most pleasant surprise for you in the making of the show?

Carlos: I think probably the continued relationships that I have been able to have with the guests. Now, that’s not to say that all of the guests are now my best friends because that’s not true. But for the most part, I’m just looking here at the list here; I have continued conversations with my former guests in some ways, shape or form, so I’ve really enjoyed that. I think being able to connect with folks that I wouldn’t otherwise have been able to do.

Kevin: How about for you, Steve?

Steve: I think it’s a lot of the same lines as what Carlos said. But I would go a little bit further to say it’s not just the guest but it’s also the guest and the listeners in that there’s been a lot of listeners who have reached out to me and connected on LinkedIn. I mean a lot of people follow on Twitter. But it’s really nice when somebody connects and you make that personal connection there and getting to know people and sort of extending the reach of who you know in the SQL community.

Carlos: I guess I will add one thing there and that is there had been more than one guest I’ve reached out to and they’re like, “You want me to do what?” I guess I’ll point one out so in Episode 45, so Wolf, up in Pittsburg. He was the nervous wreck, and I said that lovingly. He did not think he had the chops basically which is again ironic for a guy like him. So it took me a while to convince him to, “Hey, let’s do this. Let’s make it happen.” And then when he finally did to kind of see that boost in confidence it was well received. We had some comments on it so that was very gratifying as well.

Kevin: Very nice, so let’s switch gears entirely away from podcasts. Want to talk a little bit about consulting, so both of you are now independent consultants? Yes?

Carlos: Yes.

Steve: Yes.

Kevin: How long have you guys been independent, on your own or together, independent together?

Carlos: Sure, so I’ll let you go first, Steve.

Steve: Ok, and it’s a complicated answer because it has changed over as different things have happened. But I originally started as an independent consultant about 12-13 years ago. And when I did that, I mean, it was going well and then I ended up with one client that sort of ended up taking up all of my time. And then after about 2 years of being “independent” with only one client, they brought me as a regular employee, and I was there for about 7 years. And then it was about 2¼ years ago that that ended and I went back to true freelancer at that point. I said, “I don’t want to go and get a regular full time job because that’s not for me. I like the challenges of consulting and working with lots of different clients.” And then it was about, so I did that. I started my own company doing that, Stedman Solutions, and that’s been doing great. And then about a year ago, Carlos asks me to join him on the podcast. Not in any more of a business relationship than that but I joined and started helping in the podcast, and then about six months ago, maybe 8 months ago was when we decided that we would merge together between what the two of us do much more closely. Now, I still have some clients I work with under my old brand name that Stedman Solutions. But most of the
new work that we are taking on is under the SQL Data Partners brand doing independent consulting there.

Carlos: Yeah, so for me this is my third attempt.

Kevin: Third time’s a charm.

Carlos: Yeah, that’s right, third time’s a charm. In fact, Steve and I were just talking about this earlier and that is one of the things that I wanted to do is make money in the way that I wanted to make money which can be difficult. And so I kind of got fits and starts. I’ve told people before that, so originally I started consulting because I saw other consultants making very high hourly rate. And while lots of people do the hourly rate thing and that’s all very nice and great and whatnot. Just because you have a great understanding of SQL Server at least does not necessarily mean that you will make a great consultant or business owner, entrepreneur and that’s really the most important key is to stop thinking yourself as a database person and just start thinking yourself as an entrepreneur because those things are different and they get attacked differently and so that was part of my learning curve in this kind of stops and starts.

Kevin: Ok, so let’s say we have somebody in the audience who says I’m ready to go independent. Any of my employers who are listening I’m not that person in the audience. But if somebody in the audience is saying, “I’m ready to go independent and hey you just told me that being an entrepreneur is a completely different story. Well, what types of things do I need to think about before I take the plunge?”

Carlos: Marketing. So what kind of problems are you going to solve? From the tech perspective, as a full time employee, people come to us with problems whether that’s a ticket, an alert, but the work comes to us. So now as a consultant the question is how are you going to find the work and what type of work are you going to respond to, and making sure that you understand what that work is and can describe it to other people.

Steve: Yup, I think I’ll echo the same thing there. And I think that when I talk about how this is really my second time in the independent consulting where I had been doing it before and then it turned into a single client. Part of the reason that happened was at that point in time I didn’t know what I was doing and how to go out and make contact with those new clients, how to meet the new customer. And I think that’s something you can do and you can practice and work with is just who is in your network or who do you know that you can make contact with that could be providing you work. It’s surprising that there is people that I have come across I end up doing work with that I never would have necessarily considered as a perspective client in the past. But I think other things to think about for someone who wants to jump out and give it a try on their own is the security behind it.

Carlos: Or lack thereof.

Steve: Exactly, or lack thereof it. Now, I think that when you have a regular full time job most of the time there’s the illusion that it’s fairly secure. And I used that term “the illusion” because whatever happens in people’s lives, full time jobs can come to an end at any point whether it is company going out of business or a layoff or just someone knocking in along with their manager, that job can come to an end. But you generally have a lot more protection legally in different ways as a full time employee, and you have much more security, and that you know if things get slow for the company odds are that you’re still going to be getting a paycheck 2-3 weeks from now. It’s never guaranteed but with a full time position that’s pretty stable. You know that in every so many days you get a paycheck and it’s generally for about the same amount. And I think that when you go into the consulting arena that changes significantly because you run into what they call bench time or a point where you don’t have enough work for a while. And that comes back to finding your customers and marketing and reducing that bench time. But when you’ve got that bench time you’ve got to have, depending on how you’re paying yourself because the customers pay your business and then you pay yourself out of your business, you’ve got to have a buffer there so that when you do have short times that are either bench time or a period where it’s hard to get payments from clients that you can cover it. And I think it would be different for maybe a single person versus someone who is married with kids. But I know that if what I’m doing if suddenly I stop having money to contribute to my family my wife gets a bit worried about that. Alright, so part of what I do to help mitigate that is one you need to have a little bit of savings in place so that if you got a 2-week period where all of the clients decided they’re going to be a little bit late on payments you can wither that out without having a lot of financial pain right there. And then the other is, I mean around that is you’ve got to be kind of really hard with the customers when they are late. And I know that’s a challenging to do but to be able to come back and say, “I can’t keep on working on this project if you’re not going to pay.” Fortunately, it doesn’t come to that often but I think just being in a position of financial stability and I like to use the number of having 6 months of your bare minimum cash that you need to survive in the bank in order to start out doing consulting because when you start out, you are going to make mistakes. You’re going to have more expenses than you need. But there is going to be a lot of things that are challenging in that first 6 months and a lot of them are going to come down to financial challenges.

Carlos: Yeah, and I think just to echo there with Steve. Talking about that transition from the tech space to entrepreneur space so the soft skills becomes much more important there. So he mentioned kind of dealing with client payments but that whole process of just interacting with people. Once you go independent you are just no longer interacting with technology, that Idea is dead, right? Your clients are people and you have to satisfy their kind of needs first if you will.

Kevin: Right, so what point do you guys engage services of say a lawyer or an accountant?

Steve: Oh, great question. Do you want to take that or do you want me to jump in, Carlos?

Carlos: Yeah, so from the accounting perspective, from Day 1, I wanted an accountant there to at least be able to handle some of those things. So kind of goes back to economics if you have taken Economics course. You know, one country makes coconuts really well and the other one does bananas, they trade, so that is kind of the idea of hiring an accountant unless accounting is your business. Get somebody to help you with some of those things because the IRS does not mess around, at least in the United States. I can always imagine for other countries so you don’t want to get started off in a bad foot there.

Steve: And I little more on that, I mean, I don’t want to be an accountant that’s why I work in SQL Server. I wanted to thank SQL Server. If I really want to do accounting I probably would have would taken Accounting in college and gone that direction. Because of that, I mean, there’s a lot of people out there who are great at what they do with accounting and I would rather engage an accountant when it’s appropriate than try and learn all that on my own. Now, that being said, it doesn’t mean that I want to be completely illiterate on the accounting and financial side either. And I think that there are some tools out there like QuickBooks online that make it so that a lot of the stuff that you might normally need a bookkeeper for that you can do yourself. And then you can engage an accountant when it comes to tax time and all the appropriate times that you need to use an accountant there. Interesting story I mean on this when I first started back into freelance a couple of years ago I engage an accountant that gave some really bad advice. It didn’t feel quite right at that time but it came from my accountant so I believed it and then later I found out it was bad advice and that it made my first year’s taxes very challenging to get done that year. And looking back I don’t work with that accountant anymore but I work with accountants and I do a little bit more checking backgrounds and get a better understanding of who they are before working with them.

Carlos: From the legal side, generally, that’s just in the review process so it’s going to vary state by state and of course obviously country by country what the requirements are for setting up a business. Generally, so at least with me I had an attorney just kind of review some of those things or at least consult to make sure I was doing the right things. My accountant actually helps quite a bit with some of the legwork to help reduce some of that cost.

Steve: Yup, and I think that, I mean the key is use lawyers as needed. And I think there’s a lot of people who gripe at lawyers in what they do but when the time comes when you really need a lawyer. I mean again I don’t want to be a lawyer myself. I don’t even want to try to attempt that. But it’s good money spent usually because you’re in a position that you have to use a specific expertise that you don’t have.

Carlos: Yeah.

Kevin: Ok.

Carlos: And nothing else again kind of those soft skills relationships you want to be on speaking terms with someone before you have a need for their services. You’ll want to shop that around or get somebody you’re feel comfortable with rather than somebody that you have to have because you have no other choice or alternative.

Steve: Yes, that’s a very good point.

Kevin: Cool, so let’s talk a little bit about Database Corruption Challenge. Steve, what made you come up with this idea?

Steve: Wow, alright, it was interesting and I think that there is a lot of detail that Carlos actually asked me on this on Episode 12 where I first was on the podcast. It started out initially because I do lot of blogging on SQL Server topics. It started that I wanted to share some of my knowledge about database corruption and fixing it and I started writing a blog post about how to fix corruption by pulling data in from non-clustered indexes to try and figure out what was missing. And I realized that anybody could do that. I mean anybody could write a post like that so I thought, “Well, I change it up a little bit.” I’ll go and actually create a single corrupt database and I’ll put that in the blog post as a training exercise to see if somebody, to see people interested in trying to solve that. That was a Saturday. I think I did that on a Saturday morning and I threw it out. I put it on Twitter and a few things. I said, “Ok, no big deal. Nobody found it interesting.” And about 8 hours later though it got some traffic and that Brent Ozar picked it up and he decided he was going to jump in and solve it, and he solved it pretty darn quick. It think his story was he and his fiancé at that point were trying to head out to dinner when he saw this and he stopped what he was doing and fix the corruption before going to dinner. That might have cause a little bit of trouble, maybe been a little bit for dinner but he was the first to solve the first week of the corruption challenge and then he tweeted about it, and that sort of got the fire going there a little bit around more people being interested in it because I think he has a little bit more of reach on Twitter than I do.

Carlos: He can move the internet numbers that’s for sure.

Steve: Yup. After he solved it then a handful of other people jumped in to solve it and it’s at that point I realized, “Hey, this is really interesting. There is a lot of interest here. I’m going to do another one.” And then I kind of quickly made some rules and said, “Well, I could do this for 10 weeks.” And that was my initial plan, 10 weeks, but it turned out to be 10 competitions over about every 10-14 days not
every single week, and it just kind of grew from there. There were about 60-70 people who actively participated week after week and it just kind of evolved at that point. It wasn’t that I ever like sat down and thought, “Hmm, I’m going to build this Corruption Challenge.” It was just sort of a blog post that evolved and became the Corruption Challenge.

Kevin: Yeah. I remember it being a big deal and it’s still really interesting to go back because those corruption issues they still happen today.

Steve: Yup, oh yeah, and I think today I get a lot of traffic if you go to the stevestedman.com/corruption you can get to all the blog posts that I’ve done as well as all the 10 weeks of the corruption challenge. Check it out there and I get a lot of people that even though it’s been 2 years people are still learning from it, and I think almost everything that I cover in the Corruption Challenge is still valid today even in the latest versions of SQL Server.

Kevin: How much did you learn during that challenge? You started out obviously the first database you knew how to do that. You put the example together. When we got to some of the later databases did you know already all that stuff beforehand or did you have to go research more corruption, reasons for corruption?

Steve: Oh, yeah, I certainly did not know all of that when I started. I knew a lot of it but it’s one thing to know about a type of corruption and it’s a new another level to know enough about it to go be able to create it in the database that can then be backed up and distributed to people to try and fix it themselves. And there was sometimes where I thought, “Ok, well here is something I know what the corruption is but it took me 4-5 hours to go and actually build a test database that had that kind of corruption in it.”

Carlos: Right, and then to make sure that, you know, can I fix this. Is this fixable, right?

Steve: Yup, and then I think that the people who participated actively in the Corruption Challenge were incredible to be able to learn from. And I know that the participants in the first few weeks were very helpful but they were also very critical in a positive helping kind of way if anything that I tried wasn’t quite right. And there was one or two of the weeks that I put out a corrupt database and then somebody pointed out some flaw and then I have to go back and correct it in order to make it so it could actually be fixed someone.

Kevin: So of the solutions that you got, what was the most unexpected and interesting solution?

Steve: The most interesting and unusual one that I came across was Patrick Flynn, and I think he is from New Zealand. And I think it was for week 4 or 5 somewhere around there in the competition. It was one that, it was a particularly nasty corruption scenario but what he did, and one of the reason I loved it because I like CTEs, and I actually wrote a book on Common Table Expressions a while ago but it really use CTEs creatively. It was one that I actually adapted and I use it in my presentation at PASS Summit last year on database corruption. But what he did is using some temp tables and CTEs; he was able to use the DBCC page command to extract all of the data in horrible binary format into temporary tables. And then from there used CTEs to manipulate and extract all the data out of those temporary tables and reconstituted into INSERT statements to rebuild the table from scratch. I mean, if we had an hour I could walk you through the demo how it works. There were a lot of really awesome solutions but that’s the one that just jumps out at me as, wow that one was vastly superior. Not vastly superior, it was the most interesting and the one that I enjoyed working through the most. Part of the process when I did that challenge was, it was a competition people would see who could be the first one to solve it so I would throw the Corruption Challenge out there and then usually after Week 2, within about an hour, I’d start getting people submitting solutions and I would have to go through and confirm that their solution actually worked. And that one probably took me the longest amount of time to understand that it worked because it was so interesting and I just wanted to dive in and totally understand every single thing it was doing. I love that example, that’s my favorite out of all of them.

Kevin: Very nice. Let’s switch gears again, we’re going to talk about a very nice conference. Carlos, why did you pick such a hard name to pronounce for Compa Con?

Carlos: Compa Con. Yes, well I didn’t consult you, number one. And then I guess have you tried finding a URL lately, right?

Kevin: This is true.

Carlos: Ultimately, this is an extension, will be honed to be an extension of the podcast. This idea of bringing people together, talking about SQL Server in different ways, you know, ways that people might be using today or think of ways they haven’t consider with new features. You know, just different ways to attack different problems. Like Patrick’s solution for the corruption challenge, sharing that type of information. And so actually before I launched the podcast I wanted a name for the people who listen to the podcast. Kind of create a sense of community and that idea of companero kind of came to mind. I put a little video of this together out on Twitter or on YouTube rather. So companero is a Spanish word for companion and as a missionary for my church I had a companion and so we were companeros. And this person, we worked together, you know, 24 hours a day and this is for 2-year commitment. And so having good companions along the road just help things goes smoother and so again that was this kind of idea for the podcast of we want to get people together to talk about helping you get from one path to the other. And Steve and I are both actually big scouters which we didn’t find out until kind of after we started talking and so that idea of being on the trail, right? You know, known paths versus unknown paths and if you have a guide just how much simpler that makes everything. And so that’s ultimately where the idea of Companero Conference came from and then we’ve been developing that idea with the hopes that people will com. Right, you get access to folks that maybe you don’t know but we’ve. Now, I hate to use the word vetting, it’s not like, you know.

Kevin: Extreme vetting.

Carlos: Yeah, everyone’s records, IRS, background checks, all that stuff know it. These are people that we feel comfortable inviting I guess is the word to share because they knew they would be willing to share some of their experiences and do so in a way that would be positive for those who came. We hope that people will come, get some short experiences, get some help, would be able to ask questions with things that they haven’t yet face. But also then be able to when I get to a trail or scenario that they haven’t experienced before that they’ll be able to reach out and ask more than just Google.

Kevin: So, Steve, what are you looking forward to with Compa Con?

Steve: The biggest thing I’m looking forward to there is being able to meet more the people that we interact with on the podcast and meet them in person. I mean, and whether it’s the speakers that are going to be there or the attendees as well. I mean, I’m excited about the business venture of course in doing the conference but really what it comes down to is getting to know the people. Yeah, that’s it for me there.

Carlos: Alright, I will say one another thing and that is I remember again being a full time employee and not using my training budget normally because the budget was not high enough to go to some of these other conferences like PASS Summit that required to travel across the country and things. And so we wanted to, it’s like could we create something that people could afford within the budgets that they have and still come to something that’s not somebody opening up a book and you’re getting That’s not helpful, I mean. And so that was another element to that is again through the listeners they were getting value out of the podcast. We thought, “Ok well, what value can they get when we get together and can they leverage some of those budgets in a way that it will get approved, that meets the criteria of a conference and also allows them to expand their network a bit.”

Steve: Another thing to add to that that I’m really excited about too with the conference is the office hours concept. I think that quite often you go to a conference, you sit in a session for an hour or half a day or whatever it may be with the speaker and then when that’s over, it’s over. You go back to work a couple of days later and you try and use some of the things you’ve learned. Whereas with this we’ve nearly end of the conference we have an office hours slot where you will be able to meet with any of the speakers that are there to be able to discuss, or talk, or find out more about the topics that we are covered in their presentation. And I think to me that seems like a lot of fun.

Carlos: Yeah, and because the way the setup is we’re going to sprinkle that in with little bit of hands on learning. So yeah, that will be a slightly different take because I think it will be more authentic. One of the things that we are trying to do, I hate to use the word “can”, and we’ll have some scenarios where people can walkthrough individually. But we are hoping that most of this growth is kind of organic in the sense of, “Hey, you know what, Kevin, like I know you are talking about security I’d like you to show me this security thing. Can you walkthrough with this with me?” And then people just start talking, conversations in sew and you’re getting, “Yeah, let’s take a look at that. Here is how you do this.” So still kind of “hands-on” but it’s organic.

Kevin: So the conference itself will be October 4th and 5th in Norfolk, Virginia. I hear there is something involved with a boat?

Carlos: Yes, we’re going to have an evening cruise, so down there and all of a sudden I can’t remember the name of the river but we are very close to the Chesapeake Bay. One of the rivers that shoots off of the bay and of course Norfolk is a big naval yard and there is lots of traffic in that area so it will be very pleasant and it will be in the evening, the sun will be going down so will get to go out two hours out on the boat. We will actually eat dinner there as well and have a little bit of fun. There will be a top deck open air, you can go out and just hang out, again have some conversation or there will be dancing. So there’s three levels, in the second level we will have food and dancing and the third level is just kind of relaxing, you know, enjoy the weather.

Steve: And you are welcome to come along even if you don’t want to be part of the dancing.

Carlos: Yes, that’s right. We want to be very introvert friendly and so while we can’t get that third section just to ourselves. If it’s everyone’s intention we can definitely go over and push everybody outside.

Kevin: I’m claiming the nice spot against the wall. So sounds it’s going to be a blast. How about we talk about SQL Family now?

Carlos: Let’s do it.

Kevin: Ok, so how did you first get started with SQL Server? I’m going to start with Carlos for this one.

Carlos: I think I have the atypical answer, the accidental DBA kind of fits, so I want to be in networking. Networking is what I wanted to do. I did an internship for Cisco Systems. The company that I’d work for was purchased by Cisco Systems and so I wanted to do networking. That’s what I wanted to do. I went to college, I wanted to get my CCNA, all that stuff. My first job was working for a small consulting firm both kind of doing their internal IT. So it was 15 consultants, so I was doing things like email, networking, security and then setting up environments for the consultants so they could test things and whatnot, and SQL Server kind of came along with that as they were doing some of the applications. One of the consultants leaves and goes to work for the State and he calls me a couple of months later and he’s like, “Hey, they have this database administrator position. I think you should apply.” And again I’m harking back to my college days so I took two database courses. I hated both of them. It was adjunct faculty, felt very and I was like, “No way. Like, you’re crazy, right?” And he call me back and he’s like, “Hey, we are having a hard time filling the slot like I think you should consider.” I was like, “I don’t even know how to be a DBA. Like, I don’t really know anything about it.” And he’s like, “Well, this is what they pay.”
And I was like, “Oh, interesting.” Again I was at a job right out of college. I graduated in 2002 right in the end of the .com bubble so I felt fortunate actually to have a job at entry level. And so I said, well you know what. It was a significant jump from where I was. And I said, “Ok, I’ll do it.” They had SQL Server in Oracle there so they had an Oracle DBA and I applied and got the job and so basically went to the Oracle DBA and say, “Hey, how do you this?, and he showed me. And then I have to go figure out how to do it in SQL Server. That’s kind of how that started.

Kevin: Interesting, so how about you, Steve?

Steve: Well, just to echo one of the same thing as Carlos said with databases and classes in college. I had a couple of databases classes in college and I hated them. I could not stand database work the way that it was taught in the university at that point in time. But while I was in college I ended up getting a 9-month long internship working at Microsoft and this was in 1990 when Windows 3.0 had just released and just to set the timeframe there. And everyone they get hired was like in computer science and all from the local universities. They were brought in to work in tech support for Windows 3.0 right after it was released. And I learned a lot there but I didn’t want to work in tech support, and I wanted to be a programmer. And so I did everything I could to try and move from that position and I ended up taking on or working with a couple of other people in an internal project to go create some tools that were needed for the tech support team to use there. And lo and behold there was this database thing that Microsoft had just started selling that they suggest that we use and I never heard of it. And I said, “Well, what we need to do to get you speed on this is send you to Microsoft University which was an internal training course they had then. And for a week long class on how to use this thing called transact SQL. So on December 12th of 1990, I received a certificate that said, I’m qualified to use T_SQL.

Kevin: For the record, I do not have that certificate. I got qualified.

Steve: Yes, and so that’s sort of an Easter egg that I put on my blog. My parents found this in their house, this certificate like 20+ years later, and they gave it to me a couple of years ago and I scanned it in and I put a copy of that on my blog as a blog entry from 1990 even though blogs didn’t exist in 1990. Alright, if you check out stevestedman.com, you can scroll back in time and find that there if you’re looking for something that’s maybe a bit funny to look at. But anyway, so that was a 9-month long gig at Microsoft and then I went back to school and I went to do another internship and back to school and on the jobs and all that. And it seem like every job that I ended up at I ended up needing to do something with SQL Server. And then it just sort of evolved into more and more database work and I finally realized I didn’t want to be a programmer; I wanted to do database side of things. I mean, I still do programming but it is all database related programming now, and it just evolved into the DBA role and I had other jobs along the way like I ended up as a CTO at one point and I realized I don’t really like that as much. I want to go back and do more database work. Started all at Microsoft in 1990 and it just kind of evolved from there.

Kevin: Interesting. So sticking with you, Steve, if you could change one thing about SQL
Server what would it be?

Steve: The way that the check_db command works. Meaning, when it runs it goes out and scans your entire database to make sure that there is no integrity issues there, no corruption. And the problem is a lot of people don’t run that because it takes too long to run. And if there was way to say what I want to do is I want to run check_db but run it for an hour and check as much as you can possibly check and then keep track if that and then tomorrow night I’m going to kick it off for another one hour and continue that check process. That would really be a cool change that would probably help a lot with the way that people do their database checks. I know there’s ways to sort of simulate that by saying I’m going to check some of the tables but if you get to the point where you got a database with just one gigantic table, a way to run it for certain amount of time and then pick up later would be pretty awesome.

Kevin: Makes sense. Carlos, if you could change one thing about SQL Server what would it be and why would it be expanding in PolyBase?

Carlos: Yeah, you took the words out of my mouth there, Kevin. Yeah, you know, it’s funny so I was thinking a little bit about this so I went and answer some of this in Episode 0. But we’ve changed the SQL Family questions since then so this is not something that I guess I’ve had to address and of course I think one of the big things we’ve talked about, so SQL Server setup, even some Episode 98, right the first things you change, lots of things in there. As I was thinking about this, Steve and I were talking, so I’m not a user of it yet but I guess it makes me nervous so I guess I’m not sure there is quite a change yet but something that I hope that they do and that is with the introduction of services or languages like R and PolyBase and who knows what’s coming that they give me the administrator. The knuckle dragging Neanderthal that I am who is not a great programmer, you know, trying to not drown in PowerShell. Give me good tools so that I can understand and be able to react to when other people are using those languages in the database. I realized that’s kind of a tall order but help me help other people because I’m a bit nervous about some of that adoption as it increases.

Kevin: Ok, so sticking with you. What is the best piece of career advice that you have received?

Carlos: I’m not sure if it’s the best but the one that often comes back to and that is, “The money will come.” So when I graduated in 2002, that first job I was making roughly 25% lower than I thought I would be making coming out of college. I was a bit frustrated, right? Even when I moved after a couple of years, in fact that job I took as a Database Administrator position, they actually lowered the pay grade because they couldn’t increase my salary by a certain percentage to fill this thing and so. Anyway, I felt like I was, you know, that initial job my wages were lower than I wanted to be and I was expressing some frustration and the comment was, “The money will come.” If you’ll do the best that you can, invest in, kind of harkening back to our. Well, in an episode that hasn’t been released yet, so Episode 104 we’re going to talk with Eugene that idea of going deep. So go deep and get to be good at something. Get to be good at solving a problem becoming that go to person in your organization for certain problems and building trust and then good things will happen. You know, I’m not a millionaire, there is a limit there. However, we were talking about family I have 5 children. My oldest just turned 15 and my youngest is 2 so some of this risk and some of these other things I have to consider them as well. But as you continue to plot along, as you continue to keep your eye on the ball, whatever cliché you want to use there, then good things will happen and I think that has probably been the best piece of career advice there.

Kevin: Got you, so how about you, Steve? What is the best career advice that you have ever received?

Steve: I think the best advice and it kind of comes down to two but the first one is, “There is no such thing as can’t.” When somebody tells you that they can’t do something or that you can’t do something because technically it can’t be done or whatever that’s just an excuse to go and figure out how to do it. Now, maybe there is an exception to that if there is like personnel rules or things like that and
say you can’t do this things, yeah you should follow those. But when it comes to technology when people tell you that something can’t be done, I’ve always looked at it as a challenge to figure out, “Ok, well how can I do that?” The other career advice I think comes from Yoda, from the original, one of the earlier Star Wars movies and it talked about, “There is no try, there’s only do.” I don’t like to try things. I mean, I’ll try a new flavor of ice cream or I’ll try something new on the menu but I like to do things. And to say that you’re going to try something to me often times, like I’ll try and do that for you, I’ll try and get that job done whatever it may be. It’s kind of an excuse to say, “Well, I tried but I can’t do it, so that leads back to the can’t. No such thing as can’t and there is no such thing as try there is only do.

Kevin: No can’t and no try. Alright, so Steve, if you could have one superhero power what would it be and why?

Steve: Oh gosh! I answered this on Episode 12 and I don’t remember what my answer was but I’m going to go with time travel on this one. Because I think if you could go back in time, I don’t think I’d be interested in going forward in time necessarily but if you could back in time, I guess it would have to come forward to get back to where I am. But if you could back in time and learn from mistakes that have been made either by yourself, or from others, or even hundreds of years ago mistakes that have been made just to experience and see what people have done, I think would be an amazing superhero power.

Kevin: Carlos, can you top that?

Carlos: Yeah, top that. Well, I don’t know that I can top it but mind would definitely be different. So in Episode 70, Travis Wright, he kind of brought up this. He said like, “You know, everybody always talks about kind of the supernatural but like some ability that they would possess.” And he said, “Well, I would think the ability to control technology would be very powerful because then you could get it to do all the stuff.” And you wouldn’t have to worry about flaming suits, or hitting somebody when you go back in time. Your matter dimensional is smashing together whatever. And so I think that as I thought about that so that superhero power of being able to, and I always set a camera with a movie. I want to say it’s terminator but it doesn’t seem right. I think they’re actually putting him in the car and get the ATM to spit out. But I feel like there is some movie out there that they walk up and they get the ATM to just start spitting out money. And so something like that although obviously I would do everything ethical, right, nothing immoral like that.

Kevin: Especially on the record.

Carlos: Especially on the record, that’s right. I think that would be my because then also if I could control technology, I don’t know getting some big drone or something like because previously it was flying. I figure that I can get a technology to zoom me around the place pretty quickly.

Kevin: That’s fair, so thank you very much for coming over to the podcast tonight, Steve Stedman and Carlos “skynet” Chacon.

Steve: And thank you for hosting. This has been great.

Carlos: Yes, Kevin, this has been fun. Thanks for having us!

Kevin: Alright, thanks everybody! Take care now. So that was Carlos and Steve today. It was pleasure having them on and hopefully you enjoyed. If you want to see more go to sqldatapartners.com/100 and please follow us on social media, so we’re @sqldatapartners on Twitter, /sqldatapartners on Facebook, also on LinkedIn. Review us on your favorite podcast platform like iTunes or Stitcher and we’ll see you on the SQL trail.

Episode 101: Inspecting a new Database

Listener Cody Ford wrote in and asked if we could share some thoughts on getting familiar with an unfamiliar database.  While we have done episodes in the past on best practices, this episode takes the approach of what we should look for on a server that is new to us–the components we should document and then review for potential updates.

Do you agree with our list?  Let us know on by leaving a comment on the show notes page.

 Episode Quote

“The foremost one there that I usually look at is backups because things happen and there is going to come a time that you need to use your backup”
“We have to make sure that the mail profile is setup that email can flow out of the system.”
“At the end of the day they can do anything they want and that could be good or bad. So we just want to make sure that… they need to get clearance from us.”
“I think as my experience has been the database diagrams are only as helpful as the culture of your environment.”

Listen to Learn

In this episode, we breakdown the sections or components of how we approach a database or instance that is new to us in the following ways:

1. System Availability
2. Admin Setup
3. Security
4. Dependencies
5. Performance Stats

Transcription: Inspecting a new Database

Carlos: Companeros, welcome to the next hundred. This is Episode 101. It’s going to be back with you guys. We’ve got another great episode. Today’s topic comes from Cody Ford.

Steve: Yes, today’s topic is on understanding an unfamiliar database. And his comment was, “How about a podcast on tips and techniques to understand an unfamiliar database.” Such as viewing and understanding dependencies of tables and viewing or creating database diagrams and any other tips to come up to speed on an unfamiliar database besides the knowledge transfer from other employees because you don’t always have that available.

Carlos: Exactly, so that’s going to be our topic today so thanks Cody for that suggestion. I guess one of the things that we will point out now is that, so this is going to be kind of on the investigative side versus the best practices side. So more of I just need to get the information so I need to figure what if anything to change or to be aware of versus here is what I should be changing. Does that make sense?

Steve: Yup.

Carlos: We do have a couple of companero shout outs. First we want to thank Kevin Feasel for all of his help with Episode 100. I know we enjoyed it. I hope you enjoyed it, and so thanks Kevin for taking a little bit of time to chat with us again.

Steve: So we phrase that as “Thanks Kevin for having us on your podcast.”

Carlos: Yes. We want our podcast back.

Steve: Yes, that was a lot of fun.

Carlos: It was a lot of fun so we appreciate that, and of course all the user comments. Those who contributed questions we are appreciative for that. We do have a couple of other shout outs that came to us via LinkedIn.

Steve: Yup, and I think some of these things came from Episode 99 when we started asking people LinkedIn in addition to Twitter. The first one came from Jack Rose and he said, “After hungrily devouring your wonderful SQL Data Partners podcasts for months, Episode 99 finally inspired me to reach out to you as instructed there. Not sure you intended listeners to simply follow you or actually connect personally so I’m trying both. Inspiring work.”

Carlos: Yes, so thanks Jack for listening. We appreciate you’re reaching out. You know, it’s funny the combination and that was purely by accident kind of that combination of both asking for people to reach out on LinkedIn and the Impostor Syndrome. But there was a very high correlation on the number of people mentioned that episode and like, “Well, I’ve thought about reaching out before but now after that episode I’ll do it.”

Steve: Yup, and we have another comment from LinkedIn from Chris Albert.

Carlos: Yes, so he says, “I’ve been listening to the podcast for the last couple of months and love the show. Sad to say I’ve already gone through the whole back catalog of episodes during my commute.” But he’ll keep tuning in, so thanks Chris for tuning in. It is unfortunate that you’ve managed to get through the back catalogs so quickly. Thanks for hanging in there. I know particularly in the beginning they are a little bit rough.

Steve: I know. When I joined the podcast on Episode 50 I had not listened to all of the preview episodes and I went back and listened to all of them as well. And I know it was a little bit sad when I got to the end too. But we’ve got new ones every week so.

Carlos: That’s right we’re going to keep chugging along here and we make it a commitment for another year so we’re glad that you’re sticking around. Ok, so the URL for today’s episode is going to be sqldatapartners.com/newdb.

Steve: Or sqldatapartners.com/101.

Carlos: Ok, so ultimately again the topic for today is becoming familiar with an unfamiliar database. And there’s really tacks or ways to kind of go about this and I know Cody kind of specifically mentioned an individual database. We’re now going to attack this first from an instance level, right, so this is the scenario again where you’re either new to a new job, maybe for whatever reason that database was stood up another department and now all of a sudden you’re asked to take it over, you’re consulting, you know what have you. So instead of focusing just of the database we’re going to take a look first at a couple of things on the instance level and then we’ll circle back to any kind of specific database things.

Steve: Yup, so the first area that we usually take a look at is system availability because
those are the kind of things that if you’re suddenly responsible for this those are the kind of things you could lose your job over.

Carlos: That’s right. Or get calls in the night about, all those things. And you’re like, “Ok well, let me nip stuff in the bud in the beginning.”

Steve: Yup, so the foremost one there that I usually look at is backups because things happen and there is going to come a time that you need to use your backup. And there is a lot of different ways that people attempt to do backups that aren’t always the greatest when you come to restore time.

Carlos: Exactly, and I guess we should say kind of jumping off here as well there is a lot of way to script a lot of these things out and we’re going to be talking about Database Health Monitor and how this can help us a lot with a lot of this as well. But first we’re going to dig in the why around what we are doing here. And so with the backups, some of the things that we want to know, one is are they happening and then where are they going?

Steve: Right. And that where they are going could be a pretty key thing because sometimes you take a look and say, “Backups are running everyday but they are going to the C Drive.” Not always a good thing to do. Other times they are going to a network share that gets purged every night or moved somewhere that as a DBA you don’t have access to where they are getting moved to.

Carlos: A lot of times that location also depends on how long we can keep those databases around so we may have to do cleanup because of a disk size issue when in reality we need to keep those databases around a little bit longer.

Steve: So part of the why behind keeping them around longer is that there is a lot of things that can happen that you may not catch or may not notice for a few days to a week or even a couple of weeks. And if you’ve only got 3-4 days with the backups and you find a problem that you need to pull some data out of a backup from 2 weeks ago but you don’t know about it until it’s already passed that retention window, you could be in a hot water there.

Carlos: Yeah, you would be in trouble. And the last component that we want to talk about there which kind of goes along with our next component, we’ll jump into file recovery options, so what types of backups are we doing here? When we think about file recovery we are thinking about full, or simple; or bulk log, I don’t see a lot of people using that on the long term. That’s kind of a more short term type thing. So full or simple, what am I doing with those backups for the databases based on the file recovery setting.

Steve: Yes, and I think that there, I mean the thing I see is oftentimes if you just install the database with an application or something like that oftentimes you’re not getting it in the right recovery model to able to meet your expectations on the recovery time or recovery point objectives. And I think when we talk about recovery options you can have your opinion on whether simple recovery or full recovery models are better. But it really comes down to what are the recovery time and point objectives that you’re trying to meet there and do those options that it’s currently configured for meet what you’re looking for.

Carlos: Exactly. A lot of times we’ll see databases in full recovery model but then the transaction log only happens once a day for example, the transaction log backup. And so again, this is information collecting. Now, we are going to get this information. We are going to figure out, “Oh ok, well this sounds like an issue.” We will go and have the discussion to go figure out what it needs to be but this is the first area that you want to check.

Steve: Yeah, and I think an interesting story there that I came across like a few years ago on some confusion over the recovery model option was that somebody had a database that was basically working, this was the company that I work several years ago, but it was basically working as a queue. And the data we get thrown into it and then another process would grab the data out of that and move it somewhere else. It was one of those things that it never get backup because it never had any data in it for a long period of time. However, and it was true because data would very rarely reside in that database for longer than 5 minutes.

Carlos: Oh, ok got you.

Steve: And it was SQL Express database and it was set to full recovery model. It had been running for about 4 years without ever having a backup on it and eventually the problem we ran into. You want to guess what the problem was, Carlos?

Carlos: Probably ran out of disk space.

Steve: We ran out of disk space. And this was a database that I never known was in used with this company prior to running out of disk space. But they call me and said, “Well, it’s out of disk space. Why SQL Server is being such a pig?” It’s because it wasn’t configured correctly, so that was one we had to flip over to simple recovery model and then shrink the files down or the log files down. They had plenty of disk space, leave it to simple recovery mode and never had a problem again. That was a scenario where simple recovery model was the right way to do it and it was also an environment where it didn’t really need backups because data was never there longer than 5 minutes.

Carlos: There you go. The other components of system availability are going to be disk space which we kind of just talked about, right? How much do I have left? And then the DBCC CHECKDBs, are they being run on a regular basis. Lots of ways to go about checking that but again that is just information that we want to grab.

Steve: So why is it important to run CHECKDB regularly, Carlos?

Carlos: So we could devote multiple podcasts episodes to this very question, Steve.

Steve: You know, in fact, we have haven’t we?

Carlos: Yeah, we have devoted a couple, that’s right. But ultimately looking for corruption there and I know that I’m just going to leave it as that because if we go further Steve and we’ll take a whole episode just on that one.

Steve: Ok, ok.

Carlos: So the next section then is admin setup. So kind of how are things set up from an administrative type perspective. Some of the things that I’m looking there are: what are the agent jobs, who are the owners and who are the operators of the system? Do we have male setup? Am I going to get notifications for the jobs? Right, so couple of things there. We have to make sure that the male profile is setup that email can flow out of the system. I’m looking at those jobs, what notifications if any do I need on them? Does it matter, right? Again, these are just questions. I’m collecting this information and then I want to be able to go back to the business and say look what needs to get changed here.

Steve: Yup. Another one that I checked around, not just the job owners, but as far as the SQL Server services for SQL Server and SQL Server agent. Who are those running at? That’s one that I’ve seen that sometimes you look and you realize, “Oh, that SQL Server is running as a domain user account of an employee that left 6 months ago.” And recently somebody in the IT Department deleted that domain account and next time you restart SQL Server it may not restart that or it will not restart with that user.

Carlos: Right. You know, it is funny now that I think about that. How many times, how many instances I’ve seen that have actually been the server account is an employee account. For whatever reason, that happens. The other one there in admin setup is server defaults. So again, we are talking about SQL Server settings here, something that you’d see on the advance configuration. Again, what they should be is going to be another question. But I just want to know what has been changed and let me compare to either internal documentation or expectations of a business.

Steve: So that’s where you find out that they’ve turn on auto shrink and auto close and those kinds of features.

Carlos: That’s right, and I guess we should point out that this is a scenario where particularly in the newer versions of SQL Server you can actually set some of that stuff at the database level so there will be a little bit of difference between instance and database. But for the most part I think a lot of those setting are in the older versions of SQL Server at the instance level. But there are things like the shrinking or the closing of connections or whatnot that can still be at the database level.

Steve: Yup. Ok, unto security then. I mean the biggest thing that usually comes up on security is sys admin privileges.

Carlos: And again, so this is the domain or instance perspective, who else has sys admin privileges on there? Because at the end of the day they can do anything they want and that could be good or bad. So we just want to make sure that, again if we’re responsible, if we are the person tied to that server they need to get clearance from us. We need to be ok with that idea that for not pushback on it. I think this is very common particularly when we have third party applications where we’re like, “Oh, just skip sys admin.” Which I know can kind of be a pain sometimes but I think Microsoft, as much gripe or much complaining as we like to do sometimes about some of the security, they have gone to great pains to try to make some of these roles available. I know a lot of times we see for example in monitoring tools. They want DBO, sys admin rights, when view system state, view server state. We’ll get them what they need.

Steve: Right. But I think it’s far easier when asked what permissions are needed for specific application for someone to respond, “Oh, they just need sys admin privileges.”

Carlos: That’s right because they know you won’t go wrong. There won’t be any problems at that point.

Steve: Yup. But it’s not the greatest thing to do. So I like to look that as how any sys admin log-ins do we really have? Is every single user a sys admin? And then the flip side of that is, do we have just one SA log-in and is that the only log-in in the entire database? I know I’ve seen that a few times. We don’t need users. We just use the SA log-in for everyone.

Carlos: Yeah. Again, so those are the things that we’re going to want to document and then potentially defend or say we anyway to make a change here.

Steve: Yup, and I know that’s oftentimes changing the log-in process especially if everyone is sharing an account can be very challenging to do because sometimes it requires code changes, or configuration file changes or things like that, to straighten out eventually but at least getting the understanding of what it is is a good spot to be because then you know who can hurt the server.

Carlos: Right. Well, even then I think let’s just say in that scenario where the application is using the one account. Now, password change might be a little bit cumbersome but not maybe as cumbersome as changing all of the application connection information but at least creating additional accounts for the people that connect using SQL Server Management Studio and things like that. So that at least you can take the password for that application account and put it somewhere where not everybody has it, so little harder to get to, that type of thing.

Steve: Yup. And the place that it’s harder to get to shouldn’t be in the source code that all the developers have access.

Carlos: That’s also true.

Steve: Yeah. I remember going through a DOD audit for security on a company I was working for a couple of years ago. I mean it took months just to straighten out all of the log-in privileges because prior to that happening nobody there really cared about things being secure on that server. It was simply one SA login and that was good enough for the owners.

Carlos: It’s good enough for me, right?

Steve: Yup.

Carlos: Ok, so the next area we want to take a peek at are dependencies. These are objects in the database that the database is going to use or the application is going to use. And again, we just want to know a bit about them, so the first one is user objects in system databases. A lot of times, we as administrators can be the most guilty of this.

Steve: I know I’ve done that. I’ve connected to a database and with the intension of using a specific database but then accidentally created a store procedure in the master database. And one way I help prevent that is don’t use the master database as your default database.

Carlos: If you don’t have another database and on a new server that can be difficult. I’m
even thinking about some of the community scripts, so sp_whoisactive, the blitz scripts. I mean they’re going to default to the master database and so if you don’t have one that might be something to think about as you start investigating or at least being aware of some of those things. Now, again a little bit easier because you know you’re creating them, you can clean them up but if there are other objects in there we want to know why, right? This is all in an effort because of recovery. If that database is dependent on an object in the master database that doesn’t get restored if we have to move it to a new server then all of a sudden I have problems and may not know why.

Steve: Yup. The next thing to look at there oftentimes are triggers. What are the tables that have lots of triggers or complex triggers on them?

Carlos: Yeah, exactly. And again, this is just information so that I know that when I insert a table, or I do x, then something else is going to happen. There’s another component involved there. I know a lot of times I feel like the admin audit. It seems like there is a pretty popular trigger out there so that when administrators do things like create tables, or create databases, or change users, that information gets audited and put into another table. Why if I have instances where all of a sudden that database is not available, the trigger starts failing and then you have issues. So again, you just want to be aware of what triggers are around.

Steve: Right. So it seems like we really have three layers of triggers to think about there. One is just your traditional triggers that are on the tables when things change, when things are inserted, updated or deleted. Then you have the DDL type triggers like you have just mentioned, administrators adding a table or changing a table. But then you also have the log-in triggers that can be setup on a database. That’s one that I came across not too long ago where I’m wondering why are logins are so slow. Well every time somebody is logging in it was inserting a row into a table and that had been running on a database from more than 10 years. Log-in table had never been purged. So it was one of those I asked, do you really care who log-in into the database 5 years ago or 2 years ago? And then we ended up getting rid of the trigger eventually, but for the short term fix was just truncate the table because they didn’t care about it and things were suddenly fast again.

Carlos: You know what, that’s another great example because I feel like most of the time when we get in trouble with triggers it’s because we as administrators are trying to outthink ourselves a little bit there. Then that scenario like, “Oh, let me put a trigger for log-in because for whatever reason that was a requirement. I thought about it. I learned about it and let me implement this because I think it’s going to help me. And then it can come back to bite us because if we forget about it the database changes hands ownership. That’s another thing to take a peek at.

Steve: And some of those aren’t entirely obvious until you run into a problem.

Carlos: Oh, exactly, exactly. Another one, and again so this is I guess more at the database level but going through each of those databases and then looking for disabled indexes. So we talked a little bit about the bulk logs scenario so cases when I have large imports, data warehouses. Sometimes the indexes can be disabled until the import finishes and then they can get re-enabled or rebuilt and the index can be used. Every once in a while however in that process the job fails or for whatever reason the indexes aren’t re-enabled. Those are other things that you’re going to take a peek at because basically we are storing data that SQL Server can’t use anymore and do we want to use it? Do we want to get rid of it? You know, those are the kind of questions we want to start asking with disabled indexes.

Steve: Yup, it’s just overhead that’s not used for anything at that point. And maybe you should be using it or maybe you should be getting rid of it. It really depends.

Carlos: Exactly.

Steve: Yup. So then we also want to take a look at non-trusted foreign keys and constraints. And that can cause some interesting trouble where both around data integrity and around performance.

Carlos: Exactly. We think have that foreign key and we think, “Ok, we are straight.” And
again foreign keys are going to help us. They hurt as a little bit on the insert, it has that lookup, so we get that and that’s probably why they got disabled. Again, lots of bulk inserts and things like that. But then when we are trying to do our queries and particularly the big queries where we are joining a lot of tables even though the foreign key is there SQL Server is going to go about giving you that query differently because the foreign key is no longer trusted. And you’re going to get different results based on that and so again that’s the information we’re we want to know about.

Steve: Yes, and sometimes that different result can be a changed in the query plan that goes from like a full table scan on a table versus not even touching that table based off of those trusted foreign keys.

Carlos: Exactly. And then the last one again we want to be aware of is plan guides. So SQL Server is going to look to those plan guides going to implement them in the query plans. So we just want to be aware is there anything that kind of overriding the compiler and indicating, “No, no, you should do it my way.”

Steve: Yup, but the other thing to consider with plan guides too is if somebody went gang busters and added too many plan guides. There are some issues around plan guides and being able to change stored procedure code for store procedures that have plan guides attached to them or created for them. So if you have plan guides that have been added for instance on a vendor supplied database and you’re doing an update from the vendor of that database you may need to go through and disable your plan guides prior to doing the update and then put them back in after doing an update. So plan guides although they, I mean I kind of think of them as the in case of emergency thing where you only use them sparingly. But you’d better make sure that everyone knows about them because they do have other ramifications that might not be entirely apparent when you use them.

Carlos: And I think this is going to increase because on the newer version of SQL Server with the query store if we’ve gone in, so query the whole premise of this is I’m using a plan that’s less efficient. I want to go back and use this other plan. I’m oversimplifying this, right? But basically we’re putting in, I guess not really a plan guide, but we are putting in some information to say, “Hey, use this instead of what you think is right.” And again those are going to be things that we’re going to want to know about. We may have done them, we may have executed them, or somebody else may have but we’re going to want to be aware that a change was made for whatever reason.

Steve: Yup, and you know it sort of brings me back to one of my about SQL Server, and I probably should have answered this as what would I change in SQL Server if I could, but there are two terms that I really despised. The term plan guides and the other one is the term query hints. They should be called plan commands. It’s not like a guide like, “Oh, we recommend you do this.” It’s a command that says, “You will always do it this way.” And the thing with query hints is they’re not hints like, “Oh, you might want to try this.” They are commands that say you will do this. Anyway sorry, side rant there on nomenclature but these guides are not guides. They are commands that say, “It will happen this way.” So just keep that in mind when you’re looking at plan guides.

Carlos: Yeah, and I think we are going to start seeing more of those in newer versions. And then the last area that we are going to touch on, we can now start taking a peek at some of the performance related things. Now we did mention that non trusted foreign keys can affect performance and whatnot. But here we are actually going to start collecting some of the metrics around performance, and I think ultimately what we are talking about here is establishing baselines.

Steve: Yup, and this can imply instance wide or it can apply to a single database. Either way, depending on what it is you are looking at there. I mean with query stats being able to have a baseline to understand what queries are causing the most wait statistics. What indexes are being used or not being used? What files for that database or instance are being overloaded or have very little I/O on them.
And I think that understanding that can have a huge impact on the way you look at things when you go to troubleshoot that server later.

Carlos: Exactly, and I think with the file stats, I think it is one of those eye opening for me, and let’s say you have an instance and you introduce a new database, right? So obviously the file stats on day one should be zero or near zero, whatever. Well, then you turn that thing on and you want to take a peek at it overtime and you may find out that all of a sudden these thing is going to be, you know, very chatty. It’s going to be a big hog and now you’re going to have store problems. And so again, establishing what those things are. We mention a couple, the queries, the index, and the files stats, wait stats, I guess we should include in there as well as to where I am now and then what I am doing in a week, in a month, in a quarter, you know, that kind of a thing. And of course this is again where the third party tools come into play to capturing that information. For most of this we don’t need to reinvent the wheel. There are things out there already that will do that and Database Health Monitor is one of them.

Carlos: One additional item that I guess we should talk about when it comes to investigating databases and that’s different from the instance level is actually taking a peek at the database diagrams. This could be kind of hit or miss. I think the diagrams are helpful but the tools that we have natively are great at helping us with this.

Steve: Right, and I know that if you use the database, I mean I like to stay away from the database diagrams in SQL Server Management Studio. And I think we talk about this many episodes ago but the reason I don’t like that is that people like to use it, kind of like Visio, to be able to make some changes and diagram things and print it out. But so many times I’ve seen that people don’t realize that when you go into database diagram and start drawing lines that’s actually making changes to the database in adding foreign keys.

Carlos: Right, live editor.

Steve: Yup, live editor and I’ve seen that cause problem so many times. I generally say, stay away from that. What I like to do if I want to look at database diagram is I like to use Visio. And Visio on certain versions, I don’t remember the exact Visio Enterprise or Visio.

Carlos: Yeah, it went through a weird transition where it wasn’t there for a little while. I
guess you could still reverse engineer it but some of the database objects. I think it was 2012. This goes back to our episode on the SQL Server data tools. During that transition this also was also affected by that.

Steve: Right, so with Visio I like to go in and just either on a smaller database take all the tables and import those into Visio so you can see where all the foreign keys are or if it’s larger database just bring in a subset of the tables that might be around specific areas that you’re looking at, bring into Visio. In that way you can visualize it and if you have access to like a large size plotter it’s really kind of handy to be able to print it out and have like a poster size diagram of the database on your wall. And I’m not really big in favor of wasting paper but that’s a really useful way so that when people come to talk to you about a database you can just stand up and go to the diagram on the wall and make sure you’re both discussing the same thing.

Carlos: I love that idea. I think this also harkens back to the idea of is this a third party app or is this something that we are developing in-house? I think a third party app might, there might be still some use for that but definitely the in-house development. I mean there’s, I don’t know how many conversations I’ve settled just because we pop it up in the database diagram and say, “Ok, this is what I’m talking about. Is that what you’re talking about?” “Oh no, I’m talking about this thing.” “Ok, well, there you go.” Now, I have never had access to the plotter. We always had to go like Kinko’s or whatever and they’re never quite big enough or we are piecing them together, all these legal paper and then we have to tape it together. So I’ve migrated beyond that. I think as my experience has been the database diagrams are only as helpful as the culture of your environment. Meaning, that if you’re willing to use them and look to them and adopt them as like a change management process. They become much more helpful. If it’s something that you don’t care about and you’re not like you’re making changes to the database without a diagram first being updated then it becomes less helpful, right? Almost like source control type thing, type idea. So because of that I’ve kind of moved on even passed Visio and I like the Embarcadero tool. It’s this expensive. It’s like erwin, so erwin was kind of the cream of the crop. Embarcadero kind of came in underneath but lots of the same functionalities. I think one of the things that you suffer from like in Visio is particularly once you get beyond like 10 tables. It becomes very hard to see that in one document and so that ability to kind of section them off into different areas or group them just becomes very very helpful. But again still, it is helpful I think, and the biggest ways in one, data types, relationships, and then understanding where things go, so again that idea of diagramming. This is my table, this is an object. What’s going to go in this object? “Oh, ok you’re describing something that doesn’t quite match that description. Maybe it should go over in this different table or we need to think about that differently.

Steve: Yup, oh yeah, I can think of an interesting story where I was working with a client a few years ago where we are working on some reporting queries and trying to figure out how to use some tuning or how to get the data that they needed in there. And so I went in and I thought, “Well ok, I don’t understand this database. It’s a third party vendor that provided it.” And the first thing I did is I opened it up and start looking at foreign keys and I realized that there was not a single foreign key on over 200+ tables in the entire database.

Carlos: And you’re like, “Ok, yes!”

Steve: So it’s like, ok well the only thing we have at that point is to try and guess what is intended to be treated as a foreign key is just how things are being used in queries. And I found that there are a lot of places that people were joining on values that shouldn’t have been joined on and wondering why they didn’t work. And if there just been foreign keys in the database it would have made a lot more sense to someone trying to get the understanding on what you can join on.

Carlos: Yeah, I know exactly. Ok, so Cody hopefully that’s helpful. So I guess to kind of recap a little bit there if we go back and put in some of those groupings, so first is system availability, then we have admin setup, we take a look at security,
dependencies and then performance. I guess we should add the sixth category in there and that is database diagram type of information. We’ve shared a couple of different stories there about how to do that. But again lot of just depend on your culture and whatnot.

Steve: Yeah, I want to say thanks Cody for the great question that gave us a good topic for this episode. And Carlos, I don’t think we have a next hundred episode entirely booked out yet. Do we?

Carlos: Not yet. We are shy by just, I don’t know, 99 or so.

Steve: Yes, the reason I bring that up is if anybody has any questions, or ideas, or topics that you would like to have us cover in the podcast, let us know, just like Cody did here. I will be happy to consider it for the podcast episodes.

Carlos: That’s right. I guess so speaking of the future, Tracy Boggiano who is going to be speaking at the Companero Conference. She’ll be on next week. And then we have Eugene Meininger–I’m pretty sure I’m saying that last name right. I’ve told myself I would say it right the next time I said it and actually I did that. But he’s going to be on the program as well.

Steve: Speaking of the conference.

Carlos: Yeah, we just have over a hundred days to go and so we’re looking forward to it. You can still register at companeroconference.com. August 4th and 5th we’ll be down in Norfolk and we hope to see you there.

Steve: Yup, I’m certainly looking forward to it.

Carlos: Yes, so again if you want to check out the show notes in today’s episode that will be at sqldatapartners.com/newdb.
Steve: Or sqldatapartners.com/101.

Carlos: And of course we really enjoy you reaching out to us on social media. You can do that on Twitter, or at LinkedIn. I’m @carloslchacon.

Steve: I’m on LinkedIn @stevestedman, and on Twitter @sqlemt. We’ll see you on the SQL trail.

Episode 99: Imposter Syndrome

Impostor syndrome (also known as impostor phenomenon or fraud syndrome) is a term coined in 1978 by clinical psychologists Dr. Pauline R. Clance and Suzanne A. Imes referring to high-achieving individuals marked by an inability to internalize their accomplishments and a persistent fear of being exposed as a “fraud”.  In this episode of the SQL Data Partners podcast, we take on a topic that is not technical; however, might play a very important role in some of the opportunities we take–or miss out on.  Today’s guest is Mindy Curnutt, a 3x MVP and a real chance taker as she recently decided to become independently employed.

In this episode we talk through the idea of imposter syndrome and give some examples of how it affects us, but we also try and tackle ways we can identify it and steps to help us overcome.  I think you are going to find this episode very interesting and we hope you enjoy it.


 Episode Quote

“I mean SQL Server, I’m so passionate about it that it doesn’t feel like work to me.”

“Coding involves regular failure.”

“It’s ok to be wrong. You’re not expected to be perfect.” 

“Don’t let that impostor syndrome stop you from taking advantage of opportunities.”

Listen to Learn

– Description of impostor syndrome
– Effects of impostor syndrome in one’s career
– Symptoms of impostor syndrome
– Impostor syndrome in IT professionals
– Tips to overcome impostor syndrome

Mindy on Twitter
Mindy on LinkedIn

About Mindy Curnutt
Mindy Curnutt

Mindy Curnutt is a 3-time Microsoft MVP holder (SQL Server 2014 & 2015 and Data Platform 2016) and has worked with relational databases since 1995 and SQL Server since version 6.5. She has been involved in the development of the following systems: transportation management & maintenance (TMS), JIT manufacturing (MRP), sales/accounting (ERP), customer relations management (CRM), medical billing & audit, and US Govt / IRS Taxation  (forgive me). Mindy is the Lead Partner at Mindy Curnutt & Associates Consulting, which specializes in Microsoft Data Platform & SQL Server architectural guidance, performance tuning, training and Remote DBA Services.

Transcription: Imposter Syndrome

Carlos: Mindy, welcome to the program!

Mindy: Thank you. Thank you so much. It’s my pleasure.

Carlos: Yes. It’s good to have you and being from a nice state like Texas, you’re right in the center, so Steve and I have a little coast rivalry here and so you here we could play nicely.

Steve: Yeah. We can’t claim that Mindy is an East Coaster or a West Coaster on this one.

Carlos: Yeah, that’s right. So thanks for coming on. It’s great to have you and ultimately today we’re going to be talking about impostor syndrome, what it is and how this might affect us in terms of what we’re able to do in our careers, what we’re kind of willing to reach out and kind of take risks and then even get into how widespread this is or how many people this can affect. So I guess let’s go ahead just to get the conversation going, why don’t you give us a definition or some insight into what impostor syndrome is.

Mindy: Well, impost syndrome, and I’ve just learned about this only three years ago. Although it was actually coined, the term was coined way back the late 70s, so it’s been around for quite some time. I learned about at CodeMash outside of Cleveland. It was about three years ago there was a woman who did a presentation on impostor syndrome and it just hit me right between the eyes. It’s one of those things that’s so obvious when somebody explains it to you but you really never gave it any thought and then once you realized that it’s actually a thing, for me at least personally, it was a game changer as far as my confidence level because it undermines your confidence. And it’s your own thoughts that are doing it which is what’s so bizarre. So impostor syndrome is it’s actually only seen in high achieving individuals which is very interesting. And it’s basically the inability to internalize or recognize your own accomplishments as the accomplishments that they truly are, and having this persistent fear that you’re going to be exposed as a fraud or that you don’t really deserve the position or the respect that people are giving you. You know, that you’re being asked to be a speaker, you’re being asked to write a book, you’re being asked to participate as a manager in some process at work and you feel like, “Wow, this is great. I’m glad that they all think that I can do this. If they’ve really knew me they’d know that I’m not really capable of all these and I’m really not that special.” And to find out how many other people feel that way which in the tech field is the majority of the people feel that way secretly. It’s very interesting.

Carlos: Sure. In fact, you actually have a little bit of survey data around this.

Mindy: Yes, I did. Interestingly enough after, when I decided I was going to do this presentation I went out and did a survey. I sent a definition of impostor syndrome out to the MVP list, the Data Platform MVPs, and I said here is a definition of impostor syndrome. I have a survey, completely anonymous and would all of you please if you have time go to the survey and it is simply a YES or NO, have you felt this way anytime in the last 5 years. And what was it, 86%. I had 42 Data Platform MVPs respond and 86% of them admitted that they had felt that way in the last 5 years, so that’s recently. That’s not just sort of in the beginning of their career or whatever. And then when I went actually internally at my own company and asked 11 IT professionals that were managerial positions or higher, if they had felt the same way within the last five years. I got an 81% response. And the stuff that I’ve read on the Internet is that that’s actually that those are pretty real numbers that it’s very very common. And people just either they don’t recognize that everyone else feels that way. They didn’t know there was a word for it or like myself I just thought it was me.

Steve: You know that’s really interesting because I know the whole impostor syndrome is something that, I mean honestly, I’d never heard of it prior to Carlos scheduling this presentation or this podcast recording. And I went and did a little bit of research on it and look at your slides from SQL Saturday and all that. I thought, wow. I can’t believe I have never heard of this and it’s something that I know I’ve seen and probably experienced at some level here and there.

Carlos: Sure, and I think in some forms it’s easier to feel maybe then than in others, right? And that’s not say that it couldn’t affect a single individual in all different kinds of places. I think obviously the more you feel comfortable in a situation and things like that. It might not be as bad but I think anytime you get into the place where you start comparing yourself to others and what you’ve don’t to the outcome of others. I think it’s very easy to kind of fall into this trap. Or telling yourself that, “Hey, you know, it’s not worth it. Just throw it in the can now.” You know, they are never going to take you seriously.

Mindy: Yeah, well and it’s interesting so statistically the most commonly found in technical fields so not only would it be in IT but it’s very common in the league of profession. It’s very common for physicians and especially specialists to have this, “I don’t really deserve to be a brain surgeon type of thing.” You know, “I’m not really, I know all these people think I can do all these great stuff.” But I think that it’s really exacerbated in IT because of the pace. You don’t just learn something. You have to keep just, it’s a constant constant swim against the riptide to try to keep current and it seems like with. I don’t know if it’s just me getting older or if the pace is just going faster and faster and faster which is I’d maybe it’s both.

Carlos: Yeah, obviously, we’re seeing SQL Server release cycles now, one year, right?

Mindy: Right. Well, and now I’ve spent 20 years becoming just a super super deep and narrow with my specialty being SQL Server scalability and performance. And being able to get to the root cause of basically slowness and whether it is hardware related, maintenance related, architectural design of the database related, if it is the application and how the application is approaching it and that took years to develop. And now all of a sudden I got all these people popping up around me going, “Hey, Document DB is now Cosmos DB.” And I’m like, “What!” While I still know my stuff it makes me, you know, I feel shaky. I think that’s a very unique field to be in. They don’t think that that’s happening in the legal area. Is it, right? I mean, maybe with all the laws changing recently but.

Steve: But what’s interesting with that is, I mean, you talked about where you’re at and what you know how to do there. I mean that’s basically a 22-year education since you first started using SQL Server. And it’s the continued education and that’s more education perhaps than someone who has a law degree or someone who is a doctor may have had along the way. And to get there, I mean a lot of people don’t look at it that way but every year you are out there doing new stuff it’s more training, it’s more education. You’re always learning more.

Carlos: So let’s kind of bend this towards, most of our listeners, the companeros out there, they are working, they are in the workplace, how is this most likely affecting them?

Mindy: Well, one of the things that can be bad about it is that it basically prevents you from getting ahead in your career. You’re less inclined to basically apply for those advanced positions because you’re listening to these voices that say, “You’re not ready”, or “Maybe just a couple more years, or you shouldn’t raise your hand for that, you’re not as good as they think you are”, “I know they are suggesting you should apply for that Director role but really come on you’re not ready for it. You need more experience still because you could fall down and it could be a huge failure and it would be totally embarrassing.” These voices that don’t even, it’s like you want to turn around and like look inside your brain and go, “Shut up!” Right? You could be underpaid. You’re not going in asking for as high of a salary and then you find out that other people have gotten the salary you didn’t come in as aggressive enough because you didn’t feel like maybe you were worth that, right? And there is also the community suffers. You’re not the only one suffering but the community suffers because people don’t raise their hands. You know, right now the PASS Summit, today is I think the last day for the call for speakers. How many people didn’t even submit to the call for speakers because they didn’t just feel they were quite ready for it?

Carlos: Me, I’m raising my hand.

Mindy: It’s like how many SQL Saturdays does it take, right? How many times do you have to actually go out and when will you be ready like it’s next year is going to be the year? And realistically if you ask other people if they think you’re ready they are all going to probably raise their hands and go, “Yeah, you’re ready.” And you are the one who is like, “Yeah, maybe not.” Maybe you’re not blogging, you know, putting stuff down in writing. That’s frightening because it’s in writing for everybody to see and even if you go back and fix it later the way back machine is going to have it out there forever and ever that you said that. You might not be participating on forums because again, I mean, sometimes people can be kind of snarky on those.

Carlos: Yeah, that’s rough.

Mindy: There’s people that are asking questions on Twitter for Twitter help and you might not want to volunteer an answer because you don’t feel like you’re 100% sure and you don’t want somebody to point out that maybe you weren’t exactly right. But you still think that you could help the person but you don’t speak up, so then these people aren’t getting help. So it just doesn’t hurt you, it hurts the community as a whole. It hurts other people that you could be mentoring because we have good things to share and I think people would benefit. Nobody is perfect right? I mean coding involves constant failure. Expecting that you’re going to be perfect is unrealistic.

Steve: Ok, so now you have mentioned that there was some story about the Air Force Academy. Could you share that with us?

Mindy: Yeah, so this is early on. And this is my example of how this looking back with
hindsight. How this really impacted my life and impostor syndrome. When I was 18, so all through high school, junior high and high school, if you asked me, as a child, you know, you asked kids, “What do you want to be when you grow up?” And I wanted to be an astronaut. That was like the whole, the Space Shuttle was big back then and we had Sally Ride. You know, I remember the Space Shuttle, I remember I was in high school when it blew up, right? And that was a big deal. We all watched it in school. But I wanted to be an astronaut. And on the way to being an astronaut I want to fly jets and the only branch of the military in 1987 that let women fly jets was the Air Force. All the other branches you got to fly the transport planes. And I went out and I actually had my solo pilot license that I got through my boss that I worked for. He was very supportive and he had a plane, and I would go wax it all the time, wash it and wax it and get all the oil off the back of it and like buff it all out so we could take it up. And so I actually ended up getting a nomination to the Air Force Academy, and part of getting into the Air Force Academy once you get the nomination is you have to passed a health test and a physical fitness test, and a whole bunch, you know, they dilate your eyes, and they listen to your knees, and they do all these stuffs. And so I knew I had to pass this physical fitness test and there were four parts to it and one of the parts was pull ups which is different than chin ups. Chin ups is where your hands are facing you and that’s actually they’re easier. A pull up is where your hands are facing away from you and they would take a broom handle and put it 6 inches in back of your calf and another broom handle 6 inches in front of your calf. And when you were doing the pull up if you hit either one of those broom handles that pull up didn’t count, and I have to do 5 of them. And I knew this was going to be a problem. My math teacher was letting me out of school, out of class, like half of the class all the time and I would go with this boy and that we were working together in a gym and using that machinery pull the bar down. I mean I got closer but I could never pull my full body weight with that machine. I mean we work on it for 6 months all the time. And it is not like I was heavy or anything. It is just I would get to where my arms were hanging, I was hanging and my elbows were actually perpendicular to my forearm in like and L and I would just shake and I could not do that last little “uhh” to get my chin above the bar. And I would just shake and shake and shake and shake and shake for like 40 seconds, right? Uhhh!

Carlos: I’m surprised you lasted that long. I think at 3 seconds it would be like, I’m done.

Mindy: Oh man! Well, that last part is there is like some muscle across your right where your collar bone is and that area that is what does that last piece and I just don’t have probably any muscle there. It’s just nothing, it is just bone. So I failed the test and they were like, “Yeah, you know, I know you have the nomination from the senator and that they are really hard to get. They only have five open spots but you didn’t pass it so too bad for you.” And so I went away from that and I didn’t go into it with impostor syndrome but as I walked away from that being rejected I started developing this huge impostor syndrome about, “You know, I was the only girl. I was the only girl there and, you know, it would have been bad if I have been accepted because I would have felt so out of place and I probably wouldn’t have made it anyway. I think they might just have picked me because I was a girl and maybe…” I just psyched myself into this, you know, you would have had impostor syndrome. You would have felt so bad there. They would have, not that I would have had impostor syndrome but that I would have been exposed as a fraud if I had gone there, and it would have been a horrible experience and I had been saved from a terrible mistake. And thank goodness that they rejected me because that’s the best thing that could have happened because boy that could have been a mistake. And then so about two weeks later senator calls up and says, “I would like to offer you a nomination to Annapolis. And I had so freaked myself out with impostor syndrome that I was like, “Are you kidding me? I’m so glad I didn’t get to go the Air Force Academy because it would have been such a mistake. I would have been humiliated. I don’t want to go to Annapolis.”, and I turned it down.

Steve: Wow!

Mindy: Oh no, right?

Carlos: And he’s the one calling you. It wasn’t like, you know, he’s reaching out saying, “Hey, I got this. Do you want it?”

Mindy: Yeah.

Steve: Now, you mentioned that it’s only a few years ago that you’ve heard about impostor syndrome but it’s been, I mean there were many years in between from the Air Force Academy opportunity to learning about impostor syndrome. When you learned about impostor syndrome was it just something that hit you at that point, “Wow, that’s what it happened.” Or did it take a while to sort of come around and realized that.

Mindy: So the Air Force Academy experience and the Annapolis I didn’t like have some epiphany like immediately that that’s what had happened there in that particular situation but as the woman was doing the presentation of impostor syndrome. I mean, I couldn’t think of exact times in what meeting I was in with whoever it was with but I definitely thought, “Wow, I have felt that.” So many times in meetings where I really wanted to contribute something and I didn’t interject because I didn’t want to sound dumb in case I wasn’t absolutely 100% right. And maybe the people in the meeting were have used a couple of acronyms that I wasn’t familiar with so I started feeling like, “Ok this is. I’m glad they all wanted to include me in this meeting. I’m not really sure why I’m here because I don’t have much to contribute so I’m just going to sit here and be quiet.” Oh my god! And I had felt like that. I know I felt like that before.

Steve: That’s always interesting when you talk about acronyms and like sort of feeling left out because, I mean, a lot of people will have like their own companies sort of acronyms that maybe different from what people elsewhere use. And it’s almost like being in their secret circle to be able to understand what they are talking about. And I think that it can be very challenging and very difficult to feel part of the group when you don’t always understand what their acronyms are at first.

Mindy: I could totally relate to that. I mean I work in trucking and transportation software. It took a few years before I actually knew what they were talking about when they started talking about “backhaul” or a “dead head” or a “cross dock” where I was like, “What!” I have no idea what they were talking about.

Carlos: Or “smokey”.

Mindy: There was a funny there was a guy that’s like, you know, I was at this seminar once and I was trying to make small talk with another customer at the table and I said, “So what do you guys haul?” And he looked at me and he said, “Reefer”. I almost spit my food on the table I’m like, “Pweaa! You, haul what?” And he’s like, “Reefer.” And then somebody looked at me. My eyes were just big as saucers and some of them they started laughing and they’re like, “Refrigerated stuff.” I’m like, “Oh my god!”

Steve: Wow!

Mindy: Yeah. I guess that’s the term.

Carlos: You know, I have experienced a little bit of impostor syndrome particularly, so I bring up this podcast a lot and my experiences with it and I remember. It has happened a couple of times but one that was poignant kind of stuck out to me is when I interviewed Brent Ozar. You know, from a name perspective, you think direct at the top at that point. I had reached out to him. Go through my standard process to reach out to people. It’s not quite like it is today but back in the day I have this very simple process. He agreed, we started talking anyway the episode went fine. I thought it was fine but I was asking for some feedback and he said, “You know, I would have liked to understood, the agenda basically wasn’t well set and I didn’t like where we kind of went with some of these things. It wasn’t what I was expecting. I wish you would have asked me like we could have fortified that agenda a little bit better.” And I was like, “Oh my gosh”, right?

Mindy: Yeah, you’re like, “Pewww”.

Carlos: And the reason I didn’t do that is because I was like, “It’s Brent.” Like who am I to tell him that this is what the agenda needs to be, right? So I kind of left it soft and mushy a little and he would have benefited from a little bit more structure or me going through a process and kind of giving some feedback as to and so what we would have talked about. And so I think kind of going to that point of the community if you will at large or the people that you’re involved with. It’s not just you. Other that you’re working with can also be affected by you not wanting to participate or not thinking at your place to participate.

Mindy: Yes. Yeah.

Carlos: And I guess we’ve talked about the survey a little bit why it is destructive, so what can we do to help ourselves? If we find ourselves coming that funk what’s the remedy?

Mindy: Well, simply knowing that it’s a thing, for me was 80% of the fix. For some reason it was almost as if someone have snipped these strings that have been holding my wings back. I don’t know how else to explain it. I was actually able to recognize when it was happening and go, “Ok, I know that is. I know I’m not the only one who feels that way so stop it.” Now, it doesn’t completely, you know, made it where the thing doesn’t try to rear its head up but it’s knowing that it’s actually a voice that is shouldn’t be speaking to me and I can ask it to be silent instead of just listening to something that you’re not realizing is destructive. I don’t know why just to knowing that it was something that others and seeing how many other people felt that way. It was hugely freeing, so that’s the biggest thing. The other suggestions are just forgive yourself for any negative stuff and that’s really hard to do but like I said, “Coding involves regular failure.” Being occasionally wrong and like last night I twitted something that was wrong.

Carlos: Oh boy!

Mindy: Yeah, and regarding the PASS Summit, and regarding what the definition of an abstract versus the description is then it caused a couple of people to get confused and then I got an email from PASS HQ and they said, “Actually, that’s not correct.” And I went right back on Twitter and I said, “Opp, I was wrong. This is actually how it really works.” And I think before I knew that impostor syndrome was a thing I would have just stick my head in the sand, “Look at the people who just saw me say something that was wrong”, right? It’s ok to be wrong. You’re not expected to be perfect, and other people don’t expect you to be perfect. The other things there are some suggestions. Maybe print up your resume if you have a copy of it. Take your name off of it. Put somebody else’s name on there. Maybe mail it to yourself or something. Look at your actual, what you’ve done, what your qualifications are, where you’ve worked, what you’ve accomplished, where you’ve spoken, what you’ve blogged about, what test you’ve passed. If you saw all of that and it was somebody else, how would you feel about that person? And is it different than how you are judging yourself?

Steve: That’s interesting.

Mindy: Yeah.

Steve: I have to go back and look at my resume now and see how it looks or how I look at myself.

Mindy: One person had said that she keeps a diary. Well, it wasn’t really a diary. It was like when somebody tells something nice about her or she gets a compliment she would come home that end of that day if she remembered to do it or she might write it down in a piece of paper and tear it up and shove it in her purse. And then when she came home at night, she found those papers in her purse and she like put them in a little money box that she had in her cabinet. And anytime that she started to feeling down about herself or feeling this impostor syndrome type of feeling about, “I’m not worthy, whatever”, she goes and opens up, she calls it her Happy Box. Opens up the box and goes through all these things, all these times that people and the date on their and who said it that they said, “Wow, you just saved me”, whatever amount of time or “You just did this good thing” or “Wow! I’m so glad we called you” or “Oh my gosh that query is so much faster”, or whatever. Because every day goes by, boom boom boom, they become a blur. You can’t remember all the details of all the things that happened to you start to turn into a smear.

Carlos: That’s an interesting concept, right, so taking that because I think where it’s easier to hang on to the negative comments, a little harder to hold on to the positive ones sometimes so that idea of kind of keeping track of that, noting it, measuring it, right? You bring a catalog of it so that you can refer to it when you need to, that’s an interesting thought.

Mindy: Yes.

Steve: So one of the things I noticed in your presentation was a slide when you talked about killing your heroes. What do you mean by that?

Mindy: There is where I can talk about Jimmy. Well, I didn’t kill Jimmy.

Carlos: Thank goodness.

Mindy: Yeah, so killing your heroes. What I mean by that is don’t put people on a pedestal. They are not putting themselves on the pedestal. Brent didn’t put himself on the pedestal. Jimmy didn’t put himself on a pedestal. I did that. And I didn’t really, you know, doesn’t put herself on a pedestal. I know her now. I put her on a pedestal many years ago. I did that, she didn’t do that. So when I say kill you heroes, really what I mean is, don’t be making people into something that’s bigger than life because they are just people. Most of them are really nice people and they would be just like, their mouth would fall open if they knew what kind of pedestal that people put them on. So my example is in 2010, well before 2010, because my focus has always been performance. I was doing a session back at that time. This was back when we had spinning disk and separating the log file from the data files was still critical, right? So I was doing a presentation called “SQL Server I/O Uh-oh” and it was all about how to really maximize your and try to stay away from the pitfalls of the I/O being so slow when everything else had move ahead so much quicker, which for a long time that was huge problem. Jimmy May had written this whitepaper when he was at Microsoft that talked about the stripe to use in fixing the misalignment in the partition from Windows 2003 and how substantial that is, and using the correct allocation unit or block size and setting all that up. And if you do the trifecta of these three things, how you can get 30% or better performance off of your disk and just set up SQL the way that SQL works. And I went over and over that whitepaper when I was building out my presentation, and I was trying to make my presentation fun and so I summed my presentation with a song. I basically took Patsy Cline’s I Fall to Pieces, and I changed the words up so I brought my guitar to the PASS Summit and I play it like, “Don’t do your I/O in pieces.” I basically made this whole parody about doing your I/O in pieces and it was recorded and I thought it was really fun. And I wanted to tell Jimmy that I had written this song, it was based on his whitepaper but I was terrified of him. I was so intimidated by him because he is Jimmy May, and I have put him up in this pedestal. You know, I was just like, if I was in the same room I was like I just didn’t want to talk to him. It was just like, “Oh my god, I’m not worthy.” I actually twitted to him, and I have this in my revised presentation now. I send him a private message on Twitter and I said, “Hey, I did this SQL I/O presentation and I summarized your whitepaper in a song, it was recorded and here is a link to the YouTube video. I would love your thoughts.” And he responded with, “Unfortunately, I’m buried. I’m in Vancouver over the weekend. No internet.” And I was like, “Oh, I feel. I shouldn’t have even have asked him.” And now Jimmy and I are really good friends, you know. I mean, we are, now we’re where he’s like just post something on my Facebook page about something last night. I mean, we talk all the time. So I did that, he did not do that. I did that. Like if I had some of my other friends that said that I wouldn’t have thought anything of it, right? But because I had this weird impostor syndrome like, “I’m the fraud and he is the master and I don’t… Oh my gosh.”

Carlos: So I have to ask, does that YouTube video still exist?

Mindy: Yes, it’s out there. I think there is one out there now of me doing. Yes I think it is. It may not be the PASS Summit. I think it’s me speaking at TransForum, TMW’s TransForum event but it is out there. Yes me singing that song.

Steve: So perhaps we should wrap up the end of the episode with that song.

Carlos: It sounds like a good idea. Oh, let’s see here we go. I’m looking; I can see you holding a guitar. We’ll find it and we’ll put it up in the …

Mindy: I think my name is spelled with an “e”, whoever posted it they put Mendy Curnutt.

Mindy: Well, basically the core of it is impostor syndrome is very very common in tech. 80% something of the Data Platform MVPs admitted that they had it recently. It can prevent you from getting to where you want to go with your career and just recognizing that it’s a thing is a huge huge silencer or like an enabler of getting around it. Man, don’t let that impostor syndrome stop you from taking advantage of opportunities.

Steve: Ok, great advice. Alright, shall we move on to the SQL Family then, Carlos?

Carlos: Let’s do it.

Steve: So Mindy, how did you first get started with SQL Server?

Mindy: Wait, it’s a long time ago, so I moved to Seattle after college, a few years after college. I was playing music as a hobby. I play a guitar, obviously, you saw on that video that I talked about. I play the guitar and sing and then in my 20’s I was always out singing at open mic and stuff. I moved to Seattle because that was during the Grunge period and I just want to play music all the time. So when I moved to Seattle I had to get a job. I ended up getting a job at this water jet cutting company as a project manager. My degree was in Economics. I don’t know where else I’m going to get a job in. And I was trying to do my job and it was very very difficult because they didn’t have, they had computers but they had just like Word Perfect and Lotus 1, 2, 3. There was no actual just in time manufacturing application. Everything was on paper and it was trying to get something through the shop and figuring out what we had in inventory and how long will the vendor times, and what bids have they’ve given me. It was a nightmare so they had just bought Microsoft Office. They had Access 2.0 and I went to some college classes at night at the learned VBA, and I ended up writing myself a program to manage my job which ended up turning into just in time manufacturing application. And it got ported to SQL Server and so it was just out of necessity. I mean it has a logical, my mind wants to make processes and things and relate things to things. I wish I would have known that I had such an affinity for that but I didn’t have really any computers when I was a kid. We didn’t have them and then college you had to like reserve time. Nobody had a computer in my dorm so I had to actually get into an environment where there are computers present for me to realize that that was something that I was really sort of naturally drawn to and good at.

Steve: Wow, very interesting.

Carlos: If you could change one thing about SQL Server what would it be?

Mindy: If I could change one thing about SQL Server. I should be looking at these questions before you ask me them. Where did that thing go? If I could change one thing about SQL Server what would it be? Man, that’s a good question. Can we come back to that? Let me think about that a little bit.

Carlos: Sure.

Steve: Sure, we can do that. So what’s the best piece of career advice that you have received?

Mindy: Oh, that’s an easy one. Yey! Ok, the best piece of career advice I ever received was “What’s the worst that can happen?” Yeah, so I worked for a guy. Right when I got out of college I worked for a guy in a wine industry. Actually I wasn’t out of college. I had to take a break during the middle of college because we’re having some financial troubles paying for college, so I stepped back during my sophomore year and I moved home and I was working full time and then I was going to school at night at a lower, it’s Sonoma State University instead of UC Sta. Barbara. And one day the guy that I was working for, he asked me, “Why wasn’t I going to University of California anymore?” And I said, “Well, it’s financially.” It’s too much money basically and my parents don’t want to get another student loan. And I can’t get any grant because they had too much land and we’re just in a pickle. He helped me basically. He helped me figure out how to get my parents to re-file their income tax for the last two years and get me off their income tax, and then not to take “NO” for an answer. He’s like, “Ok, that’s a problem. How do we get around the problem”, right? “How do we get around it?” So there is why you’re being stopped and how do we backed up and reverse and what other approach can we take, so that was huge. And then I was able to actually get the student loans and the grants and things like that because I was personally dirt poor. But then when I went to go back to the University they said, “No, sorry. You’ve been gone for a year so you’re going to need to re-apply.” And I was just devastated and when I came back he said, “Well, what’s the problem?” And I said, “Well, I have to re-apply and then I’m not going to be able to get in until next year.” He said, “Well, who makes those rules up?” And I was like, “What do you mean?” And he said, “Do you think the Dean? Who can break the rules? You think the Dean can get around the rules?” And he goes, “Why don’t you go down there and talk to the Dean and see if the Dean will let you back in. What’s the worst that could happen?” Well, right exactly where you are now, right?

Steve: Yeah. I guess you could have just say NO, right?

Mindy: Right, so there has been a lot of that. I took that advice throughout a lot of my career of. Don’t take NO for an answer if the NO is just because of the rules are kind of dumb. Like how can you get around it but still not breaking the law or anything? But is there a way to get around something if you don’t think it makes a lot of sense. Is there a way to rightfully get around it? And don’t be afraid to ask and what’s the worst that could happen. You’re exactly where you are right now.

Steve: So did it worked out when you talk to the Dean?

Mindy: Yeah, she let me right back in.

Carlos: There you go.

Steve: Awesome.

Mindy: Yeah, huge lesson.

Carlos: Yeah, yeah. Do you want to circle back to the SQL Server question or should we continue on.

Mindy: That one thing? I think we can continue on. Yeah, because I’m not sure what the answer about that, maybe, I’ll email you afterwards with my answer.

Carlos: That’s fine. Out last question for you today, Mindy, if you could have one superhero power what would it be and why do you want it?

Mindy: If I could have one superhero power I would want to be able to. Well, the first thing I wanted to say was because I had a friend that passed away from ALS and I
would want to be able to put my hands on somebody with ALS and just cure them. But then I thought, wow, then I would be flying all over the world constantly and I’d never get to go to sleep because everybody would want me to cure them.

Carlos: Ok, so the analytical brain starting to pump down there.

Mindy: There you go. I wish I could like wave a wand and cure everyone who had ALS. That would be, right, just to make that go away. It was really terrible.

Carlos: Well, Mindy, thanks so much for being with us today.

Mindy: Thank you! Thank you so much.

Steve: This has been great.

Carlos: Yeah, good information and we do appreciate you taking some time to chat with us.

Mindy: Thanks!

Steve: And I look forward to seeing you when we meet up at the conference, The Companero Conference.

Carlos: That’s right!

Mindy: Great, I’m looking forward to that a lot.

Episode 98: The First Change

In a recent SQLSaturday conference, I walked into the speakers room and asked the question–What is the first thing you change after you install SQL Server?  It was interesting to get their take on the server setup and I think you will enjoy the conversation.  There are various answers on this one but some of the speakers have mentioned stuffs like set auto grow files, SQL Prompt, SQL parameter class and max memory among others.  I would like to thank Kevin Feasel, Jonathan Stewart, Eugene Meidinger, Raymond Kim, Tracy Boggiano, Mindy Curnutt, Thomas Grohser, and Vladimir Oselsky for their suggestions.

 Episode Quote

“I would say that now I’m basically a broken person without SQL Prompt.”

“One of the things that I recommend all of our customers… is an administrative failed logging attempt alert system.”

Listen to Learn

What people say is the first thing to change about a SQL Server installation.

It should be noted that the suggestion on the auto boost was said in jest.  🙂

Transcript: The First Change

Brian Carrig: 226 which is the first thing I would change from the default. Second thing I would change from the default is the set auto-grow all files for user databases. And SQL 2016, the default behavior is that single file growth rather than auto-grow all files. Previously, everybody would have enable trace flag for that behavior that’s ignored now so you have to set a setting that says auto-grow all files.

Mindy: I’m staying away for the totally obvious ones so… let’s do come up with.

Mindy: My name is Mindy Curnutt.  One of the things that I recommend all of our customers put on their servers and that I’ve scripted up and I give to them is like a complimentary script is an administrative failed logging attempt alert system. So it basically goes out in it is querying the error log, event log to look for the 18456… What is it? 18… No, it’s 18, failed log-in attempts basically. I think it’s 18456, event class, something severity, whatever. I used to know it at the top of my head but I’ve gotten too administrative. So anyway and then I have it set where if there is like if someone is trying to get in with an account that has administrative level rights, like SA for example, and someone has try to get in with that account with the wrong password more than x amount of times and it’s configurable within a configurable time window. It will send an email to the administrator so you know someone is trying to guess your admin password and you got their IP address and you immediately know that’s happening.

Carlos: What’s a little scary there is I’m constantly surprise that the number of error, SQL server logs that I see where I’m just constantly seeing like failed SA log-ins. Like back to back to back to back… And normally it’s like some machine that just forgot to change their password but it just constantly banging on the server and you’re like, “Hmm, that seems a little weird.” Anybody else, here we go.

Alex: I would recommend customers take closer look what a developer is using on their code to dynamically execute string. The problem what I fixed and forcing C# developers to be more specific especially with SQL parameter class that they are using default and the default is nvarchar. And most our databases we have a varchar which is a trigger convert implicit. Nobody can see it but it cost lots of problem in their performance. So number one I forcing developers if they don’t want to use, it’s called SqlDbType, implementing have to be varchar because by default it’s nvarchar. So to avoid such problem better to use store procedure which is much more manageable or just to keep an eye on your ideal code that’s much more for C# developers and be specific with data types.

Carlos: So that’s kind of an interesting take, right, so like setting a policy before you get going?

Alex: An interesting part they say when I’m done in presentations about. They say it’s[00:05:00] the best practice to specify data type in request code. I have a code, I wrote a code for them, and guys, that’s what you should do, that’s have to be. So sometimes we’re teaching .NET developers something else.

Carlos: Sure. Here we go.

Jeremy Fry: My name is Jeremy Fry and I agree with the whole room that these changes that people are indicating that are best practice to change or reasons why they would change it. Mine is max memory and the reason why it’s because I am a BI guy, and so other tools need to run on a server analysis services, SSIS. Although I would like to in real world scenario and in a best use case scenario break those components apart where I’m segregating if I have a warehouse my analysis services on its own server. An instance versus on the production server where your transactional information is held and your day to day business is occurring. But in a perfect world, that’s doesn’t always happen so with that being said sometimes I see memory the analysis services utilizes memory is it starts to cut things off at about 80% utilization. And so if you have high level of things happening on your transaction system that’s utilizing a lot of memory then you start to see a bottleneck down the line in other tools as well.

Woman: Ok, nobody said it so I was leaving it there. When I install a new instance of SQL server and it depends upon the resources that are in the actual operating system for the SQL server and of course also what other instances of SQL might be on that server so there is no tried and true setting that I use to put it at but I always go in and look at the cost threshold for parallelism and the max degree of parallelism because the settings that they are currently set at I don’t think are realistic for today’s hardware. And Microsoft, of course, likes to be always backward compatible so those values are kind of sitting at levels that are no longer appropriate for either today’s hardware or the platform anymore. Now what you set them at can vary but you need to look at that I think. Too much parallelism could not, you know, it’s like too much cookies, right?

Carlos: There you go. That’s a knowledge that I haven’t heard before – cookies to parallelism.

Alex: [inaudible – 7:58] package but you’re just killing parallelism. It’s not right for every single, there should be balance. I can’t find formula what the number of CPU and.

Woman: It depends on the code.

Man: Yeah, I start with 25 and I go up as far as a hundred.

Alex: They start it from 80 go through to 300. I told them, guys, I’m only one who is fighting a whole team including manager. I’m opposition. I hardly convince manager to jump over 2014. Skip upgrade 2014. Go over to 2016 directly because 2014 doesn’t do for application. Absolutely, CPU utilization, that’s true. Other than that, 0, just jump. I was like this to be fired because our manager he doesn’t like any oppositions. Somehow I got another back from my Pennsylvania team, he was my manager in Ireland. And the Pennsylvania team backed me up that’s why I still in here. He doesn’t like any opposition.
Tracy: Yeah, definitely query store then. Change the size. It only keeps 100MB of data. Change it up to like 2GB or something and tell it how many days you want to keep because it keeps up to a year. And get it off of the primary file group. There is a connect item for that. I twitted it out this morning so you can find it and upload it.

Woman: Ok, here is one. How about optimized for adhoc workloads? I’ve read that here has never been one negative, somebody is going to like respond, but I’ve read no one has ever ever ever seen anything negative or bad ever ever happened turning that on, ever. There you go, one more.

Carlos: So I do know that [word unclear – 12:29] experienced it once that when he turned it on he actually saw a CPU spike that he couldn’t explain. So you could toggle it, right? Turn it back off, right, CPU would. I can’t remember what the spike was but I think it was may have been negligible. But basically he could flip it and then he would see CPU differences. Now, I don’t know if you eventually tribute that to something else and it was like luck that somebody just happen to be doing something else while he was toggling that. That was the one freaky thing which I need to follow up back with him. But I don’t how he resolved that. I know that’s what [name unclear – 13:09] was complaining about.

Man: The one application that ran the same query plan only twice for every single. The one really efficient. If it comes exactly two times.

Woman: It was a query that checks to see if there were records were there before it actually runs the query. It seems like …

Man: It could be.

Woman: Really, [inaudible – 13:31] You’re like, go to the grocery store to see if they are open and you go back home and you get your car.

Man: I see you’ve met my developers.

Man: This is like one really [term unclear – 13:54] kind of thing. But for SSRS I would advice there are logs for when stuff gets used, and the default, I forgot, I think it’s like a month, maybe two or three but you can set it all the way to like 6 months. And that logs are really useful when you need to go back and say is anyone still using this report, because usually the answer is no. I mean, I know we’ve got a bunch of reports that are just aren’t being used and at some point we need to go back through, and it’s really nice to have 6 months of data that says the only time it was ran was when you ran to if it is still working. So that’s a nice change for SSRS is just get that log because at some point you’re going have to do cleanup because you just get report blown. It’s a problem.

Mandy: This is Mandy again. So that makes me think of having a SQL agent job that once a week or something cycles your error logs so you don’t end up when some things actually not going well and you want to go in the error log and take a look and your server has been up for a year. Yeah, good luck with that, right? You’re[00:15:00]like waiting, and waiting, and waiting, and like two hours later the thing might come up, right, so awful.

Tracy: Hi, Tracy Boggiano, again. Can we make the Hackathon noise go away and error logs please?

Man: Oh my god, error logs, gigabytes.

Tracy: Gigabytes in a day. System [inaudible – 15:30] points getting full [inaudible – 15:31] gigabyte log file so we only keep 30 days.

Man: All the messages are really scary. Couldn’t identify this file, couldn’t do a check point. All these horrible stuffs here that’s Microsoft, they are like…
Woman: Oh, your favorite settings. First is SSMS, if you like to have it, do certain things, and you want to see sp_helpindex, and you want to see sp whatever, right?

Woman: Don’t you go and setup hot keys. You don’t have to sit there and do that. You have a certain way of working.

Man: I’m not smart enough to do that. I don’t have any ITs that… I know what I like to do, that’s why.

Woman: I know there was one version of SSMS were they got rid of control [inaudible – 16:28] I couldn’t even work because I don’t hit that [inaudible – 16:30] some kind of road thing. You put it back, I think there are so many people like, “Arhaha”.

Kevin: This is Kevin Feasel. I must confess I’ve been lying the whole time. The first thing I do is install/configure PolyBase. The second thing I do is install R. The third thing I do is change the log growth.

Brian: This is Brian again, while we’re doing confessions, I would say that now I’m basically a broken person without SQL Prompt.

Carlos: We got a couple of concurrence over here. Yeah, SQL Confessions, oh boy!

Man: Just consider it the [inaudible – 17:24] DBA you got to buy SQL Prompt, just get it over with, alright?

Carlos: Yeah, I guess I’m interested when they are going to. Is there a competitor for the SQL Prompt. I know that Compare.

Man: I don’t use SQL Prompts.

Carlos: Oh, what are you using?

Man: I use DevArt SQL Complete.

Carlos: Oh, DevArt, ok. Yes, such true, they are there. I feel like I see a lot more publicity on the other tools maybe because of a compare and all, you know.

Man: [term unclear – 17:54] has also a SQL completion tool. I think it is also named SQL Complete.