Episode 32: Level Up Your Game With PowerShell

1400PowerShell, isn’t that a Windows Administration tool?  This is certainly how it is positioned by Microsoft, but why should the DBA community give it more than a passing glance?  My guest is Mike Fal and we chat about ways data pros can use PowerShell.

Mike says there are two reasons why PowerShell is valuable for SQL Server folks.  I hope you will join us for the conversation.  We promise not to bore you with syntax.

Are you using PowerShell?  I would love to hear your stories in the comments at the bottom of the page.

Transcription: PowerShell

Transcription: PowerShell

Carlos L. Chacon: This is the “SQL Data Partners Podcast,” Episode 32. I am Carlos L Chacon, your host. Welcome, compañeros, to the show. Thanks for joining us on the SQL trail.

This podcast is dedicated to SQL server-related topics, designed to help you become more familiar with what’s out there and how you might use certain features or ideas and apply them in your environment. The SQL community is a great big family. We like to connect with others, as well.

Today, our topic is PowerShell. I have to admit, I’m a little nervous about this subject. I was in Seattle at the Brent Ozar FreeCon last November, November of 2015.

There were about 30 of us at this event. I was trying to reach out to some folks about podcasting topics and wanted to see if they’d come onto the show with me.

In that invitation, I said, “Some topics may not work well in a podcast. PowerShell is one of them.” Sorry, Mike.

Of course, Mike Fal who’s our guest today. He’s a big PowerShell guy, and he thought that was full of baloney. He said, “No, this is a great topic. You need to talk about it. You need to have me on your show.” I thought, “OK.”

He’s here to prove us wrong. Mike is a data base consultant with UpSearch. We’re not going to be talking about syntax or anything like that. Although, we might hit one or two pieces that might come up.

However, we are going to discuss why you my Compañeros need to think about PowerShell. How it can make your lives a little bit easier. Maybe how you would go about getting started, or increasing your knowledge in PowerShell.

Today’s episode is brought to you by SQLCRUISE — Learn. Network. Relax. Grow. Just add water. Check out sqlcruise.com for more information. Let’s get to it. Welcome to the program. Mike, thanks for coming on the “SQL Data Partners Podcast.” It’s good to have you.

Mike Fal: It’s good to be here, Carlos. Thanks for letting me tag along here.

Carlos: When I think about community members, you are one of the folks that has a good niche or a good brand in the PowerShell space. You talk about it. You blog about it. You give lots of presentations about it.I know I wanted to have you on, and talk a little bit about PowerShell. Ultimately, this is a SQL Server target audience, so why as SQL Server folks do we need to be concerned about PowerShell?

Mike: I know that it’s an interesting question and one that I often think about. A little bit of additional background with myself is this. I am a SQL Server DBA. I’ve been one for 15 years. I get it where people come from the SQL Server background and say, “What’s this new PowerShell thing? Why should I care about it?Why does it matter to me? It’s funny too, because PowerShell isn’t that new. It’s been around actually for almost 10 years now.

Carlos: I know. Isn’t that crazy?

Mike: Yeah. But it’s been seen as a system administrator tool, and I get it. That’s partly on how Microsoft pushes it. That’s partly on how the SQL Server team supports it. It’s just in general how it gets exposed.To answer your question why, it’s interesting…I have a slide that I put into my presentations to answer this exact question, because it’ll always come up. There’s two reasons primarily in my mind, why PowerShell is valuable for SQL Server folks.

The first is that it works outside of the stack. What I mean by that is, your SQL Server is very good at managing data. But it’s not so good at other Windows tasks, like if you need to get some information from the Active Directory. You can do it. It works, but it’s a pain in the butt.

Carlos: Yeah. It is a pain.

Mike: Yeah. Linked servers and ADSI objects, it’s a nightmare. It’s the same with the file system. If we go to make a backup in the file path, to where we’re backing up doesn’t exist, SQL Server throws out a big, fat error, and says, what am I supposed to do?PowerShell is baked into the Windows operating system, and in my mind lives outside of the stack, or alongside with it. It can interact with the SQL Server. It can interact with the file system. It can interact with Active Directory. Exchange any number of things, and in a better manner.

There’s always the conversation, for example, the SQL Server, we can do a lot of stuff through SQL Command Shell. But there’s security concerns around that, and how that interacts. I know there’s things you can do around that, and there’s that ongoing debate.

But PowerShell gives you a more robust, a more flexible way to work with the file system and the operating system. Then, alongside it, interact with SQL Server. That’s the primary, the number one reason, why I would say to SQL Server folks why you would want to consider PowerShell for your tool belt.

Then the second is, when Jeffrey Snover, who is a Technical Fellow at Microsoft, and the guy who basically came up with PowerShell, when he came up with it, and his team put it together, they realized that, when they were building this sort of tool, administrators aren’t dealing with one server, or two servers, or three servers.

They’re dealing with 20 servers, 50 servers, a 100 servers. So PowerShell has multi-server execution kind of baked right into it. From the ground up it’s designed to say, I’m not going to just run a task in one place. I’m going to run it in a number of places.” We run into this in SQL Server all the time, like, “OK, I’m going to go and execute a script on a SQL Server.

Then I’m going to run that same script on another server, and then another server.” We have other tools available to use to help with that. I know Red Gate has their Multi Script tool. You can within central management server do multi-server execution. They’re a little clunky. I don’t want to say Red Gate’s tool is clunky, but it’s also not supported [inaudible 6:33] as I understand it.

We have this situation where we need to do the same thing in multiple places. PowerShell is built to do that from the ground up. There’s the third reason I also throw out. It’s kind of a “duh” reason. I think it makes a lot of sense to everybody. It actually applies to any script. It’s not just PowerShell, it’s T-SQL, it’s VBScript, it’s what-have-you.

If I write a script…if I put a task into a script and automate it, I can hand that script to somebody else, and they don’t even necessarily have to have knowledge of what’s in the script. They can just run it, right?

Carlos: Right.

Mike: The nice thing about PowerShell is most system administrators are familiar with PowerShell, so let’s say for example I have a script — and I actually have several demos on this — of using PowerShell to automate my SQL install from start to finish. Not just to install SQL Server, but to configure it, get all the file paths set up, set service counts, what-have-you.If I wrap that all up in a script and I hand it to a system administrator who may not be familiar with SQL, but he’s like, “I know PowerShell. I can run a script, and I have some general understanding of that,” he can take that and do that task for me. Because the other thing is, I’m a lazy DBA. I don’t want to be clicking buttons and running SQL installs.

I want to be designing architectures and building high level concept stuff, so I’ll hand the menial tasks off to somebody else, particularly if I can script it, or, honestly, if I can wrap it up in a PowerShell script and automate it completely so it’s push-button. That’s the long and sordid story of why I would say SQL Server should consider PowerShell for their toolbelt.

Carlos: Yeah, there you go. I think with that script, as you mentioned, as you hand that off to the admin, it’s so something they feel comfortable with, but it’s also something that they wouldn’t necessarily feel uncomfortable with. You hand then a T-SQL script, now they have to get into Management Studio, or something else.Now they feel like they’re touching the database. They may like, “Well, hey, that’s not me. That should be you.” You’re like, “Hey, here’s a script.” I think that could make that transition a little bit easier.

Mike: That’s absolutely right. I think that’s also one of the things that saves you some time with using PowerShell is if you’ve scripted everything out, you don’t have to add the additional layer of, “Well, I have to log into Management Studio and run this.” Because there are utilities baked into PowerShell to execute directly against SQL Server. So it can save you a step or two.

Carlos: Now Ron Deskins, who is part of the Richmond User Group community, he’s one of the leaders. He works for Markel, a large organization. His opinion — now he said this a year ago — but he said that if you were an administrator now, and he’s including database administrators in that, and you didn’t know PowerShell, that in five years, you wouldn’t be able to find a job.

Mike: That’s a fairly extreme statement.Obviously people like to make those extreme statements because it starts a buzz. But I will say that PowerShell is becoming more and more important in the Microsoft stack. Certainly, it’s getting pushed as a necessary tool for system administrators. Aspects like Exchange and Active Directory are, at this point, starting to be administered almost completely through PowerShell.

In fact, you have GUIs, Graphic User Interfaces — to manage things like Active Directory and IIS and Exchange. They’re all executing PowerShell commands underneath. So it really is, for system administrators, yes, I would say that’s definitely the case. SQL Server administrators, it’s a little fuzzier, simply because one, yes, most of what we do is T-SQL.

As long as you have a solid understanding of T-SQL, you’re going to be employable, you’re going to be fine. However, you’re going to be at a disadvantage to people who know PowerShell, to people who can interact with that language to manage tasks in larger scales and in more efficient manners.

I get where the sentiment is coming from. I sort of agree with that sentiment. At the same time, people should not be panicking and going, “Oh my God, what am I going to do?”

Carlos: [laughs] Let’s get into a little bit of that, some of the differences between T-SQL and PowerShell, or that balance.

Mike: In one aspect, they’re not all that different, because for the most part, PowerShell and T-SQL are top-down, interpreted at runtime script languages. You write a T-SQL script, and it goes from the top to the bottom and executes stuff. PowerShell is a very similar way. In PowerShell, you can create functions that are certain programming constructs that you can apply within PowerShell.I like to think of PowerShell more as an object-aware language, and not like a C#, which is object-oriented and you create classes and you instantiate objects in all of this. PowerShell, because it’s a top-down scripting language, it still has that particular feel to it, but it’s built on .Net. That was another conscious decision with the PowerShell team when they built it. They built it on top of the .Net framework.

Everything within PowerShell is an object, and you get this robustness of manipulation and data access. Strings are not just strings. They’re string objects. Integers are integer objects. You get date time objects. I think that’s actually a good parallel between SQL Server and the PowerShell language is, PowerShell supports this idea of strong data types. Databases are built on data types, right?

Carlos: Right.

Mike: They’re very important to us. Selecting the right data type is a key component to database design. Just as well with PowerShell. To extend it out a little more of this comparison, PowerShell also has a lot in common with the Unix environment in Korn shell Bash shell. For people out there who are familiar or have done some time in Linux, they’re probably, “Oh, OK. I’ve done some of that scripting.”Some of the syntax and the format of PowerShell has a lot in common with Korn shell and Bash shell. At the same time, it takes it a step further by adding the strong data types. There are some similar concepts. At the same time, PowerShell is different in the way that you…it’s more like a C# in the way you writes PowerShell.

You’re going to have curly braces, and you’re going to have loops, and you’re going to be creating variables. Certainly, variable declaration is completely different in PowerShell than it would be in T-SQL.

Carlos: It’s interesting that you’ve just mentioned a couple of things, like all of a sudden you’re talking about PowerShell, and you’re in Korn shell, and all these things, and I’m like, “Oh, my gosh.” Somebody, for example, like myself, who was never a programmer. I took a class in college, but that was not my thing. I come from the networking side.I think, “Oh, man. T-SQL was, in a sense, one hurdle,” and now you’re saying, “OK, gosh, now I got to go learn all the shell and everything here.” [laughs]

Mike: No, not at all. Again, to speak to my background a little bit, I came up from system administration myself. I don’t really have a programming background. My “Secret shame” that I always like to joke to people about is I actually have a music performance degree out of college.I went to CU Boulder and studied to be a musician. Because that didn’t pay so well, I found myself going into IT. I’ve never really done any sort of programming outside of the scripting stuff. I did dabble a little in Java and C# at one point, just to see what it was like and if it would interest me. You don’t want to see my C# code, man. You do not want to see it.

I get it, where it feels a little overwhelming. That’s the thing is it’s, anytime people look at PowerShell, they’re like, “Oh, my God. It’s this whole new thing that I have to learn.” I would say it’s not as new or there’s not as much to take on as people might think. First off, yes, PowerShell is very deep. Then again, so is SQL Server.

