in Search

Do you Twitter? Follow us @CodeBetter

Eric Wise

Business & .NET

Know Your Role

Nothing is more dangerous than a programmer pushed into a DBA role who doesn't know crap about databases. Too often, you see consultancies, smaller organizations, and people in general trying to save a buck and not bring any DBA presence on board. Too often I see arrogant developers claim that "DBAs only get in the way" and "I'm not doing anything fancy, I don't need no stinking DBA". To me, this is the equivalent of someone saying "I'm building a house, and I know carpentry, so I'm not hiring a stinking plumber or electrician".

Case in point, I'm taking a look at a software application that seems to be plagued by performance issues. The server, although relatively beefy, is struggling to keep up with what on the front end seem the most simple of requests. So I fire up the SQL Profiler for 20 minutes during the work day when the application is being most utilized and then peek at the results, ordering my profile results (T-SQL and Stored Procedure metrics) by duration and reads. I quickly parse down the lists and find a query that looks up customers by phone number, with the area code and exchange/number separate... what would seem to be a straightforward and low load query.

Here's the metrics from the SQL Trace:

Reads: 416016

Duration: 5422 milliseconds

CPU: 1313

That's a lot of reads, and very very slow as far as I'm concerned. So I pop open the database table to see what kind of indexing is going on. Sadly, there are indexes on the table, but not the appropriate kind. So I blew away the indexes, and recreated them in a more... sensible manner. Then I ran the Profiler and executed a series of similar queries and got the following results:

Reads: 9720

Duration: 78 milliseconds

CPU: 78

As you can see, the performance with a proper index was ASTRONOMICALLY better than the previous setup. I know for a fact that no DBA was involved in the original project and a group of developers with a very high opinion of themselves did everything from start to finish. Hence, my admonition, know your role. Very few people can be all things to all people, and in cases like the one above, even having a minimal DBA presence on the project could have had time spent putting in very basic optimizations like this that could have given much better performance, scalability, and user satisfaction.

Published Aug 28 2006, 06:37 PM by Eric Wise

Filed under: Business / IT, Rants, Patterns and Practices

Comments

Pieter said:

Hi Eric,

This is a very common phenomenon, I have been involved in several audits in the past where the applications are really well written, but the databases indexing is shocking. Here you reduced a query from 5s to nothing, I have seen cases where applications timeout, users are fuming and then by only inserting the correct index suddenly the application is a wonderful beast in the eyes of the users and you are declared a god, just because of the reasons you have mentioned here.

# August 28, 2006 8:37 PM

David said:

I'm not surprised by this. Most "traditional" programmers I've worked with just don't seem that interested in learning how a database works and how to use it effectively.

But your example surely seems like a case where the database could help automatically. I know SQL Server keeps stats on this type of thing - why can't it go the next step and add the missing index? I'd much prefer that type of feature in my database than XML support, .Net support, etc.

# August 28, 2006 9:39 PM

Eric Wise said:

There are tools in SQL Server that can help you identify these kind of things. But nothing replaces a skilled DBA that understands the underpinnings of how SQL works.

# August 28, 2006 9:53 PM

Jeff Perrin said:

So the project required a DBA because some indexes were improperly set? My current project didn't have a DBA for 2 years, until recently since we're now at the stage of optimizing for performance. It seems to me that as long as the database is intelligently structured in the first place, a DBA's role would be rather small in most cases.

Step one - Identify a performance issue (ie; a report takes an unreasonable amount of time to run)

Step two - Profile your application to figure out where the most time is being spent

Step three - If the problem is determined to be in the database, try to fix it, calling in an expert if it's over your head.

I guess I'm one of those arrogant developers that doesn't think you need a DBA... Until you really need one.

# August 28, 2006 11:30 PM

Eric Wise said:

"It seems to me that as long as the database is intelligently structured in the first place"

And who do you think should 'intelligently structure' a database. Frankly, I don't know many developers who know what the various normal forms are. Also, besides indexing and structure, if you have a knowledge of the order in which SQL executes and where the performance bottlenecks are you can build much better queries. What about SQL Jobs, DTS packages, security/history auditing, and data warehousing?

I see Jeff's style of comments a lot, as indicated in the original post. I think the problem is that they see and use a database like a dumb file store instead of a relational query engine that it really is.

Also bear in mind that I advocated even a minimal DBA presence. If you're taking TWO YEARS on a project, doesn't it make sense to have a database expert come in for a week or two, in the initial design phases, and a few days here and there to review indexes and complex queries?

# August 29, 2006 7:57 AM

Jeff Perrin said:

"doesn't it make sense to have a database expert come in for a week or two, in the initial design phases, and a few days here and there to review indexes and complex queries?"

No. Because at the start of the project we don't have any complex queries, or a very complex table structure (Agile). We use an OR Mapper (Toplink) and have either 0 or two stored procedures in our entire system. Those stored procedures exist to deal with a performance issue.

And I *do* see the database pretty much as a dumb filestore, which is exactly what it is for our application. Our "smarts" are written in Java, and the database is just there to persist data between sessions. There's some reporting of course, but that is also mapped directly to an Oracle view through Toplink.

Why don't you just come to the realization that not everyone sees things the way you do? Is our project invalid and doomed to failure because of our lack of a DBA from day one? I hope not.

# August 29, 2006 9:09 AM

Eric Wise said:

I'm not saying that your project is a failure. But I'd be interested to see the business justification for using a high dollar system like Oracle as a dumb file store. =)

And no, I'm not going to be a programming facist and say you have to optimize up front. Certainly I know you don't put new oil in your car until the engine smokes and don't do maintainance on the roof of your house until it actually leaks...

# August 29, 2006 9:16 AM

Jeff Perrin said:

"But I'd be interested to see the business justification for using a high dollar system like Oracle as a dumb file store."

So would I... ;)

# August 29, 2006 9:30 AM

Sahil Malik said:

You need a DBA. Anyone who thinks they don't are just silly.

# August 29, 2006 10:22 AM

Wayne M said:

Let's all step back and take a deep breath. The reality is that many projects do not have the functional needs for a full-time DBA, do not have the cost budget to pay for a full-time DBA, nor have the schedule slack to stop and wait for a part-time DBA to become available when the need arises. As such, in many projects, programmers must put forth their best efforts to develop a database adequate to the project's needs. This is not a matter of respect, this is a matter of resource allocation.

Respect arises as an issue when the project hits a need where database expertise is required. Programmers and the project manager need recognize when it becomes more advantageous to call upon a DBA to address database needs rather than having the programmers try and figure it out. This is a schedule versus cost trade-off that may differ project to project.

Anyone coming into a project part time needs to understand the level of emotional commitment the full time members have made to the project and respect it. One needs to avoid disparaging any mistakes that have been made; the full time team has done its best with its knowledge and skills. One also needs to leave the full time team with some added skills and knowledge so that they are less likely to repeat mistakes in the future. It is not effective to simply flit in, solve a problem, and leave.

There is always a trade-off between what can be done within the database and what can be done outside of the database. This means, that for many projects, minimal database development effort is required and an adequate design is sufficient. Developers need to respect the skills of DBAs and ask for their support as needed. DBAs need to respect the efforts of programmers and pitch in as needed.

# August 29, 2006 10:32 AM