SQL Server долго выполняющий запрос, занимающий часы, но использующий низкую загрузку ЦП

Я выполняю некоторые хранимые процедуры в SQL Server 2012 под Windows Server 2012 на выделенном сервере с 32 ГБ ОЗУ и 8 ядрами ЦП. Загрузка ЦП всегда ниже 10%, а использование ОЗУ - 80%, поскольку для SQL Server назначено 20 ГБ (из 32 ГБ).

Существуют некоторые хранимые процедуры, которые занимают 4 часа в некоторые дни, а другие дни, с почти такими же данными, занимают 7 или 8 часов.

Я использую наименее ограничительный уровень изоляции, поэтому я думаю, что это не должно быть проблемой блокировки. Размер базы данных составляет около 100 ГБ, а самая большая таблица содержит около 5 миллионов записей.

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

У меня есть полный контроль над сервером, поэтому я могу изменить любой параметр конфигурации.

У меня есть несколько вопросов:

  1. Можно ли улучшить производительность запросов с помощью параллелизма?
  2. Почему загрузка процессора такая низкая?
  3. Каковы лучшие практики для настройки SQL Server?
  4. Каковы лучшие бесплатные инструменты для аудита сервера? Я попробовал один из Microsoft под названием SQL Server 2012 BPA, но отчет всегда пуст без предупреждений.

РЕДАКТИРОВАТЬ: я проверил журнал, и я нашел это:

18.03.2015 11:09:25,spid26s, неизвестно,SQL Server обнаружил 82 возникновения запросов ввода-вывода, выполнение которых заняло более 15 секунд для файла [C:\Program Files\Microsoft SQL Server\MSSQL11.HLSQLSERVER\MSSQL\DATA\templog.ldf] в базе данных [tempdb] (2). Дескриптор файла ОС - 0x0000000000000BF8. Смещение последнего длинного ввода-вывода: 0x00000001fe4000

Источник

2 ответа

Решение
  1. Увеличьте максимальную память до 24 ГБ.
  2. Переместите tempdb с диска c и рассмотрите несколько файлов tempdb с автоматическим увеличением как минимум до 128 Мбит / с или 256 Мбит / с.
  3. Установите панель мониторинга производительности и запустите отчет панели мониторинга производительности, чтобы увидеть, какие запросы выполняются, и проверить ожидания.
  4. Если вы используете alto frow для журнала данных пользователя и файлов журналов на 10%, измените его на что-то похожее на рост tempdb выше.
  5. Использование панели мониторинга производительности для выявления очевидных отсутствующих индексов, которые прогнозируют влияние улучшения на 95% или более.
  6. Не обращайте внимания на всех недобросовестных Сэйеров, которые говорят не делать то, что я предлагаю. Если вы делаете эти 5 вещей, но у вас все еще возникают проблемы, опубликуйте некоторые результаты с панели мониторинга производительности, которая, кстати, бесплатна.
  7. Еще одна вещь, которая может оказаться полезной, скачать и установить хранимую процедуру sp_whoisactive, запустить ее и посмотреть, какие процессы запущены. Исследуйте запросы, которые вы найдете после запуска sp_whoisactive.
Источник

запрос занимает несколько часов, но использует низкий процессор

Вы говорите так, как будто процессор будет иметь значение для большинства операций с БД. ПОДСКАЗКА: они не делают.

Базы данных нуждаются в IO. Оперативная память греха в некоторых случаях помогает смягчить это, но в конце концов она снижается до IO.

И вы знаете, что я вижу в вашем вопросе? Процессор, память (как-то при условии, что 32 ГБ впечатляет), но НЕТ СЛОВА НА ДИСКОВЫХ РАСПИСАНИЯХ

И это то, что имеет значение. Диски, раздача файлов для распределения нагрузки.

Если вы посмотрите на счетчики производительности, то увидите, что задержка очень высока на дисках - потому что, какой бы "жалкой" (в терминах sql server) структура диска у вас ни была, она просто не соответствует задаче.

Время начинать покупать. SSD намного дешевле, чем диски. Вы можете сказать "О, как они дешевле". Ну, вы не покупаете ГБ - вы покупаете IO. И в прошлый раз, когда я проверял, SSD не стоил в 100 раз дороже дисков - но они имеют 100 и более раз IO. и мы всегда говорим о случайных IO.

Затем изолируйте Tempdb на отдельном SSD - tempdb или ничего не делает, или TON, и вы хотите это увидеть.

Затем изолируйте файл журнала.

Создайте несколько файлов данных для базы данных и базы данных tempdb (особенно для базы данных tempdb - столько, сколько у вас есть ядер).

И да, это будет стоить денег. Но в конце - вам нужен IO, и, как и большинство разработчиков, у вас есть процессор. Плохо для базы данных.

Источник
Другие вопросы по тегам