Creating a Job for Scheduled Instance Maintenance

Scenarios

After a DB instance runs for a period of time, the system performance deteriorates because index fragments increase and statistics are not updated in a timely manner. You are advised to create a SQL agent job to periodically re-create indexes, update statistics, and shrink the database.

Creating an Index Re-creating Job

1. Start the SQL Server Management Studio client and log in to it as user rdsuser.

   

2. Right-click SQL Server Agent and choose New > Job to create an SQL agent job.

   

3. Enter the name and description, and click OK.

   

4. Select Steps and click New to add an execution step.
   Figure 1 Adding an execution step
   

5. Enter the step name, type, and command, and click OK. Set Command to the SQL statements that need to be executed periodically. When the number of index fragments reaches a specified value, for example, 30%, the index can be recreated.
   Figure 2 Configure parameters
   

   Run the following SQL statement to recreate the index because the number of index fragments of all tables in the specified dbname exceeds 30%:

   use [dbname]
   SET NOCOUNT ON
   DECLARE @Objectid INT, @Indexid INT,@schemaname VARCHAR(100),@tablename VARCHAR(300),@ixname VARCHAR(500),@avg_fip float,@command VARCHAR(4000)
   DECLARE IX_Cursor CURSOR FOR
   SELECT A.object_id,A.index_id,QUOTENAME(SS.name) AS schemaname,QUOTENAME(OBJECT_NAME(B.object_id,B.database_id))as tablename ,QUOTENAME(A.name) AS ixname,B.avg_fragmentation_in_percent AS avg_fip FROM sys.indexes A inner join sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') AS B 
   ON A.object_id=B.object_id and A.index_id=B.index_id 
   INNER JOIN sys.objects OS ON A.object_id=OS.object_id
   INNER JOIN sys.schemas SS ON OS.schema_id=SS.schema_id
   WHERE B.avg_fragmentation_in_percent>10 and B.page_count>20 AND A.index_id>0 AND A.is_disabled<>1
   --AND OS.name='book'
   ORDER BY tablename,ixname
   OPEN IX_Cursor
   FETCH NEXT FROM IX_Cursor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fip
   WHILE @@FETCH_STATUS=0
   BEGIN
   IF @avg_fip>=30.0
   BEGIN
   SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD ';
   END
   --PRINT @command
   EXEC(@command)
   FETCH NEXT FROM IX_Cursor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fip
   END
   CLOSE IX_Cursor
   DEALLOCATE IX_Cursor

   

   In the preceding SQL statements, you only need to change the value of Use [dbname] in the first line to the specified database name.

   If you need to execute the SQL statements for all databases, modify the SQL statements to add cyclic execution for all databases.

6. Select Schedules and click New to add a scheduled execution plan.
   Figure 3 Adding a scheduled execution plan
   

7. Add a schedule that is executed once a month, modify the daily frequency and duration, and click OK.
   Figure 4 Configuring a scheduled execution plan
   

8. View that the job has been created.
   Figure 5 job
   

9. Right-click the job and choose Start Job at Step to manually run the job.
   Figure 6 Runing a job.
   

10. Check whether the job can run properly. If the job runs normally, the maintenance job for periodically recreating the indexes of the db1 database has been created.

    

Updating Statistics

1. Perform 1 to 4.
2. Enter the step name, type, and command, and click OK. Set Command to the stored procedure for updating statistics. For details, see Updating Database Statistics.
   Figure 7 Updating statistics
   

3. Select Schedules and click New to add a scheduled execution plan.
   Figure 8 Adding a scheduled execution plan
   

4. Add a schedule that is executed once a month, modify the daily frequency and duration, and click OK.
   Figure 9 Configuring a scheduled execution plan
   

5. View that the job has been created.
   Figure 10 Updating statistics job
   

6. Right-click the job and choose Start Job at Step to manually run the job.

Shrinking the Database Periodically

1. Perform 1 to 4.
2. Enter the step name, type, and command, and click OK. Set Command to the SQL commands for shrinking the database.

   EXEC [master].[dbo].[rds_shrink_database_log] @dbname='myDbName';

   Set @dbname to the database name.

3. Select Schedules and click New to add a scheduled execution plan.
   Figure 11 Adding a scheduled execution plan
   

4. Add a schedule that is executed once a month, modify the daily frequency and duration, and click OK.
   Figure 12 Configuring a scheduled execution plan
   

5. Right-click the job and choose Start Job at Step to manually run the job.