sqlserver索引資訊檢視及重新生成組織索引

當資料庫表使用久了，特别是資料頻繁增删改後，索引會産生碎片，會影響索引的效率，這就需要及時優化。

官方建議大于百分三十的索引是要重建的.

索引相關語句

---------------------------------------------重新組織索引----------------------------------------

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'}

--指定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'