Высокая загрузка CPU на сервере СУБД MS SQL Server

Наблюдаем высокую загрузку CPU по счетчикам Processor Time на сервере СУБД c MS SQL Server.
Что делать?

Симптом

Видим высокую загрузку CPU на сервере MS SQL Server.

Загрузку видим «сейчас», при этом по данным Performance Monitor, Диспетчера задач или Монитора ресурсов мы уверены, что основную нагрузку создает именно MS SQL Server.

Что требуется сделать

Существует два подхода к получению ответа на вопрос: «Почему и что именно создает такую нагрузку».

Каждый из подходов может оказаться полезным.

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

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

Смысл в том, чтобы указать такие фильтры

которые будут включать имена таблиц в найденном вами на предыдущем шаге запросе.

Если всё аккуратно сделаете, то в полученном технологическом журнале запрос у вас будет только тот, который нужен.

Журнал получится небольшим.

Собственно стек из кода на встроенном языке будет сразу в конце события с запросом.

Top запросов, создающих нагрузку на CPU на сервере СУБД за последний час

Нагрузка на CPU по базам

Наибольшая нагрузка на CPU

Наиболее часто выполняемые запросы

Индексы с высокими издержками при использовании

Подход 2

Подключиться к серверу СУБД. Запустить MS Sql Server Management Studio

Выяснить, какие именно базы создают наибольшую нагрузку на сервер СУБД за последний период (в течение которого наблюдается нагрузка). В этом примере период 1 час ’01:00:00.000′, его нужно будет изменить.

Получаем список запросов, которые создали нагрузку за последний час, посчитанный по 10000 запросов. (Выполняется около 2 минут).

В первую очередь смотрим на percent_worker_time и percent_elapsed_time. Нагрузка не должна быть «размазана» между всеми запросами.

4.1. Если нагрузка «размазана» по запросам, нужно настраивать трассировку

4.1.1. Для этого на сервере должна быть директория для трассировки.

Нужно директорию указать вместо ‘InsertFileNameHere’ в скрипте ниже.

Размер файла трассировки ограничен 1 Гб.

4.1.2. Останавливаем трассировку, когда уверены, что трассировку собрали в интересующий период нагрузки.

4.1.3. Сохраняем трассировку в тестовую БД test в таблицу trace, в которой будем анализировать загрузку.

4.1.4. Добавляем в таблицу trace две колонки HashSQL varchar(4000) и HashSQLMD5 varbinary(32).

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

4.1.5. Анализируем трассировку. Например, смотрим:

Находим наиболее интересные запросы по SUM([CPU]).

В целом эту же методику можно использовать для поиска по Reads, Writes, Duration, и т.д.

4.1.6. Для того, чтобы найти запрос в коде конфигурации настраиваем тех журнал вида

указываем ключевые слова из запроса.

4.1.7. По технологическому журналу определяем лидера, исправляем.

4.2. Если есть явный лидер, лучше начать с этого лидера и повторить алгоритм.

4.2.1. Для того, чтобы найти запрос в коде конфигурации настраиваем тех журнал вида

указываем ключевые слова из запроса.

4.2.2. По технологическому журналу определяем лидера, исправляем в коде конфигурации.

В собранном технологическом журнале будет искомый запрос с указанием стека на встроенном языке, «откуда» этот запрос выполнялся.

Источник

Устранение проблем с высоким уровнем использования ЦП в SQL Server

Применяется к: SQL Server

В этой статье пошаговая процедура диагностики и устранения проблем, вызванных высоким уровнем использования ЦП на компьютере с Microsoft SQL Server.

Хотя существует множество возможных причин высокого ЦП, которые возникают в SQL Server, наиболее распространенными являются следующие:

Вы можете использовать следующие действия для устранения проблем с высоким уровнем использования ЦП в SQL Server.

Шаг 1. Убедитесь, что SQL Server вызывает высокий ЦП

Используйте один из следующих средств, чтобы проверить, действительно ли SQL Server способствует высокому ЦП:

Диспетчер задач (На вкладке Process проверьте, близко ли значение ЦП для SQL Server Windows NT-64 Bit близко к 100 процентам)

Производительность и монитор ресурсов(perfmon)

Для сбора встречных данных в течение 60 сек можно использовать следующий скрипт Powershell:

Если вы заметите, что процент последовательно превышает 90 процентов, это подтвердит, что SQL Server вызывает высокий % User Time ЦП. Однако, если вы заметите, что постоянно превышает 90 процентов, это указывает на то, что антивирусное программное обеспечение или другие драйверы или другой компонент ОС на компьютере способствуют высокому % Privileged time ЦП. Необходимо работать с системным администратором для анализа первопричин этого поведения.

Шаг 2. Определение запросов, способствующих использованию ЦП

Если Sqlservr.exe вызывает высокий ЦП, определите ответственные за это запросы с помощью следующего запроса:

Шаг 3. Обновление статистики

После определения запросов с самым высоким уровнем потребления ЦП обновим статистику для соответствующих таблиц, участвующих в этих запросах.

