Sql server windows nt 64 bit грузит процессор
Добрый день, win ser 2012, mssql 2012
служба sql server windows nt Нагружает процессор до 70 % и такая нагрузка бывает длиться по 15 минут.
в этот период времени у пользователей бывает вышибает программу 1с.
подскажите как проверить? какой запрос съедает ресурсы ЦП?
(0) Это вам повезло.
Ясно чего системе не хватает. Нужно больше процессоров. Теоретически вы можете попробовать понять почему оно грузит процессор. А практически для небольших организаций дать системе больше ядер дешевле чем разбираться.
Намного дороже по деньгам ситуация когда 1С тормозит а на сервере ничего не загружено, ни процессор, ни диск, ни память.
в активити мониторе можно увидеть текущий запрос, который долго выполняется, если его породила 1ска, то по именам метаданных прикинуть, что же это такое, поискать в коде и т.п.
вообще это может быть что-то типа "анализ субконто" за весь период без отборов
если это 1с, то, опять же, можно посмотреть в консоли 1с у кого из юзеров сейчас большое "время вызова СУББ текущее", подойти и ненавязчиво поинтересоваться, что же он такое запустил.
>какой запрос съедает ресурсы ЦП?
>как мне понять что именно грузит проц?
как это в одной голове уживается. полушария живут отдельно друг от друга?
я бы предложил ребилд индексов или хотя бы обновление статистики сделать.
проверить модель восстановления БД, может какой то гений full поставил и теперь там лог в 10 раз больше базы.
когда скуль нормально настроен регламентные его так не насилуют обычно. скорее всего что то в конфигурации сервера/бд напутано.
>я бы предложил ребилд индексов или хотя бы обновление статистики сделать.
есть повод выбрасывать скомпилированные планы выполнения?
Что-то не ясно, почему в (10) противопоставление: эмпирический - основанный на опыте. Эвристический - наверное, имелось в виду, что нужно чутье. Одно другому не противоречит же - нужно применять чутье во время опыта.
В этой статье приведены процедуры диагностики и устранения проблем, вызванных высокой загрузкой ЦП на компьютере, на котором выполняется Microsoft SQL Server. Хотя существует множество возможных причин высокой загрузки ЦП, которые возникают в SQL Server, наиболее распространенными причинами являются следующие:
- Высокие логические операции чтения, вызванные сканированием таблицы или индекса из-за следующих условий:
- Устарелая статистика
- Отсутствующие индексы
- Плохо спроектированные запросы
Чтобы устранить проблемы с высокой загрузкой ЦП в SQL Server, выполните следующие действия.
Шаг 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) , используя созданный во втором столбце результатов запроса.
Общие рекомендации
Электропитания - использовать «Высокая производительность»
настройка кэширования записи на диск
антивирус, - добавить папку SQL Server и файлов БД в исключения
настройка настроены параметры параллелизма (cost threshold for parallelism, max degree of parallelism)
настройка Hyper-Threading.
Шаг 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:
Мониторинг Reporting Services - Performance Dashboard Reports
Для наблюдения за 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?
Шаг 9. Настройка виртуальной машины
Если вы используете виртуальную машину, убедитесь, что вы не перепроизводите ЦП и что они настроены правильно. Дополнительные сведения см. в статье об устранении неполадок с производительностью виртуальных машин ESX и ESXi (2001003).
2. Ищем проблемные процессы
spID с 1 до 50 - это системные. Мы можем отключать (kill spID) или смотреть запрос только для пользовательских (spID>50).
Также пробуем использовать хранимки exec sp_who, sp_who1, sp_who2, sp_who3 - они позволяют посмотреть все процессы и их текущее состояние.
По spid можно найти этот запрос:
Альтернативно вы можете посмотреть последний запрос, выполняющийся в рамках этого spID:
А также можно убить процесс через kill spID. Убили процесс - и посмотрели как это сказалось на загрузке.
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.
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:
Шаг 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. В таких случаях вы можете проверить, может ли вычисляемый столбец с индексом помочь или сохранить запрос так, как это было, с учетом того, что он может привести к более высоким сценариям ЦП.
1. Cмотрим счетчики perfmon
Определяем проблема в Kernel или User запросах.
В perfmon смотрим следующие параметры:
- Processor: % Privileged Time – Percentage of time processor spends on execution of Microsoft Windows kernel commands such as OS activity. (If more than 30% involve Windows Admins)
- Process (sqlservr): % Privileged Time – the sum of processor time on each processor for all threads of the process (SQL Kernel)
- Processor: % User Time – percentage of time the processor spends on executing user processes such as SQL Server. This includes I/O requests from SQL Server
Если это значение % Privileged Time / No of logical cpus больше 30%, то скорее всего дело в системных настройках, возможно антивирус.
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).
Мониторинг (платный)
(платный) от разных компаний:
Idera — SQL Diagnostic Manager
Red-Gate — SQL Monitor
ApexSQL — ApexSQL Monitor
Quest — Spotlight on SQL Server Enterprise
SentryOne — SQL centry
Так же - вариант мониторинга SQL Server на Zabbix.
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:
4. Анализ найденных проблемных запросов
В найденных запросах посмотрите execution plan и посмотрите где наибольший cost.
Шаг 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 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.
Альтернативная документация по поиску CPU проблем 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 процентов, антивирусная программа, другие драйверы или другой компонент ОС на компьютере способствуют высокой загрузке ЦП. Чтобы проанализировать первопричину этого поведения, обратитесь к системному администратору.
Мониторинг SSMS - «Стандартные отчеты»
«Стандартные отчеты» в пользовательском интерфейсе Management Studio
SQL Server Management Studio предоставляет минимальный необходимый набор стандартных отчетов для получения информации в режиме пользовательского интерфейса.
Доступ к этим отчетам может быть выполнен через «Обозреватель объектов» (Object explorer) → Правый клик мыши по базе данных → «Отчеты» (Reports) → «Стандартный отчет» (Standard reports)
Перечень «Стандартные отчеты»:
Перечень «Стандартные отчеты»:
- Занято место на диске
- Использование дисковой памяти верхними таблицами
- Использование дисковой памяти таблицей
- Использование дисковой памяти секцией
- События резервного копирования и восстановления
- Все транзакции
- Все блокирующие транзакции
- Самые продолжительные транзакции
- Транзакции, блокирующие наибольшее кол-во транзакций при выполнении
- Транзакции с наибольшим кол-вом блокировок
- Статистика блокировки ресурсов по объектам
- Статистика выполнения объектов
- Журнал согласованности баз данных
- Статистика использования индекса
- Физическая статистика индекса
- Журнал изменений схемы
- Статистика пользователей
- Перечень «Пользовательские отчеты»
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 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.
3. Выявление проблем через спец запросы SQL
Также попробуйте выполнить следующие запросы для поиска проблемных мест по CPU
Еще один скрипт для поиска проблемных запросов по CPU:
Для найденных элементов можно удалить план в кеше (подставив sql_handle):
Еще 1 запрос на поиск проблем по CPU:
Также посмотрите правой кнопкой на Сервере > reports> Standard reports > Top CPU queries.
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 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.
СКРИПТЫ - системные
sp_who и sp_who2
найти блокирующие и ожидающие запросы
1 -- 100_1_sys.dm_exec_query_stats - most time cpu
-- 100_1_sys.dm_exec_query_stats_Which Queries are taking the most time cpu to execute
Шаг 3. Обновление статистики
Определив запросы с наибольшим потреблением ЦП , обновите статистику таблиц, используемых этими запросами. Системную хранимую sp_updatestats процедуру можно использовать для обновления статистики всех определяемых пользователем и внутренних таблиц в текущей базе данных. Например.
Системная sp_updatestats хранимая процедура выполняется UPDATE STATISTICS для всех пользовательских и внутренних таблиц в текущей базе данных. Для регулярного обслуживания убедитесь, что регулярное планирование обслуживания обновляет статистику. Используйте такие решения, как " Дефрагментация адаптивного индекса", для автоматического управления дефрагментативной индекса и обновлениями статистики для одной или нескольких баз данных. Эта процедура автоматически выбирает, следует ли перестраивать или реорганизовать индекс в соответствии с уровнем фрагментации, помимо других параметров, и обновлять статистику с помощью линейного порогового значения.
Дополнительные сведения см sp_updatestats . в sp_updatestats.
Если SQL Server по-прежнему использует чрезмерную емкость ЦП, перейдите к следующему шагу.
Шаг 4. Добавление отсутствующих индексов
Выполните следующий запрос, чтобы определить запросы, которые приводят к высокой загрузке ЦП и содержат по крайней мере один отсутствующий индекс в плане запроса:
Просмотрите планы выполнения для идентифицированных запросов и настройте запрос, внося необходимые изменения. На следующем снимке экрана показан пример, в котором SQL Server будет указывать на отсутствующий индекс для запроса. Щелкните правой кнопкой мыши часть "Отсутствующий индекс" в плане запроса, а затем выберите "Отсутствующие сведения об индексе", чтобы создать индекс в другом окне SQL Server Management Studio.
Используйте следующий запрос, чтобы проверить отсутствие индексов и применить все рекомендуемые индексы с высокими значениями мер улучшения. Начните с 5 или 10 рекомендаций из выходных данных с наибольшим improvement_measure значения. Эти индексы имеют наиболее значительное положительное влияние на производительность. Решите, следует ли применять эти индексы, и убедитесь, что для приложения выполнено тестирование производительности. Затем продолжайте применять рекомендации по отсутствующим индексам, пока не дойдет желаемых результатов производительности приложения. Дополнительные сведения об этом разделе см. в разделе "Настройка некластеризованных индексов с отсутствующим предложением индекса".
Источники и что почитать по теме утечек CPU
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):
Высокая загрузка 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 - Заходим в Диспетчер задач, на вкладке Подробности находим sqlserver и смотрим колонку ЦП.
Если это значение постонно высокое, то значит где-то идет утечка CPU.
В этом руководстве мы собрали различные советы как решать подобную проблему
Мониторинг Activity Monitor - Монитор активности
Открыть монитор активности CTRL+ALT+A или SSMS стандарт. панель инструментов значок.
Монитор активности SQL Server 2008 объединяет данные о процессах, предоставляя наглядную информацию по выполняющимся и недавно выполнявшимся процессам.
Монитор активности предлагает администратору раздел обзора, внешне похожий на Диспетчер задач Windows, а также компоненты детального просмотра отдельных процессов, ожидания ресурсов, ввода-вывода в файлы данных и последних ресурсоемких запросов.
Поиск проблемных мест в SQL Server по CPU
Шаг 10. Увеличение масштаба SQL Server
Если отдельные экземпляры запросов используют небольшую емкость ЦП, но общая рабочая нагрузка всех запросов вместе приводит к высокой загрузке ЦП, рассмотрите возможность масштабирования компьютера путем добавления дополнительных ЦП. Используйте следующий запрос, чтобы найти количество запросов, превышающих определенное пороговое значение среднего и максимального потребления ЦП на выполнение и которые выполняются много раз в системе (убедитесь, что значения двух переменных соответствуют вашей среде):
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.
Читайте также: