How to setup Automated Indexing

Prerequisites

Before you begin:

• SQL Server Management Studio (SSMS) installed

• Appropriate permissions (sysadmin or maintenance plan privileges)

• SQL Server Agent must be enabled and running

• SQL Server Standard, Enterprise, or Developer Edition

---

Section 1: Verify SQL Server Agent is Running

1. Open SQL Server Management Studio (SSMS).

2. Connect to the SQL Server instance.

3. In Object Explorer, locate SQL Server Agent.

   • If the icon has a red square, it is stopped.

4. Right-click SQL Server Agent → select Start.

   • If prompted for confirmation, click Yes.

Note:
SQL Server Agent is required for all scheduled tasks, including maintenance plans.

---

Section 2: Create a Maintenance Plan for Index Rebuilds

Step 1: Launch the Maintenance Plan Wizard

1. In Object Explorer, expand the Management folder.

2. Right-click Maintenance Plans.

3. Select Maintenance Plan Wizard.

4. Click Next on the introduction screen.

---

Step 2: Name the Maintenance Plan

1. In the Plan Properties screen:

   • Name: Enter a descriptive name, such as:
     “Weekly Index Rebuild Plan”

   • (Optional) Add a description for documentation purposes.

2. Click Next.

---

Step 3: Select the Maintenance Tasks

You will be presented with a list of available maintenance tasks.

1. Check the box for:
   ✔ Rebuild Index

2. Click Next.

---

Step 4: Define the Order of Tasks

If only the Rebuild Index task is selected, it will appear as a single item.

1. Ensure Rebuild Index is listed.

2. Click Next.

---

Section 3: Configure the Rebuild Index Task

This screen determines where and how SQL Server performs index rebuilds.

Step 1: Choose Databases

1. Under Databases, choose one of the following:

   • All databases (includes system DBs—not recommended)

   • All user databases (recommended)

   • These databases: (allows granular selection)

2. If selecting specific databases, check each one you want to include.

---

Step 2: Object Selection

Leave the default option (Include all objects) unless you want to limit the task to specific tables.

---

Step 3: Index Rebuild Options

Configure the following settings:

• ✔ Rebuild indexes — keeps table offline unless using the ONLINE option.

• ✔ Sort results in tempdb (recommended)

  • Improves performance if tempdb has sufficient space.

• ✔ Keep index online while reindexing

  • Available only in Enterprise Edition (allows concurrent access).

• ✔ Compact large object columns (optional).

Review options and adjust based on your environment.

3. Click Next.

---

Section 4: Schedule the Index Rebuild Job

1. On the Select Plan Schedule screen, click Change…
   The New Job Schedule window appears.

Recommended Schedule Settings

• Name: Weekly Index Rebuild

• Frequency: Weekly

• Recurs every: 1 week

• Days: Select a low-usage day (e.g., Sunday)

• Time: Choose an off-peak time (e.g., 2:00 AM)

2. Click OK to save the schedule.

3. Click Next.

---

Section 5: Review and Confirm the Plan

1. Review the summary of configuration settings.

2. Click Finish to create the maintenance plan.

SSMS will create:

• A Maintenance Plan under Management → Maintenance Plans

• A SQL Server Agent Job under SQL Server Agent → Jobs

When the process completes, you should see a confirmation screen showing the results.

---

Section 6: Verify the Plan

Check the Maintenance Plan

1. Expand Management → Maintenance Plans.

2. Locate your plan:
   Weekly Index Rebuild Plan

3. Double-click to review or modify tasks if needed.

---

Check the SQL Agent Job

1. Expand SQL Server Agent → Jobs.

2. Confirm a job with the same plan name exists.

3. Optionally right-click → Start Job at Step… to test the plan manually.

---

Section 7: Monitoring and Logs

SQL Server saves logs for maintenance plans automatically.

To review logs:

1. Expand SQL Server Agent → Jobs

2. Right-click the index rebuild job

3. Select View History

Look for:

• Task success/failure

• Run duration

• Details about index rebuild operations