SQL Server 索引碎片整理

索引碎片整理的四种方法：

1）删除索引并重建

2）使用 DROP_EXISTING 语句重建索引

3）使用 ALTER INDEX REBUILD 语句重建索引

4）使用 ALTER INDEX REORGANIZE 重新组织索引

--1.查看碎片SELECT DB_NAME() AS DatbaseName ,  SCHEMA_NAME(o.Schema_ID) AS SchemaName ,  OBJECT_NAME(s.[object_id]) AS TableName ,  i.name AS IndexName ,  ROUND(s.avg_fragmentation_in_percent, 2) AS [Fragmentation %] ,  CASE WHEN avg_fragmentation_in_percent > 30 THEN '严重碎片，索引需要重建'    WHEN avg_fragmentation_in_percent >= 5     AND avg_fragmentation_in_percent < 30 THEN '轻度碎片，索引需要重新组织'    ELSE '正常状态'  END 提示FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s  INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id  INNER JOIN sys.objects o ON i.object_id = O.object_idORDER BY [Fragmentation %] DESC --2.整理碎片（建议在空闲时间运行，尤其不要在生产环境运行）SET NOCOUNT ONDECLARE @Objectid INT , @Indexid INT , @schemaname VARCHAR(100) , @tablename VARCHAR(300) , @ixname VARCHAR(500) , @avg_fip FLOAT , @command VARCHAR(4000)DECLARE IX_Cursor CURSORFOR 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 avg_fip DESC ,   tablename ,   ixnameOPEN IX_CursorFETCH NEXT FROM IX_Cursor INTO @Objectid, @Indexid, @schemaname, @tablename, @ixname, @avg_fipWHILE @@FETCH_STATUS = 0  BEGIN   --碎片率>5%或<=30%，索引重组  IF @avg_fip < 30.0    SET @command = N'ALTER INDEX ' + @ixname + N' ON ' + @schemaname    + N'.' + @tablename + N' REORGANIZE ';      --碎片率>=30%，索引重建  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)';   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)';   END  --打印命令，单独执行  PRINT @command    --直接执行命令  --EXEC(@command)   FETCH NEXT FROM IX_Cursor INTO @Objectid, @Indexid, @schemaname,@tablename, @ixname, @avg_fip END CLOSE IX_CursorDEALLOCATE IX_Cursor 

 

原文转载：http://www.shaoqun.com/a/489513.html

sgshop：https://www.ikjzd.com/w/1982

粉丝通：https://www.ikjzd.com/w/743

livingsocial：https://www.ikjzd.com/w/714.html

索引碎片整理的四种方法：1）删除索引并重建2）使用DROP_EXISTING语句重建索引3）使用ALTERINDEXREBUILD语句重建索引4）使用ALTERINDEXREORGANIZE重新组织索引--1.查看碎片SELECTDB_NAME()ASDatbaseName,SCHEMA_NAME(o.Schema_ID)ASSchemaName,OBJECT_NAME(s.[object_id]
netporter：https://www.ikjzd.com/w/2132
shirley：https://www.ikjzd.com/w/1684
长隆水上乐园闭园时间?广州长隆水上乐园什么时候闭园?：http://tour.shaoqun.com/a/19234.html
情人节快到了，深圳哪里有适合情侣去玩的地方啊？：http://tour.shaoqun.com/a/39757.html
日本跨境电商新平台Starday面向中国卖家招商，前6个月0佣金 ：https://www.ikjzd.com/home/104400