Как посмотреть чем sql занимает процессор

Мониторинг использования ЦП

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

Эффективный способ определения использования ЦП — использовать счетчик времени процессора:% процессора в Монитор производительности. Этот счетчик отслеживает время, которое ЦП тратит на выполнение потока во время работы. Согласованное состояние от 80 до 90 процентов может указывать на необходимость обновления ЦП или добавления дополнительных процессоров. При работе с многопроцессорными системами следите за отдельным экземпляром упомянутого счетчика для каждого процессора. Это значение представляет суммарное процессорное время указанного процессора. Чтобы определить среднее для всех процессоров, воспользуйтесь вместо этого счетчиком Система: % общего процессорного времени .

Дополнительно можно контролировать следующие счетчики:

Процессор: % работы в привилегированном режиме

Физический диск

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

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

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

Связанный контент

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

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

Большое количество логических считываний при сканировании таблицы или индексов из-за следующих причин:
- Устарелая статистика
- Отсутствующие индексы
- Проблемы плана с учетом параметров
- Неверное проектирование запроса
Для устранения неполадок с высокой загрузкой ЦП в SQL Server выполните следующие действия.

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

Используйте один из приведенных ниже инструментов, чтобы проверить, действительно ли процесс SQL Server вызывает высокую загрузку ЦП:
- Диспетчер задач. На вкладке Процессыпроверьте, указано ли в колонке ЦП для SQL Server Windows NT-64 Bit значение, близкое к 100 процентам.
- Монитор производительности и ресурсов (perfmon)
  - Счетчик: Process/%User Time , % Privileged Time
  - Экземпляр: sqlservr
```
$serverName = $env:COMPUTERNAME $Counters = @( ("\\$serverName" + "\Process(sqlservr*)\% User Time"), ("\\$serverName" + "\Process(sqlservr*)\% Privileged Time") ) Get-Counter -Counter $Counters -MaxSamples 30 | ForEach < $_.CounterSamples | ForEach < [pscustomobject]@< TimeStamp = $_.TimeStamp Path = $_.Path Value = ([Math]::Round($_.CookedValue, 3)) >Start-Sleep -s 2 > > 
```
  Если % User Time значение постоянно превышает 90 процентов (% пользовательского времени — это сумма времени процессора на каждом процессоре, его максимальное значение равно 100 % * (без ЦП)), SQL Server процесс приводит к высокой загрузке ЦП. Однако, если значение % Privileged time постоянно превышает 90 процентов, высокой загрузке ЦП способствует антивирусное ПО, другие драйвера или компоненты ОС. Свяжитесь со своим системным администратором, чтобы проанализировать причину этого поведения.
  
  Шаг 2: Определение запросов, способствующих высокой загрузке ЦП
  
  Если высокая загрузка ЦП вызвана процессом Sqlservr.exe , наиболее распространенной причиной этого являются запросы SQL Server, выполняющие сканирование таблицы или индекса, а также сортировки, операции с хэшем и циклы (оператор вложенного цикла или WHILE (T-SQL)). Чтобы понять, какая часть из общего ресурса ЦП загружена текущими запросами, запустите следующее выражение:
```
DECLARE @init_sum_cpu_time int, @utilizedCpuCount int --get CPU count used by SQL Server SELECT @utilizedCpuCount = COUNT( * ) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE' --calculate the CPU usage by queries OVER a 5 sec interval SELECT @init_sum_cpu_time = SUM(cpu_time) FROM sys.dm_exec_requests WAITFOR DELAY '00:00:05' SELECT CONVERT(DECIMAL(5,2), ((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU from Queries as Percent of Total CPU Capacity] FROM sys.dm_exec_requests 
```
  Чтобы определить запросы, отвечающие за высокую активность ЦП, выполните следующую инструкцию:
```
SELECT TOP 10 s.session_id, r.status, r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000 * 60) 'Elaps M', SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text, COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text, r.command, s.login_name, s.host_name, s.program_name, s.last_request_end_time, s.login_time, r.open_transaction_count FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time DESC 
```
  Если в настоящее время запросы не управляют ЦП, можно выполнить следующую инструкцию, чтобы найти прошлые запросы с привязкой к ЦП:
