SQL Server 2016 Maintenance Plan Improvements

Let’s just get one thing out of the way first.

Yes, I understand that you, dear reader, probably hate maintenance plans. You associate them with everything dumb that you did early on in your career, like setting up a single plan that reorganized indexes, then rebuilt them, then updated their statistics. You are older and wiser now, and you swear by tools like Ola Hallengren’s maintenance scripts or Minion Reindex.

This blog post, however, is not about you.

It’s about all of the SQL Servers out there that have not yet had the wonderful opportunity to feel the tender loving hand of a qualified database administrator such as yourself. It’s about the tools that accidental DBAs will use over the years to come.

So let’s start with the index rebuild task:

Index rebuilds in SQL Server 2016

Index rebuilds in SQL Server 2016

Be still, my beating heart.

You can tell it’s kinda slapped together haphazardly – note the awkward spacing of the “Used in last” line at the bottom – but God bless ’em, Microsoft’s heart is in the right place. We have new options to only rebuild indexes if they’re a certain percent fragmented, or a certain size, or they’ve been used recently.

The goodness continues on the reorg screen:

Reorganizing indexes in SQL Server 2016

Reorganizing indexes in SQL Server 2016

Same nice options about only optimizing indexes that are in use, or are in bad shape.

The CHECKDB screen shows off its new MAXDOP capabilities, now that DBCC CHECKDB can take a hint:


Part of me is happy because undereducated database caretakers now have new, more powerful tools at their disposal.

The other part of me is a little bit sad because it’s still not easy to use. If maintenance plans are designed for the accidental and junior DBAs amongst us, I don’t think a lot of this stuff should even be an option. It should just default to the right thing, and take care of the database with Microsoft’s best practices set up as standard.

But that is a really, really small part of me. Maintenance plans are getting better, and that means something good.

What I Look For When I’m Hiring Database Professionals

Matan, Guy, and I recording the podcast

Matan, Guy, and I recording the podcast

On today’s episode of the SQL Server Radio podcast, I talk with Guy Glantser and Matan Yungman about what we look for when we’re hiring.

In the broadest sense, don’t think junior or senior:

  • I’m hiring someone for what they already know, or
  • I’m hiring someone for their capability to learn

(In reality, it’s usually a blend of both, but just think big picture for now.)

If I’m hiring you for what you already know, then I’ve got a list of skills, and I want to see your proficiency in those skills. If one of those skills includes communication, then I’m going to judge you based on how you communicate your mastery of the other skills. For example, I might be looking at your blog posts, presentations, or webcasts about the topics you’re great at.

If I’m hiring your excellent learning skills, then I want to see what you’ve been interested in learning in the past, and how you’ve gone about learning those topics. It doesn’t have to be technical, either – maybe you were interested in perfecting an Eggs Benedict recipe. Show me what resources you used, your preferred style of learning, what lessons you picked up along the way, and how you would recommend that I learn that same thing as fast as possible.

To hear more about my philosophies on that, and hear how Guy and Matan approach hiring for their own companies, check out the half-hour SQLServerRadio podcast.

My Favorite Database Disaster Stories

The statute of limitations has passed, so this week on SQL Server Radio, I get together with Guy Glantser and Matan Yungman to talk about our favorite oops moments.

I talked about my very first database disaster ever – done back when I was in my 20s and working for a photo studio, using Xenix, long before I ever thought I wanted to be a database administrator. (Yes, kids, Microsoft had their own Unix thirty years ago, and suddenly I feel really, really old. No, I wasn’t using it thirty years ago.)

This episode was so much fun because we recorded it in-person, together, gathered around a table in Tel Aviv when I was there for SQLSaturday Israel 2016. I really love talking to these guys, and I think you can hear how fun the chemistry is on the podcast.

Head on over and listen to our disaster stories, and when you’re done, check out my classic post 9 Ways to Lose Your Data.

[Video] Office Hours 2016/04/13

This week, Jessica, Richie, and Tara discuss whether you should skip SQL Server 2014 and jump to 2016, our Performance Tuning When You Can’t Fix the Queries class, and whether you should detach a database in order to drop the connections to it. (Wow!)

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Announcing Our New Pre-Con: Performance Tuning When You CAN Fix the Queries

The team resting up before tuning queries

The team resting up before tuning queries

Going to be in the Seattle area in October?

Are your users frustrated because the app is too slow, and you can change the SQL queries – but you’re not sure how? Or which ones? Or where to start?

