数据库重组或者重建索引
时间：2021-07-01 10:21:17
帮助过：3人阅读
功能描述：自动重新组织或重新生成数据库中平均碎片超过 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
数据库重组或者重建索引
标签：
