SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

DBAs and the Fear of Maintenance Plans

By Tony Davis,

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.



Total article views: 342 | Views in the last 30 days: 1
Related Articles

How Maintenance Plans are a Big Plus for SQL Server Administration

SQL Server 2012 makes your database maintenance jobs easy by proving an effective Maintenance Plan W...


Brad's Sure Guide to SQL Server Maintenance Plans

Brad's Sure Guide to Maintenance Plans shows you how to use the Maintenance Plan Wizard and Designer...


The Maintenance Plan Wizard Can Lead You Astray

In theory, the SQL Server Maintenance Plan Wizard is supposed to make it easier for non-DBAs or novi...


Capture Index Maintenance Operations

One good database maintenance practice is to keep the indexes in good working order. This is typical...


Index maintenance

How to convince your colleagues to have index maintenance job