Как улучшить производительность в таблице SQL Server с полями изображений?
У меня есть очень конкретная проблема производительности на работе!
В системе мы используем существует таблица, которая содержит информацию о текущем процессе рабочего процесса. Одно из полей содержит электронную таблицу, которая содержит метаданные о процессе (не спрашивайте меня почему!! и НЕ я не МОГУ ИЗМЕНИТЬ IT!!)
Проблема состоит в том, что эта электронная таблица хранится в Поле изображений в SQL Server 2005 (в наборе базы данных с совместимостью SQL 2000).
Эта таблица в настоящее время имеет 22K + строки и даже простой запрос как это:
SELECT TOP 100 *
FROM OFFENDING_TABLE
Занимает 30 секунд для получения данных в Анализаторе Запроса.
Я думаю об обновлении совместимости к SQL 2005 (однажды это, мне сообщили, что приложение может обработать его).
Вторая вещь я думаю, состоит в том, чтобы изменить тип данных столбца к varbinary(max) но я не знаю, если выполнение этого будет влиять на приложение.
Другая вещь, которую я рассматриваю, состоит в том, чтобы использовать sp_tableoption установить large value types out of row кому: 1 поскольку это в настоящее время 0, но у меня нет информации, если выполнение этого улучшит производительность.
Кто-либо знает, как улучшить производительность в таком сценарии?
Отредактированный для разъяснения
Моя проблема состоит в том, что я не имею никакого контроля на том, что приложение спрашивает к SQL Server, и я сделал некоторое Отражение о нем (приложением является.NET 1,1 веб-сайта), и это использует незаконное поле для некоторого внутреннего материала, что я понятия не имею, каково это.
Я должен улучшить общую производительность этой таблицы.
4 ответа
Я бы посоветовал вам посмотреть на здоровье макета таблицы:
select * from sys.dm_db_index_physical_stats(
db_id(), object_id('offending_table'), null, null, detailed);
Нужно обратить внимание на такие показатели, как avg_fragmentation_in_percent, page_count, avg_page_space_used_in_percent, record_count и ghost_record_count. Такие признаки, как высокая фрагментация, или большое количество призрачных записей, или низкий процент использованных страниц, указывают на проблемы, и ситуация может быть довольно сильно улучшена, просто перестроив индекс (т.е. таблицу) с нуля:
ALTER INDEX ALL ON offending_table REBUILD;
Я говорю это, учитывая, что вы не можете изменить ни таблицу, ни приложение. Если бы вы могли изменить таблицу и приложение, то советы, которые вы уже получили, являются хорошими (не используйте '*', не выбирайте без условия, используйте более новый тип varbinary(max) и т.д. и т.п.).
Я бы также посмотрел на среднее время жизни страницы в счетчиках производительности, чтобы понять, не не хватает ли системе памяти. Из вашего описания симптомов система выглядит связанной IO, что наводит меня на мысль, что кэширование страниц происходит слабо, и больше оперативной памяти могло бы помочь, а также более быстрый IO подсистемы. На системе SQL 2008 я бы также предложил включить сжатие страниц, но на 2005 это невозможно.
И, чтобы быть уверенным, убедитесь, что запросы не блокируются из-за зацикливания самого приложения, т.е. запрос не тратит 90% из этих 30 секунд на ожидание блокировки строки. Посмотрите на sys.dm_exec_requests во время выполнения запроса, посмотрите время ожидания, тип ожидания и ресурс ожидания. Это PAGEIOLATCH_XX? Или это блокировка? Также, как обстоят дела с sys.dm_os_wait_stats на вашем сервере, каковы основные причины ожидания?
Прежде всего - никогда не выполняйте SELECT * в производственном коде - сообщать или нет.
У вас есть три основных варианта:
переместить это поле большого двоичного объекта в отдельную таблицу, если оно не всегда требуется; вероятно, это непрактично, поскольку вы упомянули, что не можете изменить схему
, будьте более осторожны с операторами
SELECT, чтобы выбрать только те поля, которые вам действительно нужны - и опустите поле blob, посмотрите, можете ли вы ограничить ваш запрос, чтобы включить предложение
WHEREи найти способ оптимизации плана запроса, например добавление подходящего индекса в таблицу (если вы можете)
Нет никакого волшебного переключателя "сделай это быстрее" - но вы можете оптимизировать свой запрос или оптимизировать макет таблицы. Оба помогают. Если вы ничего не можете изменить - ни макет таблицы, ни добавить индекс, ни изменить запросы, боюсь, вам будет сложно что-либо оптимизировать ....
Просто измените поле на VARBINARY. (MAX) вообще ничего не изменит - не следует ожидать улучшения производительности просто от изменения типа данных.
Короткий ответ - выполнять SELECT только для нескольких строк, когда возвращенные поля не включают в себя поле неправильного изображения, то есть нет SELECT *. Если вам нужно значение поля изображения, извлекайте его в индивидуальном порядке.
Установка опции больших типов значений вне строки определенно должна помочь производительности. Размер строки будет значительно меньше, SQL Server сможет выполнять гораздо меньше физических чтений, чтобы пройти через таблицу.