Один из основных инструментов для оптимизации работы MS SQL Server - размещение различных таблиц и индексов на разных физических дисках. В Microsoft SQL такая настройка Server выполняется при помощи файловых групп (filegroup), а в Oracle - при помощи табличных пространств (tablespace). В статье Вадима Гончаренко рассказывается, как средствами Axapta 3.0 выполняется настройка файловых групп Microsoft SQL Server.

 

Настройки хранения для Microsoft SQL Server в Axapta 3.0

Выдержка из документа Microsoft Business Solutions Axapta V3.0 Databases Advanced:

In MS SQL Server Axapta you don't need to bother about how the create segments that are stored in the database since it's handled automatically by the database management system. All Axapta related segments are stored in the primary file group; therefore, this doesn't give you much control over how the segments are stored should you want to try and move segments around to increase performance. A common question is why doesn't Axapta support the use of several secondary file groups? Axapta theoretically does support secondary file groups; however, Axapta does not have an interface to support the secondary file group. Code could be added in the SQLSTORAGE table to have a table or database located in the secondary file group. Currently, there are no plans on implementing this interface.

В Аксапте имеется функциональность для поддержки настройки хранения для Microsoft SQL Server, схожей с настройками хранения Oracle. Для тех, кто не располагает лицензией на модуль «Oracle Database», вкратце: она позволяет из интерфейса Аксапты настроить файловую группу (в случае Oracle – табличное пространство), в которой будет храниться таблица или индекс. Настройки достаточно гибкие – существуют такие варианты, как «Все таблицы», «Выбранная таблица», «Все индексы», «Все индексы указанной таблицы», «Выбранные индексы для выбранной таблицы». Таким образом, администратор может из Аксапты управлять размещением таблиц на дисках и, следовательно, управлять производительностью, не прибегая к сторонним утилитам.

 

Internals, или о том, как это устроено

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

CREATE TABLE [SQLSTORAGE]
 ( [ID] [int], /* непонятное поле со значениями 0/1
                  0 - запись используется для отображения в форме SysSqlSetup
                  1 – запись используется для выполнения DDL операторов */
   [OBJECTTYPE] [int], /* 0 - настройка для таблицы 1 - настройка для индекса */
   [TABLEID] [int], /* для OBJECTTYPE = 0 идентификатор таблицы или 0 - «все таблицы» */
   [INDEXID] [int], /* для OBJECTTYPE = 1 идентификатор индекса или 0 - «все индексы» */
   [OVERRIDE] [int], /* поле не используется */
   [PARM] [varchar], /* собственно тип параметра, описываемого записью используемые
                        значения для MSSQL: FILLFACTOR SEGMENT IRL */
   [VALUE] [varchar], /* текст, который будет подставлен в DDL оператор, например, ON 'PRIMARY' */
   [RECID] [int])

Параметр SEGMENT изначально определял сегмент, в котором должен был создаваться объект. Начиная с версии 7.0, Microsoft SQL Server более не оперирует понятием «сегмент», вместо него используются «файл» и «файловая группа». К счастью, синтаксис команд CREATE TABLE и CREATE INDEX в той части, где указывается файловая группа, не изменился, поэтому эта функциональность не пострадала. Единственное неудобство – это код, который выбирает значения для выпадающего списка доступных сегментов в форме SysSqlSetup:

oid sqlServerInit()
{
    …
    resultSet = statement.executeQuery(
                   'select name
                        from syssegments
                        where name not in (\'logsegment\', \'system\')'
                );
    …
} 

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

select distinct groupname
    from sysfilegroups g, sysfiles f
    where g.groupid = f.groupid 

Параметр IRL относится к свойству "INSERT ROW LOCK" таблицы MSSQL. Начиная с версии 7.0, поддерживающей блокировки на уровне строк, использование этого параметра не имеет смысла.

Назначение параметра FILLFACTOR понятно, однако для того, чтобы его использовать, придется немного попрограммировать. Дело в том, что при использовании обоих параметров FILLFACTOR и SEGMENT формируется некорректный DDL оператор вида

CREATE INDEX <INDEXNAME>
    ON <TABLENAME> (<FIELDDEF>) ON 'PRIMARY'
    WITH FILLFACTOR=90 

Правильный должен быть таким

CREATE INDEX <INDEXNAME>
    ON <TABLENAME> (<FIELDDEF>)
    WITH FILLFACTOR=90 ON 'PRIMARY' 

Для того чтобы пользоваться обоими параметрами одновременно, придется перекрыть метод generateComponents() в форме SysSqlSetup

switch(dbId)
{ case DatabaseId::MS_SQL_SERVER: { if (sqlEnableSegment.value()) componentPart1 = componentPart1 +'ON \'' +sqlSegment.getText(sqlSegment.selection())+'\' '; if (sqlEnableFillFactor.value()) //componentPart1 = componentPart1 +'WITH FILLFACTOR =' // +int2str(sqlFillFactor.value())+' '; componentPart1 = 'WITH FILLFACTOR=' +int2str(sqlFillFactor.value()) +' '+componentPart1+' '; .. }

Настройка

Итак, со значениями параметров хранения мы разобрались, приступаем к самой настройке. Официально настройка параметров хранения для MSSQL работает (после небольшой модификации, описанной выше), но не поддерживается. Поэтому саму форму, в которой делается настройка, из пользовательского интерфейса Аксапты вызвать нельзя, придется либо немного попрограммировать, либо вызывать форму SysSqlSetup вручную из AOT. Переходим к самой настройке

Настройка filegroup для выбранной таблицы

Настройка filegroup для индексов выбранной таблицы

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

 

О чем надо помнить

Изменение настроек хранения для таблицы или индекса не означает их немедленного перемещения в новую файловую группу. Оно произойдет в следующих случаях:

 

Кластерные индексы

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

Таким образом, пересоздание кластерного индекса в другой файловой группе означает автоматический перенос в нее и самого индекса, и данных. Так как кластерные индексы определены на большинстве таблиц Аксапты, файловая группа с индексами будет занимать гораздо больше места, чем предполагалось – ведь в ней будет располагаться и часть данных. С этим можно или смириться, или пользоваться более тонкой настройкой «Одна файловая группа для всех таблиц - одна файловая группа для всех некластерных индексов».

 

Вадим Гончаренко, vgoncharenko@rabota-na-rezultat.ru