Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 MS SQL Server 2005 устранение фрагментации индексов  [new]
aht0
Member

Откуда:
Сообщений: 12
Добрый день!

Подскажите, пожалуйста, как настроить реорганизацию/перестроение всех индексов во всех таблицах.

В литературе нахожу только примеры для индексов 1-ой таблицы (ALTER INDEX ... REBUILD/REORGANIZE), а таблиц в базе целая куча (база 1С).
Буду очень признателен за рабочие примеры.
Помогите чайнику :-)
30 июн 09, 10:55    [7357634]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
rancid
Member

Откуда: *мск
Сообщений: 114
DECLARE @object AS int;
DECLARE @avg_frag AS float;
DECLARE @index_name AS sysname;
DECLARE @dbid AS smallint;
DECLARE @sql_prepare AS varchar(255);
DECLARE @max_length AS smallint; --по этому определяем хранит таблица LOB или нет

use [YOUR_DATABASE];
SET @dbid = DB_ID();

DECLARE rebuildoff CURSOR FOR
SELECT	DISTINCT ips.object_id, sys.indexes.name
FROM	sys.columns AS scol, sys.types AS stype, sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, NULL) AS ips, sys.indexes
WHERE	sys.indexes.object_id = ips.object_id AND scol.object_id = ips.object_id AND ips.index_id = sys.indexes.index_id AND stype.system_type_id = scol.system_type_id AND ips.avg_fragmentation_in_percent >30.0 AND (stype.max_length= -1 OR stype.max_length= 16)
ORDER BY ips.object_id;

DECLARE rebuildon CURSOR FOR
SELECT	DISTINCT ips.object_id, sys.indexes.name
FROM	sys.columns AS scol, sys.types AS stype, sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, NULL) AS ips, sys.indexes
WHERE	sys.indexes.object_id = ips.object_id AND scol.object_id = ips.object_id AND ips.index_id = sys.indexes.index_id AND stype.system_type_id = scol.system_type_id AND ips.avg_fragmentation_in_percent >30.0 AND (stype.max_length <> -1 AND stype.max_length <> 16)
ORDER BY ips.object_id;

DECLARE reindex CURSOR FOR
SELECT	ips.object_id,  ind.name
FROM	sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, NULL) AS ips, sys.indexes AS ind
WHERE	ips.object_id = ind.object_id AND ips.index_id = ind.index_id AND ips.avg_fragmentation_in_percent > 5.0 AND ips.avg_fragmentation_in_percent <= 30.0;

OPEN rebuildoff
WHILE 1=1 BEGIN
	
	FETCH FROM rebuildoff INTO @object, @index_name
	IF @@fetch_status <> 0 BREAK;
	IF @index_name IS NOT NULL
	BEGIN	
		PRINT N'В таблице ' + RTRIM(OBJECT_NAME(@object));
		PRINT N'будет выполнена перестройка индекса с параметром ONLINE=OFF: ' + RTRIM(@index_name);
	END

	--SET @sql_prepare = 'ALTER INDEX ['+@index_name+'] ON ['+OBJECT_NAME(@object)+'] REBUILD WITH (ONLINE = OFF);';
	--EXEC(@sql_prepare);

END
CLOSE rebuildoff
DEALLOCATE rebuildoff

OPEN rebuildon
WHILE 1=1 BEGIN

	FETCH FROM rebuildon INTO @object, @index_name
	IF @@fetch_status <> 0 BREAK;
	
	IF @index_name IS NOT NULL
	BEGIN

		PRINT N'В таблице ' + RTRIM(OBJECT_NAME(@object));
		PRINT N'будет выполнена перестройка индекса с параметром ONLINE=ON: ' + RTRIM(@index_name);
	END
	--SET @sql_prepare = 'ALTER INDEX ['+@index_name+'] ON ['+OBJECT_NAME(@object)+'] REBUILD WITH (ONLINE = ON);';
	--EXEC(@sql_prepare);

END
CLOSE rebuildon
DEALLOCATE rebuildon

OPEN reindex
WHILE 1=1 BEGIN
	
	FETCH FROM reindex INTO @object, @index_name
	IF @@fetch_status <> 0 BREAK;

	PRINT N'В таблице ' + RTRIM(OBJECT_NAME(@object));
	PRINT N'будет выполнена реорганизация индекса : ' + RTRIM(@index_name) + ';';
	--SET @sql_prepare = 'ALTER INDEX ['+@index_name+'] ON ['+OBJECT_NAME(@object)+'] REORGANIZE;';
	--EXEC (@sql_prepare);