```
SELECT TOP 10 qs.last_execution_time, st.text AS batch_text, SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text, (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms, (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms, (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st ORDER BY(qs.total_worker_time / qs.execution_count) DESC 
```
  Шаг 3. Обновление статистики
  
  Определив запросы с наибольшим потреблением ЦП, обновите статистику таблиц, используемых этими запросами. Для обновления статистики всех внутренних и заданных пользователем таблиц текущей базы данных можно использовать процедуру sp_updatestats , хранимую в системе. Например:
```
exec sp_updatestats 
```
  Процедура sp_updatestats , хранимая в системе, выполняет UPDATE STATISTICS для всех внутренних таблиц и таблиц, заданных пользователем, в текущей базе данных. В рамках регулярного обслуживания необходимо проверять актуальности статистики. Используйте такие решения, как Адаптивная дефрагментация индекса, для автоматического управления дефрагментацией индекса и обновлением статистики для одной или больше баз данных. С помощью линейного порогового значения эта процедура автоматически принимает решение о перестройке или реорганизации индекса в зависимости от его уровня фрагментации и других параметров, а также об обновлении статистики.
  
  Дополнительные сведения о sp_updatestats см. в sp_updatestats.
  
  Если SQL Server по-прежнему использует слишком много ресурса ЦП, перейдите к следующему шагу.
  
  Шаг 4. Добавление отсутствующих индексов
  
  Отсутствие индексов может привести к замедлению выполнения запросов и высокой загрузке ЦП. Вы можете определить отсутствующие индексы и создать их для улучшения этого влияния на производительность.
```
-- Captures the Total CPU time spent by a query along with the query plan and total executions SELECT qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms, q.[text], p.query_plan, qs_cpu.execution_count, q.dbid, q.objectid, q.encrypted AS text_encrypted FROM (SELECT TOP 500 qs.plan_handle, qs.total_worker_time, qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpu CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q CROSS APPLY sys.dm_exec_query_plan(plan_handle) p WHERE p.query_plan.exist('declare namespace qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //qplan:MissingIndexes')=1 
```
- Просмотрите планы выполнения для идентифицированных запросов и настройте запрос, внося необходимые изменения. На следующем снимке экрана показан пример, в котором SQL Server будет указывать на отсутствующий индекс для запроса. Щелкните правой кнопкой мыши часть «Отсутствующий индекс» в плане запроса, а затем выберите «Отсутствующие сведения об индексе», чтобы создать индекс в другом окне SQL Server Management Studio.
- Используйте следующий запрос, чтобы проверить отсутствие индексов и применить все рекомендуемые индексы с высокими значениями мер улучшения. Начните с 5 или 10 рекомендаций из выходных данных с наибольшим значением improvement_measure. Эти индексы имеют наиболее значительное положительное влияние на производительность. Решите, следует ли применять эти индексы, и убедитесь, что для приложения выполнено тестирование производительности. Затем продолжайте применять рекомендации по отсутствующим индексам, пока не достигнете желаемых результатов производительности приложения. Дополнительные сведения по этой теме см. в разделе «Настройка некластеризованных индексов с отсутствующим предложением индекса».
```
SELECT CONVERT(VARCHAR(30), GETDATE(), 126) AS runtime, mig.index_group_handle, mid.index_handle, CONVERT(DECIMAL(28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure, 'CREATE INDEX missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, migs.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE CONVERT (DECIMAL (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC 
```
  Шаг 5. Изучение и устранение проблем с учетом параметров
  
  Вы можете использовать команду DBCC FREEPROCCACHE, чтобы освободить кэш планов и проверить, устранена ли проблема с высокой загрузкой ЦП. Если проблема устранена, это указывает на проблему с учетом параметров (PSP, также известная как «проблема со анализом параметров»).
  
  Использование DBCC FREEPROCCACHE без параметров удаляет все скомпилированные планы из кэша планов. Это приведет к повторной компиляции новых выполнений запросов, что приведет к увеличению длительности каждого нового запроса. Лучший подход — использовать DBCC FREEPROCCACHE ( plan_handle | sql_handle ) , чтобы определить, какой запрос может вызвать проблему, а затем устранить этот отдельный запрос или запросы.
  
  Чтобы устранить проблемы, чувствительные к параметрам, используйте следующие методы. Каждый метод имеет компромиссы и недостатки.
