Главная > SQL Server > Дефрагментация индексов на основе информации о фрагментации

Дефрагментация индексов на основе информации о фрагментации

 Читал недавно чтиво 🙂 по администрированию SQL Server, был пример очень интересный по реиндексации.

Немного переделал, получилось следующее:
Дефрагментация индексов на основе информации о фрагментации.
Дефрагментируются только наиболее фрагментированные индексы.

Возможно проведение операций во время работы пользователей в базе данных.
В любое время можно прервать выполнение дефрагментации без потери результата.

Хранимая процедура:
———————————————————————————————

use master

go

if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[sp_defragment_frag_indexes]’) and OBJECTPROPERTY(id, N‘IsProcedure’) = 1)

drop procedure [dbo].[sp_defragment_frag_indexes]

go

 

CREATE PROCEDURE sp_defragment_frag_indexes @maxfrag DECIMAL, @whatif VARCHAR (6) = » AS

 

— Объявляем необходимые переменные

SET NOCOUNT ON

DECLARE @tablename VARCHAR (128)

DECLARE @execstr VARCHAR (255)

DECLARE @objectid INT

DECLARE @objectowner VARCHAR(255)

DECLARE @indexid INT

DECLARE @frag DECIMAL

DECLARE @indexname CHAR(255)

DECLARE @dbname sysname

DECLARE @tableid INT

DECLARE @tableidchar VARCHAR(255)

 

— На всякий случай проверяем,что база данных пользовательская

SELECT @dbname = db_name();

 

IF @dbname IN (‘master’, ‘msdb’, ‘model’, ‘tempdb’)

BEGIN

    PRINT Эта процедура не может быть запущена для системных БД’;

    RETURN

END

 

— Начинаем проверку уровня фрагментации

— Вначале объявляем курсор

DECLARE tables CURSOR FOR

SELECT convert(varchar,so.id) FROM sysobjects so JOIN sysindexes si ON so.id = si.id WHERE so.type =‘U’ AND si.indid < 2 AND si.rows > 0;

— Затем создаем временную таблицу для хранения информации о фрагментации

CREATE TABLE #fraglist (ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255),IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL);

— Открываем курсор

OPEN tables

— Для каждой таблицы в базе данных выполняем команду DBCC SHOWCONTIG

FETCH NEXT FROM tables INTO @tableidchar

WHILE @@FETCH_STATUS = 0

BEGIN

    — Проходим по всем индексам для таблицы

    INSERT INTO #fraglist

    EXEC (‘DBCC SHOWCONTIG (‘ + @tableidchar + ‘) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)

    FETCH NEXT

    FROM tables

    INTO @tableidchar

END

— Закрываем курсор

CLOSE tables

DEALLOCATE tables

— Для проверки выводим информацию из временной таблицы

SELECT * FROM #fraglist

 

— Теперь необходимо произвести дефрагментацию

— Вначале опять объявляем курсор

DECLARE indexes CURSOR FOR

SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity FROM #fraglist f JOIN sysobjects so ON f.ObjectId=so.id WHERE ScanDensity <= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0

— Выводим для проверки информацию о начале дефрагментации

SELECT ‘Started defragmenting indexes at ‘ + CONVERT(VARCHAR,GETDATE())

— Открываем курсор

OPEN indexes

— Проходим циклом по всем индексам

FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag

WHILE @@FETCH_STATUS = 0

BEGIN

    SET QUOTED_IDENTIFIER ON

    SELECT @execstr = ‘DBCC INDEXDEFRAG (‘ + @dbname + ‘, ‘ + RTRIM(@tablename) + ‘, ‘ + RTRIM(@indexname) + ‘) WITH NO_INFOMSGS’

    SELECT Выполняем: ‘ + @execstr;

    if @whatif <> ‘whatif’ EXEC (@execstr);

    SET QUOTED_IDENTIFIER OFF

    FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag

END

— Затем закрываем курсор

CLOSE indexes;

DEALLOCATE indexes;

 

— Отчитываемся о времени завершения

SELECT ‘Finished defragmenting indexes at ‘ + CONVERT(VARCHAR,GETDATE());

 

— Удаляем временную таблицу

DROP TABLE #fraglist;

GO

———————————————————————————————

 

Использование:

———————————————————————————————

— Дефрагментировать индексы таблиц базы данных Test, для которых значение параметра Scan Density < 80%

 

USE Test

EXEC sp_defragment_frag_indexes 80.00;

———————————————————————————————

Применимо:
MS SQL Server 2000, 2005

Реклама
Рубрики:SQL Server
  1. Комментариев нет.
  1. No trackbacks yet.

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

%d такие блоггеры, как: