Предисловие
Часто возникает потребность в создании такого индикатора производительности, который бы показывал состояние СУБД относительно предыдущего периода или конкретного дня. В статье Реализация индикатора производительности запросов, хранимых процедур и триггеров в 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] 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
И аналогично создается таблица для архива [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.session_id;
GO
Для снятия снимка активных запросов с последующим сохранением его в выше описанные таблицы, создадим хранимую процедуру:
Пример реализации хранимой процедуры для сбора снимков активных запросов
USE [ИМЯ БД]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [srv].[AutoStatisticsActiveRequests]
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
declare @tbl0 table (
[SQLHandle] [varbinary](64) NOT NULL,
[TSQL] [nvarchar](max) NULL
);
declare @tbl1 table (
[PlanHandle] [varbinary](64) NOT NULL,
[SQLHandle] [varbinary](64) NOT NULL,
[QueryPlan] [xml] NULL
);
declare @tbl2 table (
[session_id] [smallint] NOT NULL,
[request_id] [int] NULL,
[start_time] [datetime] NULL,
[status] [nvarchar](30) NULL,
[command] [nvarchar](32) NULL,
[sql_handle] [varbinary](64) NULL,
[statement_start_offset] [int] NULL,
[statement_end_offset] [int] NULL,
[plan_handle] [varbinary](64) NULL,
[database_id] [smallint] NULL,
[user_id] [int] NULL,
[connection_id] [uniqueidentifier] NULL,
[blocking_session_id] [smallint] NULL,
[wait_type] [nvarchar](60) NULL,
[wait_time] [int] NULL,
[last_wait_type] [nvarchar](60) NULL,
[wait_resource] [nvarchar](256) NULL,
[open_transaction_count] [int] NULL,
[open_resultset_count] [int] NULL,
[transaction_id] [bigint] NULL,
[context_info] [varbinary](128) NULL,
[percent_complete] [real] NULL,
[estimated_completion_time] [bigint] NULL,
[cpu_time] [int] NULL,
[total_elapsed_time] [int] NULL,
[scheduler_id] [int] NULL,
[task_address] [varbinary](8) NULL,
[reads] [bigint] NULL,
[writes] [bigint] NULL,
[logical_reads] [bigint] NULL,
[text_size] [int] NULL,
[language] [nvarchar](128) NULL,
[date_format] [nvarchar](3) NULL,
[date_first] [smallint] NULL,
[quoted_identifier] [bit] NULL,
[arithabort] [bit] NULL,
[ansi_null_dflt_on] [bit] NULL,
[ansi_defaults] [bit] NULL,
[ansi_warnings] [bit] NULL,
[ansi_padding] [bit] NULL,
[ansi_nulls] [bit] NULL,
[concat_null_yields_null] [bit] NULL,
[transaction_isolation_level] [smallint] NULL,
[lock_timeout] [int] NULL,
[deadlock_priority] [int] NULL,
[row_count] [bigint] NULL,
[prev_error] [int] NULL,
[nest_level] [int] NULL,
[granted_query_memory] [int] NULL,
[executing_managed_code] [bit] NULL,
[group_id] [int] NULL,
[query_hash] [binary](8) NULL,
[query_plan_hash] [binary](8) NULL,
[most_recent_session_id] [int] NULL,
[connect_time] [datetime] NULL,
[net_transport] [nvarchar](40) NULL,
[protocol_type] [nvarchar](40) NULL,
[protocol_version] [int] NULL,
[endpoint_id] [int] NULL,
[encrypt_option] [nvarchar](40) NULL,
[auth_scheme] [nvarchar](40) NULL,
[node_affinity] [smallint] NULL,
[num_reads] [int] NULL,
[num_writes] [int] NULL,
[last_read] [datetime] NULL,
[last_write] [datetime] NULL,
[net_packet_size] [int] NULL,
[client_net_address] [varchar](48) NULL,
[client_tcp_port] [int] NULL,
[local_net_address] [varchar](48) NULL,
[local_tcp_port] [int] NULL,
[parent_connection_id] [uniqueidentifier] NULL,
[most_recent_sql_handle] [varbinary](64) NULL,
[login_time] [datetime] NULL,
[host_name] [nvarchar](128) NULL,
[program_name] [nvarchar](128) NULL,
[host_process_id] [int] NULL,
[client_version] [int] NULL,
[client_interface_name] [nvarchar](32) NULL,
[security_id] [varbinary](85) NULL,
[login_name] [nvarchar](128) NULL,
[nt_domain] [nvarchar](128) NULL,
[nt_user_name] [nvarchar](128) NULL,
[memory_usage] [int] NULL,
[total_scheduled_time] [int] NULL,
[last_request_start_time] [datetime] NULL,
[last_request_end_time] [datetime] NULL,
[is_user_process] [bit] NULL,
[original_security_id] [varbinary](85) NULL,
[original_login_name] [nvarchar](128) NULL,
[last_successful_logon] [datetime] NULL,
[last_unsuccessful_logon] [datetime] NULL,
[unsuccessful_logons] [bigint] NULL,
[authenticating_database_id] [int] NULL,
[TSQL] [nvarchar](max) NULL,
[QueryPlan] [xml] NULL
);
insert into @tbl2 (
[session_id]
,[request_id]
,[start_time]
,[status]
,[command]
,[sql_handle]
,[TSQL]
,[statement_start_offset]
,[statement_end_offset]
,[plan_handle]
,[QueryPlan]
,[database_id]
,[user_id]
,[connection_id]
,[blocking_session_id]
,[wait_type]
,[wait_time]
,[last_wait_type]
,[wait_resource]
,[open_transaction_count]
,[open_resultset_count]
,[transaction_id]
,[context_info]
,[percent_complete]
,[estimated_completion_time]
,[cpu_time]
,[total_elapsed_time]
,[scheduler_id]
,[task_address]
,[reads]
,[writes]
,[logical_reads]
,[text_size]
,[language]
,[date_format]
,[date_first]
,[quoted_identifier]
,[arithabort]
,[ansi_null_dflt_on]
,[ansi_defaults]
,[ansi_warnings]
,[ansi_padding]
,[ansi_nulls]
,[concat_null_yields_null]
,[transaction_isolation_level]
,[lock_timeout]
,[deadlock_priority]
,[row_count]
,[prev_error]
,[nest_level]
,[granted_query_memory]
,[executing_managed_code]
,[group_id]
,[query_hash]
,[query_plan_hash]
,[most_recent_session_id]
,[connect_time]
,[net_transport]
,[protocol_type]
,[protocol_version]
,[endpoint_id]
,[encrypt_option]
,[auth_scheme]
,[node_affinity]
,[num_reads]
,[num_writes]
,[last_read]
,[last_write]
,[net_packet_size]
,[client_net_address]
,[client_tcp_port]
,[local_net_address]
,[local_tcp_port]
,[parent_connection_id]
,[most_recent_sql_handle]
,[login_time]
,[host_name]
,[program_name]
,[host_process_id]
,[client_version]
,[client_interface_name]
,[security_id]
,[login_name]
,[nt_domain]
,[nt_user_name]
,[memory_usage]
,[total_scheduled_time]
,[last_request_start_time]
,[last_request_end_time]
,[is_user_process]
,[original_security_id]
,[original_login_name]
,[last_successful_logon]
,[last_unsuccessful_logon]
,[unsuccessful_logons]
,[authenticating_database_id]
)
select [session_id]
,[request_id]
,[start_time]
,[status]
,[command]
,[sql_handle]
,[TSQL]
,[statement_start_offset]
,[statement_end_offset]
,[plan_handle]
,[QueryPlan]
,[database_id]
,[user_id]
,[connection_id]
,[blocking_session_id]
,[wait_type]
,[wait_time]
,[last_wait_type]
,[wait_resource]
,[open_transaction_count]
,[open_resultset_count]
,[transaction_id]
,[context_info]
,[percent_complete]
,[estimated_completion_time]
,[cpu_time]
,[total_elapsed_time]
,[scheduler_id]
,[task_address]
,[reads]
,[writes]
,[logical_reads]
,[text_size]
,[language]
,[date_format]
,[date_first]
,[quoted_identifier]
,[arithabort]
,[ansi_null_dflt_on]
,[ansi_defaults]
,[ansi_warnings]
,[ansi_padding]
,[ansi_nulls]
,[concat_null_yields_null]
,[transaction_isolation_level]
,[lock_timeout]
,[deadlock_priority]
,[row_count]
,[prev_error]
,[nest_level]
,[granted_query_memory]
,[executing_managed_code]
,[group_id]
,[query_hash]
,[query_plan_hash]
,[most_recent_session_id]
,[connect_time]
,[net_transport]
,[protocol_type]
,[protocol_version]
,[endpoint_id]
,[encrypt_option]
,[auth_scheme]
,[node_affinity]
,[num_reads]
,[num_writes]
,[last_read]
,[last_write]
,[net_packet_size]
,[client_net_address]
,[client_tcp_port]
,[local_net_address]
,[local_tcp_port]
,[parent_connection_id]
,[most_recent_sql_handle]
,[login_time]
,[host_name]
,[program_name]
,[host_process_id]
,[client_version]
,[client_interface_name]
,[security_id]
,[login_name]
,[nt_domain]
,[nt_user_name]
,[memory_usage]
,[total_scheduled_time]
,[last_request_start_time]
,[last_request_end_time]
,[is_user_process]
,[original_security_id]
,[original_login_name]
,[last_successful_logon]
,[last_unsuccessful_logon]
,[unsuccessful_logons]
,[authenticating_database_id]
from [inf].[vRequestDetail];
insert into @tbl1 (
[PlanHandle],
[SQLHandle],
[QueryPlan]
)
select [plan_handle],
[sql_handle],
(select top(1) [query_plan] from sys.dm_exec_query_plan([plan_handle])) as [QueryPlan]
from @tbl2
where (select top(1) [query_plan] from sys.dm_exec_query_plan([plan_handle])) is not null
group by [plan_handle],
[sql_handle];
insert into @tbl0 (
[SQLHandle],
[TSQL]
)
select [sql_handle],
(select top(1) text from sys.dm_exec_sql_text([sql_handle])) as [TSQL]--[query_text]
from @tbl2
where (select top(1) text from sys.dm_exec_sql_text([sql_handle])) is not null
group by [sql_handle];
;merge [srv].[SQLQuery] as trg
using @tbl0 as src on trg.[SQLHandle]=src.[SQLHandle]
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[SQLHandle],
[TSQL]
)
VALUES (
src.[SQLHandle],
src.[TSQL]
);
;merge [srv].[PlanQuery] as trg
using @tbl1 as src on trg.[SQLHandle]=src.[SQLHandle] and trg.[PlanHandle]=src.[PlanHandle]
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[PlanHandle],
[SQLHandle],
[QueryPlan]
)
VALUES (
src.[PlanHandle],
src.[SQLHandle],
src.[QueryPlan]
);
select [session_id]
,[request_id]
,[start_time]
,[status]
,[command]
,[sql_handle]
,(select top(1) 1 from @tbl0 as t where t.[SQLHandle]=tt.[sql_handle]) as [TSQL]
,[statement_start_offset]
,[statement_end_offset]
,[plan_handle]
,(select top(1) 1 from @tbl1 as t where t.[PlanHandle]=tt.[plan_handle]) as [QueryPlan]
,[database_id]
,[user_id]
,[connection_id]
,[blocking_session_id]
,[wait_type]
,[wait_time]
,[last_wait_type]
,[wait_resource]
,[open_transaction_count]
,[open_resultset_count]
,[transaction_id]
,[context_info]
,[percent_complete]
,[estimated_completion_time]
,[cpu_time]
,[total_elapsed_time]
,[scheduler_id]
,[task_address]
,[reads]
,[writes]
,[logical_reads]
,[text_size]
,[language]
,[date_format]
,[date_first]
,[quoted_identifier]
,[arithabort]
,[ansi_null_dflt_on]
,[ansi_defaults]
,[ansi_warnings]
,[ansi_padding]
,[ansi_nulls]
,[concat_null_yields_null]
,[transaction_isolation_level]
,[lock_timeout]
,[deadlock_priority]
,[row_count]
,[prev_error]
,[nest_level]
,[granted_query_memory]
,[executing_managed_code]
,[group_id]
,[query_hash]
,[query_plan_hash]
,[most_recent_session_id]
,[connect_time]
,[net_transport]
,[protocol_type]
,[protocol_version]
,[endpoint_id]
,[encrypt_option]
,[auth_scheme]
,[node_affinity]
,[num_reads]
,[num_writes]
,[last_read]
,[last_write]
,[net_packet_size]
,[client_net_address]
,[client_tcp_port]
,[local_net_address]
,[local_tcp_port]
,[parent_connection_id]
,[most_recent_sql_handle]
,[login_time]
,[host_name]
,[program_name]
,[host_process_id]
,[client_version]
,[client_interface_name]
,[security_id]
,[login_name]
,[nt_domain]
,[nt_user_name]
,[memory_usage]
,[total_scheduled_time]
,[last_request_start_time]
,[last_request_end_time]
,[is_user_process]
,[original_security_id]
,[original_login_name]
,[last_successful_logon]
,[last_unsuccessful_logon]
,[unsuccessful_logons]
,[authenticating_database_id]
into #ttt
from @tbl2 as tt
group by [session_id]
,[request_id]
,[start_time]
,[status]
,[command]
,[sql_handle]
,[TSQL]
,[statement_start_offset]
,[statement_end_offset]
,[plan_handle]
,[database_id]
,[user_id]
,[connection_id]
,[blocking_session_id]
,[wait_type]
,[wait_time]
,[last_wait_type]
,[wait_resource]
,[open_transaction_count]
,[open_resultset_count]
,[transaction_id]
,[context_info]
,[percent_complete]
,[estimated_completion_time]
,[cpu_time]
,[total_elapsed_time]
,[scheduler_id]
,[task_address]
,[reads]
,[writes]
,[logical_reads]
,[text_size]
,[language]
,[date_format]
,[date_first]
,[quoted_identifier]
,[arithabort]
,[ansi_null_dflt_on]
,[ansi_defaults]
,[ansi_warnings]
,[ansi_padding]
,[ansi_nulls]
,[concat_null_yields_null]
,[transaction_isolation_level]
,[lock_timeout]
,[deadlock_priority]
,[row_count]
,[prev_error]
,[nest_level]
,[granted_query_memory]
,[executing_managed_code]
,[group_id]
,[query_hash]
,[query_plan_hash]
,[most_recent_session_id]
,[connect_time]
,[net_transport]
,[protocol_type]
,[protocol_version]
,[endpoint_id]
,[encrypt_option]
,[auth_scheme]
,[node_affinity]
,[num_reads]
,[num_writes]
,[last_read]
,[last_write]
,[net_packet_size]
,[client_net_address]
,[client_tcp_port]
,[local_net_address]
,[local_tcp_port]
,[parent_connection_id]
,[most_recent_sql_handle]
,[login_time]
,[host_name]
,[program_name]
,[host_process_id]
,[client_version]
,[client_interface_name]
,[security_id]
,[login_name]
,[nt_domain]
,[nt_user_name]
,[memory_usage]
,[total_scheduled_time]
,[last_request_start_time]
,[last_request_end_time]
,[is_user_process]
,[original_security_id]
,[original_login_name]
,[last_successful_logon]
,[last_unsuccessful_logon]
,[unsuccessful_logons]
,[authenticating_database_id];
UPDATE trg
SET
trg.[status] =case when (trg.[status]<>'suspended') then coalesce(src.[status] collate DATABASE_DEFAULT, trg.[status] collate DATABASE_DEFAULT) else trg.[status] end
--,trg.[command] =coalesce(src.[command] collate DATABASE_DEFAULT, trg.[command] collate DATABASE_DEFAULT)
--,trg.[sql_handle] =coalesce(src.[sql_handle] , trg.[sql_handle] )
--,trg.[TSQL] =coalesce(src.[TSQL] collate DATABASE_DEFAULT, trg.[TSQL] collate DATABASE_DEFAULT)
,trg.[statement_start_offset] =coalesce(src.[statement_start_offset] , trg.[statement_start_offset] )
,trg.[statement_end_offset] =coalesce(src.[statement_end_offset] , trg.[statement_end_offset] )
--,trg.[plan_handle] =coalesce(src.[plan_handle] , trg.[plan_handle] )
--,trg.[QueryPlan] =coalesce(src.[QueryPlan] , trg.[QueryPlan] )
--,trg.[connection_id] =coalesce(src.[connection_id] , trg.[connection_id] )
,trg.[blocking_session_id] =coalesce(trg.[blocking_session_id] , src.[blocking_session_id] )
,trg.[wait_type] =coalesce(trg.[wait_type] collate DATABASE_DEFAULT, src.[wait_type] collate DATABASE_DEFAULT)
,trg.[wait_time] =coalesce(src.[wait_time] , trg.[wait_time] )
,trg.[last_wait_type] =coalesce(src.[last_wait_type] collate DATABASE_DEFAULT, trg.[last_wait_type] collate DATABASE_DEFAULT)
,trg.[wait_resource] =coalesce(src.[wait_resource] collate DATABASE_DEFAULT, trg.[wait_resource] collate DATABASE_DEFAULT)
,trg.[open_transaction_count] =coalesce(src.[open_transaction_count] , trg.[open_transaction_count] )
,trg.[open_resultset_count] =coalesce(src.[open_resultset_count] , trg.[open_resultset_count] )
--,trg.[transaction_id] =coalesce(src.[transaction_id] , trg.[transaction_id] )
,trg.[context_info] =coalesce(src.[context_info] , trg.[context_info] )
,trg.[percent_complete] =coalesce(src.[percent_complete] , trg.[percent_complete] )
,trg.[estimated_completion_time] =coalesce(src.[estimated_completion_time] , trg.[estimated_completion_time] )
,trg.[cpu_time] =coalesce(src.[cpu_time] , trg.[cpu_time] )
,trg.[total_elapsed_time] =coalesce(src.[total_elapsed_time] , trg.[total_elapsed_time] )
,trg.[scheduler_id] =coalesce(src.[scheduler_id] , trg.[scheduler_id] )
,trg.[task_address] =coalesce(src.[task_address] , trg.[task_address] )
,trg.[reads] =coalesce(src.[reads] , trg.[reads] )
,trg.[writes] =coalesce(src.[writes] , trg.[writes] )
,trg.[logical_reads] =coalesce(src.[logical_reads] , trg.[logical_reads] )
,trg.[text_size] =coalesce(src.[text_size] , trg.[text_size] )
,trg.[language] =coalesce(src.[language] collate DATABASE_DEFAULT, trg.[language] collate DATABASE_DEFAULT)
,trg.[date_format] =coalesce(src.[date_format] , trg.[date_format] )
,trg.[date_first] =coalesce(src.[date_first] , trg.[date_first] )
,trg.[quoted_identifier] =coalesce(src.[quoted_identifier] , trg.[quoted_identifier] )
,trg.[arithabort] =coalesce(src.[arithabort] , trg.[arithabort] )
,trg.[ansi_null_dflt_on] =coalesce(src.[ansi_null_dflt_on] , trg.[ansi_null_dflt_on] )
,trg.[ansi_defaults] =coalesce(src.[ansi_defaults] , trg.[ansi_defaults] )
,trg.[ansi_warnings] =coalesce(src.[ansi_warnings] , trg.[ansi_warnings] )
,trg.[ansi_padding] =coalesce(src.[ansi_padding] , trg.[ansi_padding] )
,trg.[ansi_nulls] =coalesce(src.[ansi_nulls] , trg.[ansi_nulls] )
,trg.[concat_null_yields_null] =coalesce(src.[concat_null_yields_null] , trg.[concat_null_yields_null] )
,trg.[transaction_isolation_level] =coalesce(src.[transaction_isolation_level] , trg.[transaction_isolation_level] )
,trg.[lock_timeout] =coalesce(src.[lock_timeout] , trg.[lock_timeout] )
,trg.[deadlock_priority] =coalesce(src.[deadlock_priority] , trg.[deadlock_priority] )
,trg.[row_count] =coalesce(src.[row_count] , trg.[row_count] )
,trg.[prev_error] =coalesce(src.[prev_error] , trg.[prev_error] )
,trg.[nest_level] =coalesce(src.[nest_level] , trg.[nest_level] )
,trg.[granted_query_memory] =coalesce(src.[granted_query_memory] , trg.[granted_query_memory] )
,trg.[executing_managed_code] =coalesce(src.[executing_managed_code] , trg.[executing_managed_code] )
,trg.[group_id] =coalesce(src.[group_id] , trg.[group_id] )
,trg.[query_hash] =coalesce(src.[query_hash] , trg.[query_hash] )
,trg.[query_plan_hash] =coalesce(src.[query_plan_hash] , trg.[query_plan_hash] )
,trg.[most_recent_session_id] =coalesce(src.[most_recent_session_id] , trg.[most_recent_session_id] )
,trg.[connect_time] =coalesce(src.[connect_time] , trg.[connect_time] )
,trg.[net_transport] =coalesce(src.[net_transport] collate DATABASE_DEFAULT, trg.[net_transport] collate DATABASE_DEFAULT)
,trg.[protocol_type] =coalesce(src.[protocol_type] collate DATABASE_DEFAULT, trg.[protocol_type] collate DATABASE_DEFAULT)
,trg.[protocol_version] =coalesce(src.[protocol_version] , trg.[protocol_version] )
,trg.[endpoint_id] =coalesce(src.[endpoint_id] , trg.[endpoint_id] )
,trg.[encrypt_option] =coalesce(src.[encrypt_option] collate DATABASE_DEFAULT, trg.[encrypt_option] collate DATABASE_DEFAULT)
,trg.[auth_scheme] =coalesce(src.[auth_scheme] collate DATABASE_DEFAULT, trg.[auth_scheme] collate DATABASE_DEFAULT)
,trg.[node_affinity] =coalesce(src.[node_affinity] , trg.[node_affinity] )
,trg.[num_reads] =coalesce(src.[num_reads] , trg.[num_reads] )
,trg.[num_writes] =coalesce(src.[num_writes] , trg.[num_writes] )
,trg.[last_read] =coalesce(src.[last_read] , trg.[last_read] )
,trg.[last_write] =coalesce(src.[last_write] , trg.[last_write] )
,trg.[net_packet_size] =coalesce(src.[net_packet_size] , trg.[net_packet_size] )
,trg.[client_net_address] =coalesce(src.[client_net_address] collate DATABASE_DEFAULT, trg.[client_net_address] collate DATABASE_DEFAULT)
,trg.[client_tcp_port] =coalesce(src.[client_tcp_port] , trg.[client_tcp_port] )
,trg.[local_net_address] =coalesce(src.[local_net_address] collate DATABASE_DEFAULT, trg.[local_net_address] collate DATABASE_DEFAULT)
,trg.[local_tcp_port] =coalesce(src.[local_tcp_port] , trg.[local_tcp_port] )
,trg.[parent_connection_id] =coalesce(src.[parent_connection_id] , trg.[parent_connection_id] )
,trg.[most_recent_sql_handle] =coalesce(src.[most_recent_sql_handle] , trg.[most_recent_sql_handle] )
,trg.[login_time] =coalesce(src.[login_time] , trg.[login_time] )
,trg.[host_name] =coalesce(src.[host_name] collate DATABASE_DEFAULT, trg.[host_name] collate DATABASE_DEFAULT)
,trg.[program_name] =coalesce(src.[program_name] collate DATABASE_DEFAULT, trg.[program_name] collate DATABASE_DEFAULT)
,trg.[host_process_id] =coalesce(src.[host_process_id] , trg.[host_process_id] )
,trg.[client_version] =coalesce(src.[client_version] , trg.[client_version] )
,trg.[client_interface_name] =coalesce(src.[client_interface_name] collate DATABASE_DEFAULT, trg.[client_interface_name] collate DATABASE_DEFAULT)
,trg.[security_id] =coalesce(src.[security_id] , trg.[security_id] )
,trg.[login_name] =coalesce(src.[login_name] collate DATABASE_DEFAULT, trg.[login_name] collate DATABASE_DEFAULT)
,trg.[nt_domain] =coalesce(src.[nt_domain] collate DATABASE_DEFAULT, trg.[nt_domain] collate DATABASE_DEFAULT)
,trg.[nt_user_name] =coalesce(src.[nt_user_name] collate DATABASE_DEFAULT, trg.[nt_user_name] collate DATABASE_DEFAULT)
,trg.[memory_usage] =coalesce(src.[memory_usage] , trg.[memory_usage] )
,trg.[total_scheduled_time] =coalesce(src.[total_scheduled_time] , trg.[total_scheduled_time] )
,trg.[last_request_start_time] =coalesce(src.[last_request_start_time] , trg.[last_request_start_time] )
,trg.[last_request_end_time] =coalesce(src.[last_request_end_time] , trg.[last_request_end_time] )
,trg.[is_user_process] =coalesce(src.[is_user_process] , trg.[is_user_process] )
,trg.[original_security_id] =coalesce(src.[original_security_id] , trg.[original_security_id] )
,trg.[original_login_name] =coalesce(src.[original_login_name] collate DATABASE_DEFAULT, trg.[original_login_name] collate DATABASE_DEFAULT)
,trg.[last_successful_logon] =coalesce(src.[last_successful_logon] , trg.[last_successful_logon] )
,trg.[last_unsuccessful_logon] =coalesce(src.[last_unsuccessful_logon] , trg.[last_unsuccessful_logon] )
,trg.[unsuccessful_logons] =coalesce(src.[unsuccessful_logons] , trg.[unsuccessful_logons] )
,trg.[authenticating_database_id] =coalesce(src.[authenticating_database_id] , trg.[authenticating_database_id] )
from [srv].[RequestStatistics] as trg
inner join #ttt as src on (trg.[session_id]=src.[session_id])
and (trg.[request_id]=src.[request_id])
and (trg.[database_id]=src.[database_id])
and (trg.[user_id]=src.[user_id])
and (trg.[start_time]=src.[start_time])
and (trg.[command] collate DATABASE_DEFAULT=src.[command] collate DATABASE_DEFAULT)
and ((trg.[sql_handle]=src.[sql_handle] and src.[sql_handle] IS NOT NULL) or (src.[sql_handle] IS NULL))
and ((trg.[plan_handle]=src.[plan_handle] and src.[plan_handle] IS NOT NULL) or (src.[plan_handle] IS NULL))
and (trg.[transaction_id]=src.[transaction_id])
and ((trg.[connection_id]=src.[connection_id] and src.[connection_id] IS NOT NULL) or (src.[connection_id] IS NULL));
UPDATE trg
SET trg.[EndRegUTCDate]=GetUTCDate()
from [srv].[RequestStatistics] as trg
where not exists(
select top(1) 1
from #ttt as src
where (trg.[session_id]=src.[session_id])
and (trg.[request_id]=src.[request_id])
and (trg.[database_id]=src.[database_id])
and (trg.[user_id]=src.[user_id])
and (trg.[start_time]=src.[start_time])
and (trg.[command] collate DATABASE_DEFAULT=src.[command] collate DATABASE_DEFAULT)
and ((trg.[sql_handle]=src.[sql_handle] and src.[sql_handle] IS NOT NULL) or (src.[sql_handle] IS NULL))
and ((trg.[plan_handle]=src.[plan_handle] and src.[plan_handle] IS NOT NULL) or (src.[plan_handle] IS NULL))
and (trg.[transaction_id]=src.[transaction_id])
and ((trg.[connection_id]=src.[connection_id] and src.[connection_id] IS NOT NULL) or (src.[connection_id] IS NULL))
);
INSERT into [srv].[RequestStatistics] ([session_id]
,[request_id]
,[start_time]
,[status]
,[command]
,[sql_handle]
--,[TSQL]
,[statement_start_offset]
,[statement_end_offset]
,[plan_handle]
--,[QueryPlan]
,[database_id]
,[user_id]
,[connection_id]
,[blocking_session_id]
,[wait_type]
,[wait_time]
,[last_wait_type]
,[wait_resource]
,[open_transaction_count]
,[open_resultset_count]
,[transaction_id]
,[context_info]
,[percent_complete]
,[estimated_completion_time]
,[cpu_time]
,[total_elapsed_time]
,[scheduler_id]
,[task_address]
,[reads]
,[writes]
,[logical_reads]
,[text_size]
,[language]
,[date_format]
,[date_first]
,[quoted_identifier]
,[arithabort]
,[ansi_null_dflt_on]
,[ansi_defaults]
,[ansi_warnings]
,[ansi_padding]
,[ansi_nulls]
,[concat_null_yields_null]
,[transaction_isolation_level]
,[lock_timeout]
,[deadlock_priority]
,[row_count]
,[prev_error]
,[nest_level]
,[granted_query_memory]
,[executing_managed_code]
,[group_id]
,[query_hash]
,[query_plan_hash]
,[most_recent_session_id]
,[connect_time]
,[net_transport]
,[protocol_type]
,[protocol_version]
,[endpoint_id]
,[encrypt_option]
,[auth_scheme]
,[node_affinity]
,[num_reads]
,[num_writes]
,[last_read]
,[last_write]
,[net_packet_size]
,[client_net_address]
,[client_tcp_port]
,[local_net_address]
,[local_tcp_port]
,[parent_connection_id]
,[most_recent_sql_handle]
,[login_time]
,[host_name]
,[program_name]
,[host_process_id]
,[client_version]
,[client_interface_name]
,[security_id]
,[login_name]
,[nt_domain]
,[nt_user_name]
,[memory_usage]
,[total_scheduled_time]
,[last_request_start_time]
,[last_request_end_time]
,[is_user_process]
,[original_security_id]
,[original_login_name]
,[last_successful_logon]
,[last_unsuccessful_logon]
,[unsuccessful_logons]
,[authenticating_database_id])
select src.[session_id]
,src.[request_id]
,src.[start_time]
,src.[status]
,src.[command]
,src.[sql_handle]
--,src.[TSQL]
,src.[statement_start_offset]
,src.[statement_end_offset]
,src.[plan_handle]
--,src.[QueryPlan]
,src.[database_id]
,src.[user_id]
,src.[connection_id]
,src.[blocking_session_id]
,src.[wait_type]
,src.[wait_time]
,src.[last_wait_type]
,src.[wait_resource]
,src.[open_transaction_count]
,src.[open_resultset_count]
,src.[transaction_id]
,src.[context_info]
,src.[percent_complete]
,src.[estimated_completion_time]
,src.[cpu_time]
,src.[total_elapsed_time]
,src.[scheduler_id]
,src.[task_address]
,src.[reads]
,src.[writes]
,src.[logical_reads]
,src.[text_size]
,src.[language]
,src.[date_format]
,src.[date_first]
,src.[quoted_identifier]
,src.[arithabort]
,src.[ansi_null_dflt_on]
,src.[ansi_defaults]
,src.[ansi_warnings]
,src.[ansi_padding]
,src.[ansi_nulls]
,src.[concat_null_yields_null]
,src.[transaction_isolation_level]
,src.[lock_timeout]
,src.[deadlock_priority]
,src.[row_count]
,src.[prev_error]
,src.[nest_level]
,src.[granted_query_memory]
,src.[executing_managed_code]
,src.[group_id]
,src.[query_hash]
,src.[query_plan_hash]
,src.[most_recent_session_id]
,src.[connect_time]
,src.[net_transport]
,src.[protocol_type]
,src.[protocol_version]
,src.[endpoint_id]
,src.[encrypt_option]
,src.[auth_scheme]
,src.[node_affinity]
,src.[num_reads]
,src.[num_writes]
,src.[last_read]
,src.[last_write]
,src.[net_packet_size]
,src.[client_net_address]
,src.[client_tcp_port]
,src.[local_net_address]
,src.[local_tcp_port]
,src.[parent_connection_id]
,src.[most_recent_sql_handle]
,src.[login_time]
,src.[host_name]
,src.[program_name]
,src.[host_process_id]
,src.[client_version]
,src.[client_interface_name]
,src.[security_id]
,src.[login_name]
,src.[nt_domain]
,src.[nt_user_name]
,src.[memory_usage]
,src.[total_scheduled_time]
,src.[last_request_start_time]
,src.[last_request_end_time]
,src.[is_user_process]
,src.[original_security_id]
,src.[original_login_name]
,src.[last_successful_logon]
,src.[last_unsuccessful_logon]
,src.[unsuccessful_logons]
,src.[authenticating_database_id]
from #ttt as src
where not exists(
select top(1) 1
from [srv].[RequestStatistics] as trg
where (trg.[session_id]=src.[session_id])
and (trg.[request_id]=src.[request_id])
and (trg.[database_id]=src.[database_id])
and (trg.[user_id]=src.[user_id])
and (trg.[start_time]=src.[start_time])
and (trg.[command] collate DATABASE_DEFAULT=src.[command] collate DATABASE_DEFAULT)
and ((trg.[sql_handle]=src.[sql_handle] and src.[sql_handle] IS NOT NULL) or (src.[sql_handle] IS NULL))
and ((trg.[plan_handle]=src.[plan_handle] and src.[plan_handle] IS NOT NULL) or (src.[plan_handle] IS NULL))
and (trg.[transaction_id]=src.[transaction_id])
and ((trg.[connection_id]=src.[connection_id] and src.[connection_id] IS NOT NULL) or (src.[connection_id] IS NULL))
);
drop table #ttt;
END
GO
Процесс сбора можно автоматизировать. Например, если вызов данной хранимой процедуры поместить в задачу Агента с расписанием (например, каждые 10 секунд) или по событию (например, [Databases].[Active Transactions].[_Total] > 0).
В конце суток делаем общий анализ и результат сохраняем в таблицу через вызов хранимой процедуры.
Реализация таблицы
USE [ИМЯ БД]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [srv].[IndicatorStatistics](
[execution_count] [bigint] NOT NULL,
[max_total_elapsed_timeSec] [decimal](38, 6) NOT NULL,
[max_total_elapsed_timeLastSec] [decimal](38, 6) NOT NULL,
[DATE] [date] NOT NULL,
CONSTRAINT [PK_IndicatorStatistics] PRIMARY KEY CLUSTERED
(
[DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Реализация хранимой процедуры
USE [ИМЯ БД]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [srv].[AutoStatisticsTimeRequests]
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
delete from [srv].[TSQL_DAY_Statistics]
where [DATE]<=DateAdd(day,-180,GetUTCDate());
INSERT INTO [srv].[TSQL_DAY_Statistics]
([command]
,[DBName]
,[PlanHandle]
,[SqlHandle]
,[execution_count]
,[min_wait_timeSec]
,[min_estimated_completion_timeSec]
,[min_cpu_timeSec]
,[min_total_elapsed_timeSec]
,[min_lock_timeoutSec]
,[max_wait_timeSec]
,[max_estimated_completion_timeSec]
,[max_cpu_timeSec]
,[max_total_elapsed_timeSec]
,[max_lock_timeoutSec]
,[DATE])
SELECT [command]
,[DBName]
,[plan_handle]
,[sql_handle]
,count(*) as [execution_count]
,min([wait_timeSec]) as [min_wait_timeSec]
,min([estimated_completion_timeSec]) as [min_estimated_completion_timeSec]
,min([cpu_timeSec]) as [min_cpu_timeSec]
,min([total_elapsed_timeSec]) as [min_total_elapsed_timeSec]
,min([lock_timeoutSec]) as [min_lock_timeoutSec]
,max([wait_timeSec]) as [max_wait_timeSec]
,max([estimated_completion_timeSec]) as [max_estimated_completion_timeSec]
,max([cpu_timeSec]) as [max_cpu_timeSec]
,max([total_elapsed_timeSec]) as [max_total_elapsed_timeSec]
,max([lock_timeoutSec]) as [max_lock_timeoutSec]
,cast([InsertUTCDate] as [DATE]) as [DATE]
FROM [srv].[vRequestStatistics] with(readuncommitted)
where cast([InsertUTCDate] as date) = DateAdd(day,-1,cast(GetUTCDate() as date))
and [command] in (
'UPDATE',
'TRUNCATE TABLE',
'SET OPTION ON',
'SET COMMAND',
'SELECT INTO',
'SELECT',
'NOP',
'INSERT',
'EXECUTE',
'DELETE',
'DECLARE',
'CONDITIONAL',
'BULK INSERT',
'BEGIN TRY',
'BEGIN CATCH',
'AWAITING COMMAND',
'ASSIGN',
'ALTER TABLE'
)
and [database_id] in (
/*список отслеживаемых БД через DB_ID('ИМЯ_БД')*/
)
and [DBName] is not null
group by [command]
,[DBName]
,[plan_handle]
,[sql_handle]
,cast([InsertUTCDate] as [DATE]);
declare @inddt int=1;
;with tbl11 as (
select [SqlHandle], max([max_total_elapsed_timeSec]) as [max_total_elapsed_timeSec]
,min([max_total_elapsed_timeSec]) as [min_max_total_elapsed_timeSec]
,avg([max_total_elapsed_timeSec]) as [avg_max_total_elapsed_timeSec]
,sum([execution_count]) as [execution_count]
from [srv].[TSQL_DAY_Statistics]
where [max_total_elapsed_timeSec]>=0.001
and [DATE]<cast(DateAdd(day,-@inddt,cast(GetUTCDate() as date)) as date)
group by [SqlHandle]
)
, tbl12 as (
select [SqlHandle], max([max_total_elapsed_timeSec]) as [max_total_elapsed_timeSec]
,min([max_total_elapsed_timeSec]) as [min_max_total_elapsed_timeSec]
,avg([max_total_elapsed_timeSec]) as [avg_max_total_elapsed_timeSec]
,[DATE]
from [srv].[TSQL_DAY_Statistics]
where [max_total_elapsed_timeSec]>=0.001
and [DATE]=cast(DateAdd(day,-@inddt,cast(GetUTCDate() as date)) as date)
group by [SqlHandle], [DATE]
)
, tbl1_sum as (select sum([execution_count]) as [sum_execution_count] from tbl11)
, tbl1_total as (
select (select [sum_execution_count] from tbl1_sum) as [execution_count]
, sum(tbl11.[max_total_elapsed_timeSec]*tbl11.[execution_count])/(select [sum_execution_count] from tbl1_sum) as [max_total_elapsed_timeSec]
, sum(tbl12.[max_total_elapsed_timeSec]*tbl11.[execution_count])/(select [sum_execution_count] from tbl1_sum) as [max_total_elapsed_timeLastSec]
, tbl12.[DATE]
from tbl11
inner join tbl12 on tbl11.[SqlHandle]=tbl12.[SqlHandle]
group by tbl12.[DATE]
)
INSERT INTO [srv].[IndicatorStatistics]
([DATE]
,[execution_count]
,[max_total_elapsed_timeSec]
,[max_total_elapsed_timeLastSec]
)
select t1.[DATE]
,t1.[execution_count]
,t1.[max_total_elapsed_timeSec]
,t1.[max_total_elapsed_timeLastSec]
from tbl1_total as t1;
declare @dt datetime=DateAdd(day,-2,GetUTCDate());
INSERT INTO [srv].[RequestStatisticsArchive]
([session_id]
,[request_id]
,[start_time]
,[status]
,[command]
,[sql_handle]
,[statement_start_offset]
,[statement_end_offset]
,[plan_handle]
,[database_id]
,[user_id]
,[connection_id]
,[blocking_session_id]
,[wait_type]
,[wait_time]
,[last_wait_type]
,[wait_resource]
,[open_transaction_count]
,[open_resultset_count]
,[transaction_id]
,[context_info]
,[percent_complete]
,[estimated_completion_time]
,[cpu_time]
,[total_elapsed_time]
,[scheduler_id]
,[task_address]
,[reads]
,[writes]
,[logical_reads]
,[text_size]
,[language]
,[date_format]
,[date_first]
,[quoted_identifier]
,[arithabort]
,[ansi_null_dflt_on]
,[ansi_defaults]
,[ansi_warnings]
,[ansi_padding]
,[ansi_nulls]
,[concat_null_yields_null]
,[transaction_isolation_level]
,[lock_timeout]
,[deadlock_priority]
,[row_count]
,[prev_error]
,[nest_level]
,[granted_query_memory]
,[executing_managed_code]
,[group_id]
,[query_hash]
,[query_plan_hash]
,[most_recent_session_id]
,[connect_time]
,[net_transport]
,[protocol_type]
,[protocol_version]
,[endpoint_id]
,[encrypt_option]
,[auth_scheme]
,[node_affinity]
,[num_reads]
,[num_writes]
,[last_read]
,[last_write]
,[net_packet_size]
,[client_net_address]
,[client_tcp_port]
,[local_net_address]
,[local_tcp_port]
,[parent_connection_id]
,[most_recent_sql_handle]
,[login_time]
,[host_name]
,[program_name]
,[host_process_id]
,[client_version]
,[client_interface_name]
,[security_id]
,[login_name]
,[nt_domain]
,[nt_user_name]
,[memory_usage]
,[total_scheduled_time]
,[last_request_start_time]
,[last_request_end_time]
,[is_user_process]
,[original_security_id]
,[original_login_name]
,[last_successful_logon]
,[last_unsuccessful_logon]
,[unsuccessful_logons]
,[authenticating_database_id]
,[InsertUTCDate]
,[EndRegUTCDate])
SELECT [session_id]
,[request_id]
,[start_time]
,[status]
,[command]
,[sql_handle]
,[statement_start_offset]
,[statement_end_offset]
,[plan_handle]
,[database_id]
,[user_id]
,[connection_id]
,[blocking_session_id]
,[wait_type]
,[wait_time]
,[last_wait_type]
,[wait_resource]
,[open_transaction_count]
,[open_resultset_count]
,[transaction_id]
,[context_info]
,[percent_complete]
,[estimated_completion_time]
,[cpu_time]
,[total_elapsed_time]
,[scheduler_id]
,[task_address]
,[reads]
,[writes]
,[logical_reads]
,[text_size]
,[language]
,[date_format]
,[date_first]
,[quoted_identifier]
,[arithabort]
,[ansi_null_dflt_on]
,[ansi_defaults]
,[ansi_warnings]
,[ansi_padding]
,[ansi_nulls]
,[concat_null_yields_null]
,[transaction_isolation_level]
,[lock_timeout]
,[deadlock_priority]
,[row_count]
,[prev_error]
,[nest_level]
,[granted_query_memory]
,[executing_managed_code]
,[group_id]
,[query_hash]
,[query_plan_hash]
,[most_recent_session_id]
,[connect_time]
,[net_transport]
,[protocol_type]
,[protocol_version]
,[endpoint_id]
,[encrypt_option]
,[auth_scheme]
,[node_affinity]
,[num_reads]
,[num_writes]
,[last_read]
,[last_write]
,[net_packet_size]
,[client_net_address]
,[client_tcp_port]
,[local_net_address]
,[local_tcp_port]
,[parent_connection_id]
,[most_recent_sql_handle]
,[login_time]
,[host_name]
,[program_name]
,[host_process_id]
,[client_version]
,[client_interface_name]
,[security_id]
,[login_name]
,[nt_domain]
,[nt_user_name]
,[memory_usage]
,[total_scheduled_time]
,[last_request_start_time]
,[last_request_end_time]
,[is_user_process]
,[original_security_id]
,[original_login_name]
,[last_successful_logon]
,[last_unsuccessful_logon]
,[unsuccessful_logons]
,[authenticating_database_id]
,[InsertUTCDate]
,[EndRegUTCDate]
FROM [srv].[RequestStatistics]
where [InsertUTCDate]<=@dt;
delete from [srv].[RequestStatistics]
where [InsertUTCDate]<=@dt;
END
GO
Как видно из кода, хранимая процедура также чистит таблицу [srv].[RequestStatistics], чтобы предотвратить ее разрастание и обеспечить быстрые вставки снимков активных запросов.
Данную хранимую процедуру также можно задать в вызове Агента ежедневно в конце суток.
Перейдем теперь к самому анализу собранных данных.
Для сравнения текущего состояния СУБД к предыдущему периоду можно использовать следующее представление:
Реализация представления
USE [ИМЯ БД]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [srv].[vIndicatorStatistics] as
SELECT [DATE]
,[execution_count]
,[max_total_elapsed_timeSec]
,[max_total_elapsed_timeLastSec]
,[max_total_elapsed_timeLastSec]-[max_total_elapsed_timeSec] as [DiffSnapshot]
,([max_total_elapsed_timeLastSec]-[max_total_elapsed_timeSec])*100/[max_total_elapsed_timeSec] as [% Snapshot]
, case when ([max_total_elapsed_timeLastSec]<[max_total_elapsed_timeSec]) then N'УЛУЧШИЛАСЬ'
else case when ([max_total_elapsed_timeLastSec]>[max_total_elapsed_timeSec]) then N'УХУДШИЛАСЬ'
else N'НЕ ИЗМЕНИЛАСЬ' end
end as 'IndicatorSnapshot'
FROM [srv].[IndicatorStatistics]
GO
Для сравнения текущего состояния СУБД к конкретному дню можно выполнить следующий запрос:
Запрос
with tbl1 as (
select min(total_elapsed_timeSec) as [MIN], max(total_elapsed_timeSec) as [MAX], avg(total_elapsed_timeSec) as [AVG], [sql_handle], count(*) as [Count], '2017-11-01' as [DATE]
from [srv].[vRequestStatisticsArchive]
where [start_time] between '2017-11-01T07:00:00' and '2017-11-01T21:00:00'
group by [sql_handle]
)
, tbl2 as (
select min(total_elapsed_timeSec) as [MIN], max(total_elapsed_timeSec) as [MAX], avg(total_elapsed_timeSec) as [AVG], [sql_handle], count(*) as [Count], '2017-11-08' as [DATE]
from [srv].[vRequestStatistics]
where [start_time] between '2017-11-08T07:00:00' and '2017-11-08T21:00:00'
group by [sql_handle]
)
select coalesce(tbl1.[sql_handle], tbl2.[sql_handle]) as [sql_handle],
coalesce(tbl1.[MIN], 0) as [MIN 01.11.2017],
coalesce(tbl2.[MIN], 0) as [MIN 08.11.2017],
coalesce(tbl1.[MAX], 0) as [MAX 01.11.2017],
coalesce(tbl2.[MAX], 0) as [MAX 08.11.2017],
coalesce(tbl1.[AVG], 0) as [AVG 01.11.2017],
coalesce(tbl2.[AVG], 0) as [AVG 08.11.2017],
coalesce(tbl1.[Count], 0) as [Count 01.11.2017],
coalesce(tbl2.[Count], 0) as [Count 08.11.2017]
from tbl1
left outer join tbl2 on tbl1.[sql_handle]=tbl2.[sql_handle];
GO
Здесь сравнивается работа с 07-00 до 21-00 1 и 8 ноября 2017 года (например, это рабочее время предприятия, чтобы исключить анализ работы регламентных задач).
Данную выгрузку можно оформить как детальный отчет и прикрепить его к общему отчету, полученному из представления [srv].[vIndicatorStatistics].
Для понимания как выполнялся запрос и что происходило в тот или иной момент времени, достаточно обратиться к представлению [srv]. [vRequestStatistics] с фильтром по [start_time] (дата и время запуска запроса).
Результат
В данной статье был рассмотрен пример реализации общего индикатора производительности MS SQL Server, позволяющий определить состояние СУБД относительно предыдущего периода или конкретного дня. В качестве меры было взято все время выполнения запросов (Total Elapsed Time).
Данный метод является универсальным. Т е его необходимо настраивать, исходя из потребностей, а также определить меру (т е что будем собирать и сравнивать).
Также данный подход позволяет обнаружить проблему сразу или за определенный диапазон прошедшего времени.
На полученных данных можно реализовать робота, который бы принимал решения какие запросы улучшать или отключать, чтобы предупредить падение всей системы, с последующим оповещением администраторов.
Источники:
» Реализация индикатора производительности запросов, хранимых процедур и триггеров в MS SQL Server
» MSDN