If you really want to be a true expert at PowerShell, you’re going to spend a lot of time with it. [laughs] But to get started, most of us have used CMD.exe. We’ve gone in, we’ve run directory workups. We’ve done IPConfig, ping, all of that stuff. You can do this all in PowerShell. Then you start to take little steps beyond that.

You say, “OK, now I’m going to do a directory lookup, but now I’m going to stuff that into a variable and maybe process through those files. Maybe I’m going to add some code that will check and see if — we go back to the backup path example — check and see if the backup path exists before it tries to execute a SQL backup.” If it doesn’t, create it, then execute the SQL backup.

Those are some very simple patterns that people can ease into. That being said, yes, PowerShell is a deep language and takes a long time to…I’d say people can get their feet under them in probably about three months. Just messing around with it and playing with it. I’ve been working with it for two and a half years now. I’ve got a pretty good grasp, but there’s plenty of stuff that I’m still like, “Oh, that’s beyond me.”

Carlos: [laughs] Interesting. You mentioned the backup example. If we were to apply the same idea to T-SQL learning, a lot of us, there are a lot of scripts out there that we will take from the Internet, and we will try to apply it in our environments. Is that something we could start with PowerShell?

Mike: Yeah. Actually, it totally is. There’s plenty of examples, and folks, I’m not the only SQL person talking about PowerShell. I talk about it a lot. I did that because I didn’t feel like there were enough voices, but there are some pretty good voices out there. For example, Allen White works at SQL Century, been doing this stuff for a long while.He’s got tons of great blog posts up on sqlblog.com, of things that he’s done with PowerShell, little learning cases, whatnot. Ben Miller, aka @DBAduck — he’s out of Utah. Another really strong PowerShell guy — got a lot of good scripts out there. Laerte Junior — he’s a PowerShell MVP, but SQL Server DBA out of Brazil. He writes a lot of stuff up on Simple Talk. There’s resources out there.

Again, people, if they want to go into my blog at mikefal.net — Mike F-A-L — because sometimes people want to add the extra L. I don’t blame them. It happens all the time. I’ve got a bunch of little scripts and snippets and things that I’ve messed around with. There are resources out there.

Carlos: We will post these that you’ve mentioned. If we connect and you have a few others you want to toss my way, we will put these up on the show notes for today’s episode, so it’s sqldatapartners.com/podcast. You can go there, search for today’s episode on PowerShell, and find this information.

Mike: Yeah. I actually will provide free, because I have several slides that I put at the end of my presentations that I go through different resources. Books that people can use, blogs that are useful, online training. Just to mention it, Microsoft Virtual Academy has like hours of free PowerShell training. If people want to do webinars, go up to.Now, it’s just generic PowerShell training. A lot of those are targeted to people who are doing more system administration stuff, but once you get the language fundamentals under you, then it’s up to your imagination. It’s up to, “Hey, what kind of problem do I need to solve?”

Carlos: The sky is the limit.

Mike: Yeah.

Carlos: Now, so you recommend someone starting, and let’s say they’re either…you brought the example CMD. I can use CMD, kind of feel comfortable with that. I want to start using PowerShell. Should I take a couple of classes to Microsoft Virtual Academy to understand some syntax there before I jump into, let’s say, using a PowerShell script to back up my databases, or should we just jump right into one of your scripts that we could use to do that?

Mike: Everybody’s learning method’s a little different. Some people like the in person classes, some people, they want to read, and study everything, some people, they like, “I’m going to dive in, and I’m going to put my hands into it up to my elbows, and just get messy. Just…”

Carlos: Yeah, “Let’s do it.”

Mike: “…full throttle over myself, and see what comes out.” Personally, I’ll be honest. I’m the latter of like I just dive in, and I start trying to figure stuff out. A good starting point no matter what your learning path is, any of these paths that we’ve talked about, I always recommend the book “PowerShell in a Month of Lunches.” It’s a book by Don Jones and Jeff Hicks.

Carlos: I have the book.

Mike: A lot of people do, and what it does is it breaks up the PowerShell fundamentals into, I want to say, 26, 27 different lessons that are each about an hour long. The idea is, is on your lunch hour, you just sit down, you want to run through the exercise. It’s pretty low impact, but you reinforce these fundamentals over the course of the month.Lots of people use that, they love it. I’ve got nothing but good things about this book, so I always recommend to people that. Now, for me what was useful, once I had the language fundamentals, a couple of things that are good patterns for people to get into to learn PowerShell, one, stop using CMD.exe. Use PowerShell.exe.

The reason is, is because pretty much everything you can do in CMD.exe, you can do in PowerShell.exe. If you needed to run a ping, or you do an IP config, again any of these commands, you can do that through PowerShell.exe and then you’re in the language. You have the access to all the other PowerShell functionality.

It’s just that’s a good app to get into. Then I will typically recommend to people, once they’re in there, just walk through the file system. If you got to go to a directory, or you need to maybe delete some files. One of the big light bulb moments to me for PowerShell, and why it was such a useful tool is DBAs.

Most of us have experienced the scenario of that 3:00 AM phone call, where backup was failing because the drive filled up, because it’s just full of old backups. We’ve all been there. We go in and plug in the computer, we go into each of the directories and we delete what we needed to. What I found is — if I need to delete all my transactional backups older than three days, I wanted to do that, I can do that across an entire drive with one line of PowerShell.

That saves me 10s, 20s, 30s of minutes, if not an hour. That was to me the big light bulb moment of like, “Oh my God. This is amazing.” That’s what I recommend to people for learning. It’s to say, “Get into PowerShell, get on that command line, manipulate the file system, explore things that way.” It starts to reinforce the patterns of how the language works.

Carlos: That’s a good point. As administrators, we have this mantra of, “Do it once, and then script it so you don’t have to do it again,” but auto this task and you make a good example of… If you’re doing that more than once then you’re not doing yourself any favors. You get a ticket, maybe other people see it like, “Oh, I have to engage this person.” You find some value, but make that easier on yourself. Instead of having to take that time, script it out.

Mike: Exactly. It comes back to again…I’m a lazy DBA, so I try to automate absolutely as much as I possibly can. I’ve had those kind of conversations with people. A lot of people come to me and say, “We can’t automate everything.” And I’m going, “Well, I think you can and at least you should make the effort.” We can have the conversation.There’s certainly a lot of good philosophical points about should be automated and what shouldn’t be automated. To use an example, I can sit down and automate out backup or restore testing. I actually have scripts for that, where I automatically restore databases to another server, I do DBCC checks and I email if there’s any errors or any problems with that process. A lot of people are like, “Oh, that’s great, that’s saves me a lot of time.”

I would also say that there’s a lot of value in practicing the method itself. Even though I have a script and a Powershell script that does that automated database restore, I will still sit down and manually restore the database, point in time, because I need to make sure I’m still good. When a fire breaks out…

Carlos: It hits the fan.

Mike: …and everything goes to heck, you’ve got to know what you’re doing. It’s that fine line between automation and practice.

Carlos: There you go. Great advice from Mike Fal. Thanks for that information. We’ve started the conversation, of course we’ll have some additional information at sqldatapartners.com/podcast. If you want to check out Mike’s stuff and some of the scripts that he’ll make available to us. Mike, it is time for what I call the SQL Family portion of the program.

Mike: OK, then.

Carlos: Here, we like to talk more about you, and some of your life experiences. Now, one of the things we’d like to talk about is tools. How people are working, what they’re using. Now, we’ve been talking about PowerShell, and if that’s your favorite tool, that’s cool, but we want to know what your favorite tool is. Free tool, paid tool, why do you use it, and why do you like it?

Mike: Oh, gosh. I think probably my favorite free tool is sp_WhoIsActive.

Carlos: Ta da.

Mike: Adam Mechanic script.

Carlos: Yes, Adam Mechanic

Mike: I will be perfectly honest that I think I would find it hard to troubleshoot problems if I didn’t have it because it’s just so useful for wrapping up information about a problem. When somebody calls me, and says, “Hey, there’s a slow query,” or, “We’ve got a problem,” almost my first command that I execute is sp_WhoIsActive to see blocks, what are the processes waiting on, and it’s all right there.Sp_WhoIsActive is probably my favorite free community tool that I would use.

Carlos: Very cool. Before we go on, I do want to mention our sponsor sqlcruise.com. Now, Mike, are you a cruiser?

Mike: Actually, so I’m glad you brought that up. I went on the last Alaskan Cruise a couple years back.

Carlos: There you go.

Mike: I can’t say enough good things about SQL Cruise, and the kind of amazing opportunity it is. It was career changing for me. What I always like to tell people is one of the instructors was Buck Woody for that particular cruise.Because of the intimacy of the cruise, it’s 20 people. Tim keeps everything small, and you got a lot of good face time with the instructors. I had the opportunity the opportunity to walk around Ketchikan Alaska for three and a half hours with just me, and Buck Woody just talking.

Carlos: Very cool.

Mike: He really opened my eyes to some things with where the industry is going, patterns and practices, things that I should consider out with my career. It did really change my career. I can’t recommend strongly enough for people to consider SQL Cruise as a training opportunity.

Carlos: I agree. Tim and Amy Ford have put together a great training environment, and they’ve offered listeners of the show $100 off the price of admission. Of course, we’ve missed out on the 2016 cruise already, but you can check out sqldatapartners.com/sqlcruise for more details on how to get on board, and how to save that $100, so check it out.Mike, you inherit a million dollars. What are you going to do with it? [laughs]

Mike: Whoa. What am I going to do with a million dollars? Gosh, you know, I’ll be honest. It’s not really a question I’ve given a lot of thought to, because in general, I tend to be, look, if it’s not a possibility, I’m not going to spend a lot of mental cycles on figuring it out.I probably would sit down, and probably try, and start my own SQL server consulting business. I have a lot of admiration for the Paul Randalls and the Brangosars and even yourself, Randy Knight — these guys have started their own boutique SQL consulting firms. That’s one of the exciting things of going to up search — I’m getting to participate in one of these unique little groups.

One, I think these outfits get it. They understand the balance of community involvement and helping people out with highly targeted, highly focused, highly specialized SQL Server consulting skills. I want to give more community members the opportunity to participate in that, because too many of us, we get stuck in the day to day grind. We’re at the corporate job, which we all get tired of.

A million dollars…I’m the kind of person that…I like challenges, I like things put in front of me, so I wouldn’t get the million dollars and say, “Well, I’m going to go…I might spend some time on a beach.” I will confess, I probably would spend a little time just relax…

Carlos: At least the weekend, right?

Mike: Yeah, at least the weekend, but I would get bored so I would want to invest that money in myself to continue to challenge myself and push myself forward. To be perfectly honest, owning and running a business — that’s a pretty steep challenge for me, but one that would be pretty cool, if I could pull it off.

Carlos: There you go. If the numbers come your way, make sure you let the compañeros here at SQL Data Partners know, and we’ll make sure that you get lots of good talent there with you. Our last question, Mike — if you could have one superhero power, what would it be and why do you want it?

Mike: It would be the ability to read other people’s minds. This is because…We’ve all been there, right? We sit down, we sit with — whether they’re our customers, whether they’re our clients, whether they’re our co-workers. One of the things that I…

Carlos: The spouse.

Mike: Spouse. What have you? One of the things I always struggle with is communication. Not personally, as you can tell, I talk a lot, I’m pretty open with that. Somebody will come to you and say, “Hey, I’ve got this problem or this thing’s going on.” We have to sit and tease them and work with our interns.It’s very much an art form to figuring out without being able to read somebody’s mind — what they’re thinking and what they want. If I could read their mind, I’d skip all those steps like, “Got it. All right. I know what you got. I know what you need, I’m going to take care of that for you.” Or, “I know what you’re trying to get at, no confusion, we’ll get it sorted.”

Carlos: Much responsibility, much privilege. Or much privilege, much responsibility there, right? Start reading my mind. I don’t want to hear about my dirty laundry online.

Mike: I will keep that to myself, sir. I promise.

Carlos: Very good. Mike, thanks for being here. I so appreciate your time.

