sql-server - reconstruir - sql server reindex

¿Script para reconstruir y reindexar el índice fragmentado?

Question

¿Puede alguien proporcionar el script para reconstruir y volver a indexar el índice fragmentado cuando ''avg_fragmentation_in_percent'' excede ciertos límites (mejor si no se usa el cursor)?

Answer 1

Dos soluciones: una simple y otra más avanzada.

Introducción

Hay dos soluciones disponibles para usted dependiendo de la gravedad de su problema

Reemplace con sus propios valores, de la siguiente manera:

• Reemplace XXXMYINDEXXXX con el nombre de un índice.
• Reemplace XXXMYTABLEXXX con el nombre de una tabla.
• Reemplace XXXDATABASENAMEXXX con el nombre de una base de datos.

Solución 1. Indexación

Reconstruir todos los índices de una tabla en modo fuera de línea

ALTER INDEX ALL ON XXXMYTABLEXXX REBUILD

Reconstruir un índice especificado para una tabla en modo fuera de línea

ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REBUILD

Solución 2. Fragmentación.

La fragmentación es un problema en las tablas que regularmente tienen entradas agregadas y eliminadas.

Ver porcentaje de fragmentación

SELECT
ips.[index_id] ,
idx.[name] ,
ips.[avg_fragmentation_in_percent]
FROM
sys.dm_db_index_physical_stats(DB_ID(N''XXXMYDATABASEXXX''), OBJECT_ID(N''XXXMYTABLEXXX''), NULL, NULL, NULL) AS [ips]
INNER JOIN sys.indexes AS [idx] ON [ips].[object_id] = [idx].[object_id] AND [ips].[index_id] = [idx].[index_id]

Fragmentación 5..30%

Si el valor de fragmentación es mayor que 5%, pero menor que 30%, entonces vale la pena reorganizar los índices.

Reorganizar todos los índices de una tabla.

ALTER INDEX ALL ON XXXMYTABLEXXX REORGANIZE

Reorganizar un índice especificado para una tabla

ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REORGANIZE

Fragmentación 30% +

Si el valor de fragmentación es del 30% o mayor, entonces vale la pena reconstruir los índices en el modo en línea.

Reconstruir todos los índices en modo en línea para una tabla

ALTER INDEX ALL ON XXXMYTABLEXXX REBUILD WITH (ONLINE = ON)

Reconstruir un índice especificado en modo en línea para una tabla

ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REBUILD WITH (ONLINE = ON)

Answer 2

Aquí está la secuencia de comandos modificada que tomé de http://www.foliotek.com/devblog/sql-server-optimization-with-index-rebuilding que encontré útil para publicar aquí. Aunque utiliza un cursor y sé cuál es el problema principal con los cursores, se puede convertir fácilmente en una versión sin cursor.

Está bien documentado y puede leerlo fácilmente y modificarlo según sus necesidades.

 IF OBJECT_ID(''tempdb..#work_to_do'') IS NOT NULL
DROP TABLE tempdb..#work_to_do
BEGIN TRY
--BEGIN TRAN
use yourdbname
-- Ensure a USE statement has been executed first.
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 @pagecount INT;
DECLARE @command NVARCHAR(4000);
DECLARE @page_count_minimum SMALLINT
SET @page_count_minimum = 50
DECLARE @fragmentation_minimum FLOAT
SET @fragmentation_minimum = 30.0
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid ,
index_id AS indexid ,
partition_number AS partitionnum ,
avg_fragmentation_in_percent AS frag ,
page_count AS page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,
''LIMITED'')
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
AND index_id > 0
AND page_count > @page_count_minimum;
IF CURSOR_STATUS(''global'', ''partitions'') >= -1
BEGIN
PRINT ''partitions CURSOR DELETED'' ;
CLOSE partitions
DEALLOCATE partitions
END
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR LOCAL
FOR
SELECT *
FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE ( 1 = 1 )
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag, @pagecount;
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;
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 (@command); //uncomment for testing
PRINT N''Rebuilding index '' + @indexname + '' on table ''
+ @objectname;
PRINT N'' Fragmentation: '' + CAST(@frag AS VARCHAR(15));
PRINT N'' Page Count: '' + CAST(@pagecount AS VARCHAR(15));
PRINT N'' '';
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
--COMMIT TRAN
END TRY
BEGIN CATCH
--ROLLBACK TRAN
PRINT ''ERROR ENCOUNTERED:'' + ERROR_MESSAGE()
END CATCH

Answer 3

Descubrí que la siguiente secuencia de comandos es muy buena para mantener los índices, puede programarlo para que se ejecute todas las noches o cualquier otro período de tiempo que desee.

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

Answer 4

La verdadera respuesta, en 2016 y 2017, es: usar los guiones de Ola Hallengren:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Eso es todo lo que cualquiera de nosotros necesita saber o molestarse, en este punto de nuestra evolución mutua.

Answer 5

Para reconstruir el uso:

ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD

o para reorganizar el uso:

ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REORGANIZE

La reorganización se debe utilizar en fragmentaciones inferiores (<30%), pero solo la reconstrucción (que es más pesada para la base de datos) reduce la fragmentación al 0%.
Para obtener más información, consulte https://msdn.microsoft.com/en-us/library/ms189858.aspx