Ms sql server грузит диск на 100
This article provides procedures to diagnose and fix issues that are caused by high CPU usage on a computer that's running Microsoft SQL Server. Although there are many possible causes of high CPU usage that occur in SQL Server, the following ones are the most common causes:
- High logical reads that are caused by table or index scans because of the following conditions:
- Out-of-date statistics
- Missing indexes
- Poorly designed queries
You can use the following steps to troubleshoot high-CPU-usage issues in SQL Server.
Шаг 2. Определение запросов, влияющих на использование ЦП
Sqlservr.exe Если этот процесс приводит к высокой загрузке ЦП, наиболее распространенной причиной являются запросы SQL Server, которые выполняют сканирование таблицы или индекса, за которыми следует сортировка, хэш-операции и циклы (оператор вложенного цикла или WHILE (T-SQL)). Чтобы получить представление о том, какой объем ЦП в настоящее время используются запросами из общей емкости ЦП, выполните следующую инструкцию:
Чтобы определить запросы, отвечающие за высокую активность ЦП, выполните следующую инструкцию:
Если в настоящее время запросы не управляют ЦП, но произошла высокая загрузка ЦП, можно выполнить следующую инструкцию, чтобы найти исторические запросы с привязкой к ЦП:
Шаг 6. Изучение и устранение проблем с SARGability
Предикат в запросе считается SARGable (поиск с поддержкой ARGument), если SQL Server может использовать поиск индекса для ускорения выполнения запроса. Многие макеты запросов препятствуют sarGability и приводят к сканированию таблиц или индексов и высокой загрузке ЦП. Рассмотрим следующий запрос к базе данных AdventureWorks ProductNumber SUBSTRING() , где необходимо извлечь каждый из них и применить к ней функцию перед сравнением со строковым литеральным значением. Как видите, сначала необходимо получить все строки таблицы, а затем применить функцию, прежде чем можно будет выполнить сравнение. Выборка всех строк из таблицы означает сканирование таблицы или индекса, что приводит к более высокому использованию ЦП.
Применение любой функции или вычислений к столбцам в предикате поиска обычно делает запрос неуязвимым и приводит к более высокому потреблению ЦП. Решения обычно включают в себя перезапись запросов творческим способом, чтобы сделать SARGable. Возможным решением для этого примера является перезапись, при которой функция удаляется из предиката запроса, выполняется поиск в другом столбце и выполняются те же результаты:
Вот еще один пример, когда менеджеру по продажам может потребоваться предоставить 10 % комиссионных продаж по крупным заказам и узнать, какие заказы будут иметь комиссию больше 300 долларов США. Ниже приведен логический, но неуязвимый способ.
Ниже приведено возможное менее интуитивно понятное, но доступное для SARGable перезапись запроса, при котором вычисление перемещается на другую сторону предиката.
SARGability применяется не только к WHERE предложениям, но и к JOINs предложениям и HAVING предложениям GROUP BY ORDER BY . Частые случаи предотвращения SARGability CONVERT()``CAST() в запросах включают в себя функции, WHERE ISNULL()``COALESCE() JOIN используемые в предложениях или в предложениях, которые приводят к сканированию столбцов. В вариантах преобразования типов данных ( CONVERT или CAST ) решением может быть сравнение тех же типов данных. Ниже приведен пример, в котором T1.ProdID столбец INT явно преобразуется в тип данных в . JOIN Преобразование не позволяет использовать индекс в столбце соединения. Та же проблема возникает при неявном преобразовании, когда типы данных отличаются и SQL Server один из них для выполнения соединения.
Чтобы избежать сканирования T1 таблицы, ProdID можно изменить базовый тип данных столбца после надлежащего планирования и проектирования, а затем присоединить два столбца без использования функции преобразования ON T1.ProdID = T2.ProductID .
Другим решением является создание вычисляемого столбца T1 CONVERT() , в котором используется та же функция, а затем создание индекса на нем. Это позволит оптимизатору запросов использовать этот индекс без необходимости изменять запрос.
В некоторых случаях запросы нельзя легко переписать, чтобы обеспечить возможность SARGability. В таких случаях вы можете проверить, может ли вычисляемый столбец с индексом помочь или сохранить запрос так, как это было, с учетом того, что он может привести к более высоким сценариям ЦП.
Просмотр блокировок в SQL Server
После того как мы убедились, что серверу хватает ресурсов, можно переходить к просмотру блокировок.
Блокировки можно посмотреть через Activity Monitor в SSMS, но мы воспользуемся T-SQL, так как этот вариант более удобен и нагляден. Выполняем запрос:
Этот запрос возвращает список блокировок в виде дерева. Это удобно в работе, так как обычно, если возникает одна блокировка, она провоцирует за собой другие. Аналогично в Activity Monitor или в выводе sp_who2 можно увидеть поле “Blocked By”.
Если запрос ничего не вернул, то блокировок нет.
Если запрос вернул какие-то данные, то нужно проанализировать цепочку.
HEAD значит что этот запрос является причиной всех остальных блокировок ниже по дереву. 64 – это идентификатор процесса (SPID). После этого пишется тело запроса, который вызвал блокировку. Если у вас хватает ресурсов сервера, то скорее всего дело в самом запросе и во взаимном обращении к каким-то объектам. Для того чтобы сказать точнее, нужно анализировать конкретный запрос, который вызвал блокировку.
Причина
Эта проблема возникает из-за большого количества конфликтов блокировки в системе с высокой степенью параллельности. В этих операционных системах множественные потоки ЦП конкурируют за ресурсы и провращаются в течение длительного промежутка времени, пока они работают в цикле, чтобы определить, доступен ли ресурс, а не немедленное выдаче. Если несколько потоков ЦП провращаются (цикл while ) для ресурса, а не выдаются, это приводит к проблемам с использованием ЦП и производительности.
Шаг 8. Устранение SOS_CACHESTORE спин-блокировки
Если в экземпляре SQL Server SOS_CACHESTORE происходит интенсивное состязание за спин-блокировку или вы заметили, что планы запросов часто удаляются в незапланированных рабочих нагрузках запросов, T174 DBCC TRACEON (174, -1) просмотрите следующую статью и включите флаг трассировки с помощью команды:
Исправление. SOS_CACHESTORE спин-блокировок в кэше нерегламентированного SQL Server планов приводит к высокой загрузке ЦП в SQL Server.
Если условие высокой загрузки ЦП разрешается с помощью, T174 включите его в качестве параметра запуска с помощью диспетчер конфигурации SQL Server.
Высокая загрузка ЦП может возникать из-за конфликтов спин-блокировок во многих других типах спин-блокировок, SOS_CACHESTORE но обычно сообщается о них. Дополнительные сведения о спин-блокировках см. в разделе "Диагностика и устранение конфликтов спин-блокировок на SQL Server
Step 4: Add missing indexes
Run the following query to identify queries that cause high CPU usage and that contain at least one missing index in the query plan:
Review the execution plans for the queries that are identified, and tune the query by making the required changes. The following screenshot shows an example in which SQL Server will point out a missing index for your query. Right-click the Missing index portion of the query plan, and then select Missing Index Details to create the index in another window in SQL Server Management Studio.
Use the following query to check for missing indexes and apply any recommended indexes that have high improvement measure values. Start with the top 5 or 10 recommendations from the output that have the highest improvement_measure value. Those indexes have the most significant positive effect on performance. Decide whether you want to apply these indexes and make sure that performance testing is done for the application. Then, continue to apply missing-index recommendations until you achieve the desired application performance results. For more information on this topic, see Tune nonclustered indexes with missing index suggestions.
Инструменты для диагностики SQL Server
Если вы правильно диагностировали проблему, то половина работы уже сделана. Рассмотрим какие инструменты обычно используются системным администратором для диагностики различных проблем в SQL Server:
Step 8: Fix SOS_CACHESTORE spinlock contention
If your SQL Server instance experiences heavy SOS_CACHESTORE spinlock contention or you notice that your query plans are often removed on unplanned query workloads, review the following article and enable trace flag T174 by using the DBCC TRACEON (174, -1) command:
If the high-CPU condition is resolved by using T174 , enable it as a startup parameter by using SQL Server Configuration Manager.
High CPU may result from spinlock contention on many other spinlock types, but SOS_CACHESTORE is a commonly-reported one. For more information on spinlocks, see Diagnose and resolve spinlock contention on SQL Server
Step 2: Identify queries contributing to CPU usage
If the Sqlservr.exe process is causing high CPU usage, by far, the most common reason is SQL Server queries that perform table or index scans, followed by sort, hash operations and loops (nested loop operator or WHILE (T-SQL)). To get an idea of how much CPU the queries are currently using, out of overall CPU capacity, run the following statement:
To identify the queries that are responsible for high-CPU activity currently, run the following statement:
If queries aren't driving the CPU at this moment, but high CPU has happened, you can run the following statement to look for historical CPU-bound queries:
Загрузка процессора в SQL Server
Нагрузку на процессор определить проще, так как это можно сделать в Диспетчере задач. Чтобы узнать текущую нагрузку на процессор, найдите в Диспетчере задач процесс sqlservr.exe
Если вы хотите узнать нагрузку за прошедшее время, можно воспользоваться запросом:
В результате мы получим поминутную статистику использования процессора.
Шаг 3. Обновление статистики
Определив запросы с наибольшим потреблением ЦП , обновите статистику таблиц, используемых этими запросами. Системную хранимую sp_updatestats процедуру можно использовать для обновления статистики всех определяемых пользователем и внутренних таблиц в текущей базе данных. Например.
Системная sp_updatestats хранимая процедура выполняется UPDATE STATISTICS для всех пользовательских и внутренних таблиц в текущей базе данных. Для регулярного обслуживания убедитесь, что регулярное планирование обслуживания обновляет статистику. Используйте такие решения, как " Дефрагментация адаптивного индекса", для автоматического управления дефрагментативной индекса и обновлениями статистики для одной или нескольких баз данных. Эта процедура автоматически выбирает, следует ли перестраивать или реорганизовать индекс в соответствии с уровнем фрагментации, помимо других параметров, и обновлять статистику с помощью линейного порогового значения.
Дополнительные сведения см sp_updatestats . в sp_updatestats.
Если SQL Server по-прежнему использует чрезмерную емкость ЦП, перейдите к следующему шагу.
Шаг 1. Убедитесь, что SQL Server загрузка ЦП приводит к высокой загрузке ЦП
Используйте одно из следующих средств, чтобы проверить, действительно ли SQL Server процесс влияет на высокую загрузку ЦП:
Диспетчер задач. На вкладке " Процесс" проверьте, близко ли значение столбца ЦП для SQL Server Windows NT-64 бита к 100 процентам.
Монитор производительности и ресурсов (perfmon)
- Счетчик: Process/%User Time , % Privileged Time
- Экземпляр: sqlservr
Для сбора данных счетчиков в течение 60 секунд можно использовать следующий сценарий PowerShell:
Если % User Time производительность постоянно превышает 90 процентов, SQL Server процесс приводит к высокой загрузке ЦП. Однако, если % Privileged time значение постоянно превышает 90 процентов, антивирусная программа, другие драйверы или другой компонент ОС на компьютере способствуют высокой загрузке ЦП. Чтобы проанализировать первопричину этого поведения, обратитесь к системному администратору.
Шаг 4. Добавление отсутствующих индексов
Выполните следующий запрос, чтобы определить запросы, которые приводят к высокой загрузке ЦП и содержат по крайней мере один отсутствующий индекс в плане запроса:
Просмотрите планы выполнения для идентифицированных запросов и настройте запрос, внося необходимые изменения. На следующем снимке экрана показан пример, в котором SQL Server будет указывать на отсутствующий индекс для запроса. Щелкните правой кнопкой мыши часть "Отсутствующий индекс" в плане запроса, а затем выберите "Отсутствующие сведения об индексе", чтобы создать индекс в другом окне SQL Server Management Studio.
Используйте следующий запрос, чтобы проверить отсутствие индексов и применить все рекомендуемые индексы с высокими значениями мер улучшения. Начните с 5 или 10 рекомендаций из выходных данных с наибольшим improvement_measure значения. Эти индексы имеют наиболее значительное положительное влияние на производительность. Решите, следует ли применять эти индексы, и убедитесь, что для приложения выполнено тестирование производительности. Затем продолжайте применять рекомендации по отсутствующим индексам, пока не дойдет желаемых результатов производительности приложения. Дополнительные сведения об этом разделе см. в разделе "Настройка некластеризованных индексов с отсутствующим предложением индекса".
Шаг 5. Изучение и устранение проблем, чувствительных к параметрам
Используйте команду DBCC FREEPROCCACHE , чтобы проверить, устранена ли проблема с высокой загрузкой ЦП.
Если проблема по-прежнему существует, RECOMPILE можно добавить указание запроса к каждому из запросов с высокой загрузкой ЦП, которые определены на шаге 2.
Если проблема устранена, это указывает на проблему с учетом параметров (PSP, также известная как "проблема с сканированием параметров"). Чтобы устранить проблемы, чувствительные к параметрам, используйте следующие методы. Каждый метод имеет связанные компромиссы и недостатки.
Используйте указание запроса RECOMPILE для каждого выполнения запроса. Это указание помогает сбалансировать небольшое увеличение загрузки ЦП компиляции с более оптимальной производительностью при каждом выполнении запроса. Дополнительные сведения см. в разделах "Параметры" и "Повторное использование плана выполнения", " Конфиденциальность параметров" и "Указание запроса RECOMPILE".
Ниже приведен пример применения этого указания к запросу.
Используйте указание запроса OPTIMIZE FOR , чтобы переопределить фактическое значение параметра, используя типичное значение параметра, достаточное для большинства возможных значений параметров. Этот параметр требует полного понимания оптимальных значений параметров и связанных характеристик плана. Ниже приведен пример использования этого указания в запросе.
Используйте указание запроса OPTIMIZE FOR UNKNOWN , чтобы переопределить фактическое значение параметра средой вектора плотности. Это также можно сделать, захватив значения входящих параметров в локальных переменных, а затем используя локальные переменные в предикатах вместо использования самих параметров. Для этого исправления средняя плотность должна быть достаточно высокой.
Используйте DISABLE_PARAMETER_SNIFFING запроса, чтобы полностью отключить сканирование параметров. Ниже приведен пример использования в запросе.
Используйте указание запроса KEEPFIXED PLAN , чтобы предотвратить повторную компиляцию в кэше. Это решение предполагает, что общий план "достаточно хороший" — это план, который уже находится в кэше. Вы также можете отключить автоматическое обновление статистики, чтобы снизить вероятность вытеснения хорошего плана и компиляции нового неверного плана.
Используйте команду DBCC FREEPROCCACHE в качестве временного решения, пока код приложения не будет исправлен. С помощью этой команды DBCC FREEPROCCACHE (plan_handle) можно удалить только план, который вызывает проблему. Например, чтобы найти планы запросов Person.Person , ссылающееся на таблицу в AdventureWorks, этот запрос можно использовать для поиска дескриптора запроса. Затем можно освободить определенный план запроса из кэша DBCC FREEPROCCACHE (plan_handle) , используя созданный во втором столбце результатов запроса.
Шаг 10. Увеличение масштаба SQL Server
Если отдельные экземпляры запросов используют небольшую емкость ЦП, но общая рабочая нагрузка всех запросов вместе приводит к высокой загрузке ЦП, рассмотрите возможность масштабирования компьютера путем добавления дополнительных ЦП. Используйте следующий запрос, чтобы найти количество запросов, превышающих определенное пороговое значение среднего и максимального потребления ЦП на выполнение и которые выполняются много раз в системе (убедитесь, что значения двух переменных соответствуют вашей среде):
SQL Server 2016 Developer SQL Server 2016 Enterprise SQL Server 2016 Enterprise Core SQL Server 2016 Express SQL Server 2016 Standard SQL Server 2016 Web SQL Server 2017 Developer on Windows SQL Server 2017 Enterprise Core on Windows SQL Server 2017 Enterprise on Windows SQL Server 2017 Standard on Windows Еще. Меньше
Step 1: Verify that SQL Server is causing high CPU usage
Use one of the following tools to check whether the SQL Server process is actually contributing to high CPU usage:
Task Manager: On the Process tab, check whether the CPU column value for SQL Server Windows NT-64 Bit is close to 100 percent.
Performance and Resource Monitor (perfmon)
- Counter: Process/%User Time , % Privileged Time
- Instance: sqlservr
You can use the following PowerShell script to collect the counter data over a 60-second span:
If % User Time is consistently greater than 90 percent, the SQL Server process is causing high CPU usage. However, if % Privileged time is consistently greater than 90 percent, your antivirus software, other drivers, or another OS component on the computer is contributing to high CPU usage. You should work with your system administrator to analyze the root cause of this behavior.
Специальные предложения
(4) AlexO, vasyak319
Для невежды нет ничего лучше молчания, но если бы он знал,
что для него лучше всего, — не был бы он невеждой.Как правило, повышенную нагрузку на диски можно определить различными способами. Основной из них – это получение счетчика «Средней длины очереди к диску»
Сразу неверное предположение, отсюда - неверные выводы.
И "решения", где-то правильные, где-то - бессмысленные, но точно не связанные с проблемой "повышенная нагрузка на диски", или связанная, но не так, как предположил автор.
Дальше статью можно и не читать, но рассмотрим подробнее.
Очередь к диску сама по себе - это не "нагрузка на диск", а следствие интенсивного использования диска, т.е. следствие повышенной нагрузки .
Т.е. это очень важный показатель оценки работы дисковой подсимтемы, но не он определят проблему, и бороться надо не с очередью к диску, а с причиной - недостаточной (или уже несоответствующей) скоростью записи-чтения системы HDD (что наиболее эффективно), либо, с другой стороны, заставлять приложения меньше использовать HDD, и больше - ОЗУ.
Вы, собственно, это и рассматриваете далее, но из-за неверной предпосылки - рассматриваете не в том контексте и верные, и неверные подходы.Как правило, повышенную нагрузку на диски можно определить различными способами. Основной из них – это получение счетчика «Средней длины очереди к диску»
Сразу неверное предположение, отсюда - неверные выводы.
И "решения", где-то правильные, где-то - бессмысленные, но точно не связанные с проблемой "повышенная нагрузка на диски", или связанная, но не так, как предположил автор.
Дальше статью можно и вовсе не читать, но все же рассмотрим подробнее, даже если автор против этого.
Очередь к диску сама по себе - это не "нагрузка на диск", а следствие интенсивного использования диска, т.е. следствие повышенной нагрузки .
Т.е. это очень важный показатель оценки работы дисковой подсимтемы, но не он определяет проблему, и бороться надо не с очередью к диску, а с причиной - недостаточной (или уже несоответствующей) скоростью записи-чтения системы HDD (что наиболее эффективно), либо, с другой стороны, заставлять приложения меньше использовать HDD, и больше - ОЗУ.
Вы, собственно, это и рассматриваете далее, но из-за неверной предпосылки - рассматриваете не в том контексте и верные, и неверные подходы.
Кратко:Понятно, что если данные в кэше (будем считать, что в ОЗУ, т.к. кэш может быть и на диске - это кто как реализует его хранение) - то чем чаще используется кэш, тем менее используются диски. Никаких графиков тут и не нужно - все эти графики "зависимости очереди от . страниц памяти. " - не более, чем красивые картинки, т.к. данная "проблема" - всего лишь отражение работы конкретного физического механизма: данные в кэше - используется кэш, нет данных - происходит обращение к диску.
И никакие "найти тяжелые неоптимальные запросы" (и остальные предложенные "приемы устранения проблемы") тут вообще роли не играют: если требуются данные, которых нет в кэше - вот хоть какой запрос будет легким, но все равно будет обращение к БД (диску).
Да, можно увеличить размер используемого кэша ОЗУ (всякие там "ключи использвоания памяти выше 2 ГБ" и т.д.), но это опять же "решения на час" - какого угодно размера кэш забьется, но если не будет необходимых данных - будет обращение к диску.2. Нагрузка на диски, обусловленная свопированием памяти на диски вследствие нехватки свободной памяти.
Это тоже понятно, что если своп (виртуальный кэш, используемый приложением) находится на диске - будет нагружаться диск, в ОЗУ - диск будет разгружен. Это все и без графиков понятно всем, из одного предложения.
Другое дело, что можно регулировать объем того и другого для приложений, но об этом - как раз ни слова (именно из-за неверной предпосылки - ушел в "объяснениях" совсем в другую сторону).А это вообще откровенная профанация (т.е. преднамеренное запутывание).
Не хочешь, чтобы "внутренние механизмы SQL" создавали нагрузку - отключи SQL. И никакой нагрузки не будет вовсе.
Регламентные операции - будь то резервное копирование, логирование и прочие, определяются не мифической "нагрузкой на диск из-за внутренних механизмов", а необходимостью: не нужно - отключи. Нужно, но тормозит - совершенствуй дисковую подсистему. Больше вариантов нет.
Log Shipping - это также регламентная операция передачи журнала транзакций, и суть её не в "создании нагрузки", а в резервировании лога транзакций: не нужно - отключи.
Т.е. по третьему пункту: автор, SQL слишком "тяжел" для твоих серверов - выключи его, переходи на файловую 1С.
Но не сваливай на регламент СУБД проблемы с дисковой подсистемой.В этой статье приведены процедуры диагностики и устранения проблем, вызванных высокой загрузкой ЦП на компьютере, на котором выполняется Microsoft SQL Server. Хотя существует множество возможных причин высокой загрузки ЦП, которые возникают в SQL Server, наиболее распространенными причинами являются следующие:
- Высокие логические операции чтения, вызванные сканированием таблицы или индекса из-за следующих условий:
- Устарелая статистика
- Отсутствующие индексы
- Плохо спроектированные запросы
Чтобы устранить проблемы с высокой загрузкой ЦП в SQL Server, выполните следующие действия.
Шаг 9. Настройка виртуальной машины
Если вы используете виртуальную машину, убедитесь, что вы не перепроизводите ЦП и что они настроены правильно. Дополнительные сведения см. в статье об устранении неполадок с производительностью виртуальных машин ESX и ESXi (2001003).
Анализ использования оперативной памяти SQL Server
Для начала нужно определить сколько памяти доступно SQL Server. Для этого запустите SSMS (SQL Server Management Studio), зайдите на сервер и зайдите в свойства сервера (ПКМ по названию сервера в Обозревателе объектов).
Сам по себе доступный объём RAM вам ничего не скажет. Нужно сравнить это число с используемой памятью в Диспетчере Задач и самим движком SQL Server с помощью DMV.
В Диспетчере задач, во вкладке Подробности, найдите sqlservr.exe и посмотрите сколько оперативной памяти использует этот процесс.
- Если на сервере, например, 128 GB оперативной памяти, а процесс sqlservr.exe использует 60 GB и ограничений по RAM у SQL Server нет, то оперативной памяти вам хватает.
- Если SQL Server использует 80-90% RAM от заданной или максимальной, то в таком случае нужно смотреть DMV. Имейте в виду, что sqlservr.exe не сможет использовать всю оперативную память. Если на сервере 128 GB, то sqlservr.exe может использовать только 80-90% (100-110 GB), так как остальная память резервируется для операционной системы.
Имейте в виду, что процесс SQL Server’a не отдаёт оперативную память обратно в систему. Например, ваш SQL Server обычно использует 20 GB памяти, но при месячном отчете он увеличивает потребление до 100 GB, и даже когда вычисление отчета закончится и сервер будет работать в прежнем режиме, процесс SQL Server’a всё равно будет использовать 100 GB до перезагрузки службы.
Даже если вы уверены, что оперативной памяти серверу хватает, не будет лишним точно знать объём потребляемой RAM.
Узнать реальное использование RAM можно с помощью Dynamic Management Views. DMV это административные вьюверы (представления). С помощью DMV можно диагностировать практически любую проблему в SQL Server.
Посмотрим sys.dm_os_sys_memory, для удобства используем запрос:
Рассмотрим каждый выводимый параметр:
Все эти данные полезны, если вы хотите точно определить сколько ваш SQL Server потребляет RAM. Чаще всего это используют, если есть подозрения что для экземпляра выделено слишком много оперативной памяти.
Если Вам нужно убедиться, что серверу хватает RAM, вы можете смотреть только на поля system_low_memory_signal_state, system_high_memory_signal_state и system_memory_state_desc. Если system_low_memory_signal_state = 1, то серверу явно не хватает оперативной памяти.
Анализ нагрузки на диск SQL Server
Посмотрим на загрузку дисков в операционной системе. Для этого запустите resmon.exe.
Нам нужна вкладка Disk. В секции Disk Activity отображаются файлы, к которым идёт обращение, и их скорость read/write на текущий момент. Отфильтруйте эту секцию по Total (кликните на Total). На самом верху будут файлы, которые на данный момент максимально используют диск. В случае с SQL Server это может быть полезно чтобы определить какая база больше всего нагружает диск на текущий момент.
В секции Storage отображаются все диски в системе. В этой секции нам нужны 2 параметра – Active Time и Disk Queue. Active Time в процентах отображает нагрузку на диск, то есть если вы видите на диске C:\ Active Time равный 90, это значит что ресурс чтения/записи диска на текущий момент используется на 90%. Столбец Disk Queue отображает очередь обращений к диску, и если значение очереди не равно нулю, то диск загружен на 100% и не справляется с нагрузкой. Так же если Active Time близок к 100, то диск используется практически на пределе своих возможностей по скорости.
Обнаружение и решение проблем с производительностью SQL Server
Самой распространенной проблемой с которой сталкивается системный администратор, работающий с SQL Server, это жалобы пользователей на производительность запросов и самого сервера: “тормозит”, “долго выполняется запрос“, и так далее.
Прежде всего нужно убедиться, что серверу хватает ресурсов. Рассмотрим, как в SQL Server быстро проанализировать использование памяти, CPU, дисков и наличие блокировок.
Политики SQL Server
Даже когда у вас всё работает хорошо и жалоб нет, на самом деле может быть много проблем, которые всплывут позже. Для этого в SQL Server есть политики.
Политика в SQL Server это, грубо говоря, проверка правила на соответствие заданному значению. Например, с помощью политик вы можете убедиться, что на всех базах на сервере выключен Auto Shrink. Рассмотрим пример импорта и выполнения политики
В SSMS, подключитесь к серверу, на котором хотите выполнять политики (Management -> раздел Policy Management).
Импортируем файл Database Auto Shrink.xml. Жмём Evaluate
На экземпляре node1 две базы данных, test1 и test2. На test2 включен autoshrink. Посмотрим детали.
Политика определила включенный параметр AutoShrink, в описании обычно пишется объяснения к правилам. В данном случае дается объяснение почему auto shrink лучше отключать.
Политики могут выполняться либо по расписанию, либо по требованию (разово). Результаты выполнения политики можно посмотреть в журнале политик.
При установке SQL Server нужно выбирать только используемые компоненты СУБД, и указывать настройки в соответствии с конфигурацией “железа” вашего сервера. Всегда следите чтобы серверу хватало ресурсов, и чтобы на сервере не было блокировок
Самым мощным инструментом для диагностики SQL Server является T-SQL и DMV. Так же рекомендуется построить круглосуточный мониторинг над SQL Server и над обслуживающей его инфраструктурой для обнаружения всех возможных проблем.
Step 9: Configure your virtual machine
If you're using a virtual machine, ensure that you aren't overprovisioning CPUs and that they're configured correctly. For more information, see Troubleshooting ESX/ESXi virtual machine performance issues (2001003).
Step 10: Scale up SQL Server
If individual query instances are using little CPU capacity, but the overall workload of all queries together causes high CPU consumption, consider scaling up your computer by adding more CPUs. Use the following query to find the number of queries that have exceeded a certain threshold of average and maximum CPU consumption per execution and have run many times on the system (make sure that you modify the values of the two variables to match your environment):
С проблемой повышенной нагрузки на диски (дисковые хранилища и массивы, далее просто диски), сталкиваются почти все администраторы и специалисты технической поддержки при эксплуатации средних и крупных информационных систем на базе SQL Server (от 50 активных пользовательских сессий). Но всегда ли правильно идет интерпретация проблемы, попробуем разобраться на нескольких практических примерах.
Повышенная нагрузка на диски сервера баз данных
С проблемой повышенной нагрузки на диски (дисковые хранилища и массивы, далее просто диски), сталкиваются почти все администраторы и специалисты технической поддержки при эксплуатации средних и крупных информационных систем на базе SQL Server (от 50 активных пользовательских сессий). Но всегда ли правильно идет интерпретация проблемы, попробуем разобраться на нескольких практических примерах.
Как правило, повышенную нагрузку на диски можно определить различными способами. Основной из них – это получение счетчика «Средней длины очереди к диску»:
Рис.1. Средняя длина очереди к диску для чтения и записи
На рис. 1 можно наблюдать типичную ситуацию с повышенной очередью к диску, «на пальцах» этот параметр можно объяснить, как среднее количество пакетных заданий для физического диска в очереди к выполнению. В моменты повышенной очереди к диску возникают задержки на всех, даже минимальных операциях с диском, что в ряде случаев приводит к общему падению производительности. Следует учитывать возможности каждого диска по параллельной обработке, так как от этого зависит критичность проблемы. В случае, если средняя очередь к диску больше, чем возможности диска, то проблема стоит очень остро и повлияет в общем на скорость всех операций и информационной системе. Если же средняя очередь к диску больше 1, но меньше возможностей диска, то диск справляется с нагрузкой за счет своих ресурсов, но это не значит, что проблемы не существует вообще, – повышенная нагрузка на диск может привести к уменьшению срока жизни механизмов диска.
Рассмотрим несколько основных причин повышенной нагрузки на диски для систем на базе MS SQL Server.
- Нагрузка на диски обусловлена быстрым вытеснением данных из кеша SQL Server.
Рис.2. Демонстрация вытеснения данных из кеша SQL Server
На рисунке 2 показаны 3 условных этапа различной нагрузки на диск. На этапе 1 и этапе 3 – очереди к диску были минимальны. Почему же на этапе 2 очередь резко возросла и это привело к появлению проблем производительности у пользователей? Ответ на этот вопрос легко найти на втором графике рисунка 2: «Ожидаемый срок жизни страницы памяти», который показывает предполагаемое время нахождения страницы данных в кеше SQL Server. Между двумя этапами видим резкое понижение этого графика со значения 3000 до 200. С точки зрения логики работы SQL Server это означает, что данные будут находится к кеше не 3000 секунд как раньше, а 200 секунд, следовательно, если пользователь запросит данные через 300 секунд, то SQL Server с почти 100% вероятностью не найдет их в оперативной памяти (кеше) и придется выполнять операцию чтения с диска. Этими операциями обеспечивается рост очереди к диску. В течение всего этапа 2 кеш «прогревался» (заполнялся данными) и на этапе 3 нагрузка на диск упала.
Мы определили вид проблемы, теперь рассмотрим варианты решения.
Что надо сделать:
- Найти тяжелые неоптимальные запросы, которые вытеснили данные из кеша SQL Server. Прошу обратить внимание, что это не всегда равносильно поиску длительных запросов, так как зачастую быстрые, но неоптимальные запросы SQL приводят к подобным проблемам.
- Возможно проблема в качестве обслуживания статистик и индексов MS SQL Server.
Что не надо делать:
- Не надо покупать новые диски (дисковые массивы), это не решает проблему, а скорее ее усугубляет.
Для написания материала мы использовали инструмент для мониторинга производительности PerfExpert, позволяющий обеспечить возможность сбора и глубокого анализа данных.
Рассмотрим еще несколько практических ситуаций с повышенной нагрузкой на диск, где причиной являются совершенно различные по природе причины.
2. Нагрузка на диски, обусловленная свопированием памяти на диски вследствие нехватки свободной памяти.
Рис.1. Практический пример повышенной нагрузки на диск
На рисунке 1 показана практическая ситуация на сервере БД SQL Server у клиента в течение 1,5 часов. Как видно по счетчику «Средней длины очереди к диску» диск нагружен и не справляется с количеством обращений к нему.
На рисунке также показаны два других показателя: «Нагрузка CPU», «Свободная оперативная память» для поиска причин торможения диска. Условно делим ситуацию на два этапа: первый этап – очередь к диску практически равна 0 и пользователи работают в обычном режиме, и второй этап – в течение которого очередь к диску поднимается до максимальных значений (342) и пользователи не могут качественно работать. Чем же обусловлена такая нагрузка на диск?
Нагрузка обусловлена процессом свопированием оперативной памяти на диск, при котором при нехватки оперативной памяти некоторые страницы записываются в специальную область на физический диск. При этом скорость работы с такими страницами падает, повышается нагрузка на диск и замедляются все операции в системе.
Показатель «Свободная оперативная память» как раз показывает доступность реальной оперативной памяти для других процессов, а, следовательно, чем его значение больше, тем меньше вероятность свопирования. На рисунке 1 значение свободной оперативной памяти на сервере баз данных постоянно уменьшается до 500 Мб, далее до 200 Мб, это в свою очередь и привело к нагрузке на диск (на этапе 2).
Встает вопрос – а зачем на рисунке 1 мы показали счетчик «Нагрузка CPU»? Все просто, на этапе 1 средняя загрузка CPU была около 50%, на этапе 2 – 40%, при этом в системе работало аналогичное количество пользователей. Такое уменьшение значения говорит о том, что процессор недозагружен и узкое место в производительности сместилось в сторону диска (он не справляется).
Для исправления этой ситуации достаточно правильно распределить потребление оперативной памяти и не допустить уменьшение ее объема до 500Мб (как рекомендация). Неправильным вариантом решения была бы покупка более производительного физического диска или хранилища.
3. Нагрузка на диски, обусловленная внутренними механизмами работы SQL Server.
Рис. 2. Периодическая нагрузка на диск
Как видно из рисунка 2, периодически очередь к диску увеличивается, причем эти «скачки» происходят через одинаковые временные интервалы. Это может говорить о том, что есть периодически повторяемые регламентные операции.
Из нашего опыта это могут быть следующие операции:
- Увеличение размера файлов данных и лога транзакций (особенно если указан фиксированный размер прироста).
- Резервная копия файла данных или журнала транзакций.
Сбор и анализ данных осуществлялся с использованием мониторинга производительности PerfExpert.
Step 5: Investigate and resolve parameter-sensitive issues
Use the DBCC FREEPROCCACHE command to check whether the high-CPU-usage issue is fixed.
If the issue still exists, you can add a RECOMPILE query hint to each of the high-CPU queries that are identified in step 2.
If the issue is fixed, it's an indication of a parameter-sensitive problem (PSP, also known as "parameter sniffing issue"). To mitigate the parameter-sensitive issues, use the following methods. Each method has associated tradeoffs and drawbacks.
Use the RECOMPILE query hint for each query execution. This hint helps balance the slight increase in compilation CPU usage with a more optimal performance for each query execution. For more information, see Parameters and Execution Plan Reuse, Parameter Sensitivity and RECOMPILE query hint.
Here's an example of how you can apply this hint to your query.
Use the OPTIMIZE FOR query hint to override the actual parameter value by using a typical parameter value that's good enough for most parameter value possibilities. This option requires a full understanding of optimal parameter values and associated plan characteristics. Here's an example of how to use this hint in your query.
Use the OPTIMIZE FOR UNKNOWN query hint to override the actual parameter value with the density vector average. You can also do this by capturing the incoming parameter values in local variables, and then using the local variables within the predicates instead of using the parameters themselves. For this fix, the average density must be good enough.
Use the DISABLE_PARAMETER_SNIFFING query hint to disable parameter sniffing completely. Here's an example of how to use it in a query:
Use the KEEPFIXED PLAN query hint to prevent recompilations in cache. This workaround assumes that the "good enough" common plan is the one that's already in cache. You can also disable automatic statistics updates to reduce the chances that the good plan will be evicted and a new bad plan will be compiled.
Use the DBCC FREEPROCCACHE command as a temporary solution until the application code is fixed. You can use the DBCC FREEPROCCACHE (plan_handle) command to remove only the plan that is causing the issue. For example, to find query plans that reference the Person.Person table in AdventureWorks, you can use this query to find the query handle. Then you can release the specific query plan from cache by using the DBCC FREEPROCCACHE (plan_handle) that is produced in the second column of the query results.
Проблемы
При использовании нового экземпляра Microsoft SQL Server 2016 (или 2017) или экземпляра SQL Server 2016 (или 2017), обновленного на основе более ранней версии SQL Server, возникают проблемы с производительностью из-за высокой загрузки ЦП. Кроме того, если вы запрашиваете таблицу sys.dm_os_spinlock_stats , вы увидите высокое значение в столбце счетчики для строк SECURITY_CACHE (кэша безопасности) и CMED_HASH_SET (кэша метаданных) (по сравнению с другими строками в таблице). Например, обратите внимание на следующие значения:
Шаг 7. Отключение интенсивной трассировки
Проверьте наличие SQL трассировки или трассировки XEvent, которая влияет на производительность SQL Server и приводит к высокой загрузке ЦП. Например, использование следующих событий может привести к высокой загрузке ЦП при трассировке SQL Server активности:
- XML-события плана запроса ( query_plan_profile , query_post_compilation_showplan , query_post_execution_plan_profile , query_post_execution_showplan , query_pre_execution_showplan )
- События уровня инструкции ( sql_statement_completed , sql_statement_starting , sp_statement_starting , sp_statement_completed )
- События входа и входа ( login , process_login_finish , login_event , logout )
- События блокировки ( lock_acquired , lock_cancel , lock_released )
- События ожидания ( wait_info , wait_info_external )
- SQL аудита (в зависимости от группы, для SQL Server и действий в этой группе)
Выполните следующие запросы, чтобы определить активные трассировки XEvent или Server:
Step 3: Update statistics
After you identify the queries that have the highest CPU consumption, update statistics of the tables that are used by these queries. You can use the sp_updatestats system stored procedure to update the statistics of all user-defined and internal tables in the current database. For example:
The sp_updatestats system stored procedure runs UPDATE STATISTICS against all user-defined and internal tables in the current database. For regular maintenance, ensure that regularly schedule maintenance is keeping statistics up to date. Use solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, among other parameters, and update statistics with a linear threshold.
For more information about sp_updatestats , see sp_updatestats.
If SQL Server is still using excessive CPU capacity, go to the next step.
Step 6: Investigate and resolve SARGability issues
A predicate in a query is considered SARGable (Search ARGument-able) when SQL Server engine can use an index seek to speed up the execution of the query. Many query designs prevent SARGability and lead to table or index scans and high-CPU usage. Consider the following query against the AdventureWorks database where every ProductNumber must be retrieved and the SUBSTRING() function applied to it, before it's compared to a string literal value. As you can see, you have to fetch all the rows of the table first, and then apply the function before you can make a comparison. Fetching all rows from the table means a table or index scan, which leads to higher CPU usage.
Applying any function or computation on the column(s) in the search predicate generally makes the query non-sargable and leads to higher CPU consumption. Solutions typically involve rewriting the queries in a creative way to make the SARGable. A possible solution to this example is this rewrite where the function is removed from the query predicate, another column is searched and the same results are achieved:
Here's another example, where a sales manager may want to give 10% sales commission on large orders and wants to see which orders will have commission greater than $300. Here's the logical, but non-sargable way to do it.
Here's a possible less-intuitive but SARGable rewrite of the query, in which the computation is moved to the other side of the predicate.
SARGability applies not only to WHERE clauses, but also to JOINs , HAVING , GROUP BY and ORDER BY clauses. Frequent occurrences of SARGability prevention in queries involve CONVERT() , CAST() , ISNULL() , COALESCE() functions used in WHERE or JOIN clauses that lead to scan of columns. In the data-type conversion cases ( CONVERT or CAST ), the solution may be to ensure you're comparing the same data types. Here's an example where the T1.ProdID column is explicitly converted to the INT data type in a JOIN . The conversion defeats the use of an index on the join column. The same issue occurs with implicit conversion where the data types are different and SQL Server converts one of them to perform the join.
To avoid a scan of the T1 table, you can change the underlying data type of the ProdID column after proper planning and design, and then join the two columns without using the convert function ON T1.ProdID = T2.ProductID .
Another solution is to create a computed column in T1 that uses the same CONVERT() function and then create an index on it. This will allow the query optimizer to use that index without the need for you to change your query.
In some cases, queries can't be rewritten easily to allow for SARGability. In those cases, see if the computed column with an index on it can help, or else keep the query as it was with the awareness that it can lead to higher CPU scenarios.
Решение
Эта проблема исправлена в перечисленных ниже накопительных обновлениях для 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 , обсуждает проблемы и разрешения, связанные с отношением блокировки.
23.03.2020
insci
SQL Server
комментария 3
В этой статье мы рассмотрим популярные инструменты, T-SQL запросы и скрипты для обнаружения и решения различных возможных проблем с производительностью SQL Server. Эта статья поможет вам разобраться, когда вашему SQL Server недостаточно ресурсов (памяти, CPU, IOPs дисков), найти блокировки, выявить медленные запросы. Посмотрим какие есть встроенные инструменты и бесплатные сторонние скрипты и утилиты для анализа состояния Microsoft SQL Server.
Step 7: Disable heavy tracing
Check for SQL Trace or XEvent tracing that affects the performance of SQL Server and causes high CPU usage. For example, using the following events may cause high CPU usage if you trace heavy SQL Server activity:
- Query plan XML events ( query_plan_profile , query_post_compilation_showplan , query_post_execution_plan_profile , query_post_execution_showplan , query_pre_execution_showplan )
- Statement-level events ( sql_statement_completed , sql_statement_starting , sp_statement_starting , sp_statement_completed )
- Log-in and log-out events ( login , process_login_finish , login_event , logout )
- Lock events ( lock_acquired , lock_cancel , lock_released )
- Wait events ( wait_info , wait_info_external )
- SQL Audit events (depending on the group audited and SQL Server activity in that group)
Run the following queries to identify active XEvent or Server traces:
Читайте также: