Обслуживание баз данных для SharePoint Server 2010

Статья
03/05/2017

Последнее изменение раздела: 2016-11-30

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

Применимо к: Microsoft SharePoint Server 2010, Microsoft SharePoint Foundation 2010

Авторы: Билл Бэйр (Bill Baer) и Брайан Портер (Bryan Porter)

Технический рецензент: Пол С. Рэндал (Paul S. Randal) (SQLskills.com (Возможно, на английском языке))

Содержание

Введение
Проверка ошибок согласованности с помощью команды консоли БД DBCC CHECKDB
Команда консоли БД DBCC CHECKDB
Команда консоли БД DBCC CHECKDB и производительность
Оценка и уменьшение степени фрагментации индекса
Сравнение оперативного и автономного способов перестроения индекса
Оценка степени фрагментации в базах данных SQL Server 2008 и 2005 (sys.dm_db_index_physical_stats)
Уменьшение степени фрагментации базы данных
Уменьшение степени фрагментации отдельных таблиц и их индексов
Оптимизация производительности индекса с помощью коэффициента заполнения
Сжатие файлов данных
Создание планов обслуживания SQL Server 2008
Заключение

Примечание

Прежде чем выполнять любые задачи, связанные с обслуживанием или изменением баз данных SharePoint 2010, ознакомьтесь со статьей, посвященной поддержке изменений в базах данных, используемых серверными продуктами Office и службами Windows SharePoint Services.

Введение

Для оптимальной работы баз данных Microsoft SharePoint 2010 крайне важно своевременно выполнять стандартные процедуры обслуживания.

Ниже приводится список рекомендуемых задач обслуживания баз данных SharePoint 2010.

Проверка целостности базы данных.
Дефрагментация индексов путем их реорганизации или перестроения.
Настройка коэффициента заполнения для сервера.

Примечание

Эта статья посвящена обслуживанию базы данных. В ней не рассматриваются вопросы планирования мощности или производительности. Дополнительные сведения по этим темам можно найти в статье Планирование и настройка рабочих характеристик хранилища и SQL Server (SharePoint Server 2010).

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

Базы данных конфигурации.
Базы данных контента.
Базы данных профилей приложения-службы профилей пользователей.
Базы данных социального контента приложения-службы профилей пользователей.
Базы данных отчетов приложения-службы Web Analytics.
Промежуточные базы данных приложения-службы Web Analytics.
Базы данных службы автоматизации Word.

Задачи обслуживания баз данных можно выполнять как с помощью команд Transact-SQL, так и с помощью мастера обслуживания. В этой статье описываются доступные команды Transact-SQL, а также рассматривается процесс создания планов обслуживания с помощью мастера обслуживания баз данных Microsoft SQL Server. Здесь также приводятся подробные примеры для Microsoft SQL Server 2008 R2 и Microsoft SQL Server 2005.

Проверка ошибок согласованности с помощью команды консоли БД DBCC CHECKDB

В процессе обслуживания в первую очередь выполняется проверка согласованности, в ходе которой данные и индекс проверяются на отсутствие повреждений. Для внутренней проверки согласованности данных и страниц индекса используйте оператор команды консоли БД DBCC CHECKDB.

Проблемы согласованности базы данных в основном связаны с ошибками подсистемы ввода-вывода. Тем не менее, иногда они могут быть вызваны другими причинами, в том числе неправильным завершением работы сервера БД или сбоем диска. Зачастую признаками нарушения согласованности базы данных становится заметное снижение производительности и уровня доступности базы данных. Проверку согласованности БД SharePoint 2010 следует выполнять как минимум один раз в неделю, а также каждый раз при возникновении сбоев сервера БД или подсистемы ввода-вывода.

Команда консоли БД DBCC CHECKDB

Команда консоли БД DBCC CHECKDB проверяет логическую и физическую целостность всех объектов выбранной базы данных. Для этого выполняются следующие операции.

Эквивалент команды консоли БД DBCC CHECKALLOC, который обеспечивает проверку структур выделения в базе данных.
Эквивалент команды консоли БД DBCC CHECKTABLE, который обеспечивает проверку логической и физической целостности всех таблиц и представлений в базе данных.
Эквивалент команды консоли БД DBCC CHECKCATALOG, который обеспечивает проверку согласованности метаданных в базе.

Команду DBCC CHECKDB рекомендуется выполнять вместо отдельных операций (CHECKALLOC, CHECKTABLE и CHECKCATALOG), поскольку она определяет более широкий диапазон возможных ошибок и обеспечивает более высокий уровень безопасности при выполнении в рабочей среде.

Команда DBCC CHECKDB активно использует ресурсы подсистемы ввода-вывода, ЦП и памяти. Чтобы снизить нагрузку на рабочую систему, связанную с проверкой согласованности, рекомендуется выполнять команду DBCC CHECKDB в отношении восстановленных резервных копий баз данных SharePoint на отдельном сервере.

Мы рекомендуем сначала выполнить команду DBCC CHECKDB, а затем, если будут обнаружены какие-либо ошибки, восстановить последние резервные копии затронутых баз данных.

Важно!

Команду DBCC CHECKDB нельзя выполнять с параметром REPAIR_ALLOW_DATA_LOSS. При необходимости можно выполнить ее с параметрами REPAIR_FAST и REPAIR_REBUILD, которые обновляют индексы только в связанной базе данных.

Ниже показан пример выходных данных команды DBCC CHECKDB.

DBCC results for 'Contoso_Content_1'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 2663 rows in 21 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 309 rows in 4 pages for object "sys.sysrowsets".

...more

CHECKDB found 0 allocation errors and 0 consistency errors in database 'Contoso_Content_1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Дополнительные сведения об использовании команды DBCC CHECKDB в SQL Server 2008 см. в статье, посвященной команде консоли БД DBCC CHECKDB (Transact-SQL).

Команда консоли БД DBCC CHECKDB и производительность

Команда DBCC CHECKDB потребляет огромный объем ресурсов подсистемы ввода-вывода, ЦП, памяти и пространства во временной БД, в связи с чем ее рекомендуется выполнять в нерабочие часы. Существует распространенное заблуждение, что команда DBCC CHECKDB работает в блокирующем режиме, однако такое поведение наблюдалось только до версии SQL Server 2000. Дополнительные сведения см. в статье из цикла распространенных заблуждений администраторов баз данных SQL Server, которая посвящена блокирующему режиму при использовании команды DBCC CHECKDB (Возможно, на английском языке).

Выполнение команды DBCC CHECKDB зачастую сопряжено с существенными затратами ресурсов рабочей системы. В таких случаях не следует выполнять проверку согласованности таблиц по отдельности. Чтобы снизить нагрузку на рабочую систему в ходе проверки целостности, рекомендуется использовать один из следующих способов.

Используйте параметр WITH PHYSICAL_ONLY, чтобы уменьшить объем потребляемых ресурсов ЦП и памяти.
Выполните проверку целостности в отношении резервной копии базы данных, восстановленной на отдельном сервере SQL Server.

Дополнительные сведения об этих способах см. в записи блога Пола С. Рэндала (Paul S. Randal), посвященной вариантам проверки согласованности в сверхбольших базах данных с помощью команды CHECKDB (Возможно, на английском языке).

Оценка и уменьшение степени фрагментации индекса

Фрагментация индекса — это состояние, при котором определяемый ключом логический порядок страниц в таблице или индексе отличается от физического порядка размещения страниц в файлах данных. Это также может свидетельствовать о низкой плотности данных на страницах файлов данных, что влечет за собой неэффективное использование дискового пространства, памяти и ресурсов подсистемы ввода-вывода. Основными причинами фрагментации индекса являются многочисленные операции вставки, обновления или удаления данных в таблицах. На следующих рисунках показана разница между новым нефрагментированным индексом и таким же индексом после большого числа операций вставки, обновления и удаления. Красная стрелка определяет физический порядок размещения индекса, а черная — логический порядок страниц.

Рис. 1. Нефрагментированный индекс (источник: Пол С. Рэндал)

Нефрагментированный индекс

Рис. 2. Фрагментированный индекс (источник: Пол С. Рэндал)

Фрагментированный индекс

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

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

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

Например, в SharePoint 2010 сильно подвержена фрагментации таблица AllDocs, которая содержит библиотеки документов, связанные с ними документы, списки и элементы списков, а также соответствующие метаданные.

