Индекс с колоночным хранением в Denali

В грядущей версии SQL Server 11 - Denali, имеется одно нововведение, призванное увеличить в десятки или даже сотни раз производительность некоторых запросов к хранилищам данных.

Речь идет о индексе с колоночным хранением (index columnstore).
Индекс с колоночным хранением появился в недавно вышедшем Denali CTP3.

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

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

Синтаксис создания колоночного индекса:

CREATE COLUMNSTORE INDEX [Имя индекса] ON [Имя таблицы] (column1, column2... column N);

Я провел тест, с целью сравнения производительности при использовании традиционных индексов и колоночных.

Итак, есть таблица фактов TestTable (
ID INT,
Date DATETIME,
StoreID INT,
Amount NUMERIC(12,4)
).

В таблицу вставлено 2 млн строк. Есть обычные индексы по полям Date, StoreID плюс индекс с included column Amount. Индексы занимают 164 Мб.

Запрос на выборку данных :
-- Таблица с традиционными индексами
SELECT
t.Date, t.StoreID, SUM(t.Amount)
FROM TestTable t
WHERE t.Date BETWEEN '20110101' AND '20110701'
GROUP BY t.Date, t.StoreID

Характеристики выполнения запроса :

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestTable'. Scan count 3, logical reads 8872, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 2215 ms, elapsed time = 1201 ms.


Имеется таблица TestTableColIdx с точно такими же данными, но с индексом типа columnstore.

Инструкция по созданию индекса данного типа:
CREATE COLUMNSTORE INDEX Col_Index ON TestTableColIdx (Date, StoreID, Amount);

Первое, что бросается в глаза - большая разница в размере индексов. Как я уже писал, набор обычных индексов на таблице TestTable, подходящих для тестового запроса, занимает 164 Мб, а в случае колоночного индекса его размер 18 Мб - эффективность сжатия такихиндексов внушает оптимизм :)
Размер индексов я получил при помощи встроенного отчета Disk Usage By Partition.

Выполняем запрос на другой тестовой таблице TestTableColIdx -

-- Таблица с колоночным индексом
SELECT
t.Date, t.StoreID, SUM(t.Amount)
FROM TestTableColIdx t
WHERE t.Date BETWEEN '20110101' AND '20110701'
GROUP BY t.Date, t.StoreID

Характеристики выполнения запроса по таблице TestTableColIdx с колоночным индексом :

Table 'TestTableColIdx'. Scan count 2, logical reads 328, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 55 ms.


Из информации о статистике выполнения видно, что запрос по тестовой таблице с поколоночным индексом выполняется быстрее более чем в 20 раз, а количество чтений станиц из буфера меньше в 27 раз.

Однако, у индексов данного типа имеется и ряд ограничений и недостатков.

Отмечу, на мой взгляд, наиболее важные.

1. Таблица с колоночным индексом не может быть обновлена операторами INSERT, UPDATE, DELETE, MERGE и утилитой массовой загрузки bcp. В документации по Denali CTP3 описаны пути решения этой проблемы (например, удаление индекса, обновление данных, затем создание индекса).
2. Колоночный индекс не может быть кластерным или уникальным.
3. Не может быть построен по столбцам определенных типов.
4. Не может использоваться при репликации и отслеживании изменений в данных - Change Tracking и Change Data Capture.

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

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

Дополнительная информация :
BOL

SQL Server и случай с параллельным изменением данных.

Один мой коллега подошел с вопросом, суть которого такова :
Есть таблица, в которую один процесс производит вставку записей. В то же время из этой таблицы второй процесс удаляет данные. И пока не завершится вставка записей, удаление не происходит, т.е. второй процесс ждет окончания первого. Записи не перекрываются; удаление происходит по условию, под которое не попадают вставляемые записи. Коллега задает вопрос, почему так происходит и как с этим бороться.

Индекса по условию удаления на таблице нет.

Смоделируем пример :
USE [AdventureWorks]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
DROP TABLE TestTable
GO

CREATE TABLE dbo.TestTable
(
Name VARCHAR(100),
CreatedDateTime DATETIME
)
GO

-- эти записи будут удаляться из другой сессии
INSERT INTO TestTable (Name, CreatedDatetime)
VALUES ('Record 1', '20110401'), ('Record 2', '20110401'), ('Record 3', '20110401')
GO

-- Запрос 1. Вставка записей в первой сессии
BEGIN TRAN
INSERT INTO TestTable (Name, CreatedDatetime)
VALUES ('Record 4', '20110402'), ('Record 5', '20110402'), ('Record 6', '20110402')

--COMMIT TRAN

--Запрос 2. Удаление записей во второй сессии
DELETE TestTable WHERE CreatedDateTime = '20110401'

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

Проблему решает режим версионности ALLOW_SNAPSHOT_ISOLATION.

-- удалим все из тестовой таблицы
DELETE TestTable
GO

-- включаем SNAPSHOT
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- эти записи будут удаляться из другой сессии
INSERT INTO TestTable (Name, CreatedDatetime)
VALUES ('Record 1', '20110401'), ('Record 2', '20110401'), ('Record 3', '20110401')
GO

-- Запрос 1. Вставка записей в первой сессии
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
INSERT INTO TestTable (Name, CreatedDatetime)
VALUES ('Record 4', '20110402'), ('Record 5', '20110402'), ('Record 6', '20110402')

--COMMIT TRAN

--Запрос 2. Удаление записей во второй сессии
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
DELETE TestTable WHERE CreatedDateTime = '20110401'

Записи будут удалены из снимка предыдущей версии данных, сохраненных до начала транзакции первого запроса. После этого можно закоммитить первый запрос. В таблице будут находится записи, вставленные в первом запросе. Записи «Record 1», «Record 2», «Record 3» будут удалены.
Надо заметить что это работает только при режиме версионности ALLOW_SNAPSHOT_ISOLATION, но не READ_COMMITTED_SNAPSHOT.

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

По материалам статьи Sarvesh Singh и дискуссии на форуме sqlservercentral.com.


Может ли внешний ключ с проверочным ограничением, без создания индексов, повысить производительность запросов ? Ответ на этот вопрос является утвердительным и вот почему. Оптимизатор может использовать внешний ключ (foreign key) и проверочное ограничение (check constraint) для создания более эффективного плана, на основании ограничений внешнего ключа.

Вот пример.

Создадим две таблицы:

Create Table sales
(
CustomerID INT Primary key
);


Create Table SalesOrderdetail
(
SalesOrderID int Primary key,
CustomerID int Not Null
Constraint FTCustomerID
References sales (CustomerID)
);

Заполним их тестовыми значениями :

insert into sales (CustomerID)
values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

insert into SalesOrderdetail(SalesOrderID,CustomerID)
values (1,1),(2,1),(3,1),(4,2),(5,2),(6,2),(7,3),(8,3),(9,3),(10,4),(11,4),(12,4),(13,5),(14,5),(15,5)

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

Запрос:

select so.* from SalesOrderdetail as so
where exists (select * from sales as s
where so.CustomerID=s.CustomerID)

План запроса 1:

|--Clustered Index Scan(OBJECT:([AdventureWorks].[dbo].[SalesOrderdetail].[PK__SalesOrd__B14003C2184C96B4] AS [so]))

Графический план запроса


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

А что произойдет, если отключить ограничение внешнего ключа ?

Alter Table SalesOrderdetail nocheck constraint FTCustomerID;
GO

select so.* from SalesOrderdetail as so
where exists (select * from sales as s
where so.CustomerID=s.CustomerID)
GO

План запроса 2:

|--Nested Loops(Inner Join, OUTER REFERENCES:([so].[CustomerID]))
|--Clustered Index Scan(OBJECT:([AdventureWorks].[dbo].[SalesOrderdetail].[PK__SalesOrd__B14003C2184C96B4] AS [so]))
|--Clustered Index Seek(OBJECT:([AdventureWorks].[dbo].[sales].[PK__sales__A4AE64B80169315C] AS [s]), SEEK:([s].[CustomerID]=[AdventureWorks].[dbo].[SalesOrderdetail].[CustomerID] as [so].[CustomerID]) ORDERED FORWARD)

Графический план запроса

Это новый план старого запроса.

Так как ограничение внешнего ключа отключено, оптимизатор не знает, имеет ли SalesOrderdetail корректные ссылки на столбец CustomerID таблицы Sales. Поэтому происходит обращение к Sales и применение оператора EXISTS. Как следствие, имеем не самый оптимальный план выполнения.

Теперь включим ограничение внешнего ключа:

Alter table SalesOrderdetail check constraint FTCustomerID;

И снова выполним запрос и посмотрим на план. Если вы ожидали увидеть такой план как на план 1, то это не так. План будет построен по 2 сценарию.
Почему так произошло ?
Это произошло потому, что это ограничение внешнего ключа является "не надежным" (not trusted). Оптимизатор, при построении данного плана не принимает во внимание это ограничение. Внешний ключ не является надежным, т.к. в таблице SalesOrderdetail могут быть записи с таким CustomerID, который отсутствует в Sales. Когда мы включили ограничение внешнего ключа, то оно не проверило уже существующие данные, которые могут быть не корректны ! Например, в SalesOrderdetail может быть значение CustomerID отсутствующее в Sales.

Можно проверить статус внешнего ключа, запустив следующий код:

select name,is_not_trusted from sys.foreign_keys where name= 'FTCustomerID'

(Кстати, многие разработчики и администраторы были удивлены количеством «не надежных» внешних ключей когда запустили этот запрос на своих базах).
Результат запроса подтвердит то, что исследуемый внешний ключ имеет статус “not trusted”. Для изменения статуса на противоположный нужно использовать опцию WITH CHECK, как показано ниже.

Alter table SalesOrderdetail WITH CHECK check constraint FTCustomerID;

Теперь внешний ключ имеет статус «trusted», в этом можно убедиться запустив вышеприведенный запрос из sys.foreign_keys , где для этого ключа в поле is_not_trusted будет значение 0.
И если снова запустить наш тестовый запрос, то мы увидим план 1.

Продолжим опыты, сделаем так, что столбец CustomerID может допускать значения NULL.

drop table SalesOrderdetail
go
Create Table SalesOrderdetail
(
SalesOrderID int Primary key,
CustomerID int Null
Constraint FTCustomerID
References sales (CustomerID)
);

Снова запускаем тестовый запрос и снова видим не самый оптимальный план 2. Оптимизатор выполняет оператор EXISTS для таблицы Sales, несмотря на то, что ключ определен как «надежный». Причиной такого поведения является столбец CustomerID, допускающий значения NULL.

Для возвращения к оптимальному плану нужно изменить запрос следующим образом :

select so.* from SalesOrderdetail as so
where exists (select * from sales as s
where so.CustomerID=s.CustomerID)
and so.customerID is not null

Оптимизатор точно знает, что ни одна запись с CustomerID = NULL не должна быть получена, поэтому не выполняет оператор EXISTS.

Как видно из этой статьи, внешний ключ и проверочное ограничение могут повысить производительность запроса.

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

Выявить такие ключи можно при помощи следующего запроса :

SELECT
name,
is_not_trusted f
FROM sys.foreign_keys
WHERE is_not_trusted = 1

Все вышеописанное справедливо и для подобных тестовых запросов:

select so.* from SalesOrderdetail as so
where so.CustomerID IN (select s.CustomerID from sales as s)

select so.* from SalesOrderdetail as so
JOIN sales as s ON so.CustomerID =s.CustomerID

Оптимизатор работает точно так же, как и в случае тестового запроса, приведенного автором.

Есть также особенность при использовании внешних ключей с ограничениями, которые были определены с параметром NOT FOR REPLICATION. Если внешний ключ был определен с параметром NOT FOR REPLICATION, то он всегда будет в состоянии «not trusted». Изменить поведение этого ключа можно только пересоздав его без опции NOT FOR REPLICATION.

T-SQL 2008. Приятные мелочи :)

Как известно, в SQL Server 2008 добавилось много замечательных улучшений языка T-SQL.

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

1. Можно инициализировать переменные при их объявлении.

DECLARE @M INT = 10

2. Появились операции +=, —=,/=,%=, &=, |=, and ^= , позволяющие писать более компактный и красивый код.

3. Вставка нескольких значений в одной инструкции INSERT. При вставке нескольких значений в таблицу, нет необходимости писать несколько инструкций INSERT – VALUE или SELECT – UNION.

Пример использования :

-- Задача по получению значений столбца в одну строку

CREATE TABLE #Table
(
Col VARCHAR(10)
)

INSERT INTO #Table(Col)
VALUES ('AAA'), ('BBB'), ('CCC'), ('DDD'), ('EEE')

/*
раньше вместо этого приходилось писать несколько раз INSERT… VALUE или SELECT … UNION
*/

DECLARE @Str VARCHAR(2000) = '' -- сразу инициализируем переменную

SELECT @Str += Col + ',' FROM #Table -- при помощи += получаем более
-- компактный код
PRINT @Str

DROP TABLE #Table

Сжатие данных SQL Server 2008

Cжатие данных в SQL Server 2008.

 

Недавно мне пришлось выполнять  миграцию своего хранилища данных с SQL Server 2005 на SQL Server 2008. Как известно, одним из новшеств SQL Server 2008 является сжатие данных. Эта возможность призвана увеличить производительность базы данных за счет сжатия данных и индексов в таблицах и индексированных представлениях и, как следствие, уменьшения операций ввода-вывода. Также, благодаря сжатию, может существенно уменьшится размер базы, что облегчает администрирование и управление. Все это звучало заманчиво, и я решил использовать эту возможность.  

  

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

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

 

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

 

Было решено исследовать эффект от сжатия данных и провести тестирование, чтобы ответить на вопрос о падении производительности.  

  

Подготовка теста.  

 

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

Вот ее структура: 

 

[ID] [int],    -- PK

[StockID] [smallint],  -- склад

[ProductID] [int],  -- товар

[DateID] [smallint],  -- дата

[StockFormatID] [smallint], -- тип склада

[Qty] [smallint]  -- количество 

 

Исходный размер таблицы - 16 ГБ, индексов - 18 ГБ (я воспользовался системной  ХП sp_spaceused для определения размеров данных и индексов). 

 

Теперь самое время решить по каким критериям будем оценивать эффективность сжатия.

 

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

 

Вот тестовый запрос на выборку  к данной таблице :

  

SET STATISTICS TIME ON        -- для измерения времени выполнения запроса

SET STATISTICS IO ON            -- для измерения логических и физических операций ввода-вывода

GO 

 

SELECT   

  fact.DateID,  

  fact.StockID,  

  SUM(fact.Qty) AS Qty

FROM fact.ProductMMR fact

WHERE (fact.DateID BETWEEN @DateIDBegin AND @DateIDEnd)

GROUP BY fact.DateID, fact.StockID  

 

Был задан временной промежуток 30 дней (в таблице фактов это соответствует более 150 млн. записей). 

  

Определение стратегии  сжатия и его реализация. 

 

Подробно про реализацию сжатия можно почитать в MSDN. Там же описана реализация сжатия для страниц и для строк. Эффект от сжатия зависит от данных в таблице - насколько много там повторяющихся значений и каков тип данных.

 

Теперь перейдем к реализации сжатия, предварительно определив его стратегию. 

Sunil Agarwal в своем блоге приводит ряд рекомендаций по этому поводу, позволю себе их обобщить и привести здесь:

1. Не имеет смысла сжимать  данные или индексы имеющие  малый размер. Если таблица занимает 10 страниц,  и будет сжата до  одной, то это не принесет  выгоды в данном случае. Необходимо  помнить о том, что сжатые  данные должны быть распакованы  каждый раз, когда к ним осуществляется  доступ. Перед применением сжатия,  вы должны оценить текущий  размер таблицы/индекса и прогнозируемый  размер после сжатия.

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

