首先创建一Procedure  ,然后使用任务自动每天晚上执行，

就可以对碎片和索引进行优化

使用这个查询可以看出某些Table 有多少碎片，

SELECT object_name(a.object_id) [TableName] ,a.index_id ,name [IndexName] ,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID() , NULL , NULL, NULL, NULL ) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

The RebuildIndex method re-creates an index implementing a Microsoft SQL Server PRIMARY KEY or UNIQUE key constraint.

SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
DECLARE @dbId int;
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
 DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index IDs to names.
set @dbId=DB_ID();
SELECT
 object_id AS objectid,
 index_id AS indexid,
 partition_number AS partitionnum,
 avg_fragmentation_in_percent AS frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (@dbId, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
 SELECT @objectname = o.name, @schemaname = s.name
 FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id
 WHERE o.object_id = @objectid;

 SELECT @indexname = name
 FROM sys.indexes
 WHERE object_id = @objectid AND index_id = @indexid;

 SELECT @partitioncount = count (*)
 FROM sys.partitions
 WHERE object_id = @objectid AND index_id = @indexid;

 -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
 IF @frag < 30.0
 BEGIN;
  SELECT @command = 'ALTER INDEX [' + @indexname + '] ON ' + @schemaname + '.[' + @objectname + '] REORGANIZE';
  IF @partitioncount > 1
   SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
  EXEC (@command);
 END;
 IF @frag >= 30.0
 BEGIN;
  SELECT @command = 'ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.[' + @objectname + '] REBUILD';
  IF @partitioncount > 1
   SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
  EXEC (@command);
 END;
 PRINT 'Executed ' + @command;
 FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- drop the temporary table
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;