Если SQL Server по-прежнему используется высокий ЦП, перейдите к следующему шагу.

Шаг 4. Добавление потенциально отсутствующих индексов

Используйте следующий запрос, чтобы определить запросы с высоким уровнем использования ЦП, которые содержат по крайней мере один отсутствующий индекс в плане запросов.

Просмотрите планы выполнения выявленных запросов и настройте запрос, реализуя необходимые изменения. Ниже приводится пример, SQL Server указыв на отсутствующий индекс для запроса. Нажмите правой кнопкой мыши на недостающую часть индекса плана запроса и выберите отсутствующие сведения об индексе, чтобы создать индекс в другом окне в SSMS.

Используйте следующий запрос Динамическое представление управления (DMV), чтобы проверить отсутствующие индексы и применить все рекомендуемые индексы с высокими измерениями улучшения.

Шаг 5. Изучение проблем, чувствительных к параметрам

Используйте команду DBCC FREEPROCCACHE, чтобы проверить, устранена ли проблема с высоким уровнем использования ЦП.

Если проблема еще существует, вы можете добавить подсказку запроса к каждому из высоких запросов ЦП, которые определены RECOMPILE в шаге 2.

Если проблема устранена, это указывает на проблему, чувствительную к параметрам (PSP, ака «проблема нюхания параметров»). Чтобы устранить проблемы, чувствительные к параметрам, используйте следующие методы. Каждый метод имеет связанные компромиссы и недостатки.

Используйте подсказку запроса RECOMPILE при каждом выполнении запроса. Это обходное решение балансирует время компиляции и увеличивает ЦП для улучшения качества планирования. Вот пример того, как это можно применить к запросу.

Чтобы переопредить фактическое значение параметра с типичным значением параметра, которое создает план, достаточно хороший для большинства возможностей параметра, используйте подсказку параметра (OPTIMIZE FOR. ). Этот параметр требует полного понимания оптимальных значений параметров и связанных с ними характеристик плана. Вот пример использования этого подсказки в запросе.

Чтобы переопредить фактическое значение параметра со средним вектором плотности, используйте подсказку параметра (OPTIMIZE FOR UNKNOWN). Это также можно сделать, захватив входящие значения параметров в локальных переменных, а затем используя локальные переменные в предикатах, а не используя сами параметры. Для этого исправления средняя плотность должна быть достаточно высокой.

Используйте подсказку DISABLE_PARAMETER_SNIFFING запроса, чтобы полностью отключить нюхание параметров. Вот пример использования его в запросе:

Используйте подсказку запроса KEEPFIXED PLAN, чтобы предотвратить перекомпилирование в кэше. Этот обход предполагает, что общий план «достаточно хорош» — это тот, который уже находится в кэше. Вы также можете отключить автоматические обновления статистики, чтобы уменьшить вероятность того, что хороший план будет выселен и будет составлен новый плохой план.

Использование команды DBCC FREEPROCCACHE является временным решением до тех пор, пока код приложения не будет исправлен. Вы можете использовать DBCC FREEPROCCACHE (plan_handle) команду, чтобы удалить только план, который вызывает проблему. Например, чтобы найти планы запросов, ссылаясь на таблицу Person.Person в AdventureWorks, этот запрос можно использовать для поиска ручки запроса. Затем можно освободить определенный план запроса из кэша с помощью выпускаемого во втором столбце DBCC FREEPROCCACHE (plan_handle) результата запроса.

Шаг 6. Отключить тяжелую трассировку

Проверьте, SQL трассировка трассировки или XEvent, которая влияет SQL Server производительности и вызывает высокое использование ЦП. Например, события SQL аудита, события вызывают высокие XML-планы, события уровня событий заявления, операции входа и входа, блокировки и ожидания.

Запустите следующие запросы, чтобы определить активные следы XEvent или Server:

Шаг 7. Исправление SOS_CACHESTORE раздора в spinlock

Шаг 8. Настройка виртуальной машины

Если вы используете виртуальную машину, убедитесь, что вы не перенастроили процессоры и они настроены правильно. Дополнительные сведения см. в выпуске Устранение неполадок с производительностью виртуальных машин ESX/ESXi (2001003).

Шаг 9. Масштабировать SQL Server

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

Источник

SQL Server сильно грузит процессор. Проблемы с CPU. Оптимизация запросов SQL по процессору

Если это значение постонно высокое, то значит где-то идет утечка CPU.

В этом руководстве мы собрали различные советы как решать подобную проблему

Поиск проблемных мест в SQL Server по CPU

1. Cмотрим счетчики perfmon

Определяем проблема в Kernel или User запросах.

В perfmon смотрим следующие параметры:

Если это значение % Privileged Time / No of logical cpus больше 30%, то скорее всего дело в системных настройках, возможно антивирус.

2. Ищем проблемные процессы

По spid можно найти этот запрос:

Альтернативно вы можете посмотреть последний запрос, выполняющийся в рамках этого spID:

3. Выявление проблем через спец запросы SQL

Также попробуйте выполнить следующие запросы для поиска проблемных мест по CPU

Еще один скрипт для поиска проблемных запросов по CPU:

Для найденных элементов можно удалить план в кеше (подставив sql_handle):

Еще 1 запрос на поиск проблем по CPU:

Также посмотрите правой кнопкой на Сервере > reports> Standard reports > Top CPU queries.

4. Анализ найденных проблемных запросов

В найденных запросах посмотрите execution plan и посмотрите где наибольший cost.

Источники и что почитать по теме утечек CPU

Альтернативная документация по поиску CPU проблем SQL Server

Что проверить в первую очередь:

Общие рекомендации

настройка кэширования записи на диск

настройка настроены параметры параллелизма (cost threshold for parallelism, max degree of parallelism)

настройка Hyper-Threading.

«Стандартные отчеты» в пользовательском интерфейсе Management Studio

SQL Server Management Studio предоставляет минимальный необходимый набор стандартных отчетов для получения информации в режиме пользовательского интерфейса.

Доступ к этим отчетам может быть выполнен через «Обозреватель объектов» (Object explorer) → Правый клик мыши по базе данных → «Отчеты» (Reports) → «Стандартный отчет» (Standard reports)

Перечень «Стандартные отчеты»:

Перечень «Стандартные отчеты»:

Открыть монитор активности CTRL+ALT+A или SSMS стандарт. панель инструментов значок.

Монитор активности SQL Server 2008 объединяет данные о процессах, предоставляя наглядную информацию по выполняющимся и недавно выполнявшимся процессам.

Монитор активности предлагает администратору раздел обзора, внешне похожий на Диспетчер задач Windows, а также компоненты детального просмотра отдельных процессов, ожидания ресурсов, ввода-вывода в файлы данных и последних ресурсоемких запросов.

Для наблюдения за SQL Server есть интересный пакет отчетов Reporting Services, называется он SQL Server Performance Dashboard Reports.

The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio.

Вопрос – используется ли Reporting Services?

Мониторинг (платный)

(платный) от разных компаний:

Idera — SQL Diagnostic Manager

Red-Gate — SQL Monitor

ApexSQL — ApexSQL Monitor

Quest — Spotlight on SQL Server Enterprise

SentryOne — SQL centry

Источник

KB3195888 — FIX: высокая загрузка ЦП приводит к проблемам с производительностью в SQL Server 2016 и 2017

Проблемы

Причина

Эта проблема возникает из-за большого количества конфликтов блокировки в системе с высокой степенью параллельности. В этих операционных системах множественные потоки ЦП конкурируют за ресурсы и провращаются в течение длительного промежутка времени, пока они работают в цикле, чтобы определить, доступен ли ресурс, а не немедленное выдаче. Если несколько потоков ЦП провращаются (цикл while ) для ресурса, а не выдаются, это приводит к проблемам с использованием ЦП и производительности.

Решение

Эта проблема исправлена в перечисленных ниже накопительных обновлениях для SQL Server.

Примечание. После установки накопительного обновления 2 для SQL Server 2016 (CU2) имена SECURITY_CACHE и CMED_HASH_SET заменяются на LOCK_RW_SECURITY_CACHE и LOCK_RW_CMED_HASH_SETсоответственно. После применения CU2 значения отображаются следующим образом.

Все новые накопительные обновления для SQL Server содержат все исправления и все исправления для системы безопасности, которые были включены в предыдущий накопительный пакет обновления. Ознакомьтесь с самыми последними накопительными обновлениями для SQL Server.

Корпорация Майкрософт подтверждает наличие этой проблемы в своих продуктах, которые перечислены в разделе «Применяется к».

Источник

При запуске запросов в SQL Server

В этой статье вы можете решить проблему с высоким уровнем использования ЦП при запуске запросов в SQL Server.

Применяется к: SQL Server
Исходный номер КБ: 2009160

Симптомы

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

Вы также можете испытывать другие побочные эффекты, такие как условия OOM, давление памяти для выселения кэша плана или RESOURCE_SEMAPHORE непредвиденные ожидания.

Кроме того, вы можете заметить, что планы запросов для запросов, потребляющий большое количество ЦП или воспоминаний, имеют атрибут OPTIMIZED для вложенных циклов присоединиться к оператору, за набором True.

Причина

Эта проблема возникает в некоторых случаях, SQL Server процессор запроса вводит операцию сортировки для оптимизации, хотя она и не требуется. Эта операция называется Оптимизированные вложенные циклы или сортировка пакетов.

В этих случаях план затрагивает только меньшее количество строк, и затраты на установку для операции сортировки могут перевесить ее преимущества. Поэтому это приводит к низкой производительности.

Решение

Чтобы устранить проблему, используйте флаг трассировки 2340 для отключения оптимизации. Кроме того, чтобы отключить оптимизацию на уровне запроса, включив следующий подсказку запроса:

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

Источник