Мониторим тяжелые запросы, классифицируем результаты

Дальнейшее развитие инструментария для мониторинга запросов.

В предыдущей статье Мониторим тяжелые запросы описан инструментарий по автоматическому сбору и накоплению информации о тяжелых запросах. Теперь нам нужно классифицировать запросы. Такая классификация позволит ответить на следующие вопросы:

Какова "сезонность" появления в топе тех или иных запросов
Все ли тяжелые запросы нам известны
Как меняется картина создаваемой нагрузки запросом после его тюнинга или соответствующего тюнинга БД (например, индексация какого-то поля).

Если БД для статистики уже создана на основании предыдущей статьи, то ее доработки можно будет применять инкрементально, по мере их… гм… вкрапления;) в публикации.

Также, в конце приведу аккумулированные скрипты — для обновления существующей БД, и полные, для создания "с нуля".

Классификатор запросов

Для классификации используется сопоставление текстов первичных запросов с "масками" каких-то известных нам запросов. Маска — это текстовка запроса, используемая для оператора LIKE.

Классификатор запросов организуем в новой таблице queries.

Скрипт создания таблицы queries

/****** Object:  Table [dbo].[queries]    Script Date: 07.05.2024 9:03:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[queries](
[qr_key] [int] NOT NULL,
[description] [nvarchar](150) NULL,
[mask] [nvarchar](max) NULL,
[related_qr_key] [int] NULL,
CONSTRAINT [PK_queries] PRIMARY KEY CLUSTERED
(
[qr_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Поля таблицы:

qr_key — идентификатор запроса, ключевое поле таблицы
description — произвольное описание
mask — маска для сопоставления полного текста запроса и записи из queries
related_qr_key — идентификатор qr_key какой-либо другой строки той же таблицы queries (см. подробности ниже)

Текстовки полных запросов одного и того же действия могут различаться — слегка или совсем. Например, какой-то запрос дорабатывается конфигурацией динамически, с добавлением/удалением условий запроса. Тогда для такого запроса можно ввести маску с минимальным набором применяемых условий.

В других случаях запрос полностью рефакторится — с декомпозицией, изменением логики и проч. Но (!) нам хотелось бы видеть общую картину нагрузки, до и после изменений. И тогда вводим две записи в queries. В первой записи вводим идентификатор qr_key (какое-то значение [ID1]), описание запроса description и маску для запроса в редакции ДО его изменений. Во второй записи также вводим идентификатор qr_key (какое-то значение [ID2]), описание запроса description, маску для запроса в редакции ПОСЛЕ его изменений и указываем значение поля related_qr_key, равное введенному qr_key для первой записи (значение [ID1]). Этим получаем привязку статистики обоих запросов к одной записи классификатора (где qr_key = [ID1]) — так работает процедура сопоставления.

Также свяжем таблицы queries и top_cpu_usage внешним ключом и добавим индексы.

Скрипт создания внешнего колюча и доп.индексов

ALTER TABLE [dbo].[top_cpu_usage]  WITH CHECK ADD  CONSTRAINT [FK_top_cpu_usage_queries] FOREIGN KEY([qr_id])
REFERENCES [dbo].[queries] ([qr_key])
GO

ALTER TABLE [dbo].[top_cpu_usage] CHECK CONSTRAINT [FK_top_cpu_usage_queries]
GO

/****** Object:  Index [idx_top_cpu_usage__qr_id]    Script Date: 08.05.2024 14:04:45 ******/
CREATE NONCLUSTERED INDEX [idx_top_cpu_usage__qr_id] ON [dbo].[top_cpu_usage]
(
[qr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_top_cpu_usage__period]    Script Date: 08.05.2024 14:05:17 ******/
CREATE NONCLUSTERED INDEX [IX_top_cpu_usage__period] ON [dbo].[top_cpu_usage]
(
[period] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

В классификатор нужно сразу добавить "предопределенное значение" — ввести запись с идентификатором qr_key = -1 и описанием "Не определено", чтобы с этой записью связывать запросы, которые не были распознаны по маскам.

Механизм сопоставления

Теперь нам нужно сопоставить данные таблиц статистики top_cpu_usage и классификатора queries. Для этого в queries мы предусмотрели маску запроса (поле mask), а в таблице top_cpu_usage уже изначально присутствует поле для сопоставленного идентификатора запроса (qr_id). Но соединять эти таблицы напрямую через like — это затратное мероприятие. Группировать или отбирать top_cpu_usage по текстовкам исходного запроса — тоже накладно. Поэтому будем также работать с хешами текстовок запросов.

В таблице top_cpu_usage создадим новое поле qr_text_hash и проиндексируем его.

Скрипт создания поля и индекса

 ALTER TABLE top_cpu_usage
ADD qr_text_hash binary(20) null

GO

/****** Object:  Index [idx__top_cpu_usage__qr_text_hash]    Script Date: 07.05.2024 17:30:38 ******/
CREATE NONCLUSTERED INDEX [idx__top_cpu_usage__qr_text_hash] ON [dbo].[top_cpu_usage]
(
[qr_text_hash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Хеши произвольных текстовок будем получать с помощью нами созданной скалярной функции MD5Hash.

Скрипт создания MD5Hash

/****** Object:  UserDefinedFunction [dbo].[MD5Hash]    Script Date: 07.05.2024 17:27:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Dolinin, Permenergosbyt
-- Create date: 26.04.19
-- Description: Returns MD5-hash of input string
-- =============================================
CREATE FUNCTION [dbo].[MD5Hash]
(
-- Add the parameters for the function here
@txt text
)
RETURNS binary(16)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar binary(16)

-- Add the T-SQL statements to compute the return value here
SET @ResultVar = HASHBYTES('MD5', cast(@txt as varchar(8000)))

-- Return the result of the function
RETURN @ResultVar

END

GO

Доработаем процедуру сохранения текущей статистики, чтобы она также сразу сохраняла хеши текстовок запросов.

Скрипт изменения sp_store_top_cpu_usage_data

/****** Object:  StoredProcedure [dbo].[sp_store_top_cpu_usage_data]    Script Date: 07.05.2024 20:59:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Dolinin, Permenergosbyt
-- Create date: 05.04.19
-- Description: This proc collect info about top CPU usages
-- =============================================
ALTER PROCEDURE [dbo].[sp_store_top_cpu_usage_data]
AS
BEGIN
SET NOCOUNT ON;

declare @depth datetime = '01:00:00.000'
declare @period datetime = getdate()
declare @count_of_result int = 100
declare @count_of_primary_data int = 10000

SELECT
*
into #T0 FROM (
select top (@count_of_primary_data)
*
from
sys.dm_exec_query_stats qs
where qs.last_execution_time > (CURRENT_TIMESTAMP - @depth)
order by qs.last_execution_time desc
) as qs
;

SELECT
SUM(qs.max_elapsed_time) as elapsed_time,
SUM(qs.total_worker_time) as worker_time
into #T1 FROM #T0 qs

select
(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
#T0 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
order by qs.last_execution_time desc
;

declare
@rec_num int,
@percent_elapsed_time decimal(5, 1),
@percent_worker_time decimal(5, 1),
@elapsed_time bigint,
@worker_time bigint,
@query_plan xml,
@text nvarchar(max),
@sql_handle varbinary(64),
@statement_start_offset int,
@statement_end_offset int,
@plan_generation_num bigint,
@plan_handle varbinary(64),
@creation_time datetime,
@last_execution_time datetime,
@execution_count bigint,
@total_worker_time bigint,
@last_worker_time bigint,
@min_worker_time bigint,
@max_worker_time bigint,
@total_physical_reads bigint,
@last_physical_reads bigint,
@min_physical_reads bigint,
@max_physical_reads bigint,
@total_logical_writes bigint,
@last_logical_writes bigint,
@min_logical_writes bigint,
@max_logical_writes bigint,
@total_logical_reads bigint,
@last_logical_reads bigint,
@min_logical_reads bigint,
@max_logical_reads bigint,
@total_clr_time bigint,
@last_clr_time bigint,
@min_clr_time bigint,
@max_clr_time bigint,
@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),
@total_rows bigint,
@last_rows bigint,
@min_rows bigint,
@max_rows bigint,
@dbid int

declare cur cursor FORWARD_ONLY for
select top (@count_of_result)
try_cast(T2.elapsed_time*100/T1.elapsed_time as decimal(5, 1)) as percent_elapsed_time,
try_cast(T2.worker_time*100/T1.worker_time as decimal(5, 1)) as percent_worker_time,
T2.elapsed_time,
T2.worker_time,
T2.query_plan,
T2.text,
T2.sql_handle,
T2.statement_start_offset,
T2.statement_end_offset,
T2.plan_generation_num,
T2.plan_handle,
T2.creation_time,
T2.last_execution_time,
T2.execution_count,
T2.total_worker_time,
T2.last_worker_time,
T2.min_worker_time,
T2.max_worker_time,
T2.total_physical_reads,
T2.last_physical_reads,
T2.min_physical_reads,
T2.max_physical_reads,
T2.total_logical_writes,
T2.last_logical_writes,
T2.min_logical_writes,
T2.max_logical_writes,
T2.total_logical_reads,
T2.last_logical_reads,
T2.min_logical_reads,
T2.max_logical_reads,
T2.total_clr_time,
T2.last_clr_time,
T2.min_clr_time,
T2.max_clr_time,
T2.total_elapsed_time,
T2.last_elapsed_time,
T2.min_elapsed_time,
T2.max_elapsed_time,
T2.query_hash,
T2.query_plan_hash,
T2.total_rows,
T2.last_rows,
T2.min_rows,
T2.max_rows,
T2.dbid
from
#T2 as T2
INNER JOIN #T1 as T1
ON 1=1
order by T2.worker_time desc

open cur

fetch next from cur into
@percent_elapsed_time,
@percent_worker_time,
@elapsed_time,
@worker_time,
@query_plan,
@text,
@sql_handle,
@statement_start_offset,
@statement_end_offset,
@plan_generation_num,
@plan_handle,
@creation_time,
@last_execution_time,
@execution_count,
@total_worker_time,
@last_worker_time,
@min_worker_time,
@max_worker_time,
@total_physical_reads,
@last_physical_reads,
@min_physical_reads,
@max_physical_reads,
@total_logical_writes,
@last_logical_writes,
@min_logical_writes,
@max_logical_writes,
@total_logical_reads,
@last_logical_reads,
@min_logical_reads,
@max_logical_reads,
@total_clr_time,
@last_clr_time,
@min_clr_time,
@max_clr_time,
@total_elapsed_time,
@last_elapsed_time,
@min_elapsed_time,
@max_elapsed_time,
@query_hash,
@query_plan_hash,
@total_rows,
@last_rows,
@min_rows,
@max_rows,
@dbid
set @rec_num=1
WHILE @@FETCH_STATUS = 0
BEGIN

insert into prom_monitoring..top_cpu_usage(
period, rec_num,
percent_elapsed_time,
percent_worker_time,
elapsed_time,
worker_time,
query_plan,
text,
sql_handle,
statement_start_offset,
statement_end_offset,
plan_generation_num,
plan_handle,
creation_time,
last_execution_time,
execution_count,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_clr_time,
last_clr_time,
min_clr_time,
max_clr_time,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time,
query_hash,
query_plan_hash,
total_rows,
last_rows,
min_rows,
max_rows,
dbid,
qr_id,
qr_text_hash
)
values(
@period, @rec_num,
@percent_elapsed_time,
@percent_worker_time,
@elapsed_time,
@worker_time,
@query_plan,
@text,
@sql_handle,
@statement_start_offset,
@statement_end_offset,
@plan_generation_num,
@plan_handle,
@creation_time,
@last_execution_time,
@execution_count,
@total_worker_time,
@last_worker_time,
@min_worker_time,
@max_worker_time,
@total_physical_reads,
@last_physical_reads,
@min_physical_reads,
@max_physical_reads,
@total_logical_writes,
@last_logical_writes,
@min_logical_writes,
@max_logical_writes,
@total_logical_reads,
@last_logical_reads,
@min_logical_reads,
@max_logical_reads,
@total_clr_time,
@last_clr_time,
@min_clr_time,
@max_clr_time,
@total_elapsed_time,
@last_elapsed_time,
@min_elapsed_time,
@max_elapsed_time,
@query_hash,
@query_plan_hash,
@total_rows,
@last_rows,
@min_rows,
@max_rows,
@dbid,
-1,
dbo.MD5Hash(@text)
)
set @rec_num=@rec_num + 1

fetch next from cur into
@percent_elapsed_time,
@percent_worker_time,
@elapsed_time,
@worker_time,
@query_plan,
@text,
@sql_handle,
@statement_start_offset,
@statement_end_offset,
@plan_generation_num,
@plan_handle,
@creation_time,
@last_execution_time,
@execution_count,
@total_worker_time,
@last_worker_time,
@min_worker_time,
@max_worker_time,
@total_physical_reads,
@last_physical_reads,
@min_physical_reads,
@max_physical_reads,
@total_logical_writes,
@last_logical_writes,
@min_logical_writes,
@max_logical_writes,
@total_logical_reads,
@last_logical_reads,
@min_logical_reads,
@max_logical_reads,
@total_clr_time,
@last_clr_time,
@min_clr_time,
@max_clr_time,
@total_elapsed_time,
@last_elapsed_time,
@min_elapsed_time,
@max_elapsed_time,
@query_hash,
@query_plan_hash,
@total_rows,
@last_rows,
@min_rows,
@max_rows,
@dbid

END

CLOSE cur;
DEALLOCATE cur;

drop table #T2
;
drop table #T1
;
drop table #T0
;

END

GO

И вот, наконец, сама процедура sp_fill_qr_id_for_cpu_usage, выполняющая сопоставление запросов.

Скрипт создания процедуры sp_fill_qr_id_for_cpu_usage

/****** Object:  StoredProcedure [dbo].[sp_fill_qr_id_for_cpu_usage]    Script Date: 08.05.2024 14:12:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Dolinin, Permenergosbyt
-- Create date: 27.04.19
-- Description: Filling identifiers for determinating of kind queries
-- =============================================
CREATE PROCEDURE [dbo].[sp_fill_qr_id_for_cpu_usage]
-- Add the parameters for the stored procedure here
@only_empty bit = 1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

update top_cpu_usage
set qr_text_hash=dbo.MD5Hash([text])
where qr_text_hash is null

if @only_empty=0
begin
update top_cpu_usage
set qr_id = -1
end

update top_cpu_usage
set qr_id = -1
where qr_id is null

declare @hash binary(16)
declare @qr_text varchar(8000)
declare @qr_id int

declare cur cursor FORWARD_ONLY for
select u.qr_text_hash
from top_cpu_usage u
where coalesce(u.qr_id, -1)=-1
group by u.qr_text_hash

open cur

fetch next from cur into @hash

WHILE @@FETCH_STATUS = 0
BEGIN

set @qr_id = null
set @qr_text = null

select top 1 @qr_text = cast(text as varchar(8000))
from top_cpu_usage u
where u.qr_text_hash=@hash

if (not @qr_text is null)
begin
select top 1 @qr_id=coalesce(q.related_qr_key, q.qr_key)
from queries q
where @qr_text like cast(q.mask as varchar(8000))
order by q.qr_key

if (not @qr_id is null)
begin
update top_cpu_usage
set qr_id=@qr_id
where qr_text_hash=@hash
end

end

fetch next from cur into @hash

end

CLOSE cur
DEALLOCATE cur

END

GO

У процедуры есть единственный параметр @only_empty. В него передаем 0 или 1, соответственно, для перезаполнения всех идентификаторов запросов, либо, где идентификаторы не определены.

В задачу плана обслуживания, после выполнения процедуры заполнения новыми статистиками, добавляем код запуска сопоставления.

Скрипт задачи плана обслуживания

use prod_monitoring -- вместо prod_monitoring впишите название вашей БД сбора статистики
exec sp_store_top_cpu_usage_data
exec [dbo].[sp_fill_qr_id_for_cpu_usage] @only_empty = 1

Заполнение классификатора запросов

Ну да, теперь мы имеем все механизмы, чтобы распознавать и сопоставить запросы. Но как понять — откуда вообще генерятся те или иные запросы?… Как узнать, что какой-то маске соответствует какой-то метод конфигурации или выполняется какой-то макет СКД?…

Тут два путя — реверс-инжиниринг проблемного запроса и поиск через серверный технологический журнал.

Реверс-инжиниринг

В моей практике, это всегда входная точка для сопоставлений.

Использую специальную внешнюю обработку, заменяющую имена таблиц и полей БД на соответствующие аналоги метаданных. Эта внешка мне досталась через третьи-четвертые руки от каких-то подрядчиков. Написана в продвинутом стиле. Поэтому публиковать и раздавать ее не буду. Вероятно, аналоги такой внешки уже и есть на ИС. Особо не искал, т.к. лучшее — враг хорошего;)

В общем случае, все достаточно несложно при создании подобных обработок. Используем ПолучитьСтруктуруХраненияБазыДанных(…). Понятно, что имена таблиц у нас уникальны. Но еще и сквозь всей БД — уникальны имена полей. Поэтому обычный СтрЗаменить() хорошо сможет преобразовать запрос.

В итоге, из такого запроса (запрос 1) — получаем эдакий (запрос 2).

SQL-запрос и его реверс

-- Запрос 1
(@P1 numeric(10),@P2 datetime2(3),@P3 datetime2(3),@P4 datetime2(3),@P5 numeric(10),@P6 numeric(10),@P7 numeric(10),@P8 datetime2(3),@P9 datetime2(3),@P10 datetime2(3),@P11 varbinary(16))SELECT
T1._Fld611,
T2._Fld586,
T2._Fld587
FROM dbo._Document46_VT598 T1
LEFT OUTER JOIN dbo._Document46 T2
ON T1._Document46_IDRRef = T2._IDRRef
WHERE ((T2._Date_Time >= DATEADD(DAY,@P1 - 1,DATEADD(MONTH,CAST(DATEPART(MONTH,@P2) AS NUMERIC(4)) - 1,DATEADD(YEAR,CAST(DATEPART(YEAR,@P3) AS NUMERIC(4)) - 2000,@P4)))) AND (T2._Date_Time <= DATEADD(SECOND,-@P5,DATEADD(MONTH,@P6,DATEADD(DAY,@P7 - 1,DATEADD(MONTH,CAST(DATEPART(MONTH,@P8) AS NUMERIC(4)) - 1,DATEADD(YEAR,CAST(DATEPART(YEAR,@P9) AS NUMERIC(4)) - 2000,@P10))))))) AND (T1._Fld600RRef = @P11)
ORDER BY (T2._Fld587)

-- Запрос 2
(@P1 numeric(10),@P2 datetime2(3),@P3 datetime2(3),@P4 datetime2(3),@P5 numeric(10),@P6 numeric(10),@P7 numeric(10),@P8 datetime2(3),@P9 datetime2(3),@P10 datetime2(3),@P11 varbinary(16))ВЫБРАТЬ
T1.ПолныйРасход,
T2.ДатаС,
T2.ДатаПо
ИЗ dbo.Документ.РасчетЭлПотребления.ПоказанияСчетчиков T1
ЛЕВОЕ СОЕДИНЕНИЕ dbo.Документ.РасчетЭлПотребления T2
ПО T1.Документ.РасчетЭлПотребления_Ссылка = T2._Ссылка
ГДЕ ((T2._Дата >= DATEADD(DAY,@P1 - 1,DATEADD(MONTH,CAST(DATEPART(MONTH,@P2) КАК NUMERIC(4)) - 1,DATEADD(YEAR,CAST(DATEPART(YEAR,@P3) КАК NUMERIC(4)) - 2000,@P4)))) И (T2._Дата <= DATEADD(SECOND,-@P5,DATEADD(MONTH,@P6,DATEADD(DAY,@P7 - 1,DATEADD(MONTH,CAST(DATEPART(MONTH,@P8) КАК NUMERIC(4)) - 1,DATEADD(YEAR,CAST(DATEPART(YEAR,@P9) КАК NUMERIC(4)) - 2000,@P10))))))) И (T1.ТочкаУчета = @P11)
УПОРЯДОЧИТЬ ПО (T2.ДатаПо)

Если запрос достаточно специфичен (содержит какие-то редко используемые таблицы или поля), то его ищем через глобальный поиск. Ну а когда найти не получается, тогда переходим ко второму способу — ищем с помощью ТЖ.

Поиск через серверный технологический журнал

Настраиваем logcfg.xml примерно так

<config xmlns="http://v8.1c.ru/v8/tech-log">
<dump location="E:1C_INFODumps" create="1" type="2"/>
<log location="E:1C_INFOTechLogs" history="1">
<event>
<eq property="name" value="DBMSSQL"/>
<like property="sql" value="%SELECT%Fld120,%IDRRef,%Description,%ISNULL(CAST(%Fld1031Balance%AS NUMERIC%,%ISNULL(CAST(%Fld985Turnover_ AS NUMERIC%,%ISNULL(CAST(%Fld985Turnover%AS NUMERIC%,%ISNULL(%)%FROM %Reference5%WHERE%Fld139RRef =%ORDER BY%Fld120%"/>
</event>
<property name="all"/>
</log>
</config>

В моем примере, возможно, я перемудрил с маской запроса — навставлял много "%". Но результат был достигнут.

Найденный код

18:55.932002-1530975,DBMSSQL,4,process=rphost,p:processName=es_prom,t:clientID=334,t:applicationName=1CV8,t:computerName=upr-term,t:connectID=81808,SessionID=99,Usr=Мер Е.И.,Trans=0,dbpid=67,Sql='SELECT
T1._Fld120,
T1._IDRRef,
T1._Description,
ISNULL(CAST(T2.Fld1031Balance_ AS NUMERIC(37, 2)),0.0),
ISNULL(CAST(T6.Fld985Turnover_ AS NUMERIC(21, 2)),0.0),
ISNULL(CAST(T8.Fld985Turnover_ AS NUMERIC(27, 2)),0.0),
ISNULL(T10.Q_001_F_001_,?)
FROM dbo._Reference5 T1
LEFT OUTER JOIN (SELECT
T3.Fld1026RRef AS Fld1026RRef,
CAST(SUM(T3.Fld1031Balance_) AS NUMERIC(38, 8)) AS Fld1031Balance_
FROM (SELECT
T4._Fld1026RRef AS Fld1026RRef,
CAST(SUM(T4._Fld1031) AS NUMERIC(37, 8)) AS Fld1031Balance_
FROM dbo._AccumRgT1036 T4
WHERE T4._Period = ? AND (((T4._Fld1025RRef = ?) AND (T4._Fld3182RRef <> ?))) AND (T4._Fld1031 <> ?) AND (T4._Fld1031 <> ?)
GROUP BY T4._Fld1026RRef
HAVING (CAST(SUM(T4._Fld1031) AS NUMERIC(37, 8))) <> 0.0
UNION ALL SELECT
T5._Fld1026RRef AS Fld1026RRef,
CAST(CAST(SUM(CASE WHEN T5._RecordKind = 0.0 THEN -T5._Fld1031 ELSE T5._Fld1031 END) AS NUMERIC(31, 8)) AS NUMERIC(31, 2)) AS Fld1031Balance_
FROM dbo._AccumRg1023 T5
WHERE T5._Period >= ? AND T5._Period < ? AND T5._Active = 0x01 AND (((T5._Fld1025RRef = ?) AND (T5._Fld3182RRef <> ?)))
GROUP BY T5._Fld1026RRef
HAVING (CAST(CAST(SUM(CASE WHEN T5._RecordKind = 0.0 THEN -T5._Fld1031 ELSE T5._Fld1031 END) AS NUMERIC(31, 8)) AS NUMERIC(31, 2))) <> 0.0) T3
GROUP BY T3.Fld1026RRef
HAVING (CAST(SUM(T3.Fld1031Balance_) AS NUMERIC(38, 8))) <> 0.0) T2
ON (T1._IDRRef = T2.Fld1026RRef)
LEFT OUTER JOIN (SELECT
T7._Fld982RRef AS Fld982RRef,
CAST(SUM(T7._Fld985) AS NUMERIC(27, 8)) AS Fld985Turnover_
FROM dbo._AccumRg981 T7
WHERE T7._Period >= ? AND T7._Period <= ? AND T7._Active = 0x01 AND ((T7._Fld984RRef = ?))
GROUP BY T7._Fld982RRef
HAVING (CAST(SUM(T7._Fld985) AS NUMERIC(27, 8))) <> 0.0) T6
ON (T1._IDRRef = T6.Fld982RRef)
LEFT OUTER JOIN (SELECT
T9._Fld982RRef AS Fld982RRef,
CAST(SUM(T9._Fld985) AS NUMERIC(33, 8)) AS Fld985Turnover_
FROM dbo._AccumRgTn995 T9
WHERE T9._Period >= ? AND T9._Period < ? AND ((T9._Fld984RRef = ?)) AND (T9._Fld985 <> ?)
GROUP BY T9._Fld982RRef
HAVING (CAST(SUM(T9._Fld985) AS NUMERIC(33, 8))) <> 0.0) T8
ON (T1._IDRRef = T8.Fld982RRef)
LEFT OUTER JOIN (SELECT
T11._Fld585RRef AS Q_001_F_000RRef,
MAX(T11._Date_Time) AS Q_001_F_001_
FROM dbo._Document46 T11
WHERE (T11._Fld595RRef = ?) AND (T11._Fld1543RRef = ?)
GROUP BY T11._Fld585RRef) T10
ON (T10.Q_001_F_000RRef = T1._IDRRef)
WHERE (T1._Fld139RRef = ?)
ORDER BY (T1._Fld120)
p_0: 17530101000000
p_1: 39991101000000
p_2: 0xB58A000EA6447A5D11DAEBCC4C466D62
p_3: 0x87E933DAB071671E4FCABF1E470F9F79
p_4: 0N
p_5: 0N
p_6: 20240508235959
p_7: 39991101000000
p_8: 0xB58A000EA6447A5D11DAEBCC4C466D62
p_9: 0x87E933DAB071671E4FCABF1E470F9F79
p_10: 20240501000000
p_11: 20240508235959
p_12: 0xB58A000EA6447A5D11DAEBCC4C466D62
p_13: 20240401000000
p_14: 20240501000000
p_15: 0xB58A000EA6447A5D11DAEBCC4C466D62
p_16: 0N
p_17: 0xB58A000EA6447A5D11DAEBCC4C466D62
p_18: 0xB8D81959996D07DA487C7451D4C02F79
p_19: 0xB58A000EA6447A5D11DAEBCC4C466D62
',Rows=10505,RowsAffected=-1
18:56.010008-0,Context,2,process=rphost,p:processName=es_prom,t:clientID=334,t:applicationName=1CV8,t:computerName=upr-term,t:connectID=81808,SessionID=99,Usr=Мер Е.И.,Context='
Обработка.АРМ_Диспетчер.Форма.ОсновнаяФорма.Форма : 155 : ЗаполнитьДанные_Уведомления();
Обработка.АРМ_Диспетчер.Форма.ОсновнаяФорма.Форма : 252 : Выборка = Запрос.Выполнить().Выбрать();'

Визуализация

Хранящуюся статистику просматриваем с помощью MS PowerBI Desktop. Видим такую вот картинку.

Графики нагрузки

Кстати, визуализируем не напрямую данные таблицы top_cpu_usage, а используем специально созданную несложную вьюшку, в которой добавлено поле текстового описания запроса.

Скрипт создания view vw_top_cpu_usage

/****** Object:  View [dbo].[vw_top_cpu_usage]    Script Date: 08.05.2024 13:28:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_top_cpu_usage]
AS
SELECT        dbo.top_cpu_usage.period, dbo.top_cpu_usage.rec_num, dbo.top_cpu_usage.percent_elapsed_time, dbo.top_cpu_usage.percent_worker_time,
dbo.top_cpu_usage.elapsed_time, dbo.top_cpu_usage.worker_time, dbo.top_cpu_usage.query_plan, dbo.top_cpu_usage.text, dbo.top_cpu_usage.sql_handle,
dbo.top_cpu_usage.statement_start_offset, dbo.top_cpu_usage.statement_end_offset, dbo.top_cpu_usage.plan_generation_num, dbo.top_cpu_usage.plan_handle,
dbo.top_cpu_usage.creation_time, dbo.top_cpu_usage.last_execution_time, dbo.top_cpu_usage.execution_count, dbo.top_cpu_usage.total_worker_time,
dbo.top_cpu_usage.last_worker_time, dbo.top_cpu_usage.min_worker_time, dbo.top_cpu_usage.max_worker_time, dbo.top_cpu_usage.total_physical_reads,
dbo.top_cpu_usage.last_physical_reads, dbo.top_cpu_usage.min_physical_reads, dbo.top_cpu_usage.max_physical_reads,
dbo.top_cpu_usage.total_logical_writes, dbo.top_cpu_usage.last_logical_writes, dbo.top_cpu_usage.min_logical_writes, dbo.top_cpu_usage.max_logical_writes,
dbo.top_cpu_usage.total_logical_reads, dbo.top_cpu_usage.last_logical_reads, dbo.top_cpu_usage.min_logical_reads, dbo.top_cpu_usage.max_logical_reads,
dbo.top_cpu_usage.total_clr_time, dbo.top_cpu_usage.last_clr_time, dbo.top_cpu_usage.min_clr_time, dbo.top_cpu_usage.max_clr_time,
dbo.top_cpu_usage.total_elapsed_time, dbo.top_cpu_usage.last_elapsed_time, dbo.top_cpu_usage.min_elapsed_time, dbo.top_cpu_usage.max_elapsed_time,
dbo.top_cpu_usage.query_hash, dbo.top_cpu_usage.query_plan_hash, dbo.top_cpu_usage.total_rows, dbo.top_cpu_usage.last_rows,
dbo.top_cpu_usage.min_rows, dbo.top_cpu_usage.max_rows, dbo.top_cpu_usage.dbid, dbo.top_cpu_usage.qr_id, dbo.queries.description
FROM            dbo.top_cpu_usage LEFT OUTER JOIN
dbo.queries ON dbo.queries.qr_key = dbo.top_cpu_usage.qr_id

GO

Аккумулированные скрипты

Скрипт для обновления созданной ранее БД

/****** Object:  Table [dbo].[queries]    Script Date: 07.05.2024 9:03:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[queries](
[qr_key] [int] NOT NULL,
[description] [nvarchar](150) NULL,
[mask] [nvarchar](max) NULL,
[related_qr_key] [int] NULL,
CONSTRAINT [PK_queries] PRIMARY KEY CLUSTERED
(
[qr_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[top_cpu_usage]  WITH CHECK ADD  CONSTRAINT [FK_top_cpu_usage_queries] FOREIGN KEY([qr_id])
REFERENCES [dbo].[queries] ([qr_key])
GO

ALTER TABLE [dbo].[top_cpu_usage] CHECK CONSTRAINT [FK_top_cpu_usage_queries]
GO

/****** Object:  Index [idx_top_cpu_usage__qr_id]    Script Date: 08.05.2024 14:04:45 ******/
CREATE NONCLUSTERED INDEX [idx_top_cpu_usage__qr_id] ON [dbo].[top_cpu_usage]
(
[qr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_top_cpu_usage__period]    Script Date: 08.05.2024 14:05:17 ******/
CREATE NONCLUSTERED INDEX [IX_top_cpu_usage__period] ON [dbo].[top_cpu_usage]
(
[period] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE top_cpu_usage
ADD qr_text_hash binary(20) null

GO

/****** Object:  Index [idx__top_cpu_usage__qr_text_hash]    Script Date: 07.05.2024 17:30:38 ******/
CREATE NONCLUSTERED INDEX [idx__top_cpu_usage__qr_text_hash] ON [dbo].[top_cpu_usage]
(
[qr_text_hash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  UserDefinedFunction [dbo].[MD5Hash]    Script Date: 07.05.2024 17:27:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Dolinin, Permenergosbyt
-- Create date: 26.04.19
-- Description: Returns MD5-hash of input string
-- =============================================
CREATE FUNCTION [dbo].[MD5Hash]
(
-- Add the parameters for the function here
@txt text
)
RETURNS binary(16)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar binary(16)

-- Add the T-SQL statements to compute the return value here
SET @ResultVar = HASHBYTES('MD5', cast(@txt as varchar(8000)))

-- Return the result of the function
RETURN @ResultVar

END

GO

/****** Object:  StoredProcedure [dbo].[sp_store_top_cpu_usage_data]    Script Date: 07.05.2024 20:59:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Dolinin, Permenergosbyt
-- Create date: 05.04.19
-- Description: This proc collect info about top CPU usages
-- =============================================
ALTER PROCEDURE [dbo].[sp_store_top_cpu_usage_data]
AS
BEGIN
SET NOCOUNT ON;

declare @depth datetime = '01:00:00.000'
declare @period datetime = getdate()
declare @count_of_result int = 100
declare @count_of_primary_data int = 10000

SELECT
*
into #T0 FROM (
select top (@count_of_primary_data)
*
from
sys.dm_exec_query_stats qs
where qs.last_execution_time > (CURRENT_TIMESTAMP - @depth)
order by qs.last_execution_time desc
) as qs
;

SELECT
SUM(qs.max_elapsed_time) as elapsed_time,
SUM(qs.total_worker_time) as worker_time
into #T1 FROM #T0 qs

select
(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
#T0 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
order by qs.last_execution_time desc
;

declare
@rec_num int,
@percent_elapsed_time decimal(5, 1),
@percent_worker_time decimal(5, 1),
@elapsed_time bigint,
@worker_time bigint,
@query_plan xml,
@text nvarchar(max),
@sql_handle varbinary(64),
@statement_start_offset int,
@statement_end_offset int,
@plan_generation_num bigint,
@plan_handle varbinary(64),
@creation_time datetime,
@last_execution_time datetime,
@execution_count bigint,
@total_worker_time bigint,
@last_worker_time bigint,
@min_worker_time bigint,
@max_worker_time bigint,
@total_physical_reads bigint,
@last_physical_reads bigint,
@min_physical_reads bigint,
@max_physical_reads bigint,
@total_logical_writes bigint,
@last_logical_writes bigint,
@min_logical_writes bigint,
@max_logical_writes bigint,
@total_logical_reads bigint,
@last_logical_reads bigint,
@min_logical_reads bigint,
@max_logical_reads bigint,
@total_clr_time bigint,
@last_clr_time bigint,
@min_clr_time bigint,
@max_clr_time bigint,
@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),
@total_rows bigint,
@last_rows bigint,
@min_rows bigint,
@max_rows bigint,
@dbid int

declare cur cursor FORWARD_ONLY for
select top (@count_of_result)
try_cast(T2.elapsed_time*100/T1.elapsed_time as decimal(5, 1)) as percent_elapsed_time,
try_cast(T2.worker_time*100/T1.worker_time as decimal(5, 1)) as percent_worker_time,
T2.elapsed_time,
T2.worker_time,
T2.query_plan,
T2.text,
T2.sql_handle,
T2.statement_start_offset,
T2.statement_end_offset,
T2.plan_generation_num,
T2.plan_handle,
T2.creation_time,
T2.last_execution_time,
T2.execution_count,
T2.total_worker_time,
T2.last_worker_time,
T2.min_worker_time,
T2.max_worker_time,
T2.total_physical_reads,
T2.last_physical_reads,
T2.min_physical_reads,
T2.max_physical_reads,
T2.total_logical_writes,
T2.last_logical_writes,
T2.min_logical_writes,
T2.max_logical_writes,
T2.total_logical_reads,
T2.last_logical_reads,
T2.min_logical_reads,
T2.max_logical_reads,
T2.total_clr_time,
T2.last_clr_time,
T2.min_clr_time,
T2.max_clr_time,
T2.total_elapsed_time,
T2.last_elapsed_time,
T2.min_elapsed_time,
T2.max_elapsed_time,
T2.query_hash,
T2.query_plan_hash,
T2.total_rows,
T2.last_rows,
T2.min_rows,
T2.max_rows,
T2.dbid
from
#T2 as T2
INNER JOIN #T1 as T1
ON 1=1
order by T2.worker_time desc

open cur

fetch next from cur into
@percent_elapsed_time,
@percent_worker_time,
@elapsed_time,
@worker_time,
@query_plan,
@text,
@sql_handle,
@statement_start_offset,
@statement_end_offset,
@plan_generation_num,
@plan_handle,
@creation_time,
@last_execution_time,
@execution_count,
@total_worker_time,
@last_worker_time,
@min_worker_time,
@max_worker_time,
@total_physical_reads,
@last_physical_reads,
@min_physical_reads,
@max_physical_reads,
@total_logical_writes,
@last_logical_writes,
@min_logical_writes,
@max_logical_writes,
@total_logical_reads,
@last_logical_reads,
@min_logical_reads,
@max_logical_reads,
@total_clr_time,
@last_clr_time,
@min_clr_time,
@max_clr_time,
@total_elapsed_time,
@last_elapsed_time,
@min_elapsed_time,
@max_elapsed_time,
@query_hash,
@query_plan_hash,
@total_rows,
@last_rows,
@min_rows,
@max_rows,
@dbid
set @rec_num=1
WHILE @@FETCH_STATUS = 0
BEGIN

insert into prom_monitoring..top_cpu_usage(
period, rec_num,
percent_elapsed_time,
percent_worker_time,
elapsed_time,
worker_time,
query_plan,
text,
sql_handle,
statement_start_offset,
statement_end_offset,
plan_generation_num,
plan_handle,
creation_time,
last_execution_time,
execution_count,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_clr_time,
last_clr_time,
min_clr_time,
max_clr_time,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time,
query_hash,
query_plan_hash,
total_rows,
last_rows,
min_rows,
max_rows,
dbid,
qr_id,
qr_text_hash
)
values(
@period, @rec_num,
@percent_elapsed_time,
@percent_worker_time,
@elapsed_time,
@worker_time,
@query_plan,
@text,
@sql_handle,
@statement_start_offset,
@statement_end_offset,
@plan_generation_num,
@plan_handle,
@creation_time,
@last_execution_time,
@execution_count,
@total_worker_time,
@last_worker_time,
@min_worker_time,
@max_worker_time,
@total_physical_reads,
@last_physical_reads,
@min_physical_reads,
@max_physical_reads,
@total_logical_writes,
@last_logical_writes,
@min_logical_writes,
@max_logical_writes,
@total_logical_reads,
@last_logical_reads,
@min_logical_reads,
@max_logical_reads,
@total_clr_time,
@last_clr_time,
@min_clr_time,
@max_clr_time,
@total_elapsed_time,
@last_elapsed_time,
@min_elapsed_time,
@max_elapsed_time,
@query_hash,
@query_plan_hash,
@total_rows,
@last_rows,
@min_rows,
@max_rows,
@dbid,
-1,
dbo.MD5Hash(@text)
)
set @rec_num=@rec_num + 1

fetch next from cur into
@percent_elapsed_time,
@percent_worker_time,
@elapsed_time,
@worker_time,
@query_plan,
@text,
@sql_handle,
@statement_start_offset,
@statement_end_offset,
@plan_generation_num,
@plan_handle,
@creation_time,
@last_execution_time,
@execution_count,
@total_worker_time,
@last_worker_time,
@min_worker_time,
@max_worker_time,
@total_physical_reads,
@last_physical_reads,
@min_physical_reads,
@max_physical_reads,
@total_logical_writes,
@last_logical_writes,
@min_logical_writes,
@max_logical_writes,
@total_logical_reads,
@last_logical_reads,
@min_logical_reads,
@max_logical_reads,
@total_clr_time,
@last_clr_time,
@min_clr_time,
@max_clr_time,
@total_elapsed_time,
@last_elapsed_time,
@min_elapsed_time,
@max_elapsed_time,
@query_hash,
@query_plan_hash,
@total_rows,
@last_rows,
@min_rows,
@max_rows,
@dbid

END

CLOSE cur;
DEALLOCATE cur;

drop table #T2
;
drop table #T1
;
drop table #T0
;

END

GO

/****** Object:  StoredProcedure [dbo].[sp_fill_qr_id_for_cpu_usage]    Script Date: 08.05.2024 14:12:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Dolinin, Permenergosbyt
-- Create date: 27.04.19
-- Description: Filling identifiers for determinating of kind queries
-- =============================================
CREATE PROCEDURE [dbo].[sp_fill_qr_id_for_cpu_usage]
-- Add the parameters for the stored procedure here
@only_empty bit = 1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

update top_cpu_usage
set qr_text_hash=dbo.MD5Hash([text])
where qr_text_hash is null

if @only_empty=0
begin
update top_cpu_usage
set qr_id = -1
end

update top_cpu_usage
set qr_id = -1
where qr_id is null

declare @hash binary(16)
declare @qr_text varchar(8000)
declare @qr_id int

declare cur cursor FORWARD_ONLY for
select u.qr_text_hash
from top_cpu_usage u
where coalesce(u.qr_id, -1)=-1
group by u.qr_text_hash

open cur

fetch next from cur into @hash

WHILE @@FETCH_STATUS = 0
BEGIN

set @qr_id = null
set @qr_text = null

select top 1 @qr_text = cast(text as varchar(8000))
from top_cpu_usage u
where u.qr_text_hash=@hash

if (not @qr_text is null)
begin
select top 1 @qr_id=coalesce(q.related_qr_key, q.qr_key)
from queries q
where @qr_text like cast(q.mask as varchar(8000))
order by q.qr_key

if (not @qr_id is null)
begin
update top_cpu_usage
set qr_id=@qr_id
where qr_text_hash=@hash
end

end

fetch next from cur into @hash

end

CLOSE cur
DEALLOCATE cur

END

GO

/****** Object:  View [dbo].[vw_top_cpu_usage]    Script Date: 08.05.2024 13:28:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_top_cpu_usage]
AS
SELECT        dbo.top_cpu_usage.period, dbo.top_cpu_usage.rec_num, dbo.top_cpu_usage.percent_elapsed_time, dbo.top_cpu_usage.percent_worker_time,
dbo.top_cpu_usage.elapsed_time, dbo.top_cpu_usage.worker_time, dbo.top_cpu_usage.query_plan, dbo.top_cpu_usage.text, dbo.top_cpu_usage.sql_handle,
dbo.top_cpu_usage.statement_start_offset, dbo.top_cpu_usage.statement_end_offset, dbo.top_cpu_usage.plan_generation_num, dbo.top_cpu_usage.plan_handle,
dbo.top_cpu_usage.creation_time, dbo.top_cpu_usage.last_execution_time, dbo.top_cpu_usage.execution_count, dbo.top_cpu_usage.total_worker_time,
dbo.top_cpu_usage.last_worker_time, dbo.top_cpu_usage.min_worker_time, dbo.top_cpu_usage.max_worker_time, dbo.top_cpu_usage.total_physical_reads,
dbo.top_cpu_usage.last_physical_reads, dbo.top_cpu_usage.min_physical_reads, dbo.top_cpu_usage.max_physical_reads,
dbo.top_cpu_usage.total_logical_writes, dbo.top_cpu_usage.last_logical_writes, dbo.top_cpu_usage.min_logical_writes, dbo.top_cpu_usage.max_logical_writes,
dbo.top_cpu_usage.total_logical_reads, dbo.top_cpu_usage.last_logical_reads, dbo.top_cpu_usage.min_logical_reads, dbo.top_cpu_usage.max_logical_reads,
dbo.top_cpu_usage.total_clr_time, dbo.top_cpu_usage.last_clr_time, dbo.top_cpu_usage.min_clr_time, dbo.top_cpu_usage.max_clr_time,
dbo.top_cpu_usage.total_elapsed_time, dbo.top_cpu_usage.last_elapsed_time, dbo.top_cpu_usage.min_elapsed_time, dbo.top_cpu_usage.max_elapsed_time,
dbo.top_cpu_usage.query_hash, dbo.top_cpu_usage.query_plan_hash, dbo.top_cpu_usage.total_rows, dbo.top_cpu_usage.last_rows,
dbo.top_cpu_usage.min_rows, dbo.top_cpu_usage.max_rows, dbo.top_cpu_usage.dbid, dbo.top_cpu_usage.qr_id, dbo.queries.description
FROM            dbo.top_cpu_usage LEFT OUTER JOIN
dbo.queries ON dbo.queries.qr_key = dbo.top_cpu_usage.qr_id

GO

Скрипт для создания объектов с нуля

/****** Object:  StoredProcedure [dbo].[sp_fill_qr_id_for_cpu_usage]    Script Date: 08.05.2024 14:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Dolinin, Permenergosbyt
-- Create date: 27.04.19
-- Description: Filling identifiers for determinating of kind queries
-- =============================================
CREATE PROCEDURE [dbo].[sp_fill_qr_id_for_cpu_usage]
-- Add the parameters for the stored procedure here
@only_empty bit = 1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

update top_cpu_usage
set qr_text_hash=dbo.MD5Hash([text])
where qr_text_hash is null

if @only_empty=0
begin
update top_cpu_usage
set qr_id = -1
end

update top_cpu_usage
set qr_id = -1
where qr_id is null

declare @hash binary(16)
declare @qr_text varchar(8000)
declare @qr_id int

declare cur cursor FORWARD_ONLY for
select u.qr_text_hash
from top_cpu_usage u
where coalesce(u.qr_id, -1)=-1
group by u.qr_text_hash

open cur

fetch next from cur into @hash

WHILE @@FETCH_STATUS = 0
BEGIN

set @qr_id = null
set @qr_text = null

select top 1 @qr_text = cast(text as varchar(8000))
from top_cpu_usage u
where u.qr_text_hash=@hash

if (not @qr_text is null)
begin
select top 1 @qr_id=coalesce(q.related_qr_key, q.qr_key)
from queries q
where @qr_text like cast(q.mask as varchar(8000))
order by q.qr_key

if (not @qr_id is null)
begin
update top_cpu_usage
set qr_id=@qr_id
where qr_text_hash=@hash
end

end

fetch next from cur into @hash

end

CLOSE cur
DEALLOCATE cur

END

GO
/****** Object:  StoredProcedure [dbo].[sp_store_top_cpu_usage_data]    Script Date: 08.05.2024 14:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Dolinin, Permenergosbyt
-- Create date: 05.04.19
-- Description: This proc collect info about top CPU usages
-- =============================================
CREATE PROCEDURE [dbo].[sp_store_top_cpu_usage_data]
AS
BEGIN
SET NOCOUNT ON;

declare @depth datetime = '01:00:00.000'
declare @period datetime = getdate()
declare @count_of_result int = 100
declare @count_of_primary_data int = 10000

SELECT
*
into #T0 FROM (
select top (@count_of_primary_data)
*
from
sys.dm_exec_query_stats qs
where qs.last_execution_time > (CURRENT_TIMESTAMP - @depth)
order by qs.last_execution_time desc
) as qs
;

SELECT
SUM(qs.max_elapsed_time) as elapsed_time,
SUM(qs.total_worker_time) as worker_time
into #T1 FROM #T0 qs

select
(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
#T0 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
order by qs.last_execution_time desc
;

declare
@rec_num int,
@percent_elapsed_time decimal(5, 1),
@percent_worker_time decimal(5, 1),
@elapsed_time bigint,
@worker_time bigint,
@query_plan xml,
@text nvarchar(max),
@sql_handle varbinary(64),
@statement_start_offset int,
@statement_end_offset int,
@plan_generation_num bigint,
@plan_handle varbinary(64),
@creation_time datetime,
@last_execution_time datetime,
@execution_count bigint,
@total_worker_time bigint,
@last_worker_time bigint,
@min_worker_time bigint,
@max_worker_time bigint,
@total_physical_reads bigint,
@last_physical_reads bigint,
@min_physical_reads bigint,
@max_physical_reads bigint,
@total_logical_writes bigint,
@last_logical_writes bigint,
@min_logical_writes bigint,
@max_logical_writes bigint,
@total_logical_reads bigint,
@last_logical_reads bigint,
@min_logical_reads bigint,
@max_logical_reads bigint,
@total_clr_time bigint,
@last_clr_time bigint,
@min_clr_time bigint,
@max_clr_time bigint,
@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),
@total_rows bigint,
@last_rows bigint,
@min_rows bigint,
@max_rows bigint,
@dbid int

declare cur cursor FORWARD_ONLY for
select top (@count_of_result)
try_cast(T2.elapsed_time*100/T1.elapsed_time as decimal(5, 1)) as percent_elapsed_time,
try_cast(T2.worker_time*100/T1.worker_time as decimal(5, 1)) as percent_worker_time,
T2.elapsed_time,
T2.worker_time,
T2.query_plan,
T2.text,
T2.sql_handle,
T2.statement_start_offset,
T2.statement_end_offset,
T2.plan_generation_num,
T2.plan_handle,
T2.creation_time,
T2.last_execution_time,
T2.execution_count,
T2.total_worker_time,
T2.last_worker_time,
T2.min_worker_time,
T2.max_worker_time,
T2.total_physical_reads,
T2.last_physical_reads,
T2.min_physical_reads,
T2.max_physical_reads,
T2.total_logical_writes,
T2.last_logical_writes,
T2.min_logical_writes,
T2.max_logical_writes,
T2.total_logical_reads,
T2.last_logical_reads,
T2.min_logical_reads,
T2.max_logical_reads,
T2.total_clr_time,
T2.last_clr_time,
T2.min_clr_time,
T2.max_clr_time,
T2.total_elapsed_time,
T2.last_elapsed_time,
T2.min_elapsed_time,
T2.max_elapsed_time,
T2.query_hash,
T2.query_plan_hash,
T2.total_rows,
T2.last_rows,
T2.min_rows,
T2.max_rows,
T2.dbid
from
#T2 as T2
INNER JOIN #T1 as T1
ON 1=1
order by T2.worker_time desc

open cur

fetch next from cur into
@percent_elapsed_time,
@percent_worker_time,
@elapsed_time,
@worker_time,
@query_plan,
@text,
@sql_handle,
@statement_start_offset,
@statement_end_offset,
@plan_generation_num,
@plan_handle,
@creation_time,
@last_execution_time,
@execution_count,
@total_worker_time,
@last_worker_time,
@min_worker_time,
@max_worker_time,
@total_physical_reads,
@last_physical_reads,
@min_physical_reads,
@max_physical_reads,
@total_logical_writes,
@last_logical_writes,
@min_logical_writes,
@max_logical_writes,
@total_logical_reads,
@last_logical_reads,
@min_logical_reads,
@max_logical_reads,
@total_clr_time,
@last_clr_time,
@min_clr_time,
@max_clr_time,
@total_elapsed_time,
@last_elapsed_time,
@min_elapsed_time,
@max_elapsed_time,
@query_hash,
@query_plan_hash,
@total_rows,
@last_rows,
@min_rows,
@max_rows,
@dbid
set @rec_num=1
WHILE @@FETCH_STATUS = 0
BEGIN

insert into prom_monitoring..top_cpu_usage(
period, rec_num,
percent_elapsed_time,
percent_worker_time,
elapsed_time,
worker_time,
query_plan,
text,
sql_handle,
statement_start_offset,
statement_end_offset,
plan_generation_num,
plan_handle,
creation_time,
last_execution_time,
execution_count,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_clr_time,
last_clr_time,
min_clr_time,
max_clr_time,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time,
query_hash,
query_plan_hash,
total_rows,
last_rows,
min_rows,
max_rows,
dbid,
qr_id,
qr_text_hash
)
values(
@period, @rec_num,
@percent_elapsed_time,
@percent_worker_time,
@elapsed_time,
@worker_time,
@query_plan,
@text,
@sql_handle,
@statement_start_offset,
@statement_end_offset,
@plan_generation_num,
@plan_handle,
@creation_time,
@last_execution_time,
@execution_count,
@total_worker_time,
@last_worker_time,
@min_worker_time,
@max_worker_time,
@total_physical_reads,
@last_physical_reads,
@min_physical_reads,
@max_physical_reads,
@total_logical_writes,
@last_logical_writes,
@min_logical_writes,
@max_logical_writes,
@total_logical_reads,
@last_logical_reads,
@min_logical_reads,
@max_logical_reads,
@total_clr_time,
@last_clr_time,
@min_clr_time,
@max_clr_time,
@total_elapsed_time,
@last_elapsed_time,
@min_elapsed_time,
@max_elapsed_time,
@query_hash,
@query_plan_hash,
@total_rows,
@last_rows,
@min_rows,
@max_rows,
@dbid,
-1,
dbo.MD5Hash(@text)
)
set @rec_num=@rec_num + 1

fetch next from cur into
@percent_elapsed_time,
@percent_worker_time,
@elapsed_time,
@worker_time,
@query_plan,
@text,
@sql_handle,
@statement_start_offset,
@statement_end_offset,
@plan_generation_num,
@plan_handle,
@creation_time,
@last_execution_time,
@execution_count,
@total_worker_time,
@last_worker_time,
@min_worker_time,
@max_worker_time,
@total_physical_reads,
@last_physical_reads,
@min_physical_reads,
@max_physical_reads,
@total_logical_writes,
@last_logical_writes,
@min_logical_writes,
@max_logical_writes,
@total_logical_reads,
@last_logical_reads,
@min_logical_reads,
@max_logical_reads,
@total_clr_time,
@last_clr_time,
@min_clr_time,
@max_clr_time,
@total_elapsed_time,
@last_elapsed_time,
@min_elapsed_time,
@max_elapsed_time,
@query_hash,
@query_plan_hash,
@total_rows,
@last_rows,
@min_rows,
@max_rows,
@dbid

END

CLOSE cur;
DEALLOCATE cur;

drop table #T2
;
drop table #T1
;
drop table #T0
;

END

GO
/****** Object:  UserDefinedFunction [dbo].[MD5Hash]    Script Date: 08.05.2024 14:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Dolinin, Permenergosbyt
-- Create date: 26.04.19
-- Description: Return MD5-hash of input string
-- =============================================
CREATE FUNCTION [dbo].[MD5Hash]
(
-- Add the parameters for the function here
@txt text
)
RETURNS binary(16)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar binary(16)

-- Add the T-SQL statements to compute the return value here
SET @ResultVar = HASHBYTES('MD5', cast(@txt as varchar(8000)))

-- Return the result of the function
RETURN @ResultVar

END

GO
/****** Object:  Table [dbo].[history_of_changing]    Script Date: 08.05.2024 14:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[history_of_changing](
[rec_id] [int] IDENTITY(1,1) NOT NULL,
[period] [datetime] NOT NULL,
[contents_of_changing] [nvarchar](max) NULL,
CONSTRAINT [PK_history_of_changing] PRIMARY KEY CLUSTERED
(
[rec_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[queries]    Script Date: 08.05.2024 14:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[queries](
[qr_key] [int] NOT NULL,
[description] [nvarchar](150) NULL,
[mask] [nvarchar](max) NULL,
[related_qr_key] [int] NULL,
CONSTRAINT [PK_queries] PRIMARY KEY CLUSTERED
(
[qr_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[top_cpu_usage]    Script Date: 08.05.2024 14:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[top_cpu_usage](
[period] [datetime] NOT NULL,
[rec_num] [int] NOT NULL,
[percent_elapsed_time] [decimal](5, 1) NULL,
[percent_worker_time] [decimal](5, 1) NULL,
[elapsed_time] [bigint] NULL,
[worker_time] [bigint] NULL,
[query_plan] [xml] NULL,
[text] [text] NULL,
[sql_handle] [varbinary](64) NULL,
[statement_start_offset] [int] NULL,
[statement_end_offset] [int] NULL,
[plan_generation_num] [bigint] NULL,
[plan_handle] [varbinary](64) NULL,
[creation_time] [datetime] NULL,
[last_execution_time] [datetime] NULL,
[execution_count] [bigint] NULL,
[total_worker_time] [bigint] NULL,
[last_worker_time] [bigint] NULL,
[min_worker_time] [bigint] NULL,
[max_worker_time] [bigint] NULL,
[total_physical_reads] [bigint] NULL,
[last_physical_reads] [bigint] NULL,
[min_physical_reads] [bigint] NULL,
[max_physical_reads] [bigint] NULL,
[total_logical_writes] [bigint] NULL,
[last_logical_writes] [bigint] NULL,
[min_logical_writes] [bigint] NULL,
[max_logical_writes] [bigint] NULL,
[total_logical_reads] [bigint] NULL,
[last_logical_reads] [bigint] NULL,
[min_logical_reads] [bigint] NULL,
[max_logical_reads] [bigint] NULL,
[total_clr_time] [bigint] NULL,
[last_clr_time] [bigint] NULL,
[min_clr_time] [bigint] NULL,
[max_clr_time] [bigint] NULL,
[total_elapsed_time] [bigint] NULL,
[last_elapsed_time] [bigint] NULL,
[min_elapsed_time] [bigint] NULL,
[max_elapsed_time] [bigint] NULL,
[query_hash] [binary](8) NULL,
[query_plan_hash] [binary](8) NULL,
[total_rows] [bigint] NULL,
[last_rows] [bigint] NULL,
[min_rows] [bigint] NULL,
[max_rows] [bigint] NULL,
[dbid] [int] NULL,
[qr_id] [int] NULL,
[qr_text_hash] [binary](20) NULL,
CONSTRAINT [PK_top_cpu_usage] PRIMARY KEY CLUSTERED
(
[period] ASC,
[rec_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  UserDefinedFunction [dbo].[statistics_at_dt]    Script Date: 08.05.2024 14:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Dolinin, Permenergosbyt
-- Create date: 27.04.2024
-- Description: Returns last statistics of queries
-- =============================================
CREATE FUNCTION [dbo].[statistics_at_dt]
(
-- Add the parameters for the function here
@stats_dt datetime
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT *
from top_cpu_usage u
where u.period=(select max(period) from top_cpu_usage where @stats_dt is null or (not @stats_dt is null and period <= @stats_dt))
)

GO
/****** Object:  View [dbo].[vw_top_cpu_usage]    Script Date: 08.05.2024 14:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_top_cpu_usage]
AS
SELECT        dbo.top_cpu_usage.period, dbo.top_cpu_usage.rec_num, dbo.top_cpu_usage.percent_elapsed_time, dbo.top_cpu_usage.percent_worker_time,
dbo.top_cpu_usage.elapsed_time, dbo.top_cpu_usage.worker_time, dbo.top_cpu_usage.query_plan, dbo.top_cpu_usage.text, dbo.top_cpu_usage.sql_handle,
dbo.top_cpu_usage.statement_start_offset, dbo.top_cpu_usage.statement_end_offset, dbo.top_cpu_usage.plan_generation_num, dbo.top_cpu_usage.plan_handle,
dbo.top_cpu_usage.creation_time, dbo.top_cpu_usage.last_execution_time, dbo.top_cpu_usage.execution_count, dbo.top_cpu_usage.total_worker_time,
dbo.top_cpu_usage.last_worker_time, dbo.top_cpu_usage.min_worker_time, dbo.top_cpu_usage.max_worker_time, dbo.top_cpu_usage.total_physical_reads,
dbo.top_cpu_usage.last_physical_reads, dbo.top_cpu_usage.min_physical_reads, dbo.top_cpu_usage.max_physical_reads,
dbo.top_cpu_usage.total_logical_writes, dbo.top_cpu_usage.last_logical_writes, dbo.top_cpu_usage.min_logical_writes, dbo.top_cpu_usage.max_logical_writes,
dbo.top_cpu_usage.total_logical_reads, dbo.top_cpu_usage.last_logical_reads, dbo.top_cpu_usage.min_logical_reads, dbo.top_cpu_usage.max_logical_reads,
dbo.top_cpu_usage.total_clr_time, dbo.top_cpu_usage.last_clr_time, dbo.top_cpu_usage.min_clr_time, dbo.top_cpu_usage.max_clr_time,
dbo.top_cpu_usage.total_elapsed_time, dbo.top_cpu_usage.last_elapsed_time, dbo.top_cpu_usage.min_elapsed_time, dbo.top_cpu_usage.max_elapsed_time,
dbo.top_cpu_usage.query_hash, dbo.top_cpu_usage.query_plan_hash, dbo.top_cpu_usage.total_rows, dbo.top_cpu_usage.last_rows,
dbo.top_cpu_usage.min_rows, dbo.top_cpu_usage.max_rows, dbo.top_cpu_usage.dbid, dbo.top_cpu_usage.qr_id, dbo.queries.description
FROM            dbo.top_cpu_usage LEFT OUTER JOIN
dbo.queries ON dbo.queries.qr_key = dbo.top_cpu_usage.qr_id

GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [idx__top_cpu_usage__qr_text_hash]    Script Date: 08.05.2024 14:36:24 ******/
CREATE NONCLUSTERED INDEX [idx__top_cpu_usage__qr_text_hash] ON [dbo].[top_cpu_usage]
(
[qr_text_hash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [idx_top_cpu_usage__qr_id]    Script Date: 08.05.2024 14:36:24 ******/
CREATE NONCLUSTERED INDEX [idx_top_cpu_usage__qr_id] ON [dbo].[top_cpu_usage]
(
[qr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_top_cpu_usage__period]    Script Date: 08.05.2024 14:36:24 ******/
CREATE NONCLUSTERED INDEX [IX_top_cpu_usage__period] ON [dbo].[top_cpu_usage]
(
[period] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[history_of_changing] ADD  CONSTRAINT [DF_history_of_changing_period]  DEFAULT (getdate()) FOR [period]
GO
ALTER TABLE [dbo].[top_cpu_usage]  WITH CHECK ADD  CONSTRAINT [FK_top_cpu_usage_queries] FOREIGN KEY([qr_id])
REFERENCES [dbo].[queries] ([qr_key])
GO
ALTER TABLE [dbo].[top_cpu_usage] CHECK CONSTRAINT [FK_top_cpu_usage_queries]
GO