更新时间:2024-09-05 GMT+08:00
创建实例定期维护job
操作场景
实例运行一段时间后,由于索引碎片增加,统计信息未及时更新等会导致系统性能有所下降。建议创建定期执行的SQL agent job,定期执行索引重建、统计信息更新、数据库收缩操作。
重建索引job
- 启动SQL Server Managerment Studio客户端,使用rdsuser用户登录。
- 选择“SQL Server Agent”,右键单击 ,新建SQL agent job。
- 输入名字以及描述信息,单击“OK”。
- 选择“Steps”,单击“New”,添加执行步骤。
图1 添加执行步骤
- 输入步骤名称,类型及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,添加多所有库的循环执行,此处不做详细示例。
- 选择“Schedules”,单击“New”,添加定时执行计划。
图3 添加定时执行计划
- 添加每个月执行一次的定时计划,触发时间、定时周期可以修改,完成后单击“OK”。
图4 定时执行计划
- 上述步骤执行完成后,job建立完毕。
图5 job
- 选择job,右键单击“Start Job at Step”,手动运行job,检查job是否能正常运行。
图6 运行job
- 运行正常,定时重建db1数据库的索引的维护job创建完毕。
更新统计信息
- 重复执行重建索引job中的1~4。
- 输入步骤名称,类型及Command,完成后单击“OK”。Command中填写更新统计信息的存储过程,存储过程的详细内容请参考更新数据库的统计信息。
图7 更新统计信息
- 选择“Schedules”,单击“New”,添加定时执行计划。
图8 添加定时执行计划
- 添加每个月执行一次的定时计划,触发时间、定时周期可以修改,完成后单击“OK”。
图9 定时执行计划
- 上述步骤执行完成后,job建立完毕。
图10 更新统计信息job
- 选择job,右键单击“Start Job at Step”,手动运行job,检查job是否能正常运行。
定时收缩数据库
- 重复执行重建索引job中的1~4。
- 输入步骤名称,类型及Command,完成后单击“OK”。Command中填写收缩数据库的SQL命令。
EXEC [master].[dbo].[rds_shrink_database_log] @dbname='myDbName';
其中@dbname参数填写数据库的名字。
- 选择“Schedules”,单击“New”,添加定时执行计划。
图11 添加定时执行计划
- 添加每个月执行一次的定时计划,触发时间、定时周期可以修改,完成后单击“OK”。
图12 定时执行计划
- 添加完成后,右键单击“Start Job at Step”,手动运行job,检查job是否能正常运行。