Skip to main content
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.
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).
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.

​
Check whether to reorganize or rebuild

1

Open the database in SQL Server Management Studio

Select the database containing activity data — typically named <hostname>.EkranActivityDB.
2

Run the fragmentation report

Right-click the database, select Reports > Standard Reports, and run Index Physical Statistics.
3

Read the results

The report lists every index, its current fragmentation level, and a recommended action.
As a general rule: reorganize indexes fragmented below 30%, and rebuild indexes fragmented at 30% or higher.

​
Create a scheduled maintenance plan

1

Start a new Maintenance Plan

In Object Explorer, expand Management, right-click Maintenance Plans, and select New Maintenance Plan. Name it.
2

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”).
3

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.
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.
4

Schedule the plan

Click the calendar icon to open New Job Schedule, and set a recurrence (for example, nightly at 1 AM).
Running it every night isn’t necessary — every six months is a reasonable default.
5

Save the plan

Save the schedule, then save the maintenance plan itself. It appears under Management, alongside its automated job in SQL Server Agent.
6

Test it

Right-click the job and select Execute to confirm it runs correctly.
SQL Server Management Studio showing a maintenance plan and scheduled job

A completed index rebuild maintenance plan and its scheduled job.

Database management

Archive, cleanup, and other database maintenance.

Database server errors

Common MS SQL Server connection issues.