Properly managing index density and fragmentation levels can be very time-consuming. Having spent my fair share of time in the trenches, I was happy to take a look at Idera's SQL defrag manager 2.5 to see what it had to offer. Overall, I was very impressed with what I found.

SQL defrag manager 2.5 is a GUI that wraps existing T-SQL functionality for index analysis, defragmentation, and optimization. It lets you quickly analyze and manually defragment indexes with just a few clicks of the mouse. Moreover, SQL defrag manager makes it pretty easy to do at-a-glance index management by utilizing a dashboard approach that can be easily configured to highlight indexes, tables, or databases with problems.

When analyzing SQL defrag manager 2.5 and putting it through the paces, I was impressed by how smoothly the installation went, and how easy it was to get everything set up. There’s no need to install any agents on your managed servers. Instead, the installation process creates a new database that acts as a repository for policies, as well as scheduling and historical data that can be used with the UI or admin console (which can be installed on any client machine).

The admin console has a Microsoft Office ribbon look and feel that makes navigating to different options and features a breeze. During my review, I noted that features are easy to discover, and everything about this tool is intuitive and easy to understand. More importantly, SQL defrag manager makes manual analysis and correction of index fragmentation a snap using the Fragmentation Explorer, which displays object hierarchies, index metrics, and fragmentation details.

Where SQL defrag manager really shines, however, is by enabling automated index defragmentation. By using the admin console in conjunction with the specialized Defragmentation Service and the repository (or custom database) created during installation, you can easily create policies (or jobs) that can be scheduled as needed. Policies are easy and intuitive to configure, and you can specify thresholds for fragmentation levels and scan densities (as well as filters for what kinds of conditions to ignore) that indicate when you need to take action. Policies respond to fragmented indexes by rebuilding or reorganizing them.

Flexibility and control are rampant throughout the policy creation process. For example, policies can target entire servers, all the indexes of a given table, or selected indexes on one server and a handful of indexes on another. You just have to select which objects you want to be included from a tree view of registered servers and specify other criteria as needed via the appropriate tabs used for policy definitions, as shown in Figure 1.

One of my favorite features enables policies to take no action; instead, problematic indexes are dropped into a to-do list where they can be manually and individually analyzed and dealt with. Another place in which SQL defrag manager shows considerable maturity and real-world clout is when it comes to defining Resource Checks that can be evaluated prior to each policy being executed, as shown in Figure 1. With this feature, you can craft moderately aggressive defragmentation policies but still ensure that if an extraction, transformation, and loading (ETL) job runs a bit late, policy execution won't bring the server to its knees by blindly starting when scheduled.

Sadly, as cool and powerful as SQL defrag manager’s policies are, there's no way to fire them off manually. So, if you've got a policy defined for a few problematic tables that you need to run on a semi-regular basis, you either have to manually defrag those tables or temporarily tweak the policy’s schedule. This is a big limitation in my mind because it prevents you from using of SQL defrag manager's more powerful capabilities in ad-hoc fashion. Another negative factor that I encountered was that DROPed tables kept showing up in the UI, and trying to force re-scans of the dropped tables resulted in errors.

Even with those quirks, I would have given SQL defrag manager a nearly 5 out of 5 rating if it weren't for its pricing. As amazing as this tool is, at $1,195 per managed server, it's overpriced. Although Idera offers discounts for volume licenses, it doesn't make that blatantly obvious on its website. Still, this solution has some great features and can help cut costs in terms of administrative effort, so it's definitely worth an evaluation.