Written by 16:05 Database administration, Performance Tuning

Implementing a Common MS SQL Server Performance Indicator

Introduction

There is often a need to create a performance indicator that would show database activity related to the previous period or specific day. In the article titled “Implementing SQL Server Performance Indicator for Queries, Stored Procedures, and Triggers”, we provided an example of implementing this indicator.

In this article, we are going to describe another simple way to track how and how long the query execution takes, as well as how to retrieve execution plans for each time point. 

This method is especially useful in the cases when you need to generate daily reports, so you can not only automate the method but also add it to the report with minimum technical details.

In this article, we will explore an example of implementing this common performance indicator where Total Elapsed Time will serve as a metric.

Solution

First, we provide a general algorithm:

1) Take a snapshot of active queries
2) Save the result
3) Perform a general analysis and save the result to a table at the end of the day
4) Make a comparative analysis of the data received

Now, we are going to provide some details.

To take a snapshot of active queries, create the following tables:

  1. Table of query plans
    USE [DATABASE_NAME]
    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
  2. Table of queries
    USE [DATABASE_NAME]
    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
  3. Table with snapshots of active queries:
    [expand title=”Code”]

    USE [DATABASE_NAME]
    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

    [/expand]

    In a similar way, we create a table for the [srv].[RequestStatisticsArchive] archive.

  4. Table with total daily statistics
    USE [DATABASE_NAME]
    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
  5. View by recorded snapshots of active queries
    [expand title=”Code”]

    USE [DATABASE_NAME]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE view [srv].[vRequestStatistics] as
    /*query statistics*/
    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]--The number of characters in the currently executing package or stored procedure in which the current statement is run. It can be used together with dynamic management functions, such as sql_handle, statement_end_offset and sys.dm_exec_sql_text to get the currently executing statement for the query. NULL values are allowed.
          ,rs.[statement_end_offset]--The number of characters in the currently executing package or stored procedure, in which the current statement was completed. It can be used together with dynamic management functions, such as sql_handle, statement_end_offset and sys.dm_exec_sql_text to get the currently executing statement for the query. NULL values are allowed.
          ,rs.[wait_type] collate Cyrillic_General_CI_AS as [wait_type]--waiting type
          ,rs.[wait_time]--If the query is currently blocked, the column contains waiting time (in ms). NULL values are not allowed.
    	  ,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]--If the query was blocked earlier, the column contains the type of the latest waiting time. NULL values are not allowed.
          ,rs.[wait_resource] collate Cyrillic_General_CI_AS as [wait_resource]--If the query is currently blocked, the column indicates the resource for which the query is waiting to release. NULL values are not allowed.
          ,rs.[open_transaction_count]--The number of transactions available for this query. NULL values are not allowed.
          ,rs.[open_resultset_count]--The number of resulting sets available for the given query. NULL values are not allowed.
          ,rs.[transaction_id]--A transaction identifier of the currently executing query. NULL values are not allowed.
          ,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]--CPU time (in ms) spent on executing the query. NULL values are not allowed.
    	  ,round(cast(rs.[cpu_time] as decimal(18,3))/1000, 3) as [cpu_timeSec]
          ,rs.[total_elapsed_time]--Total time elapsed since the request was received (in ms). NULL values are not allowed.
          ,rs.[scheduler_id]--An identifier of the scheduler that plans the given query. NULL values are not allowed.
          ,rs.[task_address]--The address of the memory block dedicated to the task relevant to this request. NULL values are allowed.
          ,rs.[reads]--The number of reads performed by the given query. NULL values are allowed.
          ,rs.[writes]--The number of writes performed by the given query. NULL values are not allowed.
          ,rs.[logical_reads]--The number of logical reads executed by the given query. NULL values are not allowed.
          ,rs.[text_size]--Setting the TEXTSIZE parameter for the given query. NULL values are not allowed.
          ,rs.[language] collate Cyrillic_General_CI_AS as [language]--Setting a language for the given query. NULL values are allowed.
          ,rs.[date_format] collate Cyrillic_General_CI_AS as [date_format]--Setting the DATEFORMAT parameter for the given query. NULL values are allowed.
          ,rs.[date_first]--Setting the DATEFIRST parameter for the given query. NULL values are not allowed.
          ,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]--transaction isolation level for the given query. NULL values are not allowed (0 is not specified, 1 – 5 values are set in the ascending order of the transaction isolation level).
          ,rs.[lock_timeout]--Lock timeout for the given query (in ms).  NULL values are not allowed.
    	  ,round(cast(rs.[lock_timeout] as decimal(18,3))/1000, 3) as [lock_timeoutSec]
          ,rs.[deadlock_priority]--The value of the DEADLOCK_PRIORITY parameter for the given query. NULL values are not allowed.
          ,rs.[row_count]--The number of rows returned to the client for this query. NULL values are not allowed.
          ,rs.[prev_error]--Previous error occurred when executing the query. NULL values are not allowed.
          ,rs.[nest_level]--The current level of code nesting level executed for the given query. NULL values are not allowed.
          ,rs.[granted_query_memory]--The number of pages to execute the query. NULL values are not allowed.
          ,rs.[executing_managed_code]--Indicates if the given query is currently executing the CLR object code (for example, procedure, type, or trigger). This toggle is set when the CLR object is in the stack, even when the Transact-SQL code is called from the environment. NULL values are not allowed.
          ,rs.[group_id]--ID of the workload group which this query belongs to. NULL values are not allowed.
          ,rs.[query_hash]--Binary hash-value is calculated for the query and used to identify queries with similar logic. It is possible to use query hash to determine the use of statistic resources for the queries that differ only in their literal values.
          ,rs.[query_plan_hash]--Binary hash-value is calculated for the query execution plan and used to identify similar execution plans. It is possible to use plan hash to find total query cost with similar execution plans. 
    	  ,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]--Еhe number of characters in the currently executing package or stored procedure in which the current statement is run. It can be used together with the dynamic management functions, such as sql_handle, statement_end_offset, and sys.dm_exec_sql_text to get the currently executing statement for the query. NULL values are allowed.
          ,rs.[statement_end_offset]--The number of characters in the currently executing package or stored procedure in which the current statement was completed. It can be used together with dynamic management functions, such as sql_handle, statement_end_offset, and sys.dm_exec_sql_text to get the currently executing statement for the query. NULL values are allowed.
          ,rs.[wait_type] collate Cyrillic_General_CI_AS--waiting type
          ,rs.[wait_time]--If the query is currently blocked, the column contains the current waiting time (in ms). NULL values are allowed.
    	  ,round(cast(rs.[wait_time] as decimal(18,3))/1000, 3) as [wait_timeSec]
          ,rs.[last_wait_type] collate Cyrillic_General_CI_AS--If the query was blocked earlier, the column contains the type of the latest waiting. NULL values are not allowed.
          ,rs.[wait_resource] collate Cyrillic_General_CI_AS--If the query is currently blocked, the column contains the resource for which the query is waiting to release. NULL values are not allowed.
          ,rs.[open_transaction_count]--The number of transactions available for the given query. NULL values are not allowed.
          ,rs.[open_resultset_count]--The number of resulting sets available for the given query. NULL values are not allowed.
          ,rs.[transaction_id]--Identifier of the transaction in which the query is being executed. NULL values are not allowed.
          ,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]--CPU time (in ms) spent on the query execution. NULL values are not allowed.
    	  ,round(cast(rs.[cpu_time] as decimal(18,3))/1000, 3) as [cpu_timeSec]
          ,rs.[total_elapsed_time]--Total time elapsed since the request was received (in ms). NULL values are not allowed.
          ,rs.[scheduler_id]--Identifier of the scheduler that plans the given query. NULL values are not allowed.
          ,rs.[task_address]--Address of the memory block dedicated to the task related to this query. NULL values are allowed.
          ,rs.[reads]--The number of reads performed by the given query. NULL values are not allowed.
          ,rs.[writes]--The number of writes performed by the given query. NULL values are not allowed.
          ,rs.[logical_reads]--The number of logic reads performed by the given query. NULL values are not allowed.
          ,rs.[text_size]--Setting the TEXTSIZE parameter for the given query. NULL values are not allowed.
          ,rs.[language] collate Cyrillic_General_CI_AS--Setting the language for the given query. NULL values are allowed.
          ,rs.[date_format] collate Cyrillic_General_CI_AS--Setting the DATEFORMAT parameter for the given query. NULL values are allowed.
          ,rs.[date_first]--Setting the DATEFIRST parameter for the given query. NULL values are not allowed.
          ,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]--Isolation level which was created along with the transaction for the given query. NULL values are not allowed (0 is not specified, 1 - 5 values are set ascending the transaction isolation level).
          ,rs.[lock_timeout]--Lock timeout for the given query (in ms). NULL values are not allowed.
    	  ,round(cast(rs.[lock_timeout] as decimal(18,3))/1000, 3) as [lock_timeoutSec]
          ,rs.[deadlock_priority]--Value of the DEADLOCK_PRIORITY parameter for the given query. NULL value is not allowed.
          ,rs.[row_count]--Number of rows returned to the client for the given query. NULL value is not allowed.
          ,rs.[prev_error]--Previous error occurred when executing the query. NULL values are not allowed.
          ,rs.[nest_level]--The current code nesting level executed for the given query. NULL value is not allowed.
          ,rs.[granted_query_memory]--The number of pages to execute the query. NULL value is not allowed.
          ,rs.[executing_managed_code]--indicates if the given query is currently executing the CLR object code (for example, procedure, type, or trigger). This toggle is set during the time when the CLR object is in the stack, even when the Transact-SQL code is called from the environment. NULL values are not allowed.
          ,rs.[group_id]--ID of the workload group which this query belongs to. NULL values are not allowed.
          ,rs.[query_hash]--Binary hash-value is calculated for the query and used to identify queries with similar logic. It is possible to use query hash to determine the use of statistic resources for queries that differ only in their literal values.
          ,rs.[query_plan_hash]--Binary hash-value is calculated for the query execution plan and used to identify similar execution plans. It is possible to use plan hash to find total query cost with similar execution plans. 
    	  ,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

    [/expand]

  6. View by the selection of current active requests:
    [expand title=”Code”]

    USE [DATABASE_NAME]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE view [inf].[vRequestDetail] as
    /*active, ready to be executed and pending queries, as well as those that explicitly block other sessions */
    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

    [/expand]

