Does Your Database Need a Defrag?

Keeping SQL Server databases running smoothly is an ongoing task for DBAs, particularly in the area of database maintenance. Database performance can slow over time, caused by everything from limited server system memory or storage to performance-throttling server load driven by an increasing number of users.

Rick Pleczko, President and CEO at Idera, explains in an email interview that database maintenance is a vital task that DBAs need to pay attention to. "Keeping up with database maintenance is extremely important. Just like your car, if you don't keep up with maintenance then performance and availability suffer and eventually a significant breakdown occurs," says Pleczko. "In the world of SQL Server this can cause significant business interruption while problems are fixed or, even worse, data loss. It's a case of pay me now or pay me a lot more later."

In a recent issue of SQL Server Magazine, reader Eric Peterson suggests that defragmentation could help solve some performance-related issues. (See Reader to Reader, "Queries Dragging? Try Defragging.") "Did you ever have a user tell you a query is taking a lot longer to compete than before, even though nothing in it has changed? If so, there's a good chance that the indexes in the table that the query ran against have become fragmented," explains Peterson. "Fixing this problem is a two-step process. First, you need to first determine which indexes have become fragmented. Second, you need to defrag those indexes."

To help address database index defragmentation, SQL Magazine Contributing Editor Kevin Kline suggests DBAs take a look at SQLIndexDefragAll, a free defragmentation script developed by Microsoft. "SQLIndexDefragAll defragments indexes on all unexcluded SQL Server databases," says Kline. "You can also use this job to defragment indexes within a specific SQL Server instance, database, or table." (See Tool Time with Kevin Kline, "DBA Automation Scripts," September 2007.)

Pleczko agrees that defragmentation tasks can be accomplished by using scripts and other tools for limited numbers of SQL Servers. He also argues that attempting to accomplish that task for "fifty or a hundred SQL Servers" is difficult to manage without automated tools, and suggests that Idera's recently upgraded SQL defrag manager 1.2 provides a solution for DBAs looking to automate defragmentation duties across multiple servers. Defrag manager provides central management and reporting of defrag operations, can be configured to defrag indexes automatically, and can point out areas where defragmentation could lead to performance improvements.

"We've seen cases where expensive application re-architecture projects have been initiated to solve a fragmentation related problem," says Pleczko. "[That] problem could have been solved very cheaply by simply automating and keeping up with maintenance activities."

SQL Defrag Manager 1.2 is $995 per SQL Server instance. For additional information email [email protected] or visit the Idera website.