SQLServerCentral Editorial

DBAs and the Fear of Maintenance Plans

,

In a shrinking economy, many DBAs fear for their job security. This fear is fuelled by the emergence of such tools as SQL Server Maintenance Plans, designed to make the job of creating and scheduling essential database maintenance tasks a wizard-driven formality.

The SQL Server Maintenance Plan Wizard and Designer don't offer a great deal in the way of flexibility, but they do allow you to set up basic plans that will at least ensure that the absolutely critical maintenance tasks get done. Nobody could argue, for example, with the good sense in performing regular full and transaction log backups, or running database integrity checks. The Maintenance Plan wizard offers to the less experienced DBA a quick and simple route to make sure these essential tasks are performed and scheduled.

However, in the hands of the unwary DBA, the Maintenance Wizard also offers a means to ensure that other valid maintenance tasks are applied in a counter-productive, if not outright destructive, manner. With a few mouse clicks, one can define and schedule a task that will regularly attempt to shrink "all user databases", blissfully ignorant of the fact that the unused space in these database files might have been put there by design, in order to allow the database to grow in size gracefully, without relying on regular, expensive auto-growth events.

Likewise, with a few swift ticks in the appropriate boxes, one can schedule a task to blindly drop and recreate (rebuild) all indexes on any specified tables, regardless of whether they need defragmenting, or whether it will actually offer any benefit in terms of query performance. You don't even get the option to only beat up on those indexes that really do have significant fragmentation. It's a pure scattergun approach.

Brad McGehee's forthcoming book on SQL Server Maintenance Plans makes it clear that, in its place, the Maintenance Plan wizard is a powerful time-saving tool for any DBA. But its "place" is to ease the maintenance burden on smaller, non-business critical systems.

For any database that supports a "tier1" application, effective maintenance relies on the judgment, experience and common sense of a flesh and blood DBA. It relies on their knowledge of the business applications and how these applications use the underlying databases, tables and indexes, and on their understanding of the performance metrics, IO's and so on that determine if tasks such as index rebuilds really are worthwhile.

So, ultimately, I would say this to any DBA who dreads the moment they turn up to work one morning to find they've been made redundant by an automated maintenance plan: you can sleep peacefully for the foreseeable future.

Cheers,

Tony.

Rate

4.5 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (4)

You rated this post out of 5. Change rating