How to Get on With Your Resident DBA - A Guide for the Modern Developer
Although the role of developer and DBA is less well defined in many organizations than it used to be, there is still a major distinction between the two: DBAs major in the administration of databases and may also write code; developers in the main only write code to run on the database.
Now this isn't a piece criticizing developers. It's just that many developers only see the development view of the database, ie: as a repository of information. Fair enough, as a developer you've got enough to focus on trying to turn vague user wishes into usable code! However, with a bit of forethought, you can make your life even easier. How? Befriend your resident DBA...
In order to achieve this magical feat, you need to get into the DBA's head. You see, DBAs are blamed by system administrators when the server has problems. They're the first to get blamed by the network guys when the Ethernet cables start to glow. They're blamed by users when they can't get their data. And they're usually the first port of call for a developer when their Transact SQL gets complex or they can't connect to the database. With all of this in mind, here are some top tips for extracting maximum performance from your DBA...
- Understand that DBAs aren't concerned JUST with code - Developers are code-centric. DBAs are database-centric, and they also need to concern themselves with performance, service packs, change management, network issues, storage strategies... the list goes on. Whereas a developer's role is more focused, a DBA has a lot more things to think about, many of them unknowns which can affect each other adversely.
- Don't blame the database - It's true that 90% of the time, if a query's running slowly, it's either a badly written query, or bad database design. Fact. If it wasn't, there would be more DBAs in the world. Surprisingly, it's rarely the database, despite the dizzying complexity of a modern relational database engine. Look at your code first.
- Eliminate what you can before hassling the DBA - Can't connect to SQL Server? Bring up a DOS prompt and ping the server. If you can't see it, chances are your DBA can't either, which means it's a system support problem. And if you can't see any other servers, it's probably a network or power outage.
- Check if anyone else is having a problem connecting - Remember fractions at school? Well just like fractions, finding the root cause of your problem is often about finding the common denominator. If 20 developers are suffering a connection problem, it's likely to be the database server or the network. If it's just you, and you've connected without problem before, it might be an incorrect password or connection string typo. Eliminate these problems first before bothering someone else. It's surprising how many people don't.
- Run your code from Query Analyzer/Management Studio – Not just Visual Studio. Particularly if you're building dynamic SQL (question why if you are), dump the SQL query in code into a string variable, copy it from the debug window into QA/Management Studio and run it from there. If it won't run from there, you can bet it won't run from your application either.
- Don't get hung up on milliseconds - Hours and minutes count. Many tips save milliseconds each time a query is run. Which is fine if that query gets executed a billion times each day, but except for the most heavily hit, high-volume transactional systems, most don't. For your average HR or CRM system, it's more important to identify long running queries first and get the execution time down from minutes to seconds. As a general rule, do as much as possible on the server, using T-SQL, to avoid lots of round trips across the network.
- Use the information available to you - Simple join tricks, such as creating an index on each column that's to be joined in a query can improve query performance massively on large table. If you can do that, you can create your own index creation scripts too. And although query plans are usually the domain of DBAs, if you have the time and dedication to understand how they work, you can almost always work out exactly where a query is performing slowly, and therefore which part of it you need to address in order to speed it up.
- Version control your stored procs - This one's really important, even in a single developer environment. With two or more developers, it's essential. Always book your stored procs in and out of source control. Why? If you alter one and screw it up, you can revert to the last version easily without any major headaches. You can play with your procs, safe in the knowledge that you can always recover the last working version if your creative streak fails.
- Ask for help in setting up test servers - SQL Server is easy to set up out of the box. It works and it's easy. But to get the best performance, speak to people who know how. This is rarely your system support people, as even the most hardened server room techies only rarely know how to correctly set up a database server for optimal performance. So if you're setting up a test box, avoid frustrating performance problems, and do it right from day one. Ask your DBA - preferably before you decide what hardware is going to be bought. A simple question can save you a lot of grief. You can also read our Quick Guide to Installing SQL Server 2005.
- Treat your DBA as a colleague, not a demi god - Most DBAs are decent, hard working people who some to work to perform a minor juggling act each day. Anyone who treats them decently instead of just someone to whinge at will get on their side. Very often this is something as simple as being co-operative.
End of Essential Guide
For further essential guides, technical articles, blogs, newsgroups, forums, links and more, please visit the SQL Server Club site at www.sqlserverclub.com