In-Memory базы данных Analysis Services

Для повышения производительности аналитических отчетов, использующих таблицы с большими объемами данных, постоянно разрабатываются и внедряются все новые технологии. В частности в СУБД MS SQL Server в 2012 году появились колоночные индексы, а начиная с MS SQL 2014 и далее развивается интеграция реляционного движка с таблицами, оптимизированными для оперативной памяти. В то же время еще в 2012 году в Analysis Services появилась возможность устанавливать его в одном из двух режимов: многомерном или табличном. В новом табличном режиме в Analysis Services можно создавать базы данных, содержащие табличные модели, которые хранятся в оперативной памяти. При этом таблицы в таких базах данных хранятся в колоночном режиме (каждый столбец хранится отдельно, с применением специальных алгоритмов сжатия и сбора статистики).

Если развитие реляционных таблиц, оптимизированных для оперативной памяти, замедляется необходимостью их интеграции с обилием компонентов, создававшихся десятилетиями, то табличный Analysis Services был создан с нуля, и для многих это может стать преимуществом. К примеру в MS SQL 2014 запросы к memory-таблицам выполняются на одном ядре, что автоматически делает их непригодными для больших и даже средних объемов в данной версии СУБД. В Analysis Services такой проблемы нет, там все распараллеливается по доступным ядрам. Рассмотрим пример того как создавать и делать запросы к табличным моделям и сравним их производительность с запросами к классическим реляционным таблицам.

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

if db_id ( 'Testing' ) is null
begin
    create database Testing
        on primary
        (
            name = TestingData,
            filename = 'C:\Users\dataUser\Desktop\TestingData.mdf',
            size = 1 Gb,
            maxsize = unlimited,
            filegrowth = 100 Mb
        )
        log on
        (
            name = TestingLog,
            filename = 'C:\Users\dataUser\Desktop\TestingLog.ldf',
            size = 1 Gb,
            maxsize = unlimited,
            filegrowth = 100 Mb
        )
end
go

alter database Testing set recovery simple
go

use Testing
go

if object_id ( 'dbo.Attrs', 'U' ) is null
begin
    create table dbo.Attrs
    (
        iAttrId int identity ( 1, 1 ) not null,
        vcAttr  varchar ( 100 )       not null,
        constraint PK_Attrs_iAttrId primary key clustered ( iAttrId asc ) on [primary]
    ) on [primary]
end
go

if object_id ( 'dbo.Entities', 'U' ) is null
begin
    create table dbo.Entities
    (
        iEntityId int identity ( 1, 1 ) not null,
        vcEntity  varchar ( 100 )       not null,
        iAttrId   int                   not null,
        constraint PK_Entities_iEntityId primary key clustered ( iEntityId asc ) on [primary],
        constraint FK_Entities_iAttrId foreign key ( iAttrId ) references dbo.Attrs ( iAttrId )
            on update cascade on delete no action
    ) on [primary]
end
go

if object_id ( 'dbo.Dates', 'U' ) is null
begin
    create table dbo.Dates
    (
        dtDate date not null,
        constraint PK_Dates_dtDate primary key clustered ( dtDate asc ) on [primary]
    ) on [primary]
end
go

if object_id ( 'dbo.FactsDayly', 'U' ) is null
begin
    create table dbo.FactsDayly
    (
        iEntityId int   not null,        
        dtDate    date  not null,
        fSum      float not null,
        constraint PK_FactsDayly_dtDate_iEntityId primary key clustered
            ( dtDate asc, iEntityId asc ) on [primary],
        constraint FK_FactsDayly_iEntityId foreign key ( iEntityId ) references dbo.Entities ( iEntityId )
            on update cascade on delete no action,
        constraint FK_FactsDayly_dtDate foreign key ( dtDate ) references dbo.Dates ( dtDate )
            on update cascade on delete no action
    ) on [primary]
end
go
if object_id ( 'dbo.FactsPeriods', 'U' ) is null
begin
    create table dbo.FactsPeriods
    (
        iEntityId    int   not null,        
        dtDate       date  not null,
        dtDateNext   date  not null,
        fSum         float not null,
        constraint PK_FactsPeriods_iEntityId_dtDate primary key clustered
            ( iEntityId asc, dtDate asc ) on [primary],
        constraint AK_FactsPeriods_dtDate_dtDateNext_iEntityId unique nonclustered
            ( dtDate asc, dtDateNext desc, iEntityId asc ) on [primary],
        constraint FK_FactsPeriods_iEntityId foreign key ( iEntityId ) references dbo.Entities ( iEntityId )
            on update cascade on delete no action,
        constraint FK_FactsPeriods_dtDate foreign key ( dtDate ) references dbo.Dates ( dtDate )
            on update no action on delete no action,
        constraint FK_FactsPeriods_dtDateNext foreign key ( dtDateNext ) references dbo.Dates ( dtDate )
            on update no action on delete no action
    ) on [primary]
