Space Management Wizard

From RapidSQL
Jump to: navigation, search

Go Up to Using Space Analyst

The Space Management Wizard can create two specialized space management jobs that allow you to proactively eliminate space threats before they affect your database:

Reorganize

You can create a "reorganization analyst job" that allows you to specifically select various databases and/or objects and then set reorganization thresholds that determine when a reorganization will occur. Once constructed, you can schedule these jobs to run as often as you'd like. During execution, these jobs will interrogate your chosen database/objects and determine if a reorganization needs to occur by applying your customized reorganization thresholds. If any objects are found to need reorganization, they are dynamically reorganized using any customized options you specify.

You can build space maintenance jobs that will perform functions such as check the validity of your databases and filegroups, repair any found minor corruption, and eliminate unused space in your databases. You can also set the job up to notify you if any problems are found during job execution.

Creating or editing a space Management Job

The Space Management Wizard lets you create space reorganization or maintenance jobs without knowing the code. After creation, jobs can be reused as is, or edited.

To open the Space Management Wizard

  1. Start the Space Analyst and click the Open Wizard button on the Space Analyst toolbar.
    OR
  2. Select Open Wizard from the Space Analyst menu.

The sections below display the fields you may find creating or editing a space management job. Be aware that the sections you see, and the order in which they appear in the wizard vary depending on the selections you make and the database platform.

New or Used Task

Here you choose whether to create a new space management task or whether to use a previously saved task. If you want to reuse a previously created job, you can type or browse to find the previously saved task definition file (*.sptsk).

Type of Space Management Task

Required Information Description

Create standard reorganization job

Select to build a reorganization job for a database or the objects you specify.

Create reorganization analyst job

Select to create and deploy a reorganization job that runs periodically to detect and automatically correct space inefficiencies.

Create space maintenance job

Select to build a space maintenance job that validates database structures and performs storage maintenance tasks.

Database/Objects to Be Part of the Reorganization or Maintenance Task

Choose the databases and/or objects you want to include in the job you are creating. Simply move items from the tree in the right pane to the left pane by clicking the right arrow. You can amend your selections using the left arrow. The selection is limited to entire databases for a space maintenance job.

Custom Reorganization

Required Information Description

Perform no customizations

When you choose this option, all default object and database attributes are used.

Perform global customizations

This option gives you the chance to define options that will be globally applied to all objects being reorganized.

Perform specific object customizations

Here you individualize options for specific objects being reorganized.

Save the Operation

If you want to save the operation, you can use the default name supplied or type another in the upper panel. If you don’t want to save the operation, remove the default name. Browse to the location where you want to save the file. The job as it was created appears in the read-only preview panel. To make changes, you must go back and change your selections or open the job in an ISQL window prior to execution.

Execution Preferences for a Reorganization Operation

Option Description

Open the reorganization script in an ISQL editor.

The reorganization script will be generated and then opened in a new ISQL editing session.

Perform reorganization now

The reorganization is run immediately with results presented afterward in an ISQL window.

Schedule the reorganization script for later execution.

The reorganization script will be generated and then scheduled to run at a specified date/time.

Global Customizations

Option Description

Relocate to file group

Select to perform an extended alter and move the table(s) to the requested file group using the ON keyword in the table creation DDL.

Perform sorted reorg using table's clustered index

Select to rebuild of any table's clustered index.

Perform online reorganization where possible

MICROSOFT SQL SERVER ONLY: Select to use the DBCC INDEXDEFRAG command to perform an online rebuild of every table's index(es).

View reorganization output messages

Select for the WITH NO_INFOMSGS clause of any DBCC command to not be used.

Defragment tables in DROP/CREATE fashion

Select to defragment tables in DROP/CREATE fashion.

Relocate to file group

Select for DROP_EXISTING clause instead of doing a drop and then create.

Fill Factor

Select to specify a new fill factor for the DBCC DBREINDEX command or a create/drop of an existing index.

Index Reorganization

Rebuild:,Drop/Create, or Reorganize

Perform online reorganization where possible

Select to use the DBCC INDEXDEFRAG command to perform an online rebuild of every index.

View reorganization output messages

Select for the WITH NO_INFOMSGS clause of any DBCC command to not be used.

Specific Object Customization

Option Description

Select

Drop-down list lets you switch between indexes and tables and see the particular objects for each.

Relocate to file group

Select a clause instead of doing a drop and then create.

Fill Factor

For indexes, select to specify a new fill factor.

Perform sorted reorganization using table's clustered index

For tables, select to perform sorted reorganization using table's clustered index.

Index reorganization

Rebuild: This is the default. Drop/Create Reorganize

Perform online reorganization where possible

Select to perform an online rebuild of every index.

View reorganization output messages

Select for the WITH NO_INFOMSGS clause of any DBCC command to not be used.

Defragment tables in DROP/CREATE fashion

For tables, select to defragment tables in DROP/CREATE fashion.

Threshold Criteria for Object Reorganization

Option Description

Table: Average Page Density

Default is less than or equal to 50.

Scan Density Percent

Default is less than or equal to 0.

Logical Fragmentation

Greater than or equal to 25 is the default value.

Extent Fragmentation

Default is greater than or equal to 50.

Forwarded Record Percent

Default is greater than or equal to 50.

Index: Average Fragmentation

Default is greater than or equal to 25.

Scan Density Percent

Default is less than or equal to 0.

Logical Fragmentation

Greater than or equal to 25 is the default value.

Extent Fragmentation

Default is greater than or equal to 50.

Index Tree Depth

Default is greater than or equal to 3.

Average Page Density

Default is less than or equal to 50.

Note: All defaults are editable. Also, all current metrics for these criteria can be found on the Objects tab >Space Diagnostics view. The default thresholds are visible when you choose Threshold Options from the Space Analyst drop-down menu.

Integrity Check Options

Option Description

Do not perform any integrity checks

Self-explanatory.

Check databases

Checks the database integrity for database(s) you identified earlier.

Check file groups

Self-explanatory.

Check specific database objects

In a later step you are able to select the particular objects.

Include index checks for all tables

This is the default and isn’t actually optional.

Attempt to repair minor problems

Enabled as a default for database or database objects integrity checks. You can unselect.

Include check for database catalogs

Enabled as a default for database integrity checks. You can unselect.

Correct data dictionary inaccuracies

Enabled as a default for database integrity checks. You can unselect.

Check data purity

Verifies that columns only have data in the correct ranges, and so on.

Integrity Check for Specific Database Objects

Here you select objects for the database(s) you have included in this maintenance job.

Unused Space Options

Option Description

Do not eliminate unused space

Self-explanatory

Eliminate unused space for databases

Select to only truncate space for databases larger than a specific size (in MB).

Only eliminate space when database is larger than

Available if you selected Eliminate unused space for databases option. Lets you specify maximum database size in MB.

Amount of free space to leave for database

Lets you specify how much free space is to remain after a space truncation.

Eliminate unused space for specific database tables

Self-explanatory.

Problem Notification

Option Description

No, do not notify me

Select to not have Space Analyst notify you of any errors.

Yes, notify me in the following ways.

Select to have Space Analyst notify you of any errors and then type an e-mail or net send address.

Space Analyst - Threshold Options

The Space Analyst Reorganization Thresholds Options dialog lets you customize the various reorganization thresholds that can be used to flag storage and data objects that need to be reorganized. The thresholds you specify will apply to the Space Analysis Reports.

Important Notes

None

The table below describes the options and functionality on the Space Analyst Thresholds Options dialog for IBM DB2 for Linux, Unix, and Windows:

Option Description

Table Reorganization Thresholds

The table thresholds apply to the displays used in tab 4 of the Space Analyst and the queries that are run to present the various customized displays. They are also used for the tablespace analysis report.

Index Reorganization Thresholds

The index thresholds apply to the displays used in tab 5 of the Space Analyst and the queries that are run to present the various customized displays. They are also used for the tablespace analysis report.

The table below describes the options and functionality on the Space Analyst Thresholds Options dialog for Oracle:

Option Description

Tablespace and Datafile Fragmentation Thresholds

The tablespace and datafile thresholds apply to the tablespace and datafile fragmentation index. The default for each is 50 - meaning, flag any tablespace or datafile whose fragmentation index is less than 50. They are also used for the tablespace analysis report.

Table Reorganization Thresholds

The table thresholds apply to the displays used in tab 4 of the Space Analyst and the queries that are run to present the various customized displays. They are also used for the tablespace analysis report.

Index Reorganization Thresholds

The index thresholds apply to the displays used in tab 5 of the Space Analyst and the queries that are run to present the various customized displays. They are also used for the tablespace analysis report.

The table below describes the options and functionality on the Space Analyst Thresholds Options dialog for Sybase ASE:

Option Description

Database and Log Space Thresholds

The database and log space thresholds apply to the database and log fragmentation index. The default for each is 80 - meaning, flag any database or log whose fragmentation index is less than 80. The database and log space thresholds apply to any grid that shows free space percent for those entities.

Table Reorganization Thresholds

The table thresholds apply to the displays used in tab 5 of the Space Analyst and the procedures that are run to present the various customized displays. They are also used for the database analysis report.

Index Reorganization Thresholds

The index thresholds apply to the displays used in tab 5 of the Space Analyst and the queries that are run to present the various customized displays. They are also used for the database analysis report.

The table below describes the options and functionality on the Space Analyst Thresholds Options dialog for SQL Server:

Option Description

Database and Log Space Thresholds

The database and log space thresholds apply to the database and log fragmentation index. The default for each is 80 - meaning, flag any database or log whose fragmentation index is less than 80. The database and log space thresholds apply to any grid that shows free space percent for those entities.

Table Reorganization Thresholds

The table thresholds apply to the displays used in tab 5 of the Space Analyst and the procedures that are run to present the various customized displays. They are also used for the database analysis report.

Index Reorganization Thresholds

The index thresholds apply to the displays used in tab 5 of the Space Analyst and the queries that are run to present the various customized displays. They are also used for the database analysis report.

Fragmentation Analysis Mode

Detailed is the default. The other options are Sampled and Limited. This option serves as a parameter that’s passed to the space diagnostic procedures use on the Objects tab for the reorganization summary and the detail views for indexes and tables.

Completing the Space Analyst Thresholds Options Dialog Box for Space Analyst

To complete this dialog box, do the following:

  1. On the Analysts Toolbar, click the Options button.
    The Space Analyst opens the dialog box.
  2. Review thresholds.
  3. Click OK.