Wednesday, 12 October 2016

T-SQL Tuesday #83 - Dealing with the same problems.

Here's my rather hastily written post for T-SQL Tuesday, #83 hosted by Andy Mallon. If you aren't aware what T-SQL Tuesday is you can read all about it hereThe topic this month is "we're still dealing with the same problems". My contribution is that too many people who don't understand SQL Server make very important decisions about SQL Server.

When I read some of the other blog posts and comments on the topic I can see a common theme; people who aren't well versed in SQL (and how it works) are either responsible for looking after a very important SQL server (although it's never just the one) or they're responsible for bringing the right people in to do so.

The topic brought to mind a recent conversation I had with a CRM developer over in the States. They were a accidental DBA, the reluctant sort, and struggling to nail down a good maintenance routine on several production instances and as such they were reaching out to various forums for assistance. It soon transpired that on advice they'd received elsewhere their current maintenance program included items like daily shrink operations, twice daily heavy index rebuilds and no sight of consistency checks. We don't need to dig any deeper but its safe to assume the SQL instances weren't in the best of shape.

It's very easy to see what's going to happen here eventually, because I've seen it many times. On one hand you have someone obviously lacking the necessary skills and experience to maintain a SQL estate correctly and making some serious bad decisions along the way - problem #1.

On the other hand you have to look at the decision not to have a dedicated DBA, problem #2. The decision will often come down to cost and no understanding of what SQL Server is because it often comes with an assumption that SQL will either look after itself or you can take someone with very limited exposure to SQL and have them do it. 

Whilst accidental DBA's are relatively common and sometimes successful this type of scenario can be handled badly. The most common problem that I see is that they're not expected to be dedicated DBA's. Normally it's a case of having them check a few things here and there and then get on with the usual job, project work etc. To be frank, it doesn't work, if you have a sizable estate its going to take a sizable administration effort.

In my opinion an accidental DBA should be managed the same way as a junior one, with adequate training and mentoring. What usually happens though is the SQL estate is being covered by internet searches and it's safe to say that is very hit and miss. Take the CRM developer I mentioned earlier, someone somewhere told them that twice daily index rebuilds are the key to performance and that daily shrinks are a good idea. Dangerous stuff.

The solution is very straightforward. SQL instances need an administrator and a dedicated one at that. Junior and accidental DBA's go some way but they need someone to fulfill the role of mentor, that's where experience comes in.