Монитоинг и обслуживание индексов баз данных сервера планирования

При создании модели Сервер планирования создает один кластерный индекс для новой таблицы с именем MG_*. Такой кластерный индекс включает все ключи измерений в этой таблице групп мер. Порядок ключей измерений в этом кластерном индексе Сервер планирования выбирает произвольно. Такой порядок может не самым лучшим образом соответствовать использованию таблицы групп мер отдельным клиентом (например для обратной записи, загрузки данных и для вычисления на основе правил). Администратору базы данных может потребоваться проверить порядок столбцов кластерного индекса и изменить его.

Например, приведенный ниже кластерный индекс создан для таблицы MG_Strategic_Plan_MeasureGroup_default_partition со следующим заранее установленным порядком столбцов. Можно изменить порядок столбцов в этом индексе, чтобы он лучше подходил для такого использования таблицы групп мер. Также может понадобиться переместить столбец Entity_MemberID на место первого столбца этого индекса, потому что пользователи Надстройка PerformancePoint для Excel часто выполняют операции обратной записи на основе измерения "Объект".

CREATE CLUSTERED INDEX [ClusteredIndex_default_partition] ON [dbo].[MG_Strategic_Plan_MeasureGroup_default_partition] 
(
      [Scenario_MemberId] ASC,
      [Time_Month] ASC,
      [Account_MemberId] ASC,
      [BusinessProcess_MemberId] ASC,
      [Entity_MemberId] ASC,
      [TimeDataView_MemberId] ASC,
      [Currency_MemberId] ASC,
      [BusinessDriver_MemberId] ASC,
      [Product_MemberId] ASC,
      [Flow_MemberId] ASC,
      [Intercompany_MemberId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Пример

ALTER INDEX [ClusteredIndex_default_partition] 
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REBUILD WITH (ONLINE = ON);

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

В SQL Server 2005 можно устранить фрагментацию индекса с помощью или реорганизации, или перестроения индекса. Перед принятием решения, какой метод дефрагментации использовать, необходим анализ индекса для определения степени его фрагментации.

Пример

В следующем примере показано, как отслеживать степень фрагментации индекса.

Если выполнить следующую кодовую инструкцию:

SELECT a.index_id, name, avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('MG_Strategic_Plan_MeasureGroup_default_partition'),
     NULL, NULL, NULL) AS a
        JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

будет возвращен набор результатов, подобный следующему.

index_id	имя	avg_fragmentation_in_percent
1	MG_Strategic_Plan_MeasureGroup_default_partition_PK	23.076923076923077

Дополнительные сведения о фрагментации см. в разделе SQL Server Books Online. В данном примере рекомендуется реорганизовать индекс MG_Strategic_Plan_MeasureGroup_default_partition_PK, потому что значение параметра avg_fragmentation_in_percent меньше 30 процентов.

При степени фрагментации больше, чем 30 процентов, было бы рекомендовано выполнить перестроение индекса.

Примечание.
Упомянутые выше 30 процентов взяты из рекомендации в разделе SQL Server Books Online. Можно изменить данное число, чтобы оно наилучшим образом подходило для конкретной ситуации.

Рекомендуется выполнять реорганизацию индекса, если индекс не сильно фрагментирован. Однако если индекс сильно фрагментирован, лучшие результаты будут достигнуты при перестроении индекса. Рекомендации по поводу фрагментации см. в Checking Index Fragmentation.

Пример

ALTER INDEX [ClusteredIndex_default_partition] 
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REORGANIZE ;

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

В следующем примере показано построение индекса заново с интерактивным параметром для таблицы групп мер в Сервер планирования.

ALTER INDEX [ClusteredIndex_default_partition] 
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REBUILD WITH (ONLINE = ON);

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

Перестроение индекса обычно дает лучшие результаты, чем его реорганизация. При этом устраняются все уровни фрагментации, и с конечного уровня, и из дерева B, благодаря чему восстанавливается баланс дерева. Перестроением индекса обновляется статистика, так как его действие аналогично «полному сканированию» с точной статистикой.

Дополнительные сведения о перестроении индекса см. в разделе SQL Server Books Online.

Понятия

Автоматизация обслуживания индекса с использованием сценариев
Как автоматизировать обслуживание индекса