csharp/NaverCloudPlatform/SqlServerDbaTool/NaverCloudPlatform-SqlServerDbaTool-ffd4834/LazyLog/Sqlmon/dm_exec_query_stats.cs

dm_exec_query_stats.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LogClient;
using System.Threading;
using System.Data.SqlClient;
using System.Data;
using CsLib;

namespace lazylog
{
    clast dm_exec_query_stats : BaseSqlmon
    {
        string BaseSummaryTableName = string.Empty;
        string CurrentSummaryTableName = string.Empty;
        string PreviousTableName = string.Empty;
        string SummaryTableGenQuery = string.Empty;
        string MakeViewQuery = string.Empty;
        string LocalSaveDeltaQuery = string.Empty;
        string dm_exec_query_stats_statement_TableGenQuery = string.Empty;
        //string dm_exec_query_stats_statement_last_access_limit = string.Empty;
        string GetQueryStatementQuery = string.Empty;
        int SummaryTableRemainCnt = 0;
        string RemovePlan = string.Empty;

        public dm_exec_query_stats() : base()
        {
            try
            {
                BaseTableName = GetType().Name;
                BaseSummaryTableName = "dm_exec_query_stats_summary";
                CurrentViewName = "view_" + BaseSummaryTableName;
                SummaryTableGenQuery = @"
CREATE TABLE [dbo].[dm_exec_query_stats_summary_{0}](
	probe_time              datetime not null, 
	[execution_count]       [bigint] NOT NULL,
	[total_worker_time]     [bigint] NOT NULL,
	[total_logical_reads]   [bigint] NOT NULL,
	[total_physical_reads]  [bigint] NOT NULL,
	[total_logical_writes]  [bigint] NOT NULL,
	[total_elapsed_time]    [bigint] NOT NULL,
	[total_grant_kb]        [bigint] NULL,
	[sql_handle]            [varbinary](64) NOT NULL,
	[statement_start_offset] [int] NOT NULL,
	[statement_end_offset]  [int] NOT NULL,
	[plan_handle]           [varbinary](64) NOT NULL,
	[query_hash]            [binary](8) NULL,
	[query_plan_hash]       [binary](8) NULL
) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

CREATE CLUSTERED INDEX [cl_dm_exec_query_stats_summary_{0}] ON [dbo].[dm_exec_query_stats_summary_{0}]
(
	[probe_time] ASC, 
	[query_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


declare @dropViewQuery varchar(8000)
declare @createViewQuery varchar(8000)

set @dropViewQuery
=
'
	drop view view_dm_exec_query_stats_summary
';

set @createViewQuery 
=
'
	create view view_dm_exec_query_stats_summary
	as 
	select 
        probe_time              
        , [execution_count]       
        , [total_worker_time]     
        , [total_logical_reads]   
        , [total_physical_reads]  
        , [total_logical_writes]  
        , [total_elapsed_time]    
        , [total_grant_kb]        
        , [sql_handle]            
        , [statement_start_offset]
        , [statement_end_offset]  
        , [plan_handle]           
        , [query_hash]            
        , [query_plan_hash]       
	from dm_exec_query_stats_summary_{0}
';

begin try
    if exists (select * from INFORMATION_SCHEMA.VIEWS where table_name = 'view_dm_exec_query_stats_summary')
    begin 
	    exec (@dropViewQuery)
    end 
end try 
begin catch 
	RAISERROR(N'lazylog : drop view error %s', 16, 1, @dropViewQuery) WITH LOG	
end catch 

begin try
	exec (@createViewQuery)
end try 
begin catch 
	RAISERROR(N'lazylog : create view error %s', 16, 1, @createViewQuery) WITH LOG	
end catch 



";

                // 0 stats_summary
                // 1 PreviousTableName 
                // 2 CurrentTableName
                LocalSaveDeltaQuery = @"
insert into [view_dm_exec_query_stats_summary]
(
	  probe_time
	, execution_count
	, total_worker_time		
	, total_logical_reads	
	, total_physical_reads	
	, total_logical_writes	
	, total_elapsed_time	
	, total_grant_kb		
	, sql_handle            
	, statement_start_offset
	, statement_end_offset  
	, plan_handle			
	, query_hash
	, query_plan_hash     
)
select top (100)
	  @probe_time probe_time 
	, case when execution_count