Степень фрагментации индекса определяется как доля страниц индекса, логический порядок размещения которых отличается от и физического.

Сравнение оперативного и автономного способов перестроения индекса

Оперативное перестроение индекса поддерживается только в выпусках SQL Server Enterprise, Developer и Evaluation Edition. Эти ограничения учитываются в методах, описываемых в данной статье. В тех случаях, когда выпуск SQL Server, в котором размещается рассматриваемая база данных, или сам индекс не поддерживает оперативное перестроение индекса, приведенные в этой статье процедуры выполняют автономное перестроение. Оперативное перестроение может быть недоступно для индекса, содержащего столбцы с крупными объектами, например столбцы с типом данных NVARCHAR(MAX), IMAGE и т. д.

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

Обратите внимание, что при оперативном перестроении в двух случаях выполняется кратковременная блокировка таблиц. В связи с этим перестроение индекса рекомендуется всегда планировать на периоды минимальной нагрузки.

Оценка степени фрагментации в базах данных SQL Server 2008 и 2005 (sys.dm_db_index_physical_stats)

Чтобы определить степень фрагментации таблицы или представления в SQL Server 2008 и SQL Server 2005, следует использовать динамическое административное представление sys.dm_db_index_physical_stats.

Оценка осуществляется на основании значения в столбце avg_fragmentation_in_percent. Максимальная производительность достигается в тех случаях, когда значение в столбце avg_fragmentation_in_percent близко к нулю. Приемлемыми считаются значения в диапазоне от 0 до 10%. Дополнительные сведения см. в статье, посвященной представлению sys.dm_db_index_physical_stats.

В следующей таблице показан пример представления sys.dm_db_index_physical_stats, в столбце avg_fragmentation_in_percent которого присутствует значение 9,375.

`database_id`	`index_type_desc`	`alloc_unit_type_` `desc`	`avg_fragmentation_` `in_percent`
10	`CLUSTERED INDEX`	`IN_ROW_DATA`	`0`
`10`	`NONCLUSTERED INDEX`	`IN_ROW_DATA`	`0`
`10`	`NONCLUSTERED INDEX`	`IN_ROW_DATA`	`0`







`10`	`CLUSTERED INDEX`	`IN_ROW_DATA`	`0`
`10`	`NONCLUSTERED INDEX`	`IN_ROW_DATA`	`0`
`10`	`CLUSTERED INDEX`	`IN_ROW_DATA`	`9.375`

Использование динамического административного представления sys.dm_db_index_physical_stats

Нажмите кнопку Пуск в панели задач и последовательно выберите Все программы, Microsoft SQL Server 2008, SQL Server Management Studio.

Чтобы использовать представление sys.dm_db_index_physical_stats для объекта базы данных, требуются ИД базы данных и объекта.
Выберите базу данных контента в обозревателе объектов и щелкните элемент Создать запрос. Выполните следующий скрипт.

SELECT DB_ID() AS [Database ID];

Примечание

Если DB_ID используется без имени базы данных, текущая база должна иметь уровень совместимости 100 (БД SQL Server 2008) или 90 (БД SQL Server 2005). Если было выполнено обновление с более ранней версии SQL Server, в выражении DB_ID необходимо указать имя базы данных. Дополнительные сведения об уровнях совместимости см. в статье, посвященной хранимой процедуре sp_dbcmptlevel (Transact-SQL).
Выполните представление sys.dm_db_index_physical_stats в отношении выбранных базы данных или объекта. При этом можно указать базу данных, таблицу или индекс.

При выполнении представления sys.dm_db_index_physical_stats используйте следующий синтаксис.
```
sys.dm_db_index_physical_stats ( 
    { database_id | NULL | 0 | DEFAULT }
    , { object_id | NULL | 0 | DEFAULT }
    , { index_id | NULL | 0 | -1 | DEFAULT }
    , { partition_number | NULL | 0 | DEFAULT }
    , { mode | NULL | DEFAULT }
)
```
Тщательно планируйте использование динамического административного представления sys.dm_db_index_physical_stats, поскольку оно чрезвычайно ресурсоемко. Полное руководство по использованию этого представления см. в посвященной ему статье (Возможно, на английском языке).

