A few folks have asked: will auto-tuning and adaptive query plans mean the end of performance tuning jobs for SQL Server? In this week’s episode, I talk about why I’m excited about those features rather than afraid of them. Digging into this problem, I share the #1 mindset problem I had as a DBA, why this mindset is so common among database professionals, and a daily habit that can change your approach to new technology.
Audio version
No time to watch right now or read the transcript below? Listen on the go! This is available in podcast format on iTunes , on Google Play, or plug this RSS feed into your favorite podcast app: http://dearsqldba.libsyn.com/rss
Video version
?
Transcript
Disclaimer: this is a transcript of a chatty webcast, so it’s not written like a blog post. It was created by machines and edited by a lady who was also thinking about Hekaton and decoupage at the same time, so it’s likely got misspellings, inconsistencies, and grammatical atrocities.
Welcome to Dear SQL DBA, a podcast and YouTube show for SQL Server database administrators and developers. I’m Kendra little from SQLWorkbooks.com.
This week’s question
This episode I am talking about auto-tuning, as well as the #1 mindset problem that I have had as a database administrator.
I’ve had a few folks ask me the question: you had an episode in the past, Kendra, where you talked about, “will the cloud eat my DBA job?” But, they say, I’m still concerned. Now that artificial intelligence is the buzzword for everything, and we’ve got all sorts of self tuning features in SQL Server, should I really spend my time investing and building my DBA skills? Especially as a performance tuner? Or is the SQL Server just gonna totally tune itself, so that this is wasted time.
Why are people asking this now?
The reason that this question is coming up is because we have things like the first version of an auto tuning feature in query store. which is quite interesting. You may have been hearing things about auto-tuning from the SQL Server marketing materials since SQL Server 2005.
Those of us have been around for a while have been hearing you won’t need to hire a DBA anymore– we’ve been hearing that line for quite a while now, and it’s never come true. But the fact that that message is around points out that hiring database administrators or developers who focus on performance tuning is expensive, right? That’s a recurring cost, it doesn’t come for free.
And these new features can say– okay, we’ve got query store in SQL Server, where we can configure the database to look at different query plans and gather some aggregate statistics to basically say: okay sometimes this query is fast, and sometimes it’s slower. Maybe I can try to pin the faster plan and see if it just gets faster.
There’s a lot of cool stuff in that, right? Like the idea of, maybe some of these tough problems like parameter sniffing that are really tricky to diagnose, when the procedure compiles in one way I get this plan that doesn’t work very well, and when it compiles differently I get a better plan. I’m just gonna stick with the fast one without even talking to a person. Hey that sounds really really awesome, and that can be great if the faster plan is truly the best plan.
This is a lot like coffee
You can go to a cafe. I live in Portland Oregon in the United States, and I can go to a cafe and get lovingly handcrafted coffee made by a person with a very advanced degree who has also studied many different kinds of coffee beans, and they’re operating a finely crafted machine. And they’re just making something beautiful for me.
And then I can also go to another store where the person presses a button and the coffee’s automatically made, and maybe I get the coffee really quickly. It’s automatically made and maybe it’s even a pretty darn good coffee.
Is it still as good as the coffee that is lovingly handcrafted by the highly skilled professional who can make great conversation with me, and is really funny while I’m getting the coffee?
No, it’s not as good. In some cases I’m gonna just want to take the coffee that’s cheaper. When cost is the biggest factor, maybe I don’t want to interact with the person at all, I just want a good enough coffee. I think there really is something to that.
But this is something that isn’t new if you think about it
This isn’t even specific to auto-tuning
Think about SSDs, think about the amount of memory we can put in a server now. Think about increases in just CPU power over the last five years. There’s a lot of things that– I mean we could have terrible code that’s really unoptimized, and there’s a lot of ways that with buying an expensive server that maybe we do have to replace eventually– maybe we have to replace it in five years– but it’s cheaper than hiring a DBA and paying their salary for all those five years, right?
And maybe we replace it faster than five years. It’s life cycle through our whole environment maybe is five years, it may not be in production for five years. Because these changes and improvements in what we can do keep coming faster! But the thing is, we’ve had these massive improvements in hardware, that you’d think oh maybe we don’t need performance tuning anymore.
But I don’t think it’s that our code just keeps getting worse and worse. It’s that we want to do more and more stuff!
We want to process more and more information
So at the same time that our hardware is getting faster, and the SQL Server optimizer has been getting more and more clever too, and we’ve been getting all these new features with columnstore, in different ways we can process material, all of this stuff is happening. We still– there’s more and more people who want to process data in different ways! There’s more and more data.
Yeah, cost continues to be a factor.
But the need to do things, there are so many complex choices to make out there with hardware. With which technologies to use. With what are — how are we going to deliver the data the fastest? How are we going to lay this out in our environment? How are we going to manage this the most quickly? There’s so much architecture work to do, that even with advanced auto-tuning, even as this auto-tuning becomes smarter and smarter: whether or not to turn it on, how to design the environment, and how to manage this all– there is still a lot of work to do in there! And a lot of it has to do with performance.
Also, when things go wrong, we need a human being to help make it work. And in critical situations, there’s more ways that the SQL Server is becoming more intelligent.
There’s things like adaptive query processing…
SQL Server can now start generating query plans that aren’t, “I’m just gonna do this one thing.” The query plan has some flexibility built into it. Hey if this join doesn’t look like the right join, maybe I can adapt on the second run. But you know, one of the things about this feature: there’s a similar feature to this that you’ve been able to by in Oracle in the past. And well I’ve noticed that there are still Oracle DBAs, right? They still seem to have jobs. They still seem to be doing well.
And as I think about adaptive query processing and starting to work with it more and more, I think: well when things do get slow, this is gonna be really interesting to figure out! And then, how does this play in with the other auto tuning features? Troubleshooting this and learning when to deploy this just get more and more exciting.
I don’t feel afraid of this
Yeah, it is possible that eventually auto-tuning will get to the point that it takes all of the performance tuning jobs, but I think that happens at a point where all of the jobs been taken over by artificial intelligence. It’s way down the line. It long far into the future, and hopefully as a society at that point we have figured out more about what to do when you have incredibly intelligent technology.
But that’s not just a problem for the DBAs, that is an interesting futurism problem for everyone.
For the foreseeable future in our lifetime, I really think if you’re interested in performance tuning these auto-tuning features are really exciting, and they make your job more interesting.
If you’re moving into this path as a specialist, and you want to specialize more and more, this is an area where learning how you can use this, how you can recognize it, how you can troubleshoot it, how you can configure it, this is a great opportunity.
Because we’re just starting to get this, and we’re at a point that could potentially be really exciting.
If you build your expertise in this, rather than fearing that it’s gonna take your job, it’s a way you could make your job much cooler
Even if your current company isn’t using this stuff, you can carve out some time in your week. And you gotta you got to be able to say, I’m gonna carve out some time and really use it each week, to have a sandbox and play around with this. Even if your company isn’t using SQL Server 2017 now, it’s absolutely justifiable just if you’re on the career path of DBA to say: I need to understand the new features and the latest release, so that when it could help us, when this is a feature that could make a real difference to us, I can recognize it and help point out things that could make this decision worth doing. So education is definitely a responsibility for yourself.
Don’t don’t say, ok, just because we have 2008R2 instances still, I can’t learn new stuff.
You really CAN learn new stuff. You can’t do it necessarily on that 2008R2 instance.
This is where the mindset problem comes in
When I was thinking about this question, I was thinking back to when I managed SQL 2000 instances, SQL Server 2005 instances. Back in the day I was lucky to often get to work in positions where I got to work on the latest and greatest technologies, in some case before they became released. Which, let me tell you, it has its own downsides. And cool things, too, but it’s a double-edged sword.
But I also worked with some environments where, you know, we couldn’t upgrade them. And I say “couldn’t” for political reasons, and for financial reasons.
But thinking back to my history as a DBA, I always wanted to learn performance tuning. I always had that desire. But I had a mindset that was super problematic, and and I built this mindset as part of my job. If you have this mindset, too, it is the #1 mindset problem that I would work on altering. As a database administrator we tend to develop this habit of scanning the technical world we live in for problems that may occur.
Part of why we do this is just change planning: “what could go wrong?”” is a really important thing to think about. Companies’ data is often critical to the company’s survival. Not just a matter of winning or losing a few dollars, but bad things happening can put the company out of business, when it comes to the data. So we tend to get into this mindset of being a protector, and looking for what is going to go wrong.
This mindset can be very helpful for planning changes, for saying: okay how can I mitigate that risk? We do need to in our jobs identify risk. But the problem is: this mindset can put you in a place where you don’t see opportunities in new features. Where you become just fearful.
I became a person who feared technical change
Is this gonna break my system??? Is this gonna break my job??
You become habituated to scanning the world for these things.
We need to as DBAs, not only we do have to look for risks– we do have to protect the data– but we also need to get into a mindset of looking for opportunities and possibilities, even if we can’t always act on those opportunities. Even if we aren’t going to be able to make these possibilities happen in our workplace.
I needed to change my mindset
It’s very very helpful for you, for your employer, and it also is honestly more FUN to be the person who thinks about the cool things you can do, and recognizes them. Who doesn’t feel down just because you can’t do them all, but actually just enjoys saying: hey, you know, what if we did this? what would happen then? Who is just interested. Seizes the opportunities. Plays around with stuff, comes up with clever ideas. These are the habits that are going to lead you into that area where you are specializing in the new features.
You’re either taking your current company into a technology that you helped them find, or maybe you’re finding a different job either at your current company, or at another company, where you’re doing that, too. But you’re finding those possibilities, and you’re moving into them. And this may sound just impossible, because I’ve got 99 changes to deploy, and they’ve all got problems. and it’s Friday at 4:20. Well, the way to change this: you can’t just suddenly wake up one day and be like, “I’m gonna be a person who is creative and thinks of possibilities.”
You can’t just rationally decide, hey I’m gonna change my mindset, and it’s done
But you can do it, you just have to be craftier about it. There are daily habits that you can change to change your mindset.
One of the things that I have been doing for a while, I’ve doing it for a couple months, but I’ve been doing it seriously for about twenty days: I started at the end of the day, just making a list of things that I was grateful for. Things that occurred to me for that day. When I first started I was just doing five or six things. That’s the one that I’ve been doing for longer. But then more recently, I started getting more serious about it, and I have a little booklet. There’s maybe sixteen or seventeen lines per page.
So I just spend a little bit more time doing it, it takes me about four minutes a day. I write a list of things I’m grateful for.
Now this may sound sort of like– oh that’s kind of cheesy.
It IS cheesy but it works
Yeah, it’s totally cheesy. I didn’t even realize why it worked until recently. I was watching a video that I saw I recommended on Twitter by Erin Stellato, and at the end of this video they recommend that to make employees happier and more productive, at the beginning of the day, one of the things you do is make a list of things that you’re grateful about. They point out that part of what this does for you is it puts you in a mindset of scanning the world for things that are good.
Think about it: this is what we’re talking about! Changing your mindset to look for opportunities is very similar and related to this habit of changing your mindset to scan the world and look for things that you’re grateful for. A lot of the things that I’m grateful for are things that are opportunities and possibilities. Things that excite me, things that I’m curious about, as well as things that other people have done, too.
So changing your mindset, doing a daily habit to write down something that you’re grateful for, for me at least, for me personally has really really helped me to become a person who is more open to possibility.
More interested in reaching out to it, and less fearful of change.
As a database administrator, if you are in that mindset, too, which is just super easy to get into because we need to protect the data– I think that one little habit can really change your life for the better.
TLDR;
So I don’t think auto-tuning is gonna put performance tuners out of business. I do think we’re seeing an increasing trend where if you specialized deep, there’s so many tools you can work with, and it’s so complex that there is a lot of room still for that specialist– and there is gonna be in the future.
Thanks for listening to Dear SQL DBA. I’m Kendra Little from SQLWorkbooks.com, and I’ll talk to you soon.