Как уменьшить использование журнала транзакций SQL Server

У нас есть приложение, которое записывает журналы в таблицах Azure SQL. Структура таблицы следующая.

CREATE TABLE [dbo].[xyz_event_history]
(
    [event_history_id] [uniqueidentifier] NOT NULL,
    [event_date_time] [datetime] NOT NULL,
    [instance_id] [uniqueidentifier] NOT NULL,
    [scheduled_task_id] [int] NOT NULL,
    [scheduled_start_time] [datetime] NULL,
    [actual_start_time] [datetime] NULL,
    [actual_end_time] [datetime] NULL,
    [status] [int] NOT NULL,
    [log] [nvarchar](max) NULL,

    CONSTRAINT [PK__crg_scheduler_event_history] PRIMARY KEY NONCLUSTERED 
    (
        [event_history_id] ASC
    )
)

Таблица хранится как кластеризованный индекс столбцом scheduled_task_id (не уникальным).

CREATE CLUSTERED INDEX [IDX__xyz_event_history__scheduled_task_id] ON [dbo].[xyz_event_history]
(
    [scheduled_task_id] ASC
)

event_history_id, сгенерированный приложением, является случайным (не последовательным) GUID. Приложение создает, обновляет и удаляет старые объекты из таблицы. Столбец log обычно содержит 2-10 КБ данных, но в некоторых случаях он может вырасти до 5-10 МБ. К элементам обычно обращаются PK (event_history_id), а наиболее частый порядок сортировки - event_date_time desc.

Проблема, которую мы видим после того, как мы снизили уровень производительности для Azure SQL до "S3" (100 DTU), пересекает ограничения скорости транзакций. Это можно четко увидеть в таблице sys.dm_exec_requests - будут записи с типом ожидания LOG_RATE_GOVERNOR (msdn).

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

Операции, которые я заметил, которые оказывают большое влияние на скорость регистрации, - это удаления из xyz_event_history и обновления в столбце log. Обновления сделаны следующим образом.

UPDATE xyz_event_history
SET [log] = COALESCE([log], '') + @log_to_append
WHERE event_history_id = @id

Модель восстановления баз данных Azure SQL FULL и не может быть изменена.

Вот статистика физического индекса - есть много страниц, которые пересекаются с пределом 8K на строку.

TableName           AllocUnitTp PgCt    AvgPgSpcUsed        RcdCt   MinRcdSz    MaxRcdSz
xyz_event_history   IN_ROW_DATA 4145    47.6372868791698    43771   102         7864
xyz_event_history   IN_ROW_DATA 59      18.1995058067705    4145    11          19
xyz_event_history   IN_ROW_DATA 4       3.75277983691623    59      11          19
xyz_event_history   IN_ROW_DATA 1       0.914257474672597   4       11          19
xyz_event_history   LOB_DATA    168191  97.592290585619     169479  38          8068
xyz_event_history   IN_ROW_DATA 7062    3.65090190264393    43771   38          46
xyz_event_history   IN_ROW_DATA 99      22.0080800593032    7062    23          23
xyz_event_history   IN_ROW_DATA 1       30.5534964170991    99      23          23
xyz_event_history   IN_ROW_DATA 2339    9.15620212503089    43771   16          38
xyz_event_history   IN_ROW_DATA 96      8.70488015814184    2339    27          27
xyz_event_history   IN_ROW_DATA 1       34.3711391153941    96      27          27
xyz_event_history   IN_ROW_DATA 1054    26.5034840622683    43771   28          50
xyz_event_history   IN_ROW_DATA 139     3.81632073140598    1054    39          39
xyz_event_history   IN_ROW_DATA 1       70.3854707190511    139     39          39

• Есть ли способ сократить использование журнала транзакций?
• Как SQL Server регистрирует транзакции, как в примере выше? Это просто "старое" плюс "новое" значение? (что, вероятно, сделало бы добавление небольших фрагментов данных, которые зачастую были бы весьма неэффективными с точки зрения размера журнала транзакций).

ОБНОВЛЕНИЕ (20 апреля): Я провел несколько экспериментов с предложениями в ответах и ​​был впечатлен различием, которое делает INSERT вместо UPDATE.

В соответствии с следующей статьей msdn о внутренних журналах транзакций SQL Server (https://technet.microsoft.com/en-us/library/jj835093(v=sql.110).aspx):

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

Это автоматически делает сценарий с UPDATE ... SET X = X + 'more' крайне неэффективным с точки зрения использования журнала транзакций - для этого требуется "до захвата изображения".

Я создал простой тестовый набор, чтобы протестировать оригинальный способ добавления данных в столбец "log" в сравнении с тем, как мы просто вставляем новую часть данных в новую таблицу. Результаты, которые я получил довольно удивительно (не для меня, не слишком опытный парень SQL Server).

Тест прост: 5'000 раз добавить 1'024 символа длинной части журнала - всего 5 МБ текста в результате (не так уж плохо, как можно было бы подумать).

FULL recovery mode, SQL Server 2014, Windows 10, SSD

                    UPDATE         INSERT
Duration             07:48 (!)      00:02
Data file grow        ~8MB           ~8MB
Tran. Log grow      ~218MB (!)        0MB (why?!)

Всего 5000 обновлений, которые добавляют 1 Кбайт данных, могут отключать SQL Server в течение 8 минут (wow!). Я этого не ожидал!

Я думаю, что исходный вопрос разрешен на этом этапе, но следующие подняты:

• Почему транзакционный журнал растет, выглядит линейно (не квадратично, как мы можем ожидать, когда просто захватываем изображения "до" и "после" )? Из диаграммы видно, что количество элементов в секунду растет пропорционально на квадратный корень - это, как ожидалось, если накладные расходы растут линейно с количеством вставленных элементов.
• Почему в случае, если журнал транзакций вставки имеет тот же размер, что и перед любыми вставками? Я просмотрел журнал транзакций (с Dell Toad) для случая со вставками и выглядит как только последние 297 элементов находятся там - предположительно журнал транзакций был усечен, но почему, если он FULL режим восстановления?

ОБНОВЛЕНИЕ (21 апреля). DBCC LOGINFO вывод для случая с INSERT - до и после. Физический размер файла журнала соответствует выходу - ровно 1 048 576 байт на диске. Почему он выглядит, как журнал транзакций остается неподвижным?

RecoveryUnitId  FileId  FileSize    StartOffset FSeqNo  Status  Parity  CreateLSN
0               2       253952      8192        131161  0       64      0        
0               2       253952      262144      131162  2       64      0        
0               2       253952      516096      131159  0       128     0        
0               2       278528      770048      131160  0       128     0        

RecoveryUnitId  FileId  FileSize    StartOffset FSeqNo  Status  Parity  CreateLSN
0               2       253952      8192        131221  0       128     0        
0               2       253952      262144      131222  0       128     0        
0               2       253952      516096      131223  2       128     0        
0               2       278528      770048      131224  2       128     0        

Для тех, кто заинтересован, я записал действия "sqlserv.exe", используя Process Monitor - я вижу, что файл перезаписывается снова и снова - похоже, что SQL Server обрабатывает старые записи журналов, поскольку по какой-то причине больше не требуется: https://dl.dropboxusercontent.com/u/1323651/stackoverflow-sql-server-transaction-log.pml.

ОБНОВЛЕНИЕ (24 апреля).Кажется, я наконец начал понимать, что там происходит, и хочу поделиться с вами. Вышеприведенное рассуждение верно в целом, но имеет серьезные оговорки, что также приводило к путанице в отношении странного использования журнала транзакций с помощью INSERT s.

База данных будет вести себя как в режиме восстановления SIMPLE до тех пор, пока не будет полностью заполнен выполняется резервное копирование (даже если оно находится в режиме восстановления FULL).

Мы можем рассматривать числа и диаграмму выше как действительные для режима восстановления SIMPLE, и мне нужно повторить измерение для реального FULL - они еще более поражают.

                    UPDATE         INSERT
Duration             13:20 (!)      00:02
Data file grow         8MB           11MB
Tran. log grow      55.2GB (!)       14MB