In this post, I’ll be going over SQL Sentry Fragmentation Manager – an add-in to SQL Sentry Performance Advisor. Performance Advisor on its own provides a great deal of information about the indexes within your databases, but Fragmentation Manager adds additional insight into health, size and usage of your indexes as well as provides a tool to manage your indexes. There’s almost too much great stuff to go over in one blog post, but I’m going to try. If you want to jump to my very favorite feature of Fragmentation Manager, you can jump to it here, but I encourage you to read the entire post.
There are three sections of information within Fragmentation Manager. The graphs along the top of the screen (Overview Charts) contain information on all of the indexes within the instance that you’re currently viewing. The middle section (Index Grid View) provides more detailed information on the individual indexes in both a grid view and a tree view. As with all of our grid views, you can sort or filter on any of the columns. The graphs along the bottom of the screen (Details Charts) contain information on an individual index selected from the middle pane. One other piece of Fragmentation Manager is the ability to schedule index maintenance. Let’s go into some additional detail for these sections.
Overview Charts
The graph in the upper left corner shows you the health of all of your indexes from green (< 10% fragmentation) through red (> 30% fragmentation). Hovering over any of the sections will show you how many indexes are in that group. This is a quick way to see the relative health of all of your indexes.
Indexes use resources and this is a great way to see what they’re using. You can both the space used on disk (on the left) as well as the space used in the buffer cache (on the right). You might also notice unused space is indicated for disk space – space that has been allocated, but not used. You’ll also see empty space in both disk usage and buffer usage – this represents pages that aren’t full. Partially full pages are typically a result of fill factor – this isn’t necessarily bad, but something to keep an eye on.
Index Grid View
The center section of Fragmentation Manger provides detailed information about your indexes, including type, size, rowcount and amount of fragmentation. You can view this information in either a grid view or tree view.
Within the Index Grid View, you can also take actions on individual indexes such as analyzing fragmentation or defragmenting the index. You can also choose to omit certain indexes from the index maintenance set up through Fragmentation Manager.
Details Charts
When you click on a specific index in the Index Grid View, the graphs on the bottom of the screen will pertain to that index. You can see the fragmentation of that index over time as well as the space used both on disk and in the buffer cache.
Here is my favorite feature of Fragmentation Manager. The graph in the lower right window shows index activity. Not a big deal, right? You can get that information from sys.dm_db_index_usage_stats. The difference here, though, is that we collect the delta for user scans, user seeks and user lookups each time we query a monitored server. That means that you can not only see if the index is being used, but when it’s being used. One other thing to keep in mind is that the sys.dm_db_index_usage_stats DMV gets cleared out when the server is rebooted or if the SQL Server service is restarted. Because all of this information is stored in the SQL Sentry repository, you won’t lose anything if your server gets rebooted. I think that’s wonderful.
Scheduling Index Maintenance
An important part of Fragmentation Manager is the ability to schedule index maintenance. You can specify the schedule and maximum duration as well as the thresholds you would like to use for reorganizing and rebuilding indexes. You can also specify whether multiple threads should be used during the index defragmentation. All of the settings are explained in more detail here.
I think you’ll agree that SQL Sentry Fragmentation Manager provides a great deal of insight into your indexes as well as the ability to maintain them. I hope that you’ve enjoyed this post!
