/ Microsoft

Defragmentación a partir de la detección de la fragmentación de la DB en SQL Server

Hola a todos!

Compartiré un script que permite desfragmentar automáticamente las tablas que están muy fragmentadas de una DB en SQL Server. Es muy útil ya que ayuda a acelerar y simplificar el mantenimiento de la DB.

-- Set variables BEFORE RUN THIS SQL
-- richard.24.se@gmail.com
-- *********************************************************************************************
SET NOCOUNT ON
DECLARE @reorg_frag_thresh float SET @reorg_frag_thresh = 10.0
DECLARE @rebuild_frag_thresh float SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor tinyint SET @fill_factor = 0
DECLARE @report_only bit SET @report_only = 0
DECLARE @page_count_thresh smallint SET @page_count_thresh = 1
-- *********************************************************************************************
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 @page_count int
DECLARE @command nvarchar(4000)
DECLARE @intentions nvarchar(4000)
DECLARE @table_var TABLE(
objectid int,
indexid int,
partitionnum int,
frag float,
page_count int
)
INSERT INTO
@table_var
SELECT
[object_id] AS objectid,
[index_id] AS indexid,
[partition_number] AS partitionnum,
[avg_fragmentation_in_percent] AS frag,
[page_count] AS page_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE
[avg_fragmentation_in_percent] > @reorg_frag_thresh
AND
page_count > @page_count_thresh
AND
index_id > 0
DECLARE partitions CURSOR FOR
SELECT * FROM @table_var
OPEN partitions
WHILE (1=1) BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag, @page_count
IF @@FETCH_STATUS < 0 BREAK
SELECT
@objectname = QUOTENAME(o.[name]),
@schemaname = QUOTENAME(s.[name])
FROM
sys.objects AS o WITH (NOLOCK)
JOIN sys.schemas AS s WITH (NOLOCK)
ON s.[schema_id] = o.[schema_id]
WHERE
o.[object_id] = @objectid
SELECT
@indexname = QUOTENAME([name])
FROM
sys.indexes WITH (NOLOCK)
WHERE
[object_id] = @objectid AND
[index_id] = @indexid
SELECT
@partitioncount = count (*)
FROM
sys.partitions WITH (NOLOCK)
WHERE
[object_id] = @objectid AND
[index_id] = @indexid
SET @intentions =
@schemaname + N'.' +
@objectname + N'.' +
@indexname + N':' + CHAR(13) + CHAR(10)
SET @intentions =
REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
@intentions
SET @intentions = @intentions +
N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +
N' PAGE COUNT: ' + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10)
IF @frag < @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
SET @command =
N'ALTER INDEX ' + @indexname +
N' ON ' + @schemaname + N'.' + @objectname +
N' REORGANIZE; ' +
N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname +
N' ' + @indexname + ';'
END
IF @frag >= @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)
SET @command =
N'ALTER INDEX ' + @indexname +
N' ON ' + @schemaname + N'.' + @objectname +
N' REBUILD'
END
IF @partitioncount > 1 BEGIN
SET @intentions = @intentions +
N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
SET @command = @command +
N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
END
IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
SET @intentions = @intentions +
N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
SET @command = @command +
N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
END
IF @report_only = 0 BEGIN
SET @intentions = @intentions + N' EXECUTING: ' + @command
PRINT @intentions
EXEC (@command)
END ELSE BEGIN
PRINT @intentions
END
PRINT @command
END
CLOSE partitions
DEALLOCATE partitions
GO
Alter table Relationship rebuild
view raw Detect fragmentaion and apply defragmention DB.sql hosted with ❤ by GitHub

Hasta pronto!