Уменьшение степени фрагментации базы данных

В этом разделе приводятся рекомендации по уменьшению степени фрагментации базы данных.

Обслуживание базы данных с использованием правил анализатора работоспособности

В состав SharePoint 2010 входит платформа правил анализатора работоспособности. Она содержит большое число правил, позволяющих контролировать исправность и работоспособность среды SharePoint, а в некоторых случаях и предпринимать определенные корректирующие действия.

В SharePoint 2010 реализовано несколько правил, предназначенных для обслуживания баз данных контента. С их помощью можно автоматически уменьшать степень фрагментации некоторых баз данных SharePoint, а также обнаруживать и при необходимости обновлять неактуальные статистические сведения. Эти правила реализованы вместо устаревшего задания таймера "Статистика базы данных", которое было представлено в пакете обновления 2 для продуктов и технологий SharePoint. В зависимости от целевого объекта эти правила по умолчанию выполняются ежедневно, еженедельно или по запросу.

Все правила анализатора работоспособности, которые настроены на ежедневное выполнение и связаны с одной службой SharePoint, выполняются в одном задании таймера. Изменение расписания такого задания затронет все входящие в него правила. Все описываемые в этой статье правила связаны со службой времени SharePoint.

Правила анализатора работоспособности, которые выполняются с разными интервалами (например, еженедельно) или связаны с разными службами, выполняются в отдельных заданиях таймера. Правило, настроенное для еженедельного выполнения, будет выполняться в еженедельном задании таймера, связанном с соответствующей службой. Другими словами, правило будет выполняться по расписанию, определяемому заданием таймера.

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

Используемые SharePoint базы данных содержат фрагментированные индексы. В рамках этого правила выполняются следующие задачи.

Это правило выполняется после других правил анализатора работоспособности и присваивает всем индексам статус фрагментированных. Это позволяет предпринимать необходимые корректирующие действия без чрезмерных затрат ресурсов, с которыми сопряжена оценка состояния индекса.
Для каждой базы данных SharePoint правило ищет и, если находит, выполняет хранимую процедуру proc_DefragmentIndices, которая формирует список всех индексов базы. Для каждого индекса оценивается текущая степень фрагментации. Каждый индекс со степенью фрагментации более 30% рассматривается как требующий перестроения.
Если выпуск SQL Server поддерживает оперативное перестроение индекса, для каждого индекса предпринимается попытка выполнить соответствующую процедуру. Если это сделать не удается, например, если индекс не поддерживает оперативное перестроение или содержит столбцы с крупными объектами, выполняется автономное перестроение.

Как уже отмечалось, это правило применяется не ко всем базам данных в среде SharePoint. Для некоторых баз аналогичные операции обслуживания выполняются с помощью других правил.

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

В рамках правила Search - One or more property databases have fragmented indices выполняются следующие задачи.

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

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

В рамках правила Search - One or more property databases have fragmented indices выполняются следующие задачи.

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

Поиск — одна или несколько баз данных обхода контента могут иметь фрагментированные индексы. Это правило предназначено для обслуживания индексов баз данных обхода контента поиска SharePoint 2010 Enterprise. По умолчанию это правило выполняется только по требованию. Это правило можно запускать с любого сервера фермы.

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

Для обслуживания индексов баз данных обхода контента вручную необходимо полностью отключить правило Search - One or more crawl databases may have fragmented indices.

В рамках правила Search - One or more crawl databases may have fragmented indices выполняются следующие задачи.

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

Важно!

Правило Search - One or more crawl databases may have fragmented indices перестраивает каждый индекс в базах данных обхода контента независимо от степени фрагментации. Также это правило выполняет сжатие данных на уровне страницы, если это поддерживается выпуском SQL Server, в котором размещается база данных обхода контента.

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

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

База данных администрирования поиска
База данных Secure Store
База данных службы состояний
База данных синхронизации профилей
База данных использования
База управляемых метаданных
База данных служб Business Connectivity Services
База данных служб PerformancePoint Services

Дополнительные сведения об изменениях, поддерживаемых для баз данных SharePoint 2010, см. в статье, которая посвящена поддержке изменений в базах данных, используемых серверными продуктами Office и службами Windows SharePoint Services в базе знаний Майкрософт.