3. Если экономия от сжатия невысока, тогда сжатие проводить не рекомендуется. Бывают такие случаи, когда размер сжатых данных оказывается больше несжатых. Это говорит о том, что в таблице используются наиболее компактные типы данных.

4. Если у вас типичное OLTP-приложение, в общем случае вам следует  выбирать сжатие типа ROW. Этот  тип сжатия   менее затратный  с точки зрения распаковки  данных. Однако, как правило, сжатие  типа PAGE более эффективно,   в  плане потенциального свободного  пространства. 

Оценить выгоду от сжатия можно либо в мастере, либо при помощи хранимой процедуры sp_estimate_data_compression_savings.

 

В моем случае я получил такие  результаты : 

 

Таблица 1.

Эффективность сжатия данных.

 

Тип сжатия	Размер до сжатия	Размер после сжатия	% сжатия
ROW	33,4 ГБ	22,7 ГБ	32 %
PAGE	33,4 ГБ	18,3 ГБ	45 %

 

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

 

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

 

Видно, что в тестовой таблице сжатие на уровне строк будет не столь эффективным, как сжатие страниц. Необходимо принять во внимание, что сжатие PAGE является надмножеством сжатия ROW.

Реализовать сжатие таблицы типа PAGE/ROW можно через мастер сжатия, генерирующего подобный код: 

 

ALTER TABLE [fact].[ProductMMR] REBUILD PARTITION = ALL

WITH 

(DATA_COMPRESSION = ROW

)

Применить сжатие типа PAGE можно, применив параметр DATA_COMPRESSION = PAGE.

Указав DATA_COMPRESSION = NONE можно  отменить сжатие данных.

Я не буду приводить здесь синтаксис  сжатия индексов и партиций, интересующийся без труда найдет их в BOL.

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

 

 Результаты тестирования.

 

Итак, до и после сжатия по типу PAGE был выполнен тестовый запрос.

Вот его результаты, на «разогретом» кэше:

 

Таблица 2.

Результаты теста № 1*.

 

Тип сжатия	Время выполнения запроса(мс)	Операций логического  чтения**	Затраченное процессорное время (мс)
Без сжатия	26 147	1 419 113	308 736
Сжатие PAGE	41 104	709 360	486 453

 

*Запрос выполнялся на сервере с 12 ядрами и 32 Гб ОЗУ, дисковая подсистема 10 RAID. 

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

 

Увидев эти результаты, можно удивиться – ведь операций логического чтения на сжатых данных было произведено в два раза меньше, но время выполнения запроса оказалось на 36 % больше. А все дело видимо в том, что хоть операций чтения меньше и читается все из памяти, но велики накладные расходы на распаковку данных. Ведь распаковывается не страница целиком, а каждая запись по отдельности.

 

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

 

Поэтому было решено провести еще  один цикл тестов, но уже на холодном кэше.

 

Был выполнен то же самый тестовый запрос, но предварительно был очищен кэш процедур и буфер, при помощи команд DBCC FREEPROCCACHE и DBCC DROPCLEANBUFFERS.

 

Вот результаты тестового запроса до и после сжатия, на «холодном» кэше:

 

Таблица 3.

Результаты теста № 2.

 

Тип сжатия	Время выполнения запроса(мс)	Операций физического  чтения	Операций логического  чтения	Затраченное процессорное время (мс)
Без сжатия	43 077	1 419 105	1 420 868	235 266
Сжатие данных PAGE	48 887	707 495	710 105	416 689

 

Вот эти результаты подтверждают ранее  высказанное предположение. Как  видно, время выполнения отличается на 12 %, вместо 36 % из первого теста.

 

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

 

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

 

Но самая главная причина  того, что в моем случае упала  производительность запросов - это  относительно невысокий коэффициент  сжатия, менее 50 %. Я провел еще несколько тестов и обнаружил, что на тех таблицах, которые сжимались на 60-75 %, производительность запросов увеличивалась по сравнению с несжатыми таблицами.

 

Очевидно, что чем выше процент  сжатия, тем сильнее это скажется на приросте производительности.

 

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

 

 

Сергей Харыбин, MCTS SQL Server.