Read this article in your language IT | EN | DE | ES

Image via Wikipedia

You probably don’t have a DBA on site but you need some DB maintenance.

A DBA is a resource developers hope would be there when their product ships, but most companies don’t realize the importance of having someone actively managing their SQL servers. SQL is good enough to plug along happily without any attention. But it barfs up a lung when it runs out of disk space or runs at a snails pace when the data and indexes are fragmented, or indexes don’t exist where they should. Two very common and re-occurring issues.

The solution is coming up with a “hands free” maintenance routine. I am mostly referring to re-indexing the database. There are a few scripts out there if you Google for them, most examples end up in forum posts or on blogs. I’ve yet to find one that satisfies the needs of all people involved though. I think it go something like this:

Application Users: They want a fast performing application. Sure quality code has a lot to do with it but if nothing is done to keep performance up, users will notice a lag in the application. It only takes a change of 200 milliseconds in responsiveness for an end user to determine if the software is “faster” or “slower”. This requires a maintenance task that improves the performance of the database. It should also not impact their use of the application. [They need performance.]

IT Staff: They support the Application Users. Their tasks are to make sure they can backup the database (and/or) server, run a virus scan and other optimizations without impacting the users. That’s a tough job, not to mention if the application requires other long running processes. Scheduling all of these becomes a precarious juggling act. [They need time to do everything.]

Database Administrators: If you’ve got an application that has databases with tens of gigabytes or more in size, you could benefit from having a DBA. They can have an intimate knowledge of the database application and it’s architecture. They’ll know what to optimize and how it should be done but only if they are able to poke and prod a database. They will need plenty of time to do this, especially if they weren’t the ones that designed the database. [They need an intimate knowledge of the database.]

Application Developers: Their needs coincide with the needs of the application itself because they designed it. The application needs the database to perform quickly and have plenty of free space for SQL to do what it needs to do. For them, an increase in database performance can reflect positively on their product. [They need their product to work well.]

Now that we know who’s involved and what their needs are, I think I can come up with some requirements…

1. Reduce fragmentation on affected indexes over a certain percentage.

2. Re-index all indexes the first time, then specific indexes after that. (high performing, high use tables)

3. Automated to reduce any active involvement by IT or having to contract with an outside DBA.

4. Run within a limited amount of time to keep the database and server available for other processes and end users.

5. The solution should be portable so that it can be deployed and re-used on any SQL database.

There are some additional things that can be done but aren’t as necessary, like offer the option to add an index where it might improve query performance (although SQL already offers this through the Database Tuning Wizard). It would also be helpful to first get the SQL files (system files) in a non-contiguous state before running the SQL optimizations. An improvement of disk performance will improve SQL performance too but moving the files requires the SQL service to be stopped. One last suggestion (because I just don’t have enough feature creep to call this noteworthy) would be to determine which tables to break into individual file groups so they can be distributed to separate disks if needed.

I think that covers it. Let me know if I missed something…I think I can come up with a preliminary re-indexing script…in the next post. :)