创建实例定期维护job-九游平台

操作场景

实例运行一段时间后，由于索引碎片增加，统计信息未及时更新等会导致系统性能有所下降。建议创建定期执行的sql agent job，定期执行索引重建、统计信息更新、数据库收缩操作。

重建索引job

1. 启动sql server managerment studio客户端，使用rdsuser用户登录。

   

2. 选择“sql server agent”，右键单击“new > job”，新建sql agent job。

   

3. 输入名字以及描述信息，单击“ok”。

4. 选择“steps”，单击“new”，添加执行步骤。
   图1 添加执行步骤
   
5. 输入步骤名称，类型及command，完成后单击“ok”。command中填写需要定时执行的sql，当索引碎片达到一定程度，例如30%，可以进行重建。
   图2 步骤信息
   

   执行以下sql，对指定的dbname中的所有表检查索引碎片超过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

   

   上述重建的sql只需要修改第一行（use [dbname]），修改为指定的数据库即可。

   如果需要对所有库执行，请修改sql，添加多所有库的循环执行，此处不做详细示例。

6. 选择“schedules”，单击“new”，添加定时执行计划。
   图3 添加定时执行计划
   
7. 添加每个月执行一次的定时计划，触发时间、定时周期可以修改，完成后单击“ok”。
   图4 定时执行计划
   
8. 上述步骤执行完成后，job建立完毕。
   图5 job
   
9. 选择job，右键单击“start job at step”，手动运行job，检查job是否能正常运行。
   图6 运行job
   
10. 运行正常，定时重建db1数据库的索引的维护job创建完毕。

    

更新统计信息

1. 重复执行重建索引job中的1~4。
2. 输入步骤名称，类型及command，完成后单击“ok”。command中填写更新统计信息的存储过程，存储过程的详细内容请参考。
   图7 更新统计信息
   
3. 选择“schedules”，单击“new”，添加定时执行计划。
   图8 添加定时执行计划
   
4. 添加每个月执行一次的定时计划，触发时间、定时周期可以修改，完成后单击“ok”。
   图9 定时执行计划
   
5. 上述步骤执行完成后，job建立完毕。
   图10 更新统计信息job
   
6. 选择job，右键单击“start job at step”，手动运行job，检查job是否能正常运行。

定时收缩数据库

1. 重复执行重建索引job中的1~4。
2. 输入步骤名称，类型及command，完成后单击“ok”。command中填写收缩数据库的sql命令。

   exec [master].[dbo].[rds_shrink_database_log] @dbname='mydbname';

   其中@dbname参数填写数据库的名字。

3. 选择“schedules”，单击“new”，添加定时执行计划。
   图11 添加定时执行计划
   
4. 添加每个月执行一次的定时计划，触发时间、定时周期可以修改，完成后单击“ok”。
   图12 定时执行计划
   
5. 添加完成后，右键单击“start job at step”，手动运行job，检查job是否能正常运行。