Tuesday, 14 March 2017

T-SQL Tuesday - The Daily (Database) WTF

This is my entry for T-SQL Tuesday, graciously hosted this month by Kennie Nybo Pontoppidan. For those among you who have no idea what T-SQL Tuesday is then you can use the following link where Kennie explains all about it and this months challenge:


So, this month we're talking the daily (database) WTF where we tell a horror story from our experiences! My WTF tale takes place quite a few years ago now when I was working as a DBA of sorts where the company that who I worked with had recently purchased some new software that was soon to be going into UAT.

Part of the solution was a SQL instance that was installed by us but was configured and developed on by an external consultant, no sorry, a SQL expert consultant that had been brought into the company. I'm actually going to take a huge chunk out of the story and go straight to the time just before testing where we called in to review the instance prior to handing over.

Firstly we encountered some things that although not too bad aren't exactly out of the ordinary; Maximum Server Memory, MAXDOP and Cost Threshold all left at their default values, that's OK, see it all the time. Looking further we found affinity masking enabled and set up and also a setting that boosts SQL Servers priority - both as we found set to (wrongly) boost performance, apparently well known best practice (?) and all that.

As I went through the instance marking down more weird and wonderful settings like obscure autogrowth and automatic asynchronous updating of statistics I overheard my colleague, who was on a conference call with said consultant and other project stakeholders saying "no, we weren't implementing daily shrinks of the databases and neither will we consider it ever"; seriously!!!

At this point we hadn't considered delving into the code but by now curiosity of what lies beneath had got the better of us and we dove in. We found levels upon levels of nested views, functions all over where clauses and an abundance of query hints!

If by now you're thinking it couldn't get worse (like we did) you'd be wrong (as we were)! Part of the consultancy period involved the consultant delivering training courses to another team on how to write optimal code and configure SQL Server. Not only was this instance becoming the pinnacle of bad practice (that I had ever seen), the bad practice was in danger of becoming contagious like a spawn of evil SQL zombies (alright, that's the horror story tie-in done!).

So to wrap up the events that followed we engaged with the consultant, worked on rectifying the incorrect settings and worked with one of the Dev guys to unpick the 7 Layers of Hades as we referred to the code base as. We also put together our own little training session and delivered to the other team (and anyone else who was interested) to help them understand SQL Server a little better (consultant never attended sadly).

Looking back the horror story was really a cluster of WTF's but for me it boiled down to someone who was in and referred to themselves as a person of technical authority but was actually incredibly misleading in their practice.

It might have been tempting to get involved in all kinds of finger pointing but actually, we decided that just wasn't our place. We identified the potential issues, resolved them and educated along the way; ourselves included it's fair to say. In truth, the consultant was very receptive to our advice and ideas; I've been in many situations since where things have been very different!

But for this WTF tale there was a positive conclusion, the project on the whole was a success and I dare say we all got something out of it in the end. Ultimately WTF moments will always happen, the key is how we react and how we learn from them.

No comments:

Post a Comment