One of the biggest headaches “reluctant DBAs” have to deal with involves supporting SQL Server when it’s being used to maintain someone else’s application. What can you safely do without potentially breaking the app?
First, you should get really familiar with backup and recovery in SQL Server;
Now, for the stuff you can always do without fear:
- Reorganize and rebuild indexes -- Optimizing SQL Server indexes can offer a major performance advantage. Keep in mind that the application may not work correctly while a rebuild is underway, and it will definitely work more slowly.
- Configure most database options, with the exception of the compatibility level -- In other words, you can configure autogrow and autoshrink of files, turn on auto-update of statistics, etc. -- most of which are good ideas.
- Add memory to your server -- SQL Server loves memory, and you can never give it too much. Ditto with processors, so you should never configure processor affinity or memory limits.
- Backup and restore the database, or even pieces of it -- This won’t break anything, and you can never have too many backups. On really critical databases, I like to grab the usual full or differential backups, but I love grabbing transaction log backups as frequently as possible since they’re fast and offer a pretty granular level of recovery.
- Use SQL Server Profiler to capture server traffic for analysis -- Basically, using SQL Profiler like this is a passive operation, so the app won’t even know you’re doing anything.
Next, let’s discuss the things you can usually do without causing a problem. A well-written application shouldn’t mind any of these, but remember that not every application is well-written!
- Use the Database Engine Tuning Advisor to add or drop indexes. Adding indexes usually won’t be a problem for an app, but some apps may try to refer to specific indexes by name. This means that if you drop one, it could break something.
It shouldn’t, though; the only legit reason for an application to have an index name hardcoded into it is if the app has its own maintenance routines to kick off per-index reorgs or rebuilds.
- Split the database objects across multiple database files, thus letting you place different files on different disks for performance reasons.
I don’t see many environments where this is necessary, because today we pretty much put files on RAID arrays. The physical layout of the database is abstracted from the logical structure, so applications won’t generally care where the files physically sit—unless, of course, the app runs its own backup routines, in which case it may be relying on particular file or filegroup names.
With most applications, you can do almost any performance tuning or maintenance tasks you want and the app won’t care. Poorly-written apps may have some dependencies on things like specific index names, in which case you might want to find the vendor and ask them what they heck they were thinking.
Miss a tip? Check out the archive page for Don's series, SQL Server for the Reluctant DBA.
ABOUT THE AUTHOR:
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at www.ConcentratedTech.com.
This was first published in May 2010