end
go

Теперь запустим скрипты для генерации данных в этих таблицах достаточно большого объема, который поместится в память (~100 млн. строк).

set nocount, xact_abort on

declare @dtStart date = '2017-01-01', @dtEnd date = '2017-03-31'
;
with Dates
as
(
    select dtDate = @dtStart
        union all
    select dateadd ( day, 1, dtDate )
    from Dates
    where dtDate < @dtEnd
)
insert into dbo.Dates ( dtDate )
    select dtDate
    from Dates
    option ( maxrecursion 0 )

declare @iMin int = 1, @iMax int = 20
;
with Attrs
as
(
    select iAttr = @iMin
        union all
    select iAttr + 1
    from Attrs
    where iAttr < @iMax
)
insert into dbo.Attrs ( vcAttr )
    select cast ( iAttr as varchar ( 100 ) )
    from Attrs
    option ( maxrecursion 0 )

insert into dbo.Entities with ( tablock ) ( vcEntity, iAttrId )
    select top ( 40000000 )
        cast ( abs ( binary_checksum ( newid () ) ) as varchar ( 100 ) ),
        1 + abs ( binary_checksum ( newid () ) ) % 20
    from
        master.dbo.spt_values as tab1
            cross join
        master.dbo.spt_values as tab2
            cross join
        master.dbo.spt_values as tab3

go
alter table dbo.FactsDayly drop constraint PK_FactsDayly_dtDate_iEntityId 
declare @dtStart date = '2017-01-01', @dtEnd date = '2017-03-31', @dtCurr date
set @dtCurr = @dtStart
while @dtCurr <= @dtEnd
begin
    insert into dbo.FactsDayly with ( tablock )
    (
        iEntityId,
        dtDate,
        fSum
    )
    select top ( 1000000 ) iEntityId, @dtCurr, abs ( binary_checksum ( newid () ) ) * rand () / 1000
    from dbo.Entities
    order by binary_checksum ( newid () )
    
    set @dtCurr = dateadd ( day, 1, @dtCurr )
end
alter table dbo.FactsDayly add constraint PK_FactsDayly_dtDate_iEntityId
    primary key clustered ( dtDate asc, iEntityId asc ) on [primary]
go

alter table FactsPeriods drop constraint AK_FactsPeriods_dtDate_dtDateNext_iEntityId

if object_id ( 'tempdb..#Ents', 'U' ) is not null
begin
    drop table #Ents
end
create table #Ents ( iEntityId int not null, primary key clustered ( iEntityId asc ) on [primary] ) on [primary]

declare @iMin int = 1, @iMax int = 20, @iCurr int, @vcSql nvarchar ( max )
set @iCurr = @iMin
while @iCurr <= @iMax
begin
    truncate table #Ents
    insert into #Ents with ( tablock ) ( iEntityId )
        select top ( 900000 ) iEntityId
        from dbo.Entities
        where iAttrId = @iCurr
        order by newid ()

    insert into dbo.FactsPeriods with ( tablock )
    (
        iEntityId,
        dtDate,
        dtDateNext,
        fSum
    )
    select iEntityId, dtDate = '2017-01-01',
        dtDateNext = dateadd ( day, abs ( binary_checksum ( newid () ) ) % 20 + 1, '2017-01-01' ),
        fSum = abs ( binary_checksum ( newid () ) ) * rand () / 1000
    from #Ents

    set @vcSql = N'
    insert into dbo.FactsPeriods
    (
        iEntityId,
        dtDate,
        dtDateNext,
        fSum
    )
    select iEntityId, dtDate, dtDateNext, fSum = abs ( binary_checksum ( newid () ) ) * rand () / 1000
    from
    (
        select ents.iEntityId, dtDate = dateadd ( day, 1, per.dtDateNext ),
            dtDateNext = dateadd
            (
                day,
                abs ( binary_checksum ( newid () ) ) % 20 + 1,
                dateadd ( day, 1, per.dtDateNext )
            )
        from
            #Ents ents
                inner join
            (
                select iEntityId, dtDateNext = max ( dtDateNext )
                from dbo.FactsPeriods
                group by iEntityId
            ) per on ents.iEntityId = per.iEntityId
    ) dates
    where dtDateNext <= '2017-03-31'
    '
    exec sp_executesql @vcSql
    exec sp_executesql @vcSql
    exec sp_executesql @vcSql
    exec sp_executesql @vcSql
    
    set @iCurr += 1