Take heart – there’s hope. The Brent Ozar Unlimited team does this every week, and we’ll share our proven methodologies to performance tune code, execution plans, Entity Framework, and more.

This is a two-day pre conference session in Seattle on Monday-Tuesday, October 24-25, 2016. Lunch is included with your admission. (You’re on your own for breakfast and dinner.) Class is held at the Big Picture Theater in comfy seats from 9AM to 4:30PM.

You’ll meet the consultants – Angie, Brent, Doug, Erik, Richie, and Tara – for two days of learning and fun, plus t-shirts, magnetic poetry kits, and more.

To celebrate the launch, it’s $100 off – it’s one heck of a deal at just $299 for 2 solid days of training! Register now – our pre-cons usually sell out. See you in Seattle!

P.S. – there’s t-shirts, magnetic poetry kits, stickers, and swag involved.

Looking for a New Challenge? kCura is Hiring a DBA.

I’m a big kCura Relativity fan – it’s an application that really pushes SQL Server hard, written by people who are a ton of fun to work with.

If you’re looking for a challenge in a really cool environment, check out what they’re looking for:

A Production Database Administrator with a deep demonstrated knowledge of MS SQL administrative tasks and the ability to consult on design, development, and automation improvements.  Having a passion for maintaining MS SQL databases that meet or exceed internal and client contracted production SLAs for availability and performance. The right candidate will ideally have past hands-on experience administering MS SQL databases running in a public cloud environment such as AWS or MS Azure.


  • Install and configure SQL Server 2012 and higher versions. Configurations built and supported should include scenarios that include leveraging SQL Always On, windows fail over clustering, and transaction replication.
  • Document complex installation, configuration, and optimization procedures so they can be automated.
  • Provide support 24/7/365 for any troubleshooting or corrective actions related to incidents impacting application availability within the production environments.
  • Take proactive measures to monitor, trend, and tune SQL databases, such as running maintenance jobs (backups, DBCCs, apply indexes/re-indexing, etc.), to meet or exceed baseline stability and performance SLAs on large databases (1 TB+) and large volumes of databases (100+).
  • Create, implement, and maintain SQL DB Health Checks, and have a demonstrated ability to automate SQL health reporting/event notification, and corrective actions.
  • Configure SQL Server monitoring utilities to minimize false alarms, and have a demonstrated ability to monitor/trend SQL environments to determine and implement enhanced monitoring thresholds to prevent incidents and reduce mean time to recovery (MTTR).
  • When performance issues arise, determine the most effective way to increase performance including scaling up or out, server configuration changes, index/query changes, etc.
  • Identify code defects and enhancements and develop a detailed root cause analysis that can be leveraged by the product management and development teams to improve application availability and decrease the total cost of ownership.
  • Ensure databases are being backed up and can be recovered in a manner that meets all BCDR objectives for RPO and RTO.
  • Perform all database management responsibilities in Microsoft Azure for production and non-production workloads.


  • At least 4 years’ experience working as a Microsoft SQL DBA leveraging versions 2008r2 or later.
  • Experience working in a 24/7/365 operation.
  • Bachelor’s degree in computer science or information systems.
  • Familiar with basic Azure IaaS capabilities, and some experience designing and building MS SQL databases within Azure or AWS.
  • Microsoft certifications such as MCSE, MCSD, etc.
  • Experience operating in an ISO certified and/or highly regulated (SSAE, PCI, HIPPA, etc.) hosting operation.
  • Familiar with Dev/Ops concepts, and ideally experience working with a Dev/Ops team focused on implementing and enhancing continuous delivery capabilities.
  • Experience automating SQL Server deployment and configuration through PowerShell, Chef, Puppet, etc.
  • Background designing, building, and managing a search and indexing solution such as Elastic Search, Apache SOLR, etc.
  • Previous Relativity system administration experience.


(You should read that “qualifications” list as a perfect candidate, and don’t be dissuaded from applying if you’re not the perfect candidate.)

Interested? Get on over there and apply.

I’m on the RunAsRadio Podcast

Pixelated for your pleasure

Pixelated for your pleasure

It’s always fun to talk to Richard Campbell about what’s going on, and this time around, it’s SQL Server 2016:

Listen to me chat with Richard about what’s coming in vNext.

We’re now in the second quarter of the year, and we still don’t have pricing, licensing, or edition/feature lists. Normally, when you release software, you need to educate users on how to choose the right box, but to do that, you need to train the trainers first. There simply hasn’t been any community guidance available yet on the questions end users – and managers – ask the most: how is this thing gonna be licensed?

