пятница, 13 ноября 2015 г.

Высокая загрузка 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%"/>
которые будут включать имена таблиц в найденном вами на предыдущем шаге запросе.
Если всё аккуратно сделаете, то в полученном технологическом журнале запрос у вас будет только тот, который нужен.
Журнал получится небольшим.
Собственно стек из кода на встроенном языке будет сразу в конце события с запросом.
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;
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);
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


sys.dm_exec_query_stats (Transact-SQL)

Возвращает суммарную статистику производительности для кэшированных планов запросов в SQL Server. Представление содержит по одной строке для каждой инструкции запроса в плане в кэше, а время жизни строк связано с самим планом. Когда план удаляется из кэша, соответствующие строки исключаются из представления.
System_CAPS_noteПримечание
Начальный запрос представления sys.dm_exec_query_stats может выдавать неточные результаты, если на сервере выполняется рабочая нагрузка. Более точные результаты могут быть получены при повторном выполнении запроса.
Область применения: SQL Server (от SQL Server 2008 до текущей версии), База данных SQL Azure.
Имя столбца
Тип данных
Описание
sql_handle
varbinary(64)
Токен, ссылающийся на пакет или хранимую процедуру, частью которой является запрос.
Столбец sql_handle вместе со столбцами statement_start_offset и statement_end_offset может использоваться для получения SQL-текста запроса с помощью вызова функции динамического управления sys.dm_exec_sql_text.
statement_start_offset
int
Начальная позиция запроса, описываемого строкой, в соответствующем тексте пакета или сохраняемом объекте, в байтах, начиная с 0.
statement_end_offset
int
Конечная позиция запроса, описываемого строкой, в соответствующем тексте пакета или сохраняемом объекте, в байтах, начиная с 0. В версиях до SQL Server 2014 значение -1 обозначает конец пакета. Конечные комментарии больше не включаются.
plan_generation_num
bigint
Порядковый номер, который может использоваться для проведения различия между экземплярами планов после рекомпиляции.
plan_handle
varbinary(64)
Токен, ссылающийся на скомпилированный план, частью которого является запрос. Это значение можно передать функции динамического управления sys.dm_exec_query_plan для получения плана запроса.
Значение всегда равно 0x000, если скомпилированная в собственном коде хранимая процедура запрашивает оптимизированную для памяти таблицу.
creation_time
datetime
Время компиляции плана.
last_execution_time
datetime
Время начала последнего выполнения плана.
execution_count
bigint
Количество выполнений плана с момента последней компиляции.
total_worker_time
bigint
Общее время ЦП, затраченное на выполнение плана с момента компиляции, в микросекундах (но с точностью до миллисекунды).
Для скомпилированных в собственном коде хранимых процедур функция total_worker_timeможет быть неточной, если за время меньше миллисекунды выполняется большое количество хранимых процедур.
last_worker_time
bigint
Время ЦП, затраченное на последнее выполнение плана, в микросекундах (но с точностью до миллисекунды). 1
min_worker_time
bigint
Минимальное время ЦП, когда-либо затраченное на выполнение плана, в микросекундах (но с точностью до миллисекунды). 1
max_worker_time
bigint
Максимальное время ЦП, когда-либо затраченное на выполнение плана, в микросекундах (но с точностью до миллисекунды). 1
total_physical_reads
bigint
Общее количество операций физического считывания при выполнении плана с момента его компиляции.
Значение всегда равно 0 при запросе оптимизированной для памяти таблицы.
last_physical_reads
bigint
Количество операций физического считывания за время последнего выполнения плана.
Значение всегда равно 0 при запросе оптимизированной для памяти таблицы.
min_physical_reads
bigint
Минимальное количество операций физического считывания за одно выполнение плана.
Значение всегда равно 0 при запросе оптимизированной для памяти таблицы.
max_physical_reads
bigint
Максимальное количество операций физического считывания за одно выполнение плана.
Значение всегда равно 0 при запросе оптимизированной для памяти таблицы.
total_logical_writes
bigint
Общее количество операций логической записи при выполнении плана с момента его компиляции.
Значение всегда равно 0 при запросе оптимизированной для памяти таблицы.
last_logical_writes
bigint
Количество страниц в буферном пуле, загрязненных во время последнего выполнения плана. Если страница уже является «грязной» (т. е. измененной), операции записи не учитываются.
Значение всегда равно 0 при запросе оптимизированной для памяти таблицы.
min_logical_writes
bigint
Минимальное количество операций логической записи за одно выполнение плана.
Значение всегда равно 0 при запросе оптимизированной для памяти таблицы.
max_logical_writes
bigint
Максимальное количество операций логической записи за одно выполнение плана.
Значение всегда равно 0 при запросе оптимизированной для памяти таблицы.
total_logical_reads
bigint
Общее количество операций логического считывания при выполнении плана с момента его компиляции.
Значение всегда равно 0 при запросе оптимизированной для памяти таблицы.
last_logical_reads
bigint
Количество операций логического считывания за время последнего выполнения плана.
Значение всегда равно 0 при запросе оптимизированной для памяти таблицы.
min_logical_reads
bigint
Минимальное количество операций логического считывания за одно выполнение плана.
Значение всегда равно 0 при запросе оптимизированной для памяти таблицы.
max_logical_reads
bigint
Максимальное количество операций логического считывания за одно выполнение плана.
Значение всегда равно 0 при запросе оптимизированной для памяти таблицы.
total_clr_time
bigint
Общее время, затраченное на выполнение плана внутри объектов Корпорация Майкрософт.NET Framework среды CLR с момента его компиляции, в микросекундах (но с точностью до миллисекунды). Объекты среды CLR могут быть хранимыми процедурами, функциями, триггерами, типами и статистическими выражениями.
last_clr_time
bigint
Время, затраченное на последнее выполнение плана внутри объектов .NET Framework среды CLR в микросекундах (но с точностью до миллисекунды). Объекты среды CLR могут быть хранимыми процедурами, функциями, триггерами, типами и статистическими выражениями.
min_clr_time
bigint
Минимальное время, когда-либо затраченное на выполнение плана внутри объектов .NET Framework среды CLR, в микросекундах (но с точностью до миллисекунды). Объекты среды CLR могут быть хранимыми процедурами, функциями, триггерами, типами и статистическими выражениями.
max_clr_time
bigint
Максимальное время, когда-либо затраченное на выполнение плана внутри среды CLR .NET Framework, в микросекундах (но с точностью до миллисекунды). Объекты среды CLR могут быть хранимыми процедурами, функциями, триггерами, типами и статистическими выражениями.
total_elapsed_time
bigint
Общее время, затраченное на выполнение плана, в микросекундах (но с точностью до миллисекунды).
last_elapsed_time
bigint
Время, затраченное на последнее выполнение плана, в микросекундах (но с точностью до миллисекунды).
min_elapsed_time
bigint
Минимальное время, когда-либо затраченное на выполнение плана, в микросекундах (но с точностью до миллисекунды).
max_elapsed_time
bigint
Максимальное время, когда-либо затраченное на выполнение плана, в микросекундах (но с точностью до миллисекунды).
query_hash
Binary(8)
Двоичное хэш-значение рассчитывается для запроса и используется для идентификации запросов с аналогичной логикой. Можно использовать хэш запроса для определения использования статистических ресурсов для запросов, которые отличаются только своими литеральными значениями.
query_plan_hash
binary(8)
Двоичное хэш-значение рассчитывается для плана выполнения запроса и используется для идентификации аналогичных планов выполнения запросов. Можно использовать хэш плана запроса для нахождения совокупной стоимости запросов со схожими планами выполнения.
Значение всегда равно 0x000, если скомпилированная в собственном коде хранимая процедура запрашивает оптимизированную для памяти таблицу.
total_rows
bigint
Общее число строк, возвращаемых запросом. Не может иметь значение null.
Значение всегда равно 0, если скомпилированная в собственном коде хранимая процедура запрашивает оптимизированную для памяти таблицу.
last_rows
bigint
Число строк, возвращенных последним выполнением запроса. Не может иметь значение null.
Значение всегда равно 0, если скомпилированная в собственном коде хранимая процедура запрашивает оптимизированную для памяти таблицу.
min_rows
bigint
Минимальное число строк, возвращенных запросом за столько раз, сколько выполнялся этот план с момента последней компиляции. Не может иметь значение null.
Значение всегда равно 0, если скомпилированная в собственном коде хранимая процедура запрашивает оптимизированную для памяти таблицу.
max_rows
bigint
Максимальное число строк, возвращенных запросом за столько раз, сколько выполнялся этот план с момента последней компиляции. Не может иметь значение null.
Значение всегда равно 0, если скомпилированная в собственном коде хранимая процедура запрашивает оптимизированную для памяти таблицу.
statement_sql_handle
varbinary(64)
Область применения: начиная с SQL Server 2014 до SQL Server 2014.
Зарезервировано для последующего использования.
statement_context_id
bigint
Область применения: начиная с SQL Server 2014 до SQL Server 2014.
Зарезервировано для последующего использования.
1 Если сбор статистики включен, то накопленное время рабочей роли для скомпилированных в собственном коде хранимых процедур указывается в миллисекундах. Если запрос выполняется за время меньше миллисекунды, это значение будет равно 0.

Комментариев нет:

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