phone +7 (495) 66 014 66

× Общий раздел

Высокая загрузка CPU на сервере СУБД MS SQL Server

5 года 7 мес. назад #4926 от Дмитрий
Высокая загрузка CPU на сервере СУБД MS SQL Server
Краткое содержание:
Наблюдаем высокую загрузку CPU по счетчикам Processor Time на сервере СУБД c MS SQL Server
Что делать?
Что требуется сделать
1. Подключиться к серверу СУБД
2. Запустить MS Sql Server Management Studio
3. Выяснить, какие именно запросы создают нагрузку на CPU.
4. Выяснить, какие именно информационные базы создают нагрузку найденными запросами. Выполнить запросы (см ниже)
5. Проанализировать запросы
1. Найти базу
2. Найти запрос в sql
3. Найти стек кода на встроенном языке, из которого выполняется запрос
6. Если найденный запрос выполняется сейчас несколькими пользователями (в случае, если несколько сеансов приводят к проблеме за счет частого выполнения проблемных запросов)
1. Найти соответствующие сеансы в консоли администрирования найденной базы по spid
2. Записать номер сеанса и время начала сеанса
3. Завершить найденные сеансы
7. Если найденный запрос выполняется периодически множеством пользователей
1. Понять причину неоптимальной работы запроса
2. Исправить
8. Проверить, создают ли наиболее часто выполняемые запросы наибольшую нагрузку на CPU
1. Понять причину неоптимальной работы часто выполняемых запросов
2. Исправить
9. Получить индексы с высокими издержками при использовании
1. Понять причину неоптимальной работы часто выполняемых запросов
2. Исправить
10. Убедиться, что нагрузка на диск упала
11. Понять по журналу регистрации, что именно выполняли завершенные сеансы
Как найти самый тяжелый (или несколько тяжелых) запрос в коде конфигурации.
Например, можно настроить технологический журнал с фильтрами только на один запрос. Может выглядеть так:
<?xml version="1.0" encoding="UTF-8"?>
<config xmlns=" v8.1c.ru/v8/tech-log »»">
<log location="С:\Sql_Reads" history="2">
<event>
<eq property="Name" value="DBMSSQL"/>
<like property="Sql" value="%Reference5774%"/>
<like property="Sql" value="%SELECT TOP%"/>
</event>
<property name="all"/>
</log>
<plansql/>
</config>

Смысл в том, чтобы указать такие фильтры
<like property="Sql" value="%Reference5774%"/>

которые будут включать имена таблиц в найденном вами на предыдущем шаге запросе.
Если всё аккуратно сделаете, то в полученном технологическом журнале запрос у вас будет только тот, который нужен.
Журнал получится небольшим.
Собственно стек из кода на встроенном языке будет сразу в конце события с запросом.

Top запросов, создающих нагрузку на CPU на сервере СУБД за последний час
SELECT
SUM(qs.max_elapsed_time) as elapsed_time,
SUM(qs.total_worker_time) as worker_time
into T1 FROM (
       select top 100000       *       from
       sys.dm_exec_query_stats qs
       where qs.last_execution_time > (CURRENT_TIMESTAMP - '01:00:00.000')
       order by qs.total_worker_time desc
       --order by qs.max_elapsed_time desc
) as qs;
select top 100
(qs.max_elapsed_time) as elapsed_time,
(qs.total_worker_time) as worker_time,
qp.query_plan,
st.text,
dtb.name,
qs.*,
st.dbid
INTO T2
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
left outer join sys.databases as dtb on st.dbid = dtb.database_id
where qs.last_execution_time > (CURRENT_TIMESTAMP - '01:00:00.000')
order by qs.total_worker_time desc
--order by qs.max_elapsed_time desc;
select
(T2.elapsed_time*100/T1.elapsed_time) as percent_elapsed_time,
(T2.total_worker_time*100/T1.worker_time) as percent_worker_time,
T2.*
from
T2 as T2
INNER JOIN T1 as T1
ON 1=1
order by T2.total_worker_time desc
--order by T2.max_elapsed_time desc;
drop table T2;
drop table T1;

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

5 года 7 мес. назад #4927 от Дмитрий
Нагрузка на CPU по базам
WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
             FROM sys.dm_exec_plan_attributes(qs.plan_handle)
             WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
      DatabaseName, [CPU_Time_Ms],
      CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

Наибольшая нагрузка на CPU
SELECT TOP 10 
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Inpidual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
(CASE WHEN qs.statement_end_offset = -1 
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
ELSE qs.statement_end_offset END - 
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

Наиболее часто выполняемые запросы
SELECT TOP 10 
[Execution count] = execution_count
,[Inpidual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
        (CASE WHEN qs.statement_end_offset = -1 
           THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
         ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;

Текущая статистика по задержкам (расшифровка задержек)
SELECT TOP 10
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 
             / SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' 
ORDER BY wait_time_ms DESC;

Индексы с высокими издержками при использовании
-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the following step.
SELECT TOP 1
       [Maintenance cost]  = (user_updates + system_updates)
       ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
       ,DatabaseName = DB_NAME()
       ,TableName = OBJECT_NAME(s.[object_id])
       ,IndexName = i.name
INTO #TempMaintenanceCost
FROM   sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id]
   AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
   AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
   AND (user_updates + system_updates) > 0 -- Only report on active rows.
   AND s.[object_id] = -999  -- Dummy value to get table structure.;
-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?];
-- Table already exists.
INSERT INTO #TempMaintenanceCost
SELECT TOP 10
       [Maintenance cost]  = (user_updates + system_updates)
       ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
       ,DatabaseName = DB_NAME()
       ,TableName = OBJECT_NAME(s.[object_id])
       ,IndexName = i.name
FROM   sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id]
   AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
   AND i.name IS NOT NULL    -- Ignore HEAP indexes.
   AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
   AND (user_updates + system_updates) > 0 -- Only report on active rows.
ORDER BY [Maintenance cost]  DESC;'
-- Select records.
SELECT TOP 10 * FROM #TempMaintenanceCost
ORDER BY [Maintenance cost]  DESC
-- Tidy up.
DROP TABLE #TempMaintenanceCost

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

Время создания страницы: 0.055 секунд