Mike: Thank you, Carlos. This is a great opportunity and I look forward to having more conversations with you over the air, and hopefully at Summit.

Carlos: There you go. Compañeros, if you liked today’s episode I invite you to leave a comment or review on iTunes or Stitcher. Of course, you can go to our website, at sqldatapartners.com/podcast and leave your comments there. This will allow others to find it more easily, so they can enjoy the program as well. Thanks for tuning in, and I hope to see you on the SQL trail.

Episode 31: What Has Virtualization Done To My Database?

Virtualization with David Klee

1400As database administrators, we have to live with virtualization.  As a consultant, I can’t think of a single environment I have been in that didn’t have some of the SQL Servers virtualized.  Troubleshooting issues in a virtual environment can be a bit tricky and in today’s episode I chat with David Klee about his experience and how you might learn from his experience.

What has been your experience with virtualization?  I would love to hear from you in the comments below.

Transcription: Virtualization

Transcription: Virtualization

Carlos L. Chacon: This is the SQL Data Partner’s podcast. This is episode 31. I am Carlos L. Chacon, your host. I welcome all my compañeros to the show.

Perhaps you’ve listened to several episodes or perhaps this is your first one. Either way, thanks for joining us on the SQL trail.

This podcast is dedicated to SQL Server-related topics, designed to help you become more familiar with what’s out there and how you might take advantage of them.

Of course, the SQL community is just a great, big family and we like to connect with others as well. Today our topic is virtualization. I am joined by David Klee.

David Klee: Hello.

Carlos: He is the owner of Heraflux Technology, a shop devoted to virtualization and SQL Server infrastructure. So we’re going to be digging into his thoughts on SQL Server virtualization and how he deals with that with other organizations.It’s good to have you on the show again, compañeros. Welcome to the show. David, thanks for being on the program today.

David: Thanks for having me. This is great.

Carlos: Yeah, always exciting. Virtualization’s always one of those things that comes up. I think a lot of people are starting to do that.With your focus on virtualization in the SQL Server space, I thought, “Who better to come and talk with us a little bit more about that topic, and some of your experiences with that.”

Again, some people might be familiar with virtualization, but to set the tone, let’s talk a little bit about virtualization, and what it is, and how people are using it.

David: You bet. In a nutshell, virtualization is, in my opinion, it’s an extension of the physical machine hardware itself.It’s technically an added layer on top of the hardware that allows compartmentalized operating systems, and whatever apps you want to run on there, it allows them to coexist on the same physical machine without them being aware that the other apps or operating systems are there.

The way I look at it, from that perspective it’s a single physical machine, a few VMs, and life is good. The fun part there is when you start to look at in en masse, you start to remove the definition of the physical server.

I start to look at it as a collection of physical machine compute resources that you have available to you, and queues for these operating systems, and their respective apps, to get to it.

If you need more resources in the physical machine virtualization cluster, you add more and tell it to load balance. You can assign these resources to a VM, and every one of these VMs has to go through the queues to get to those resources.

Carlos: That’s interesting. You talk a little bit about those queues. I guess my thinking of virtualization was that I just take a big box and I carve it up into chunks and make little boxes out of it. [laughs] Then I guess there’s a few more components to that, the queues being one of them.

David: Yeah, there’s a few more in there. If you were to just strictly carve it up, and this is what a lot of folks do. Let’s say I’ve got a 16-core physical machine. Does that mean I can carve up eight two vCPU VMs and that’s it?Not necessarily. You might actually be able to get away with 20 vCPUs, or even 30, being allocated on the machine even though you might only have 16 there to use. That’s where the resources in the queue model really works, because it’s not just a one-to-one map or X amount of this.

I can over-provision and over-commit. Sometimes you can do it safely. Sometimes not so much. This is one of the biggest challenges with database server virtualization. It’s that over-commit versus performance.

Carlos: Right, and I think that would be a common scenario in that from the VM side, or the network guys, or whoever is controlling the infrastructure there, the infrastructure guys, they’re like, “Oh, hey, we can get more servers into that,” but the SQL Server guys are like, “Well, hey guys. You’re killing me here.”

David: [laughs] Well, that’s the fun part. The history of virtualization started with leveraging virtualization to consolidate the data center, and it’s real fascinating because that consolidation mindset persists today. When you look at it, the VM admin, they’re told by the organization to squeeze as much onto the infrastructure that we can afford.From an DBA perspective, they generally don’t have a lot of experience with the infrastructure. Conversely, the VM admin don’t usually have a lot of experience with the apps that they’re virtualizing.

What worked back in the day for file servers, print servers, active directory, maybe web servers doesn’t necessarily work for enterprise-level database servers today, so you end up with a conflict of interest where one side doesn’t know the best practices for the other.

Carlos: How do you go about or engage with clients or whatnot? Again, these are SQL Server folks listening to this podcast.As they’re going to engage their VM admins, what would be some of the tactics or some of the questions that they might be asking to insure that they’re getting…And even some of the data that they might need to provide to say, “Hey, VM admin, this is what I’m seeing.” Or, “This is what I need from you to make sure that I’m not being impacted.”

David: Yeah, it’s all about the education first and foremost. What the DBA needs to know is how to translate what they know and what they need into the language that the sys admin really has. The DBA knows transactions per second, disk stall. VM admin doesn’t know that.What you have to do is translate that to disk latency, IOPs, CPU time, things like that. The stats that a DBA need from the VM admin, it’s essentially what VM runs the same host, what’s the performance characteristics on that host?

Am I under memory pressure? What’s going on on this machine in any given time? For example, let’s say I’ve got five SQL Servers on one physical machine.

If CFO comes along and runs a really big financial report, and it spikes the CPU, and the disk is just churning, what is that doing on the other VMs on that same physical machine? The DBAs don’t know, because it’s all isolated and compartmentalized.

All you know is that stuff is just running slower. So they need to ask what else is running at this time. What are the resources doing as they basically get allocated from VM to VM?

Carlos: Would you normally provide that just like some PerfMon metrics?

David: There’s a lot of different layers in there. There’s PerfMon inside Windows, inside each VM. There’s a number of things within side the SQL Server DMV — the DMV scripts, all over the place. Then you’ve got inside from system center with Hyper-V or V-center within VMware. You may also need to get switching infrastructure — networking, or fiber, or both. There’s storage performance characteristics direct from the SAN.If you have something with application-level metrics — some of these utilities like New Relic, you can actually see how long it takes relative to an app click — is it application server, is it backing database server, is it actually the browser? You’ve got to put all these metrics together and once you do that, you can paint a really clear picture of what’s going on.

You let these trends come out for you. I don’t have to make any assumptions. The data there gives you the answer. You just have to put it all together.

Carlos: Right. I think that so many other topics you’ve covered on this podcast is the need to communicate and work with the other groups in your organization.

David: Yes.

Carlos: I think virtualization is obviously something you can’t do all by yourself, right? Particularly unless you own the VM stack there as well, but if you’re dependent on that you’ve got to agree and get along with those team members, to share some of that information.You mentioned a couple of things. One VM in the SAN is involved, which may or may not be the same group. Then the database — that’s switching. That’s a lot of organization and orchestration that needs to happen.

David: In the networking group that may be slow for availability group transfer, and backups may have competing schedules. And DR, which may cause other problems. It gets real fascinating real fast.

Carlos: I think this is one of those areas, where you can, as a database administrator, you can begin to separate and provide leadership. That role is changing a little bit and we can go and say, “Well, I’m not just going to take another database, I am going to empower or provide information to these other groups, so that we can all provide a better experience for our clients, for our customers — whoever they may be — whether internal or external.”

David: Exactly. The role’s changing and in some really interesting ways and a lot of DBAs are either resistant to the change or don’t know where to begin with it. It’s really interesting.

Carlos: Having said that, with virtualization and all of these things and the DBA role, where do you see that heading?

David: The DBA is always going to be around. I see it becoming even more important to an organization as time goes on. The challenge here is that, 10 years ago, if I were a DBA, there’s a good chance that I knew the NUMA configuration in my machine.I knew how to configure an HBA. I knew disk configuration. In virtualization, and now I don’t really need to know that. If you’re a DBA, you need to know I need these many CPUs, I need this much RAM, I need the storage presented.

If you go forward, look at how things are moving. You have software to find everything, you have storage, networking, security — all this stuff. A lot of the requirements to be a DBA from back then aren’t around today.

Tomorrow it’s going to change even more. Where I see a DBA going is less from an administrative and an availability standpoint, more towards an orchestration and a performance standpoint. Because the performance challenges will always be there and the goal — in my opinion — is to make things more efficient and not just perform better. If I make it more efficient, I use less SAN, I use less memory.

As a result, there’s a good chance that your app gets faster. But, at the end of the day, as public cloud becomes more and more here, essentially, if I can cut down the amount of CPU that I need and have just by understanding the performance characteristics of a data model, store products, or [inaudible 11:22] things like that, then, if I’m moving that to the public cloud at some point, I might be able to get away with a smaller footprint database or a smaller VM.

Carlos: Sure.

David: That saves it’s money. It makes things faster as you save money and that makes you a hero to the organization, [laughs] it makes you even more important.

Carlos: Yeah, exactly. I think taking on or embracing that change will only give you advantages as the organization continues to change.

David: Exactly. And that’s actually how we named our company, believe it or not, Heraflux Technologies. You know that phrase, “The only constant is change”?

Carlos: Mm-hmm.

David: …that was coined by a Greek philosopher by the name of Heraclitus.

Carlos: There you go. Now you know the rest of the story.[laughter]

Carlos: [inaudible 12:14] Very good. I’m sure that most organizations now have some form of virtualization in their environment. What current hang-ups do you see or…I guess we’ve talked about some of the issues that we can have but what are some ways that organizations are not doing virtualization correctly?

David: A lot of it goes down to those queues that I mentioned. A lot of is I’m trying to do more with less. Common sense would tell you more CPUs is faster, right?

Carlos: Right.

David: Well, not necessarily in virtualization. It sounds weird. It’s counter-intuitive to everything that a DBA has ever learned about infrastructure.One example from about six months ago — a company had a 32 vCPU VM running dog slow and they couldn’t figure out why. I went and did a quick PerfMon analysis trying to figure out what’s going on. I told them, “Take this VM. Make it 3 vCPUs”

So, we went 32 to 3 and they wouldn’t do it. They wouldn’t do it. [laughs] I finally had to say, “If this doesn’t make an improvement, I’ll buy you a steak the next time I see you. [laughs] Conversely, if I was right, I get a steak.

Carlos: There you go.

David: The next maintenance window, we shut it down, we knocked it down to three, and we turned it on. In their nightly run-time and on most application clicks throughout the day, 3.1 times performance improvement.

David: Wow.

David: That’s pretty significant.

Carlos: That is significant.

David: When you can get this by reducing the footprint of the VM, there’s less that has to be scheduled. If there’s a lot of scheduling already happening because it’s a busy environment, you cut down the scheduling, things can get faster and some cases they do.

Carlos: So you mentioned you were looking at PerfMon and so, this idea of queues. We have queues in SQL Server and that’s what we get wait stats and things for, these queues. QL Server works through these different items.But, when you talk about those queues, what were you looking for then? Is that a specific metric that people could take and then go talk to their VM admins and say, “Hey, can we take a look at that number of queues that are allocated to this VM?”

David: In some ways you can. It’s a little different. PerfMon, essentially, what I was seeing was that things were slow but the CPU activity was low. It was point-blank low. There was no reason for it to be low given the workload they were under. We actually got into the hypervisor, at this point, sort of poking around.The cool thing is most hypervisors, VMware and Hyper-V, both give you the amount of time that those CPUs spend in that queue. The amount of time spent in that queue is time not spent, actually, doing what you want it to do.

Carlos: Sure.

David: If it’s a small amount, no big deal. That’s normal. Everything is…

Carlos: It’s going to happen. It’s just a normal by-product, right?

David: Yeah. And, you know, most environments you’re looking at a quarter of a percent to maybe one to two percent. If you look at the way SQL Server parallelizes it, those values just get inflated to like one percent to three percent or four percent. That’s nothing. It’s measurable, it’s not really noticeable and everything’s OK. In this particular environment, they were at 65 percent.

Carlos: Wow.

David: That’s not cool. [laughs]

Carlos: No. That’s right. That’s a lot of time waiting in line.

David: Yeah, right. That’s exactly it. So we reduced the footprint of the schedulers, and that 65 percent dropped to 4 percent.

Carlos: Um. So, then the CPUs, actually, could be doing work instead of waiting around.

David: Exactly. And the 32 percent average CPUs consumption was in the neighborhood of 3 percent. When we went to three, the CPUs were at about 55 to 60 percent, but that’s OK because things were getting executed properly and we actually being efficient with the entire stack there.

Carlos: Interesting. That’s good to think about there. So, now you brought up Hyper-V and I think the landscaping virtualization is changing. As I sip my Microsoft Kool-Aid, right, I think they’re changing a little bit.At one time, VMware, I think was still the king or was the king, maybe still is, right, but obviously, Hyper-V has gained some ground and, I think, Azure are also using Hyper-V, so it’s gaining a little more respectability. Where do you see the landscape shifting there?

David: It’s an interesting time to be a geek. We’ll put it that way.[laughter]

David: VMware has been around for longer than Hyper-V. Hyper-V has come a long way in the short amount of time it’s been around. Honestly, I’m at the point where both hypervisors really work well.The performance overhead is very low. The performance challenges are generally not with the hypervisor at all but it’s more on how it’s configured and how it’s managed. The management tools are constructed on a different philosophy based on other platforms but they both work. There are some buzzwords that are different but in terms of features, in terms of availability options, they really both work well.

Now, that said, they both have direct interaction with Azure now and you can do Amazon. You pick your flavor of cloud. So, when it comes to telling people what to implement in their environment, I turn it back to them and it’s, “What do you have in-house experience managing?”

A lot of folks have Windows Server admins that are in and do a great job with it and then Hyper-V is a logical fit. They already know it. If it’s a VMware environment, if they’ve got folks that background experience with that, go with that. It’s whatever you can manage easiest in-house.

Carlos: Yes. I guess I’m not sure it never would have dawned on me. So, I’m assuming Amazon web services, AWS, is all VMware?

David: Oh, no. It’s their own derivative of, I think, one of the open-sourced variants.

Carlos: The way you said that, I thought, “Oh, wow. Gosh.” I didn’t realize. [laughs]

David: No. No. If they are, that’d be interesting.

David: That would be interesting. Yeah. I guess, one of the last things I want to talk about and that is so, disks. Right, so SQL Server disks are generally our biggest bottlenecks now in all of our systems. Of course, you give a CPU example so that’s not a 100 percent of the cases. Maybe that’s more in the physical world as well.I feel like in the VM environment there is a lot of hoops that I have to go through, and I don’t feel like I have a good understanding of my disk set-up in a VM environment. I guess advice for DBA’s noticing unacceptable disk speed or they have some questions about their disks, what may be some terms and phrases that they need to use to talk to their VM admins about it? [laughs]First of all, you walk into a big conference room and say “Of the DBA’s in the room, how many of you love your SAN admin?”

You get about one percent of the room that kind of quietly tries to raise their hand. Then you say “Of those who raised their hand, how many of you are your storage admin?” and usually most of those folks raise their hands again.

[laughter]

Carlos: Oh boy!

David: Oh yeah. Storage has been the most biggest problem with SQL Server performance for as long as I have been a DBA and a sys admin. If you look at the speed of CPU, you look at the speed of RAM, storage is the slowest piece of the puzzle. It is also the most complicated.It is a single point of failure in most environments. They’ve got 20 SQL Servers and probably only have one SAN. It’s tough.

Carlos: Right.

David: So, what do you do? Disk is getting faster and what the advent of all-flash arrays that are now becoming cost effective the infrastructure from the physical machine to get to the SAN now becomes a bottleneck. [laughs]It’s still a storage problem. From a DBA perspective all we see is elevated disk stall inside SQL Server and elevated latencies inside Windows. From a VMWare perspective you get the same boat.

You don’t have a lot of that control over where things go directly but you can still detect it, you can still see it. Those are the things to watch out for as a DBA. Inside SQL Server is disk stall. Actually, if you go out to heraflux.com not to market that but I’ve got a script out that actually lets you collect disk stall from inside SQL Server and turn it into disk latency matrix on a given pulling interval.

You can overlay that with Windows PerfMon and you can start to say “Do I have an IO challenge inside SQL Server that Windows is also seeing?” conversely you do not, SQL Server might have it and Windows might not, and if so you have an IO bottleneck inside the SQL Server engine.

This is one of the reasons why people say create more than one temp DN data file, because you can have an IO contention bottleneck to get to that one single temp DB datafile. So you can detect it pretty easily. Those are the things to look for.

Now what do you do about it? If it’s a SQL Server problem then more data files, more file groups, potentially more logical drives or mappoints in Windows and more scuzzy controllers can help to spread off that work-load.

If it’s still a problem, you got to start looking at the VM layer and beyond that. To have these IO matrix from both SQL Server and Windows to confirm that you have got these challenges it is vital, you have to have those. But then when you go to the infrastructure folks you have to have them really open the playbook, have them show you what’s the SAN doing.

What is the disk configuration like? From a DBA perspective I shouldn’t have to care how the SAN is configured or managed, or what the background load is, it just needs to be faster than what I need.

Carlos: Gathering those metrics is ultimately the key point right?

David: Yeah.

Carlos: You know what your SQL Server, your operating system is telling you and you can provide that data and say “Hey guys this is what I’m seeing, let’s see if we can make something happen.”

David: Exactly. I’ve got a goofy example of this. There is one company that — good friends of mine, they had invested $6 million in storage. All flash, heck of a set-up. On paper it looked greay. When they put it into practice and moved their VM server to it, the SQL Servers were experiencing over 54,000 long IO alert warnings per day.

Carlos: Woah.

David: The SAN was averaging 0.4 milliseconds response time and was idle. It was a fascinating project because for one, it was the only time I’ve ever had a chair thrown in a meeting.[laughter]

David: Yeah. It was basically an argument between the SAN folks and the DBAs. The SAN people were saying, “Your metrics inside SQL Server are invalid because it’s a VM,” which was incorrect to begin with.The SAN is doing this, “So DBA whatever you are talking about, be quiet do not bring this up again” and the DBA was saying, “We are seeing significant performance problems versus where we were at 6 months ago.” They just started arguing, it was fascinating.

What it turned out to be was actually something in the middle. The all-flash array was working great. They had a fiber switch in the middle that had one little setting wrong and everything behind that fiber switch was stacking up.

Carlos: Wow long queues, huh?

David: Yeah. It was just incredible.

Carlos: There we go. I think the idea of working well with others, playing nicely with folks. Yes, you’re a SQL Server administrator, you know a lot of things, but don’t forget that they know a lot of things too.

David: Yeah. It’s get the objective measurements, get the objective data to prove your points. Don’t accuse. You want to work with them, you’re there to support the business.So you get the data and you say, “We have a problem. These are the symptoms, these are the patterns, this is the data. Let us look at the rest of the environment and overlay the data that you have with the data that I have and let’s put this together.”

Carlos: Very cool. We’ll actually put a link to the disk stall script up on the website sequeldatapartners.com/podcast. They will have a link to Heraflux and to that script, so you can get to that on the show notes or that page.

David: Awesome! There’s two scripts out there actually, there is one for the disk stall collector and there is another one for how to set up PerfMon to collect data in the same way that SQL Server is doing.

Carlos: Very cool. Well, David thanks for that conversation. Always interesting to talk with smart folks like yourself, particularly about virtualization. Now we are at what I call the SQL family portion of the program.

David: Yes.[laughter]

Carlos: Here we get to talk more about you and some of your life experiences. We always like to talk about tools, find out what other people are doing. One of the things I like to ask is what is your favorite SQL tool? It could be a free tool, pay tool? Why do you like it and how do you use it?

David: Wow, good question. [laughs] Number-one tool, Glenn Berry’s diagnostics scripts over at sqlskills.com.

Carlos: There we go.

David: These are phenomenal. They provide an exceptional amount of insight into any SQL Server that you’ve got and I leverage them almost daily to be honest, because I do a lot of health checks and efficiency checks for different customers.I don’t ever want to be the IT guy that comes and sits in the corner, does the work and disappears. You have no idea what he did. I love the education side of it.

What I do is, I say, “Hey, go here, let’s download these scripts. We are going to walk through these in your environment, and we are going to add this into the infrastructure health-check. We are going to show you the methodology of how to do the health check and that way you won’t have to call me back to do this on other environments in your server farm.”

Carlos: Yeah, very cool. They can use that and as Adam Machanic says, “Only call you for the good stuff.”

David: Exactly! Exactly. I want them to call me back for other things but not something I have already shown them how to do.

Carlos: David you have had a wealth of experience working for other people and now you are here at Heraflux. You have this consultancy. Which one is the best piece of career advice you have ever received?

David: Hmm. Ooh. Hmm, good question. The best piece of career advice I have ever got was actually by my dad. My parents have been entrepreneurs, serial entrepreneurs for a long time. I have watched them jump from different things to different things and it was always, “Don’t be afraid to go out on a limb and take a calculated gamble.”When you are in college they teach you that you want to be a programmer, that is what they tell you and that’s what you know. I was a sys admin in high school and college. I got out of college with a computer science degree and said “OK, well next up logically is development.” Well, I found out I am absolutely horrible as a software developer.

[laughter]

David: I mean, I could do it but it just was not natural you know?

Carlos: Sure.

David: It took a gamble and said, “Well OK, I’m going to get out of that, I’m going to go back to what I know and go be the infrastructure guy.” I took that job and then SQL Server was a part of that and I always kind of tinkered with SQL Server.I figured I might as well learn this, it might be interesting and at the end of my first week there I realized that I really enjoy SQL Server. I had no idea that I would. I loved it and didn’t really abandon the sys admin side but definitely put it on the back burner to dive in hard and heavy on the database side of it.

From there I took a job at a performing arts center and had a lot of work with the infrastructure there and data and integratiom and reporting. It was just a blast and the gamble to go independent was fun, it’s honestly the craziest decision I have ever made in my life. I wouldn’t wish it on anybody unless you have that mindset where it’s OK to take a gamble and you’re willing to run with it.

I absolutely love being independent because I can pick the technologies that I want to learn and I want to explore and can figure out how to adapt it to business needs. It’s so much fun to be able to incorporate something new and completely change an attitude on something.

Carlos: Very cool. So tomorrow or later this afternoon we hang out and you inherit $1 million dollars, what are you going to do with it?

David: I’m going to hire five people that I know to help me to continue to build up my business.

Carlos: Hey, there you go.

David: Then I will probably take a couple of weeks to relax and then get right back to it.[laughter]

Carlos: Compañeros, if it happens we’ll make sure we get those postings out to you.[laughter]

David: I’m serious, we were joking a couple of weeks ago with the lottery thing. If that were to have hit for some oddball reason. I got about five people on the shortlist that I would love to pick up the phone and call.Hey, we started something that I feel is special, let’s continue to grow this.

Carlos: Make it happen. Our last question — if you were to have one superhero power, what would it be and why would you want it?

David: Probably time travel, if that’s a thing. I’m a closet history buff, and I would love to be able to travel back and meet Leonardo Da Vinci. He is, in my opinion, probably the most important Renaissance man-type mentality, and it’s something that I’ve been trying to do for a long time.Not just be, you know the phrase, what is it, “Mile-wide and very shallow in a lot of areas.” I want to be a mile wide and a mile deep in a lot of areas. I’ve come to terms with the fact that I can’t learn everything about everything, but I love learning a lot about a lot, and I love being a mile deep in a few areas, because at that point you know what you don’t know. It helps you to stay humble and it helps you to continue pushing.

Carlos: Well, David, thanks again for being here. I do appreciate your time and your willingness to share your knowledge.

David: Awesome, thank you. This has been a lot of fun.

Carlos: So compañeros, if you like today’s episode, I invite you to leave a comment or review on iTunes or Stitcher. This will allow others to more easily find it, so they can enjoy the program as well. We do appreciate you tuning in, and we’ll see you on the SQL trail.

Episode 30: The Best Investment You Can Make

1400Think of one big investment that will be made this year.  How will it affect you?  Perhaps there is a large server migration project or move to Azure on the books this year.  Maybe you have been given the ok to try some new feature or have been promoted and have different responsibilities.  You will make many decisions and purchase different things, but the biggest investment you should be involved in is the investment in yourself.

I am joined by Bruce Van Horn, host of the Life is a marathon podcast and the person that introduced me to the E to E ratio.  The Entertainment to Education ratio is a gauge for how much of your time is spent entertaining yourself versus how much you education yourself.  We discuss how this affects your future opportunities but also how it affects your happiness.

How do you keep yourself educated?  I would love to hear it in the comments below

Transcription: Bruce Van Horn

Carlos L. Chacon: This is the “SQL Data Partners” podcast. I am Carlos L. Chacon your host. So, this is episode 30. We are switching gears up just a little bit today.

We’re going to be talking about your E to E ratio and how it affects your opportunities, either in your current position or potentially in getting another position. You’re not sure what your E to E ratio is? Well, stick around and we’ll tell you more about it.

This has been very well received for those I’ve shared it with. However, I can’t take all the credit for it. This concept was actually introduced to me by my friend, Bruce Van Horn, who is our guest on the program today.

Bruce is the host of the podcast “Life is a Marathon,” has written several books and is a frequent speaker on life coaching, self-esteem, personal development and personal branding.

Now, he’s also the first Richmonder I’ve had on the program, so this is a real treat for me. I hail from Richmond, Virginia. I need to share the mic with such a popular fellow from my hometown.

So, Compañeros it’s good to be with you again wherever you might be. I do appreciate you tuning in.

Of course, if you like the program and want others to know about it, I ask that you leave a review of the show or leave a comment on iTunes or Stitcher so others can find out more easily. Of course, you can reach out to me on Twitter at @carloslchacon or by email at [email protected]. I know Bruce has lots of good tidbits for you today, so let’s get to it. Compañeros, welcome to the show.

Bruce Van Horn: Hey, Carlos. Thank you for having me. It’s a lot of fun to do this. Likewise, I don’t get very many Richmonders on my show.

Carlos: Right, yes.

Bruce: It’s just the way it works out.

Carlos: We’re kind of a big, small city, right? [laughs]

Bruce: Exactly. Yep.

Carlos: Great. It’s good to have you on the program.

Bruce: It’s great to be here.

Carlos: I do appreciate you taking some time, as always. E to E ratio. You introduced this at our user group. We had a combined meeting at the .NET Group and the SQL Server User Group here in town. You talked a little bit about the E to E ratio.I’ve been thinking a lot about that ever since, and talking with others about it. I wanted to have you on the show today. Tell us, what is the E to E ratio? Why do we care about it?

Bruce: We care about it because it really does affect our lives and where we go with it, and the choices that we make are important. The E to E ratio — the two Es — are entertainment versus education. Every single human being on the planet has the same 24 hours a day, seven days a week, 365…well this year, I guess it’s 366, right? [laughs]

Carlos: There we go [inaudible 2:44] a leap year this year.

Bruce: I think this is a leap year, so we get an extra 24 hours. But everybody has the same amount of time. You have time, I have time, Richard Branson has time. The difference between most people who are successful and most people who are not successful boils down to the choices that we make about how we spend our time.Are we actively pursuing a career, building a business, raising kids? You know a little bit about that. [laughs]

Carlos: That’s right.

Bruce: The choices that we get to make are really between entertainment and education. I obviously did not come up with this. [laughs] I don’t own this. I believe that I was first introduced to this by Brian Tracy. I’ve probably heard Jack Canfield talk about this as well.I’m pretty sure Brian Tracy is really the one who has coined the phrase, the E to E ratio. How do we spend our time? There are some pretty sad statistics about the number of hours the average American adult spends in front of televisions or game systems every single week.

Carlos: I found some numbers from the US Bureau of Labor. These stats are from 2010. I’m not sure if they jibe with what you’ve seen. They’re a bit dated. This sample, for whatever reason, was geared to males who had a six-year-old. I’m not exactly sure how that worked out, but…

Bruce: OK. [laughs]

Carlos: You can gauge that. This is a man who had a six-year-old child. I don’t know if they had more than one child but that was the basis of what they had. They came up with an E to E ratio of 1:42. This goes over lots of different job types and whatnot, so we get a little bit of mix in there. I’ve heard of ratios much higher than that.1:42, that equates to about two and a half hours of entertainment a day to about three minutes of education. [laughs]

Bruce: Exactly. Yeah. Of course, what we’re talking about there is personal education, not the amount of time we’re spending teaching our kids. It’s all about choices. I am going to be very quick to say that there is absolutely nothing wrong with entertainment.

Carlos: We have to have it.

Bruce: Entertainment is awesome. You’ve got to have it, you know? We’ve all watched “The Shining,” right? “All work, no play makes Jack a dull boy.”[laughter]

Bruce: It’s going to be cold and snowy here in Richmond at some point. We need a little entertainment to keep us from getting cabin fever. The problem is that a lot of people use entertainment as a comfort zone. They use it as a medicinal [laughs] substance. We come home. We’re all stressed out about work or about something else, so we plop down in front of the television, or we plop down in front of an Xbox or a PS4, and we just zone out for a while.

Carlos: James Clair talks about systems. You think about the TV room. We even call it the TV room. Everything’s situated around that. The system is built so when we walk in the room, the couch is situated in front of the TV. The remote is there. We just sit down and the system is made so we can be there for a while. Breaking out of that can be hard sometimes.

Bruce: Yeah. It can. When I send out tweets or Facebook posts or whatever, you would be surprised at the amount of pushback I get from that. “I need to watch my TV,” or whatever. You know what? Great. You get to choose how you live your life. I get to choose how I live my life. I honestly cannot tell you the last time I sat down and watched a television program. It’s been years. It has been years.I am a single dad with full custody of two boys about to be 19 and 13. We do not have cable TV in our home.

Carlos: Wow.

Bruce: We haven’t for many years. We have the Internet. They’ve got their PS4 and their Xbox. They’ve got the Netflix. They’re teenagers, so their entertainment ratio is going to be a little higher. For me, I made very conscious decisions. It actually started with me cutting out just watching the news.I used to be one of these guys who, if I was home — which was almost all the time, because I work at home — the TV would be on, and it would have been on one of the news channels. That was the first thing to go. We kept television but I at least stopped watching the news. My wife and kids would watch TV but I would choose to be doing something else in a different room.

There was a little pushback with that, you know, “Why don’t you come and spend time?” And I would do that, but I made choices to feed my brain. I started devouring self-help books like Brian Tracy, in which I encountered the E to E ratio. I listen to a lot of audiobooks, because I have a lot of free time where my brain isn’t doing much, but my hands are busy, like driving, or like running.

I like music, but I very often find myself listening to podcasts or listening to audiobooks versus running. It’s really just about the choices. What are the things that are going to be important to me? I don’t want to look back on my life and say, “I could have started that business,” or “I could have written this other book if I only had more time.”

Carlos: Sure. That’s right. Taking advantage of the open times that we have — and I don’t think either one of us are necessarily saying that you need to go back to school or spend a lot of time with a textbook — that’s not necessarily the type of education that we’re referring to. It’s just opening your world a little bit, right? We’re technology people.Most of the folks that listen to this podcast are technology people. I specialize in the SQL Server space. If you’re not willing to invest a little bit of education into yourself, then you probably should find another line of work. It’s just not going to happen for you.

Bruce: Or at least just be prepared to stay where you are, and not experience growth.

Carlos: There you go. We talked a little about missing some of those opportunities…

Bruce: I often as a life coach do a fair amount of career coaching. People will say, “I just feel stuck.” I’m stuck in my job, I’m stuck in my marriage, I’m stuck in whatever. Well, what are you doing to get unstuck? [laughs] The bottom line is not much. We’re just hoping that something else happens, but you’ve got to be proactive about it. You’ve got to do something.Even if it’s reading…for me, I would even say reading novels. I would stick that more in the education, as long as they’re not really raunchy, smutty novels.

Carlos: [laughs]

Bruce: But for me, I love the novels of Paulo Coelho.

Carlos: Sure. “The Alchemist?”

Bruce: Yeah, exactly. The Alchemist. That’s one of my favorite books. His other books, like “The Pilgrimage” or even “The Devil and Miss Prym.” They’re just great books that also tell life stories about personal development. I love all kinds of literature.

Carlos: There you go. That was probably a little more liberal than I was willing to give, although you make some good points there about other opportunities. While it is a fictional story, lots of good things that we could still put in practice by reading some of those things.

Bruce: Yeah. Reading a book, or at least listening to an audiobook versus watching the movie is so much more stimulating for your brain. I read with my son when he was going through the “Harry Potter” stage. I read all of the Harry Potter novels. I didn’t read them to him, but as he was reading them, I was reading them.We would have dialogues and then come time for the movies to come out, I was so disappointed in the movies because what I imagined in my head was so much more vivid.

Carlos: Interesting.

Bruce: Hollywood can’t actually produce a movie…

Carlos: The way your mind can.

Bruce: …that is anywhere comparable to what the human brain conceive. We come up with accents. We figure out what the characters would look like.It really does stimulate your brain in ways that can prevent brain degeneration diseases such as Alzheimer’s and things like this. It improves your memory. That’s why I’m going to say even reading books, if they’re fiction books, can be really, really healthy for you to do.

Carlos: Speaking of Harry Potter, I have to tell a quick story. I was at a conference. One of the speakers — she’s fairly well-known in our community — she was there with her kids. Her daughter was maybe 11. The speaker, for whatever reason, it was a technical topic, but he started talking about Quidditch.[laughter]

Carlos: He asked, “Does anybody not know what Quidditch is?” She raised her hand. I happened to be behind them. Her daughter looked at her, dropped her jaw, and said, “Mom!” [laughs] “You’re embarrassing me!”

Bruce: “How could you?” [laughs]

Carlos: That was kind of funny, but ultimately the idea is that you might be able to bring up a conversation with someone who has that shared connection. That’s probably the lowest denominator of additional opportunities that could come your way, is by getting to know other people.

Bruce: Create a conversation. That’s one of the reasons I don’t much care for pulp, popular fiction, but I do try to read most of the books that are on the “New York Times” best seller list in fiction, for that specific reason.I may be sitting in an airport. I may be sitting in a doctor’s office, and somebody might have that book, and I can strike a conversation with them about it. It’s just a great way to expand your mind. Expanding your mind is how you expand your horizons.

Carlos: Who knows where those things will take them? Another story I’m thinking about is, Kevin Kline is another well-known speaker/author in our space. I happened to be visiting with him.He was giving all this training. I stopped and I said, “Kevin, how in the world did you become so knowledgeable about all of this? It boggles my mind. I never think I’ll get there.”

He did talk about sacrificing some of his entertainment options in lieu of education. As we mentioned, one of the things that came out of that…we’re not saying you have to study all the time. Kevin, who now works for SQL Century, had a very popular one on Twitter this last summer.

It was called SQL Vacation. He actually took his family, they hit a couple of cities and he would give speeches or demonstrations of technical topics along the way. He was able to make a combination of vacation and work all together. They made t-shirts up. It was kind of fun.

Bruce: I haven’t made t-shirts, but I often take my boys with me while I’m out speaking. I went to the Caribbean. I was the life coaching guest speaker for a seven-day Caribbean cruise back in February of ’15. I’m going to do it this coming February again. Took my boys with me. It was just fun.