```
SELECT * FROM Person.Person WHERE LastName = 'Wood' OPTION (RECOMPILE) 
```
```
DECLARE @LastName Name = 'Frintu' SELECT FirstName, LastName FROM Person.Person WHERE LastName = @LastName OPTION (OPTIMIZE FOR (@LastName = 'Wood')) 
```
```
SELECT * FROM Person.Address WHERE City = 'SEATTLE' AND PostalCode = 98104 OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING')) 
```
```
SELECT text, 'DBCC FREEPROCCACHE (0x' + CONVERT(VARCHAR (512), plan_handle, 2) + ')' AS dbcc_freeproc_command FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan(plan_handle) CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE text LIKE '%person.person%' 
```
  Шаг 6. Изучение и устранение проблем с SARGability
  
  Предикат в запросе поддерживает поиск SARG (поиск с поддержкой аргумента), если ядро SQL Server может использовать индекс для поиска, чтобы ускорить выполнение запроса. Многие макеты запросов делают поиск по аргументам невозможным, что приводит к сканированию таблицы или индекса и высокой загрузке ЦП. Рассмотрим следующий запрос к базе данных AdventureWorks, где необходимо получить каждый элемент ProductNumber и применить к нему функцию SUBSTRING() , после чего сравнить его с буквенным значением строки. Как видите, перед началом сравнения необходимо получить все строки таблицы, а затем применить к ним функцию. Получение всех строк таблицы — это сканирование таблицы или индекса, что приводит к повышению загрузки ЦП.
```
SELECT ProductID, Name, ProductNumber FROM [Production].[Product] WHERE SUBSTRING(ProductNumber, 0, 4) = 'HN-' 
```
  Применение любой функции или вычисления к одной или нескольким строкам в предикате поиска обычно приводит к тому, что запрос не поддерживает поиска по аргументам и повышению загрузки ЦП. Решения этой проблемы обычно связаны с творческим редактированием запросов, чтобы добавить им поддержку поиска по аргументам. Возможным решением проблемы в этом примере являются следующие правки с удалением функции из предиката запроса и поиском в другой строке, что дает идентичный результат:
```
SELECT ProductID, Name, ProductNumber FROM [Production].[Product] WHERE Name LIKE 'Hex%' 
```
  Рассмотрим еще один пример, в котором менеджеру по продажам необходимо взыскать комиссионный сбор в размере 10% от суммы продаж для больших заказов, и проверить заказы, для которых комиссия составила больше 300 долларов США. Ниже приведен логический способ без поддержки поиска по аргументам.
```
SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission] FROM [Sales].[SalesOrderDetail] WHERE UnitPrice * 0.10 > 300 
```
  Ниже приведено менее интуитивное решение запроса с поддержкой поиска по аргументам, в котором вычисление перенесено в другую часть предиката.
```
SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission] FROM [Sales].[SalesOrderDetail] WHERE UnitPrice > 300/0.10 
```
  Поиск по аргументам применим не только к условию WHERE , но и к условиям JOINs , HAVING , GROUP BY и ORDER BY . Часто невозможность использования поиска по аргументам происходит из-за использования функций CONVERT() , CAST() , ISNULL() и COALESCE() с условиями WHERE и JOIN , что приводит к сканированию столбцов. При использовании условий с преобразованием типа данных ( CONVERT или CAST ) для решения проблемы можно перейти к сравнению одинаковых типов данных. Ниже приведен пример, в котором столбец T1.ProdID преобразуется в тип данных INT в условии JOIN Преобразование не позволяет использовать индекс в столбце соединения. Та же проблема возникает при неявном преобразовании, когда типы данных отличаются, и SQL Server преобразовывает один из них для выполнения соединения.
```
SELECT T1.ProdID, T1.ProdDesc FROM T1 JOIN T2 ON CONVERT(int, T1.ProdID) = T2.ProductID WHERE t2.ProductID BETWEEN 200 AND 300 
```
  Чтобы избежать сканирования таблицы T1 , можно изменить базовый тип данных столбца ProdID после надлежащего планирования и проектирования, а затем присоединить два столбца без использования функции преобразования ON T1.ProdID = T2.ProductID .
  
  Другим решением является создание вычисляемого столбца T1 , в котором используется та же функция CONVERT() , а затем создание индекса на нем. Это позволит оптимизатору запросов использовать этот индекс без необходимости изменять запрос.
```
ALTER TABLE dbo.T1 ADD IntProdID AS CONVERT (INT, ProdID); CREATE INDEX IndProdID_int ON dbo.T1 (IntProdID); 
```
  В некоторых случаях запросы нельзя легко переписать, чтобы обеспечить возможность SARGability. В таких случаях вы можете проверить, может ли вычисляемый столбец с индексом помочь, или нужно сохранить запрос так, как это было, с учетом того, что он может привести к более высоким сценариям ЦП.
  
  Шаг 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:
```
PRINT '--Profiler trace summary--' SELECT traceid, property, CONVERT(VARCHAR(1024), value) AS value FROM::fn_trace_getinfo( default) GO PRINT '--Trace event details--' SELECT trace_id, status, CASE WHEN row_number = 1 THEN path ELSE NULL end AS path, CASE WHEN row_number = 1 THEN max_size ELSE NULL end AS max_size, CASE WHEN row_number = 1 THEN start_time ELSE NULL end AS start_time, CASE WHEN row_number = 1 THEN stop_time ELSE NULL end AS stop_time, max_files, is_rowset, is_rollover, is_shutdown, is_default, buffer_count, buffer_size, last_event_time, event_count, trace_event_id, trace_event_name, trace_column_id, trace_column_name, expensive_event FROM (SELECT t.id AS trace_id, row_number() over(PARTITION BY t.id order by te.trace_event_id, tc.trace_column_id) AS row_number, t.status, t.path, t.max_size, t.start_time, t.stop_time, t.max_files, t.is_rowset, t.is_rollover, t.is_shutdown, t.is_default, t.buffer_count, t.buffer_size, t.last_event_time, t.event_count, te.trace_event_id, te.name AS trace_event_name, tc.trace_column_id, tc.name AS trace_column_name, CASE WHEN te.trace_event_id in (23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) THEN CAST(1 as bit) ELSE CAST(0 AS BIT) END AS expensive_event FROM sys.traces t CROSS APPLY::fn_trace_geteventinfo(t.id) AS e JOIN sys.trace_events te ON te.trace_event_id = e.eventid JOIN sys.trace_columns tc ON e.columnid = trace_column_id) AS x GO PRINT '--XEvent Session Details--' SELECT sess.NAME 'session_name', event_name, xe_event_name, trace_event_id, CASE WHEN xemap.trace_event_id IN(23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) THEN Cast(1 AS BIT) ELSE Cast(0 AS BIT) END AS expensive_event FROM sys.dm_xe_sessions sess JOIN sys.dm_xe_session_events evt ON sess.address = evt.event_session_address INNER JOIN sys.trace_xe_event_map xemap ON evt.event_name = xemap.xe_event_name GO 
```
  Шаг 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»
  
  Шаг 9. Настройка виртуальной машины
  
  Если вы используете виртуальную машину, убедитесь, что вы не выделяете слишком много ЦП и что они настроены правильно. Дополнительные сведения см. в статье об устранении неполадок с производительностью виртуальных машин ESX и ESXi (2001003).
  
  Шаг 10. Увеличение масштаба системы для использования дополнительных ЦП
  
  Если отдельные экземпляры запросов используют небольшую емкость ЦП, но общая рабочая нагрузка всех запросов вместе приводит к высокой загрузке ЦП, рассмотрите возможность масштабирования компьютера путем добавления дополнительных ЦП. Используйте следующий запрос, чтобы найти количество запросов, превышающих определенное пороговое значение среднего и максимального потребления ЦП на выполнение и которые выполняются много раз в системе (убедитесь, что значения двух переменных соответствуют вашей среде):
