Приемы оптимизации ТБ.Корпорации на базе MS SQL 2005

Все нижесказанное относится только к семейтсву серверов MSSQL 2005 Enterprise Edition и старше. MSSQL 2005 Standart Edition не поддерживает секционирование таблиц.

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

Что значит большое количество документов? Пример из жизни: 1200к записей в таблице [Процесс], более 5,5кк записей в таблице [Процесс_Позиции] и более 80 активных пользователей. В таких условиях открытие/обновление картотеки уже происходит довольно долго.

Если проанализировать SQL-запросы, которые генерирует сервер данных при открытии/листании картотеки, то можно увидеть, то практически во всех запросах присутствует ограничение на дату. Это закономерно - так как практически все представления имеют встроенный бланк выбора периода. Если при этом обратить внимание на план выполнения запроса, то станет понятно, что SQL сервер производит выборку из всего множества записей таблицы Процесс. Что тут нам может предложить MS SQL 2005? SQL 2005 Enterprise Edition поддерживает горизонтальное секционирование таблиц (partitioning). Что это значит? Это значит что данные будут располагаться в отдельных участках (партициях) и доступ к партициям может производится параллельно. Еще выгода тут в том, что партиция создается таким образом, чтобы количество записей в ней было не очень большим. Так как все записи имеют поле ДатаНачала, то, естественно, хотелось бы их и «поделить» по этому признаку. Инструментарий ТБК позволяет создавать в подтаблице «связанные» поля - т.е. есть поле в каждой строке подтаблицы, которое идентично полю в шапке. Это нарушает нормализацию базы данных, поэтому не стоит выбирать в качестве связанного поля строку - так как размер базы данных может в этом случае заметно увеличится.

На мой взгляд, очень хорошо в качестве параметра разделения таблицы подходит поле ДатаНачала. Собственно это подтверждено практическим опытом. Т.е. все, что написано тут работает на реальной базе и дает ощутимый прирост производительности. В зависимости от размеров базы и количества записей нужно выбрать период. В нашем случае мы остановились на периоде, равном 1 месяцу. Чаще всего пользователь открывает список документов за конкретный месяц. И количество записей за 1 месяц мы посчитали приемлимым. Единственный недостаток секционирования в том, что при реорганизации ИБ все нужно сделать заново, а на больших базах это занимает достаточно длительное время (У нас база размеров 14ГБ - секционирование проходит за 1,5 часа, не включая дополнительные подготовительные процедуры, такие как резервное копирование и пр.).

Что нужно сделать для того, чтобы сделать секционирование таблицы Процесс и Процесс.Позиции:

  1. Убедимся, что у нас MS SQL Server Enterprise Edition. Standart Edition не поддерживает секционирование.
  2. Нарушаем нормализацию базу данных. В подтаблицу Позиции документа Процесс добавляем поле ДатаНачала. (см. описание MTL)
  3. Самый главный шаг. Настроить секционирование таблиц Процесс и Процесс_Позиции. Они должны быть выравнены слева на значение поля ДатаНачала.
  4. Администрирование сервера. ПОСТОЯННОЕ. Надо расширять по необходимости сегменты, добавлять или объединять сегменты. Обслуживание индексов уже

описано, об обслуживании сегментированных таблиц можно узнать в МСДН или прочитать статью.

Размещаю ссылку на скрипт. Кому интересно - качайте, может будут предложения по оптимизации/доработке. Ссыка на скрипт Примечание: скрипт больше не предоставляется публично, все вопросы по нижеописанным контактам. Контакты: почта andy_mezentsev@mail.ru, ICQ 162-965-706

Напоследок самое главное замечание: для НОРМАЛЬНОЙ работы ТБК необходимо администрирование сервера СУБД. Этому достаточно уделять 2-3 часа в день. И будет всем хорошо: и пользователи довольны и админу никто спать не мешает.

 
doc/mssql_optimize.txt · Последние изменения: 2016/04/15 15:26 (внешнее изменение)
 
За исключением случаев, когда указано иное, содержимое этой вики предоставляется на условиях следующей лицензии:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki