当数据库表使用久了，特别是数据频繁增删改后，索引会产生碎片，会影响索引的效率，这就需要及时优化。
官方建议大于百分三十的索引是要重建的.
索引相关语句
---------------------------------------------重新组织索引----------------------------------------
ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee
REORGANIZE ;
GO
--------------------------------------------重新组织表中所有的索引--------------------------------------------
ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE
-------------------------------------------重新生成的索引--------------------------------------------
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO
---------------------------------------------重新生成表中所有的索引--------------------------------------------
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
有关重新生成，组织索引的操作详见：
SQL Server 重新组织生成索引 - pursuer.chen
查看碎片大于30%的索引
select object_id= object_id,indexid = index_id,partitionnum = partition_number,frag= avg_fragmentation_in_percent
into #work_to_do
from sys.dm_db_index_physical_stats(db_id(), null, null , null, 'LIMITED') --dm_ph_stats join sys.dm_db_partition_stats dm_pa_st on dm_ph_stats.object_id=dm_pa_st.object_id
where avg_fragmentation_in_percent >= 30.0
and index_id > 0
and object_id in (select distinct a.object_id from sys.dm_db_partition_stats a join sys.indexes b
on a.object_id=b.object_id and a.index_id=b.index_id
where a.index_id>0
and a.in_row_data_page_count>1280 )
select a.object_id,a.name,a.type_desc,b.partitionnum ,b.frag,b.indexid from sys.indexes a , #work_to_do b
where a.object_id=b.object_id and a.index_id=b.indexid-- where object_id in(select objectid from #work_to_do)
drop table #work_to_do
批量重新组织或重新生成索引的语句
declare @NumLessthan30 int
declare @NumGreaterthan30_1 int
declare @NumGreaterthan30_2 int
set @NumLessthan30=0
set @NumGreaterthan30_1=0
set @NumGreaterthan30_2=0
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' REORGANIZE ';
set @NumLessthan30=@NumLessthan30+1;
end
IF @avg_fip>=30.0 AND @Indexid=1
BEGIN
IF EXISTS (SELECT * FROM SYS.columns WHERE OBJECT_ID=@Objectid AND max_length in(-1,16))
SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD ';
ELSE
SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD '+N' WITH (ONLINE = ON)';
set @NumGreaterthan30_1=@NumGreaterthan30_1+1;
END
IF @avg_fip>=30.0 AND @Indexid>1
BEGIN
IF EXISTS (SELECT * FROM SYS.index_columns IC INNER JOIN SYS.columns CS ON CS.OBJECT_ID=IC.OBJECT_ID AND CS.column_id=IC.column_id WHERE IC.OBJECT_ID=@Objectid AND IC.index_id=@Indexid AND CS.max_length in(-1,16) )
SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD ';
ELSE
SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD '+N' WITH (ONLINE = ON)';
set @NumGreaterthan30_2=@NumGreaterthan30_2+1;
END
PRINT @command
--指定1秒后执行
--waitfor delay '0:0:1.00'
--EXEC(@command)
FETCH NEXT FROM IX_Cursor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fip
END
CLOSE IX_Cursor
DEALLOCATE IX_Cursor
print '@NumLessthan30='
print @NumLessthan30
print '@NumGreaterthan30_1='
print @NumGreaterthan30_1
print '@NumGreaterthan30_2='
print @NumGreaterthan30_2
可以根据需要修改
延时执行的语句补充
因为里面用到的延时执行，所以补充下该函数的用法
SQL有定时执行的语句WaitFor。
语法格式：waitfor {delay 'time'|time 'time'}
delay后面的时间是需要延迟多长时间后执行。
time后面的时间是指定何时执行，格式为'HH:MM:SS',不支持日期
例：
--指定10:00执行
Begin
waitfor time '10:00'
select * from tablename
end
--指定等待1小时后执行
begin
waitfor delay '1:00'
select * from tablename
end
--指定等待毫秒级别后执行
waitfor delay '0:0:0.01'
--指定等待1秒后执行
waitfor delay '0:0:1.00'
