[推荐]数据库索引碎片的自动重建或重组

[推荐]数据库索引碎片的自动重建或重组

 

——通过知识共享树立个人品牌。

 

数据库随着使用时间，若不进行相应管理就会越来越慢，优化数据库方法很多，在此不介绍，说一下，如何自动对其索引产生的碎片进行自动重建，索引碎片的增多，有什么影响，在此不阐述！

直接看代码～！节约时间。

---

  

/* 功能描述：自动重新组织或重新生成数据库中平均碎片超过 10％ 的所有分区。

执行此查询需注意以下几点：
1）、需要 VIEW DATABASE STATE 权限。
2）、在不指定数据库名称的情况下，指定 DB_ID 作为第一个参数。
3）、确定当前数据库为80以上。
*/
USE MASTER
SET NOCOUNT  ON;
DECLARE  @objectid  int;
DECLARE  @indexid  int;
DECLARE  @partitioncount  bigint;
DECLARE  @schemaname  nvarchar( 130); 
DECLARE  @objectname  nvarchar( 130); 
DECLARE  @indexname  nvarchar( 130); 
DECLARE  @partitionnum  bigint;
DECLARE  @partitions  bigint;
DECLARE  @frag  float;
DECLARE  @command  nvarchar( 4000); 
--  Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
--  and convert object and index IDs to names.
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 ( db_id( ' TABLE_NAME '),  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.
WHILE ( 1 = 1)
     BEGIN;
         FETCH  NEXT
            FROM partitions
            INTO  @objectid,  @indexid,  @partitionnum,  @frag;
         IF  @@FETCH_STATUS  <  0  BREAK;
         SELECT  @objectname  =  QUOTENAME(o.name),  @schemaname  =  QUOTENAME(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  =  QUOTENAME(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
             SET  @command  = N ' ALTER INDEX  '  +  @indexname  + N '  ON  '  +  @schemaname  + N ' . '  +  @objectname  + N '  REORGANIZE ';
         IF  @frag  >=  30.0
             SET  @command  = N ' ALTER INDEX  '  +  @indexname  + N '  ON  '  +  @schemaname  + N ' . '  +  @objectname  + N '  REBUILD ';
         IF  @partitioncount  >  1
             SET  @command  =  @command  + N '  PARTITION= '  +  CAST( @partitionnum  AS  nvarchar( 10));
         EXEC ( @command);
         PRINT N ' Executed:  '  +  @command;
     END;

--  Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

--  Drop the temporary table.
DROP  TABLE #work_to_do;
GO

 

 

© 2011  EricHu

原创作品，转贴请注明作者和出处，留此信息。

 

------------------------------------------------

cnBlobs:http://www.cnblogs.com/huyong/
CSDN：http://blog.csdn.net/chinahuyong 

  

作者：EricHu（DB、C\S、B\S、WebService、WCF、PM等）
出处：http://www.cnblogs.com/huyong/

Q Q：80368704   E-Mail: 80368704@qq.com
本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。
更多文章请看 [置顶]索引贴——（不断更新中）