Как перестроить индексы ms sql 2008

Анализ индекса SQL Server

Уровень фрагментации индекса в базе данных можно проанализировать с помощью следующего запроса:

Возвращаются следующие столбцы:

1. децибел— имя базы данных
2. схема— объект схемы (таблица, вид)
3. Т.Б.— таблица / вид, где находится индекс
4. IDX— индекс индекса
5. database_id— идентификатор базы данных
6. index_name— индекс
7. index_type_desc— описание типа индекса
8. object_id— идентификатор объекта (таблица, вид), где расположен индекс
9. frag_num— количество фрагментов на конечном уровне единицы размещения
10. осколочная граната— средний процент доступного дискового пространства, используемого всеми страницами
11. frag_page— среднее количество страниц в одном фрагменте на конечном уровне блока выделения IN_ROW_DATA
12. страница— общее количество страниц индекса или данных

Это показывает уровень фрагментации индексов, размер которых составляет не менее 1 экстента (8 страниц) и какая фрагментация составляет более 10%. Он работает только для таблиц с кластерным индексом, и учитываются только корневые индексы. Этот запрос использует два системных представления:

1. sys.dm_db_index_physical_stats — возвращает информацию о размере и фрагментации данных и индексов указанной таблицы или представления в SQL Server.
2. sys.indexes — содержит строку для каждого индекса или кучи табличного объекта, такого как таблица, представление или табличная функция.

Далее давайте рассмотрим, когда рекомендуется оптимизировать индекс и как его выполнить.

Оптимизация индексов SQL Server

В предыдущем запросе особенно важны два показателя:

1. frag — уровень фрагментации индекса в процентах
2. страница — размер индекса в общем количестве его страниц

Существуют разные подходы к интерпретации уровня фрагментации индекса и методы оптимизации индекса. Один из них будет рассмотрен в этой статье.

Индекс необходимо оптимизировать, если:

• его размер превышает 8 страниц, а уровень фрагментации — более 30%.
• его размер превышает 64 страницы, а уровень фрагментации — более 25%.
• его размер превышает 1000 страниц, а уровень фрагментации — более 20%.
• его размер превышает 10 000 страниц, а уровень фрагментации — более 15%.
• его размер превышает 100 000 страниц, а уровень фрагментации — более 10%.

Для оптимизации индекса можно использовать два подхода:

1.Реорганизация индекса

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

2. Восстановление индекса

Перестройка удаляет старый индекс и создает новый. Это исключает фрагментацию, восстанавливает дисковое пространство путем сжатия страниц до указанного или существующего коэффициента заполнения, переупорядочивает строки индекса на последовательных страницах и обновляет новую статистику индекса.
Вы можете выполнить перестройку индекса с помощью следующей команды:

Если ваша редакция MS SQL Server поддерживает это, перестроение индекса можно выполнить в режиме онлайн:

Более подробную информацию о команде ALTER INDEX можно найти Вот,

Существует множество инструментов оптимизации индексов, как бесплатных, так и платных. Например, Сергей Сыроватченко разрабатывает довольно мощный и бесплатный орудие труда для оптимизации индексов
Преимущества этого инструмента заключаются в следующем:

• оптимизированный алгоритм получения фрагментированных индексов
• способность обслуживать несколько баз данных одновременно в одном процессе
• автоматический выбор действия для индексов на основе выбранных настроек
• поддержка глобального поиска и расширенной фильтрации для лучшей аналитики
• множество настроек и полезной информации об индексах
• автоматическая генерация сценариев обслуживания индекса
• поддержка обслуживания кучи и индекса столбца
• возможность включения сжатия индекса и обновления статистики вместо восстановления
• поддержка всех выпусков SQL Server 2008 и более поздних версий, а также базы данных SQL Azure.

Подробное обсуждение инструмента можно найти Вот,

Статистический анализ и оптимизация

Давайте рассмотрим один из методов определения устаревшей статистики:

В данном методе устаревшая статистика определяется следующими показателями:

1. Если данные были значительно изменены.
2. Если статистика давно не обновлялась.
3. Если размер объекта меньше указанного максимума или этот максимум не указан.
4. Если количество строк в разделе меньше указанного максимума или этот максимум не указан.

В запросе используются следующие системные представления:

1. sys.dm_db_partition_stats — возвращает информацию о количестве страниц и строк для всех разделов текущей базы данных.
2. sys.objects — объекты базы данных.
3. sys.stats — статистика для таблиц, индексов и индексированных представлений.
4. sys.indexes — индексы.
5. sys.dm_db_stats_properties — возвращает статистические свойства указанного объекта базы данных (таблицы или индексированного представления) из текущей базы данных SQL Server.
6. sys.stats_columns — содержит по одной строке для каждого столбца, который является частью статистики sys.stats.
7. sys.columns — столбцы всех объектов со столбцами.
8. sys.types — типы данных.

Статистика может быть дополнительно оптимизирована с помощью следующих команд:

Более подробную информацию о команде UPDATE STATISTICS можно найти Вот,

Пример анализа и оптимизации показателей в dbForge Studio для SQL Server

В dbForge Studio для SQL Server, можно анализировать и оптимизировать уровень фрагментации индекса. Продукт Менеджер индекса dbForge также имеет эту функциональность.

В этом примере мы рассмотрим База данных SRV, которая предназначена для обслуживания СУБД MS SQL Server.

Эта база данных SRV распространяется свободно для любых целей. После открытия студии нажмите кнопку «Управление фрагментацией индекса…» на вкладке «Администрирование»:

На вкладке «Администрирование» выберите «Управление фрагментацией индекса…».

В открывшемся окне выберите сервер и нажмите «Параметры», чтобы настроить параметры:

Выбор сервера и настройка параметров.

В появившемся окне параметров установите нужные параметры:

Параметры оптимизации индексов.

Результаты настроек можно сохранить в виде bat-файла, нажав левую нижнюю кнопку «Сохранить командную строку…». И вы можете сбросить настройки по умолчанию, нажав правую нижнюю кнопку «Восстановить настройки по умолчанию».

Далее нажмите «ОК».

Теперь выберите нужную базу данных:

Выбор базы данных.

После этого начнется анализ. Вы также можете обновить анализ, нажав кнопку «Повторный анализ».

Когда анализ закончен, выберите нужные индексы для оптимизации:

Подбор показателей для оптимизации.

Обратите внимание, что результат анализа можно загрузить в виде файла CSV, нажав кнопку «Экспорт в CSV…».

В дальнейшем, оптимизация может быть выполнена несколькими способами:

1. создание сценария в новом окне студии (в меню «Сценарий изменений» выберите «В новое окно SQL»).
2. создание скрипта в буфер обмена (в меню «Изменения скрипта» выберите «В буфер обмена»).
3. запустить оптимизацию напрямую (нажмите кнопку «Исправить»).

Давайте выберем третий метод — нажмите кнопку «Исправить».

После того, как процесс оптимизации завершен, вам нужно снова нажать кнопку «Reanalyze»:

Анализ после оптимизации индексов.

Вывод

Проведен анализ уровня фрагментации показателей и степени устаревания статистики с последующей оптимизацией. Из приведенного выше примера также ясно, что Инструмент менеджера индексов dbForge позволяет быстро анализировать уровень фрагментации индекса, а также генерировать скрипт оптимизации индекса базы данных.

SQL SERVER 2008 – разница между Index Rebuild и Index Reorganize с примером T-SQL Script

Рекомендации: индекс должен быть перестроен когда фрагментация индекса более 40%. Реорганизацию имеет смысл делать если фрагментация колебелтся от 10% до 40%. Перестроение индеска серьезно нагружет процессор и лочит индекс. В версиях development и enterprise есть опция ONLINE, которую можно включить во время перестроения индекса. ONLINE оставит возможность использовния индекса во время перестроения.

Дефрагментация индексов со сбором статистики MS SQL 2008 R2

2012-10-24 в 9:15, admin , рубрики: MS SQL, t-sql, Администрирование баз данных, Песочница, метки: microsoft sql server, MS SQL, t-sql, администрирование баз данных

Одна из первых задач, которая которая возникает перед DBA после развертывания новой БД — это настройка планов по ее обслуживанию. Зачастую, в план обслуживания включается задача по дефрагментации индексов. Мне нравится, когда я знаю не только то, что дефрагментация выполнилась ночью с воскресенья на понедельник, но и то, как она прошла, сколько выполнялась, какие индексы были перестроены и в каком состоянии они остались после дефрагментации.

Для сбора такой статистики мною был написан небольшой скриптик, который собирает информацию о выполненной работе, а так же дает максимально подробное описание
о состоянии индексов до и после проделанной процедуры.

Но начнем с простого, создадим таблицу для хранения этих самых данных (я создал отдельную БД, куда складываю таблицы, которыми пользуюсь во время обслуживания баз данных сервера):

Столбец	Тип	Комментарий
proc_id	int	Порядковый номер процедуры, для идентификации
start_time	datetime	Начало выполнения запроса ALTER INDEX
end_time	datetime	Завершение выполнения запроса ALTER INDEX
database_id	smallint	Идентификатор БД
object_id	Int	Идентификатор таблицы
table_name	varchar(50)	Имя таблицы
index_id	Int	Идентификатор индекса
index_name	varchar(50)	Имя индекса
avg_frag_percent_before	float	Процент фрагментации индекса перед выполнением ALTER INDEX
fragment_count_before	bigint	Количество фрагментов до дефрагментации
pages_count_before	bigint	Количество страниц индекса до дефргаментации
fill_factor	tinyint	Уровень заполнения страниц индекса
partition_num	int	Номер секции
avg_frag_percent_after	float	Процент фрагментации индекса после выолнения ALTER INDEX
fragment_count_after	bigint	Количество фрагментов после дефрагментации
pages_count_after	bigint	Количество страниц индекса после дефргаментации
action	varchar(10)	Выполняемое действие

Вся процедура дефрагментации, будет брать данные из этой таблицы, а значит, надо ее заполнить:

Условия выборки:
page_count > 5 – я считаю, что перестраивать индексы с малым количеством страниц не имеет смысла, т.к. лучше не станет, а время, затраченное на выполнение процедуры — очень ценно, особенно если база работает круглосуточно и постоянно находиться под высокой нагрузкой.
avg_fragmentation_in_percent > 10 – Тоже очень субъективная цифра, практически во всей документации предлагают не трогать индекс, если его фрагментация составляет 10 или менее процентов, с чем я согласен, если у вас дела обстоят по другому, меняем.
dm.index_id > 0 – 0 – это куча

После того, как таблица заполнена, нам известно какие индексы необходимо обслужить.
Займемся делом:

Ну и на последок, соберем информацию о индексах после процедуры дефрагментации:

После выполнения такого скрипта, можно получить и посчитать очень много полезной информации. Например, время обслуживания всех индексов и каждого отдельно. Понять как это связано с размером индекса, увидеть эффективность данной операции. Собрав такую информацию за несколько раз, можно немного поменять процедуру, наверняка какие-то индексы фрагментируются больше и быстрее. В таком случае их обслуживание следует выполнять чаще. Как воспользоваться полученной информацией, решайте сами. Что же касается меня, то я, после анализа каждой такой процедуры, меняю планы обслуживания, если того требует ситуация. Мои базы работают под высокой нагрузкой круглосуточно. Поэтому постоянно перестраивать все индексы и на 1-2 часа снижать производительность сервера я не могу. Если Ваша БД, тоже работает круглосуточно, для выполнения таких вещей, следует настроить Resource Governor.

Хочу так-же заметить, что в инете есть подробные скрипты, но будьте бдительны, многие из них пользуются устаревшими командами.

Более подробно об используемых мною системных представлениях можно почитать в msdn:

freesql.blogspot.com