Если частая дефрагментация не позволяет заметно повысить производительность значительно фрагментированной базы данных или таблицы, следует проверить производительность подсистемы ввода-вывода.

Уменьшение степени фрагментации отдельных таблиц и их индексов

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

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

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

Степень фрагментации	Способ дефрагментации
До 10%	Реорганизация (оперативный режим)
От 10 до 75%	Перестроение (оперативный режим)
75%	Перестроение (автономный режим)

Примечание

Базы данных SharePoint 2010 не поддерживают команды DROP INDEX и CREATE INDEX.

Для реорганизации и перестроения индексов можно использовать выражение ALTER INDEX SQL Server 2008 или SQL Server 2005, а также мастер планов обслуживания SQL Server 2008 или SQL Server 2005. В этой статье подробно рассматриваются только способы для SQL Server 2008 и SQL Server 2005.

Использование выражения ALTER INDEX

С помощью выражения ALTER INDEX администратор базы данных может выполнять операции обслуживания по отношению к индексу таблицы или представления. С его помощью можно отключать, перестраивать или реорганизовать индексы. Кроме того, это выражение можно использовать для установки параметров индекса. В большинстве случаев перестроение индекса может выполняться в оперативном режиме базы данных, что, в отличие от автономного режима, позволяет обеспечить доступность данных пользователям.

Важно!

В SQL Server 2000 для обслуживания индекса поддерживаются команды DBCC DBREINDEX и DBCC INDEXDEFRAG. Начиная с выпуска SQL Server 2005 они являются нерекомендуемыми и будут исключены в последующих выпусках SQL Server. Не используйте эти команды для обслуживания индекса баз данных SharePoint 2010.

Примечание

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

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

USE Contoso_Content_1
GO
ALTER INDEX ALL ON [database_name. [ schema_name ] . | schema_name. ]table_or_view_name
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
GO

Оптимизация производительности индекса с помощью коэффициента заполнения

Для дополнительной оптимизации хранения данных и производительности индекса можно использовать коэффициент заполнения. При создании или перестроении индекса коэффициент заполнения (от 1 до 100) определяет процентную долю пространства на уровне каждой конечной страницы, которое может быть заполнено данными. Оставшаяся часть резервируется для дальнейшего расширения. В большинстве случаев оптимально подходит установленный на уровне сервера по умолчанию коэффициент заполнения 0 (каждая страница может заполняться на 100%). Тем не менее, в SharePoint 2010 можно использовать значение 80, которое задается на уровне сервера и обеспечивает оптимальные возможности расширения и минимальную степень фрагментации.

Примечание

Мы не рекомендуем задавать коэффициент заполнения для отдельных таблиц или индексов. Несмотря то, что этот способ рекомендуется при работе с базами, отличными от SharePoint SQL Server, для баз данных SharePoint по результатам тестирования оптимальным является значение коэффициента 80%.

Чтобы просмотреть значение коэффициента заполнения для одного или нескольких индексов, выполните запрос представления каталога sys.indexes. Дополнительные сведения см. в статье, посвященной представлению sys.indexes (Transact-SQL).

Чтобы задать значение коэффициента заполнения на уровне сервера, используйте системную хранимую процедуру sp_configure. Дополнительные сведения см. в статье, посвященной процедуре spconfigure (Transact-SQL).

Сжатие файлов данных

В SQL Server 2008 и SQL Server 2005 можно сжать любые файлы в базе данных (расширения MDF, LDF и NDF), чтобы удалить неиспользуемые страницы и высвободить дисковое пространство. Несмотря на то, что многие операции в базах SharePoint 2010 создают неиспользуемые области, в них не осуществляется автоматическое сжатие файлов данных. К таким операциям относятся команда Windows PowerShell Move-SPSite, а также удаление документов, библиотек, списков, элементов списков и сайтов.

Рис. 3. Распределение памяти в базе данных

Размещение баз данных

Неиспользуемое пространство высвобождается только с конца файла. Например, для файла базы данных контента размером 60 ГБ с заданным целевым размером 40 ГБ можно освободить максимум 20 ГБ с конца файла (концептуально — с его правого конца). Используемые страницы из последних 20 ГБ перемещаются в начальные 40 ГБ. Файлы баз данных можно сжимать как по отдельности, так и в группе.

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

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