end

alter table FactsPeriods add constraint AK_FactsPeriods_dtDate_dtDateNext_iEntityId
    unique nonclustered ( dtDate asc, dtDateNext desc, iEntityId asc ) on [primary]

Таблица FactsDayly хранит историю изменения некоторой характеристики сущностей из dbo.Entities на каждый день. Таблица dbo.FactsPeriods хранит историю изменения атрибута в более экономном формате: за даты изменения. Тестирование скорости работы отчетов в реляционной и In-Memory базах данных будет проведено с использованием этих двух таблиц. В распоряжении имеется сервер с 16 Гб памяти, четырех ядерным процессором Core i5. Для сервера зададим предел используемой памяти:

sp_configure 'max server memory', 8800
reconfigure

Цель отчета, который будет разработан, состоит в том, чтобы получить сумму атрибута fSum в группировке по атрибуту iAttrId на каждый день. При использовании таблицы dbo.FactsDayly запрос на Transact-SQL выглядит так:

dbcc setcpuweight ( 1000 ) with no_infomsgs
select ents.iAttrId, fSum = sum ( facts.fSum )
from
    dbo.FactsDayly facts
        inner join
    dbo.Entities ents on facts.iEntityId = ents.iEntityId
group by ents.iAttrId, facts.dtDate

Оптимизатор подсказывает такой индекс:

create nonclustered index IX_iEntityId_incl on dbo.FactsDayly ( iEntityId asc )
    include ( dtDate, fSum ) on [primary]

С ним запрос отработал за 31 сек. в первый раз (план выполнения заранее построен). Последующие запуски давали почти такой же результат. Уровень потребления памяти процессом sqlservr.exe составляет 7.7 Гб, однако предел который установлен для уровня памяти составляет 8.8 Гб. Остановимся пока на этом результате, и посмотрим как обстоят дела в In-Memory базе данных. Используя Visual Studio, можно создать проект типа Analysis Services Tabular Project. Для экономии памяти будем использовать workspace-базу данных в качестве основной. В открытом проекте, используя меню Model запускаем мастер импорта таблиц. В условиях небольшого объема памяти заставляем сервер ею поделиться:

sp_configure 'max server memory', 500
reconfigure

Для начала импортируем таблицы dbo.Dates, dbo.Attrs, dbo.Entities, dbo.FactsDayly. В получившейся табличной модели должны быть установлены отношения внешних ключей между таблицами. В данном случае, сервер, видя внешние ключи в реляционной базе данных, создает связи автоматически. В настройках импорта я исключил из таблицы dbo.Entities текстовый столбец: это делается для экономии памяти, и ввиду колоночного хранения данных, не должно улучшать производительность. Аналог запроса на T-SQL выглядит на языке DAX следующим образом:

evaluate
(
    summarize
    (
        FactsDayly,
        Attrs[iAttrId],
        Dates[dtDate],
        "fSumAgg",
        sum ( FactsDayly[fSum] )
    )
)

Если вы используете не самую современную версию SQL Server Management Studio и под рукой нет стороннего редактора запросов DAX, то данный запрос можно выполнить в редакторе MDX. Если же его требуется выполнить из среды T-SQL, то сделать это можно, создав связанный сервер и запустив запрос через openquery либо exec ... at ... Запустив этот запрос в первый раз, получаем результат за 6 секунд, в последующие запуски время выполнения сократилось до одной секунды! Уровень потребления памяти базой данных Analysis Services составляет 8.6 Гб. Мы получили две базы данных, отчеты которых работают принципиально разное время, в ситуации когда обе базы используют примерно одинаковый уровень ресурсов и идентичное оборудование. Однако для реляционных БД также можно использовать колоночные индексы. Поэтому не все возможности реляционного сервера были использованы. Построим некластерный колоночный индекс на таблице dbo.FactsDayly:

create nonclustered columnstore index IX_col on dbo.FactsDayly ( iEntityId, dtDate, fSum ) on [primary]

