Назад | Перейти на главную страницу

Удаление производительности для данных LOB в SQL Server

Этот вопрос связан с эта ветка форума.

Запуск SQL Server 2008 Developer Edition на моей рабочей станции и двухузлового кластера виртуальных машин Enterprise Edition, где я имею в виду «альфа-кластер».

Время, необходимое для удаления строк со столбцом varbinary (max), напрямую связано с длиной данных в этом столбце. Сначала это может показаться интуитивно понятным, но после исследования это противоречит моему пониманию того, как SQL Server на самом деле удаляет строки в целом и обрабатывает такие данные.

Проблема связана с тайм-аутом удаления (> 30 секунд), который мы наблюдаем в нашем веб-приложении .NET, но я упростил его для этого обсуждения.

Когда запись удаляется, SQL Server помечает ее как призрак, который необходимо очистить с помощью Задачи очистки призрака позднее после фиксации транзакции (см. Блог Пола Рэндала). В тесте, удаляющем три строки с данными 16 КБ, 4 МБ и 50 МБ в столбце varbinary (max), соответственно, я вижу, что это происходит на странице с внутренней частью данных, а также в транзакции журнал.

Что мне кажется странным, так это то, что X-блокировки устанавливаются на все страницы данных LOB во время удаления, а страницы освобождаются в PFS. Я вижу это в журнале транзакций, а также с sp_lock и результаты dm_db_index_operational_stats DMV (page_lock_count).

Это создает узкое место ввода-вывода на моей рабочей станции и нашем альфа-кластере, если эти страницы еще не находятся в буферном кеше. Фактически, page_io_latch_wait_in_ms из того же DMV - это практически весь период удаления, а page_io_latch_wait_count соответствует количеству заблокированных страниц. Для файла размером 50 МБ на моей рабочей станции это составляет более 3 секунд при запуске с пустым буферным кешем (checkpoint / dbcc dropcleanbuffers), и я не сомневаюсь, что это было бы больше для сильной фрагментации и под нагрузкой.

Я пытался убедиться, что это время занимало не просто выделение места в кеше. Я прочитал 2 ГБ данных из других строк перед выполнением удаления вместо checkpoint метода, который больше, чем выделено процессу SQL Server. Не уверен, что это правильный тест или нет, так как я не знаю, как SQL Server перетасовывает данные. Я предполагал, что он всегда будет вытеснять старое в пользу нового.

Более того, он даже не изменяет страницы. Это я вижу с dm_os_buffer_descriptors. Страницы остаются чистыми после удаления, в то время как количество измененных страниц меньше 20 для всех трех малых, средних и больших удалений. Я также сравнил вывод DBCC PAGE для выборки найденных страниц, и изменений не было (только ALLOCATED бит был удален из PFS). Он просто освобождает их.

Чтобы еще раз доказать, что поиск / освобождение страниц вызывают проблему, я попробовал тот же тест, используя столбец файлового потока вместо vanilla varbinary (max). Время удаления было постоянным, независимо от размера LOB.

Итак, сначала мои академические вопросы:

  1. Почему SQL Server нужно искать все страницы данных LOB, чтобы X заблокировать их? Это всего лишь деталь того, как блокировки представлены в памяти (как-то хранятся вместе со страницей)? Это заставляет влияние ввода-вывода сильно зависеть от размера данных, если они не полностью кэшированы.
  2. Зачем вообще X блокируются, просто чтобы освободить их? Разве не достаточно заблокировать только лист индекса с внутренней частью, поскольку при освобождении не требуется изменять сами страницы? Есть ли другой способ получить данные LOB, от которых защищает блокировка?
  3. Зачем вообще освобождать страницы заранее, если для этого типа работы уже есть фоновая задача?

И, может быть, более важный, мой практический вопрос:

Вот как воспроизвести описанный тест (выполняется через окно запроса SSMS):

CREATE TABLE [T] (
    [ID] [uniqueidentifier] NOT NULL PRIMARY KEY,
    [Data] [varbinary](max) NULL
)

DECLARE @SmallID uniqueidentifier
DECLARE @MediumID uniqueidentifier
DECLARE @LargeID uniqueidentifier

SELECT @SmallID = NEWID(), @MediumID = NEWID(), @LargeID = NEWID()
-- May want to keep these IDs somewhere so you can use them in the deletes without var declaration

INSERT INTO [T] VALUES (@SmallID, CAST(REPLICATE(CAST('a' AS varchar(max)), 16 * 1024) AS varbinary(max)))
INSERT INTO [T] VALUES (@MediumID, CAST(REPLICATE(CAST('a' AS varchar(max)), 4 * 1024 * 1024) AS varbinary(max)))
INSERT INTO [T] VALUES (@LargeID, CAST(REPLICATE(CAST('a' AS varchar(max)), 50 * 1024 * 1024) AS varbinary(max)))

-- Do this before test
CHECKPOINT
DBCC DROPCLEANBUFFERS
BEGIN TRAN

-- Do one of these deletes to measure results or profile
DELETE FROM [T] WHERE ID = @SmallID
DELETE FROM [T] WHERE ID = @MediumID
DELETE FROM [T] WHERE ID = @LargeID

-- Do this after test
ROLLBACK

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

| Column Type | Delete Size | Duration (ms) | Reads | Writes | CPU |
--------------------------------------------------------------------
| VarBinary   | 16 KB       | 40            | 13    | 2      | 0   |
| VarBinary   | 4 MB        | 952           | 2318  | 2      | 0   |
| VarBinary   | 50 MB       | 2976          | 28594 | 1      | 62  |
--------------------------------------------------------------------
| FileStream  | 16 KB       | 1             | 12    | 1      | 0   |
| FileStream  | 4 MB        | 0             | 9     | 0      | 0   |
| FileStream  | 50 MB       | 1             | 9     | 0      | 0   |

Мы не можем просто использовать вместо этого файловый поток, потому что:

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

Обновление 1

Проверяли теорию о том, что данные записываются в журнал транзакций как часть удаления, и это, похоже, не так. Я тестирую это неправильно? Увидеть ниже.

SELECT MAX([Current LSN]) FROM fn_dblog(NULL, NULL)
--0000002f:000001d9:0001

BEGIN TRAN
DELETE FROM [T] WHERE ID = @ID

SELECT
    SUM(
        DATALENGTH([RowLog Contents 0]) +
        DATALENGTH([RowLog Contents 1]) +
        DATALENGTH([RowLog Contents 3]) +
        DATALENGTH([RowLog Contents 4])
    ) [RowLog Contents Total],
    SUM(
        DATALENGTH([Log Record])
    ) [Log Record Total]
FROM fn_dblog(NULL, NULL)
WHERE [Current LSN] > '0000002f:000001d9:0001'

Для файла размером более 5 МБ это возвращало 1651 | 171860.

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

Обновление 2

Я получил ответ от Пола Рэндала. Он подтвердил тот факт, что он должен прочитать все страницы, чтобы пройти по дереву и найти, какие страницы нужно освободить, и заявил, что нет другого способа найти, какие страницы. Это половинный ответ на вопросы 1 и 2 (хотя и не объясняет необходимость блокировок для данных вне ряда, но это мелочь).

Вопрос 3 все еще открыт: зачем освобождать страницы заранее, если уже есть фоновая задача для очистки от удалений?

И, конечно же, очень важный вопрос: есть ли способ напрямую смягчить (то есть не обойти) это поведение при удалении в зависимости от размера? Я думаю, это будет более распространенная проблема, если только мы не единственные, кто хранит и удаляет строки размером 50 МБ в SQL Server? Кто-нибудь еще работает над этим с помощью какой-либо формы работы по сборке мусора?

Я не могу сказать, почему именно удаление VARBINARY (MAX) было бы гораздо более неэффективным по сравнению с файловым потоком, но одна идея, которую вы могли бы рассмотреть, если вы просто пытаетесь избежать тайм-аутов из своего веб-приложения при удалении этих LOBS. Вы можете сохранить значения VARBINARY (MAX) в отдельной таблице (давайте назовем ее tblLOB), на которую ссылается исходная таблица (давайте вызовем эту tblParent).

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