SQL Sentry Fragmentation Manager

Introduction

Heavily fragmented indexes degrade the performance of your database and the applications running on it. Resolve index fragmentation by reorganizing or rebuilding an index. Fragmentation Manager automatically collects table and index information, analyzes the data, takes the appropriate reorganization or rebuild operations, and then performs post defragmentation analysis.

Fragmentation Manager has a dedicated tab in SQL Sentry, Indexes. The Indexes tab displays index related statistics and charts, from the target level down to the individual index level, giving you a complete view of the fragmentation levels on your server. Having this information allows you to make intelligent decisions about index management in your environment such as when and how to perform defragmentation operations, when to adjust fill factors, or when an index definition should be changed.

Set different schedules for instances and databases down to the individual table or index level, giving you complete granular control over any defragmentation actions. Additionally, set specific schedules for rebuilds or reorganizations explicitly. Several additional settings are available to help you calibrate the actions SQL Sentry takes, including:

• The ability to set the scan level or mode that's used to obtain fragmentation statistics.
• The ability to set minimum and maximum index size thresholds for the collection of fragmentation data.
• The ability to set reorganization and rebuild fragmentation threshold percentages.
• All index defragmentation settings work within the normal SQL Sentry hierarchy meaning that settings can be configured at one level and are automatically inherited by objects below it, allowing for easy automation within your environment.

For a complete explanation of all the available settings, see the Fragmentation Manager Settings section.

Enabling Fragmentation Manager

Enable Fragmentation Manager through the right-click context menu of any instance or by opening the Indexes tab of SQL Sentry and selecting Enable Now. The first time you enable it, the Fragmentation ManagerWizard displays.

Fragmentation Manager Wizard Options

Option	Description
Collect table and index size information	The base level of Fragmentation Management. Every 15 minutes SQL Sentry collects table and index size information for the top 500 indexes in each database. Note:  Indexes are collected that meet the criteria specified in the  Minimum and Maximum size thresholds, with values ranging from 10 MB to 50,000 MB.
Collect buffer data when buffer size > eight GB	Determines if SQL Sentry collects buffer data when the buffer size is > eight GB. See the Database Source setting Collect Buffer Data when Buffer > eight GB description for a complete explanation.
Retain historical data [FM]	Retains table and index historical data.
Analyze fragmentation	SQL Sentry analyzes index fragmentation statistics based on the schedule you specify on the next screen and displays those statistics on the Indexes tab of SQL Sentry. The Limited Mode obtains fragmentation statistics. Change the Fragmentation Scan mode after the wizard completes from the Index Defragmentation settings.
Defragment indexes over specified thresholds [FM]	SQL Sentry performs defragmentation operations based on the schedule you specify in the next screen. Selects your desired reorganization and rebuild thresholds for defragmentation operations.
Reanalyze indexes after defragmentation	SQL Sentry reanalyzes fragmentation statistics after any defragmentation operations are performed.

Selecting a Schedule

To select a schedule to be used for analysis and/or defragmentation, choose a pre-existing schedule, or select the New command to create a new schedule.

Select Next to confirm your settings, and then select Finish to complete the Wizard.

Fragmentation Manager Related Settings

The following are two groups of settings relevant to Fragmentation Manager:

• Database Source settings—Used to configure the general collection of table and index information, including size collection thresholds, buffer collection thresholds, and index partition options. 
• Index Defragmentation settings—Used to configure the defragmentation and analysis operations, including scheduling, setting index reorganization, and rebuild thresholds.

Database Source Settings

Synchronization	Description	Default Value
Inherit From Parent	Specifies whether settings in this group are being inherited. For more information about inheritance, see the hierarchy chart in the Alerting and Response System topic.	True
Maximum Rows to Synchronize	Maximum rows of historical event data to collect from this source.	5000