Если вам все же необходимо выполнить сжатие базы данных, придерживайтесь следующих рекомендаций.

Не выполняйте сжатие автоматически и не планируйте сжатие программным способом в рамках плана обслуживания.
Прибегайте к сжатию только в тех случаях, когда удалено более 50% контента и вы не планируете повторно использовать высвободившееся место.
Сжимайте только базы данных контента. Из баз данных конфигурации, контента центра администрирования и различных приложений-служб практически никогда не удаляется объем контента, достаточный для эффективного выполнения этой операции.
Сжатие баз данных — чрезвычайно ресурсоемкий процесс. Поэтому, если без него никак не обойтись, тщательно планируйте время его выполнения.
После сжатия базы данных ее индексы будут сильно фрагментированы. Используйте выражения ALTER INDEX… REORGANIZE для дефрагментации. Если в вашей среде не настроена мгновенная инициализация файлов, при сжатии базы данных устанавливайте целевой размер, соответствующий предполагаемому расширению базы в кратчайшей перспективе. Дополнительные сведения см. в статье, посвященной инициализации файлов базы данных.

Чтобы высвободить некоторое пространство, можно сжать базу данных и ее файлы вручную с помощью выражений DBCC SHRINKFILE и DBCC SHRINKDATABASE в среде Management Studio SQL Server 2008 или SQL Server 2005.

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

Сжатие базы данных с помощью команд Transact-SQL

Команда DBCC SHRINKDATABASE предназначена для сжатия данных и файлов журнала отдельной базы. Для сжатия отдельных файлов используйте команду DBCC SHRINKFILE.

DBCC SHRINKDATABASE

Команда DBCC SHRINKDATABASE использует следующий синтаксис.

DBCC SHRINKDATABASE 
( 'database_name' | database_id | 0 
     [ ,target_percent ] 
     [ , { NOTRUNCATE | TRUNCATEONLY } ] 
)
[ WITH NO_INFOMSGS ]

database_name | database_id | 0 — имя или ИД базы данных. Чтобы выбрать текущую базу данных, укажите значение 0.

target_percent — объем свободного пространства (в процентном выражении), который должен остаться после сжатия базы данных.

NOTRUNCATE — сжатие данных в файлах посредством переноса выделенных страниц из конца файла на невыделенные страницы в его начале.

TRUNCATEONLY — высвобождение всего неиспользуемого пространства в конце файла для операционной системы без перемещения страниц в пределах файла.

Примечание

Параметр TRUNCATEONLY не поддерживается для баз данных SharePoint 2010.

Дополнительные сведения см. в статье, посвященной команде DBCC SHRINKDATABASE (Transact-SQL).

DBCC SHRINKFILE

Команда DBCC SHRINKFILE использует следующий синтаксис.