END
CLOSE reindex
DEALLOCATE reindex
Строки PRINT можно удалить, то что после них - разкомментировать
30 июн 09, 11:05    [7357699]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36826
Maintenance Plan настройте.
30 июн 09, 11:08    [7357714]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
aht0
Member

Откуда:
Сообщений: 12
Большое спасибо, осталось теперь только в коде разобраться...
30 июн 09, 11:17    [7357782]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Гавриленко Сергей Алексеевич
Maintenance Plan настройте.


+1

хоть у него минимум 2 недостатка:
1) логи нечитаемы. где делся sql 6.5?
2) в случае малейших проблем сваливается весь план, а не обработка 1 таблицы
30 июн 09, 11:27    [7357857]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
aht0,

Вариации на тему генерации скриптов с учетом фрагментации индексов:

http://msmvps.com/blogs/gladchenko/archive/2008/03/31/1563721.aspx

http://weblogs.asp.net/okloeten/archive/2009/01/05/6819737.aspx
30 июн 09, 11:48    [7358036]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
aht0
Member

Откуда:
Сообщений: 12
Спасибо за ссылки, обязательно посмотрю!
30 июн 09, 12:36    [7358419]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
aht0
Member

Откуда:
Сообщений: 12
rancid,
Уважаемый rancid,

запускаю Ваш запрос, в сообщениях читаю типа: "В таблице такой-то будет выполнена перестройка/реорганизация индекса с таким-то параметром".
Затем запускаю повторно и вижу опять тоже самое.

Что нужно сделать, чтобы их реорганизовать/перестроить?
Непонятно, перестраивается чего-то или нет!

Объясните, пожалуйста!
1 июл 09, 12:32    [7362887]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2791
aht0
Непонятно, перестраивается чего-то или нет!
Конечно же, ничего не перестраивается. Вы же видите: в скрипте эти команды закоментированы.
1 июл 09, 12:46    [7362968]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
aht0
Member

Откуда:
Сообщений: 12
Спасибо, объяснили дураку!
1 июл 09, 13:05    [7363099]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
aht0
Member

Откуда:
Сообщений: 12
Люди добрые, помогите по написанному здесь коду.

Комментарии убрал.
SET @sql_prepare = 'ALTER INDEX ['+@index_name+'] ON ['+OBJECT_NAME(@object)+'] REBUILD WITH (ONLINE = ON);';
изменил ONLINE = ON на OFF
(поскольку ругался на какой-то индекс, перестроение которого нужно производить с ONLINE = OFF)

После этого скрипт отработал без ошибок, показал что будет перестроено/реорганизовано.
Запустил еще раз и опять увидел кучу индексов, которые надо дефрагментировать.

Получается, что ничего не перестраивается?

Помогите разобраться.

Или если возможно, выложите, пожалуйста, рабочий скрипт для дефрагментации базы 1С.
1 июл 09, 15:35    [7364045]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
Glory
Member

Откуда:
Сообщений: 104760
Индексы, которые расположены в смешанных экстентах невозможно дефрагментировать.
Да и смысла не имеет, потому что обычно это очень маленькие индексы размером менее 64Кб
1 июл 09, 15:40    [7364075]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
aht0
Member

Откуда:
Сообщений: 12
Glory,

Т.е. как я понял в любом случае, что-то останется не дефрагментированно?

И еще один дурацкий вопрос:
после дефрагментации меняется размер базы или нет?
1 июл 09, 15:45    [7364103]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
Glory
Member

Откуда:
Сообщений: 104760
aht0
Glory,

Т.е. как я понял в любом случае, что-то останется не дефрагментированно?

Ну можно конечно поставить себе супер задачу свести дефргаментацию к нулю
Но для этого придется рассматривать причины дефрагментации каждого индекса персонально

aht0

И еще один дурацкий вопрос:
после дефрагментации меняется размер базы или нет?

Если серверу понадобится место для осуществления операции дефрагментации, то может меняться
1 июл 09, 16:08    [7364309]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
aht0
Member

Откуда:
Сообщений: 12
Всем огромное спасибо!
1 июл 09, 16:22    [7364436]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2005 устранение фрагментации индексов  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
А как часто надо делать реорганизуцию/реиндексацию/сбор статистики? на базе Диасофт?
21 июл 09, 13:11    [7439895]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить