SQL Server: сочетание разных типов сжатия по алгоритмам ROW и PAGE
На уровне разделов
По сути, существует всего две базовые концепции сжатия таблиц: первая - с целыми таблицами, вторая - сжатие на уровне разделов.
Различные части каждой таблицы могуг использоваться разными способами. К примеру, изменения, вносимые в содержащую историю транзакций таблицу, возможно, будут чаще всего касаться самых последних данных; при этом более старые данные в гой же таблице могут вообще не подвергаться каким-либо изменениям. Отсюда следует, что разумная стратегия для работы с подобной таблицей может выглядеть гак, как показано на рисунке ниже.
Вариант использования сжатия таблицы
В этом случае более старые данные в таблице (показанные зеленым цветом) сжимаются по алгоритму PAGE, текущие данные (показанные красным) — по алгоритму ROW, а некластеризованные индексы для обеих категорий данных сжимаются по алгоритму ROW. При наличии индексов, выровненных но разделам, к ним вполне может применяться стратегия, используемая в отношении базовой таблицы.
Когда применяется сжатие PAGE
Разработчики Microsoft начали оснащать системы SQL Server динамическими административными dynamic management views (DMV) еще в версии SQL Server 2005 и за прошедшее с тех пор время усовершенствовали их. Мы можем задействовать DMV для определения того, каким образом используется та или иная таблица либо индекс. Большую помощь в этом деле может оказать DMV sys.dm_ db index operational stats. В представление включаются столбцы, показывающие, как часто осуществляются операции сканирования (scan) и применяются обновления различных типов. Я намеренно исключил таблицы, подготовленные Microsoft (см. код ниже).
SELECT t.name AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
ios.partition_number AS PartitionNumber,
FLOOR(ios.leaf_update_count * 100.0 /
( ios.range_scan_count + ios.leaf_insert_count
+ ios.leaf_delete_count + ios.leaf_update_count
+ ios.leaf_page_merge_count + ios.singleton_lookup_count
)) AS UpdatePercentage,
FLOOR(ios.range_scan_count * 100.0 /
( ios.range_scan_count + ios.leaf_insert_count
+ ios.leaf_delete_count + ios.leaf_update_count
+ ios.leaf_page_merge_count + ios.singleton_lookup_count
)) AS ScanPercentage
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
INNER JOIN sys.objects AS o
ON o.object_id = ios.object_id
INNER JOIN sys.tables AS t
ON t.object_id = o.object_id
INNER JOIN sys.indexes AS i
ON i.object_id = o.object_id
AND i.index_id = ios.index_id
WHERE ( ios.range_scan_count + ios.leaf_insert_count
+ ios.leaf_delete_count + leaf_update_count
+ ios.leaf_page_merge_count + ios.singleton_lookup_count) <> 0
AND t.is_ms_shipped = 0
ORDER BY TableName, IndexName, PartitionNumber;Проверка числа сканирований и модификаций
В листинге выводится процент времени, в течение которого раздел индекса или таблицы подвергался проверке или обновлению. Представленный код базируется на коде, опубликованном в руководстве «Data Compression: мянутого кода. В этом руководстве Санджай Мишра представил исчерпывающий набор рекомендаций. Мой опыт подтверждает многие изложенные в нем положения. Вообще я рекомендую в большинстве случаев применять сжатие по методу ROW, но на разделах, проверяемых более чем 70% времени и обновляемых менее 15% всего времени, на мой взгляд, следует применять сжатие по алгоритму PAGE. Эти цифры не слишком отличаются от показателей, приведенных в указанном руководстве, и базируются на решениях, которые я нахожу эффективными.
В нашем случае мы могли бы переделать код, представленный выше, так, чтобы он учитывал предложенные рекомендации (см. код ниже).
DECLARE @ScanCutoff int = 70;
DECLARE @UpdateCutoff int = 15;
WITH PartitionStatistics
AS
(
SELECT t.name AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
ios.partition_number AS PartitionNumber,
FLOOR(ios.leaf_update_count * 100.0 /
( ios.range_scan_count + ios.leaf_insert_count
+ ios.leaf_delete_count + ios.leaf_update_count
+ ios.leaf_page_merge_count + ios.singleton_lookup_count
)) AS UpdatePercentage,
FLOOR(ios.range_scan_count * 100.0 /
( ios.range_scan_count + ios.leaf_insert_count
+ ios.leaf_delete_count + ios.leaf_update_count
+ ios.leaf_page_merge_count + ios.singleton_lookup_count
)) AS ScanPercentage
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
INNER JOIN sys.objects AS o
ON o.object_id = ios.object_id
INNER JOIN sys.tables AS t
ON t.object_id = o.object_id
INNER JOIN sys.indexes AS i
ON i.object_id = o.object_id
AND i.index_id = ios.index_id
WHERE ( ios.range_scan_count + ios.leaf_insert_count
+ ios.leaf_delete_count + leaf_update_count
+ ios.leaf_page_merge_count + ios.singleton_lookup_count) <> 0
AND t.is_ms_shipped = 0
)
SELECT TableName, IndexName, IndexID, PartitionNumber,
UpdatePercentage, ScanPercentage,
CASE WHEN UpdatePercentage <= @UpdateCutoff
AND ScanPercentage >= @ScanCutoff
THEN 'PAGE'
ELSE 'ROW'
END AS Recommendation
FROM PartitionStatistics
ORDER BY TableName, IndexName, PartitionNumber;
Измененный способ проверки числа сканирований и модификаций
Не забывайте, что приведенный в коде выше работает со статистикой индексов, которая в данный момент находится в памяти системы. Эти статистические данные удаляются при перезагрузке сервера. В целом я не советую придерживаться этих рекомендаций за исключением тех случаев, когда служба SQL Server работает без перерывов на протяжении не менее трех месяцев.
Не хватает денег на мощное серверное оборудование, на котором вы бы смогли поднять SQL Server? Тогда вот вам идея - испытайте свою удачу на соответствующем сайте (к примеру, здесь). Выберете подходящий слот, разработайте выигрышную стратегию во время бесплатной игры и примените ее, используя реальные деньги!