DBCC SHRINKFILE 
(
     { 'file_name' | file_id } 
    { [ , EMPTYFILE ] 
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]

file_name | file_id — имя или ИД файла.

EMPTYFILE — миграция всех данных из указанного файла в другие файлы той же группы.

Важно!

Параметр EMPTYFILE не поддерживается для баз данных SharePoint 2010.

target_size — целевой размер файла в мегабайтах (целое число).

Важно!

Параметр TRUNCATEONLY не поддерживается для баз данных SharePoint 2010.

Дополнительные сведения см. в статье, посвященной команде DBCC SHRINKFILE (Transact-SQL).

Сжатие базы данных с помощью SQL Server 2008 Management Studio

Используйте следующую процедуру.

Сжатие базы данных с помощью SQL Server 2008 Management Studio

Нажмите кнопку Пуск в панели задач и последовательно выберите пункты Все программы , Microsoft SQL Server 2008 и SQL Server Management Studio .
В обозревателе объектов подключитесь к экземпляру ядра СУБД SQL Server 2008 и разверните его.
Разверните узел Базы данных, щелкните правой кнопкой мыши базу, которую требуется сжать, и последовательно выберите Задачи, Сжать, Файлы.
Выберите тип и имя файла.
Щелкните элемент Реорганизация файлов перед освобождением неиспользуемого пространства. Также необходимо задать значение параметра Сжатие файла. Этот параметр позволяет высвободить неиспользуемое пространство в файле для операционной системы и переместить строки на невыделенные страницы.
Нажмите кнопку ОК.

Создание планов обслуживания SQL Server 2008

Большинство операций обслуживания баз данных, описываемых в этой статье, можно применять программным способом с использованием планов обслуживания SQL Server. С их помощью можно автоматизировать и планировать важные задачи, позволяющие защитить данные. Используя планы обслуживания в SQL Server 2008 или SQL Server 2005, администратор может планировать различные операции, в том числе проверку согласованности базы данных, реорганизацию и перестроение индексов. Дополнительные сведения см. в следующих источниках.

Мастер планов обслуживания — для SQL Server 2008.
Мастер планов обслуживания — для SQL Server 2005.

Настройка плана обслуживания баз данных SQL Server 2008

Нажмите кнопку Пуск в панели задач и последовательно выберите пункты Все программы , Microsoft SQL Server 2008 и SQL Server Management Studio .
В обозревателе объектов подключитесь к экземпляру ядра СУБД SQL Server 2008 и разверните его.
Выберите элемент Управление, щелкните правой кнопкой мыши пункт Планы обслуживания и выберите пункт Мастер планов обслуживания.
Нажимайте кнопку Далее до тех пор, пока не откроется страница "Выбор свойств плана".

Рис. 4. Страница "Выбор свойств плана"
Введите имя и описание плана в полях Имя и Описание соответственно.
Укажите, требуется ли настроить один или несколько планов.
- Чтобы настроить один план обслуживания, установите переключатель Единое расписание для всего плана или без расписания.
- Чтобы настроить несколько планов обслуживания с отдельными задачами, установите переключатель Отдельные расписания для каждой задачи.
Если в вашей среде присутствует более 10 баз данных контента или общий объем контента превышает 200 ГБ, рекомендуется настраивать отдельные планы обслуживания. Это позволяет обеспечить надлежащую избирательность и эффективность обслуживания.

Если для базы данных настроено несколько планов обслуживания, необходимо указать их имена или описания, позволяющие различать планы по предназначению и расписанию.
Нажмите кнопку Изменить, чтобы задать расписание настроенных планов.

Откроется диалоговое окно Свойства расписания задания.

Рис. 5. Диалоговое окно "Свойства расписания задания"
Настройте расписание, нажмите кнопку ОК и затем кнопку Далее.
На странице "Выбор задач по обслуживанию" выберите задачи, которые необходимо включить в план, и нажмите кнопку Далее.

Рис. 6. Страница "Выбор задач по обслуживанию"

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

Примечание

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

Рис. 7. Страница "Выбор порядка задач по обслуживанию"
На следующих страницах мастера можно настроить параметры отдельных задач. На странице "Задача "Проверка целостности базы данных"" выберите базы, для которых требуется проверить целостность, и нажмите кнопку Далее.

Примечание

Проверять на целостность можно любые базы данных SharePoint 2010.

Рис. 8. Страница "Задача "Проверка целостности базы данных""
На странице "Задача "Реорганизация индекса"" в списке Базы данных определите базы, для которых требуется реорганизовать индексы, установите флажок Сжатие больших объектов и нажмите кнопку Далее.

Рис. 9. Страница "Задача "Реорганизация индекса""
Если вместо реорганизации будет выполняться перестроение индекса, выберите базы в списке Базы данных на странице "Задача "Перестроение индексов"".
Установите переключатель Изменить долю свободного места на странице, введите значение 80 и нажмите кнопку Далее.

Значение параметра Изменить долю свободного места на странице задает коэффициент заполнения для базы данных.

Рис. 10. Страница "Задача "Перестроение индексов""
Укажите нужные значения на странице "Определить задачу "Очистка после обслуживания"" и нажмите кнопку Далее.

Совет

Мы рекомендуем удалять текстовые отчеты по планам обслуживания.

Рис. 11. Страница "Определить задачу "Очистка после обслуживания""
На странице "Выбор параметров отчета" установите флажок Сохранить отчет в текстовый файл, укажите папку для хранения файлов. Нажимайте кнопку Далее, чтобы завершить работу мастера.

Рис. 12. Страница "Выбор параметров отчета"