Дефрагментация базы данных (Тема для обсуждения)

Александр Гладченко
Дата: 08.02.2001 14:29:36

По материалам статьи Rahul Sharma на SWYNK.COM "Database Defragmentation"

Рауль пишет, что с течением времени (из-за вставок, обновления и удаления) данные и индексы могут быть сильно фрагментированы. Он предлагает вариант хранимой процедуры, с помощью которой можно дефрагментировать базу данных:

--Переиндексируем указанную базу данных,
CREATE PROCEDURE usp_DefragDatabase
-- Мы не будем использовать просто sysname,
-- потому что у него не достаточная длинна.
-- sysname вмещает 128 символов, так что мы возьмём его двойной формат.
@dbname nvarchar(256)
AS
BEGIN
-- Укажем имя базы данных в скобках.
DECLARE @quoteddbname nvarchar(256)
set @quoteddbname = quotename( @dbname )
-- Для всего того, что не попадёт в EXEC, Вы можете
-- выполнить USE отдельно от этой процедуры.
-- Для всего, что попадёт в EXEC, будет выполнена
-- переиндексация каждой таблицы указанной базы данных.
EXEC('
USE '+ @quoteddbname +'
DECLARE @sTableName sysname
DECLARE PKMS_Tables CURSOR LOCAL FOR
select table_name from information_schema.tables
where table_type = ''base table'' order by 1
OPEN PKMS_Tables
FETCH NEXT FROM PKMS_Tables INTO @sTableName

WHILE @@FETCH_STATUS = 0
BEGIN
select @sTablename = quotename(@sTablename, ''[]'')
EXEC('' DBCC DBREINDEX ( ''+@sTableName+'') WITH NO_INFOMSGS'')
FETCH NEXT FROM PKMS_Tables INTO @sTableName
END
CLOSE PKMS_Tables')
END
GO
Stork
Дата: 09.02.2001 09:51:22
Вместо курсора можно было бы использовать sp_MSForEachTable. Детали использования можно прочитать в рассылке "MS SQL SERVER - дело тонкое...", внеплановый выпуск №13.
Arty
Дата: 09.02.2001 17:13:46
В SQL 6.5 по крайней мере до SP 5a рекомендую очень осторожно пользоваться массовым DBCC DBREINDEX.
У меня сервер приходить в нерабочее состояние и для того чтобы этого
не происходило MS рекомендует после нескольких реиндексаций, давать команду
WAITFOR DELAY "00:00:01"
Лично сталкивался и был неприятно удивлен.
Хорошо что я обкатывал на тестовом сервере. Не знаю насколько широго используется сейчас SQL 6.5, я думаю было бы не лишне знать об этом.
Подробности в статье
FIX: Rebuilding Indexes w/ SQLMaint May Cause Incorrect Database [Q175789]
http://support.microsoft.com/support/kb/articles/q175/7/89.ASP
Еще не помню в какой статье упоминалось что рекомендуется
не просто давать команду DBCC DBREINDEX... а именно EXEC ('DBCC DBREINDEX...')
правда в Вашем примере так и написано.
keystop
Дата: 09.12.2002 13:08:36
А кто-нибудь имеет опыт запуска этой процедуры под SQL 2000?
Александр Гладченко
Дата: 09.12.2002 13:44:08
У меня она работает в мультисерверном задании на 20 серверах (в выходной). Сбоев небыло ни разу...
keystop
Дата: 09.12.2002 14:09:31
После выполнения этой процедуры как я понимаю UPDATE STATISTICS никакого смысла не имеет?
Предводитель команчей
Дата: 09.12.2002 18:26:03
2 ALL
По поводу "сильной фрагментации" - согласен на все 100 %. Испытал на собственной шкуре. Только, на мой взгляд, для полного рулеза, нужно также обновлять статистики и перекомпилировать процедуры. У меня сначала dbcc dbreindex работает через sp_MSforEachTable, затем sp_updatestats обновляет статистики, затем sp_recompile. Разница ДО и ПОСЛЕ ощутима.
Александр Гладченко
Дата: 09.12.2002 19:14:28
А есть ещё DBCC INDEXDEFRAG...
Предводитель команчей
Дата: 09.12.2002 19:29:39
2 Александр Гладченко
DBCC INDEXDEFRAG хорош для перестройки кластерного индекса, но для этого базу надо перевести в single user mode. Увы, я отказался от этой команды, т.к. в базах постоянно кто-то копошится даже в выходные. Кстати, может вы подскажите, как принудительно, не взирая ни на кого (имеется в виду то, что юзера работают) перевести БД в однопользовательский режим ?
VVG_
Дата: 09.12.2002 19:34:24
для этого базу надо перевести в single user mode

Правда чтоли?