```
-- Shows queries where Max and average CPU time exceeds 200 ms and executed more than 1000 times DECLARE @cputime_threshold_microsec INT = 200*1000 DECLARE @execution_count INT = 1000 SELECT qs.total_worker_time/1000 total_cpu_time_ms, qs.max_worker_time/1000 max_cpu_time_ms, (qs.total_worker_time/1000)/execution_count average_cpu_time_ms, qs.execution_count, q.[text] FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q WHERE (qs.total_worker_time/execution_count > @cputime_threshold_microsec OR qs.max_worker_time > @cputime_threshold_microsec ) AND execution_count > @execution_count ORDER BY qs.total_worker_time DESC 
```
  См. также
  - При запросах, использующих оптимизированный вложенный цикл или пакетную сортировку, могут быть выделены ресурсы ЦП или памяти.
  - Рекомендуемые обновления и параметры конфигурации для SQL Server с рабочими нагрузками с высокой производительностью
  - Рекомендуемые обновления и параметры конфигурации для SQL Server 2017 и 2016 с рабочими нагрузками c высокой производительностью
  Обратная связь
  
  Были ли сведения на этой странице полезными?
  
  Как посмотреть чем sql занимает процессор
  
  День добрый.
  Проблема: SQL постоянно занимает 1 ядро из двух.
  
  Конфигурация: компьютер разработчика Win 7 64 бит. проц 2-х ядерный атлон. Сервер Microsoft SQL Server Enterprise Edition (64-bit). Памяти 9 Гиг. Серверу sql добступно от 1 до 3 гиг.
  
  Последнее время обратил внимание что даже когда ничего не делаешь процессор загружен на 50%. Оказалось что это sql. http://screencast.com/t/HvCQlBab6 . Подумал что это регламенты и т.д. Но при выключении сервера 1с (у меня несколько выключил все) sql все равно что то делает. По монитору не пойму что:http://screencast.com/t/rEDhB8NarnV.
  
  Раньше такого вроде не было. Помогите куда копнуть, что почитать?
  
  Профайлер может чтонить подскажет
  в management studio есть activity monitir и job activity monitor
  
  (2) активити монитор показал. Базы в разработке, поэтому что бы место не занимали у некоторых поставил автоматическое сжатие. Может это оно?
  
  SQL Server сильно грузит процессор. Проблемы с CPU у процесса SQLServer. Оптимизация запросов SQL по CPU. Подробное руководство
  
  Как понять что проблема именно в SQL Server — Заходим в Диспетчер задач, на вкладке Подробности находим sqlserver и смотрим колонку ЦП.
  
  Если это значение постонно высокое, то значит где-то идет утечка CPU.
  
  В этом руководстве мы собрали различные советы как решать подобную проблему
  
  Поиск проблемных мест в SQL Server по CPU
  
  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%, то скорее всего дело в системных настройках, возможно антивирус.
  
  Используя данную инструкцию вы можете найти проблемные spID, которые в данный момент загружают процессор — https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/
  
  2. Ищем проблемные процессы
```
SELECT * FROM sys.sysprocesses WHERE cmd like 'LAZY WRITER' or cmd like '%Ghost%' or cmd like 'RESOURCE MONITOR'
```
```
SELECT top 20 spid, kpid, dbid, cpu, memusage FROM sysprocesses order by cpu desc 
```
  spID с 1 до 50 — это системные. Мы можем отключать (kill spID) или смотреть запрос только для пользовательских (spID>50).
  
  Также пробуем использовать хранимки exec sp_who, sp_who1, sp_who2, sp_who3 — они позволяют посмотреть все процессы и их текущее состояние.
  
  По spid можно найти этот запрос:
```
DECLARE @sqltext VARBINARY(128) SELECT @sqltext = sql_handle FROM sys.sysprocesses WHERE spid = 78 SELECT TEXT FROM sys.dm_exec_sql_text(@sqltext) GO
```
  Альтернативно вы можете посмотреть последний запрос, выполняющийся в рамках этого spID:
```
DBCC INPUTBUFFER(60) GO SELECT @@SPID -- получить SPID текущего процесса GO
```
  А также можно убить процесс через kill spID. Убили процесс — и посмотрели как это сказалось на загрузке.
  
  3. Выявление проблем через спец запросы SQL
  
  Также попробуйте выполнить следующие запросы для поиска проблемных мест по CPU
```
SELECT GETDATE() AS "RunTime", st.text AS batch, SUBSTRING(st.text,statement_start_offset / 2+1 ,((CASE WHEN a.statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),st.text)) * 2) ELSE a.statement_end_offset END) - a.statement_start_offset) / 2+1) AS current_statement , qp.query_plan, a.* FROM sys.dm_exec_requests a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) AS qp ORDER BY CPU_time DESC
```
  Еще один скрипт для поиска проблемных запросов по CPU:
