T-SQL Tuesday #12 – Why are DBA skills necessary?

t-sql tuesdayOne of the few persons I admire for their skills is Paul Randal, who’s the host of the 12th T-SQL Tuesday. T-SQL Tuesday is about blogging about the same topic, inevitably from different angles as we people are different. It’s fun for reading, I hope it’s fun for writing as well – this is my first time participating. Given my current state of mind, this is going to be a rambling, rather than an article.

The question outlined in the title can be interpreted in many ways. The typical things in my head are the following:

  • Programming: set-based, declarative approach vs sequential, procedural programming
  • Operations: RDBMS is quite different from OS and other standard apps (including server products)


Going through the items: a typical problem with application development is that most developers just can’t digest that SQL is not procedural language. It hates when you tell it how to do something. And it will punish you with amazing performance hits. Believe it or not but I really got T-SQL code like this:

DECLARE @tranid bigint, @processor int
DECLARE tranitem CURSOR FOR
SELECT transaction_id, processor
FROM transaction_history;
OPEN tranitem;
FETCH NEXT FROM tranitem INTO @tranid, @processor;
WHILE @@FETCH_STATUS = 0
   BEGIN
      IF @processor BETWEEN 31 AND 44
         UPDATE transaction_history SET processed = 1 WHERE transaction_id = @tranid;
      FETCH NEXT FROM tranitem INTO @tranid, @processor;
   END;
CLOSE tranitem;
DEALLOCATE tranitem;

Needless to say, transaction_history had 300 million lines and the column processor had an index on it. With the supplied script, we couldn’t wait for the completion. With the rewritten one, all the few thousand matching rows were updated in less than a minute. Oh, yes, this is what I wrote:

UPDATE transaction_history SET processed = 1 WHERE processor BETWEEN 31 AND 44

So a DBA is someone who can write effective T-SQL code? Sounds like a database developer… :)

Looking at the second item: why is a relational database engine so special? Well, because I like them :) Seriously, they should use a different approach in some cases than an OS or a webserver. Most obvious part is the scheduling I think. OSes usually use preemptive scheduler, that is, every process gets a time slice to run, then they should wait in the queue. With SQL Server that would be disastrous as the SQL processes would wait in the queue holding locks and it would bring down server performance. Instead, SQL Server goes for a cooperative scheduler, trying to give enough room for a process to finish completely as quick as possible. In general, SQL Server thrills to minimize lock holding time. And as a DBA, you should be good at understanding the locking mechanism, lock compatibilites and understand how online exactly is online indexing.

Also, if you’re a DBA, you should know that SQL Server is optimistic. It expects that if a transaction is started, it’ll be committed and in 99.99% of the cases, it’s right. In the remaining 0.01%, rollback is a bit more expensive than it could be if someone had optimized for it. Needles to say, you won’t restart your server if you abort a batch running for two hours and the rollback is not done after 30/60/90/120 minutes. Do you? You would wait a bit more to get back your database…

So far, the DBA is a guy who can write well-performing T-SQL code and understand the SQL Server architecture.

Let me add one more thing: they should be good at operational thinking as well – this differentiate a DBA from a developer and a Windows admin. Operational mindset is that you’re sensitive to potential pitfalls and you know how to avoid them. So if you see the T-SQL code, your only question is not if it’s good as it looks but you also ask if it’ll run fine in 25 instances in parallel. You know that things shoudl be tested first and test environment should be as mush similar to production as possible. (You know you should have a test environment, do you?) And you know when you can/should make bold moves, going with something untested.

Why are DBA skills necessary? Because otherwise you screw up your database server. Either by bringing it down and/or wasting its capacity. I’m no angel here. I was an involuntary DBA a few years ago and there was an issue with the backup partition in one of our clusters. To suspend all tranlog backups, I decided to stop the SQL Agent instead of disabling all the backup jops one by one. Smart idea, you may say. So I said SQL Agent service stop in Computer management and started to work. A few unexpected things happened in the next few minutes (e.g. I saw new backups appearing on the drive), so I checked the cluster and found that the agent started magically. Then I found that the whole cluster failed over. Later I realized that the cluster service detected that the agent was dead and did a failover. I should have used the cluster administrator or cluster.exe for this task. Since then, I’m very well aware of it.

One interesting thing is that people has a very dishonesting picture about SQL Server and what I see is that it partially comes from the fact that if you picked a SQL 7/2000 installer without any idea about all the things I wrote, you could still install and use it (and got surpised on the big transaction log). And these guys ran into serious problems – not because SQL Server was crap but because they had no idea about how it works. On the other side of the wall, if you had no experience with Oracle, no way on earth you could install one. That was a separate art. This way if you had an up and running Oracle server, there must have been someone with at least average skills around. (For the record: I’m not challenging the fact that Oracle had a better database engine at that time.)

4 Comments

  1. Sarah Dutkiewicz:

    This made me recall the days when I wore a DBA hat. It was a job I inherited when our DBA left the company for greener pastures and no one else on the team had a strong passion for databases. Ah the joys of working in a SQL 6.5/7/2000 environment… up until I left them in 2007 for my own chance at greener pastures. I went from an IT desktop support/system administrator/developer to all of those plus DBA responsibilities in a short period of time. It was in that time where I had to learn about the administrative side and not just the development side. Backup errors, agent issues, ah… yes. No longer a DBA, I finally fell back into development. But I still do some database development and my past DBA experiences (and the DBA skills!) and love of databases definitely come in handy.

  2. Erik:

    Sarah, once a DBA, always a DBA :) You must be a good developer if you did operations things. (and vice versa, a good ops guy is familiar with development!)

  3. Stephanie:

    Great point, though I know very little about SQL. My background is tech support. It is an area of interest for me. What would you suggest to learn and acquire training? Any online training? Many thanks!

  4. T-SQL Tuesday #012 : Summary of why DBA skills are necessary | Paul S. Randal:

    […] Erik Bitemo (blog): T-SQL Tuesday #12 – Why are DBA skills necessary? […]

Leave a comment