> ## Documentation Index
> Fetch the complete documentation index at: https://syteca.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MS SQL Database Maintenance: Index Rebuilding

> Check index fragmentation and set up a recurring SQL Server Maintenance Plan to rebuild or reorganize indexes for better database performance.

<Note>
  This method of rebuilding indexes with SQL Server Maintenance Plans only works on the Standard and Enterprise editions of MS SQL Server — the Express edition doesn't support it.
</Note>

Indexes speed up MS SQL Server database queries, but fragment over time as data is inserted, updated, and deleted. Regular maintenance keeps performance from degrading.

**Reorganizing** an index is the lighter-weight option — it's always an online operation, so queries and updates can continue against the table while it runs. Prefer it unless you have a specific reason to rebuild instead.

**Rebuilding** an index drops and recreates it. Depending on the index type and MS SQL Database Engine version, this can run offline (faster, but holds object-level locks that block queries for its duration) or online (no locks until a brief one at the very end, and — on supported Engine versions — resumable, so it can be paused and continued later).

<Warning>
  While an online index rebuild is running, every write to the indexed columns has to update an extra copy of the index, which can slightly slow down writes. If you pause a resumable rebuild, that slowdown persists until you either finish or abort it — so if you don't plan to finish, abort rather than leaving it paused.
</Warning>

## Check whether to reorganize or rebuild

<Steps>
  <Step title="Open the database in SQL Server Management Studio">
    Select the database containing activity data — typically named `<hostname>.EkranActivityDB`.
  </Step>

  <Step title="Run the fragmentation report">
    Right-click the database, select **Reports > Standard Reports**, and run **Index Physical Statistics**.
  </Step>

  <Step title="Read the results">
    The report lists every index, its current fragmentation level, and a recommended action.
  </Step>
</Steps>

<Tip>
  As a general rule: reorganize indexes fragmented below 30%, and rebuild indexes fragmented at 30% or higher.
</Tip>

## Create a scheduled maintenance plan

<Steps>
  <Step title="Start a new Maintenance Plan">
    In Object Explorer, expand **Management**, right-click **Maintenance Plans**, and select **New Maintenance Plan**. Name it.
  </Step>

  <Step title="Add the Rebuild Index task">
    Drag a **Rebuild Index Task** from the Maintenance Plan Tasks toolbox onto the plan, and rename it (for example, "Nightly Index Maintenance").
  </Step>

  <Step title="Target the activity database">
    Edit the task: for a local SQL Server, select **Local server connection** and choose `EkranActivityDB` under **Database(s)**. Leave the task's other defaults unchanged.

    <Tip>
      For large deployments, disable **Keep index online**. This disconnects the database from the server during the rebuild — Clients continue buffering data locally and send it once the rebuild finishes.
    </Tip>
  </Step>

  <Step title="Schedule the plan">
    Click the calendar icon to open **New Job Schedule**, and set a recurrence (for example, nightly at 1 AM).

    <Tip>
      Running it every night isn't necessary — every six months is a reasonable default.
    </Tip>
  </Step>

  <Step title="Save the plan">
    Save the schedule, then save the maintenance plan itself. It appears under **Management**, alongside its automated job in **SQL Server Agent**.
  </Step>

  <Step title="Test it">
    Right-click the job and select **Execute** to confirm it runs correctly.
  </Step>
</Steps>

<Frame caption="A completed index rebuild maintenance plan and its scheduled job.">
  <img src="https://mintcdn.com/syteca/0FlD-vkHsBA1azVX/images/administration/database/ms-sql-index-maintenance-plan.png?fit=max&auto=format&n=0FlD-vkHsBA1azVX&q=85&s=397674647058ba521a4f3fcaf384ab86" alt="SQL Server Management Studio showing a maintenance plan and scheduled job" width="361" height="488" data-path="images/administration/database/ms-sql-index-maintenance-plan.png" />
</Frame>

## Related

<CardGroup cols={2}>
  <Card title="Database management" icon="database" href="/docs/administration/deployment/database-management">
    Archive, cleanup, and other database maintenance.
  </Card>

  <Card title="Database server errors" icon="alert-triangle" href="/docs/resources/troubleshooting/database-server-errors">
    Common MS SQL Server connection issues.
  </Card>
</CardGroup>