Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
es3000
Member

Откуда:
Сообщений: 346
Настроил в воскресенье ночью план обслуживания:
1) проверка целостности
2) реорганизация индекса
3) восстановление индекса
4) обновление статистики
5) резервное копирование (полное)

Скажите, пожалуйста, в чем разница операций "Реорганизация индекса" и "Восстановление индекса"?
И в какой последовательности они должны выполняться?
22 апр 16, 08:14    [19091403]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Что в Вашем понимании "восстановление индекса"? Есть REBUILD / REORGANIZE.
22 апр 16, 08:19    [19091413]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
es3000
Member

Откуда:
Сообщений: 346
Я привел названия так как они есть в Managment Studio,
там при создании плана обслуживания эти задачи так и называются: "реорганизация индекса", "восстановление индекса".
Вот поэтому я и спрашиваю, что их смысл по названию и описанию не понятен
22 апр 16, 09:53    [19091766]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Что делает восстановление индекса я не знаю ибо никогда этими "волшебными" планами обслуживания не пользовался. Привык писать скрипты исходя из текущих условий проекта.

Вот наиболее общий вариант скрипта для обслуживания индексов. Править можно под себя.
22 апр 16, 10:03    [19091847]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
Yayaadmin
Guest
Еще можно добавить что не стоит заморачиваться с фрагентацией индексов с меньше чем 1000 страниц (а не 128 как в скрипте по ссылке).
https://technet.microsoft.com/en-us/library/cc966523.aspx
22 апр 16, 10:20    [19091967]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
es3000
Member

Откуда:
Сообщений: 346
AlanDenton
Что делает восстановление индекса я не знаю

это похоже REBUILD

а "реорганизация" - это REORGANIZE
22 апр 16, 10:23    [19091991]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
es3000
Member

Откуда:
Сообщений: 346
AlanDenton
Вот наиболее общий вариант скрипта для обслуживания индексов. Править можно под себя.


спасибо
22 апр 16, 10:24    [19091998]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
es3000
Member

Откуда:
Сообщений: 346
а какая из команд REBUILD \ REORGANIZE увеличивают лог транзакций?
и та и другая?
22 апр 16, 10:25    [19092004]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Yayaadmin
Еще можно добавить что не стоит заморачиваться с фрагментацией индексов с меньше чем 1000 страниц

Верное замечания. В целом скажу, что тот запрос лишь заготовка. Чтобы потом ее можно было под себя настроить. Да и размер БД разный бывает. Встречалось когда куча маленьких таблиц меньше < 1000. Их ведь тоже обслуживать надо ;)
22 апр 16, 10:27    [19092016]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
es3000
а какая из команд REBUILD \ REORGANIZE увеличивают лог транзакций?

Видимо я сегодня гугл заменяю:
http://dba.stackexchange.com/questions/10120/how-to-prevent-transaction-log-getting-full-during-index-reorganize
22 апр 16, 10:31    [19092050]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
es3000
а какая из команд REBUILD \ REORGANIZE увеличивают лог транзакций?
и та и другая?

Index Rebuild vs Reorganize: The transaction log edition
22 апр 16, 10:31    [19092057]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
es3000
Member

Откуда:
Сообщений: 346
TaPaK
Index Rebuild vs Reorganize: The transaction log edition


AlanDenton
http://dba.stackexchange.com/questions/10120/how-to-prevent-transaction-log-getting-full-during-index-reorganize


Прямого ответа в этих статьях не нашел.
Понятно, что REORGANIZE делает много мелких транзакций, а REBUILD делает одну большую транзакцию.
Но какая из них больше увеличивает размер лог-файла?
22 апр 16, 10:46    [19092159]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
Yayaadmin
Guest
AlanDenton
Yayaadmin
Еще можно добавить что не стоит заморачиваться с фрагментацией индексов с меньше чем 1000 страниц

Верное замечания. В целом скажу, что тот запрос лишь заготовка. Чтобы потом ее можно было под себя настроить. Да и размер БД разный бывает. Встречалось когда куча маленьких таблиц меньше < 1000. Их ведь тоже обслуживать надо ;)


Опять же дефрагментация таких маленьких индексов не сильно влияет на скорость выполнения запросов, с другой стороны если сервер полдня фигней страдает и ничего не делает то почему бы и их не перестр.\реорг., в общем да, зависит от БД.
Кстати, тоже неплохая статья про индексы https://habrahabr.ru/post/247373/.
22 апр 16, 10:46    [19092165]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
Yayaadmin
Guest
es3000
TaPaK
Index Rebuild vs Reorganize: The transaction log edition


AlanDenton
http://dba.stackexchange.com/questions/10120/how-to-prevent-transaction-log-getting-full-during-index-reorganize


Прямого ответа в этих статьях не нашел.
Понятно, что REORGANIZE делает много мелких транзакций, а REBUILD делает одну большую транзакцию.
Но какая из них больше увеличивает размер лог-файла?


А по моему в статье все понятно, кол-во записей при ребилде 35516 а при реорг. 2702321. Даже сама суть операций (ребилд: удаляем и создаем новый, реорг: перемащает стр. на конечном уровне чтобы лог. порядок соответствовал физ. порядку) подразумеваем что при перестр. будет больше работы с логом.
22 апр 16, 10:52    [19092214]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31962
es3000
Понятно, что REORGANIZE делает много мелких транзакций, а REBUILD делает одну большую транзакцию.
Но какая из них больше увеличивает размер лог-файла?
Понятно же по смыслу.
Если у вас лог после завершения транзакции очищается, то REORGANIZE потребует размера лога, равный самому большому изменению.
REBUILD же потребует размера лога, равному сумме изменений.