Перестроение и реорганизация индексов направлены на борьбу с различными типами фрагментации. В случае перепостроения индекс создается заново что влечет за собой наложение блокировок и недоступность индекса (или таблицы, если индекс кластерный) в течении всего этого процесса.
Реорганизация индексов — более легкая операция, таблицы и индексы в течении нее остаются доступными.
В процессе поддержки БД перепостроение и реорганизацию индексов, рекомендуется делать регулярно. Для индексов с фрагментацией менее 30 процентов достаточно реорганизации, если фрагментации больше, то следует делать перепостроение.
Скрипт для избирательно выполнения этих действий приведен ниже и взят отсюда

SQL SCRIPT:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DB_Maint_Indexes]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[DB_Maint_Indexes]
GO
CREATE PROCEDURE [dbo].[DB_Maint_Indexes]
AS
BEGIN
— This will work on MS SQL Server 2005+
— Based on the websites:
— http://msdn.microsoft.com/en-us/library/ms189858.aspx
— http://msdn.microsoft.com/en-us/library/ms189858(v=SQL.105).aspx
— http://msdn.microsoft.com/en-us/library/ms189858(v=SQL.90).aspx
—> 5% and 30% — ALTER INDEX REBUILD WITH (ONLINE = ON)*

— update all stats. use at your own discretion
—EXEC sp_updatestats;

DECLARE indexes_cur CURSOR
FOR
— Change the top to the number of indexes you want to update
SELECT TOP 20
CASE WHEN avg_fragmentation_in_percent > 30 THEN
‘ALTER INDEX ‘ + b.name + ‘ ON [‘ + s.name + ‘].[‘ + t.name
+ ‘] REBUILD;’
ELSE
‘ALTER INDEX ‘ + b.name + ‘ ON [‘ + s.name + ‘].[‘ + t.name
+ ‘] REORGANIZE;’
END AS [sSQL]
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
INNER JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
INNER JOIN sys.tables t on t.object_id = b.object_id
INNER JOIN sys.schemas s on t.schema_id = s.schema_id
WHERE NOT b.name IS NULL
AND avg_fragmentation_in_percent > 5
ORDER BY avg_fragmentation_in_percent DESC;

OPEN indexes_cur;
DECLARE @sSQL varchar(1024);
FETCH NEXT FROM indexes_cur INTO @sSQL;
WHILE (@@FETCH_STATUS = 0)
BEGIN
— EXECUTE THE SQL TO REBUILD THE INDEXES
EXECUTE (» + @sSQL + ‘;’);
FETCH NEXT FROM indexes_cur INTO @sSQL
END;
CLOSE indexes_cur;
DEALLOCATE indexes_cur;
END

DECLARE @SQL nvarchar(2048);
declare @dbname nvarchar(50);
declare @dbid int

set @dbname = ‘Office’;
set @dbid = DB_ID(‘Office’);

DECLARE reindex10 CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR

SELECT ‘ALTER INDEX ALL ON [‘ + @dbname+ ‘]..[‘ + OBJECT_NAME(afp.[OBJECT_ID]) + ‘] REBUILD WITH (SORT_IN_TEMPDB = Off);’ AS [Инструкция T-SQL]
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, ‘SAMPLED’) AS afp
WHERE afp.database_id = @dbid
AND afp.index_type_desc IN (‘CLUSTERED INDEX’)
AND (afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent 12
UNION ALL
SELECT [Инструкция T-SQL] =
CASE
WHEN afp.avg_fragmentation_in_percent >= 15
OR afp.avg_page_space_used_in_percent = 10)
OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent = 10 AND afp.avg_fragmentation_in_percent 60 AND afp.avg_page_space_used_in_percent 12
AND afp.[OBJECT_ID] NOT IN (
SELECT [OBJECT_ID]
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, ‘SAMPLED’)
WHERE database_id = @dbid
AND index_type_desc IN (‘CLUSTERED INDEX’)
AND (avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent 1
)
ORDER BY [Инструкция T-SQL]

OPEN GLOBAL reindex10
WHILE 1 = 1
BEGIN
FETCH reindex10 INTO @SQL
IF @@fetch_status <> 0 BREAK
—EXEC(@SQL)
PRINT @SQL

END
CLOSE GLOBAL reindex10
DEALLOCATE GLOBAL reindex10