Краткое содержание:
Наблюдаем высокую загрузку CPU по счетчикам Processor Time на сервере СУБД c MS SQL ServerЧто делать?
Что требуется сделать
- Подключиться к серверу СУБД
- Запустить MS Sql Server Management Studio
- Выяснить, какие именно запросы создают нагрузку на CPU.
- Выяснить, какие именно информационные базы создают нагрузку найденными запросами. Выполнить запросы (см ниже)
- Проанализировать запросы
- Найти базу
- Найти запрос в sql
- Найти стек кода на встроенном языке, из которого выполняется запрос
- Если найденный запрос выполняется сейчас несколькими пользователями (в случае, если несколько сеансов приводят к проблеме за счет частого выполнения проблемных запросов)
- Найти соответствующие сеансы в консоли администрирования найденной базы по spid
- Записать номер сеанса и время начала сеанса
- Завершить найденные сеансы
- Если найденный запрос выполняется периодически множеством пользователей
- Понять причину неоптимальной работы запроса
- Исправить
- Проверить, создают ли наиболее часто выполняемые запросы наибольшую нагрузку на CPU
- Понять причину неоптимальной работы часто выполняемых запросов
- Исправить
- Получить индексы с высокими издержками при использовании
- Понять причину неоптимальной работы часто выполняемых запросов
- Исправить
- Убедиться, что нагрузка на диск упала
- Понять по журналу регистрации, что именно выполняли завершенные сеансы
Например, можно настроить технологический журнал с фильтрами только на один запрос. Может выглядеть так:
<?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>
<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;
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);
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;
[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;
[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;
[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
-- 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. Представление содержит по одной строке для каждой инструкции запроса в плане в кэше, а время жизни строк связано с самим планом. Когда план удаляется из кэша, соответствующие строки исключаются из представления.
 Примечание |
|---|
Начальный запрос представления 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
|
Время ЦП, затраченное на последнее выполнение плана, в микросекундах (но с точностью до миллисекунды).
|
min_worker_time
|
bigint
|
Минимальное время ЦП, когда-либо затраченное на выполнение плана, в микросекундах (но с точностью до миллисекунды).
|
max_worker_time
|
bigint
|
Максимальное время ЦП, когда-либо затраченное на выполнение плана, в микросекундах (но с точностью до миллисекунды).
|
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.
Комментариев нет:
Отправить комментарий