MS SQL Serverの一般的なパフォーマンスインジケーターの実装例

USE [ ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[PlanQuery]( [PlanHandle] [varbinary](64) NOT NULL, [SQLHandle] [varbinary](64) NOT NULL, [QueryPlan] [xml] NULL, [InsertUTCDate] [datetime] NOT NULL, CONSTRAINT [PK_PlanQuery] PRIMARY KEY CLUSTERED ( [SQLHandle] ASC, [PlanHandle] 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 [srv].[PlanQuery] ADD CONSTRAINT [DF_PlanQuery_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [ ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[SQLQuery]( [SQLHandle] [varbinary](64) NOT NULL, [TSQL] [nvarchar](max) NULL, [InsertUTCDate] [datetime] NOT NULL, CONSTRAINT [PK_SQLQuery] PRIMARY KEY CLUSTERED ( [SQLHandle] 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 [srv].[SQLQuery] ADD CONSTRAINT [DF_SQLQuery_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [ ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[RequestStatistics]( [session_id] [smallint] NOT NULL, [request_id] [int] NULL, [start_time] [datetime] NULL, [status] [nvarchar](30) NULL, [command] [nvarchar](32) NULL, [sql_handle] [varbinary](64) NULL, [statement_start_offset] [int] NULL, [statement_end_offset] [int] NULL, [plan_handle] [varbinary](64) NULL, [database_id] [smallint] NULL, [user_id] [int] NULL, [connection_id] [uniqueidentifier] NULL, [blocking_session_id] [smallint] NULL, [wait_type] [nvarchar](60) NULL, [wait_time] [int] NULL, [last_wait_type] [nvarchar](60) NULL, [wait_resource] [nvarchar](256) NULL, [open_transaction_count] [int] NULL, [open_resultset_count] [int] NULL, [transaction_id] [bigint] NULL, [context_info] [varbinary](128) NULL, [percent_complete] [real] NULL, [estimated_completion_time] [bigint] NULL, [cpu_time] [int] NULL, [total_elapsed_time] [int] NULL, [scheduler_id] [int] NULL, [task_address] [varbinary](8) NULL, [reads] [bigint] NULL, [writes] [bigint] NULL, [logical_reads] [bigint] NULL, [text_size] [int] NULL, [language] [nvarchar](128) NULL, [date_format] [nvarchar](3) NULL, [date_first] [smallint] NULL, [quoted_identifier] [bit] NULL, [arithabort] [bit] NULL, [ansi_null_dflt_on] [bit] NULL, [ansi_defaults] [bit] NULL, [ansi_warnings] [bit] NULL, [ansi_padding] [bit] NULL, [ansi_nulls] [bit] NULL, [concat_null_yields_null] [bit] NULL, [transaction_isolation_level] [smallint] NULL, [lock_timeout] [int] NULL, [deadlock_priority] [int] NULL, [row_count] [bigint] NULL, [prev_error] [int] NULL, [nest_level] [int] NULL, [granted_query_memory] [int] NULL, [executing_managed_code] [bit] NULL, [group_id] [int] NULL, [query_hash] [binary](8) NULL, [query_plan_hash] [binary](8) NULL, [most_recent_session_id] [int] NULL, [connect_time] [datetime] NULL, [net_transport] [nvarchar](40) NULL, [protocol_type] [nvarchar](40) NULL, [protocol_version] [int] NULL, [endpoint_id] [int] NULL, [encrypt_option] [nvarchar](40) NULL, [auth_scheme] [nvarchar](40) NULL, [node_affinity] [smallint] NULL, [num_reads] [int] NULL, [num_writes] [int] NULL, [last_read] [datetime] NULL, [last_write] [datetime] NULL, [net_packet_size] [int] NULL, [client_net_address] [varchar](48) NULL, [client_tcp_port] [int] NULL, [local_net_address] [varchar](48) NULL, [local_tcp_port] [int] NULL, [parent_connection_id] [uniqueidentifier] NULL, [most_recent_sql_handle] [varbinary](64) NULL, [login_time] [datetime] NULL, [host_name] [nvarchar](128) NULL, [program_name] [nvarchar](128) NULL, [host_process_id] [int] NULL, [client_version] [int] NULL, [client_interface_name] [nvarchar](32) NULL, [security_id] [varbinary](85) NULL, [login_name] [nvarchar](128) NULL, [nt_domain] [nvarchar](128) NULL, [nt_user_name] [nvarchar](128) NULL, [memory_usage] [int] NULL, [total_scheduled_time] [int] NULL, [last_request_start_time] [datetime] NULL, [last_request_end_time] [datetime] NULL, [is_user_process] [bit] NULL, [original_security_id] [varbinary](85) NULL, [original_login_name] [nvarchar](128) NULL, [last_successful_logon] [datetime] NULL, [last_unsuccessful_logon] [datetime] NULL, [unsuccessful_logons] [bigint] NULL, [authenticating_database_id] [int] NULL, [InsertUTCDate] [datetime] NOT NULL, [EndRegUTCDate] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [srv].[RequestStatistics] ADD CONSTRAINT [DF_RequestStatistics_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO SET ANSI_PADDING ON GO CREATE CLUSTERED INDEX [indRequest] ON [srv].[RequestStatistics] ( [session_id] ASC, [request_id] ASC, [database_id] ASC, [user_id] ASC, [start_time] ASC, [command] ASC, [sql_handle] ASC, [plan_handle] ASC, [transaction_id] ASC, [connection_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 SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [indPlanQuery] ON [srv].[RequestStatistics] ( [plan_handle] ASC, [sql_handle] ASC ) WHERE ([sql_handle] IS NOT NULL AND [plan_handle] IS NOT NULL) 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [ ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[TSQL_DAY_Statistics]( [command] [nvarchar](32) NOT NULL, [DBName] [nvarchar](128) NOT NULL, [PlanHandle] [varbinary](64) NOT NULL, [SqlHandle] [varbinary](64) NOT NULL, [execution_count] [bigint] NOT NULL, [min_wait_timeSec] [decimal](23, 8) NOT NULL, [min_estimated_completion_timeSec] [decimal](23, 8) NOT NULL, [min_cpu_timeSec] [decimal](23, 8) NOT NULL, [min_total_elapsed_timeSec] [decimal](23, 8) NOT NULL, [min_lock_timeoutSec] [decimal](23, 8) NOT NULL, [max_wait_timeSec] [decimal](23, 8) NOT NULL, [max_estimated_completion_timeSec] [decimal](23, 8) NOT NULL, [max_cpu_timeSec] [decimal](23, 8) NOT NULL, [max_total_elapsed_timeSec] [decimal](23, 8) NOT NULL, [max_lock_timeoutSec] [decimal](23, 8) NOT NULL, [DATE] [date] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [srv].[TSQL_DAY_Statistics] ADD CONSTRAINT [DF_TSQL_DAY_Statistics_DATE] DEFAULT (getutcdate()) FOR [DATE] GO CREATE NONCLUSTERED INDEX [indDATE] ON [srv].[TSQL_DAY_Statistics] ( [DATE] 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [ ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [srv].[vRequestStatistics] as /* */ SELECT rs.[status] collate Cyrillic_General_CI_AS as [status] ,rs.[InsertUTCDate] ,rs.[start_time] ,rs.[command] collate Cyrillic_General_CI_AS as [command] ,rs.[session_id] ,rs.[blocking_session_id] ,round(cast(rs.[total_elapsed_time] as decimal(18,3))/1000, 3) as [total_elapsed_timeSec] ,DB_NAME(rs.[database_id]) collate Cyrillic_General_CI_AS as [DBName] ,rs.[is_user_process] ,rs.[login_name] collate Cyrillic_General_CI_AS as [login_name] ,rs.[program_name] collate Cyrillic_General_CI_AS as [program_name] ,rs.[host_name] collate Cyrillic_General_CI_AS as [host_name] ,sq.[TSQL] collate Cyrillic_General_CI_AS as [TSQL]--,(select top(1) text from sys.dm_exec_sql_text([sql_handle])) as [TSQL] ,pq.[QueryPlan] ,rs.[plan_handle] ,rs.[user_id] ,rs.[connection_id] ,rs.[database_id] ,rs.[sql_handle] ,rs.[statement_start_offset]--          ,     .        sql_handle, statement_end_offset  sys.dm_exec_sql_text         .   NULL. ,rs.[statement_end_offset]--          ,     .        sql_handle, statement_end_offset  sys.dm_exec_sql_text         .   NULL. ,rs.[wait_type] collate Cyrillic_General_CI_AS as [wait_type]--  ,rs.[wait_time]--     ,       ( ).    NULL. ,round(cast(rs.[wait_time] as decimal(18,3))/1000, 3) as [wait_timeSec] ,rs.[last_wait_type] collate Cyrillic_General_CI_AS as [last_wait_type]--    ,      .    NULL. ,rs.[wait_resource] collate Cyrillic_General_CI_AS as [wait_resource]--     ,    ,    .    NULL. ,rs.[open_transaction_count]-- ,    .    NULL. ,rs.[open_resultset_count]--  ,    .    NULL. ,rs.[transaction_id]-- ,    .    NULL. ,rs.[context_info] ,rs.[percent_complete] ,rs.[estimated_completion_time] ,round(cast(rs.[estimated_completion_time] as decimal(18,3))/1000, 3) as [estimated_completion_timeSec] ,rs.[cpu_time]--  ( ),    .    NULL. ,round(cast(rs.[cpu_time] as decimal(18,3))/1000, 3) as [cpu_timeSec] ,rs.[total_elapsed_time]-- ,      ( ).    NULL. ,rs.[scheduler_id]-- ,    .    NULL. ,rs.[task_address]--  ,   ,    .   NULL. ,rs.[reads]--  ,   .    NULL. ,rs.[writes]--  ,   .    NULL. ,rs.[logical_reads]--   ,   .    NULL. ,rs.[text_size]--  TEXTSIZE   .    NULL. ,rs.[language] collate Cyrillic_General_CI_AS as [language]--    .   NULL. ,rs.[date_format] collate Cyrillic_General_CI_AS as [date_format]--  DATEFORMAT   .   NULL. ,rs.[date_first]--  DATEFIRST   .    NULL. ,rs.[quoted_identifier] ,rs.[arithabort] ,rs.[ansi_null_dflt_on] ,rs.[ansi_defaults] ,rs.[ansi_warnings] ,rs.[ansi_padding] ,rs.[ansi_nulls] ,rs.[concat_null_yields_null] ,rs.[transaction_isolation_level]-- ,       .    NULL (0- ,  1  5    ) ,rs.[lock_timeout]--      ( ).    NULL. ,round(cast(rs.[lock_timeout] as decimal(18,3))/1000, 3) as [lock_timeoutSec] ,rs.[deadlock_priority]--  DEADLOCK_PRIORITY   .    NULL. ,rs.[row_count]-- ,     .    NULL. ,rs.[prev_error]-- ,    .    NULL. ,rs.[nest_level]--   ,    .    NULL. ,rs.[granted_query_memory]-- ,     .    NULL. ,rs.[executing_managed_code]--,           CLR (, ,   ).       ,    CLR   ,       Transact-SQL.    NULL. ,rs.[group_id]--   ,    .    NULL. ,rs.[query_hash]-- -           .           ,      . ,rs.[query_plan_hash]-- -             .              . ,rs.[last_request_start_time] ,rs.[last_request_end_time] ,rs.[total_scheduled_time] ,rs.[memory_usage] ,rs.[nt_user_name] collate Cyrillic_General_CI_AS as [nt_user_name] ,rs.[nt_domain] collate Cyrillic_General_CI_AS as [nt_domain] ,rs.[security_id] ,rs.[client_interface_name] collate Cyrillic_General_CI_AS as [client_interface_name] ,rs.[client_version] ,rs.[host_process_id] ,rs.[login_time] ,rs.[most_recent_sql_handle] ,rs.[parent_connection_id] ,rs.[local_tcp_port] ,rs.[local_net_address] collate Cyrillic_General_CI_AS as [local_net_address] ,rs.[client_tcp_port] ,rs.[client_net_address] collate Cyrillic_General_CI_AS as [client_net_address] ,rs.[EndRegUTCDate] FROM [srv].[RequestStatistics] as rs with(readuncommitted) inner join [srv].[PlanQuery] as pq on rs.[plan_handle]=pq.[PlanHandle] and rs.[sql_handle]=pq.[SqlHandle] inner join [srv].[SQLQuery] as sq on sq.[SqlHandle]=pq.[SqlHandle] union all SELECT rs.[status] collate Cyrillic_General_CI_AS ,rs.[InsertUTCDate] ,rs.[start_time] ,rs.[command] collate Cyrillic_General_CI_AS ,rs.[session_id] ,rs.[blocking_session_id] ,round(cast(rs.[total_elapsed_time] as decimal(18,3))/1000, 3) as [total_elapsed_timeSec] ,DB_NAME(rs.[database_id]) collate Cyrillic_General_CI_AS as [DBName] ,rs.[is_user_process] ,rs.[login_name] collate Cyrillic_General_CI_AS ,rs.[program_name] collate Cyrillic_General_CI_AS ,rs.[host_name] collate Cyrillic_General_CI_AS ,sq.[TSQL] collate Cyrillic_General_CI_AS--,(select top(1) text from sys.dm_exec_sql_text([sql_handle])) as [TSQL] ,pq.[QueryPlan] ,rs.[plan_handle] ,rs.[user_id] ,rs.[connection_id] ,rs.[database_id] ,rs.[sql_handle] ,rs.[statement_start_offset]--          ,     .        sql_handle, statement_end_offset  sys.dm_exec_sql_text         .   NULL. ,rs.[statement_end_offset]--          ,     .        sql_handle, statement_end_offset  sys.dm_exec_sql_text         .   NULL. ,rs.[wait_type] collate Cyrillic_General_CI_AS--  ,rs.[wait_time]--     ,       ( ).    NULL. ,round(cast(rs.[wait_time] as decimal(18,3))/1000, 3) as [wait_timeSec] ,rs.[last_wait_type] collate Cyrillic_General_CI_AS--    ,      .    NULL. ,rs.[wait_resource] collate Cyrillic_General_CI_AS--     ,    ,    .    NULL. ,rs.[open_transaction_count]-- ,    .    NULL. ,rs.[open_resultset_count]--  ,    .    NULL. ,rs.[transaction_id]-- ,    .    NULL. ,rs.[context_info] ,rs.[percent_complete] ,rs.[estimated_completion_time] ,round(cast(rs.[estimated_completion_time] as decimal(18,3))/1000, 3) as [estimated_completion_timeSec] ,rs.[cpu_time]--  ( ),    .    NULL. ,round(cast(rs.[cpu_time] as decimal(18,3))/1000, 3) as [cpu_timeSec] ,rs.[total_elapsed_time]-- ,      ( ).    NULL. ,rs.[scheduler_id]-- ,    .    NULL. ,rs.[task_address]--  ,   ,    .   NULL. ,rs.[reads]--  ,   .    NULL. ,rs.[writes]--  ,   .    NULL. ,rs.[logical_reads]--   ,   .    NULL. ,rs.[text_size]--  TEXTSIZE   .    NULL. ,rs.[language] collate Cyrillic_General_CI_AS--    .   NULL. ,rs.[date_format] collate Cyrillic_General_CI_AS--  DATEFORMAT   .   NULL. ,rs.[date_first]--  DATEFIRST   .    NULL. ,rs.[quoted_identifier] ,rs.[arithabort] ,rs.[ansi_null_dflt_on] ,rs.[ansi_defaults] ,rs.[ansi_warnings] ,rs.[ansi_padding] ,rs.[ansi_nulls] ,rs.[concat_null_yields_null] ,rs.[transaction_isolation_level]-- ,       .    NULL (0- ,  1  5    ) ,rs.[lock_timeout]--      ( ).    NULL. ,round(cast(rs.[lock_timeout] as decimal(18,3))/1000, 3) as [lock_timeoutSec] ,rs.[deadlock_priority]--  DEADLOCK_PRIORITY   .    NULL. ,rs.[row_count]-- ,     .    NULL. ,rs.[prev_error]-- ,    .    NULL. ,rs.[nest_level]--   ,    .    NULL. ,rs.[granted_query_memory]-- ,     .    NULL. ,rs.[executing_managed_code]--,           CLR (, ,   ).       ,    CLR   ,       Transact-SQL.    NULL. ,rs.[group_id]--   ,    .    NULL. ,rs.[query_hash]-- -           .           ,      . ,rs.[query_plan_hash]-- -             .              . ,rs.[last_request_start_time] ,rs.[last_request_end_time] ,rs.[total_scheduled_time] ,rs.[memory_usage] ,rs.[nt_user_name] collate Cyrillic_General_CI_AS ,rs.[nt_domain] collate Cyrillic_General_CI_AS ,rs.[security_id] ,rs.[client_interface_name] collate Cyrillic_General_CI_AS ,rs.[client_version] ,rs.[host_process_id] ,rs.[login_time] ,rs.[most_recent_sql_handle] ,rs.[parent_connection_id] ,rs.[local_tcp_port] ,rs.[local_net_address] collate Cyrillic_General_CI_AS ,rs.[client_tcp_port] ,rs.[client_net_address] collate Cyrillic_General_CI_AS ,rs.[EndRegUTCDate] FROM [srv].[RequestStatisticsArchive] as rs with(readuncommitted) inner join [srv].[PlanQuery] as pq on rs.[plan_handle]=pq.[PlanHandle] and rs.[sql_handle]=pq.[SqlHandle] inner join [srv].[SQLQuery] as sq on sq.[SqlHandle]=pq.[SqlHandle] GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [ ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[vRequestDetail] as /*,      ,   ,     */ with tbl0 as ( select ES.[session_id] ,ER.[blocking_session_id] ,ER.[request_id] ,ER.[start_time] ,ER.[status] ,ER.[command] ,ER.[percent_complete] ,DB_Name(coalesce(ER.[database_id], ES.[database_id])) as [DBName] ,(select top(1) text from sys.dm_exec_sql_text(ER.[sql_handle])) as [TSQL] ,(select top(1) [query_plan] from sys.dm_exec_query_plan(ER.[plan_handle])) as [QueryPlan] ,ER.[wait_type] ,ES.[login_time] ,ES.[host_name] ,ES.[program_name] ,ER.[wait_time] ,ER.[last_wait_type] ,ER.[wait_resource] ,ER.[open_transaction_count] ,ER.[open_resultset_count] ,ER.[transaction_id] ,ER.[context_info] ,ER.[estimated_completion_time] ,ER.[cpu_time] ,ER.[total_elapsed_time] ,ER.[scheduler_id] ,ER.[task_address] ,ER.[reads] ,ER.[writes] ,ER.[logical_reads] ,ER.[text_size] ,ER.[language] ,ER.[date_format] ,ER.[date_first] ,ER.[quoted_identifier] ,ER.[arithabort] ,ER.[ansi_null_dflt_on] ,ER.[ansi_defaults] ,ER.[ansi_warnings] ,ER.[ansi_padding] ,ER.[ansi_nulls] ,ER.[concat_null_yields_null] ,ER.[transaction_isolation_level] ,ER.[lock_timeout] ,ER.[deadlock_priority] ,ER.[row_count] ,ER.[prev_error] ,ER.[nest_level] ,ER.[granted_query_memory] ,ER.[executing_managed_code] ,ER.[group_id] ,ER.[query_hash] ,ER.[query_plan_hash] ,EC.[most_recent_session_id] ,EC.[connect_time] ,EC.[net_transport] ,EC.[protocol_type] ,EC.[protocol_version] ,EC.[endpoint_id] ,EC.[encrypt_option] ,EC.[auth_scheme] ,EC.[node_affinity] ,EC.[num_reads] ,EC.[num_writes] ,EC.[last_read] ,EC.[last_write] ,EC.[net_packet_size] ,EC.[client_net_address] ,EC.[client_tcp_port] ,EC.[local_net_address] ,EC.[local_tcp_port] ,EC.[parent_connection_id] ,EC.[most_recent_sql_handle] ,ES.[host_process_id] ,ES.[client_version] ,ES.[client_interface_name] ,ES.[security_id] ,ES.[login_name] ,ES.[nt_domain] ,ES.[nt_user_name] ,ES.[memory_usage] ,ES.[total_scheduled_time] ,ES.[last_request_start_time] ,ES.[last_request_end_time] ,ES.[is_user_process] ,ES.[original_security_id] ,ES.[original_login_name] ,ES.[last_successful_logon] ,ES.[last_unsuccessful_logon] ,ES.[unsuccessful_logons] ,ES.[authenticating_database_id] ,ER.[sql_handle] ,ER.[statement_start_offset] ,ER.[statement_end_offset] ,ER.[plan_handle] ,coalesce(ER.[database_id], ES.[database_id]) as [database_id] ,ER.[user_id] ,ER.[connection_id] from sys.dm_exec_requests ER with(readuncommitted) right join sys.dm_exec_sessions ES with(readuncommitted) on ES.session_id = ER.session_id left join sys.dm_exec_connections EC with(readuncommitted) on EC.session_id = ES.session_id ) , tbl as ( select [session_id] ,[blocking_session_id] ,[request_id] ,[start_time] ,[status] ,[command] ,[percent_complete] ,[DBName] ,[TSQL] ,[QueryPlan] ,[wait_type] ,[login_time] ,[host_name] ,[program_name] ,[wait_time] ,[last_wait_type] ,[wait_resource] ,[open_transaction_count] ,[open_resultset_count] ,[transaction_id] ,[context_info] ,[estimated_completion_time] ,[cpu_time] ,[total_elapsed_time] ,[scheduler_id] ,[task_address] ,[reads] ,[writes] ,[logical_reads] ,[text_size] ,[language] ,[date_format] ,[date_first] ,[quoted_identifier] ,[arithabort] ,[ansi_null_dflt_on] ,[ansi_defaults] ,[ansi_warnings] ,[ansi_padding] ,[ansi_nulls] ,[concat_null_yields_null] ,[transaction_isolation_level] ,[lock_timeout] ,[deadlock_priority] ,[row_count] ,[prev_error] ,[nest_level] ,[granted_query_memory] ,[executing_managed_code] ,[group_id] ,[query_hash] ,[query_plan_hash] ,[most_recent_session_id] ,[connect_time] ,[net_transport] ,[protocol_type] ,[protocol_version] ,[endpoint_id] ,[encrypt_option] ,[auth_scheme] ,[node_affinity] ,[num_reads] ,[num_writes] ,[last_read] ,[last_write] ,[net_packet_size] ,[client_net_address] ,[client_tcp_port] ,[local_net_address] ,[local_tcp_port] ,[parent_connection_id] ,[most_recent_sql_handle] ,[host_process_id] ,[client_version] ,[client_interface_name] ,[security_id] ,[login_name] ,[nt_domain] ,[nt_user_name] ,[memory_usage] ,[total_scheduled_time] ,[last_request_start_time] ,[last_request_end_time] ,[is_user_process] ,[original_security_id] ,[original_login_name] ,[last_successful_logon] ,[last_unsuccessful_logon] ,[unsuccessful_logons] ,[authenticating_database_id] ,[sql_handle] ,[statement_start_offset] ,[statement_end_offset] ,[plan_handle] ,[database_id] ,[user_id] ,[connection_id] from tbl0 where [status] in ('suspended', 'running', 'runnable') ) , tbl_group as ( select [blocking_session_id] from tbl where [blocking_session_id]<>0 group by [blocking_session_id] ) select [session_id] ,[blocking_session_id] ,[request_id] ,[start_time] ,[status] ,[command] ,[percent_complete] ,[DBName] ,[TSQL] ,[QueryPlan] ,[wait_type] ,[login_time] ,[host_name] ,[program_name] ,[wait_time] ,[last_wait_type] ,[wait_resource] ,[open_transaction_count] ,[open_resultset_count] ,[transaction_id] ,[context_info] ,[estimated_completion_time] ,[cpu_time] ,[total_elapsed_time] ,[scheduler_id] ,[task_address] ,[reads] ,[writes] ,[logical_reads] ,[text_size] ,[language] ,[date_format] ,[date_first] ,[quoted_identifier] ,[arithabort] ,[ansi_null_dflt_on] ,[ansi_defaults] ,[ansi_warnings] ,[ansi_padding] ,[ansi_nulls] ,[concat_null_yields_null] ,[transaction_isolation_level] ,[lock_timeout] ,[deadlock_priority] ,[row_count] ,[prev_error] ,[nest_level] ,[granted_query_memory] ,[executing_managed_code] ,[group_id] ,[query_hash] ,[query_plan_hash] ,[most_recent_session_id] ,[connect_time] ,[net_transport] ,[protocol_type] ,[protocol_version] ,[endpoint_id] ,[encrypt_option] ,[auth_scheme] ,[node_affinity] ,[num_reads] ,[num_writes] ,[last_read] ,[last_write] ,[net_packet_size] ,[client_net_address] ,[client_tcp_port] ,[local_net_address] ,[local_tcp_port] ,[parent_connection_id] ,[most_recent_sql_handle] ,[host_process_id] ,[client_version] ,[client_interface_name] ,[security_id] ,[login_name] ,[nt_domain] ,[nt_user_name] ,[memory_usage] ,[total_scheduled_time] ,[last_request_start_time] ,[last_request_end_time] ,[is_user_process] ,[original_security_id] ,[original_login_name] ,[last_successful_logon] ,[last_unsuccessful_logon] ,[unsuccessful_logons] ,[authenticating_database_id] ,[sql_handle] ,[statement_start_offset] ,[statement_end_offset] ,[plan_handle] ,[database_id] ,[user_id] ,[connection_id] from tbl union all select tbl0.[session_id] ,tbl0.[blocking_session_id] ,tbl0.[request_id] ,tbl0.[start_time] ,tbl0.[status] ,tbl0.[command] ,tbl0.[percent_complete] ,tbl0.[DBName] ,tbl0.[TSQL] ,tbl0.[QueryPlan] ,tbl0.[wait_type] ,tbl0.[login_time] ,tbl0.[host_name] ,tbl0.[program_name] ,tbl0.[wait_time] ,tbl0.[last_wait_type] ,tbl0.[wait_resource] ,tbl0.[open_transaction_count] ,tbl0.[open_resultset_count] ,tbl0.[transaction_id] ,tbl0.[context_info] ,tbl0.[estimated_completion_time] ,tbl0.[cpu_time] ,tbl0.[total_elapsed_time] ,tbl0.[scheduler_id] ,tbl0.[task_address] ,tbl0.[reads] ,tbl0.[writes] ,tbl0.[logical_reads] ,tbl0.[text_size] ,tbl0.[language] ,tbl0.[date_format] ,tbl0.[date_first] ,tbl0.[quoted_identifier] ,tbl0.[arithabort] ,tbl0.[ansi_null_dflt_on] ,tbl0.[ansi_defaults] ,tbl0.[ansi_warnings] ,tbl0.[ansi_padding] ,tbl0.[ansi_nulls] ,tbl0.[concat_null_yields_null] ,tbl0.[transaction_isolation_level] ,tbl0.[lock_timeout] ,tbl0.[deadlock_priority] ,tbl0.[row_count] ,tbl0.[prev_error] ,tbl0.[nest_level] ,tbl0.[granted_query_memory] ,tbl0.[executing_managed_code] ,tbl0.[group_id] ,tbl0.[query_hash] ,tbl0.[query_plan_hash] ,tbl0.[most_recent_session_id] ,tbl0.[connect_time] ,tbl0.[net_transport] ,tbl0.[protocol_type] ,tbl0.[protocol_version] ,tbl0.[endpoint_id] ,tbl0.[encrypt_option] ,tbl0.[auth_scheme] ,tbl0.[node_affinity] ,tbl0.[num_reads] ,tbl0.[num_writes] ,tbl0.[last_read] ,tbl0.[last_write] ,tbl0.[net_packet_size] ,tbl0.[client_net_address] ,tbl0.[client_tcp_port] ,tbl0.[local_net_address] ,tbl0.[local_tcp_port] ,tbl0.[parent_connection_id] ,tbl0.[most_recent_sql_handle] ,tbl0.[host_process_id] ,tbl0.[client_version] ,tbl0.[client_interface_name] ,tbl0.[security_id] ,tbl0.[login_name] ,tbl0.[nt_domain] ,tbl0.[nt_user_name] ,tbl0.[memory_usage] ,tbl0.[total_scheduled_time] ,tbl0.[last_request_start_time] ,tbl0.[last_request_end_time] ,tbl0.[is_user_process] ,tbl0.[original_security_id] ,tbl0.[original_login_name] ,tbl0.[last_successful_logon] ,tbl0.[last_unsuccessful_logon] ,tbl0.[unsuccessful_logons] ,tbl0.[authenticating_database_id] ,tbl0.[sql_handle] ,tbl0.[statement_start_offset] ,tbl0.[statement_end_offset] ,tbl0.[plan_handle] ,tbl0.[database_id] ,tbl0.[user_id] ,tbl0.[connection_id] from tbl_group as tg inner join tbl0 on tg.blocking_session_id=tbl0.session_id; GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [ ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[AutoStatisticsActiveRequests] AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; declare @tbl0 table ( [SQLHandle] [varbinary](64) NOT NULL, [TSQL] [nvarchar](max) NULL ); declare @tbl1 table ( [PlanHandle] [varbinary](64) NOT NULL, [SQLHandle] [varbinary](64) NOT NULL, [QueryPlan] [xml] NULL ); declare @tbl2 table ( [session_id] [smallint] NOT NULL, [request_id] [int] NULL, [start_time] [datetime] NULL, [status] [nvarchar](30) NULL, [command] [nvarchar](32) NULL, [sql_handle] [varbinary](64) NULL, [statement_start_offset] [int] NULL, [statement_end_offset] [int] NULL, [plan_handle] [varbinary](64) NULL, [database_id] [smallint] NULL, [user_id] [int] NULL, [connection_id] [uniqueidentifier] NULL, [blocking_session_id] [smallint] NULL, [wait_type] [nvarchar](60) NULL, [wait_time] [int] NULL, [last_wait_type] [nvarchar](60) NULL, [wait_resource] [nvarchar](256) NULL, [open_transaction_count] [int] NULL, [open_resultset_count] [int] NULL, [transaction_id] [bigint] NULL, [context_info] [varbinary](128) NULL, [percent_complete] [real] NULL, [estimated_completion_time] [bigint] NULL, [cpu_time] [int] NULL, [total_elapsed_time] [int] NULL, [scheduler_id] [int] NULL, [task_address] [varbinary](8) NULL, [reads] [bigint] NULL, [writes] [bigint] NULL, [logical_reads] [bigint] NULL, [text_size] [int] NULL, [language] [nvarchar](128) NULL, [date_format] [nvarchar](3) NULL, [date_first] [smallint] NULL, [quoted_identifier] [bit] NULL, [arithabort] [bit] NULL, [ansi_null_dflt_on] [bit] NULL, [ansi_defaults] [bit] NULL, [ansi_warnings] [bit] NULL, [ansi_padding] [bit] NULL, [ansi_nulls] [bit] NULL, [concat_null_yields_null] [bit] NULL, [transaction_isolation_level] [smallint] NULL, [lock_timeout] [int] NULL, [deadlock_priority] [int] NULL, [row_count] [bigint] NULL, [prev_error] [int] NULL, [nest_level] [int] NULL, [granted_query_memory] [int] NULL, [executing_managed_code] [bit] NULL, [group_id] [int] NULL, [query_hash] [binary](8) NULL, [query_plan_hash] [binary](8) NULL, [most_recent_session_id] [int] NULL, [connect_time] [datetime] NULL, [net_transport] [nvarchar](40) NULL, [protocol_type] [nvarchar](40) NULL, [protocol_version] [int] NULL, [endpoint_id] [int] NULL, [encrypt_option] [nvarchar](40) NULL, [auth_scheme] [nvarchar](40) NULL, [node_affinity] [smallint] NULL, [num_reads] [int] NULL, [num_writes] [int] NULL, [last_read] [datetime] NULL, [last_write] [datetime] NULL, [net_packet_size] [int] NULL, [client_net_address] [varchar](48) NULL, [client_tcp_port] [int] NULL, [local_net_address] [varchar](48) NULL, [local_tcp_port] [int] NULL, [parent_connection_id] [uniqueidentifier] NULL, [most_recent_sql_handle] [varbinary](64) NULL, [login_time] [datetime] NULL, [host_name] [nvarchar](128) NULL, [program_name] [nvarchar](128) NULL, [host_process_id] [int] NULL, [client_version] [int] NULL, [client_interface_name] [nvarchar](32) NULL, [security_id] [varbinary](85) NULL, [login_name] [nvarchar](128) NULL, [nt_domain] [nvarchar](128) NULL, [nt_user_name] [nvarchar](128) NULL, [memory_usage] [int] NULL, [total_scheduled_time] [int] NULL, [last_request_start_time] [datetime] NULL, [last_request_end_time] [datetime] NULL, [is_user_process] [bit] NULL, [original_security_id] [varbinary](85) NULL, [original_login_name] [nvarchar](128) NULL, [last_successful_logon] [datetime] NULL, [last_unsuccessful_logon] [datetime] NULL, [unsuccessful_logons] [bigint] NULL, [authenticating_database_id] [int] NULL, [TSQL] [nvarchar](max) NULL, [QueryPlan] [xml] NULL ); insert into @tbl2 ( [session_id] ,[request_id] ,[start_time] ,[status] ,[command] ,[sql_handle] ,[TSQL] ,[statement_start_offset] ,[statement_end_offset] ,[plan_handle] ,[QueryPlan] ,[database_id] ,[user_id] ,[connection_id] ,[blocking_session_id] ,[wait_type] ,[wait_time] ,[last_wait_type] ,[wait_resource] ,[open_transaction_count] ,[open_resultset_count] ,[transaction_id] ,[context_info] ,[percent_complete] ,[estimated_completion_time] ,[cpu_time] ,[total_elapsed_time] ,[scheduler_id] ,[task_address] ,[reads] ,[writes] ,[logical_reads] ,[text_size] ,[language] ,[date_format] ,[date_first] ,[quoted_identifier] ,[arithabort] ,[ansi_null_dflt_on] ,[ansi_defaults] ,[ansi_warnings] ,[ansi_padding] ,[ansi_nulls] ,[concat_null_yields_null] ,[transaction_isolation_level] ,[lock_timeout] ,[deadlock_priority] ,[row_count] ,[prev_error] ,[nest_level] ,[granted_query_memory] ,[executing_managed_code] ,[group_id] ,[query_hash] ,[query_plan_hash] ,[most_recent_session_id] ,[connect_time] ,[net_transport] ,[protocol_type] ,[protocol_version] ,[endpoint_id] ,[encrypt_option] ,[auth_scheme] ,[node_affinity] ,[num_reads] ,[num_writes] ,[last_read] ,[last_write] ,[net_packet_size] ,[client_net_address] ,[client_tcp_port] ,[local_net_address] ,[local_tcp_port] ,[parent_connection_id] ,[most_recent_sql_handle] ,[login_time] ,[host_name] ,[program_name] ,[host_process_id] ,[client_version] ,[client_interface_name] ,[security_id] ,[login_name] ,[nt_domain] ,[nt_user_name] ,[memory_usage] ,[total_scheduled_time] ,[last_request_start_time] ,[last_request_end_time] ,[is_user_process] ,[original_security_id] ,[original_login_name] ,[last_successful_logon] ,[last_unsuccessful_logon] ,[unsuccessful_logons] ,[authenticating_database_id] ) select [session_id] ,[request_id] ,[start_time] ,[status] ,[command] ,[sql_handle] ,[TSQL] ,[statement_start_offset] ,[statement_end_offset] ,[plan_handle] ,[QueryPlan] ,[database_id] ,[user_id] ,[connection_id] ,[blocking_session_id] ,[wait_type] ,[wait_time] ,[last_wait_type] ,[wait_resource] ,[open_transaction_count] ,[open_resultset_count] ,[transaction_id] ,[context_info] ,[percent_complete] ,[estimated_completion_time] ,[cpu_time] ,[total_elapsed_time] ,[scheduler_id] ,[task_address] ,[reads] ,[writes] ,[logical_reads] ,[text_size] ,[language] ,[date_format] ,[date_first] ,[quoted_identifier] ,[arithabort] ,[ansi_null_dflt_on] ,[ansi_defaults] ,[ansi_warnings] ,[ansi_padding] ,[ansi_nulls] ,[concat_null_yields_null] ,[transaction_isolation_level] ,[lock_timeout] ,[deadlock_priority] ,[row_count] ,[prev_error] ,[nest_level] ,[granted_query_memory] ,[executing_managed_code] ,[group_id] ,[query_hash] ,[query_plan_hash] ,[most_recent_session_id] ,[connect_time] ,[net_transport] ,[protocol_type] ,[protocol_version] ,[endpoint_id] ,[encrypt_option] ,[auth_scheme] ,[node_affinity] ,[num_reads] ,[num_writes] ,[last_read] ,[last_write] ,[net_packet_size] ,[client_net_address] ,[client_tcp_port] ,[local_net_address] ,[local_tcp_port] ,[parent_connection_id] ,[most_recent_sql_handle] ,[login_time] ,[host_name] ,[program_name] ,[host_process_id] ,[client_version] ,[client_interface_name] ,[security_id] ,[login_name] ,[nt_domain] ,[nt_user_name] ,[memory_usage] ,[total_scheduled_time] ,[last_request_start_time] ,[last_request_end_time] ,[is_user_process] ,[original_security_id] ,[original_login_name] ,[last_successful_logon] ,[last_unsuccessful_logon] ,[unsuccessful_logons] ,[authenticating_database_id] from [inf].[vRequestDetail]; insert into @tbl1 ( [PlanHandle], [SQLHandle], [QueryPlan] ) select [plan_handle], [sql_handle], (select top(1) [query_plan] from sys.dm_exec_query_plan([plan_handle])) as [QueryPlan] from @tbl2 where (select top(1) [query_plan] from sys.dm_exec_query_plan([plan_handle])) is not null group by [plan_handle], [sql_handle]; insert into @tbl0 ( [SQLHandle], [TSQL] ) select [sql_handle], (select top(1) text from sys.dm_exec_sql_text([sql_handle])) as [TSQL]--[query_text] from @tbl2 where (select top(1) text from sys.dm_exec_sql_text([sql_handle])) is not null group by [sql_handle]; ;merge [srv].[SQLQuery] as trg using @tbl0 as src on trg.[SQLHandle]=src.[SQLHandle] WHEN NOT MATCHED BY TARGET THEN INSERT ( [SQLHandle], [TSQL] ) VALUES ( src.[SQLHandle], src.[TSQL] ); ;merge [srv].[PlanQuery] as trg using @tbl1 as src on trg.[SQLHandle]=src.[SQLHandle] and trg.[PlanHandle]=src.[PlanHandle] WHEN NOT MATCHED BY TARGET THEN INSERT ( [PlanHandle], [SQLHandle], [QueryPlan] ) VALUES ( src.[PlanHandle], src.[SQLHandle], src.[QueryPlan] ); select [session_id] ,[request_id] ,[start_time] ,[status] ,[command] ,[sql_handle] ,(select top(1) 1 from @tbl0 as t where t.[SQLHandle]=tt.[sql_handle]) as [TSQL] ,[statement_start_offset] ,[statement_end_offset] ,[plan_handle] ,(select top(1) 1 from @tbl1 as t where t.[PlanHandle]=tt.[plan_handle]) as [QueryPlan] ,[database_id] ,[user_id] ,[connection_id] ,[blocking_session_id] ,[wait_type] ,[wait_time] ,[last_wait_type] ,[wait_resource] ,[open_transaction_count] ,[open_resultset_count] ,[transaction_id] ,[context_info] ,[percent_complete] ,[estimated_completion_time] ,[cpu_time] ,[total_elapsed_time] ,[scheduler_id] ,[task_address] ,[reads] ,[writes] ,[logical_reads] ,[text_size] ,[language] ,[date_format] ,[date_first] ,[quoted_identifier] ,[arithabort] ,[ansi_null_dflt_on] ,[ansi_defaults] ,[ansi_warnings] ,[ansi_padding] ,[ansi_nulls] ,[concat_null_yields_null] ,[transaction_isolation_level] ,[lock_timeout] ,[deadlock_priority] ,[row_count] ,[prev_error] ,[nest_level] ,[granted_query_memory] ,[executing_managed_code] ,[group_id] ,[query_hash] ,[query_plan_hash] ,[most_recent_session_id] ,[connect_time] ,[net_transport] ,[protocol_type] ,[protocol_version] ,[endpoint_id] ,[encrypt_option] ,[auth_scheme] ,[node_affinity] ,[num_reads] ,[num_writes] ,[last_read] ,[last_write] ,[net_packet_size] ,[client_net_address] ,[client_tcp_port] ,[local_net_address] ,[local_tcp_port] ,[parent_connection_id] ,[most_recent_sql_handle] ,[login_time] ,[host_name] ,[program_name] ,[host_process_id] ,[client_version] ,[client_interface_name] ,[security_id] ,[login_name] ,[nt_domain] ,[nt_user_name] ,[memory_usage] ,[total_scheduled_time] ,[last_request_start_time] ,[last_request_end_time] ,[is_user_process] ,[original_security_id] ,[original_login_name] ,[last_successful_logon] ,[last_unsuccessful_logon] ,[unsuccessful_logons] ,[authenticating_database_id] into #ttt from @tbl2 as tt group by [session_id] ,[request_id] ,[start_time] ,[status] ,[command] ,[sql_handle] ,[TSQL] ,[statement_start_offset] ,[statement_end_offset] ,[plan_handle] ,[database_id] ,[user_id] ,[connection_id] ,[blocking_session_id] ,[wait_type] ,[wait_time] ,[last_wait_type] ,[wait_resource] ,[open_transaction_count] ,[open_resultset_count] ,[transaction_id] ,[context_info] ,[percent_complete] ,[estimated_completion_time] ,[cpu_time] ,[total_elapsed_time] ,[scheduler_id] ,[task_address] ,[reads] ,[writes] ,[logical_reads] ,[text_size] ,[language] ,[date_format] ,[date_first] ,[quoted_identifier] ,[arithabort] ,[ansi_null_dflt_on] ,[ansi_defaults] ,[ansi_warnings] ,[ansi_padding] ,[ansi_nulls] ,[concat_null_yields_null] ,[transaction_isolation_level] ,[lock_timeout] ,[deadlock_priority] ,[row_count] ,[prev_error] ,[nest_level] ,[granted_query_memory] ,[executing_managed_code] ,[group_id] ,[query_hash] ,[query_plan_hash] ,[most_recent_session_id] ,[connect_time] ,[net_transport] ,[protocol_type] ,[protocol_version] ,[endpoint_id] ,[encrypt_option] ,[auth_scheme] ,[node_affinity] ,[num_reads] ,[num_writes] ,[last_read] ,[last_write] ,[net_packet_size] ,[client_net_address] ,[client_tcp_port] ,[local_net_address] ,[local_tcp_port] ,[parent_connection_id] ,[most_recent_sql_handle] ,[login_time] ,[host_name] ,[program_name] ,[host_process_id] ,[client_version] ,[client_interface_name] ,[security_id] ,[login_name] ,[nt_domain] ,[nt_user_name] ,[memory_usage] ,[total_scheduled_time] ,[last_request_start_time] ,[last_request_end_time] ,[is_user_process] ,[original_security_id] ,[original_login_name] ,[last_successful_logon] ,[last_unsuccessful_logon] ,[unsuccessful_logons] ,[authenticating_database_id]; UPDATE trg SET trg.[status] =case when (trg.[status]<>'suspended') then coalesce(src.[status] collate DATABASE_DEFAULT, trg.[status] collate DATABASE_DEFAULT) else trg.[status] end --,trg.[command] =coalesce(src.[command] collate DATABASE_DEFAULT, trg.[command] collate DATABASE_DEFAULT) --,trg.[sql_handle] =coalesce(src.[sql_handle] , trg.[sql_handle] ) --,trg.[TSQL] =coalesce(src.[TSQL] collate DATABASE_DEFAULT, trg.[TSQL] collate DATABASE_DEFAULT) ,trg.[statement_start_offset] =coalesce(src.[statement_start_offset] , trg.[statement_start_offset] ) ,trg.[statement_end_offset] =coalesce(src.[statement_end_offset] , trg.[statement_end_offset] ) --,trg.[plan_handle] =coalesce(src.[plan_handle] , trg.[plan_handle] ) --,trg.[QueryPlan] =coalesce(src.[QueryPlan] , trg.[QueryPlan] ) --,trg.[connection_id] =coalesce(src.[connection_id] , trg.[connection_id] ) ,trg.[blocking_session_id] =coalesce(trg.[blocking_session_id] , src.[blocking_session_id] ) ,trg.[wait_type] =coalesce(trg.[wait_type] collate DATABASE_DEFAULT, src.[wait_type] collate DATABASE_DEFAULT) ,trg.[wait_time] =coalesce(src.[wait_time] , trg.[wait_time] ) ,trg.[last_wait_type] =coalesce(src.[last_wait_type] collate DATABASE_DEFAULT, trg.[last_wait_type] collate DATABASE_DEFAULT) ,trg.[wait_resource] =coalesce(src.[wait_resource] collate DATABASE_DEFAULT, trg.[wait_resource] collate DATABASE_DEFAULT) ,trg.[open_transaction_count] =coalesce(src.[open_transaction_count] , trg.[open_transaction_count] ) ,trg.[open_resultset_count] =coalesce(src.[open_resultset_count] , trg.[open_resultset_count] ) --,trg.[transaction_id] =coalesce(src.[transaction_id] , trg.[transaction_id] ) ,trg.[context_info] =coalesce(src.[context_info] , trg.[context_info] ) ,trg.[percent_complete] =coalesce(src.[percent_complete] , trg.[percent_complete] ) ,trg.[estimated_completion_time] =coalesce(src.[estimated_completion_time] , trg.[estimated_completion_time] ) ,trg.[cpu_time] =coalesce(src.[cpu_time] , trg.[cpu_time] ) ,trg.[total_elapsed_time] =coalesce(src.[total_elapsed_time] , trg.[total_elapsed_time] ) ,trg.[scheduler_id] =coalesce(src.[scheduler_id] , trg.[scheduler_id] ) ,trg.[task_address] =coalesce(src.[task_address] , trg.[task_address] ) ,trg.[reads] =coalesce(src.[reads] , trg.[reads] ) ,trg.[writes] =coalesce(src.[writes] , trg.[writes] ) ,trg.[logical_reads] =coalesce(src.[logical_reads] , trg.[logical_reads] ) ,trg.[text_size] =coalesce(src.[text_size] , trg.[text_size] ) ,trg.[language] =coalesce(src.[language] collate DATABASE_DEFAULT, trg.[language] collate DATABASE_DEFAULT) ,trg.[date_format] =coalesce(src.[date_format] , trg.[date_format] ) ,trg.[date_first] =coalesce(src.[date_first] , trg.[date_first] ) ,trg.[quoted_identifier] =coalesce(src.[quoted_identifier] , trg.[quoted_identifier] ) ,trg.[arithabort] =coalesce(src.[arithabort] , trg.[arithabort] ) ,trg.[ansi_null_dflt_on] =coalesce(src.[ansi_null_dflt_on] , trg.[ansi_null_dflt_on] ) ,trg.[ansi_defaults] =coalesce(src.[ansi_defaults] , trg.[ansi_defaults] ) ,trg.[ansi_warnings] =coalesce(src.[ansi_warnings] , trg.[ansi_warnings] ) ,trg.[ansi_padding] =coalesce(src.[ansi_padding] , trg.[ansi_padding] ) ,trg.[ansi_nulls] =coalesce(src.[ansi_nulls] , trg.[ansi_nulls] ) ,trg.[concat_null_yields_null] =coalesce(src.[concat_null_yields_null] , trg.[concat_null_yields_null] ) ,trg.[transaction_isolation_level] =coalesce(src.[transaction_isolation_level] , trg.[transaction_isolation_level] ) ,trg.[lock_timeout] =coalesce(src.[lock_timeout] , trg.[lock_timeout] ) ,trg.[deadlock_priority] =coalesce(src.[deadlock_priority] , trg.[deadlock_priority] ) ,trg.[row_count] =coalesce(src.[row_count] , trg.[row_count] ) ,trg.[prev_error] =coalesce(src.[prev_error] , trg.[prev_error] ) ,trg.[nest_level] =coalesce(src.[nest_level] , trg.[nest_level] ) ,trg.[granted_query_memory] =coalesce(src.[granted_query_memory] , trg.[granted_query_memory] ) ,trg.[executing_managed_code] =coalesce(src.[executing_managed_code] , trg.[executing_managed_code] ) ,trg.[group_id] =coalesce(src.[group_id] , trg.[group_id] ) ,trg.[query_hash] =coalesce(src.[query_hash] , trg.[query_hash] ) ,trg.[query_plan_hash] =coalesce(src.[query_plan_hash] , trg.[query_plan_hash] ) ,trg.[most_recent_session_id] =coalesce(src.[most_recent_session_id] , trg.[most_recent_session_id] ) ,trg.[connect_time] =coalesce(src.[connect_time] , trg.[connect_time] ) ,trg.[net_transport] =coalesce(src.[net_transport] collate DATABASE_DEFAULT, trg.[net_transport] collate DATABASE_DEFAULT) ,trg.[protocol_type] =coalesce(src.[protocol_type] collate DATABASE_DEFAULT, trg.[protocol_type] collate DATABASE_DEFAULT) ,trg.[protocol_version] =coalesce(src.[protocol_version] , trg.[protocol_version] ) ,trg.[endpoint_id] =coalesce(src.[endpoint_id] , trg.[endpoint_id] ) ,trg.[encrypt_option] =coalesce(src.[encrypt_option] collate DATABASE_DEFAULT, trg.[encrypt_option] collate DATABASE_DEFAULT) ,trg.[auth_scheme] =coalesce(src.[auth_scheme] collate DATABASE_DEFAULT, trg.[auth_scheme] collate DATABASE_DEFAULT) ,trg.[node_affinity] =coalesce(src.[node_affinity] , trg.[node_affinity] ) ,trg.[num_reads] =coalesce(src.[num_reads] , trg.[num_reads] ) ,trg.[num_writes] =coalesce(src.[num_writes] , trg.[num_writes] ) ,trg.[last_read] =coalesce(src.[last_read] , trg.[last_read] ) ,trg.[last_write] =coalesce(src.[last_write] , trg.[last_write] ) ,trg.[net_packet_size] =coalesce(src.[net_packet_size] , trg.[net_packet_size] ) ,trg.[client_net_address] =coalesce(src.[client_net_address] collate DATABASE_DEFAULT, trg.[client_net_address] collate DATABASE_DEFAULT) ,trg.[client_tcp_port] =coalesce(src.[client_tcp_port] , trg.[client_tcp_port] ) ,trg.[local_net_address] =coalesce(src.[local_net_address] collate DATABASE_DEFAULT, trg.[local_net_address] collate DATABASE_DEFAULT) ,trg.[local_tcp_port] =coalesce(src.[local_tcp_port] , trg.[local_tcp_port] ) ,trg.[parent_connection_id] =coalesce(src.[parent_connection_id] , trg.[parent_connection_id] ) ,trg.[most_recent_sql_handle] =coalesce(src.[most_recent_sql_handle] , trg.[most_recent_sql_handle] ) ,trg.[login_time] =coalesce(src.[login_time] , trg.[login_time] ) ,trg.[host_name] =coalesce(src.[host_name] collate DATABASE_DEFAULT, trg.[host_name] collate DATABASE_DEFAULT) ,trg.[program_name] =coalesce(src.[program_name] collate DATABASE_DEFAULT, trg.[program_name] collate DATABASE_DEFAULT) ,trg.[host_process_id] =coalesce(src.[host_process_id] , trg.[host_process_id] ) ,trg.[client_version] =coalesce(src.[client_version] , trg.[client_version] ) ,trg.[client_interface_name] =coalesce(src.[client_interface_name] collate DATABASE_DEFAULT, trg.[client_interface_name] collate DATABASE_DEFAULT) ,trg.[security_id] =coalesce(src.[security_id] , trg.[security_id] ) ,trg.[login_name] =coalesce(src.[login_name] collate DATABASE_DEFAULT, trg.[login_name] collate DATABASE_DEFAULT) ,trg.[nt_domain] =coalesce(src.[nt_domain] collate DATABASE_DEFAULT, trg.[nt_domain] collate DATABASE_DEFAULT) ,trg.[nt_user_name] =coalesce(src.[nt_user_name] collate DATABASE_DEFAULT, trg.[nt_user_name] collate DATABASE_DEFAULT) ,trg.[memory_usage] =coalesce(src.[memory_usage] , trg.[memory_usage] ) ,trg.[total_scheduled_time] =coalesce(src.[total_scheduled_time] , trg.[total_scheduled_time] ) ,trg.[last_request_start_time] =coalesce(src.[last_request_start_time] , trg.[last_request_start_time] ) ,trg.[last_request_end_time] =coalesce(src.[last_request_end_time] , trg.[last_request_end_time] ) ,trg.[is_user_process] =coalesce(src.[is_user_process] , trg.[is_user_process] ) ,trg.[original_security_id] =coalesce(src.[original_security_id] , trg.[original_security_id] ) ,trg.[original_login_name] =coalesce(src.[original_login_name] collate DATABASE_DEFAULT, trg.[original_login_name] collate DATABASE_DEFAULT) ,trg.[last_successful_logon] =coalesce(src.[last_successful_logon] , trg.[last_successful_logon] ) ,trg.[last_unsuccessful_logon] =coalesce(src.[last_unsuccessful_logon] , trg.[last_unsuccessful_logon] ) ,trg.[unsuccessful_logons] =coalesce(src.[unsuccessful_logons] , trg.[unsuccessful_logons] ) ,trg.[authenticating_database_id] =coalesce(src.[authenticating_database_id] , trg.[authenticating_database_id] ) from [srv].[RequestStatistics] as trg inner join #ttt as src on (trg.[session_id]=src.[session_id]) and (trg.[request_id]=src.[request_id]) and (trg.[database_id]=src.[database_id]) and (trg.[user_id]=src.[user_id]) and (trg.[start_time]=src.[start_time]) and (trg.[command] collate DATABASE_DEFAULT=src.[command] collate DATABASE_DEFAULT) and ((trg.[sql_handle]=src.[sql_handle] and src.[sql_handle] IS NOT NULL) or (src.[sql_handle] IS NULL)) and ((trg.[plan_handle]=src.[plan_handle] and src.[plan_handle] IS NOT NULL) or (src.[plan_handle] IS NULL)) and (trg.[transaction_id]=src.[transaction_id]) and ((trg.[connection_id]=src.[connection_id] and src.[connection_id] IS NOT NULL) or (src.[connection_id] IS NULL)); UPDATE trg SET trg.[EndRegUTCDate]=GetUTCDate() from [srv].[RequestStatistics] as trg where not exists( select top(1) 1 from #ttt as src where (trg.[session_id]=src.[session_id]) and (trg.[request_id]=src.[request_id]) and (trg.[database_id]=src.[database_id]) and (trg.[user_id]=src.[user_id]) and (trg.[start_time]=src.[start_time]) and (trg.[command] collate DATABASE_DEFAULT=src.[command] collate DATABASE_DEFAULT) and ((trg.[sql_handle]=src.[sql_handle] and src.[sql_handle] IS NOT NULL) or (src.[sql_handle] IS NULL)) and ((trg.[plan_handle]=src.[plan_handle] and src.[plan_handle] IS NOT NULL) or (src.[plan_handle] IS NULL)) and (trg.[transaction_id]=src.[transaction_id]) and ((trg.[connection_id]=src.[connection_id] and src.[connection_id] IS NOT NULL) or (src.[connection_id] IS NULL)) ); INSERT into [srv].[RequestStatistics] ([session_id] ,[request_id] ,[start_time] ,[status] ,[command] ,[sql_handle] --,[TSQL] ,[statement_start_offset] ,[statement_end_offset] ,[plan_handle] --,[QueryPlan] ,[database_id] ,[user_id] ,[connection_id] ,[blocking_session_id] ,[wait_type] ,[wait_time] ,[last_wait_type] ,[wait_resource] ,[open_transaction_count] ,[open_resultset_count] ,[transaction_id] ,[context_info] ,[percent_complete] ,[estimated_completion_time] ,[cpu_time] ,[total_elapsed_time] ,[scheduler_id] ,[task_address] ,[reads] ,[writes] ,[logical_reads] ,[text_size] ,[language] ,[date_format] ,[date_first] ,[quoted_identifier] ,[arithabort] ,[ansi_null_dflt_on] ,[ansi_defaults] ,[ansi_warnings] ,[ansi_padding] ,[ansi_nulls] ,[concat_null_yields_null] ,[transaction_isolation_level] ,[lock_timeout] ,[deadlock_priority] ,[row_count] ,[prev_error] ,[nest_level] ,[granted_query_memory] ,[executing_managed_code] ,[group_id] ,[query_hash] ,[query_plan_hash] ,[most_recent_session_id] ,[connect_time] ,[net_transport] ,[protocol_type] ,[protocol_version] ,[endpoint_id] ,[encrypt_option] ,[auth_scheme] ,[node_affinity] ,[num_reads] ,[num_writes] ,[last_read] ,[last_write] ,[net_packet_size] ,[client_net_address] ,[client_tcp_port] ,[local_net_address] ,[local_tcp_port] ,[parent_connection_id] ,[most_recent_sql_handle] ,[login_time] ,[host_name] ,[program_name] ,[host_process_id] ,[client_version] ,[client_interface_name] ,[security_id] ,[login_name] ,[nt_domain] ,[nt_user_name] ,[memory_usage] ,[total_scheduled_time] ,[last_request_start_time] ,[last_request_end_time] ,[is_user_process] ,[original_security_id] ,[original_login_name] ,[last_successful_logon] ,[last_unsuccessful_logon] ,[unsuccessful_logons] ,[authenticating_database_id]) select src.[session_id] ,src.[request_id] ,src.[start_time] ,src.[status] ,src.[command] ,src.[sql_handle] --,src.[TSQL] ,src.[statement_start_offset] ,src.[statement_end_offset] ,src.[plan_handle] --,src.[QueryPlan] ,src.[database_id] ,src.[user_id] ,src.[connection_id] ,src.[blocking_session_id] ,src.[wait_type] ,src.[wait_time] ,src.[last_wait_type] ,src.[wait_resource] ,src.[open_transaction_count] ,src.[open_resultset_count] ,src.[transaction_id] ,src.[context_info] ,src.[percent_complete] ,src.[estimated_completion_time] ,src.[cpu_time] ,src.[total_elapsed_time] ,src.[scheduler_id] ,src.[task_address] ,src.[reads] ,src.[writes] ,src.[logical_reads] ,src.[text_size] ,src.[language] ,src.[date_format] ,src.[date_first] ,src.[quoted_identifier] ,src.[arithabort] ,src.[ansi_null_dflt_on] ,src.[ansi_defaults] ,src.[ansi_warnings] ,src.[ansi_padding] ,src.[ansi_nulls] ,src.[concat_null_yields_null] ,src.[transaction_isolation_level] ,src.[lock_timeout] ,src.[deadlock_priority] ,src.[row_count] ,src.[prev_error] ,src.[nest_level] ,src.[granted_query_memory] ,src.[executing_managed_code] ,src.[group_id] ,src.[query_hash] ,src.[query_plan_hash] ,src.[most_recent_session_id] ,src.[connect_time] ,src.[net_transport] ,src.[protocol_type] ,src.[protocol_version] ,src.[endpoint_id] ,src.[encrypt_option] ,src.[auth_scheme] ,src.[node_affinity] ,src.[num_reads] ,src.[num_writes] ,src.[last_read] ,src.[last_write] ,src.[net_packet_size] ,src.[client_net_address] ,src.[client_tcp_port] ,src.[local_net_address] ,src.[local_tcp_port] ,src.[parent_connection_id] ,src.[most_recent_sql_handle] ,src.[login_time] ,src.[host_name] ,src.[program_name] ,src.[host_process_id] ,src.[client_version] ,src.[client_interface_name] ,src.[security_id] ,src.[login_name] ,src.[nt_domain] ,src.[nt_user_name] ,src.[memory_usage] ,src.[total_scheduled_time] ,src.[last_request_start_time] ,src.[last_request_end_time] ,src.[is_user_process] ,src.[original_security_id] ,src.[original_login_name] ,src.[last_successful_logon] ,src.[last_unsuccessful_logon] ,src.[unsuccessful_logons] ,src.[authenticating_database_id] from #ttt as src where not exists( select top(1) 1 from [srv].[RequestStatistics] as trg where (trg.[session_id]=src.[session_id]) and (trg.[request_id]=src.[request_id]) and (trg.[database_id]=src.[database_id]) and (trg.[user_id]=src.[user_id]) and (trg.[start_time]=src.[start_time]) and (trg.[command] collate DATABASE_DEFAULT=src.[command] collate DATABASE_DEFAULT) and ((trg.[sql_handle]=src.[sql_handle] and src.[sql_handle] IS NOT NULL) or (src.[sql_handle] IS NULL)) and ((trg.[plan_handle]=src.[plan_handle] and src.[plan_handle] IS NOT NULL) or (src.[plan_handle] IS NULL)) and (trg.[transaction_id]=src.[transaction_id]) and ((trg.[connection_id]=src.[connection_id] and src.[connection_id] IS NOT NULL) or (src.[connection_id] IS NULL)) ); drop table #ttt; END GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [ ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[IndicatorStatistics]( [execution_count] [bigint] NOT NULL, [max_total_elapsed_timeSec] [decimal](38, 6) NOT NULL, [max_total_elapsed_timeLastSec] [decimal](38, 6) NOT NULL, [DATE] [date] NOT NULL, CONSTRAINT [PK_IndicatorStatistics] PRIMARY KEY CLUSTERED ( [DATE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [ ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[AutoStatisticsTimeRequests] AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; delete from [srv].[TSQL_DAY_Statistics] where [DATE]<=DateAdd(day,-180,GetUTCDate()); INSERT INTO [srv].[TSQL_DAY_Statistics] ([command] ,[DBName] ,[PlanHandle] ,[SqlHandle] ,[execution_count] ,[min_wait_timeSec] ,[min_estimated_completion_timeSec] ,[min_cpu_timeSec] ,[min_total_elapsed_timeSec] ,[min_lock_timeoutSec] ,[max_wait_timeSec] ,[max_estimated_completion_timeSec] ,[max_cpu_timeSec] ,[max_total_elapsed_timeSec] ,[max_lock_timeoutSec] ,[DATE]) SELECT [command] ,[DBName] ,[plan_handle] ,[sql_handle] ,count(*) as [execution_count] ,min([wait_timeSec]) as [min_wait_timeSec] ,min([estimated_completion_timeSec]) as [min_estimated_completion_timeSec] ,min([cpu_timeSec]) as [min_cpu_timeSec] ,min([total_elapsed_timeSec]) as [min_total_elapsed_timeSec] ,min([lock_timeoutSec]) as [min_lock_timeoutSec] ,max([wait_timeSec]) as [max_wait_timeSec] ,max([estimated_completion_timeSec]) as [max_estimated_completion_timeSec] ,max([cpu_timeSec]) as [max_cpu_timeSec] ,max([total_elapsed_timeSec]) as [max_total_elapsed_timeSec] ,max([lock_timeoutSec]) as [max_lock_timeoutSec] ,cast([InsertUTCDate] as [DATE]) as [DATE] FROM [srv].[vRequestStatistics] with(readuncommitted) where cast([InsertUTCDate] as date) = DateAdd(day,-1,cast(GetUTCDate() as date)) and [command] in ( 'UPDATE', 'TRUNCATE TABLE', 'SET OPTION ON', 'SET COMMAND', 'SELECT INTO', 'SELECT', 'NOP', 'INSERT', 'EXECUTE', 'DELETE', 'DECLARE', 'CONDITIONAL', 'BULK INSERT', 'BEGIN TRY', 'BEGIN CATCH', 'AWAITING COMMAND', 'ASSIGN', 'ALTER TABLE' ) and [database_id] in ( /*    DB_ID('_')*/ ) and [DBName] is not null group by [command] ,[DBName] ,[plan_handle] ,[sql_handle] ,cast([InsertUTCDate] as [DATE]); declare @inddt int=1; ;with tbl11 as ( select [SqlHandle], max([max_total_elapsed_timeSec]) as [max_total_elapsed_timeSec] ,min([max_total_elapsed_timeSec]) as [min_max_total_elapsed_timeSec] ,avg([max_total_elapsed_timeSec]) as [avg_max_total_elapsed_timeSec] ,sum([execution_count]) as [execution_count] from [srv].[TSQL_DAY_Statistics] where [max_total_elapsed_timeSec]>=0.001 and [DATE]<cast(DateAdd(day,-@inddt,cast(GetUTCDate() as date)) as date) group by [SqlHandle] ) , tbl12 as ( select [SqlHandle], max([max_total_elapsed_timeSec]) as [max_total_elapsed_timeSec] ,min([max_total_elapsed_timeSec]) as [min_max_total_elapsed_timeSec] ,avg([max_total_elapsed_timeSec]) as [avg_max_total_elapsed_timeSec] ,[DATE] from [srv].[TSQL_DAY_Statistics] where [max_total_elapsed_timeSec]>=0.001 and [DATE]=cast(DateAdd(day,-@inddt,cast(GetUTCDate() as date)) as date) group by [SqlHandle], [DATE] ) , tbl1_sum as (select sum([execution_count]) as [sum_execution_count] from tbl11) , tbl1_total as ( select (select [sum_execution_count] from tbl1_sum) as [execution_count] , sum(tbl11.[max_total_elapsed_timeSec]*tbl11.[execution_count])/(select [sum_execution_count] from tbl1_sum) as [max_total_elapsed_timeSec] , sum(tbl12.[max_total_elapsed_timeSec]*tbl11.[execution_count])/(select [sum_execution_count] from tbl1_sum) as [max_total_elapsed_timeLastSec] , tbl12.[DATE] from tbl11 inner join tbl12 on tbl11.[SqlHandle]=tbl12.[SqlHandle] group by tbl12.[DATE] ) INSERT INTO [srv].[IndicatorStatistics] ([DATE] ,[execution_count] ,[max_total_elapsed_timeSec] ,[max_total_elapsed_timeLastSec] ) select t1.[DATE] ,t1.[execution_count] ,t1.[max_total_elapsed_timeSec] ,t1.[max_total_elapsed_timeLastSec] from tbl1_total as t1; declare @dt datetime=DateAdd(day,-2,GetUTCDate()); INSERT INTO [srv].[RequestStatisticsArchive] ([session_id] ,[request_id] ,[start_time] ,[status] ,[command] ,[sql_handle] ,[statement_start_offset] ,[statement_end_offset] ,[plan_handle] ,[database_id] ,[user_id] ,[connection_id] ,[blocking_session_id] ,[wait_type] ,[wait_time] ,[last_wait_type] ,[wait_resource] ,[open_transaction_count] ,[open_resultset_count] ,[transaction_id] ,[context_info] ,[percent_complete] ,[estimated_completion_time] ,[cpu_time] ,[total_elapsed_time] ,[scheduler_id] ,[task_address] ,[reads] ,[writes] ,[logical_reads] ,[text_size] ,[language] ,[date_format] ,[date_first] ,[quoted_identifier] ,[arithabort] ,[ansi_null_dflt_on] ,[ansi_defaults] ,[ansi_warnings] ,[ansi_padding] ,[ansi_nulls] ,[concat_null_yields_null] ,[transaction_isolation_level] ,[lock_timeout] ,[deadlock_priority] ,[row_count] ,[prev_error] ,[nest_level] ,[granted_query_memory] ,[executing_managed_code] ,[group_id] ,[query_hash] ,[query_plan_hash] ,[most_recent_session_id] ,[connect_time] ,[net_transport] ,[protocol_type] ,[protocol_version] ,[endpoint_id] ,[encrypt_option] ,[auth_scheme] ,[node_affinity] ,[num_reads] ,[num_writes] ,[last_read] ,[last_write] ,[net_packet_size] ,[client_net_address] ,[client_tcp_port] ,[local_net_address] ,[local_tcp_port] ,[parent_connection_id] ,[most_recent_sql_handle] ,[login_time] ,[host_name] ,[program_name] ,[host_process_id] ,[client_version] ,[client_interface_name] ,[security_id] ,[login_name] ,[nt_domain] ,[nt_user_name] ,[memory_usage] ,[total_scheduled_time] ,[last_request_start_time] ,[last_request_end_time] ,[is_user_process] ,[original_security_id] ,[original_login_name] ,[last_successful_logon] ,[last_unsuccessful_logon] ,[unsuccessful_logons] ,[authenticating_database_id] ,[InsertUTCDate] ,[EndRegUTCDate]) SELECT [session_id] ,[request_id] ,[start_time] ,[status] ,[command] ,[sql_handle] ,[statement_start_offset] ,[statement_end_offset] ,[plan_handle] ,[database_id] ,[user_id] ,[connection_id] ,[blocking_session_id] ,[wait_type] ,[wait_time] ,[last_wait_type] ,[wait_resource] ,[open_transaction_count] ,[open_resultset_count] ,[transaction_id] ,[context_info] ,[percent_complete] ,[estimated_completion_time] ,[cpu_time] ,[total_elapsed_time] ,[scheduler_id] ,[task_address] ,[reads] ,[writes] ,[logical_reads] ,[text_size] ,[language] ,[date_format] ,[date_first] ,[quoted_identifier] ,[arithabort] ,[ansi_null_dflt_on] ,[ansi_defaults] ,[ansi_warnings] ,[ansi_padding] ,[ansi_nulls] ,[concat_null_yields_null] ,[transaction_isolation_level] ,[lock_timeout] ,[deadlock_priority] ,[row_count] ,[prev_error] ,[nest_level] ,[granted_query_memory] ,[executing_managed_code] ,[group_id] ,[query_hash] ,[query_plan_hash] ,[most_recent_session_id] ,[connect_time] ,[net_transport] ,[protocol_type] ,[protocol_version] ,[endpoint_id] ,[encrypt_option] ,[auth_scheme] ,[node_affinity] ,[num_reads] ,[num_writes] ,[last_read] ,[last_write] ,[net_packet_size] ,[client_net_address] ,[client_tcp_port] ,[local_net_address] ,[local_tcp_port] ,[parent_connection_id] ,[most_recent_sql_handle] ,[login_time] ,[host_name] ,[program_name] ,[host_process_id] ,[client_version] ,[client_interface_name] ,[security_id] ,[login_name] ,[nt_domain] ,[nt_user_name] ,[memory_usage] ,[total_scheduled_time] ,[last_request_start_time] ,[last_request_end_time] ,[is_user_process] ,[original_security_id] ,[original_login_name] ,[last_successful_logon] ,[last_unsuccessful_logon] ,[unsuccessful_logons] ,[authenticating_database_id] ,[InsertUTCDate] ,[EndRegUTCDate] FROM [srv].[RequestStatistics] where [InsertUTCDate]<=@dt; delete from [srv].[RequestStatistics] where [InsertUTCDate]<=@dt; END GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 USE [ ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [srv].[vIndicatorStatistics] as SELECT [DATE] ,[execution_count] ,[max_total_elapsed_timeSec] ,[max_total_elapsed_timeLastSec] ,[max_total_elapsed_timeLastSec]-[max_total_elapsed_timeSec] as [DiffSnapshot] ,([max_total_elapsed_timeLastSec]-[max_total_elapsed_timeSec])*100/[max_total_elapsed_timeSec] as [% Snapshot] , case when ([max_total_elapsed_timeLastSec]<[max_total_elapsed_timeSec]) then N'' else case when ([max_total_elapsed_timeLastSec]>[max_total_elapsed_timeSec]) then N'' else N' ' end end as 'IndicatorSnapshot' FROM [srv].[IndicatorStatistics] GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 with tbl1 as ( select min(total_elapsed_timeSec) as [MIN], max(total_elapsed_timeSec) as [MAX], avg(total_elapsed_timeSec) as [AVG], [sql_handle], count(*) as [Count], '2017-11-01' as [DATE] from [srv].[vRequestStatisticsArchive] where [start_time] between '2017-11-01T07:00:00' and '2017-11-01T21:00:00' group by [sql_handle] ) , tbl2 as ( select min(total_elapsed_timeSec) as [MIN], max(total_elapsed_timeSec) as [MAX], avg(total_elapsed_timeSec) as [AVG], [sql_handle], count(*) as [Count], '2017-11-08' as [DATE] from [srv].[vRequestStatistics] where [start_time] between '2017-11-08T07:00:00' and '2017-11-08T21:00:00' group by [sql_handle] ) select coalesce(tbl1.[sql_handle], tbl2.[sql_handle]) as [sql_handle], coalesce(tbl1.[MIN], 0) as [MIN 01.11.2017], coalesce(tbl2.[MIN], 0) as [MIN 08.11.2017], coalesce(tbl1.[MAX], 0) as [MAX 01.11.2017], coalesce(tbl2.[MAX], 0) as [MAX 08.11.2017], coalesce(tbl1.[AVG], 0) as [AVG 01.11.2017], coalesce(tbl2.[AVG], 0) as [AVG 08.11.2017], coalesce(tbl1.[Count], 0) as [Count 01.11.2017], coalesce(tbl2.[Count], 0) as [Count 08.11.2017] from tbl1 left outer join tbl2 on tbl1.[sql_handle]=tbl2.[sql_handle]; GO