General Settings	Description	Default Value
Inherit From Parent	Specifies whether settings in this group are being inherited. For more information about inheritance, see the hierarchy chart in the Alerting and Response System topic.	True
Enable Table/Index Data Collection	Specifies whether table and index collection is enabled. When enabled every 15 minutes SQL Sentry collects table and index size information. Note:  This setting must be True to perform Analysis operations and Automated Defragmentation operations.	True
Max Partitions to collect per Database	The maximum number of partitions information that's collected per database. Note:  Partitions are collected by size. The partitions are ordered by ReservedSpaceMB, that’s using reserved_page_count from sys.dm_db_partition_stats.	500
Min Index Size (MB) to Collect Fragmentation Data	Specifies how large an index must be before statistics are captured. If you keep the default value of 10 MB, any indexes that are smaller than 10 MB aren't analyzed or considered for defragmentation operations.	10 MB
Max Index Size (MB) to Collect Fragmentation Data	Sets the maximum size an index can be, and still be considered for defragmentation operations.	50000 MB
Collect Buffer Data when Buffer > eight GB	Specifies whether to collect buffer data when buffer size is > eight GB. Collecting detailed buffer information is inherently a low overhead process. However, because it takes approximately one second per GB of buffer, on larger buffers the associated query takes some time to complete, which causes it to appear in Top SQL and/or generate associated alerts. It's safe to enable this setting because SQL Sentry ensures that the total time spent per day collecting buffer data is roughly the same regardless of buffer size, and collection only occurs when buffer has changed. When collecting buffer data, SQL Sentry uses a variable collection frequency, that's based on the size of the buffer. The following chart shows the variable collection frequency along with the approximate time it takes to collect buffer data for different buffer sizes. Maximum Buffer Data Collection Frequency Buffer Size (GB) Collection Frequency (Min) Time to Collect (Sec) Time to Collect (Min) 4 15 4 0.1 8 15 8 0.1 16 30 16 0.3 32 60 32 0.5 64 120 64 1.1 96 240 96 1.6 128 240 128 2.1 256 480 256 4.3 384 720 384 6.4 512 1440 512 8.5 768 1440 768 12.8 1024 2880 1024 17.1	<not specified> Set with Wizard

Index Defragmentation Settings

After you enable Fragmentation Manager the Index Defragmentation settings are accessed through the Settings pane. Index Defragmentation settings are configured at the following levels: global (All Targets), sites, target group, target, instance, database, table, or at the individual index.

To configure the Index Defragmentation settings for a specific instance, complete the following steps:

1. Select the instance in the Navigator (View > Navigator), and then open the Settings pane (View > Settings).
   
2. Select Index Defragmentation from the bottom drop-down menu to configure your settings.
   

Index Defragmentation Settings 