To take a snapshot of active queries and save it to the tables described above, create a stored procedure:

Example of implementing a stored procedure to collect snapshots of active queries

[expand title=”Code”]

USE [DATABASE_NAME]
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

[/expand]

It is possible to automate the collection process. For example, if we add a call to this stored procedure with a schedule to the Agent task (for example, every 10 seconds) or by event (for example, [Databases]. [Active Transactions]. [_ Total]> 0).

At the end of the day, we perform a general analysis and save the result to the table calling the stored procedure.

Implementation of the table

USE [DATABASE_NAME]
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

Implementation of the stored procedure

[expand title=”Code”]

USE [DATABASE_NAME]
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 (
									/*a list of tracked databases through DB_ID(‘database_name’)*/
								 )
			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

[/expand]

As you can see, the stored procedure also removes data from the [srv]. [RequestStatistics] table to prevent its expansion and ensure quick snapshots of active queries.

We can create this stored procedure during daily Agent calls at the end of the day.

Now, move to the very analysis of the collected data. To compare the current database state to the previous period, use the following view.

Implementation of the view

USE [DATABASE_NAME]
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'IMPROVED'
		else case when ([max_total_elapsed_timeLastSec]>[max_total_elapsed_timeSec]) then N'WORSENED'
		else N'IS NOT CHANGED' end
	   end as 'IndicatorSnapshot'
  FROM [srv].[IndicatorStatistics]
GO

To compare the current database state to the specific day, execute the following query:

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

Here, we compare the performance from 07-00 to 21-00 on November 1 and 8, 2017 (for example, it is the working time of the enterprise to exclude the analysis of the routine tasks).

We can generate this upload as a detailed report and attach it to the general report obtained from the [srv]. [VIndicatorStatistics] view.

To understand how the query was executed and what happened from time to time, it’s enough to use the [srv]. [vRequestStatistics] view with the filter by [start_time] (the date and time of the request received). 

Summary

In this article, we considered an example of implementing a general MS SQL Server performance indicator, which allows determining the database state related to the previous period or a specific day. As a metric, we used total elapsed time.

This method is universal. It is necessary to configure it, up to your needs, as well as to determine the measure, such as what we will collect and compare.

In addition, this approach allows detecting the issue immediately or for a certain period.

With the received data, it is possible to create a robot that would make decisions what queries to improve or disable to prevent system bottlenecks and notifications of administrators.

Tags: , , , , Last modified: September 22, 2021
Close