Вопрос по sql-server – Скрипт для перестройки и переиндексации фрагментированного индекса?

20

Может ли кто-нибудь предоставить сценарий для перестройки и повторной индексации фрагментированного индекса, когда «ag_fragmentation_in_percent» apos; превышает определенные пределы (лучше, если курсор не используется)?

Почему бы и нет? Любой может задать любой вопрос, который они задают вики. Это позволяет избежать потери очков репутации. Jonathan Leffler
@ Джонатан Леффлер, но это хороший законный вопрос с небольшим риском получить голоса. меньше людей смотрят вики и / или хотят отвечать за ноль баллов. KM.
@ Савита: какая часть проблемы? Я не сразу уверен, как вы будете выполнять операцию с курсором. Вы ищете сценарий для определения процента, а затем восстановить? Вы проверили руководство для вариантов? Каковы доступные заявления? Вы должны определить детали индекса? Есть ли оператор ALTER INDEX? Jonathan Leffler

Ваш Ответ

5   ответов
10

Вот модифицированный скрипт, который я взял изhttp://www.foliotek.com/devblog/sql-server-optimization-with-index-rebuilding который я нашел полезным опубликовать здесь. Хотя он использует курсор, и я знаю, что является основной проблемой с курсорами, его можно легко преобразовать в версию без курсора.

Он хорошо документирован, и вы можете легко прочитать его и изменить в соответствии с вашими потребностями.

  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
24

Чтобы восстановить использовать:

ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD

или реорганизовать использование:

ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REORGANIZE

Реорганизацию следует использовать при более низкой (& lt; 30%) фрагментации, но только перестройка (которая тяжелее для базы данных) снижает фрагментацию до 0%.
Для получения дополнительной информации см.https://msdn.microsoft.com/en-us/library/ms189858.aspx

4

Я обнаружил, что следующий скрипт очень хорош в обслуживании индексов, вы можете запланировать его запуск ночью или на любой другой таймфрейм, какой пожелаете.

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

14

Two solutions: One simple and one more advanced.

Introduction

В зависимости от серьезности вашей проблемы вам доступны два решения.

Замените ваши собственные значения следующим образом:

  • Replace XXXMYINDEXXXX with the name of an index.
  • Replace XXXMYTABLEXXX with the name of a table.
  • Replace XXXDATABASENAMEXXX with the name of a database.

Solution 1. Indexing

Rebuild all indexes for a table in offline mode

ALTER INDEX ALL ON XXXMYTABLEXXX REBUILD

Rebuild one specified index for a table in offline mode

ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REBUILD

Solution 2. Fragmentation

Фрагментация - это проблема в таблицах, в которые регулярно добавляются и удаляются записи.

Check fragmentation percentage

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]

Fragmentation 5..30%

Если значение фрагментации больше 5%, но меньше 30%, то стоит реорганизовать индексы.

Reorganise all indexes for a table

ALTER INDEX ALL ON XXXMYTABLEXXX REORGANIZE

Reorganise one specified index for a table

ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REORGANIZE

Fragmentation 30%+

Если значение фрагментации составляет 30% или больше, его стоит перестроить, а затем индексировать в режиме онлайн.

Rebuild all indexes in online mode for a table

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

Rebuild one specified index in online mode for a table

ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REBUILD WITH (ONLINE = ON)
Похоже, что нет метода WITH (ONLINE = ON) для метода REORGANIZE
2

Реальный ответ в 2016 и 2017 годах: использовать сценарии Олы Хелленгрен:

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

Это все, что любой из нас должен знать или беспокоиться на данном этапе нашей взаимной эволюции.

Похожие вопросы