Automating Complacency

There’s no Latin word for Robot

I’m all for automation, especially when it comes to the boring stuff. As awesome as the automation routines we have for that are, I still see people messing it up.

Right now, there’s no Clippy/HAL mashup glaring at you, telling you that what you’re doing is a bad idea.

You wanna take one log backup a day right after your full backup? Done.
You wanna rebuild and then reorg and then update stats for every index? Say no more.
You wanna set your database to single user mode to take a full backup? Okie dokie.
You wanna stop taking log backups between 8pm and 8am? No worries.

And, hey, you wanna set all this up to run without warnings or notifications for failures, long running processes, or the dreaded TOO FAST process? I’m not your mom! G’head, slugger.

(Ask me about when a three hour import process started taking 3 seconds next time you see me.)

Maytag Man

Most of our client engagements start off with us running our free scripts to dig in on a health check.

As part of the process, we look at maintenance tasks. At a pretty good rate, we’ll see that something important is failing, with backups being the most common.

There are some common reasons for this, too:

  • Drive filled up
  • SAN guy changed a path
  • Permissions got hosed

The important thing here: no one was aware of it.

Automation is great, but it’s only as reliable as its monitoring.

Or monitors.

Set it and forget it

The trouble I find with most automated processes is that no one is checking in on them.

The automation layer, thankfully, rescues you from having to wake up every X minutes to take a log backup, stay up until midnight to take a full backup, etc. These are all noble ends.

But you do need to verify that automation is working as expected once in a while.

The first thing you want to know about are failures. This is easy enough in SQL Server by setting up job failure emails.

“Stats updates failed with something about dbo.Sort and tempdb, and we got alerts that the T drive is down to 46K of disk space.”

The second thing you want to know about is if a job has been running longer than usual.

“Say, why’s CHECKDB running for 8 hours? Usually it’s done in three.”

The third thing you want to know about is it jobs are finishing much more quickly than usual

“Did one of you forget to change that PRINT to an EXEC in dev?”

Which brings us to performance

I still haven’t gotten much of an answer to “what if performance was never good?”

SQL Server’s missing index requests are plain daffy sometimes. Without looking at the source code, I’m willing to wager that those (or some DTA-ish mechanism) is behind the A/B testing that goes on in Azure.

The automated tuning mechanism in general will only give you the best of all potentially bad plans.

Batch mode memory grant feedback will give up if it can’t find a middle ground (and currently requires ColumnStore to work)

Batch mode adaptive joins require… well, a join. You don’t even need one of those to make parameter sniffing happen.

I look forward to the day when there’s a process that will explore neat indexing tricks, query rewrites, temp tables, computed columns, and expanded SARGability, but that’s a long way off.

If you told me I could close SSMS today because there were no problems left to solve, I’d happily go do something else.

It’s not me, it’s you

When people talk about <insert role here> being dead, they often mean that the kind of <insert role here> they were is dead.

It says more about them than it does about you and the job you do.

Previous Post
The Perils Of VSS Snaps
Next Post
[Video] Office Hours 2018/1/3 (With Transcriptions)

