Policy Wizard - Select policy response

Select policy response

The Response tab allows you to select how the policy responds when the specified conditions are met.

Advantages of each optimize response

Reorganize does not hold long-term table locks and has less impact to availability. However, it results in less reduction of fragmentation levels. Rebuild has a higher impact on your system availability, but provides the best results in the reduction of fragmentation. If availability during the rebuild is important, select ONLINE (SQL 2005/2008 only), but note that this option requires additional operation time and resource consumption. Use SORT_IN_TEMPDB to direct SQL Server to use tempdb for the intermediate sort results used to build the index. For more information, see Microsoft Books Online.

Adding qualifying index defragmentation to the To Do list

You can select whether you want SQL Defrag Manager to automatically defragment qualifying indexes or to add them to the To Do list. These responses define what SQL Defrag Manager does when it identifies a fragmented index during an automated, scheduled analysis. For example, if you select Yes, Automatically Defrag, when SQL Defrag Manager performs a scheduled analysis activity, it attempts to defragment each index it finds that is not compliant with policy, using the selected reorganize or rebuild method. If you select No, Add To Do item, when SQL Defrag Manager performs a scheduled analysis activity, SQL Defrag Manager creates a To Do item on the To Do view of the SQL Defrag Manager Console for each index it finds that is not compliant with policy.

Available options for updating the statistics

The SQL Defrag Manager Response tab provides three options for updating your statistics: 

• Before Analysis Renewal: Updates the statistics for each object covered by this policy each time an analysis is performed.
• Before Optimization: Updates the statistics before reorganization or rebuild occurs so the query optimizer has the most current information for building the execution plan for the operation.
• Never: Prevents any statistics updates regardless of what other activities occurred.

These options can be applied using a Full Scan, Sample Percent, Sample Rows, or Resample method.

Select the Use NORECOMPUTE check box, and SQL Server does not maintain the statistics after you perform a manual update. Leave this check box clear and SQL Server maintains statistics after a manual update.