```
/* Disclaimer: I am not sure for the origin of this script/query. This query is used in our team to identify and resolve high CPU issue */ --define the temptables that will hold intermediary results IF OBJECT_ID('tempdb..#dbcc') IS NOT NULL DROP TABLE #dbcc create table #dbcc(c1 varchar(15), c2 int, c3 varchar(255),spid int default 0) IF OBJECT_ID('tempdb..#cpugroups') IS NOT NULL DROP TABLE #cpugroups create table #cpugroups (sql_handle binary(20), sql_text nvarchar(50),total_cpu bigint,total_io bigint,total_sessions int, total_threads int) --take the SPID groups that are running same code (NOT statement) insert into #cpugroups select top 10 sql_handle,substring((select text from fn_get_sql(sql_handle)),1,50), SUM(CPU) TotalCPUForGroup, SUM(physical_io) TotalIOForGroup, COUNT(distinct spid) TotalNoOfSessions,COUNT(*) TotalNoOfThreads from master..sysprocesses (nolock) where spid>50 and status<>'sleeping' and sql_handle<>0x0 and spid<>@@spid group by sql_handle order by TotalCPUForGroup desc declare @sql nvarchar(max) declare @t table (spid int) INSERT INTO @t SELECT DISTINCT spid FROM master..sysprocesses WHERE spid>50 and sql_handle in (select sql_handle from #cpugroups) declare @spid int WHILE EXISTS(select * from @t) BEGIN select top 1 @spid=spid from @t set @sql='dbcc inputbuffer('+LTRIM(STR(@spid))+')' --try to retrieve the original command for all SPIDs BEGIN TRY INSERT INTO #dbcc(c1, c2, c3) EXEC (@sql) update #dbcc set spid=@spid where spid=0 END TRY BEGIN CATCH END CATCH delete from @t where spid=@spid END select * from #cpugroups select c3 [sql_text], count(*) NoOfSessionsRunning from #dbcc group by c3 order by 2 desc select * from #dbcc
```
  Для найденных элементов можно удалить план в кеше (подставив sql_handle):
```
DBCC FREEPROCCACHE (plan_handle_id_goes_here)
```
  Еще 1 запрос на поиск проблем по CPU:
```
SELECT r.session_id ,st.TEXT AS batch_text ,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, ( ( CASE WHEN r.statement_end_offset = - 1 THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2) ELSE r.statement_end_offset END ) - r.statement_start_offset ) / 2 + 1) AS statement_text ,qp.query_plan AS 'XML Plan' ,r.* FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp ORDER BY cpu_time DESC
```
```
SELECT s.session_id, r.status, r.blocking_session_id 'Blk by', r.wait_type, wait_resource, r.wait_time / (1000 * 60) 'Wait M', r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000 * 60) 'Elaps M', Substring(st.TEXT,(r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text, Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text, r.command, s.login_name, s.host_name, s.program_name, s.last_request_end_time, s.login_time, r.open_transaction_count FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time desc
```
  Также посмотрите правой кнопкой на Сервере > reports> Standard reports > Top CPU queries.
  
  4. Анализ найденных проблемных запросов
  
  В найденных запросах посмотрите execution plan и посмотрите где наибольший cost.
  - проверьте что стоят все необходимые индексы на таблицах, которые участвуют в проблемных запросах.
  - постарайтесь как можно «быстрее» в where обрезать данные для уменьшения выборки для последующей обработки. Т.е. старайтесь не допускать сканирования по очень большой таблице без использования какой либо фильтрации (либо эта фильтрация содержит затратную логику проверки условий)
  - проверьте, что у вас нет затратных конвертаций типов
  - если используете множественно функции, проверьте что они используют минимум извлечений внутри.
  - Вариант — поставить обновление https://support.microsoft.com/ru-ru/help/3195888/fix-high-cpu-usage-causes-performance-issues-in-sql-server-2016-and-20
