Меня зовут Любовь Волкова, я системный архитектор департамента разработки бизнес-решений. В предыдущем материале мною было рассказано о мониторинге счетчиков производительности SharePoint Server 2013/2016. Сегодня речь пойдет о не менее важной задаче – обслуживании баз данных SharePoint 2013/2016.
В состав любой фермы Microsoft SharePoint 2013/2016 обязательно входит SQL-сервер, который обеспечивает хранение данных различных служб и компонентов, развернутых для работы корпоративного портала. Как известно, производительность работы с базами данных оказывает существенное влияние на работу корпоративного портала в целом. Если не уделять внимание обслуживанию баз данных SharePoint или делать это безграмотно, то на практике встречаются следующие явные проблемы: снижение производительности работы на корпоративном портале, которая очень часто выражается в постепенном увеличении времени ожидания загрузки страниц, форм, данных на веб-страницах, а также дублирование действий и их несогласованность в части администрирования.
Очень часто разработка планов обслуживания вызывает существенные затруднения у администраторов порталов ввиду сложности, комплексности и необходимости обладать компетенциями не только по администрированию SharePoint, но и по администрированию SQL-сервера. Поэтому в статье я выполнила обзор всех ключевых операций обслуживания баз данных, привела примеры скриптов и практические советы. Пользуясь приведенными описаниями, вы найдете множество рекомендаций к действию. Отмечу, что материалы статьи не являются исчерпывающими, т.к. включить описание абсолютно всех деталей и тонкостей обслуживания баз данных SharePoint не представляется возможным. Для того, чтобы вы могли получить более подробную информацию по той или иной теме, приведены ссылки на дополнительные публикации.
Все многообразие баз данных, от которого зависит работа SharePoint, можно разделить на три группы:
Для оптимальной работы баз данных Microsoft SharePoint 2013/2016 крайне важно своевременно выполнять стандартные процедуры обслуживания всех связанных баз данных.
Ниже приводится список рекомендуемых задач обслуживания баз данных SharePoint:
Эти задачи обслуживания баз данных могут выполняться посредством команд Transact-SQL, пользовательского интерфейса, а также мастера обслуживания.
Общие сведения о назначении, требованиях к расположению, моделях восстановления по умолчанию и др. информацию по каждой из баз данных можно прочитать здесь.
Данная статья ориентирована на системных администраторов, администраторов баз данных и технических специалистов, занимающихся поддержкой работы корпоративных порталов SharePoint.
Команда DBCC CHECKDB обеспечивает комплексную проверку логической и физической целостности всех объектов в указанной базе данных и включает следующий набор проверок:
Пример скрипта проверки целостности базы данных контента SharePoint «WSS_Content»:
Пример результатов выполнения скрипта, свидетельствующие об отсутствии ошибок:
Пример результатов, указывающих на наличие ошибок и необходимости решить проблему для конкретной базы данных:
Команда DBCC CHECKDB активно использует ресурсы подсистемы ввода-вывода, ЦП и памяти. Чтобы снизить нагрузку на рабочую систему, связанную с проверкой согласованности, рекомендуется выполнять команду DBCC CHECKDB в отношении восстановленных резервных копий баз данных SharePoint на отдельном сервере.
Если команда DBCC CHECKDB выполняется на рабочей базе данных, ее рекомендуется выполнять в нерабочие часы, ограничиваясь проверкой целостности физической структуры страниц и заголовков записей и целостности выделения пространства в базе данных.
Фрагментация индекса — это состояние, при котором определяемый ключом логический порядок страниц в индексе отличается от физического порядка размещения страниц в файлах данных. Это также может свидетельствовать о низкой плотности данных на страницах файлов данных, что влечет за собой неэффективное использование дискового пространства, памяти и ресурсов подсистемы ввода-вывода.
Основными причинами фрагментации индекса являются многочисленные операции вставки, обновления или удаления данных в таблицах. На Рисунок 1 и Рисунок 2 показана разница между новым нефрагментированным индексом и таким же индексом после большого числа операций вставки, обновления и удаления. Красная стрелка определяет физический порядок размещения индекса, а черная — логический порядок страниц.
Рисунок 1. Нефрагментированный индекс (источник: Пол С. Рэндал)
Рисунок 2. Фрагментированный индекс (источник: Пол С. Рэндал)
Поскольку вставка, обновление и удаление данных в строках таблиц и индексов осуществляется неравномерно, каждая страница в разные моменты времени будет иметь разные уровни полноты (плотности данных). В таком случае при обработке запроса, для которого требуется сканирование части или всех индексов таблицы, высокая степень фрагментации повлечет за собой увеличение числа операций чтения страниц, что, в свою очередь, будет препятствовать параллельному сканированию данных и приведет к существенному снижению производительности.
Фрагментация индекса может привести к снижению производительности и неэффективному использованию пространства. При этом даже в базах данных с невысокой интенсивностью использования степень фрагментации индексов может расти быстрыми темпами.
Для снижения фрагментации индексов поддерживаются два метода:
Оперативное перестроение индекса поддерживается только в выпусках SQL Server Enterprise. В тех случаях, когда выпуск SQL Server, в котором размещается рассматриваемая база данных, или сам индекс не поддерживает оперативное перестроение индекса, приведенные процедуры выполняют в автономном режиме. Оперативное перестроение может быть недоступно для индекса, содержащего столбцы с крупными объектами, например, столбцы с типом данных NVARCHAR(MAX), IMAGE и т. д.
В процессе автономного перестроения блокируется уровень таблиц, в результате чего они становятся недоступны для записи или даже для просмотра. Многие индексы баз данных SharePoint содержат столбцы с крупными объектами и поэтому всегда перестраиваются с применением автономного процесса.
Следует обратить внимание, что при оперативном перестроении может выполняться кратковременная блокировка таблиц, в результате чего запрещаются любые операции, за исключением SELECT. В базах данных SharePoint 2013/16 кластеризованные индексы используются особым образом. При перестроении такого индекса в автономном режиме к таблице применяется монопольная блокировка, которая полностью запрещает доступ пользователей к ней.
Предпочтительный способ дефрагментации индекса зависит от степени его фрагментации, а также от того, может ли он при этом оставаться в оперативном режиме или должен обрабатываться в автономном. В следующей таблице описываются рекомендуемые способы дефрагментации для индексов с различной степенью фрагментации для баз данных SharePoint:
В SharePoint 2013/2016 процесс мониторинга службы и компонентов можно частично автоматизировать с помощью правил анализатора работоспособности SharePoint (проверки статистики индексов и проверки фрагментации индексов для баз данных обхода службы поиска). Их применение позволяет ежедневно оценивать состояние, а также автоматически обслуживать индексы для следующих баз данных:
Просмотреть полный перечень системных заданий, связанных с анализом работоспособности, можно в Центре Администрирования SharePoint, перейдя в раздел Отслеживание\Просмотр определений заданий. Примеры системных заданий:
Многие базы данных SharePoint включают хранимые процедуры, используемые анализатором работоспособности для дефрагментации индексов. Хотя эти правила автоматически выполняют дефрагментацию, возможно, что потребуется запустить дефрагментацию вручную. Например, это может потребоваться ваши SLA или некоторые корпоративные регламенты.
Вы можете запланировать эти хранимые процедуры для запуска ежедневно, еженедельно или ежемесячно в зависимости от ваших потребностей и общих показателей изменения в вашем окружении.
Рекомендуется позволить SharePoint управлять своими собственными индексами базы данных. Если требуется вручную выполнить это действие, как минимум рекомендуется настроить ежедневное расписание обслуживания, чтобы выполнялась процедура proc_DefragmentIndices и еженедельное выполнение процедуры proc_MSS_DefragSearchIndexes.
Прежде чем приступить к реализации плана обслуживания фрагментированного индекса, необходимо определить наиболее фрагментированные таблицы и индексы, и только после этого разработать план их перестроения и реорганизации.
Например, в SharePoint 2013/2016 сильно подвержена фрагментации таблица AllDocs, которая содержит библиотеки документов, связанные с ними документы, списки и элементы списков, а также соответствующие метаданные. Несмотря на наличие автоматизированных системных заданий анализатора работоспособности, их работа далеко не всегда оказывается эффективной
Степень фрагментации индекса определяется как доля страниц индекса, логический порядок размещения которых отличается от и физического. Чтобы определить ее в SQL Server 2014 и SQL Server 2016, следует использовать динамическое административное представление sys.dm_db_index_physical_stats.
Вы можете использовать следующий скрипт (на примере базы данных контента SharePoint «WSS_Content»):
Результирующий набор, возвращаемый функцией sys.dm_db_index_physical_stats, включает следующие столбцы:
Пример вывода результата анализа фрагментации индексов:
Ниже приведен пример скрипта, обеспечивающего выполнение реорганизации одного из индексов таблицы «AllDocs» базы данных контента SharePoint:
Пример скрипта для перестроения всех индексов таблицы отдельной базы данных SharePoint:
Пример скрипта для перестроения отдельного индекса
Пример полученного результата (проверка дефрагментации индекса после перестроения:
Для дополнительной оптимизации хранения данных и производительности индекса можно использовать коэффициент заполнения. При создании или перестроении индекса коэффициент заполнения (от 1 до 100) определяет процентную долю пространства на уровне каждой конечной страницы, которое может быть заполнено данными. Оставшаяся часть резервируется для дальнейшего расширения. В большинстве случаев оптимально подходит установленный на уровне сервера по умолчанию коэффициент заполнения 0 (каждая страница может заполняться на 100%). Тем не менее, в SharePoint 2013 можно использовать значение 80, которое задается на уровне сервера и обеспечивает оптимальные возможности расширения и минимальную степень фрагментации.
Коэффициент заполнения (параметр «fill factor») служит для точной настройки хранения и производительности индекса. При создании или перестроении индекса коэффициент заполнения отображает процент заполнения пространства каждой страницы конечного уровня, что позволяет зарезервировать для будущего расширения оставшееся на каждой странице пространство как свободное. Например, при указании для коэффициента заполнения значения 80 на каждой странице конечного уровня будет зарезервировано 20 процентов занимаемого ею дискового пространства. Данное дисковое пространство будет использовано для расширения индекса при добавлении в базовую таблицу новых данных. Пустое место резервируется не в конце индекса, а между строками индекса.
Коэффициент заполнения — это значение в процентах от 1 до 100; значение по умолчанию на сервере — 0, что означает полное заполнение страниц конечного уровня.
Для SharePoint для поддержки роста баз данных и снижения уровня фрагментации индексов оптимальным является значение 80.
Следующий скрипт позволят получить информацию от текущих настройках коэффициента заполнения индекса по умолчанию на уровне SQL-сервера:
Пример результата:
Следует позаботиться, чтобы никоим образом не было включено автоматическое сжатие баз данных SharePoint. Сжатие можно использовать для уменьшения размера файла данных или журнала транзакций, но это очень грубый, ресурсоемкий процесс, который вызывает широкую логическую фрагментацию просмотра в файлах данных и ведет к низкой производительности. Сжатие отдельных файлов данных и журнала вручную может быть допустимо при особых обстоятельствах.
Автоматическое сжатие особенно вредно, поскольку оно запускается каждые 30 минут в фоновом режиме и пытается сжимать базы данных, для которых выставлен параметр автоматического сжатия. Этот процесс не вполне предсказуем в том, что он сжимает лишь базы данных с более чем 25% свободного места. Автоматическое сжатие использует массу ресурсов и вызывает понижающую производительность фрагментацию, так что оно нежелательно при любых обстоятельствах. Его всегда следует отключать.
Выполнение сжатия базы данных может быть выполнено только после удаления большого количества данных при условии, что не ожидается использование получившегося в результате свободного места. Настоятельно рекомендуется помнить о нескольких важных правилах:
Используйте команду DBCC SHRINKDATABASE для сжатия файлов данных и журналов, пример:
Для сжатия отдельных файлов используйте команду DBCC SHRINKFILE, пример:
На практике модели восстановления, настраиваемые по умолчанию для баз данных, от которых зависит работа SharePoint, далеко не всегда являются оптимальными и ведут к разрастанию файлов журналов транзакций и неэффективному использованию дискового пространства, дополнительным накладным расходам на резервное копирование.
В таблице ниже приведены рекомендации по типовой настройке режимов восстановления для каждой из баз данных SharePoint.
Таблица 1. Типовая настройка режимов восстановления для баз данных SharePoint
Эти рекомендации могут служить ориентиром, но для принятия решения о настойке режима восстановления каждой отдельной базы данных мы настоятельно рекомендуем, как минимум, проанализировать:
Для выполнения вручную резервного копирования базы данных на SQL-сервере (на примере системной базы данных model) необходимо выполнить следующую последовательность действий:
Подробные рекомендации по резервному копированию и восстановлению в SharePoint 2013 можно найти здесь.
С помощью планов обслуживания SQL Server можно автоматизировать и планировать важные задачи, позволяющие защитить данные. Используя планы обслуживания в SQL Server, администратор может планировать различные операции, в том числе проверку согласованности базы данных, реорганизацию и перестроение индексов.
Для создания плана обслуживания баз данных SQL Server 2014/2016 выполните следующую последовательность действий:
В состав любой фермы Microsoft SharePoint 2013/2016 обязательно входит SQL-сервер, который обеспечивает хранение данных различных служб и компонентов, развернутых для работы корпоративного портала. Как известно, производительность работы с базами данных оказывает существенное влияние на работу корпоративного портала в целом. Если не уделять внимание обслуживанию баз данных SharePoint или делать это безграмотно, то на практике встречаются следующие явные проблемы: снижение производительности работы на корпоративном портале, которая очень часто выражается в постепенном увеличении времени ожидания загрузки страниц, форм, данных на веб-страницах, а также дублирование действий и их несогласованность в части администрирования.
Очень часто разработка планов обслуживания вызывает существенные затруднения у администраторов порталов ввиду сложности, комплексности и необходимости обладать компетенциями не только по администрированию SharePoint, но и по администрированию SQL-сервера. Поэтому в статье я выполнила обзор всех ключевых операций обслуживания баз данных, привела примеры скриптов и практические советы. Пользуясь приведенными описаниями, вы найдете множество рекомендаций к действию. Отмечу, что материалы статьи не являются исчерпывающими, т.к. включить описание абсолютно всех деталей и тонкостей обслуживания баз данных SharePoint не представляется возможным. Для того, чтобы вы могли получить более подробную информацию по той или иной теме, приведены ссылки на дополнительные публикации.
Введение
Все многообразие баз данных, от которого зависит работа SharePoint, можно разделить на три группы:
- Системные базы данных SQL-сервера (master, model, msdb, tempdb).
- Системные базы данных SharePoint (база данных конфигурации и контента Центра администрирования).
- Базы данных SharePoint Server, обеспечивающие хранение данных для развернутых приложений-служб (например, службы управления метаданными, службы профилей пользователей, службы подключения к бизнес-данным и др.).
Для оптимальной работы баз данных Microsoft SharePoint 2013/2016 крайне важно своевременно выполнять стандартные процедуры обслуживания всех связанных баз данных.
Ниже приводится список рекомендуемых задач обслуживания баз данных SharePoint:
- Проверка целостности базы данных.
- Дефрагментация индексов.
- Оптимизация производительности индекса с помощью коэффициента заполнения.
- Сжатие баз данных SharePoint.
- Настройка режима восстановления для баз данных;
- Резервное копирование системных баз данных;
- Разработка планов обслуживания.
Эти задачи обслуживания баз данных могут выполняться посредством команд Transact-SQL, пользовательского интерфейса, а также мастера обслуживания.
Общие сведения о назначении, требованиях к расположению, моделях восстановления по умолчанию и др. информацию по каждой из баз данных можно прочитать здесь.
Данная статья ориентирована на системных администраторов, администраторов баз данных и технических специалистов, занимающихся поддержкой работы корпоративных порталов SharePoint.
Проверка целостности базы данных
Команда DBCC CHECKDB обеспечивает комплексную проверку логической и физической целостности всех объектов в указанной базе данных и включает следующий набор проверок:
- согласованность структур выделения места на диске для указанной базы данных;
- целостность всех страниц и структур, составляющих таблицу или индексированное представление;
- согласованность каталогов в указанной базе данных, работающей в режиме сети;
- содержимое каждого индексированного представления в базе данных;
- согласованность между таблицы метаданных, файлами в системных папках и файлами на уровне ссылок при хранении varbinary(max) данных в файловой системе с помощью FILESTREAM;
- данные компонента Service Broker в базе данных.
Пример скрипта проверки целостности базы данных контента SharePoint «WSS_Content»:
DBCC CHECKDB ('WSS_Content')
WITH PHYSICAL_ONLY, ALL_ERRORMSGS
GOПример результатов выполнения скрипта, свидетельствующие об отсутствии ошибок:
Пример результатов, указывающих на наличие ошибок и необходимости решить проблему для конкретной базы данных:
Команда DBCC CHECKDB активно использует ресурсы подсистемы ввода-вывода, ЦП и памяти. Чтобы снизить нагрузку на рабочую систему, связанную с проверкой согласованности, рекомендуется выполнять команду DBCC CHECKDB в отношении восстановленных резервных копий баз данных SharePoint на отдельном сервере.
Если команда DBCC CHECKDB выполняется на рабочей базе данных, ее рекомендуется выполнять в нерабочие часы, ограничиваясь проверкой целостности физической структуры страниц и заголовков записей и целостности выделения пространства в базе данных.
Дефрагментация индексов
Введение
Фрагментация индекса — это состояние, при котором определяемый ключом логический порядок страниц в индексе отличается от физического порядка размещения страниц в файлах данных. Это также может свидетельствовать о низкой плотности данных на страницах файлов данных, что влечет за собой неэффективное использование дискового пространства, памяти и ресурсов подсистемы ввода-вывода.
Основными причинами фрагментации индекса являются многочисленные операции вставки, обновления или удаления данных в таблицах. На Рисунок 1 и Рисунок 2 показана разница между новым нефрагментированным индексом и таким же индексом после большого числа операций вставки, обновления и удаления. Красная стрелка определяет физический порядок размещения индекса, а черная — логический порядок страниц.
Рисунок 1. Нефрагментированный индекс (источник: Пол С. Рэндал)
Рисунок 2. Фрагментированный индекс (источник: Пол С. Рэндал)
Поскольку вставка, обновление и удаление данных в строках таблиц и индексов осуществляется неравномерно, каждая страница в разные моменты времени будет иметь разные уровни полноты (плотности данных). В таком случае при обработке запроса, для которого требуется сканирование части или всех индексов таблицы, высокая степень фрагментации повлечет за собой увеличение числа операций чтения страниц, что, в свою очередь, будет препятствовать параллельному сканированию данных и приведет к существенному снижению производительности.
Фрагментация индекса может привести к снижению производительности и неэффективному использованию пространства. При этом даже в базах данных с невысокой интенсивностью использования степень фрагментации индексов может расти быстрыми темпами.
Реорганизация и перестроение индексов
Для снижения фрагментации индексов поддерживаются два метода:
- Реорганизация индекса, которая дефрагментирует и сжимает кластеризованные и некластеризованные индексы в таблицах и представлениях, что может значительно повысить производительность работы с индексами. Реорганизация всегда выполняется в оперативном режиме, таким образом, базовая таблица доступна для пользователей.
- Перестроение индекса, в результате которого выполняется полное перестроение индекса с использованием тех же столбцов, типов индекса, уникальности атрибутов и порядка сортировки. Перестроение улучшает производительность сканирования индекса. Можно перестроить индекс в оперативном или в автономном режиме.
Оперативное перестроение индекса поддерживается только в выпусках SQL Server Enterprise. В тех случаях, когда выпуск SQL Server, в котором размещается рассматриваемая база данных, или сам индекс не поддерживает оперативное перестроение индекса, приведенные процедуры выполняют в автономном режиме. Оперативное перестроение может быть недоступно для индекса, содержащего столбцы с крупными объектами, например, столбцы с типом данных NVARCHAR(MAX), IMAGE и т. д.
В процессе автономного перестроения блокируется уровень таблиц, в результате чего они становятся недоступны для записи или даже для просмотра. Многие индексы баз данных SharePoint содержат столбцы с крупными объектами и поэтому всегда перестраиваются с применением автономного процесса.
Следует обратить внимание, что при оперативном перестроении может выполняться кратковременная блокировка таблиц, в результате чего запрещаются любые операции, за исключением SELECT. В базах данных SharePoint 2013/16 кластеризованные индексы используются особым образом. При перестроении такого индекса в автономном режиме к таблице применяется монопольная блокировка, которая полностью запрещает доступ пользователей к ней.
Предпочтительный способ дефрагментации индекса зависит от степени его фрагментации, а также от того, может ли он при этом оставаться в оперативном режиме или должен обрабатываться в автономном. В следующей таблице описываются рекомендуемые способы дефрагментации для индексов с различной степенью фрагментации для баз данных SharePoint:
| Степень фрагментации | Рекомендуемые действия |
|---|---|
| От 5 до 10 % | Реорганизация (оперативный режим) |
| От 10 до 75 % | Перестроение (оперативный режим) с параметром ALTER INDEX REBUILD WITH (ONLINE=ON) |
| Более 75% | Перестроение (автономный режим) с параметром ALTER INDEX REBUILD WITH (ONLINE=OFF) |
Автоматизация обслуживания индексов посредством системных заданий
Правила анализатора работоспособности
В SharePoint 2013/2016 процесс мониторинга службы и компонентов можно частично автоматизировать с помощью правил анализатора работоспособности SharePoint (проверки статистики индексов и проверки фрагментации индексов для баз данных обхода службы поиска). Их применение позволяет ежедневно оценивать состояние, а также автоматически обслуживать индексы для следующих баз данных:
- Базы данных конфигурации.
- Базы данных контента SharePoint, включая базу данных контента Центра Администрирования.
- Базы данных профилей пользователей.
- Базы данных социальных тегов приложения-службы профилей пользователей.
- Базы данных службы автоматизации Word.
Просмотреть полный перечень системных заданий, связанных с анализом работоспособности, можно в Центре Администрирования SharePoint, перейдя в раздел Отслеживание\Просмотр определений заданий. Примеры системных заданий:
Правила, проверяющие фрагментацию индексов
- Индексы баз данных, используемых SharePoint, фрагментированы. При запуске этого правила выполняются следующие задачи:
- Правило сообщает о фрагментированных индексах. Это связано с важностью состояния индекса. В результате действия правил анализатора работоспособности, это правило будет всегда сообщать о фрагментированных индексах, чтобы привести операции восстановления в действие.
- Для каждой базы данных SharePoint, операция восстановления ищет, и в случае успешного поиска, выполняет хранимую процедуру proc_DefragmentIndices хранимой процедуры. В ходе выполнения этой хранимой процедуры строится список всех индексов в базе данных. Каждый индекс оценивается с его текущим уровнем фрагментации. Для восстановления рассматриваются любые индексы, фрагментация которых свыше 30 процентов.
- Если выпуск SQL Server поддерживает перестройку индексов в оперативном режиме, предпринимается соответствующая попытка перестроения индекса для каждого индекса. Если это не удается, то выполняется автономное восстановление индексов.
- Правило сообщает о фрагментированных индексах. Это связано с важностью состояния индекса. В результате действия правил анализатора работоспособности, это правило будет всегда сообщать о фрагментированных индексах, чтобы привести операции восстановления в действие.
- Поиск — одна или несколько баз данных обхода могут иметь фрагментированные индексы. Это правило поддерживает индексы баз данных обхода службы поиска SharePoint. По умолчанию это правило настроено, чтобы выполняться только по требованию. Правило выполняется с любого сервера в ферме. При запуске этого правила выполняются следующие задачи:
- Правило подтверждает, что окружение находится в состоянии, в котором выполнение дефрагментации индексов безопасно.
- Для каждой базы данных обхода, настроенной для приложений поиска в локальной ферме, правило выполняет хранимую процедуру proc_MSS_DefragGathererIndexes.
- Каждый индекс базы данных обхода в списке будет перестроен. Если выпуск SQL Server поддерживает перестройку индексов в оперативном режиме, то осуществляется соответствующее перестроение индексов. Если перестроение индексов в этом режиме завершается ошибкой, индекс будет перестроен в автономном режиме.
- Правило подтверждает, что окружение находится в состоянии, в котором выполнение дефрагментации индексов безопасно.
Запуск хранимых процедур дефрагментации индексов
Многие базы данных SharePoint включают хранимые процедуры, используемые анализатором работоспособности для дефрагментации индексов. Хотя эти правила автоматически выполняют дефрагментацию, возможно, что потребуется запустить дефрагментацию вручную. Например, это может потребоваться ваши SLA или некоторые корпоративные регламенты.
Вы можете запланировать эти хранимые процедуры для запуска ежедневно, еженедельно или ежемесячно в зависимости от ваших потребностей и общих показателей изменения в вашем окружении.
Рекомендуется позволить SharePoint управлять своими собственными индексами базы данных. Если требуется вручную выполнить это действие, как минимум рекомендуется настроить ежедневное расписание обслуживания, чтобы выполнялась процедура proc_DefragmentIndices и еженедельное выполнение процедуры proc_MSS_DefragSearchIndexes.
Мониторинг фрагментации индексов средствами SQL-сервера
Прежде чем приступить к реализации плана обслуживания фрагментированного индекса, необходимо определить наиболее фрагментированные таблицы и индексы, и только после этого разработать план их перестроения и реорганизации.
Например, в SharePoint 2013/2016 сильно подвержена фрагментации таблица AllDocs, которая содержит библиотеки документов, связанные с ними документы, списки и элементы списков, а также соответствующие метаданные. Несмотря на наличие автоматизированных системных заданий анализатора работоспособности, их работа далеко не всегда оказывается эффективной
Степень фрагментации индекса определяется как доля страниц индекса, логический порядок размещения которых отличается от и физического. Чтобы определить ее в SQL Server 2014 и SQL Server 2016, следует использовать динамическое административное представление sys.dm_db_index_physical_stats.
Вы можете использовать следующий скрипт (на примере базы данных контента SharePoint «WSS_Content»):
USE [WSS_Content];
GO
SELECT obj.type_desc
,tbl.name as 'TableName'
,inx.name as 'IndexName'
,phys.avg_fragmentation_in_percent
,phys.fragment_count
,phys.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED') as phys --LIMITED/SAMPLED/DETAILED
JOIN sys.objects as obj on obj.object_id=phys.object_id
JOIN sys.indexes as inx on inx.object_id=phys.object_id
JOIN sys.tables as tbl on tbl.object_id=phys.object_id
WHERE avg_fragmentation_in_percent > 75
ORDER BY avg_fragmentation_in_percent DESC;Результирующий набор, возвращаемый функцией sys.dm_db_index_physical_stats, включает следующие столбцы:
| Столбец | Описание |
|---|---|
| avg_fragmentation_in_percent | Процентная доля логической фрагментации (неупорядоченные страницы в индексе). |
| fragment_count | Число фрагментов (физически последовательные конечные страницы) в индексе. |
| avg_fragment_size_in_pages | Среднее число страниц в одном фрагменте индекса. |
Пример вывода результата анализа фрагментации индексов:
Реорганизация индекса для базы данных SharePoint
Ниже приведен пример скрипта, обеспечивающего выполнение реорганизации одного из индексов таблицы «AllDocs» базы данных контента SharePoint:
USE [WSS_Content]
GO
ALTER INDEX [AllDocs_Url] ON [dbo].[AllDocs] REORGANIZE WITH ( LOB_COMPACTION = ON )
GOПерестроение индекса для базы данных SharePoint
Пример скрипта для перестроения всех индексов таблицы отдельной базы данных SharePoint:
USE [WSS_Content]
GO
ALTER INDEX ALL ON [dbo].[AllDocs]
REBUILD WITH (
FILLFACTOR = 80
, SORT_IN_TEMPDB = ON
,STATISTICS_NORECOMPUTE = ON)
GOПример скрипта для перестроения отдельного индекса
USE [WSS_Content]
GO
ALTER INDEX [AllDocs_Url] ON [dbo].[AllDocs] REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = ON
, SORT_IN_TEMPDB = ON
, IGNORE_DUP_KEY = OFF
, ONLINE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
, FILLFACTOR = 80)
GOПример полученного результата (проверка дефрагментации индекса после перестроения:
Оптимизация производительности индекса с помощью коэффициента заполнения
Для дополнительной оптимизации хранения данных и производительности индекса можно использовать коэффициент заполнения. При создании или перестроении индекса коэффициент заполнения (от 1 до 100) определяет процентную долю пространства на уровне каждой конечной страницы, которое может быть заполнено данными. Оставшаяся часть резервируется для дальнейшего расширения. В большинстве случаев оптимально подходит установленный на уровне сервера по умолчанию коэффициент заполнения 0 (каждая страница может заполняться на 100%). Тем не менее, в SharePoint 2013 можно использовать значение 80, которое задается на уровне сервера и обеспечивает оптимальные возможности расширения и минимальную степень фрагментации.
Коэффициент заполнения (параметр «fill factor») служит для точной настройки хранения и производительности индекса. При создании или перестроении индекса коэффициент заполнения отображает процент заполнения пространства каждой страницы конечного уровня, что позволяет зарезервировать для будущего расширения оставшееся на каждой странице пространство как свободное. Например, при указании для коэффициента заполнения значения 80 на каждой странице конечного уровня будет зарезервировано 20 процентов занимаемого ею дискового пространства. Данное дисковое пространство будет использовано для расширения индекса при добавлении в базовую таблицу новых данных. Пустое место резервируется не в конце индекса, а между строками индекса.
Коэффициент заполнения — это значение в процентах от 1 до 100; значение по умолчанию на сервере — 0, что означает полное заполнение страниц конечного уровня.
Для SharePoint для поддержки роста баз данных и снижения уровня фрагментации индексов оптимальным является значение 80.
Следующий скрипт позволят получить информацию от текущих настройках коэффициента заполнения индекса по умолчанию на уровне SQL-сервера:
SELECT name,value,minimum, maximum,value_in_use
FROM sys.configurations
WHERE name IN (
'fill factor (%)'
)Пример результата:
Сжатие баз данных SharePoint
Автоматическое сжатие базы данных
Следует позаботиться, чтобы никоим образом не было включено автоматическое сжатие баз данных SharePoint. Сжатие можно использовать для уменьшения размера файла данных или журнала транзакций, но это очень грубый, ресурсоемкий процесс, который вызывает широкую логическую фрагментацию просмотра в файлах данных и ведет к низкой производительности. Сжатие отдельных файлов данных и журнала вручную может быть допустимо при особых обстоятельствах.
Автоматическое сжатие особенно вредно, поскольку оно запускается каждые 30 минут в фоновом режиме и пытается сжимать базы данных, для которых выставлен параметр автоматического сжатия. Этот процесс не вполне предсказуем в том, что он сжимает лишь базы данных с более чем 25% свободного места. Автоматическое сжатие использует массу ресурсов и вызывает понижающую производительность фрагментацию, так что оно нежелательно при любых обстоятельствах. Его всегда следует отключать.
Рекомендации
Выполнение сжатия базы данных может быть выполнено только после удаления большого количества данных при условии, что не ожидается использование получившегося в результате свободного места. Настоятельно рекомендуется помнить о нескольких важных правилах:
- не включайте автоматическое сжатие баз данных;
- не включайте сжатие баз данных в планы обслуживания или задания, выполняющиеся по расписанию;
- сжимайте только те базы данных, для которых удалено более 50% контента;
- сжимайте только базы данных контента SharePoint;
- выполняйте сжатие только в нерабочие часы ввиду высокой нагрузки на базовые подсистемы сервера во время этой операции;
- выполняйте реорганизацию или перестроение индексов после сжатия (в зависимости от степени фрагментации);
- Использование параметров TRUNCATEONLY и EMPTYFILE не поддерживается базами данных SharePoint.
Используйте команду DBCC SHRINKDATABASE для сжатия файлов данных и журналов, пример:
USE [WSS_Content]
GO
DBCC SHRINKDATABASE(N'WSS_Content')
GOДля сжатия отдельных файлов используйте команду DBCC SHRINKFILE, пример:
USE [WSS_Content]
GO
DBCC SHRINKFILE (N'WSS_Content', 0)
GOНастройка режимов восстановления баз данных SharePoint
На практике модели восстановления, настраиваемые по умолчанию для баз данных, от которых зависит работа SharePoint, далеко не всегда являются оптимальными и ведут к разрастанию файлов журналов транзакций и неэффективному использованию дискового пространства, дополнительным накладным расходам на резервное копирование.
В таблице ниже приведены рекомендации по типовой настройке режимов восстановления для каждой из баз данных SharePoint.
Таблица 1. Типовая настройка режимов восстановления для баз данных SharePoint
| База данных | |
|---|---|
| Простой | |
| Простой. Как правило настройка выполняется однократно или очень редко. Вполне достаточно выполнять резервную копию после внесения изменений |
|
| Простой |
|
| Простой |
|
| База данных контента Центра администрирования | Простой. |
| Простой. Изменения обычно активно вносятся на этапе первоначальной или точечной настройки служб и компонентов фермы SharePoint, что имеет четкие непродолжительные временные границы. Более рационально выполнять резервную копию после внесения завершенных блоков изменений. |
|
| База данных службы управления приложениями | Простой. Как правило в ферме SharePoint устанавливается от 0 до небольшого кол-ва приложений (1-5) SharePoint. Установки приложений обычно существенно разнесены по времени. Более рационально выполнять полное резервное копирование базы данных после инсталляции отдельного приложения |
| База данных службы параметров подписки | Простой. Как правило приложения SharePoint устанавливаются на небольшом кол-ве сайтов (1-5). Установки приложений обычно существенно разнесены по времени. Более рационально выполнять полное резервное копирование базы данных после инсталляции отдельного приложения. Полный. Если предполагается установка приложений на неограниченном кол-ве веб-узлов, инсталляции выполняются часто |
| Служба подключения к бизнес-данным | Простой. Настройка и внесение изменений, связанных с работой данной службы обычно непродолжительна по времени и выполняется однократно или небольшое кол-во раз. Более рационально выполнять полное резервное копирование базы данных после завершенных блоков создания/редактирования моделей данных, внешних типов контента и списков внешних данных |
| База данных приложения-службы управляемых метаданных | Простой в случае использования справочников с редко изменяемым содержимым. Полный доступ в случае внесения изменений в наборы терминов на регулярной основе (активное создание и редактирование иерархических справочников, тегирование элементов списков и документов). |
| База данных приложения-службы перевода SharePoint | Простой. |
| Полный доступ |
|
| База данных PerformancePoint Services | Простой в случае работы с набором панелей индикаторов, в настройки которых редко вносятся изменения. Полный доступ в случае интенсивного создания/изменения панелей индикаторов |
| База данных администрирования поиска | Простой. Настройка и внесение изменений, связанных с работой данной службы обычно непродолжительна по времени и выполняется однократно или небольшое кол-во раз. Более рационально выполнять полное резервное копирование базы данных после завершенных блоков настроек, изменений схемы поиска |
| Простой, если анализ результатов поиска и интеллектуальная адаптация выдачи результатов, предложений поиска не является критичными. Полный доступ, если поиск по порталу и его постоянная персонализированная работа являются критичными для бизнеса |
|
| Простой. Восстановление этой базы из резервной копии или посредством мастера восстановления чаще всего будет намного дольше в сравнении со сбросом индекса и полным обходом контента |
|
| Простой, если анализ результатов поиска и интеллектуальная адаптация выдачи результатов, предложений поиска не является критичными. Полный доступ, если поиск по порталу и его постоянная персонализированная работа являются критичными для бизнеса |
|
| Простой. Настройка и внесение изменений, связанных с работой данной службы обычно непродолжительна по времени и выполняется однократно или небольшое кол-во раз. Более рационально выполнять полное резервное копирование базы данных после завершенных блоков настроек. |
|
| База данных приложения-службы состояний | Простой. Ввиду того, что эта база данных обеспечивает хранение временных состояний для форм InfoPath, веб-частей службы Visio и не предназначена для продолжительного хранения этой информации. Корректность данных в этой базе имеет значение на момент просмотра пользователем конкретной формы InfoPath или Visio-диаграммы посредством веб-части. |
| База данных сбора данных об использовании и работоспособности | Простой или Полный доступ. Зависит от требований к допустимому для организации уровню потери данных. |
| Простой, если нет бизнес-критичного функционала, работа которого требует работы с самыми актуальным данными о профилях пользователей Полный доступ, если ситуация обратная. |
|
| Простой, если время восстановления занимает меньше времени в сравнении с перенастройкой подключений и выполнением полной синхронизации профилей Полный доступ, если ситуация обратная |
|
| Простой, если работа с социальным контентом не является бизнес-критичным функционалом Полный доступ, если ситуация обратная |
|
| Полный доступ |
Эти рекомендации могут служить ориентиром, но для принятия решения о настойке режима восстановления каждой отдельной базы данных мы настоятельно рекомендуем, как минимум, проанализировать:
- Назначение, характер хранимой информации, частоту изменения данных, категории пользователей и степень важности данных для каждой из них.
- Требования ко времени восстановления и допустимому уровню потери данных. Например, нет никакого смысла использовать режим полного восстановления для базы данных приложения-службы управляемых метаданных, если она содержит несколько иерархических справочников (наборов терминов), содержание которых редко изменяется и потеря данных за день-два не является критичным. Другое дело, если на корпоративном портале активно используются возможности управления социальным контентом (тегирование, отслеживание изменений контента и активностей на портале людей и др.), оказывающие существенное влияние на совместную работу пользователей.
- Модель восстановления по умолчанию. Эту информацию можно получить по ссылке.
- Поддерживаемые сценарии резервного копирования и восстановления. Отдельно рассмотреть возможности резервного копирования и восстановления самой платформы SharePoint (баз контента, приложений-служб, конфигурации фермы) и сравнить их со сценариями пересоздания. Например, на практике часто гораздо быстрее восстановить работу приложения-службы управляемых метаданных через ее пересоздание с подключением к восстановленной базе данных, нежели использование средств платформы. Необходимо понимать, что выбранный режим восстановления базы данных будет напрямую влиять на порядок выполнения резервного копирования и восстановления, уровень потери данных. Полезные ссылки: резервное копирование и восстановление SharePoint, баз данных SQL Server.
- В отдельных случаях можно рассмотреть полное исключение резервного копирования баз данных отдельного приложения-службы. На практике часто это служба поиска, если допускается простой в ее работе, сопоставимый со временем полного пересоздания, настройки и обхода контента.
- Технологии, которые планируется использовать для обеспечения требования к отказоустойчивости (если они есть). Возможно вам сразу придется исключить из рассмотрения режим простого восстановления.
Резервное копирование баз данных
Для выполнения вручную резервного копирования базы данных на SQL-сервере (на примере системной базы данных model) необходимо выполнить следующую последовательность действий:
- Открыть Microsoft SQL Server Management Studio, выбрать базу данных, для которой необходимо создать резервную копию, нажать правой кнопкой мыши на эту базу и выбрать Задачи > Создать резервную копию.
- На странице Общие выбрать базу данных и тип резервного копирования.
- На странице Параметры носителя выбрать тип устройства резервного копирования для операции резервного копирования и найти существующие логические и физические устройства резервного копирования.
- На странице Параметры резервного копирования укажите имя резервного набора, срок действия резервного набора и параметры сжатия.
Подробные рекомендации по резервному копированию и восстановлению в SharePoint 2013 можно найти здесь.
Разработка планов обслуживания
С помощью планов обслуживания SQL Server можно автоматизировать и планировать важные задачи, позволяющие защитить данные. Используя планы обслуживания в SQL Server, администратор может планировать различные операции, в том числе проверку согласованности базы данных, реорганизацию и перестроение индексов.
Для создания плана обслуживания баз данных SQL Server 2014/2016 выполните следующую последовательность действий:
- Нажмите кнопку Пуск в панели задач и последовательно выберите пункты Все программы > SQL Server Management Studio.
- В обозревателе объектов подключитесь к экземпляру ядра СУБД SQL Server и разверните его.
- Выберите элемент Управление, щелкните правой кнопкой мыши пункт Планы обслуживания и выберите пункт Мастер планов обслуживания.
- Нажимайте кнопку Далее до тех пор, пока не откроется страница Выбор свойств плана.
- Введите имя и описание плана в полях Имя и Описание соответственно.
- Укажите, требуется ли настроить один или несколько планов.
- Чтобы настроить один план обслуживания, установите переключатель Единое расписание для всего плана или без расписания.
- Чтобы настроить несколько планов обслуживания с отдельными задачами, установите переключатель Отдельные расписания для каждой задачи.
Если в вашей среде присутствует более 10 баз данных контента или общий объем контента превышает 200 ГБ, рекомендуется настраивать отдельные планы обслуживания. Это позволяет обеспечить надлежащую избирательность и эффективность обслуживания.
Если для базы данных настроено несколько планов обслуживания, необходимо указать их имена или описания, позволяющие различать планы по предназначению и расписанию.
- Нажмите кнопку Изменить, чтобы задать расписание настроенных планов. Откроется диалоговое окно Свойства расписания задания.
- Настройте расписание, нажмите кнопку ОК и затем кнопку Далее.
- На странице «Выбор задач по обслуживанию» выберите задачи, которые необходимо включить в план, и нажмите кнопку Далее.
Следует учитывать ряд рекомендаций:
- План обслуживания должен содержать либо операцию реорганизации, либо операцию перестроения индекса, но не обе операции одновременно.
- В план обслуживания никогда не следует включать операцию сжатия базы данных.
- Прежде чем объединить несколько задач в один план, проверьте каждую из них на продолжительность выполнения. В некоторых случаях, чтобы завершить все задачи в удобные или нерабочие часы, потребуется определить несколько планов обслуживания с разными расписаниями.
- Задача Очистка после обслуживания удаляет любые файлы, оставшиеся после запланированного обслуживания.
Для баз данных очень большого размера можно настроить отдельные план обслуживания, который будет выполнять проверку целостности базы реже, чем обслуживание индекса.
- При необходимости измените порядок задач в плане на странице «Выбор порядка задач по обслуживанию». Для этого переместите нужные задачи в списке с помощью кнопок Вверх или Вниз. Закончив настройку, нажмите кнопку Далее.
- На странице Задача «Проверка целостности базы данных» выберите базы, для которых требуется проверить целостность, укажите нужные значения, и нажмите кнопку Далее. Проверять на целостность можно любые базы данных SharePoint.
- На странице Задача «Реорганизация индекса» в списке Базы данных определите базы, для которых требуется реорганизовать индексы, установите флажок Сжатие больших объектов, выберите нужные значения параметров статистики индекса и нажмите кнопку Далее.
- Если вместо реорганизации будет выполняться перестроение индекса, выберите базы в списке Базы данных на странице Задача «Перестроение индексов».
- Установите переключатель Изменить долю свободного места на странице, введите значение 80, выберите необходимые значения дополнительных параметров и нажмите кнопку Далее.
Значение параметра Изменить долю свободного места на странице задает коэффициент заполнения для базы данных.
- Укажите нужные значения на странице Определить задачу «Очистка после обслуживания» и нажмите кнопку Далее.
Рекомендуется удалять текстовые отчеты по планам обслуживания.
- На странице Выбор параметров отчета установите флажок Сохранить отчет в текстовый файл, укажите папку для хранения файлов. Нажимайте кнопку
Далее