Carlos: There you go. We’ve talked a little bit about time. We mentioned podcasts. Obviously, we’re both hosts of podcasts. When you’re in your car in your commute, I think is probably the first place that you could identify where you might change some behavior there if you’re just listening to the radio all the time.

Bruce: Absolutely. Yes. When I’m driving, if I’m taking the boys to or from, I often don’t listen to audiobooks or things like that when I’m in the car with my boys. It’s usually after I’ve dropped them off and I’m on my way back home.[laughter]

Bruce: I get a little bit more windshield time than a lot of dads do, because my 19-year-old actually has a girlfriend who lives in Blacksburg.

Carlos: Oh, wow.

Bruce: We meet halfway in Lexington. We do that, not every once a month, but maybe once a month and a half. There’s plenty of other times. I get out and I exercise. I walk. I run.If I’m alone in the kitchen cooking or doing the dishes, cleaning up, I’ve usually got my ear buds in, and I’m not listening to music. I’m either listening to an audiobook or a podcast. There’s lots of time that you could do this.

Carlos: Let’s talk a little bit about some of those opportunities that then become available once you start to take advantage of some of these things. We talked a little bit about networking and getting to know people. You mentioned the doctor’s office. How did you make the transition into speaking, and having people want to come and listen to you?

Bruce: For me, it came to me rather than I came to it. I do have a fairly extensive public speaking background. I do sling a little teeny tiny bit of code right now.

Carlos: [laughs]

Bruce: I was on the ground floor of the dot-com boom in the late ’90s. I actually was a beta tester for the ColdFusion Web application development language. I knew the Allaire brothers really, really well. As their business grew — long before ASP was available, long before there was anything like PHP — ColdFusion was it, for a long time. A lot of the big companies wanted to adopt ColdFusion as their platform for middleware to talk to SQL Server Oracle databases.While I had a vast amount of experience with that, I’m also an English/Creative Writing major and did a lot of public speaking, so my communication skills are really good. I wound up going to work for Allaire in their training department.

In the early days, there were only five of us who were Allaire ColdFusion certified trainers. When there’s only five of you, you’re in pretty high demand. I’ve traveled all over the world teaching ColdFusion. Public speaking has always come very easily to me. I was also a musician for a very, very long time.

Being in front of people, I’m not shy. Basically as I started to make the transition out of software development into life coaching, public speaking, a lot of it had to do with some of the difficult situations I’ve been through in my life. I’ve experienced the death of a daughter. I’m a survivor of stage-four cancer.

As people have seen how I’ve responded to these types of situations, I’ve been invited to come and talk about them. It really has just sort of been a grass-root’s effort. I didn’t at any point wake up one morning and say, “You know what? I’m going to be a public speaker.”

Carlos: [laughter]

Bruce: It just sort of happened. I accepted invitations, and it grew from there. Now it is what I do.

Carlos Chacon: Sure. I think that’s a great point in the sense of we want to educate ourselves we become more familiar with a variety of different things and then as opportunities come our way we may not be as comfortable with them but if we’re willing to kind of raise our hand and say, “Sure I’ll give that a shot.” You never know what will happen.

Bruce: Exactly. You’ve got to just be willing to try it. I’ve not been afraid to make mistakes. It’s really what I think it comes down to. At least to step out of my comfort zone, because nothing good happens in your comfort zone. [laughs]

Carlos: It’s more the same, right?

Bruce: Exactly. Everything that you want in life is outside of your comfort zone. You’ve got to go get it. You are married at some point you had to step out of your comfort zone and ask her out on a date, right?[laughter]

Carlos: There you go.

Bruce: I am divorced. At some point I had to step out of my comfort and make that decision. Likewise, life changes happen as we are willing to look for opportunities and really just sense whether or not that would be something that would just be fun.For me, public speaking is just fun. There are probably some things that may come my way and I would say, “No. I don’t think I’d enjoy that.”

[laughter]

Bruce: The answer is just going to be “no” there.

Carlos: Very good. Compañeros, we are still early in the year, the new year of 2016. If you haven’t made that decision to try to educate yourself more this year, I hope that you’ll do that. Of course you are on the right track if you’re listening to this podcast.I’m sure you’re listening to others as well. Lots of other opportunities will come your way and hopefully we might see each other on the SQL trail. I would like to switch the gears just for a moment. We’ve come to the portion of the program I like to call the “SQL Family” portion.

We want to talk to you Bruce a little bit more, get to know you better about how you work and some of the things that you do. One of the things that we would like to talk about is tools.

I know we’re going to be talking about SQL tools for coding. In your profession as a pod-caster, as a speaker, as a professional coach. What are some of the tools that you like, how do you use it and why do you like it?

Bruce: For me, I would probably have to say the tool that I use the most is Evernote, simply because I’m an idea guy. I will literally wake up in the middle of the night having dreamed about something that I think to myself, “Oh, that’d be a great podcast topic,” but I know myself.I will have completely forgotten it in the morning. I will roll over. My iPhone is my alarm clock, so it is next to my bed. I’ll fire up Evernote blurry-eyed because I’m not going to put my glasses on, which I need. But I will type something to jog my memory.

If I’m out driving I will fire up Evernote and make notes. I use my iPhone a lot and I love the dictation feature in the keypads. You just tap the mic and then speak to it rather than trying to type while I’m driving. I make a lot of notes.

The reason that I like Evernote is that it syncs across all of the platforms. I have an iPad, I have a laptop or my desktop, so it doesn’t really matter to me where I’m working.

Carlos: There you go. As someone who has drank the Microsoft Kool-Aid. [chuckles]

Carlos: I’ve used the OneNote a little more often. [laughs]

Bruce: Yeah. It’s the same idea. It’s the same idea, just being able to sync across platforms. For me, I’m still the Microsoft Kool-Aid guy when it comes to the computers but for phones I’m Apple all the way.I’ve got the iPhone. I’ve got the iPad, but I’m running a Dell, a Dell laptop. I haven’t switched over to the Mac.

Carlos: There you go.

Bruce: Who knows? I would say that Evernote is probably my biggest productivity tool because it allows me…I’m constantly taking notes. Very often in my life coaching business I’ll keep notes about the life coaching sessions within Evernote. That way, if I’m out somewhere driving and one of clients calls me, then I have access to my notes wherever I am. I just like the full sync of availability of it.

Carlos: Very good. You’ve had a fairly wide variety of experiences, right? You became an English major, had some experience in writing, testing software, slinging code, now you’re a life coach.You read lots of books. Through all of that, trying to bullet that down to just one piece of advice. What’s some of the best career advice that you received and it’s helped you along the way?

Bruce: I would say the best advice that I can possibly give you is something that I had to do for myself. It’s really personal advice that flows over into every area of your life. I spent the majority of my life placing my identity as a human being into the roles that I played as a human being.I was a dad, I was a husband, I was an employee, I was an employer. I was all of these things. I was constantly striving to be the best. To be the best employee, or programmer, or boss, or the best husband, the best dad, or whatever. If there were problems in those areas, I had identity crisis.

At some point the perfect storm hits and nothing seems to be working out in any of those areas. That’s where people have a real identity crisis. That’s why a lot of people take their own lives, because they have no idea who they are.

What I had to figure out how to do was figure out how to be the best Bruce I can possibly be and everybody else benefits from that. I am a better dad than I have ever been in my life. I’m a better business owner than I’ve ever been in my life. I am not trying to be, I’m just trying to be the best Bruce that I can be.

The way I do that is by investing in myself, taking care of myself. First of all learning how to say “no” to things that completely drain me. As a dad, there are times where the kids are heaving and throwing up. You got to take care of the kids. When there’s a crisis at work…yeah, I can put in the 80 hours and get through that thing.

When that becomes the norm and it completely drains you and drains you and drains you, at some point you are over-drawn in your bank accounts. You’ve got to start doing things that make you go hard to be alive. For me it was running and it was reading books that I enjoy reading.

It was saying “yes” to the things that fill my tank, because it’s only out of a full emotional tank that you can serve other people for any sustainable period of time before you run into problems. That would be my best advice, is feed you.

Feed your brain, look for opportunities everywhere but always be growing personally. Always be doing something in the form of personal development to make you better. Whatever that is, to connect with your health, to connect with your spiritual life.

In whatever way that is, to connect with whatever it is that is the source of who and what you are. As you feed that part of you, everybody else will benefit.

Carlos: Everybody else wins.

Bruce: Everyone wins.

Carlos: Very good. Our last question Bruce. If you could have one superhero power, what would it be and why would you want it?

Bruce: I forget exactly what his real power was but I’ve been thinking about this. I’m going to have to go with the dad on The Incredibles.

Carlos: [laughs]

Carlos: OK. I was like super strength. I’d like to hear what…

Bruce: Yeah. He was super strength. He was super-fast too, not like his son was, or was it the daughter? He wasn’t stretchy woman like his wife, but I really enjoyed The Incredibles movie.That or…no, I think I’m just going to stick with that. The dad on the Incredibles.

Carlos: There you go. Being a super dad! [laughs]

Bruce: Yeah. Super dad.

Carlos: Very nice.

Bruce: While saving the world at the same time.

Carlos: [laughs]

Carlos: All in a day’s work.

Bruce: All in a day’s work. Right.

Carlos: Bruce Van Horn, thanks so much for being on the program.

Bruce: My pleasure Carlos.

Carlos: Compañeros, for more information about Bruce and we’ll have some short notes from today’s episode at sqldatapartners.com/podcast. You can go there. We’ll have information about how you can connect with Bruce, his podcast, and he even has a cruise coming up.If you’d like to have some additional life advice from him, life coaching, that will be available for you. As always, thanks for tuning in, we do appreciate it. We hope that you’ll have a great year and we’ll see you on the SQL trail.

Episode 29: Are You Thinking What SQL Server is Thinking?

Podcast Discussion with Brent Ozar

1400We each have our way of going about things–this is what makes us unique.  When it comes to getting data out of the database, many times we might think that SQL Server would go about getting data the same way we would.  If you think about scanning a Microsoft Excel document, how would you find the record you are looking for?  How does that differ from SQL Server?

My guest for this episode is Brent Ozar and we chat about internals and how SQL Server processes your request and what you need to consider as SQL Server returns your result.

Transcription: Brent Ozar

Carlos L. Chacon: This is the “SQL Data Partners” podcast, and my name is Carlos L. Chacon, your host. For this episode, Brent Ozar has agreed to come and chat with us, compañeros, about some SQL Server internals.

We’re going to discuss some situations about how SQL server will handle our queries. We want to see if our thinking and SQL Server’s lines up. Brent, of course, is one of the most recognized names in the SQL Server space.

My objective today is to not embarrass myself in this conversation. Compañeros, you know I’m a knuckle-dragging Neanderthal, and I’m hoping Brent doesn’t pick up on that too much for today’s episode, but I’m sure it will be a very interesting conversation.

If you enjoy this episode, I invite you to leave a rating or a review on this podcast on iTunes or Stitcher. This will help others be able to find the program more easily so they can enjoy it as well. It is always great to have you, compañeros.

I hope you haven’t broken too many New Year’s resolutions just yet. If you have, I still love you anyway. Welcome to the show.

[pause]

Carlos: Brent. The man, the myth, the legend.

Brent Ozar: Howdy, sir. Mostly myth, yeah.

Carlos: [laughs] Welcome to the program.

Brent: Thanks, man. It’s good to be here. It’s very fun to be here.

Carlos: Yes, I do appreciate it. One of the courses that you have is entitled “How SQL Server Thinks,” and you go through this exercise of asking the participants to try and think about how they would process these queries. This exercise I thought was super helpful for myself, and how to go through that.

Brent: Oh, good.

Carlos: It was very thought provoking. I think sometimes we assume a little too much about our SQL Servers. Perhaps we’re a little narcissistic, and don’t realize all the other work that SQL Server has to be doing. One of the first elements I thought we could discuss was the size in which SQL Server works with.