```
 select * from sys. dm_os_spinlock_stats -- провериить что нет больших чисел у SECURITY_CACHE и CMED_HASH_SET - если есть то установить обновления
```
  - https://docs.microsoft.com/ru-ru/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-plan-transact-sql?view=sql-server-ver15 находить тяжелые процессы и смотреть их планы запросов
  - Как посмотреть запрос по sql handle или plan handle — https://docs.microsoft.com/ru-ru/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sql-text-transact-sql?view=sql-server-ver15
  Источники и что почитать по теме утечек CPU
  - http://dba-datascience.com/high-cpu-usage-sql-server/
  - https://support.microsoft.com/ru-ru/help/2009160/high-cpu-use-occurs-in-your-queries-on-sql-server
  - https://blog.sqlauthority.com/2018/03/23/sql-server-how-to-fix-high-cpu-consumption-on-sql-server-2017-and-2016/
  - https://www.c-sharpcorner.com/blogs/difference-between-spwho-spwho2
  - https://docs.microsoft.com/ru-ru/archive/blogs/docast/sql-high-cpu-troubleshooting-checklist очень подробная статья.
  - https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/
  - https://www.youtube.com/watch?v=98c8spD5k5s
  Альтернативная документация по поиску CPU проблем SQL Server
  
  Что проверить в первую очередь:
  - Конфигурация железа сервера.
  - Дисковая подсистема.
  - Свободное место.
  - Антивирус на сервере стоит?
  - БД (операции) не выполняются / выполняются, как часто.
  - Проверка целостности
  - Индексы перестройка
  - Обновление статистики
  - Сжатие (шринк)
  Общие рекомендации
  
  Электропитания — использовать «Высокая производительность»
  
  настройка кэширования записи на диск
  
  антивирус, — добавить папку SQL Server и файлов БД в исключения
  
  настройка настроены параметры параллелизма (cost threshold for parallelism, max degree of parallelism)
  
  настройка Hyper-Threading.
  
  Мониторинг SSMS — «Стандартные отчеты»
  
  «Стандартные отчеты» в пользовательском интерфейсе Management Studio
  
  SQL Server Management Studio предоставляет минимальный необходимый набор стандартных отчетов для получения информации в режиме пользовательского интерфейса.
  
  Доступ к этим отчетам может быть выполнен через «Обозреватель объектов» (Object explorer) → Правый клик мыши по базе данных → «Отчеты» (Reports) → «Стандартный отчет» (Standard reports)
  
  Перечень «Стандартные отчеты»:
  
  Перечень «Стандартные отчеты»:
  - Занято место на диске
  - Использование дисковой памяти верхними таблицами
  - Использование дисковой памяти таблицей
  - Использование дисковой памяти секцией
  - События резервного копирования и восстановления
  - Все транзакции
  - Все блокирующие транзакции
  - Самые продолжительные транзакции
  - Транзакции, блокирующие наибольшее кол-во транзакций при выполнении
  - Транзакции с наибольшим кол-вом блокировок
  - Статистика блокировки ресурсов по объектам
  - Статистика выполнения объектов
  - Журнал согласованности баз данных
  - Статистика использования индекса
  - Физическая статистика индекса
  - Журнал изменений схемы
  - Статистика пользователей
  - Перечень «Пользовательские отчеты»
  Мониторинг Activity Monitor — Монитор активности
  
  Открыть монитор активности CTRL+ALT+A или SSMS стандарт. панель инструментов значок.
  
  Монитор активности SQL Server 2008 объединяет данные о процессах, предоставляя наглядную информацию по выполняющимся и недавно выполнявшимся процессам.
  
  Монитор активности предлагает администратору раздел обзора, внешне похожий на Диспетчер задач Windows, а также компоненты детального просмотра отдельных процессов, ожидания ресурсов, ввода-вывода в файлы данных и последних ресурсоемких запросов.
  
  Мониторинг 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?
  
  Мониторинг (платный)
  
  (платный) от разных компаний:
  
  Idera — SQL Diagnostic Manager
  
  Red-Gate — SQL Monitor
  
  ApexSQL — ApexSQL Monitor
  
  Quest — Spotlight on SQL Server Enterprise
  
  SentryOne — SQL centry
  
  Так же — вариант мониторинга SQL Server на Zabbix.
  
  СКРИПТЫ — системные
  
  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
  
  sys.dm_exec_query_stats OUTER APPLY sys.dm_exec_query_pl
  
  3 — 100_3_sys.dm_exec_query_stats cpu-utilization
  
  sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_plan_attributes
  Похожие публикации:

Как посмотреть чем sql занимает процессор

Мониторинг использования ЦП

Связанный контент

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

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

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

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

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

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

Шаг 6. Изучение и устранение проблем с SARGability

Шаг 7. Отключение интенсивной трассировки

Шаг 8. Устранение спин-блокировки SOS_CACHESTORE

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

Шаг 10. Увеличение масштаба системы для использования дополнительных ЦП

См. также

Обратная связь

Как посмотреть чем sql занимает процессор

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

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

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

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

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

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

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

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

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

Мониторинг SSMS — «Стандартные отчеты»

Мониторинг Activity Monitor — Монитор активности

Мониторинг Reporting Services — Performance Dashboard Reports

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

СКРИПТЫ — системные

sp_who и sp_who2

1 — 100_1_sys.dm_exec_query_stats — most time cpu

3 — 100_3_sys.dm_exec_query_stats cpu-utilization

Добавить комментарий Отменить ответ