11 Comments. Leave new

  • The usual assortment of monitoring tools alert when a job run has an abnormal duration, though I still find RYO checks to be superior in some cases. However I’d really like to see SQL Agent shown some love from MS. It’s the only component of the SQL Server family that I really like, other than the database engine and occasionally SSAS. A refresh of some sort to iron out the bugs and bring it up to the same scratch as SQL Server itself would be welcomed and could easily provide task performance monitoring functionality. It’s pretty much the only thing I ever use Object Explorer for. You really get a feel for just how good it is when you find yourself having to use (or investigate a failure in) Task Scheduler, but it needs a bump to take it to the next level.

  • I suggest that your monitoring routines include a notice that you get at least daily, whether any exceptions occurred or not.
    I’ve seen cases where the exception reporting itself was broken, but the initial attitude was “no news is good news.”

    It was not good news.

    • Exactly!!! I have worked for too many managers who were over the top with automatic set it and forget it tools and never heard of the word proactive all for the reason that us dba’s could do ‘other’ (translating to ‘more’) things rather than ensuring everything is ok. I would rather have an oil pressure gauge I can glance at occasionally than a warning light that may fall prey to a burned out bulb, broken wire, bad sender, blown fuse etc. If the oil gauge shows zero I can determine if it’s a bad gauge, line, sender, etc. and have some peace of mind or actually an engine problem itself.

  • Erik,

    That last line hit too far close to home. You’re not wrong though.

  • Collin Peterson
    January 5, 2018 2:30 pm

    While I agree with your last two sentences, there seems to be a dividing of the DBA’s responsibilities. Some of the responsibilities have gone to the sys admin, others have gone to data analyst/BI individual, and some have gone to the database developer. It’s not so much that the DBA is “dead” as it is he/she is being force to limit his/her domain. I hate that I have to be pigeon holed into a subset of the job responsibilities I have come to enjoy. For example, in the past, I have had the role of BI Analyst. I hated it because I didn’t get to work with hardware, servers, storage, and set up like a DBA is expected to. The same is true for the other positions as well.

  • When people say the DBA is dead, I think they are saying that the role of the DBA today is not what it used to be. You need only look at the history of Microsoft certifications for SQL; over the years we have had the SQL Developer, DBA, and Business Intelligence certification tracks and now there is only one: Data Management and Analytics. If you look at the required skills for that certification you’ll see an amalgamation of all those previous certification tracks. Automation is part to blame for that, because as the applications, hardware, and work force evolved, so did our ability automate away more of our work. The cloud is also part to blame for this because now we have plug and play HADR, backups, and scripted provisioning. That gives data professionals (formerly known as DBA’s) bandwidth to be more involved with things like development, analytics, Hadoop, ETL, Power BI, etc., if they choose to of course. Bottom line is, if the task is repeatable it can be automated and all the checks and assurances that your automation is working as intended can also be automated.

    In the future, there might still be a place for DBA’s who push buttons and turn knobs and watch indicators on a screen waiting for something to fall out of tolerance, but those jobs will be few and far between and nobody will want them, not unless you are at the end of your career and are just trying to ride the wave to retirement. The workforce is changing and automation is real. Instead of debating whether or not the DBA is dead, maybe we should start a conversation about our re-branding and come up with a title that is more in-line with the modern IT data professional. IMO, of course.

    • B – you lost me as soon as you used Microsoft certification content as proof of what IT professionals do on a daily basis.

      Gonna need a better starting argument than that.

      • I wasn’t using Microsoft certification as proof of what IT professionals do on a daily basis, not even close. The point I was making is that those certifications tend to follow expectations in the work force, skills a data professional would have. We used to have more specialized roles like DBA, BI Developer, SQL Developer but I see plenty of job descriptions for DBA’s that include things like SSIS, data warehouse, SQL development, etc., skills that weren’t always in the wheelhouse of the DBA. Employers are more often asking for these additional skills, and the new Data Management and Analytics certifications reflect this. Those certs mean squat in the real world, but at least give M$ credit for understanding the needs of the modern data professional. I’m not saying the “DBA is dead”. I’m saying we’ve outgrown the title of DBA, it just doesn’t mean what it used to and it has a lot of baggage.

        • The point I was making is that those certifications tend to follow expectations in the work force, skills a data professional would have.

          Uh, no. If they did, the workforce would be crying out for folks to code XML queries off the tops of their heads. You and I are just gonna have to agree to disagree vehemently here. The certs are simply garbage and don’t reflect anything that the workforce is demanding – they’re a for-profit product that Microsoft runs to make a buck. They fleece students for what students think they need to learn, not what the market is actually demanding.

          There still isn’t even an MS exam to tune an index or tune a query, and believe me, the workforce goes bonkers for that.

          • 3,113 data professionals responded to your salary survey for 2018. Of those, 1,554 had “DBA” in their job title. Of those who identify as a DBA, 805 of them had at least one of these additional job responsibilities: Developer: App code, Developer: T-SQL, Developer: Business Intelligence, Architect, Data Scientist, or Analyst. So more than half of those who identify as a DBA have job responsibilities that are not typically associated with a DBA.

            This is the point I was trying to make, that the DBA title doesn’t mean what it used to and hiring managers and team leaders are expecting more from their DBA’s. Also, people who have worked as a DBA long enough have realized they need to build new skills to differentiate themselves from competition in the marketplace. I don’t believe database administration is dead, but I do think that DBA is a dated identity that doesn’t accurately describe what most data professionals do today.

  • Bryant McClellan
    January 8, 2018 7:12 am

    What I have never understood is the thinking that DB developers are not responsible for query performance and that it falls solely into the hands of DBAs. That is like saying the System Engineer is responsible for tuning web applications, not web developers. I’ll bet SEs would push back hard on that one.

    I am not saying that every DB developer needs to be a performance expert. But if they don’t know how to get an execution plan and make high level sense of it then they don’t know what they are doing.

    Beyond that we are beginning to embrace automated deploys from RM. If there was ever a black box that needed monitoring it is RM.


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.