Paul's hosting the T-SQL Tuesday theme this month and the theme revolves around DBA skills. Specifically, "why are DBA skills necessary?" His specific post is here: Invitation to participate in T-SQL Tuesday #12 – Why are DBA skills necessary?.
This is a topic near and dear to me and one that I've found myself debating with others many times. If I were to cut to the chase and try to sum it up in one simple statement I'd say that you need to know and understand any tool in order to make it work effectively. Just as with any other tool… if I were to go and buy a chainsaw (having never touched one before in my life and this is probably a very good thing) and then bring it home and try to work it without reading the instructions, very bad things would probably happen. Maybe I could get it working but if I didn’t and I tried to mess with it, it might end up very badly for me.
Don’t get me wrong, you probably :) won’t lose any limbs while working with SQL Server but inefficiencies can be created in many area if/when database developers and database architectrs don’t have some DBA skills (and vice versa IMO). And, I can think of numerous scenarios in which this has happened not only with in-house applications but also with ISV applications. With an in-house application only your internal customers have problems. This is bad enough but imagine having customers that have paid for your software only to find out that there are shortcomings that could have been avoided through better design?
For this post, I certainly won’t name names but I will list a couple of gotchas that could have minimized (potentially eliminated in some cases) application downtime or if avoided would have improved performance. And, these are the skills that a DBA would have provided…
Yes, this is an Enterprise Edition feature but many enterprises have paid for it only to find out that they can’t leverage it on some of their more critical tables… why? Because the tables were created with LOB columns defined as part of the base table. While this doesn’t seem like it’d be a big problem and many developers would tend to have all attributes within a single table (for a single entity), SQL Server has a limitation for online operations that they cannot be done if the index has a LOB column in it. While nonclustered indexes should never have LOB columns the clustered index will naturally include all columns of the table. As a result, *ANY* table that has even 1 LOB column in it cannot support a clustered index create or rebuild as an online operation. The table will have to be taken completely offline (no inserts, update, deletes OR selects – NO OPERATIONS WHATSOEVER) while the table goes through sometimes frequently necessary maintenance. So, this isn't just a one time problem – it can last for the lifetime of the design/application.
I delivered a RunAs Radio interview on indexes a while back and a comment that I’ll never forget was the following: “I have to say that I'm not a database guy. Not even a little tiny bit. But I found the show very interesting so I opened up the enterprise manager and started pokin around. (Just lookin, no touchin) Using the Query Analyzer I discovered that our main database doesn't have a single index. Apparently the devs decided that they wanted the option of putting the tables on any kind of database server so they did not include a single index or key. I was told "all that is done in 'The System'." Good grief.”This “involuntary DBA” was already starting to get some of his DBA skills even though it wasn’t really his job or goal. But, he’s already starting to learn that there are often misconceptions about how database work. And with regard to indexes, this can be very problematic. Indexes are at the center of good database performance and health. However, it’s not just the existence of indexes but knowing the right indexes. This isn’t something that’s easy to know within only a few minutes but there are tools that can help.
To get you started, here are a few links:
- For the RunAS Radio blog post (and comment J), go here: RunAs Radio Interview Posted – "Kim Tripp Indexes Everything"
- When did SQL Server stop putting indexes on Foreign Key columns? (which is another thing that often needs to be done manually and many DBAs know that FKs are often best indexed (mostly as a good starting point – indexes may need to change over time)
- Ever-increasing clustering key – the Clustered Index Debate……….again! (this is a debate that I often have as well – where and how should we choose the clustering key? And, if not specifically chosen early and chosen appropriately all sorts of problems can occur making the database larger than necessary and very hard to manage…)
- MSDN Webcast Series on a Primer to Proper Development: MSDN Webcast Series Wrap-up Resources
To be honest, even experienced DBAs often struggle with indexes. However, there are key choices that must be made early and indexes that really need to be created – in order for a database to be healthy. These are definitely things that a good DBA will know.
I think this is a debate that will go on for quite some time AND I do think that developers need some DBA skills and I think that DBAs need some developmemt skills. In some companies these job roles overlap to a point but in some companies they feel that these skills are unnecessary. In the latter, I can already sense problems in performance and scalability and even worse – potential failure.
Someone needs these skills if you want to succeed. SQL Server does NOT do everything for you. NO RDBMS DOES!!! Remember, SQL Server is a general purpose relational database management system. You can do anything with it (which is why I love it but also why so many of my answers start with… well… It depends…).
You must understand the tool and then you can/will use it properly!
Thanks for reading!