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

08.05.19

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

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

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

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

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

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

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

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

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

/****** Object:  Table [dbo].[queries]    Script Date: 07.05.2019 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.2019 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.2019 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.2019 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.2019 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.2019 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.2019 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_INFO\Dumps" create="1" type="2"/>
<log location="E:\1C_INFO\TechLogs" 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: 20190508235959
p_7: 39991101000000
p_8: 0xB58A000EA6447A5D11DAEBCC4C466D62
p_9: 0x87E933DAB071671E4FCABF1E470F9F79
p_10: 20190501000000
p_11: 20190508235959
p_12: 0xB58A000EA6447A5D11DAEBCC4C466D62
p_13: 20190401000000
p_14: 20190501000000
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.2019 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.2019 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.2019 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.2019 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.2019 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.2019 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.2019 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.2019 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.2019 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.2019 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.2019 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.2019 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.2019 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.2019 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.2019 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.2019 14:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Dolinin, Permenergosbyt
-- Create date: 27.04.2019
-- 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.2019 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.2019 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.2019 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.2019 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

MSSQL highload технологический журнал

+16 –

См. также

Поиск и анализ длительных вызовов - CALL, SCALL

HighLoad оптимизация Технологический журнал Системный администратор Программист Бесплатно (free)

Обсудим поиск и разбор причин длительных серверных вызовов CALL, SCALL.

24.06.2024 5134 ivanov660 12

Что не так с методом ЗаполнитьЗначенияСвойств

HighLoad оптимизация Программист Платформа 1С v8.3 Бесплатно (free)

Метод очень медленно работает, когда параметр приемник содержит намного меньше свойств, чем источник.

06.06.2024 9256 Evg-Lylyk 61

[Замер] Кто самый быстрый в конфигураторе?

HighLoad оптимизация Механизмы платформы 1С Системный администратор Программист Стажер Платформа 1С v8.3 Бесплатно (free)

Сравним «Регистры сведений» с «Сервисами интеграции» и узнаем, кто быстрее!

28.03.2024 5004 dsdred 23

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

HighLoad оптимизация Программист Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024 5096 spyke 28

Быстродействие типовой 1С

HighLoad оптимизация Программист Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024 7572 vasilev2015 20

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих запросов на sql, ожиданий, конвертация запроса в 1С и рекомендации, где может тормозить.

2 стартмани

15.02.2024 12417 241 ZAOSTG 80

115

Переход на Clickhouse для анализа метрик

HighLoad оптимизация Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Принимать, хранить и анализировать показания счетчиков (метрики) в базе 1С? Почему бы нет? Но это решение быстро привело к проблемам с производительностью при попытках построить какую-то более-менее сложную аналитику. Переход на PostgresSQL только временно решил проблему, т.к. количество записей уже исчислялось десятками миллионов и что-то сложное вычислить на таких объемах за разумное время становилось все сложнее. Кое-что уже практически невозможно. А что будет с производительностью через пару лет - представить страшно. Надо что-то предпринимать! В этой статье поделюсь своим первым опытом применения СУБД Clickhouse от Яндекс. Как работает, что может, как на нее планирую (если планирую) переходить, сравнение скорости работы, оценка производительности через пару лет, пример работы из 1С. Все это приправлено текстами запросов, кодом, алгоритмами выполненных действий и преподнесено вам для ознакомления в этой статье.

1 стартмани

24.01.2024 5669 glassman 18

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024 14010 doom2good 49

Подписаться на ответы Инфостарт бот

Свернуть все

Оставьте свое сообщение

E-mail:

Автор:

Сергей Долинин (ImHunter)

Рейтинг: 327

Для получения уведомлений о новых публикациях автора подключите телеграм бот: Инфостарт бот

Публикация:

№ 1056317

Создание 08.05.19 17:15

Обновление 08.05.19 17:15

Статистика:

Просмотры 7516

Загрузки 0

Рейтинг 16

Комментарии 0

Характеристики:

Код открыт Да

Рубрики HighLoad оптимизация

Кому Системный администратор ,
Программист

Тип файла Нет файла

Платформа Не имеет значения

Конфигурация Не имеет значения

Операционная система MS SQL

Страна Не имеет значения

Отрасль Не имеет значения

Налоги Не имеет значения

Вид учета Не имеет значения

Доступ к файлу Бесплатно (free)