Пример реализации общего индикатора производительности MS SQL Server
Предисловие
Часто возникает потребность в создании такого индикатора производительности, который бы показывал состояние СУБД относительно предыдущего периода или конкретного дня. В статье Реализация индикатора производительности запросов, хранимых процедур и триггеров в MS SQL Server. Автотрассировка был предложен пример по реализации такого индикатора. Здесь же опишем еще один более простой способ, который ко всему прочему позволяет посмотреть исторически не просто за сколько выполнился запрос, но и как выполнился, а также получить планы выполнения на каждый момент времени.
Данный способ особенно полезен при предоставлении ежедневных отчетов выше стоящему руководству, т к его можно не только автоматизировать, но и вывести в отчет с минимальными техническими подробностями.
В данной статье будет рассмотрен пример реализации такого общего индикатора, где в качестве меры будет браться все время выполнения запросов (Total Elapsed Time).
Решение
Сначала приведем общий алгоритм:
1) Делаем снимок активных запросов
2) Сохраняем результат
3) В конце суток делаем общий анализ и результат сохраняем в таблицу
4) Делаем сравнительный анализ по полученным данным
Теперь приведем детализацию:
Для того, чтобы сделать снимок активных запросов, создадим следующие таблицы:
1) Таблица планов запросов:
Таблица планов запросов
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
2) Таблица запросов:
Таблица запросов
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
3) Таблица хранения снимков по активным запросам:
Таблица хранения снимков по активным запросам
USE [ИМЯ БД]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [srv].[RequestStatistics](
[session_id] [smallint] NOT NULL,
[request_id] [int] NOT NULL,
[start_time] [datetime] NOT NULL,
[status] [nvarchar](30) NOT NULL,
[command] [nvarchar](32) NOT 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] NOT NULL,
[connection_id] [uniqueidentifier] NULL,
[blocking_session_id] [smallint] NULL,
[wait_type] [nvarchar](60) NULL,
[wait_time] [int] NOT NULL,
[last_wait_type] [nvarchar](60) NOT NULL,
[wait_resource] [nvarchar](256) NOT NULL,
[open_transaction_count] [int] NOT NULL,
[open_resultset_count] [int] NOT NULL,
[transaction_id] [bigint] NOT NULL,
[context_info] [varbinary](128) NULL,
[percent_complete] [real] NOT NULL,
[estimated_completion_time] [bigint] NOT NULL,
[cpu_time] [int] NOT NULL,
[total_elapsed_time] [int] NOT NULL,
[scheduler_id] [int] NULL,
[task_address] [varbinary](8) NULL,
[reads] [bigint] NOT NULL,
[writes] [bigint] NOT NULL,
[logical_reads] [bigint] NOT NULL,
[text_size] [int] NOT NULL,
[language] [nvarchar](128) NULL,
[date_format] [nvarchar](3) NULL,
[date_first] [smallint] NOT NULL,
[quoted_identifier] [bit] NOT NULL,
[arithabort] [bit] NOT NULL,
[ansi_null_dflt_on] [bit] NOT NULL,
[ansi_defaults] [bit] NOT NULL,
[ansi_warnings] [bit] NOT NULL,
[ansi_padding] [bit] NOT NULL,
[ansi_nulls] [bit] NOT NULL,
[concat_null_yields_null] [bit] NOT NULL,
[transaction_isolation_level] [smallint] NOT NULL,
[lock_timeout] [int] NOT NULL,
[deadlock_priority] [int] NOT NULL,
[row_count] [bigint] NOT NULL,
[prev_error] [int] NOT NULL,
[nest_level] [int] NOT NULL,
[granted_query_memory] [int] NOT NULL,
[executing_managed_code] [bit] NOT NULL,
[group_id] [int] NOT 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
)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
И аналогично создается таблица для архива [srv].[RequestStatisticsArchive].
4) Таблица для хранения ежесуточных итоговых показателей:
Таблица для хранения ежесуточных итоговых показателей
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
5) Представление по записанным снимкам активных запросов:
Представление по записанным снимкам активных запросов
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
6) Представление по выборке активных запросов в текущий момент времени:
Представление по выборке активных запросов в текущий момент времени
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.blocking_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] NOT NULL,
[start_time] [datetime] NOT NULL,
[status] [nvarchar](30) NOT NULL,
[command] [nvarchar](32) NOT 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] NOT NULL,
[connection_id] [uniqueidentifier] NULL,
[blocking_session_id] [smallint] NULL,
[wait_type] [nvarchar](60) NULL,
[wait_time] [int] NOT NULL,
[last_wait_type] [nvarchar](60) NOT NULL,
[wait_resource] [nvarchar](256) NOT NULL,
[open_transaction_count] [int] NOT NULL,
[open_resultset_count] [int] NOT NULL,
[transaction_id] [bigint] NOT NULL,
[context_info] [varbinary](128) NULL,
[percent_complete] [real] NOT NULL,
[estimated_completion_time] [bigint] NOT NULL,
[cpu_time] [int] NOT NULL,
[total_elapsed_time] [int] NOT NULL,
[scheduler_id] [int] NULL,
[task_address] [varbinary](8) NULL,
[reads] [bigint] NOT NULL,
[writes] [bigint] NOT NULL,
[logical_reads] [bigint] NOT NULL,
[text_size] [int] NOT NULL,
[language] [nvarchar](128) NULL,
[date_format] [nvarchar](3) NULL,
[date_first] [smallint] NOT NULL,
[quoted_identifier] [bit] NOT NULL,
[arithabort] [bit] NOT NULL,
[ansi_null_dflt_on] [bit] NOT NULL,
[ansi_defaults] [bit] NOT NULL,
[ansi_warnings] [bit] NOT NULL,
[ansi_padding] [bit] NOT NULL,
[ansi_nulls] [bit] NOT NULL,
[concat_null_yields_null] [bit] NOT NULL,
[transaction_isolation_level] [smallint] NOT NULL,
[lock_timeout] [int] NOT NULL,
[deadlock_priority] [int] NOT NULL,
[row_count] [bigint] NOT NULL,
[prev_error] [int] NOT NULL,
[nest_level] [int] NOT NULL,
[granted_query_memory] [int] NOT NULL,
[executing_managed_code] [bit] NOT NULL,
[group_id] [int] NOT 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_mana
