Prezada Camila Leroy,
Para desframentar sua base de dados siga os passos do KB943345.
Abaixo segue o script para rodar no SQL.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_DefragIndexes')
BEGIN
DROP Procedure dbo.proc_DefragIndexes
END
GO
CREATE PROCEDURE dbo.proc_DefragIndexes
(
@onlineDefragThreshold float = 10.0,
@offlineDefragThreshold float = 90.0,
@updateStatsThreshold int = 7
)
AS
BEGIN
set nocount on
DECLARE @objectid int
DECLARE @indexid int
DECLARE @frag float
DECLARE @command varchar(8000)
DECLARE @schemaname sysname
DECLARE @objectname sysname
DECLARE @indexname sysname
declare @AllIndexes table (objectid int, indexid int, fragmentation float)
declare @currentDdbId int
select @currentDdbId = DB_ID()
insert into @AllIndexes
SELECT
object_id, index_id, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@currentDdbId, NULL, NULL , NULL, 'LIMITED')
WHERE index_id > 0
DECLARE indexesToDefrag CURSOR FOR SELECT * FROM @AllIndexes
OPEN indexesToDefrag;
-- Loop through the partitions.
FETCH NEXT
FROM indexesToDefrag
INTO @objectid, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @schemaname = 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 = name
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid
IF @frag > @onlineDefragThreshold
BEGIN
IF @frag < @offlineDefragThreshold
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + object_name(@objectid) + ' REORGANIZE'
EXEC (@command)
END;
IF @frag >= @offlineDefragThreshold
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + object_name(@objectid) + ' REBUILD'
EXEC (@command)
END;
PRINT 'Executed ' + @command
END
IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, getdate())
BEGIN
SELECT @command = 'UPDATE STATISTICS ' + @schemaname + '.' + object_name(@objectid) + ' ' + @indexname +' WITH RESAMPLE'
EXEC (@command)
PRINT 'Executed ' + @command
END
FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag
END
CLOSE indexesToDefrag;
DEALLOCATE indexesToDefrag;
END
GO
Espero ter ajudado,
Atenciosamente,
Heber Lopes
São José dos Campos
msn: heber.lopes@gmail.com
Caso este post tenha respondido sua pergunta marque como resposta para auxiliar em futuras pesquisas.
