Готовые скрипты для обслуживания БД (индексы) MS SQL

Тема в разделе 'Готовые скрипты', создана пользователем Sputnick, 11 сен 2014.

  1. Sputnick Администратор

    Репутация:
    58.920
    Готовые скрипты для обслуживания БД (индексы) MS SQL


    автоматический с сайта майкрософт
    Код:
    /******************************************************************************
    This sample T-SQL script performs basic maintenance tasks on SUSDB
    1. Identifies indexes that are fragmented and defragments them. For certain
      tables, a fill-factor is set in order to improve insert performance.
      Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx
      and tailored for SUSDB requirements
    2. Updates potentially out-of-date table statistics.
    ******************************************************************************/
     
    USE SUSDB;
    GO
    SET NOCOUNT ON;
     
    -- Rebuild or reorganize indexes based on their fragmentation levels
    DECLARE @work_to_do TABLE (
        objectid int
        , indexid int
        , pagedensity float
        , fragmentation float
        , numrows int
    )
     
    DECLARE @objectid int;
    DECLARE @indexid int;
    DECLARE @schemaname nvarchar(130);
    DECLARE @objectname nvarchar(130);
    DECLARE @indexname nvarchar(130);
    DECLARE @numrows int
    DECLARE @density float;
    DECLARE @fragmentation float;
    DECLARE @command nvarchar(4000);
    DECLARE @fillfactorset bit
    DECLARE @numpages int
     
    -- Select indexes that need to be defragmented based on the following
    -- * Page density is low
    -- * External fragmentation is high in relation to index size
    PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)
    INSERT @work_to_do
    SELECT
        f.object_id
        , index_id
        , avg_page_space_used_in_percent
        , avg_fragmentation_in_percent
        , record_count
    FROM
        sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f
    WHERE
        (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1)
        or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0)
        or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)
     
    PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))
     
    PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)
     
    SELECT @numpages = sum(ps.used_page_count)
    FROM
        @work_to_do AS fi
        INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
        INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id
     
    -- Declare the cursor for the list of indexes to be processed.
    DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do
     
    -- Open the cursor.
    OPEN curIndexes
     
    -- Loop through the indexes
    WHILE (1=1)
    BEGIN
        FETCH NEXT FROM curIndexes
        INTO @objectid, @indexid, @density, @fragmentation, @numrows;
        IF @@FETCH_STATUS < 0 BREAK;
     
        SELECT
            @objectname = QUOTENAME(o.name)
            , @schemaname = QUOTENAME(s.name)
        FROM
            sys.objects AS o
            INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE
            o.object_id = @objectid;
     
        SELECT
            @indexname = QUOTENAME(name)
            , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
        FROM
            sys.indexes
        WHERE
            object_id = @objectid AND index_id = @indexid;
     
        IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        ELSE IF @numrows >= 5000 AND @fillfactorset = 0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
        ELSE
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;
        EXEC (@command);
        PRINT convert(nvarchar, getdate(), 121) + N' Done.';
    END
     
    -- Close and deallocate the cursor.
    CLOSE curIndexes;
    DEALLOCATE curIndexes;
     
     
    IF EXISTS (SELECT * FROM @work_to_do)
    BEGIN
        PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))
        SELECT @numpages = @numpages - sum(ps.used_page_count)
        FROM
            @work_to_do AS fi
            INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
            INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id
     
        PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))
    END
    GO
     
     
    --Update all statistics
    PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
    EXEC sp_updatestats
    PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
    GO
    обновляет статистику индексов таблиц по маске
    Код:
    DECLARE @table_name varchar(50)
    DECLARE db_reindex_all CURSOR FOR
        select [name]
        from dbo.sysobjects
        where [name] like 'b_%' and [name] not like 'b_bd_%' and xtype='U'
    OPEN db_reindex_all
    FETCH NEXT FROM db_reindex_all INTO @table_name
    WHILE @@FETCH_STATUS=0
    BEGIN
        DBCC DBREINDEX (@table_name)
    FETCH NEXT FROM db_reindex_all INTO @table_name
    END
    CLOSE db_reindex_all
    DEALLOCATE db_reindex_all
     
    EXEC sp_updatestats 
  2. Виолина

    Репутация:
    0

Поделиться этой страницей