数据库重组或者重建索引
标签：
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did160652
数据库重组或者重建索引
功能描述：自动重新组织或重新生成数据库中平均碎片超过 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 );
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 (), NULL , NULL , NULL , ‘ LIMITED ‘ )
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 ;
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN 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 ;
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 partitions;
DEALLOCATE partitions;
DROP TABLE #work_to_do;
GO
阅读：26次