Этот вопрос связан с эта ветка форума.
Запуск 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.
Итак, сначала мои академические вопросы:
И, может быть, более важный, мой практический вопрос:
Вот как воспроизвести описанный тест (выполняется через окно запроса 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 |
Мы не можем просто использовать вместо этого файловый поток, потому что:
Проверяли теорию о том, что данные записываются в журнал транзакций как часть удаления, и это, похоже, не так. Я тестирую это неправильно? Увидеть ниже.
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
.
Кроме того, я бы ожидал, что сами страницы будут грязными, если данные будут записаны в журнал. Кажется, что в журнал регистрируются только освобождения, что соответствует тому, что было грязным после удаления.
Я получил ответ от Пола Рэндала. Он подтвердил тот факт, что он должен прочитать все страницы, чтобы пройти по дереву и найти, какие страницы нужно освободить, и заявил, что нет другого способа найти, какие страницы. Это половинный ответ на вопросы 1 и 2 (хотя и не объясняет необходимость блокировок для данных вне ряда, но это мелочь).
Вопрос 3 все еще открыт: зачем освобождать страницы заранее, если уже есть фоновая задача для очистки от удалений?
И, конечно же, очень важный вопрос: есть ли способ напрямую смягчить (то есть не обойти) это поведение при удалении в зависимости от размера? Я думаю, это будет более распространенная проблема, если только мы не единственные, кто хранит и удаляет строки размером 50 МБ в SQL Server? Кто-нибудь еще работает над этим с помощью какой-либо формы работы по сборке мусора?
Я не могу сказать, почему именно удаление VARBINARY (MAX) было бы гораздо более неэффективным по сравнению с файловым потоком, но одна идея, которую вы могли бы рассмотреть, если вы просто пытаетесь избежать тайм-аутов из своего веб-приложения при удалении этих LOBS. Вы можете сохранить значения VARBINARY (MAX) в отдельной таблице (давайте назовем ее tblLOB), на которую ссылается исходная таблица (давайте вызовем эту tblParent).
Отсюда, когда вы удаляете запись, вы можете просто удалить ее из родительской записи, а затем периодически запускать процесс сборки мусора и очищать записи в таблице LOB. Во время этого процесса сборки мусора может быть дополнительная активность жесткого диска, но она, по крайней мере, будет отделена от внешнего веб-интерфейса и может выполняться в непиковое время.