Перезапустив запрос на T-SQL, видим в плане, что сканирование сжатого колоночного индекса в пакетном режиме делает свое дело: среднее время выполнения сократилось до 17 сек. Уровень потребления памяти при этом достиг допустимого максимума в 8.6 Гб. Результат намного лучше чем 31 сек., но все же не дотягивает до скорости DAX-запроса, работая в 17 раз медленнее! В ходе данного тестирования In-Memory база данных показала свою силу и преимущества в создании высокоскоростных аналитических отчетов, работающих с большими массивами данных. Рассмотрим теперь пример, где эти преимущества окажутся еще более явными.

При проектировании таблиц фактов может потребоваться их хранение не на каждый день, а только за даты изменения меры, как это сделано в таблице dbo.FactsPeriods. Такой подход позволяет сэкономить значительный объем места, которое занимает таблица, что серьезно облегчает ее сканирование и снижает требования к объему дисков. Для получения агрегатов в группировке по dtDate и iAttrId на основе таблицы dbo.FactsPeriods требуется такой чуть более сложный запрос на T-SQL:

dbcc setcpuweight ( 1000 ) with no_infomsgs
select ents.iAttrId, dates.dtDate, fSum = sum ( pers.fSum )
from
    dbo.FactsPeriods pers
        inner join
    dbo.Entities ents on pers.iEntityId = ents.iEntityId
        inner join
    dbo.Dates dates on dates.dtDate between pers.dtDate and pers.dtDateNext
group by ents.iAttrId, dates.dtDate

Перед его запуском зададим новое значение для максимального уровня памяти, доступного серверу:

exec sp_configure 'max Server memory', 8300
reconfigure

На кэшированных данных запрос работает 44 сек. Проверим его скорость с колоночным индексом:

create nonclustered columnstore index IX_col on dbo.FactsPeriods
    ( iEntityId, dtDate, dtDateNext, fSum ) on [primary]

С колоночным индексом запрос работает 18 секунд (добавление колоночного индекса на dbo.Entities ситуацию не изменило). Теперь ограничим уровень потребления памяти сервером:

exec sp_configure 'max Server memory', 500
reconfigure

В целях экономии памяти удалим из табличной модели In-Memory базы данных таблицу FactsDayly и добавим в модель таблицу FactsPeriods. После загрузки данных в память таблица FactsDayly должна ссылаться на таблицу Entities по столбцу iEntityId и на таблицу Dates по двум столбцам dtDate и dtDateNext. Переведем запрос с языка T-SQL на язык DAX следующим образом:

evaluate
    generate
    (
        values ( Dates[dtDate] ),
        calculatetable
        (
            summarize
            (
                FactsPeriods,
                Attrs[iAttrId],
                "fSumAgg",
                sum ( FactsPeriods[fSum] )
            ),
            filter
            (
                FactsPeriods,
                FactsPeriods[dtDate] <= Dates[dtDate]
                &&
                FactsPeriods[dtDateNext] >= Dates[dtDate]
            )
        )
    )

В первый раз запрос отработал 4 секунды, в последующем - за одну секунду! Уровень потребления памяти In-Memory базой данных оставался при этом на уровне 8.3 Гб.

Если вы будете вводить In-Memory базы данных в промышленную эксплуатацию, конечно, требуют автоматизации задачи администрирования. Например, задачи загрузки данных в память. Используя SQL Server Profiler, несложно увидеть команды на языке XML/A для перезагрузки таблиц. Данные команды также можно запускать на стороне T-SQL через связанный сервер. Наполнять таблицы можно с опцией ProcessAdd, если требуется перегружать не всю таблицу, а лишь вставить новые строки. Либо можно использовать опцию ProcessFull полной перезагрузки данных. Также как и в реляционных базах данных, таблицы в табличных моделях можно секционировать. Например, можно секционировать таблицы фактов по дате, благодаря чему можно не тратить много времени на полную перезагрузку, а перегружать лишь нужные секции. Дополнительно меня порадовало то, что в качестве источника строк для таблицы может выступать хранимая процедура (это недоступно в многомерном Analysis Services). Правда, в табличных моделях в отличие от многомерных баз данных, к сожалению, пока еще нельзя создавать своих clr-сборок.

Сам же язык DAX является мощным и активно развивающимся языком, предоставляющим много конструкций для перевода задач с языка T-SQL на DAX, обеспечивая огромную скорость. Используя SQL Server Profiler, можно отслеживать события, возникающие при выполнения DAX-запросов, например, сканирование VertiPaq. Также профайлером можно находить логический и физический планы выполнения запросов DAX, которые можно анализировать в текстовом виде, что дает возможность заниматься полноценной оптимизацией DAX-запросов. Технология, предоставляемая табличным Analysis Services, еще не используется активно в нашей стране, призываю всех активно начать ее использовать!