• Article

Disable Indexes (General Page)

When disabled, indexes are not kept up-to-date and are not used by queries. Disabling a clustered index marks the table as offline and prevents it from being used.

Using the Disable Indexes General Page

  • Click OK to disable the indexes listed in the Indexes to disable grid.

  • To remove an index from the Indexes to disable grid, select the index and then press DELETE.

  • To sort the indexes in the Indexes to disable grid, click on title in the header row.

Reasons to Disable Indexes

  • Indexes are sometimes disabled to reduce the overhead when loading large amounts of data into a table.

  • Indexes are sometimes disabled to test query performance with different index configurations.

  • Special-purpose indexes are sometimes created for infrequent activities, such as a set of monthly reports, and they are disabled when not needed.

Indexes to Disable grid

  • Index Name
    Displays the name of the index. During execution, this column also displays an icon representing the status.

  • Table Name
    Displays the name of the table or view that the index was created on.

  • Index Type
    Displays the type of the index: Clustered, Nonclustered, Spatial, or XML.

  • Status
    Displays the status of the disable operation. Possible values after execution are:

    • Blank

      Prior to execution Status is blank.

    • In progress

      Disabling of the indexes has been started but is not complete.

    • Success

      The disable operation completed successfully.

    • Error

      An error was encountered during the index disable operation, and the operation did not complete successfully.

    • Stopped

      The disable of the index was not completed successfully because the user stopped the operation.

  • Message
    Provides the text of error messages during the disable operation. During execution, errors appear as hyperlinks. The text of the hyperlinks describes the body of the error. The Message column is rarely wide enough to read the full message text. There are two ways to get the full text:

    • Move the mouse pointer over the message cell to display a ToolTip with the error text.

    • Click the hyperlink to display a dialog box displaying the full error.

Enabling Indexes

To enable the indexes again, right-click the index and then click Rebuild, or right-click Indexes and then click Rebuild All.