Brent: One of my most favorite inspirational moments as a presenter was sitting in through a session by a Professor Edward Tufte, who tries to teach you how to teach things. One of the things he said is you’ve got to put something in people’s hands, something physical that relates to what you’re doing.I’m like, “How on earth am I going to do that with SQL Server? It’s just a bunch of numbers, bits and bytes. How does this all work?” I thought, “Well, OK. What’s the smallest unit of something that I could put in people’s hands?” It’s an 8K page. Most of what SQL Server does, does in these 8K pages. I went through Stack Overflow’s open source database.

They publish their database via Creative Commons, so any of us can use it for demos. I said, “All right, I’m going to take a simple table that people will understand — the list of users — and I’m going to put it into Excel so that it ends up looking like an 8K page.”

Then, during the course of this session, I take these pages and I say, “I want you to be the engine. I want you to think of yourselves as doing this work just the way that SQL Server does.”

It’s so funny to see people’s eyes open up and all of a sudden go, “Oh! There are rows on a page and the more columns that I add to each row, oh my gosh, that’s less rows I can fit on the page. If I want to go through and find specific rows, I don’t have a shortcut. I actually have to look at every page.”

It’s so much fun to see people start to fold these things together and instead of thinking of them as groups of rows, think of them as pages. Then, all of a sudden it opens this huge door for people. I see all of these lights start to open up in their eyes and go, “Oh, now I understand where things like logical reads come from,” for example.

It’s so fulfilling to see this switch flip all of a sudden, when people start to see these pages matter.

Carlos: Yeah, I think that was one of the big eye-openers for me was that, as we go, we — as you mentioned in Excel — might be able to say, “Oh, there’s that individual cell that I want.” But SQL Server doesn’t go through that process. It’s not a row. It’s not a column. It’s a page. It has to process all of those pages.

Brent: Without a nice, fancy non-clustered index to get you quickly to the rows that you want. Oh my gosh, I mean it’s scan city. You’re ripping through all of these pages like pages in a book. Really makes a world of difference having those printed pages.

Carlos: Let me issue a query. SQL Server reads everything from memory. We talked about those 8K pages. It has to be there in memory. If it’s not there in memory, we’ll go out to disk and say, “Hey, disk. Will you please pass me the pages?” Brings them up and then it can start to read those.I think there’s potentially still a bit of confusion around how SQL Server does that work, in that we equate the number of rows returned with the level of effort SQL Server has put in to give us those rows.

Brent: Yes! Yes.

Carlos: I had a peer recently say, “Well, I’m only trying to pull the top ten records. This query has been running for four minutes.” Right? [laughs]

Brent: Yes. When people have the physical pages in their hand, then they start to get it. I’m like, “You can’t give me the first five if you have an ORDER BY in there.” I got to go build the whole list. I got to sort the list. We’ve got to think about where we’re going to set aside all this space to work on the list. Is it going to be physical pages that you’re going to scribble somewhere?It’s fun as the presentation unfolds because you can see the developers say things like, “Well, I choose B-tree [mumbling] ,” and I can be like, “No, no! Hold up! As a human being, how are you going to accomplish this?” It starts to break down these barriers of, “Oh, I thought there was something magical happening inside the engine.”

But, no, no, no. It’s just pages. It’s just pages that SQL Server has to scribble down and find the answers to. It’s magical.

Carlos: Let’s say we have a table. The table has four columns. As you mentioned, it’s going to have just one clustered index. That’s what it has there. Just for some easy numbers — I’m not sure that it matters at this point — let’s say it has 1,000 pages.

Brent: Gotcha.

Carlos: If we pass this query, and it needs to read that…I guess we’ll just start from the basic, SELECT *. As we mentioned, SQL Server’s going to grab that and it’s going to start reading those individual pages.

Brent: Pages in order. Yeah! It’s so neat to think about how not only is it going to rip through those 1,000 pages, but unless you tell it like an ORDER BY, you don’t know which index it’s going to use, if there’s multiples. It could use any one that satisfies your query. You don’t know whether those pages are going to be in disk or on memory, and it doesn’t really matter.People used to think, “Oh, I got to tune so that I read less stuff from disk.” Just read less pages, regardless of where they’re at. All of a sudden, your query is going to start performing better. I always want to have this giant asterisk over my head when I say that. There are edge case scenarios where reading less pages will actually make the query perform worse.

But I try to give people general advice in the start of this, that as long as you follow basic rules and go from reading 1,000 pages down to just one or two pages, all of a sudden this thing really starts to perform. There’s less locking and blocking going on, too.

Say that you needed a lock on those 1,000 pages while you’re getting your work done, due to there’s an update involved in a query or something like that. If I can get you to touch less pages, it’s going to clear out this table for concurrency. Suddenly hundreds of users can hammer at it exactly the same time and improve performance. Makes a huge difference.

Carlos: You mentioned sorting. Let’s talk a little bit about that. Before we have that sort, we’re just reading those pages. It doesn’t matter how many columns we chose. We have one clustered index. We’re reading all the 1,000 pages no matter what we do. Now we have a sort. Now we’ve modified what SQL Server has to do. We’ve involved another database in the process, right?

Brent: The classic example of this is the white pages in the phone book. Say this is the thing that was sitting up on your grandfather’s fridge. Nobody uses these anymore, but it was the white pages ordered by last name and first name. What if I told you, “Go get me everybody in the city ordered by first name.” First, you’re going to have to read through the who entire phone book.Then, you’re going to be scribbling people down as you go, because none of us are smart enough to remember all the thousands or millions of people that are in the phone book. So we are going to be writing them all down, sorting them by first name. SQL Server doesn’t cache any of that. It goes through and scribbles it all every time, either in memory or in TempDB, SQL Server’s working staging area.

It’s going to do this whole sorting thing, give you your answers, and then throw that work away. It’s never going to reuse it. If you do the same query five times in a row or a hundred people are doing it simultaneously, all this work happens for every single session.

It’s so amazing to pull the hood off of that one too and say to people, “I want you to go build this entire query yourself using just pens and paper, and sort all this data. Now I want you to go do it again and again.” I used to think as I was a developer, “Well, it’s in cache. SQL Server just did this, so I should be able to just query it again and get it.” If we’re doing any kind of sorting, whammo!

We’re going to redo all of that CPU work every single time. People often say, “Well, I don’t have an ORDER BY in my query. But I am joining a couple of tables together.” Ouch! Guess what? You’re going to be sorting those in order to join them together. We end up having this implicit ORDER BY even when we don’t have ORDER BY in the query.

SQL Server only caches those raw data pages, not the joined-together pages from multiple tables or any of our SORT BY outputs. Crazy! Often when I’m dealing with developers, they’ll say, “Well, I could build a better database than that.” That’s how open source gets started.

You can go through and look at MySQL, Postgres. There’s a lot of tools out there. There are better databases, quote unquote. I’m going to use that term really loosely — “better databases” — where they have those kinds of options, but a lot of this is, you’ve got to put it together yourself. There’s advanced switches and knobs you have to throw.

I find that even in a database like SQL Server, where it has a lot of really good defaults, there’s a lot of things in there that just work magically by default without having to throw switches. We don’t even know the switches that we need to flip inside SQL Server, let alone when you start jumping into open source, and there’s all kinds of switches that you have to flip.

Carlos: It takes a lot more know-how.

Brent: Savvy. Yeah.

Carlos: That’s right, savvy, to do that.

Brent: I think, to some extent, developers are qualified to do it. You see a lot of the Facebooks, the Twitters, the big huge sites, where there’s teams of developers that run their own databases internally or use open source databases and they know it really well. They’re able to wring a lot of performance out of it.Because they’re developers, they will pop open the source code of the database and go, “Oh! I see why this works this way. It will make me a better developer.” We don’t get that luxury in SQL Server. The closest we can come to popping the hood and reading source code is to go to training sessions. Go to SQL Saturdays, read books. That makes you a much better developer, just knowing how the internals work.

Carlos: There you go. Talking about those switches. For some reason, that scene from “Apollo 13” where they’re in space. He’s put that little post-it note. This switch is the one that he needs to flip.

Brent: [laughs] Yes.

Carlos: Getting those just right. You get the wrong one, and who knows where you’ll be, right?

Brent: Yes. It’s dangerous. There’s so many switches around there that we can just go and flip. They cause performance to go horribly awry. Priority boost sounds fantastic. Turns out, it’s not.

Carlos: Even some of the trace flags that are available, they get thrown around like, “Oh! You can use it for this one specific very purpose,” and then all the system gets affected by it, and you’re like, “Well, that’s not exactly what I was hoping.”

Brent: How did this work?Usually I’ll tell people, whenever you’re going to throw a switch, tell me why you shouldn’t. Give me the explanation of what will break when you throw this switch. If they go, “Oh, nothing. It’s just going to work.” All right, then you don’t know the switch well enough to flip it yet. You have to go research and find out ways where this breaks things before you’re qualified to go turn that switch on.

I use that rule for myself. I don’t want to sound like I know everything. Whenever I see something that looks like a really good fix, I’m like, “All right, well it’s probably too good to be true. Let me go find out what’s really going on inside this thing.”

Carlos: I was presenting at the .NET Group here in Richmond, VA. We were doing a little T-SQL review. We started talking about sorting, how painful this can be for SQL Server. I tossed out the suggestion that instead of sorting in SQL Server, that they consider trying to sort some of those things in the application. Easier to scale, all those kinds of things. I thought they were going to run me out of the building. Like…[laughter]

Carlos: We need this data! We need it sorted in our application. Anyway, we started talking a little bit about sliding windows. They were like, “We don’t need all million records. I only want the first hundred,” blah blah blah. Then we got into windowing functions. Interestingly enough, I did some quick testing. I guess I should say, I have seen queries improve by rewriting them using a windowing function.

Brent: Sure!

Carlos: Well let me just take a peek at what that sorting would do using a Windows function. I had two queries. The first one was SELECT Name, and I had an ORDER BY. The second one was using a Windows function to do basically the same thing. The weird thing was, my sort operation, the cost, was actually the same for both queries. I thought, “Hmm. That’s kind of strange.”Then it dawned on me. In the first select with ORDER BY, I was only using a single column. I changed that to a SELECT * ORDER BY.

Brent: Boom!

Carlos: Then, boom![laughter]

Carlos: That cost went through the roof. I thought, “Oh, OK.” I guess the windowing function had forced me to be very specific in what I was bringing back, whereas [inaudible 14:41] we just start with, “OK, let me see what’s in there,” and we start building our queries with SELECT *, things like that. Thinking about what we need to bring back is also important.

Brent: Oh, man. I have this session, “Watch Brent Tune Queries.” You can see the whole thing if you go to brentozar.com/go/tunequeries. It’s an hour, hour and a half long. I think the recorded version’s from Sweden or something. One of the things that I say is, when someone brings me a query that’s not performing well, there’s two things that I do just as soon as they hand it to me.The first thing that I’ll do is comment out the ORDER BY. The second thing I’ll do is, out of everything they have in the select statement, I’ll take all of it out and just return the number one. Select one from, and then whatever the tables are. Just with those two changes, I’ll look and see what the difference is before and after.

If all of a sudden we’re across the finish line, whatever it is they wanted in terms of speed, I’m not going to hand this back to him and say, “We’re done.”

What I’m going to say is, “All right. First off, tell me, are you sure you need it ordered in the database?” Because now, all of a sudden, you’ve seen the difference here. And, “Do you need every one of those fields?” Or just, “Tell me the exact ones that you can’t live without, the stuff that’s really important.”

You’d be surprised how often all of a sudden, people are like, “Oh, wow! I can’t believe it was that different. All right, let me go back to the drawing board and think a little bit about what I exactly need out of the database.” You nailed it when you say, often people think, “Screw the database! I need this stuff out of the database.”

When I explain to people, “All right, enterprise edition. US $7,000 per core, standard edition US $2,000 per core,” I can’t scale that up easily. It’s really expensive to throw CPU power at it. That’s what sorting is, essentially, is all CPU.

But as a DBA, I’ll fight for you to get more application servers and more powerful application servers instead of being stuck on these crappy two vCPU VMs with two gigs of RAM. I’ll go to bat and make sure that you get the nice beefy application servers that you want. Then we can both be happy together, because we’ll spend less on licensing.

Then I can turn around and give you nice big awesome application server boxes. Then all of a sudden, they realize that we’re in the same fight together, and everybody wins. They’re like, “Oh, OK. It turns out this database stuff isn’t so bad.”

Carlos: To that point, knowing what we want, or what we need in our queries, we may only be looking at two columns, but if we tell SQL Server we want all the columns, SQL Server’s going to think, “Oh, you need all the columns,” and it’s going to go do that work to make sure that it gives that to you well, as efficient as it can. If you only need those two columns, only ask for those two.

Brent: Yeah, I used to concentrate as a DBA, I’m like, “Oh, don’t return back fields you don’t need, because it will take a long time to say those fields over the network.” That’s just the least of our problems, compared to sorting them, building that list, and writing them all down in order. The more columns that you add, the more space that it takes, the longer the sorts take.The examples that I use in “How to Think Like the Engine,” I want to say it’s a 50x cost increase by just doing an ORDER BY whenever I’ve got the SELECT * on this particular table. 50x slower queries — man, that blows! Just picking the fields that you want, suddenly it drops down to like 45x instead of 50. Drops down to 5x, but yeah.

Carlos: Now that we have the queries that we want, we can then start thinking about indexes potentially, right?

Brent: Yeah.

Carlos: Scaling that even further, because we’ve narrowed down here’s what we need, we could potentially presort some of that in our index. Then we’re off to the races.

Brent: Yeah, I want to give my developers a prebaked copy of whatever it is that they want. I want it prebaked, presorted in the database already so that when they ask for it, SQL Server’s only looking at a few pages. It’s able to render those results very quickly. Prebaked for databases doesn’t mean I need to persist the whole data set permanently and refresh it every half hour or anything like that.Indexes are just a lightweight copy of the table. Of course, they’re as lightweight as you design them. You can make ugly indexes if you want, as well. But I like to think of it back in the phone book thing again. When you want to find all the dry cleaners in the phone book, you really don’t want to have to go through the white pages looking at every single one of them, seeing if it has dry cleaners in the name.

This is when you come up with the yellow pages in the phone book where it’s organized by business type. You seek directly to the dry cleaners, and you are out of there. I can give my developers lots of indexes that match up with exactly the way that they’re going to query the table.

Of course, the more indexes that you add, every time someone enters or leaves your city, that’s additional work that you are going to have to deal with in maintaining your indexes. I like to tell people, aim for five or less indexes per table with five or less fields per index. Nothing magical about those numbers. It’s just that I have two hands, each with five fingers on them.

It tends to make them move pretty easily, and people will remember those numbers. You can certainly go beyond five indexes at Stack Overflow. We’ve got some tables that are 30, 40, 50 indexes on them, but it’s because the database servers has boatloads of RAM, they are all solid state drives underneath, the developers take querying very seriously.

The more you know about the rules, the more you can push the boundaries of those rules, but for starters, just aim for five or less indexes per table, five or less fields per index.

Carlos: Maybe before, I was a little more hesitant because it’s going to take more write space and whatnot. Then there’s the care and feeding. But now I guess I lean a little bit more towards, if I can identify a good need for it or there is this query that, “Hey, we need this to run much faster,” then I’m all for creating that index, but I also know that I have good care and feeding mechanisms for my indexes.It’s not just create it and forget about it. I use all those scripts and whatnot, because I am looking at that. Then I am going to take a peek at that logging to see, “OK, how many times are we rebuilding these indexes and things? Do I need to make a change based on this care and feeding?” That’s why I lean that way, but it’s because I’m making an effort into watching those indexes.

Brent: It also makes a difference if the application’s constantly changing, if my developers are constantly adding, removing or changing queries. We build a screen in the app that we think everybody is going to use and it turns out nobody uses it. You have to go back and look to see how often is the index actually getting used by queries, because over time, some or parts of your app are no longer interesting to people.So it’s a matter of shedding those indexes that weren’t as important, and then building new ones that match better closely to your today’s usage patterns of the application. I am all for adding indexes, because it’s a lot safer in most cases than changing code. Who wants to change code?

You have to go through testing, QA, deployments, all that. If I can give you a couple, few indexes and all of a sudden the users are happy, everybody wins. That’s a total good thing. In a perfect world, would I like everybody to handcraft every query? Sure, but nobody has time for that. We all want to ship products so that we can go out and get paid. That’s really what’s it’s all about at the end of the day.

Carlos: In order to use those indexes to the best of our ability, you talk something about sargable. So if your index be sargable, and I am not sure that we’ll have a ton of time to get into that, but you have a blog post that we will put it in the show notes for today that will point people to what makes basically good filter criteria. I guess there are one or two that we should talk about.

Brent: I like to think of it in terms of the white pages of the phone book again. If I say, “Go find me all the people whose last name starts with Smith,” you’re going to jump straight to the Smith section, you are going to read it until the names no longer matches Smith, and then you are out of there.If I say, “Tell me everybody whose uppercased last name is like Smith, you and I would think, we still jump to the Smiths, read them out and keep going. SQL Server is not like that. There’s this whole problem with collations. Some collations will work, some collations will not. SQL Server has to do this lowest common denominator thing, build an execution plan that’s going to work for every collation.

It will say, “All right, I’m going to uppercase every single last name in the database and then check to see if it matches Smith.” Even if my database isn’t the case sensitive collation, SQL server is going to go through all of that work. What’s worse is, remember it only caches raw data pages, not output. It’s going to go through and uppercase everybody’s last name every single time you run the query.

That’s a hardcore extreme example, whole upper thing. Most of us don’t uppercase the query results anymore. We say, “Go find something that matches this,” but anything with LTRIM or RTRIM often functions on dates. Any kinds of cast or convert, you’re rolling the dice there as to whether or not SQL Server is going to run it every single time.

That means whenever you have a search argument that’s like that…whenever you’re saying, for example, where uppercase of last name or where the RTRIM, LTRIM of last name equals something, that means to SQL Server, “Well, I can’t efficiently use an index with these search arguments. These search arguments won’t let me use an index effectively,” so this is where this term sargable comes in.

SARG is “Search Arguments.” S-A-R-G. It’s one of those made up of consultant-y words like “performant” or “compressive,” but it basically means that we can write better queries if we know what’s sargable and what isn’t. That’s where that blog post comes in. It’s one of those things where as soon as you see this in action once, all of a sudden you go, “Oh! I never need to learn this lesson again.”

This explains something very simple on SQL Server. Now, don’t use the month function or the year function on dates if you are trying to search for things, for example, or in your joins. Then all of a sudden again, performance goes through the roof, just with that one simple tip.

Carlos: Let’s transition to what I call the “SQL family portion” of the program.

Brent: Oh my goodness![laughter]

Carlos: Here we get to hear a little bit more about you and some of your experiences and…

Brent: Oh boy.

Carlos: One of the questions I like to ask people is what their favorite SQL tool is.

Brent: Aside from the stuff that I write, obviously, because I’m going to put that aside, I use that every day. That’s cool. I’m not going to pimp my stuff here. The number one thing that’s helped me for more than a decade is Adam Machanic’s sp_whoisactive.I’m sure that a bunch of people say this every time that they come in, because it’s an awesomely powerful tool, but it’s one of those things that it blows me away isn’t included inside SQL Server, because it’s just so simple. Everyone needs to know what is going on on the SQL Server right now.

If you use that tool and know just a couple of commands, get_locks is an interesting parameter, get_plans, get_task_info. You don’t have to know all of the parameters by any means, but just those will help you get an amazing start — get_plans, get_locks, get_task_info. You can start to really get a feel for what’s going on inside your database.

Carlos: Yes, that’s a very cool tool. Adam was nice enough to be on the show earlier and talked a little bit about that. Actually, my takeaway from that conversation was getting involved a little bit quicker if you are in the development process. Taking a look at that at some of the lower stages so you get a baseline.Right? Who would have thought of what it is that you’re expecting so that you can feel more confident with the query as you move forward.

Brent: You know, I have one of the most duct tape ways of using it that I’ll show developers. I’m like, “All right, when you think it’s slow, run it five times in a row. Don’t even digest. Just hit F5 five times in a row and look to see what’s happening.”If it’s the same queries that are frozen up continuously, or even they’re just showing up all the time, it’s time to go look to see what those queries are doing and why they are called so often. Before you run a profiler trace, just bang F5 a few times on sp_whoisactive, and see what keeps showing up.

So often, I’ve had developers say, “Oh, I didn’t know that query ran so often. Where’s that coming from?” And all of a sudden they find an N+1 bug or something.

Carlos: You’ve been around the block, right? Many different scenarios have a very successful consulting practice now…

Brent: Oh, thanks.

Carlos: In putting that all together, what’s one of the best pieces of career advice that you received along the way?

Brent: Oh, man. When I was in high school and I went applying for colleges, somehow along the way I ended up applying to MIT, mostly for laughs.I wanted to see what the process was like. I went in for an interview. The way that they did interviews at the time is, you would go to a local graduate of MIT and interview with them. So, I go to this company that made bowling balls and I’m interviewing with one of their staff.

As we talk, near the end of the interview he said, “Do you see yourself going to MIT?” I was like, “Heck no! I am not the kind of a person who goes to MIT.”

“You know, I’m not that…” And he’s like, “Tell me more about what you mean.” Over the next course of discussion, we figured out it’s all human beings. These are just people who are normal human beings. He walked me through a list of celebrities.

He’s like, “You understand that these people all started with nothing. The only thing that got them to where they are is their drive and belief that they can go do something and accomplish something.” None of that sunk in at the time. I was like, “OK, whatever.” I did not go to MIT, needless to say, I dropped out of college.

It was years later when it came a time to go start a company. It was very weird because I never wanted to start a company. I like a salary. I like stability. I like a paycheck, to check out at a certain time and walk away and know what’s going to happen.

When I finally came time to start a company, it was a weird thought that well all right, “I’m a human being, and human beings start companies. No one is more qualified than I am to do this so, let’s give it a shot.” It’s been that looking at other people and remembering that they are just human beings who started from nothing and just ran with it, has been really weirdly empowering.

Whatever it is that you want to go do, go look at other human beings who’ve done it. Look at what their track has been, how they got along the way. They faced setbacks. They had tough times. They’re just human beings who have to buckle down and keep sucking it up and working at it. They don’t have any magical skills that you don’t have.

Carlos: Our last question for today. Brent, if you could have one superhero power, what would it be, and why would you want it?

Brent: I would really like to be able to see the future, just because there is all this uncertainty, especially when you go start a business. You know how it is when you’re…I’m like, “I have no idea what’s going to happen six months from now, a year from now, two years from now.” Sure, it’s nice to say, “Well, you can go make it anything you want to.” But life has these weird setbacks and changes.I would love to be able to know, what’s it going to be like 10 years from now? What’s it going to be like 20 years from now? It would be nice to look in the future and go, “All right, at the age of 60, I’m going to be able to be retired and I’m still going to have all my health and senses,” or “I’m going to be in a car accident at 55, so it’s time to stop, just continuously go to the beach.”

[laughter]

Carlos: Smell the roses a little bit.

Brent: Yes, that would be phenomenal. I’d really like to see that. I’m not as worried about failure or anything like that. It’s just…I want to know how much time I have left to make as much of a difference as I can.

Carlos: Very interesting. Brent, thanks again.

Brent: My pleasure! Good to be here.

Carlos: As always, compañeros, if there is something you want to hear about or you have some other question, you can reach out to me on Twitter. I am @CarlosLChacon. Or you can shoot me an email at [email protected]. We will of course have the links to the show notes today for some of the podcasts, and of course, Brent’s training class up on today’s show notes at sqldatapartners.com/podcast.Compañeros, I will see you on the SQL trail.