Поделиться через

Устранение проблем с подключением в 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 процентов, высокой загрузке ЦП способствует антивирусное ПО, другие драйвера или компоненты ОС. Свяжитесь со своим системным администратором, чтобы проанализировать причину этого поведения.

  • Панель мониторинга производительности. В СРЕДЕ SQL Server Management Studio щелкните правой кнопкой мыши <SQLServerInstance> и выберите панель мониторинга производительности стандартных отчетов>>.

    На панели мониторинга показан график использования ЦП системы с линейчатой диаграммой. Темный цвет указывает на использование ЦП ядра SQL Server, а более светлый цвет представляет общее использование ЦП операционной системы (см. условные обозначения на графике для справки). Нажмите кнопку циклического обновления или F5 , чтобы увидеть обновленное использование.

Шаг 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 может использовать поиск по индексу для ускорения выполнения запроса. Многие структуры запросов предотвращают возможность SARG и приводят к сканированию таблиц или индексов и высокой загрузке ЦП. Рассмотрим следующий запрос к базе данных AdventureWorks, где необходимо получить каждый элемент ProductNumber и применить к нему функциюSUBSTRING(), после чего сравнить его с буквенным значением строки. Как видите, необходимо сначала получить все строки таблицы, а затем применить функцию, прежде чем будет можно выполнить сравнение. Извлечение всех строк из таблицы означает сканирование таблицы или индекса, что приводит к более высокой загрузке ЦП.

SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE SUBSTRING(ProductNumber, 0, 4) =  'HN-'

Применение любой функции или вычисления к столбцам в предикате поиска обычно делает запрос недоступным для анализа и приводит к более высокому потреблению ЦП. Решения обычно включают творческое переписывание запросов, чтобы сделать SARG пригодным. Возможным решением для этого примера является такая переработка, при которой функция удаляется из предиката запроса, выполняется поиск в другом столбце и получаются те же результаты:

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

См. также