Мониторим тяжелые запросы

/****** Object:  Table [dbo].[top_cpu_usage]    Script Date: 20.04.2024 18:05:33 ******/
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,
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:  StoredProcedure [dbo].[sp_store_top_cpu_usage_data]    Script Date: 20.04.2024 18:09:40 ******/
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
)
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
)
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