SELECT a. avg_fragmentation_in_percent AS 碎片, c. NAME + '.' + b. NAME AS 表名
FROM sys.dm_db_index_physical_stats ( DB_ID (), NULL, NULL , NULL, 'LIMITED' ) AS a INNER JOIN sys.objects AS b ON a. object_id = b. object_id INNER JOIN sys.schemas AS c ON c. schema_id = b. schema_id INNER JOIN sys.indexes AS d ON d. object_id = a. object_id AND d. index_id= a. index_id INNER JOIN ( SELECT object_id , index_id, partitioncount= COUNT (*) FROM sys.partitions GROUP BY object_id , index_id) AS e ON e. object_id = a. object_id AND e. index_id= a. index_idWHERE a. avg_fragmentation_in_percent > 10.0 AND a. index_id > 0 AND d. Name IS NOT NULL
直接在查询分析器里执行前面加上 use DBName--指定DB建议楼主看一本入门的书,你这样问大家沟通起来费力啊
--管理
--SQL Server代理
--右键作业
--新建作业
--"常规"项中输入作业名称
--"步骤"项
--新建
--"步骤名"中输入步骤名
--"类型"中选择"Transact-SQL 脚本(TSQL)" --如:下面的SQL腳本
--"数据库"选择执行命令的数据库
--"命令"中输入要执行的语句:
EXEC 存储过程名 ... --该存储过程用于创建表--确定
--"调度"项
--新建调度
--"名称"中输入调度名称
--"调度类型"中选择你的作业执行安排
--如果选择"反复出现"
--点"更改"来设置你的时间安排
http://blog.csdn.net/roy_88/article/details/6163583用作業調度上面方法,數據量不大建議一個月一次
這類日常工作,建議定期讓Job去執行上面優化方法有判斷是整理還是重建,你可參照
會用就行了,是判斷碎片<30%用整理,其它用重建
Job在6樓貼了方法
FROM sys.dm_db_index_physical_stats ( DB_ID (), NULL, NULL , NULL, 'LIMITED' ) AS a INNER JOIN sys.objects AS b ON a. object_id = b. object_id INNER JOIN sys.schemas AS c ON c. schema_id = b. schema_id INNER JOIN sys.indexes AS d ON d. object_id = a. object_id AND d. index_id= a. index_id INNER JOIN ( SELECT object_id , index_id, partitioncount= COUNT (*) FROM sys.partitions GROUP BY object_id , index_id) AS e ON e. object_id = a. object_id AND e. index_id= a. index_idWHERE a. avg_fragmentation_in_percent > 10.0 AND a. index_id > 0 AND d. Name IS NOT NULL
use DBName--指定DB建议楼主看一本入门的书,你这样问大家沟通起来费力啊