Database Optimization

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:

    • Insert a representative name such as Defrag and shrink log.
    • Leave the Owner and Category as-is.
    • Insert a representative description, such as Defrag the WorkflowGen database and shrink the WorkflowGen database log.
  4. After setting the general configuration, go to the Steps page and click New…

  5. On the General tab:

    • Give the step a name (e.g. Launch defrag).
    • Leave the Type value set to Transact-SQL script (T-SQL).
    • Leave Run as empty.
    • Select the database you want the job to run on.
    • Insert the following command:
    DECLARE @TableName nvarchar(255), @SqlQuery nvarchar(max)
    SELECT @TableName = min(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
                
    WHILE @TableName is not null
    BEGIN
    SET @SqlQuery='ALTER INDEX ALL ON [' + @TableName + '] REORGANIZE;'
    EXEC SP_EXECUTESQL @SqlQuery
    SELECT @TableName = min(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' and TABLE_NAME > @TableName          
    END
    
  6. On the Advanced tab:

    • On success action select the Go to the next step option.
    • Retry and Interval can be set to 0.
    • For Failure action, select Quit the job reporting.
    • The rest can be left empty.
  7. On the Steps page, add a second step.

  8. On the General tab:

    • Give the step a name (Shrink Log).
    • Leave Type set to Transact-SQL script (T-SQL).
    • Leave Run as empty.
    • Select the database you want the job to run on.
    • Insert the following command:
    DBCC SHRINKFILE (WFGEN_Log, 1)
    
  9. On the Advanced tab:

    • For Success action, select the Quit the job reporting success option.
    • Retry and Interval can be set to 0.
    • For Failure action, select the Quit the job reporting failure.
    • The rest can be left empty.
  10. Go to the Schedules page and click New…, then configure as follows:

    • Insert a representative name for this new schedule.
    • Make sure the Schedule type is set to Recurring and Enabled is checked.
    • We recommend setting Frequency to Daily and Recurs every to 1 to 3 days, depending on the usage volume of the database. (The higher the volume, the smaller the occurrence interval should be.)
    • We recommend setting Daily frequency to Occurs once at a time outside of business hours.
    • We recommend setting Duration to No end date.

Summary of the scheduled job