General Defrag Settings	Description	Default Value
Inherit From Parent	Specifies whether settings in this group are being inherited. For more information about inheritance, see the hierarchy chart in the Alerting and Response System topic.	False
Primary Schedule	The schedule where index analysis and/or defrag performs, according to the configured reorg and rebuild thresholds. For more information about schedules, see the Schedules topic.	<not specified> Set with Wizard
Operation Type	Specifies whether analysis only or analysis and defrag operations occur under the Primary schedule.	Analyze Only
Reanalyze Indexes after Defrag	Specifies whether to run the fragmentation analysis again after any scheduled defrag.	False
Fragmentation Scan Mode	The Scan mode used for obtaining fragmentation statistics: Limited mode is the fastest mode and scans the smallest number of pages. For an index, only the parent-level pages of the B-tree are scanned. For a heap, only the associated PFS and IAM pages are examined; the data pages of the heap aren't scanned. Sample mode returns statistics based on a one percent sample of all the pages in the index or heap. If the index or heap has fewer than 10,000 pages, Detail mode is used instead of Sample mode. Detail mode scans all pages and returns all statistics. Additional Information: For more information about Scan modes, see the sys.dm_db_index_physical_stats (Transact-SQL) MSDN article.	Limited
Maximum Concurrent Operations	Specifies the maximum number of concurrent operations. Operations are defined as an analysis, rebuild, or reorganization.  This setting is capped at five.	one
Delay Between Defrag Operations	When Maximum Concurrent operations is set to one, specify a delay between defrag operations to prevent send/redo queue overload when availability groups or mirroring are in use.	30 seconds
Maximum Duration	This sets the maximum allowed runtime for the defragmentation process. If the maximum duration is met during a rebuild operation the rebuild still completes. If the maximum duration is met during a reorganization operation, the reorganization stops and starts again during the next scheduled cycle.	two hours
Partitions	Partitions to include in the rebuild. This option is applicable for indexes that have a sliding windows partition scheme.  Additional Information: For more information about partition schemes, see the Implementing Partitioned Tables and Indexes MSDN article. All —All partitions belonging to the index that meet the defragmentation criteria are either rebuilt or reorganized. Max Only —If the maximum numbered partition of the index meets the defragmentation criteria it's rebuilt or reorganized. Exclude Max —All partitions that meet the defragmentation criteria, excluding the maximum numbered partition, are either rebuilt or reorganized. Note:  Online rebuilds for partitioned indexes only perform if the Partitions setting is set to All and all partitions in the index meet the defragmentation criteria. If the above criteria isn't met, an Offline Rebuild performs instead.	All Partitions
Index Reorg Settings	Description	Default Value
Inherit From Parent	Specifies whether settings in this group are inherited. For more information about inheritance, see the hierarchy chart in the Alerting and Response System topic.	True
Reorg Threshold%	Sets the fragmentation threshold percent at which indexes are reorganized. If the default threshold of 10 percent is kept, indexes that have an index fragmentation percentage below 10 percent , as determined during the Fragmentation Scan, aren't reorganized. Indexes with a fragmentation percent exceeding 10 percent are reorganized. Additional Information: For more information about reorganizing indexes, see the Reorganizing and Rebuilding Indexes MSDN article.	10 percent
Reorg Schedule	Dedicated schedule where reorgs only are performed, according to the reorg threshold. Rebuilds aren't performed even if the rebuild threshold has been exceeded, and are reorganized.	<not specified>
Index Rebuild Settings	Description	Default Value
Inherit From Parent	Specifies whether settings in this group are inherited. For more information about inheritance, see the hierarchy chart in the Alerting and Response System topic.	True
Rebuild Threshold %	Sets the fragmentation threshold percent for the indexes that are rebuilt. If the default value of 30 percent is kept, only indexes that have an index fragmentation percent of 30 percent or greater, as determined during the Fragmentation Scan are rebuilt. Additional Information: For more information about rebuilding indexes, see the Reorganizing and Rebuilding Indexes MSDN article.	30 percent
Rebuild Schedule	Dedicated schedule where rebuilds only (online or offline) are performed, according to the rebuild threshold.	<not specified>
Offline Rebuild Window	If an offline rebuild performs, it starts the offline rebuild during the specified window.	<not specified>
Sort in tempdb	Specifies if tempdb is to be used for sorting during rebuild operations. Additional Information: For more information about tempdb and index creation, see the tempdb and Index Creation MSDN article.	False
Use Online Rebuild (Enterprise Only)	Determines if the index rebuild operations perform online. Additional Information: For more information about performing index operations online, see the Performing Index Operations Online MSDN article.	True
MAXDOP (Enterprise Only)	Determines the max degree of parallelism (MAXDOP) that's used when rebuilding the index. The default value is zero, which allows the server to determine the number of CPUs that are used.  Additional Information: For more information about configuring Parallel Index Operations, see  the Configuring Parallel Index Operations MSDN article. Note:  When Fragmentation Manager is working on an index, it uses the MaxDOP to determine how parallel each concurrent connection can run. Increasing the Concurrent Connections increases how many indexes are being worked by the tool at any given time.	zero

Manual Fragmentation Operations

Fragmentation Operations can also be initiated manually. Within the Navigator use the right-click context menu of any database, table, or index to initiate fragmentation operations, including analysis, reorganizations, or rebuilds.

Manual Fragmentation Operations can also be initiated within the Indexes tab. From the Grid/Tree view found in the center of the screen, use the context menu of any database, table, or index to access fragmentation operations.

Fragmentation Alert Conditions

The following fragmentation related conditions are available to configure actions for:

• Defragmentation Completed
• Defragmentation Started
• Defragmentation Failure

To configure a fragmentation related condition, complete the following steps: 

1. Select the node appropriate to the level you'd like to configure the action for in the Navigator pane (View > Navigator), and then open the General Conditionssection in the Conditions pane (View > Conditions).
   
2. Select Add in the Conditions pane to open the Actions Selector window.
3. Expand the Index actions and then the appropriate condition. Use the check box(es) to select which actions should be taken in response to this condition being met. Select OK to save your setting.
   

Indexes Tab

Database Space Usage Band

By enabling the Fragmentation Manager, the functionality of the Disk Space tab is also enhanced by providing additional information regarding the space usage of indexes. For more information, see the Disk Space topic.

Buffer Size (GB)	Collection Frequency (Min)	Time to Collect (Sec)	Time to Collect (Min)
4	15	4	0.1
8	15	8	0.1
16	30	16	0.3
32	60	32	0.5
64	120	64	1.1
96	240	96	1.6
128	240	128	2.1
256	480	256	4.3
384	720	384	6.4
512	1440	512	8.5
768	1440	768	12.8
1024	2880	1024	17.1