Scribd Logo
Cargar

¡Te damos la bienvenida a Scribd!

  • 118 vistas

    Mantenimiento de Índices en SQL Server

    Cargado por

    agusalsa
    Los índices en las bases de datos SQL Server pueden fragmentarse con el tiempo debido a las actualizaciones, lo que reduce el rendimiento de las consultas. Es necesario realizar periódicamente un mantenimiento de los índices para evitar la fragmentación mediante la reorganización o reconstrucción de los índices fragmentados. La reorganización mantiene el orden de los índices de forma más ligera cuando la fragmentación es baja, mientras que la reconstrucción vuelve a crear completamente el índice para eliminar la fragmentación de forma más efectiva pero con mayor coste cuando

    Copyright:

    © All Rights Reserved
    Descargue como DOCX, PDF, TXT o lea en línea desde Scribd

    Mantenimiento de Índices en SQL Server

    Cargado por

    agusalsa
    118 vistas3 páginas
    Los índices en las bases de datos SQL Server pueden fragmentarse con el tiempo debido a las actualizaciones, lo que reduce el rendimiento de las consultas. Es necesario realizar periódicamente un mantenimiento de los índices para evitar la fragmentación mediante la reorganización o reconstrucción de los índices fragmentados. La reorganización mantiene el orden de los índices de forma más ligera cuando la fragmentación es baja, mientras que la reconstrucción vuelve a crear completamente el índice para eliminar la fragmentación de forma más efectiva pero con mayor coste cuando

    Derechos de autor

    © © All Rights Reserved

    Formatos disponibles

    DOCX, PDF, TXT o lea en línea desde Scribd

    ¿Le pareció útil este documento?

    ¿Este contenido es inapropiado?

    Los índices en las bases de datos SQL Server pueden fragmentarse con el tiempo debido a las actualizaciones, lo que reduce el rendimiento de las consultas. Es necesario realizar periódicamente un mantenimiento de los índices para evitar la fragmentación mediante la reorganización o reconstrucción de los índices fragmentados. La reorganización mantiene el orden de los índices de forma más ligera cuando la fragmentación es baja, mientras que la reconstrucción vuelve a crear completamente el índice para eliminar la fragmentación de forma más efectiva pero con mayor coste cuando

    Copyright:

    © All Rights Reserved
    Descargue como DOCX, PDF, TXT o lea en línea desde Scribd
    Descargar como docx, pdf o txt
    118 vistas3 páginas

    Mantenimiento de Índices en SQL Server

    Cargado por

    agusalsa
    Los índices en las bases de datos SQL Server pueden fragmentarse con el tiempo debido a las actualizaciones, lo que reduce el rendimiento de las consultas. Es necesario realizar periódicamente un mantenimiento de los índices para evitar la fragmentación mediante la reorganización o reconstrucción de los índices fragmentados. La reorganización mantiene el orden de los índices de forma más ligera cuando la fragmentación es baja, mientras que la reconstrucción vuelve a crear completamente el índice para eliminar la fragmentación de forma más efectiva pero con mayor coste cuando

    Copyright:

    © All Rights Reserved
    Descargue como DOCX, PDF, TXT o lea en línea desde Scribd
    Descargar como docx, pdf o txt
    Está en la página 1de 3

    Mantenimiento de ndices en SQL Server: cmo evitar la

    fragmentacin
    Los ndices son objetos de la base de datos diseados de manera similar a los ndices que
    usamos en los libros para encontrar contenidos en el mismo.
    stos ordenan los datos en nuestras tablas, permitiendo un acceso ms rpido y eficiente a
    aqullos que estemos consultando, modificando o eliminando.
    Por ello, la actualizacin de los registro de las tablas de la base datos, obliga al servidor SQL a
    realizar ciertas operaciones que nos garanticen el orden de los datos en los ndices. Debido a
    esta actualizacin, la informacin almacenada en los ndices se ve fragmentada con su uso.
    Estafragmentacin depende de parmetros como el fill factor, nmero de pginas, tamao del
    ndice y frecuencia de actualizacin.
    En definitiva, un ndice sobre un tabla, debido a actualizaciones sobre sta, puede perder
    eficacia y, con ello, nuestras consultas volverse ms lentas y su rendimiento deteriorarse.
    Es necesario, pues, realizar peridicamente tareas de consulta del estado de nuestros ndices,
    para mantener la fragmentacin por debajo de unos lmites recomendables. sta se mide en
    porcentaje, correspondiendo un valor bajo (entre 0% y 10%) a un estado ptimo del ndice y
    siendo peor el estado cuando el valor de dicho indicador mayor sea.

    Defragmentacin, al rescate
    Para evitar el deterioro del rendimiento en nuestro servidor, deberemos mantener nuestros
    ndices en un estado de fragmentacin ptimo. Lo podremos lograr sencillamente siguiendo
    estos pasos.

    Primer paso: detectar fragmentacin en los ndices de tu base de datos. Para ello,
    nos basaremos en la vista de sistema sys.dm_db_index_physical_stats, que encapsularemos
    en la siguiente query:

    123456789101112

    SELECT DB_NAME(database_id) AS DatabaseName, database_id,


    OBJECT_NAME(ips.object_id) AS TableName, ips.object_id,
    i.name AS IndexName, i.index_id, p.rows,
    ips.partition_number, index_type_desc,
    alloc_unit_type_desc, index_depth, index_level,
    avg_fragmentation_in_percent, fragment_count,
    avg_fragment_size_in_pages, page_count,
    avg_page_space_used_in_percent, record_count,
    ghost_record_count, version_ghost_record_count,
    min_record_size_in_bytes,
    max_record_size_in_bytes, avg_record_size_in_bytes,
    forwarded_record_count
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL ,
    NULL, 'LIMITED') ips
    INNER JOIN sys.indexes i ON i.object_id = ips.object_id AND
    i.index_id = ips.index_id
    INNER JOIN sys.partitions p ON p.object_id = i.object_id
    AND p.index_id = i.index_id
    WHERE avg_fragmentation_in_percent > 10.0 AND ips.index_id
    > 0 AND page_count > 1000
    ORDER BY avg_fragmentation_in_percent DESC

    view rawgistfile1.sql hosted with by GitHub

    Segundo paso: ejecutar un script para defragmentar los ndices con problemas. El
    script determina si hay que hacer un Reorganize o un Rebuild para cada ndice:

    -- 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 @command nvarchar(4000);
    -- 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
    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 AND page_count >
    1000;
    -- Declare the cursor for the list of partitions to be processed.
    DECLARE partitions CURSOR 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;
    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;
    -- 30 is an arbitrary decision point at which to switch between reorganizing and
    rebuilding.
    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 and deallocate the cursor.
    CLOSE partitions;
    DEALLOCATE partitions;
    -- Drop the temporary table.
    DROP TABLE #work_to_do;
    GO

    Diferencias entre reconstruir (REBUILD) y reorganizar


    (REORGANIZE) un ndice
    Bsicamente, existen dos maneras de volver a ordenar un ndice. La ms eficaz consiste en
    eliminar el ndice y volver a crearlo, desde cero. sta garantiza un resultado ptimo, dejando la
    fragmentacin en 0%. Como contrapartida, es una operacin ms costosa y genera bloqueos
    sobre la tabla cuyo ndice se est actualizando. Usaremos esta opcin (REBUILD) cuando la
    fragmentacin del ndice supere el 30%.
    Para ndices ligeramente fragmentados (entre el 10% y el 30%) existe la opcin de reorganizar
    (REORGANIZE) el ndice. Esta opcin simplemente reordena los datos del ndice dentro de las
    pginas que ocupa, devolviendo un orden adecuado al mismo. Se trata de una operacin
    mucho ms ligera, que no bloquea las tablas y vistas subyacentes.
    Adicionalmente, habr que tener en cuenta que solamente tiene sentido efectuar la operacin
    dedefragmentacin sobre ndices con un tamao superior a 1000 pginas. Ya que, por
    debajo de este valor, SQL considera que es ms eficiente escribir los datos de forma
    desordenada que mantener un ndice poco efectivo, al tratarse de una tabla pequea.

    También podría gustarte