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

  • Статья

Применяется к: 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

  • Для сбора сведений на протяжении 60 секундного интервала можно использовать следующий сценарий PowerShell:

    $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. Добавление отсутствующих индексов

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

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

    -- 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

  2. Просмотрите планы выполнения для идентифицированных запросов и настройте запрос, внося необходимые изменения. На следующем снимке экрана показан пример, в котором SQL Server будет указывать на отсутствующий индекс для запроса. Щелкните правой кнопкой мыши часть "Отсутствующий индекс" в плане запроса, а затем выберите "Отсутствующие сведения об индексе", чтобы создать индекс в другом окне SQL Server Management Studio.

    Снимок экрана: план выполнения с отсутствующим индексом.

  3. Используйте следующий запрос, чтобы проверить отсутствие индексов и применить все рекомендуемые индексы с высокими значениями мер улучшения. Начните с 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 ), чтобы определить, какой запрос может вызвать проблему, а затем устранить этот отдельный запрос или запросы.

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

  • Используйте указание запроса RECOMPILE. Вы можете добавить указание запроса RECOMPILE к одному или нескольким запросам с высокой загрузкой ЦП, которые определены на шаге 2. Это указание помогает сбалансировать небольшое увеличение загрузки ЦП при компиляции с более оптимальной производительностью при каждом выполнении запроса. Дополнительные сведения см. в разделах "Параметры" и "Повторное использование плана выполнения", "Конфиденциальность параметров" и "Указание запроса RECOMPILE".

    Ниже приведен пример применения этого указания к запросу.

    SELECT * FROM Person.Person 
WHERE LastName = 'Wood'
OPTION (RECOMPILE)

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

    DECLARE @LastName Name = 'Frintu'
SELECT FirstName, LastName FROM Person.Person 
WHERE LastName = @LastName
OPTION (OPTIMIZE FOR (@LastName = 'Wood'))

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

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

    SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

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

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

    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_BLOCKALLOCPARTIALLIST состязания за спин-блокировку на компьютерах с большим объемом памяти

Если ваш экземпляр SQL Server испытывает случайный высокий уровень загрузки ЦП из-за состязания за спин-блокировкуSOS_BLOCKALLOCPARTIALLIST, рекомендуется применить накопительный пакет обновления 21 для SQL Server 2019 года. Дополнительные сведения о том, как решить эту проблему, см. в справочнике по ошибкам 2410400 и DBCC DROPCLEANBUFFERS , которые предоставляют временное устранение неполадок.

Высокая загрузка ЦП из-за состязания за спин-блокировку на XVB_list на высокопроизводительных компьютерах

Если в вашем экземпляре SQL Server возникает сценарий с высокой загрузкой ЦП, вызванный состязанием за спин-блокировку на XVB_LIST компьютерах с высокой конфигурацией (высокопроизводительных системах с большим количеством процессоров нового поколения), включите флаг трассировки TF8102 вместе с TF8101.

Примечание.

Высокая загрузка ЦП может быть результатом состязания за спин-блокировку во многих других типах спин-блокировок. Дополнительные сведения о спин-блокировки см. в статье Диагностика и разрешение конфликтов спин-блокировок в 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

См. также