That means one of two things: either it’s gonna be exactly the same (which doesn’t seem likely, given the crazy number of new features that are going into the boxed product this time around), or it’s gonna be wildly different, and passionate discussions might still be going on.

I think this is the best release since 2005. Granted, there’s still a couple of massive problems – for example, CHECKDB simply skips In-Memory OLTP and stretch tables, so I think you’d have to be an outright idiot to deploy either of those features on data you care about. Don’t think you care about the data? Remember, if there’s any corruption in your Hekaton tables, your entire database doesn’t start up. None of your other tables are accessible, and you have to restore from your last full backup plus your transaction logs. (You were taking those, right?)

But aside from that, SQL Server 2016 is fantastic, and in the podcast with Richard, we talk about Query Store, AlwaysEncrypted, the run to the cloud, and more. Enjoy!

SQL Server 2005 support ends next week. Here’s my tribute to it.

I remember the first time I used SQL Server 2005.

Did you know SQL Server has an expiration date on the bottom of the box? Also, this one is curdled.

Did you know SQL Server has an expiration date on the bottom of the box? Also, this one is curdled.

I was a database administrator working on a new-build data warehouse project in Miami. Both our data warehouse and SQL Server 2005 were looking like they were going to come out at the same time, and I kept hoping I could make the timing work.

SQL Server 2005 looked so seductive. Let’s take just a moment to think back about all the cool new technologies it introduced:

SQL Server Management Studio – for better or worse, this user interface has been with us for over a decade. I still hear some folks pining for the old days of Query Analyzer – not me, man.

Dynamic Management Views – no more obscure DBCC commands. Now you could just query system tables and get all kinds of health information.

SMTP email – which doesn’t sound awesome until you remember that in the SQL Server 2000 days, we usually had to install Outlook on the database server just to send mail.

Database mirroring – well, technically it wasn’t supported at RTM, but you could do it with a trace flag.

VARCHAR(MAX) – goodbye, text and ntext datatypes, and say hello to Max Headroom.

Index improvementstable partitioning looked better on paper than it actually was, but include fields on indexes are a staple of tuning today.

T-SQL crowd pleasers – common table expressions (CTEs), windowing functions, ROW_NUMBER(), cross apply, outer apply, try/catch…

Business intelligence – what an incredible flourish of new stuff. SSIS replaced DTS packages, and SSAS and SSRS were huge. SQL Server Notification Services, uh, not so huge.

Service Broker and CLR – the ability to use the relational database as an app server or an asynchronous messaging queue. Myspace jumped on this. (Or is it off this?)

Looking back, 2005 was an incredible leapfrog past SQL Server 2000. Granted, a lot of that was due to the looooong gestation period – Microsoft will surely never wait 5 years to release a database update again.

But here’s the awesome part: for the last 10+ years, you’ve been able to take your database from the last version up to the new version, as-is. You’ve been able to use the same T-SQL syntax, and just add on a few new tools. You haven’t had to learn a new language (if you don’t want to), or even change any of your tooling.

If you wanted to keep your skills exactly the same from SQL 2005 to SQL 2016, you could. All of the core stuff still works the same.

It’s just faster and more powerful.

Here’s to the foundation laid by SQL Server 2005.

Erik Says:


Are you tired of trying to find a database administrator in your own home town? Tired of hearing DBAs tell you “no” over and over again? Want to meet someone exotic who will say yes to your change requests?

Or maybe you’ve tried remote DBA firms in the past, but they feel a little too cold and distant, and you want something better than phone calls.

unsplash-photo-1452475168325-672188dc4dc3-MailOrderGood news: announcing

Right now, all over the world, there are lonely database administrators waiting for you. They’d love to come work with you, but they just don’t know how to meet you. helps make these exciting connections possible.

Here’s how it works: you fill out a profile with your database’s requirements, and then we’ll arrange a trip for you to visit a series of offshore candidates that you’d never ordinarily get the chance to meet. You can try them each out, select one that works best for you, and then we’ll work together to get the necessary legal paperwork done.

Interested? Head on over to to get started.

[Video] Office Hours 2016/03/23

This week, Angie, Doug, Erik, Jessica, Richie, and Tara talk about Microsoft DPM, using database mirroring to make migrations faster, and why Doug doesn’t believe you that you can’t change your code.

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.