А вот если лог после завершения транзакции не очищается, то без разницы.
22 апр 16, 10:53    [19092221]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
es3000
Member

Откуда:
Сообщений: 346
Yayaadmin
кол-во записей при ребилде 35516 а при реорг. 2702321.

а размеры записей одинаковые?

Yayaadmin
при перестр. будет больше работы с логом.

если при перестроении больше работы с логом, тогда почему количество записей меньше?
22 апр 16, 11:14    [19092373]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
es3000
Member

Откуда:
Сообщений: 346
alexeyvg
Если у вас лог после завершения транзакции очищается, то REORGANIZE потребует размера лога, равный самому большому изменению.
REBUILD же потребует размера лога, равному сумме изменений.

А вот если лог после завершения транзакции не очищается, то без разницы.

что вы имеете ввиду под "лог очищается"?
и что такое "изменение"?
22 апр 16, 11:16    [19092396]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
AnyKey45
Member

Откуда: Ekaterinburg-Moscow-EU
Сообщений: 219
es3000,

попробуйте
https://ola.hallengren.com/
22 апр 16, 11:26    [19092488]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
Yayaadmin
Guest
es3000
Yayaadmin
кол-во записей при ребилде 35516 а при реорг. 2702321.

а размеры записей одинаковые?

Yayaadmin
при перестр. будет больше работы с логом.

если при перестроении больше работы с логом, тогда почему количество записей меньше?


Не то написал, при реорг. больше работы с логом.
22 апр 16, 11:29    [19092508]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
я там кое-какие недоделки исправил в скрипте, чтобы работал:

DECLARE @SQL NVARCHAR(MAX);
DECLARE @dbname NVARCHAR(128) = N'mydb';
DECLARE @dbid smallint = DB_ID(@dbname);
DECLARE @pagecount int = 128; --порог кол-ва страниц индекса (128 -> 1 MB)
DECLARE @IsDetailedScan BIT = 0;
DECLARE @IsOnline BIT = 0;
DECLARE @mode nvarchar(20) = CASE WHEN @IsDetailedScan = 1 THEN 'DETAILED' ELSE 'LIMITED' END;

PRINT '--Обслуживание индексов базы "' + @dbname + '":' + CHAR(13) + CHAR(10)

SET @SQL = 
	(
	SELECT 
		'ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
			CASE WHEN s.avg_fragmentation_in_percent > 30
				THEN 
					'REBUILD WITH (SORT_IN_TEMPDB = ON' +
					-- Enterprise, Developer
					CASE WHEN SERVERPROPERTY('EditionID') IN (1804890536, -2117995310) AND @IsOnline = 1
							THEN ', ONLINE = ON'
							ELSE ''
					  END + 
					')'
				ELSE 'REORGANIZE'
			END + ';' + CHAR(13) + CHAR(10)
	FROM 
		(
		SELECT 
			  s.[object_id]
			, s.index_id
			, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
		FROM sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, @mode) s
		WHERE s.page_count > @pagecount 
			AND s.index_id > 0 -- <> HEAP
			AND s.avg_fragmentation_in_percent > 5
		GROUP BY s.[object_id], s.index_id
		) s
		INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
		INNER JOIN sys.objects o ON o.[object_id] = s.[object_id]
		FOR XML PATH(''), TYPE
	).value('.', 'NVARCHAR(MAX)');
	

IF (@SQL IS NULL)
	PRINT '--индексы не требуют обслуживания';
ELSE
	PRINT @SQL;

--EXEC sys.sp_executesql @SQL


AlanDenton
Что делает восстановление индекса я не знаю ибо никогда этими "волшебными" планами обслуживания не пользовался. Привык писать скрипты исходя из текущих условий проекта.

Вот наиболее общий вариант скрипта для обслуживания индексов. Править можно под себя.
22 апр 16, 12:07    [19092759]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Konst_One,

кстати пишут же что от 30% стоит Rebuild делать или лишним не будет?
22 апр 16, 12:11    [19092777]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
TaPaK
Konst_One,

кстати пишут же что от 30% стоит Rebuild делать или лишним не будет?


в этом скрипте как раз 30% порог на rebuild
запусти на какой-нибудь базе и посмотри что сгенерит
22 апр 16, 12:14    [19092795]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Konst_One
TaPaK
Konst_One,

кстати пишут же что от 30% стоит Rebuild делать или лишним не будет?


в этом скрипте как раз 30% порог на rebuild
запусти на какой-нибудь базе и посмотри что сгенерит

извиняюсь, недосмотрел :)
22 апр 16, 12:16    [19092805]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Konst_One
я там кое-какие недоделки исправил в скрипте, чтобы работал:

DECLARE @dbname NVARCHAR(128) = N'mydb';
DECLARE @dbid smallint = DB_ID(@dbname);

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
INNER JOIN sys.objects o ON o.[object_id] = s.[object_id]

Если коннекшен будет на базу master, а указана база mydb, то выйдет в итоге хрень: фрагментированные индексы вернутся для mydb, а соединение будет идти по объектам из master.

Впрочем и меня тоже можно покритиковать за:

avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)

общую фрагментацию индекса так не сильно корректно. Да и поддержки работы с секциями тут нет. Время появится я допишу тот скриптец.
22 апр 16, 12:25    [19092864]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно настроить задания с индексами в плане обслуживания БД?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
общую фрагментацию индекса так не сильно корректно определять. Да и среднее считать тоже не комильфо.
22 апр 16, 12:27    [19092879]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить