Full Automatic Tuning: SQL Server 2026's Most Killer Feature

MJ-t-sql-TuesdayThis post is a response to this month's T-SQL Tuesday #100 prompt by the creator of T-SQL Tuesday himself, Adam Machanic.   T-SQL Tuesday is a way for SQL Server bloggers to share ideas about a different database or professional topic every month.

This month I'm going down the science fiction route and pretending that I'm writing about a new SQL Server feature in 2026.


Fully Automatic Tuning

Watch this week's video on YouTube

I was really excited when automatic tuning capabilities were first introduced in SQL Server 2017.  I couldn't wait to say so-long to the days where I had to spend time fixing basic, repetitive query tuning problems.

And while those first versions of automatic plan choice corrections were fine, there was a lot left to be desired...

Fortunately, Microsoft has fully leveraged its built-in R and Python services to allow for advanced automatic tuning to make the life of SQL Server DBAs and developers that much easier.

On By Default

Perhaps the coolest part of these new automatic tuning capabilities is that they are on by default.  What this means is that databases will seem to perform better right out of the box without any kind of intervention.

I think the fact that Microsoft is confident enough to enable this by default in the on-premise version of SQL Server shows how confident they are in the capabilities of these features.

Optimize For Memory and Data Skew

While the first iterations of automatic query tuning involved swapping out query plans when SQL Server found a regression in CPU performance, the new automatic plan correction is able to factor in many more elements.

For example, instead of optimizing for CPU usage, setting the new flag OPTIMIZE_FOR_MEMORY = ON  allows SQL server to minimize memory usage instead.

Also, with the addition of the new  "Optimized" cardinality estimator (so now we have "Legacy", "New", and "Optimized" -  who's in charge of naming these things???) SQL Server is able to swap out different estimators at the query level in order to generate better execution plans!

What time is it?

Another new addition to automatic plan corrections is SQL Server's ability to choose an appropriate execution plan based on historical time-of-day server usage.

So if a query is executing during a lull period on the server, SQL Server is intelligent enough to realize this and choose a plan that is more resource intensive.  This means faster query executions at the cost of a more intensive plan - but that's OK since the server isn't being fully utilized during those times anyway.

Making use of hardware sensors

As the world continues to include more data collecting sensors everywhere, SQL Server makes good use of these data points in 2026.

Tying into the server's CPU and motherboard temperature sensors, SQL Server is able to negotiate with the OS and hardware to allow for dynamic CPU overclocking based on server demands.

While this option is not turned on by default, enabling dynamic overclocking allows for SQL Server to give itself a CPU processing boost when necessary, and then dial back down to more stable levels once finished.

This obviously won't be a feature used by everybody, but users who are willing to trade off some stability for additional analytical processing performance will love this feature.

How I Stopped Worrying And Learned To Love Automatic Tuning

At the end of the day, we are our own worst enemies.  Even with the latest and greatest AI technology, we are capable of writing queries so terrible that even the smartest machine learning algorithms can't grasp.

While SQL Server's automatic tuning features work wonderfully on the boring and mundane performance problems, there are still plenty of performance problems that it leaves for us to troubleshoot.

And I love that.  Let the software optimize itself and maintain a "good enough" baseline while letting me play with the really fun performance problems.

I'm sure these features will continue to evolve - but so will we, working on new problems and facing new data challenges.

What I Do When I'm NOT Writing SQL

MJ-t-sql-TuesdayThis post is a response to this month's T-SQL Tuesday prompt created by Aaron BertrandAdam Machanic created T-SQL Tuesday as a way for SQL users to share ideas about interesting topics every month.  This month's topic is "Your passions outside of SQL Server".


Getting away

I spend most of my days in front of a computer.

While this is great because I love technology, a few times a year I like separating myself from all forms of semiconductors. I do this by going camping.

Watch this week's video on YouTube

I highly recommend watching this week's video on YouTube - I was able to include way more photos and video clips there!

Camping is a mental reset where I can focus on activities that are completely opposite of what I do every day. It involves different challenges and skill sets, and provides a literal breath of fresh air when compared to working in a typical office environment.

How did I come to enjoy sleeping on the ground and learning to survive with only 20lbs of gear? Let me describe three distinct eras in my camping evolution.

I didn't grow up camping

film-only-2 High school Bert's first camping trip in Vermont

My family never went on camping trips. I always enjoyed the outdoors, but I never learned any basic outdoors skills like you might learn in scouts.

My friends during high school came from similar upbringings. We didn't know anything about building campfires or sleeping in tents, but we wanted to learn.

So, during the summer before college we decided we'd drive up to Vermont, rent a campsite at a state park, and learn to live outdoors.

While an immensely fun trip, our skills were lacking since we basically ended up eating ramen and sleeping in our cars (because our incorrectly set up tent gushed with rainwater during the middle of the night).

We kept at it though, going back every summer, until we managed to figure out how things should work. After a few trips we were no longer eating just ramen and not needing to sleep in our cars anymore.

Have tent, will travel

My outdoor skills improved as I continued to camp through my college years; I learned to cook food over coals, stay dry during the harshest rain storms, and light fires with a single match instead of half a can of camping stove fuel.

During this time I also realized that could travel almost anywhere and have my accommodations cost less than \$20 per night PER CAMPSITE.

this-waters-too-warm-and-clear I must have been thinking "the water is too clear and warm for this New Englander"

This meant my friends (and future wife Renee) spent college spring break camping through the Everglades and the Florida Keys. We spent our days kayaking, zip lining, and eating key lime pies before returning to our ocean front campsite for the evening. I think in all that trip cost us less than \$400 per person for 9 days of fun, including gas for the minivan.

the-backpacking-view-3 Ultra cheap vacation to Yellowstone National Park

Ultra cheap vacations continued and I now graduated to cooking single pot meals and foil packet dinners. My car was still parked nearby but I wasn't having to sleep in it at night.

I even ventured back to Vermont and proposed to Renee there while camping (she said yes even after three days of no showers. That's when I knew she was a keeper).

Backpacking and gourmet food

better-than-any-hotel Everything we need, carried in and carried out.

Camping eventually evolved into backpacking - instead of having a car a few feet away with a cooking stove and cooler of cold food, now I was learning about how to go into the wilderness for days with everything I would need carried in on my back.

Backpacking is truly exciting. You get to go and see places only accessible by a long hike on foot. The trips require more planning, both in terms of hiking routes, food preparation, water scouting, wildlife management, etc.... all things that invigorate my researching, detail oriented personality.

Living out of a backpack also means that you can easily fly to locations and still have a cheap vacation. I got to see beautiful National Parks like Yellowstone, the Grand Tetons, and Shenandoah all because I knew how to fit everything I need to survive into a carry-on and personal item (except for stove fuel, bear spray, and pocket knife ...some things you just have to buy on location).

Not only is visiting these places cool to begin with, but camping overnight in them means you get to see the park early before any car driving tourists arrive and you get to stay out way longer after they all leave the park to go grab dinner.

By this point my car is parked miles away from where I am sleeping and my food game has stepped up. Although I am limited to carrying everything I need in a pack, we frequently dine on pad thai, pizza, and cinnamon rolls.

pizza After a long day spent hiking...

cinnamon-rolls ... these are the only foods I need to eat.

Who says you need to be roughing it while camping?

I Have A SQL Login - Why Can't I Connect?

tsqltuesdaylogo

This post is a response to this month's T-SQL Tuesday prompt created by Arun SirpalAdam Machanic created T-SQL Tuesday as a way for SQL users to share ideas about interesting topics. This month's topic is "Your Technical Challenges Conquered".


DBA Skills 101: SQL Logins

While writing last week's post about efficiently scripting database objects, I decided to make progress towards my 2018 learning goals by figuring out what database permissions were needed for running SQL Server Management Studio's "Generate Scripts" tool.

I thought it would be best to start with a clean slate so I created a new SQL login and database user so that I could definitively figure out which permissions are needed.

Normally I use Windows Authentication for my logins, but this time I thought "since I'm getting crazy learning new things, let me try creating a SQL Login instead."

After I created my login, I decided to test connecting to my server before digging into the permissions.  Result?

Watch this week's video on YouTube

I can't connect!

That's right, I tried to connect and I got this very detailed error message :

Login failed for user Microsoft SQL Server Error: 18456

"Great," I thought.  "I should just switch to a Windows Authentication login, those always work for me."

"BUT NO, THEN I WON'T LEARN ANYTHING!"

On to troubleshooting

First things first, I tried retyping my login and password (I know typing in the password of "password" is really tricky but I've made mistakes doing much simpler things).

No luck.  Maybe when I created the login I fat-fingered the password?

I recreated the login, making sure I precisely typed the password.  Try to connect again...nope.

Ok, ok.  I'm missing something obvious.  I have this error message though - maybe the internet will know!

I find the exact error message in a blog post by Aaron Bertrand - he's a credible guy, I bet I'll find the solution there!

Nope.

(Side note: the answer is there, just buried in the comments.  In my eager "this will be an easy solution" I didn't bother scrolling down that far).

Ok... how about books online?  Even though I created the login through the SSMS GUI, I know the T-SQL command to do the same is CREATE LOGIN.  Maybe I'll find the solution in the documentation?

No luck.

(Side note again: in hindsight you can get to the solution from the above link, but it's buried two further links deep.  While troubleshooting I was in the mindset of "ain't nobody got time for that" - I wanted a solution given to me immediately without having to do any further research!)

I kept searching online, reading through Stack Overflow answers, not finding what I needed.

(Side note (last one, I promise): anyone else having a harder time searching for relevant Stack Overflow answers?  It feels like more and more I find questions/answers that are for older versions and no longer relevant)

At this point I was really frustrated.  "CREATING A LOGIN SHOULD BE LIKE THE FIRST THING A DBA LEARNS!! WHY IS THIS SO HARD?!?!?!?!?!?!"

At that point I was tired and disappointed that I had spent more time trying to solve this login problem rather than actually figuring out the permissions that I wanted to include in my blog post.

Sleep

I decided to take a break for the night and revisit the problem the next morning.

As expected, I searched the internet for the answer again and somehow my keyword selection hit the jackpot - I found the Stack Overflow answer telling me I needed to set the server to mixed authentication mode:

mixed-authentication-mode

Wow, that was easy.

Takeaway

This wasn't a complex problem.  At least, it shouldn't have been a complex problem.

All in all I spent probably 30 minutes trying to figure it out - not the longest amount of time I've sunk into a problem that ended up having a really simple solution.

However, this stuff happens.  It's amazing what a fresh (rested) set of eyes can do for solving a problem.

Lesson learned: next time I'm getting frustrated by a problem that I think should be easy to solve, I need to step away from the computer and come back once I have a clearer mindset :).

One Last Technical Challenge (BONUS)

I figured I'd add one more technical challenge to this post: submit a pull request to the sql-docs GitHub.

My rationale was that I couldn't be the only person to have ever been stumped by authentication modes.  Maybe I could be helpful to the next person who visits the CREATE LOGIN books online page and give them a hint as to why they can't connect.

Contributing to open source isn't something I've done through Github before, but luckily I had Steve Jones's excellent write up to guide me.

There were no real challenges here since I was just making a simple edit, and low and behold a few days later my PR got merged and is now live in BOL - cool!

Learning Through Blogging and Speaking

55ae8-1lh0mvkliatliiikt0vlyow

This post is a response to this month's T-SQL Tuesday prompt created by Mala Mahadevan. T-SQL Tuesday was created by Adam Machanic and is a way for SQL users to share ideas about interesting topics. This month's topic is Setting Learning Goals for 2018.


Watch this week's video on YouTube

What do I want to learn in 2018?

Next year I'm going to strive to learn more about tasks usually reserved for a DBA - backups, replication, user and role permissions, etc...

As a developer, I am generally isolated from the day-to-day work of a DBA.  Not that I necessarily want to be responsible for a system's backups (and restorations!), but I do feel that I am missing out on a broader understanding of SQL Server by never having to perform these tasks myself.

I like being able to do-all-of-the-things, or at least know how to do them, because I think it makes me a better developer and a better architect of solutions.  By not having an expert knowledge of things like availability groups, I feel like it hurts me when it comes time to architect solutions.

The learning process

I like to learn by doing.  Since I'm not planning on becoming a DBA anytime soon, I'll need to create my own scenarios to learn how these different DBA specific features of SQL Server work.

This won't give me real-world experience, but for the most part I am ok with that - I'm trying to learn more details on the broader concepts rather than the nitty gritty of various real-world scenarios.

The best way I've found to learn by doing in this academic sense is through blogging.  I like blogging because it forces me to learn a concept well enough to be able to articulate it back easily to my readers (hi mom!).

Blogging forces you to create demos that point out features that are critical to understanding a demo.  If I can create a demo that recreates a real-world scenario, then my understanding of that problem in the real-world will be pretty good; at least way better than if I just read a book or MSDN article.

Implementation

Blogging is all good and fun, but the best way I've found to really understand a topic is to present it.  If you can teach someone else then you have a pretty good handle on the content.

Now, I'm not aspiring to give a 500-level talk about disaster-recovery planning, but I do think I can learn enough to present some 100-level topics to SQL Server beginners who are just a few books online articles behind me.

The best part about presenting is that you will have people smarter than you in the room with you.  Or at least people who have a different perspective about the topic than you.  These people will generally ask really good questions that...you don't know the answer to!

But there's no shame in saying you don't know the answer.  It's actually a wonderful opportunity - after the presentation, you can take your time and learn the solution.  Then blog about it so others can know the answer too.  Then incorporate into your future presentations.  It's a beautiful cycle.

My Most Embarrassing SQL Moment

T-SQL Tuesday #92: Lessons Learned the Hard Way

55ae8-1lh0mvkliatliiikt0vlyow

This post is a response to this month's T-SQL Tuesday prompt. T-SQL Tuesday was created by Adam Machanic and is a way for SQL users to share ideas about interesting topics. This month's topic is Lessons Learned the Hard Way.


Watch this week's video on YouTube

"Is this your query that's killing the server?"

It was my first week on the job and I was learning to query one of our major databases.

Up until that point, my SQL experience was limited to working on a *tiny* e-commerce database. Query performance was never something I had to deal with because any query I wrote, no matter how poorly written, would always execute quickly.

This new database I was working on though had tables with a billion+ rows. I should have been more conscious about how I was writing my joins and filtering my records, but I wasn't. I wrote my query and executed it in SQL Server Management Studio.

About 20 minutes into my query's execution, I received an email from my new DBA, and it looked something like this:

Uhh, there might be a problem here

"Is this your query that's killing the server?"

Oops.

I don't think my mouse ever moved to the stop execution button as quickly as it did that moment.

I was incredibly embarrassed to have brought our production server to a crawl. I was also incredibly embarrassed to have had my first interaction with my new DBA be about a query that created major problems for him.

Although there were no long-term damages from my server-crushing query, it was a scenario that I definitely didn't want to relive again in the future.

Next time: don't do that again

Obviously, this was an experience where I learned that maybe I shouldn't write queries against unfamiliar data in production.

  • I should have been practicing on a dev database.
  • I should have looked at table meta data and made sure I understood relationships between tables better.
  • I should have done some more preliminary querying with more restrictive filters to be able to catch performance problems earlier on with smaller result sets.
  • I should have examined what indexes were available and made sure I was attempting to use them.
  • I should have used a (NOLOCK) if I absolutely had to test on the production data so that at the very least I wouldn't prevent the high transaction ETLs from modifying data in that database.

All of those "should haves" quickly became my checklist for what to do before running any query in an unfamiliar environment.

I've still written plenty of ugly and inefficient queries since then, however none of them ever caused me to bring the SQL server to a halt like I did in my first week. That was one lesson that I learned the hard way.

« Page 3 / 3