You can see bigger tables and look index fragmentation, I did this with one 300 gb database and down to 200 gb after that, look the follow procedures:
/*
EXEC dbo.spVerFragmentacionPorTabla 'Acum'
*/
IF EXISTS (SELECT * FROM sysobjects WHERE ID = OBJECT_ID('dbo.spVerFragmentacionPorTabla') AND type = 'P')
DROP PROCEDURE dbo.spVerFragmentacionPorTabla
GO
CREATE PROCEDURE dbo.spVerFragmentacionPorTabla
@Tabla varchar(50)
AS
BEGIN
SELECT a.index_id, nam5e, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@Tabla),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
END
GO
/*
EXEC dbo.spIndicesFragmentacion 'MovFlujo',1 -- 1 = On line 0 = exclusive
*/
IF EXISTS (SELECT * FROM sysobjects WHERE ID = OBJECT_ID('dbo.spIndicesFragmentacion') AND type = 'P')
DROP PROCEDURE dbo.spIndicesFragmentacion
GO
CREATE PROCEDURE dbo.spIndicesFragmentacion
@Tabla varchar(255),
@EnLinea bit = 1
AS BEGIN
DECLARE @SQL varchar(max),
@Indice varchar(255),
@Operacion varchar(255),
@Porcentaje float,
@Fragmentos int,
@ActualID bigint
DECLARE curInd CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT name, avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@Tabla), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 5.0
OPEN curInd
FETCH NEXT FROM curInd INTO @Indice, @Porcentaje, @Fragmentos
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'ALTER INDEX ' + @Indice + ' ON ' + @Tabla
IF @Porcentaje BETWEEN 5.0 AND 30.0
SELECT @Operacion = 'REORGANIZE'
ELSE
SELECT @Operacion = 'REBUILD'
SELECT @SQL = @SQL + CHAR(32) + @Operacion
IF @EnLinea = 1 And @Operacion = 'REBUILD' SELECT @SQL = @SQL + CHAR(32) + 'WITH (ONLINE = ON) '
INSERT LogIndicesFragmentacion (Empieza, Tabla, Indice, FragmentosI, Operacion)
VALUES (GETDATE(), @Tabla, @Indice, @Fragmentos, @Operacion)
SELECT @ActualID = @@IDENTITY
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
SELECT @Operacion = @Operacion + CHAR(32) + ERROR_MESSAGE()
END CATCH
UPDATE LogIndicesFragmentacion
SET Termina = GETDATE(),
FragmentosT = fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@Tabla), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE name = @Indice
AND Indice = @Indice
FETCH NEXT FROM curInd INTO @Indice, @Porcentaje, @Fragmentos
END
CLOSE curInd
DEALLOCATE curInd
END
GO
/*
SELECT * FROM LogIndicesFragmentacion
*/
IF NOT EXISTS (SELECT * FROM sysObjects WHERE id = OBJECT_ID('dbo.LogIndicesFragmentacion') AND type = 'U')
CREATE TABLE LogIndicesFragmentacion
(
ID bigint identity primary key clustered not null,
Empieza datetime null default getdate(),
Termina datetime null,
Tabla varchar(255),
Indice varchar(255),
FragmentosI int null,
FragmentosT int null,
Operacion varchar(255) null,
)
GO
