# Database Optimization

**URL:** <https://discuss.workflowgen.com/t/database-optimization/2080>\
**Category:** Database\
**Created:** [August 5, 2022, 6:12pm UTC](https://discuss.workflowgen.com/t/database-optimization/2080 "2022-08-05T18:12:10Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![lyelle](https://yyz2.discourse-cdn.com/flex030/user_avatar/discuss.workflowgen.com/lyelle/32/455_2.png) [@lyelle](https://discuss.workflowgen.com/u/lyelle)\
**Post date:** [August 5, 2022, 6:12pm UTC](https://discuss.workflowgen.com/t/database-optimization/2080/1 "2022-08-05T18:12:10Z")

</div>

## Overview

To maintain a healthy database performance or if your database is experiencing slow response time due to heavy usage, we suggest to improve its performance behavior by adding a SQL Server scheduled job (i.e. automated task) that will defragment the database table indexes and reduce the size of the SQL log file to free up disk space.

## Applies to

- SQL Server

## Procedure

1. In the **Object Explorer** of the Sql Server Management Studio (SSMS), expand the **SQL Server Agent** section to add a new job.

2. Right-click on the `Jobs` folder and choose **New job…**

3. Configure the new job’s **General** tab:

4. After setting the general configuration, go to the **Steps** page and click **New…**

5. On the **General** tab:

6. On the **Advanced** tab:

7. On the **Steps** page, add a second step.

8. On the **General** tab:

9. On the **Advanced** tab:

10. Go to the **Schedules** page and click **New…** , then configure as follows:

## Summary of the scheduled job

 ![Untitled (2)](https://canada1.discourse-cdn.com/flex030/uploads/workflowgen/original/1X/64f589cb91a3f7bc53b5d9c8037ab64b282b08f3.png)

---

<div class="post-metadata">

**Author:** ![hcampeau](https://avatars.discourse-cdn.com/v4/letter/h/bc8723/32.png) [@hcampeau](https://discuss.workflowgen.com/u/hcampeau)\
**Post date:** [October 28, 2022, 7:31pm UTC](https://discuss.workflowgen.com/t/database-optimization/2080/2 "2022-10-28T19:31:05Z")

</div>

I see your defrag command is using REORGANIZE vs REBUILD. I’m not a DBA but is there a preference between the two?

---

<div class="post-metadata">

**Author:** ![lyelle](https://yyz2.discourse-cdn.com/flex030/user_avatar/discuss.workflowgen.com/lyelle/32/455_2.png) [@lyelle](https://discuss.workflowgen.com/u/lyelle)\
**Post date:** [October 28, 2022, 8:02pm UTC](https://discuss.workflowgen.com/t/database-optimization/2080/3 "2022-10-28T20:02:13Z")

</div>

Hello, hcampeau,

The reason we are using REORGANIZE over REBUILD is mostly to minimize downtime.

REORGANIZE is a lightweight solution that doesn’t require a schema mod lock, that can be used not matter the SQL server edition you possess. If interrupted or stop it will just stop reorganizing at the point where it was.

REBUILD is heavy in its usage and also have restriction depending on the SQL server edition. If the rebuild is stopped it will roll back which can impact the performance and downtime.

- With the standard edition, the rebuild is done as an offline operation and is single-threaded.
- With the Enterprise edition the rebuild can be done online but will be using a schema modification lock, which can impact the downtime of the SQL server.

You may decide to use REBUILD instead of REORGANIZE, but do take into consideration the different impacts.

Regards,  
Lynn