В настоящее время у меня возникла странная проблема, с которой я не совсем понимаю SQL Server. Мы используем SQL в качестве хранилища файлов для нашей службы внутреннего хранилища, и наша база данных содержит около полумиллиона строк. Большинство файлов (86%) имеют размер 1 МБ или меньше, но даже в свежих копиях нашей базы данных, где мы просто заполняем таблицу данными для целей теста, кажется, что строки с большими объемами данных, хранящихся в BLOB, часто вызывать таймауты, когда наш SQL Server находится под нагрузкой.
Насколько я понимаю, SQL Server удаляет строки, это процесс сборки мусора, то есть строка помечается как призрак, а затем строка удаляется процессом очистки призрака после того, как изменения копируются в журнал транзакций. Это подсказывает мне, что независимо от размера данных в большом двоичном объекте удаление строки должно быть практически мгновенным. Однако при удалении этих строк мы определенно сталкиваемся с большим количеством таймаутов и поразительно низкой производительностью.
В нашем наборе тестовых данных это файлы размером более 30 МБ, которые вызывают эту проблему. Это крайний случай, мы не часто с ними сталкиваемся, и даже несмотря на то, что мы рассматриваем файловый поток SQL как решение некоторых наших проблем, мы пытаемся сузить круг вопросов, от которых возникают эти проблемы.
Мы выполняем наши удаления внутри транзакции. Мы также выполняем обновления метаданных, таких как статистика размера файла, но они существуют в отдельной таблице, отдельно от самих данных файла. Данные иерархии хранятся в таблице, содержащей информацию о файле.
На самом деле, в конце концов, имеет значение не столько то, что мы делаем с удалениями, мы просто не можем найти никаких ссылок на низкую производительность удаления для строк, которые содержат большой объем данных в BLOB. Мы пытаемся определить, стоит ли вообще это исследовать, или это должен быть один из наших процессов, связанных с удалением, вызывающим проблему.
Есть ли ситуации, в которых это могло произойти? Часто ли сервер базы данных достигает точки полного тайм-аута, когда многие из этих удалений происходят одновременно? Есть ли способ решить эту проблему, если она существует?
Можете ли вы определить «тайм-аут»? Сколько времени нужно для выполнения команды?
SQL Server регистрирует удаление в журнале транзакций, а затем, когда контрольные точки базы данных, удаление фактически удаляется из файла данных. Когда вы запускаете удаление, страницы загружаются в память (если их еще нет), и удаление записывается в журнал транзакций.
Когда вы удаляете данные из таблицы, сколько записей вы пытаетесь удалить одновременно?
Есть ли какие-либо внешние ключи, ссылающиеся на эту таблицу, или сама таблица имеет внешние ключи? Я видел, как производительность удаления снижается, если есть внешний ключ без индекса в столбце, особенно когда у вас столько строк.
Возможно, попробуйте включить план выполнения при выполнении удаления и посмотреть, не происходят ли какие-либо непредвиденные шаги (например, сканирование таблиц). Также проверьте DMV sys.dm_exec_requests, когда у вас есть активный заблокированный запрос, особенно столбцы wait_type, wait_time и wait_resource.