Реализация индикатора производительности запросов, хранимых процедур и триггеров в MS SQL Server. Автотрассировка

  • Tutorial

Предисловие


Администратору баз данных рано или поздно захочется иметь индикатор производительности, который бы показывал все ли хорошо с запросами. Также известно, что запуск Профайлера на целые сутки существенно загружает систему, и поэтому не может быть оптимальным решением в базе данных, которая используется 24x7.

Так как же определять состояния запросов? И как запускать трассировку при обнаружении проблем с запросами без участия человека?

В данной статье приведу реализацию индикатора производительности запросов, хранимых процедур и триггеров, а также их использование для запуска трассировки.

Решение


Сначала общий подход реализации индикатора производительности запросов, хранимых процедур и триггеров:

1) создать необходимые таблицы для сбора и анализа информации
2) создать представления для сбора информации
3) создать хранимые процедуры для сбора информации
4) создать представления для вывода информации

А теперь реализация:

1) создать необходимые таблицы для сбора и анализа информации:

1.1) для запросов:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [srv].[SQL_StatementExecStat](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[InsertDate] [datetime] NULL,
	[QueryHash] [binary](8) NULL,
	[ExecutionCount] [bigint] NULL,
	[TotalWorkerTime] [bigint] NULL,
	[StatementText] [nvarchar](max) NULL,
	[TotalElapsedTime] [bigint] NULL,
 CONSTRAINT [PK_SQL_StatementExecStat] PRIMARY KEY CLUSTERED 
(
	[ID] 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

SET ANSI_PADDING ON
GO



1.2) для хранимых процедур:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[SQL_ProcedureExecStat](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[InsertDate] [datetime] NULL,
	[database_id] [int] NULL,
	[object_id] [int] NULL,
	[ExecutionCount] [bigint] NULL,
	[TotalWorkerTime] [bigint] NULL,
	[TotalElapsedTime] [bigint] NULL,
	[TotalPhysicalReads] [bigint] NULL,
	[TotalLogicalReads] [bigint] NULL,
	[TotalLogicalWrites] [bigint] NULL,
 CONSTRAINT [PK_SQL_ProcedureExecStat] PRIMARY KEY CLUSTERED 
(
	[ID] 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



1.3) для триггеров:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[SQL_TriggerExecStat](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[InsertDate] [datetime] NULL,
	[database_id] [int] NULL,
	[object_id] [int] NULL,
	[ExecutionCount] [bigint] NULL,
	[TotalWorkerTime] [bigint] NULL,
	[TotalElapsedTime] [bigint] NULL
) ON [PRIMARY]

GO



2) создать представления для сбора информации (здесь также можно вставить фильтры, т е убирать ненужную информацию (например, запросы и процедуры с триггерами репликаций и т д)):

2.1) для запросов:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [srv].[vStatementExecInfo] as 
with info as (
SELECT
	query_stats.query_hash					AS QueryHash, 
    SUM(query_stats.total_worker_time	) /
	SUM(query_stats.execution_count)		AS AvgCPU_Time,
	SUM(query_stats.execution_count		)	AS ExecutionCount,
	SUM(query_stats.total_worker_time	)	AS TotalWorkerTime,
    MIN(query_stats.statement_text		)	AS StatementText,
    MIN(query_stats.min_worker_time		)	AS MinWorkerTime,
    MAX(query_stats.max_worker_time		)	AS MaxWorkerTime,
	SUM(query_stats.total_physical_reads)	AS TotalPhysicalReads,
    MIN(query_stats.min_physical_reads	)	AS MinPhysicalReads,
    MAX(query_stats.max_physical_reads	)	AS MaxPhysicalReads,
	SUM(query_stats.total_physical_reads) / 
	SUM(query_stats.execution_count)		AS AvgPhysicalReads,
	SUM(query_stats.total_logical_writes)	AS TotalLogicalWrites,
    MIN(query_stats.min_logical_writes	)	AS MinLogicalWrites,
    MAX(query_stats.max_logical_writes	)	AS MaxLogicalWrites,
	SUM(query_stats.total_logical_writes) / 
	SUM(query_stats.execution_count)		AS AvgLogicalWrites,
	SUM(query_stats.total_logical_reads )	AS TotalLogicalReads,
    MIN(query_stats.min_logical_reads	)	AS MinLogicalReads,
    MAX(query_stats.max_logical_reads	)	AS MaxLogicalReads,
	SUM(query_stats.total_logical_reads ) / 
	SUM(query_stats.execution_count)		AS AvgLogicalReads,
	SUM(query_stats.total_elapsed_time	)	AS TotalElapsedTime,
    MIN(query_stats.min_elapsed_time	)	AS MinElapsedTime,
    MAX(query_stats.max_elapsed_time	)	AS MaxElapsedTime,
	SUM(query_stats.total_elapsed_time	) / 
	SUM(query_stats.execution_count)		AS AvgElapsedTime,
 	MIN(query_stats.creation_time		)	AS MinCreationTime,
	MAX(query_stats.last_execution_time	)	AS LastExecuteTime
FROM 
    (SELECT QS.query_hash
			,QS.total_worker_time	
			,QS.execution_count			
			,QS.min_worker_time		
			,QS.max_worker_time		
			,QS.min_physical_reads	
			,QS.max_physical_reads	
			,QS.total_physical_reads
			,QS.total_logical_writes
			,QS.min_logical_writes	
			,QS.max_logical_writes	
			,QS.min_logical_reads	
			,QS.max_logical_reads	
			,QS.total_logical_reads 
			,QS.min_elapsed_time	
			,QS.max_elapsed_time	
			,QS.total_elapsed_time	
			,QS.creation_time		
			,QS.last_execution_time
    ,SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
WHERE execution_count > 1
and last_execution_time >= dateadd(hour,-3,getdate())
GROUP BY query_stats.query_hash)
select 
	QueryHash, 
	AvgCPU_Time,
	ExecutionCount,
	TotalWorkerTime,
	StatementText,
	MinWorkerTime,
	MaxWorkerTime,
	TotalPhysicalReads,
	MinPhysicalReads,
	MaxPhysicalReads,
	AvgPhysicalReads,
	TotalLogicalWrites,
	MinLogicalWrites,
	MaxLogicalWrites,
	AvgLogicalWrites,
	TotalLogicalReads,
	MinLogicalReads,
	MaxLogicalReads,
	AvgLogicalReads,
	TotalElapsedTime,
	MinElapsedTime,
	MaxElapsedTime,
	AvgElapsedTime,
	MinCreationTime,
	LastExecuteTime
from info

GO



Здесь используются два системных представления sys.dm_exec_query_stats и sys.dm_exec_sql_text

2.2) для хранимых процедур:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [srv].[vProcedureExecInfo] as 
with info as (
SELECT
	procedure_stats.database_id					AS database_id,
	procedure_stats.object_id					AS object_id,
	MIN(procedure_stats.type)						AS type, 
    SUM(procedure_stats.total_worker_time	) /
	SUM(procedure_stats.execution_count)		AS AvgCPU_Time,
	SUM(procedure_stats.execution_count		)	AS ExecutionCount,
	SUM(procedure_stats.total_worker_time	)	AS TotalWorkerTime,
    MIN(procedure_stats.ProcedureText		)	AS ProcedureText,
    MIN(procedure_stats.min_worker_time		)	AS MinWorkerTime,
    MAX(procedure_stats.max_worker_time		)	AS MaxWorkerTime,
	SUM(procedure_stats.total_physical_reads)	AS TotalPhysicalReads,
    MIN(procedure_stats.min_physical_reads	)	AS MinPhysicalReads,
    MAX(procedure_stats.max_physical_reads	)	AS MaxPhysicalReads,
	SUM(procedure_stats.total_physical_reads) / 
	SUM(procedure_stats.execution_count)		AS AvgPhysicalReads,
	SUM(procedure_stats.total_logical_writes)	AS TotalLogicalWrites,
    MIN(procedure_stats.min_logical_writes	)	AS MinLogicalWrites,
    MAX(procedure_stats.max_logical_writes	)	AS MaxLogicalWrites,
	SUM(procedure_stats.total_logical_writes) / 
	SUM(procedure_stats.execution_count)		AS AvgLogicalWrites,
	SUM(procedure_stats.total_logical_reads )	AS TotalLogicalReads,
    MIN(procedure_stats.min_logical_reads	)	AS MinLogicalReads,
    MAX(procedure_stats.max_logical_reads	)	AS MaxLogicalReads,
	SUM(procedure_stats.total_logical_reads ) / 
	SUM(procedure_stats.execution_count)		AS AvgLogicalReads,
	SUM(procedure_stats.total_elapsed_time	)	AS TotalElapsedTime,
    MIN(procedure_stats.min_elapsed_time	)	AS MinElapsedTime,
    MAX(procedure_stats.max_elapsed_time	)	AS MaxElapsedTime,
	SUM(procedure_stats.total_elapsed_time	) / 
	SUM(procedure_stats.execution_count)		AS AvgElapsedTime,
 	MIN(procedure_stats.cached_time		)	AS MinCachedTime,
	MAX(procedure_stats.last_execution_time	)	AS LastExecuteTime
FROM 
    (SELECT QS.database_id
			,QS.object_id
			,QS.type
			,QS.total_worker_time	
			,QS.execution_count			
			,QS.min_worker_time		
			,QS.max_worker_time		
			,QS.min_physical_reads	
			,QS.max_physical_reads	
			,QS.total_physical_reads
			,QS.total_logical_writes
			,QS.min_logical_writes	
			,QS.max_logical_writes	
			,QS.min_logical_reads	
			,QS.max_logical_reads	
			,QS.total_logical_reads 
			,QS.min_elapsed_time	
			,QS.max_elapsed_time	
			,QS.total_elapsed_time	
			,QS.cached_time		
			,QS.last_execution_time
			,ST.text as Proceduretext
     FROM sys.dm_exec_Procedure_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as procedure_stats
WHERE execution_count > 1
and last_execution_time >= dateadd(hour,-3,getdate())
GROUP BY database_id,object_id)
select 
	database_id,
	object_id,
	type, 
	AvgCPU_Time,
	ExecutionCount,
	TotalWorkerTime,
	ProcedureText,
	MinWorkerTime,
	MaxWorkerTime,
	TotalPhysicalReads,
	MinPhysicalReads,
	MaxPhysicalReads,
	AvgPhysicalReads,
	TotalLogicalWrites,
	MinLogicalWrites,
	MaxLogicalWrites,
	AvgLogicalWrites,
	TotalLogicalReads,
	MinLogicalReads,
	MaxLogicalReads,
	AvgLogicalReads,
	TotalElapsedTime,
	MinElapsedTime,
	MaxElapsedTime,
	AvgElapsedTime,
	MinCachedTime,
	LastExecuteTime
from info

GO



Здесь используются два системных представления sys.dm_exec_Procedure_stats и sys.dm_exec_sql_text

2.3) для триггеров:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [srv].[vTriggerExecInfo] as 
with info as (
SELECT
	procedure_stats.database_id					AS database_id,
	procedure_stats.object_id					AS object_id,
	MIN(procedure_stats.type)						AS type, 
    SUM(procedure_stats.total_worker_time	) /
	SUM(procedure_stats.execution_count)		AS AvgCPU_Time,
	SUM(procedure_stats.execution_count		)	AS ExecutionCount,
	SUM(procedure_stats.total_worker_time	)	AS TotalWorkerTime,
    MIN(procedure_stats.ProcedureText		)	AS ProcedureText,
    MIN(procedure_stats.min_worker_time		)	AS MinWorkerTime,
    MAX(procedure_stats.max_worker_time		)	AS MaxWorkerTime,
	SUM(procedure_stats.total_physical_reads)	AS TotalPhysicalReads,
    MIN(procedure_stats.min_physical_reads	)	AS MinPhysicalReads,
    MAX(procedure_stats.max_physical_reads	)	AS MaxPhysicalReads,
	SUM(procedure_stats.total_physical_reads) / 
	SUM(procedure_stats.execution_count)		AS AvgPhysicalReads,
	SUM(procedure_stats.total_logical_writes)	AS TotalLogicalWrites,
    MIN(procedure_stats.min_logical_writes	)	AS MinLogicalWrites,
    MAX(procedure_stats.max_logical_writes	)	AS MaxLogicalWrites,
	SUM(procedure_stats.total_logical_writes) / 
	SUM(procedure_stats.execution_count)		AS AvgLogicalWrites,
	SUM(procedure_stats.total_logical_reads )	AS TotalLogicalReads,
    MIN(procedure_stats.min_logical_reads	)	AS MinLogicalReads,
    MAX(procedure_stats.max_logical_reads	)	AS MaxLogicalReads,
	SUM(procedure_stats.total_logical_reads ) / 
	SUM(procedure_stats.execution_count)		AS AvgLogicalReads,
	SUM(procedure_stats.total_elapsed_time	)	AS TotalElapsedTime,
    MIN(procedure_stats.min_elapsed_time	)	AS MinElapsedTime,
    MAX(procedure_stats.max_elapsed_time	)	AS MaxElapsedTime,
	SUM(procedure_stats.total_elapsed_time	) / 
	SUM(procedure_stats.execution_count)		AS AvgElapsedTime,
 	MIN(procedure_stats.cached_time		)	AS MinCachedTime,
	MAX(procedure_stats.last_execution_time	)	AS LastExecuteTime
FROM 
    (SELECT QS.database_id
			,QS.object_id
			,QS.type
			,QS.total_worker_time	
			,QS.execution_count			
			,QS.min_worker_time		
			,QS.max_worker_time		
			,QS.min_physical_reads	
			,QS.max_physical_reads	
			,QS.total_physical_reads
			,QS.total_logical_writes
			,QS.min_logical_writes	
			,QS.max_logical_writes	
			,QS.min_logical_reads	
			,QS.max_logical_reads	
			,QS.total_logical_reads 
			,QS.min_elapsed_time	
			,QS.max_elapsed_time	
			,QS.total_elapsed_time	
			,QS.cached_time		
			,QS.last_execution_time
			,ST.text as Proceduretext
     FROM sys.dm_exec_trigger_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as procedure_stats
WHERE execution_count > 1
and last_execution_time >= dateadd(hour,-3,getdate())
GROUP BY database_id,object_id)
select 
	database_id,
	object_id,
	type, 
	AvgCPU_Time,
	ExecutionCount,
	TotalWorkerTime,
	ProcedureText,
	MinWorkerTime,
	MaxWorkerTime,
	TotalPhysicalReads,
	MinPhysicalReads,
	MaxPhysicalReads,
	AvgPhysicalReads,
	TotalLogicalWrites,
	MinLogicalWrites,
	MaxLogicalWrites,
	AvgLogicalWrites,
	TotalLogicalReads,
	MinLogicalReads,
	MaxLogicalReads,
	AvgLogicalReads,
	TotalElapsedTime,
	MinElapsedTime,
	MaxElapsedTime,
	AvgElapsedTime,
	MinCachedTime,
	LastExecuteTime
from info

GO



Здесь используются два системных представления sys.dm_exec_trigger_stats и sys.dm_exec_sql_text

3) создать хранимые процедуры для сбора информации:

3.1) для запросов:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [srv].[InsertForSQL_StatementExecStat]
	@koef decimal(12,2)=0.0 --коэффициент сбора,
	--подбирается экспериментальным путем для более точного сбора,
	--в большинстве случаев можно оставить 0.0,
	--если частота запуска сбора не будет превышать 5 минут
	--на точность расчетов влияет частота сбора и коэффициент сбора
	--чем чаще запуск сбора, тем меньше влияет коэффициент сбора
AS
BEGIN
	SET NOCOUNT ON;

	declare @AvgCPU_Time bigint
       ,@MaxAvgCPU_Time bigint
	   ,@AvgTotalWorkerTime bigint
	   ,@MaxTotalWorkerTime bigint
	   ,@AvgAvgElapsedTime bigint
	   ,@MaxAvgElapsedTime bigint
	   ,@AvgTotalElapsedTime bigint
	   ,@MaxTotalElapsedTime bigint
	
	select
		@AvgCPU_Time			= AVG(AvgCPU_Time),
		@MaxAvgCPU_Time			= max(AvgCPU_Time),
		@AvgTotalWorkerTime		= AVG(TotalWorkerTime),
		@MaxTotalWorkerTime		= max(TotalWorkerTime),
		@AvgAvgElapsedTime		= AVG(AvgElapsedTime),
		@MaxAvgElapsedTime		= max(AvgElapsedTime),
		@AvgTotalElapsedTime	= AVG(TotalElapsedTime),
		@MaxTotalElapsedTime	= max(TotalElapsedTime)
	from srv.vStatementExecInfo;
	
	insert into srv.SQL_StatementExecStat
	(
		[InsertDate]
	   ,[QueryHash]
	   ,[ExecutionCount]
	   ,[TotalWorkerTime]
	   ,[StatementText]
	   ,[TotalElapsedTime])
	select
		getdate()
	   ,[QueryHash]
	   ,[ExecutionCount]
	   ,[TotalWorkerTime]
	   ,[StatementText]
	   ,[TotalElapsedTime]
	from srv.vStatementExecInfo
	where(AvgCPU_Time      > @AvgCPU_Time		  + @koef * (@MaxAvgCPU_Time	  - @AvgCPU_Time))
	  or (TotalWorkerTime  > @AvgTotalWorkerTime  + @koef * (@MaxTotalWorkerTime  - @AvgTotalWorkerTime))
	  or (AvgElapsedTime   > @AvgAvgElapsedTime   + @koef * (@MaxAvgElapsedTime   - @AvgAvgElapsedTime))
	  or (TotalElapsedTime > @AvgTotalElapsedTime + @koef * (@MaxTotalElapsedTime - @AvgTotalElapsedTime));
END

GO



3.2) для хранимых процедур:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [srv].[InsertForProcedureExecStat]
	@koef decimal(12,2)=0.0 --коэффициент сбора,
	--подбирается экспериментальным путем для более точного сбора,
	--в большинстве случаев можно оставить 0.0,
	--если частота запуска сбора не будет превышать 5 минут
	--на точность расчетов влияет частота сбора и коэффициент сбора
	--чем чаще запуск сбора, тем меньше влияет коэффициент сбора
AS
BEGIN
	SET NOCOUNT ON;

	declare @AvgCPU_Time bigint
       ,@MaxAvgCPU_Time bigint
	   ,@AvgTotalWorkerTime bigint
	   ,@MaxTotalWorkerTime bigint
	   ,@AvgAvgElapsedTime bigint
	   ,@MaxAvgElapsedTime bigint
	   ,@AvgTotalElapsedTime bigint
	   ,@MaxTotalElapsedTime bigint;
	
	select
		@AvgCPU_Time			= AVG(AvgCPU_Time),
		@MaxAvgCPU_Time			= max(AvgCPU_Time),
		@AvgTotalWorkerTime		= AVG(TotalWorkerTime),
		@MaxTotalWorkerTime		= max(TotalWorkerTime),
		@AvgAvgElapsedTime		= AVG(AvgElapsedTime),
		@MaxAvgElapsedTime		= max(AvgElapsedTime),
		@AvgTotalElapsedTime	= AVG(TotalElapsedTime),
		@MaxTotalElapsedTime	= max(TotalElapsedTime)
	from srv.vProcedureExecInfo;
	
	insert into srv.SQL_ProcedureExecStat
	(
		[InsertDate]
	   ,database_id
	   ,object_id
	   ,[ExecutionCount]
	   ,[TotalWorkerTime]
	   ,[TotalElapsedTime]
	   ,[TotalPhysicalReads]
	   ,[TotalLogicalReads]
	   ,[TotalLogicalWrites])
	select
		getdate()
	   ,database_id
	   ,object_id
	   ,[ExecutionCount]
	   ,[TotalWorkerTime]
	   ,[TotalElapsedTime]
	   ,[TotalPhysicalReads]
	   ,[TotalLogicalReads]
	   ,[TotalLogicalWrites]
	from srv.vProcedureExecInfo
	where(AvgCPU_Time      > @AvgCPU_Time		  + @koef * (@MaxAvgCPU_Time	  - @AvgCPU_Time))
	  or (TotalWorkerTime  > @AvgTotalWorkerTime  + @koef * (@MaxTotalWorkerTime  - @AvgTotalWorkerTime))
	  or (AvgElapsedTime   > @AvgAvgElapsedTime   + @koef * (@MaxAvgElapsedTime   - @AvgAvgElapsedTime))
	  or (TotalElapsedTime > @AvgTotalElapsedTime + @koef * (@MaxTotalElapsedTime - @AvgTotalElapsedTime));
END

GO



3.3) для триггеров:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [srv].[InsertForTriggerExecStat]
	@koef decimal(12,2)=0.0 --коэффициент сбора,
	--подбирается экспериментальным путем для более точного сбора,
	--в большинстве случаев можно оставить 0.0,
	--если частота запуска сбора не будет превышать 5 минут
	--на точность расчетов влияет частота сбора и коэффициент сбора
	--чем чаще запуск сбора, тем меньше влияет коэффициент сбора
AS
BEGIN
	SET NOCOUNT ON;

	declare @AvgCPU_Time bigint
       ,@MaxAvgCPU_Time bigint
	   ,@AvgTotalWorkerTime bigint
	   ,@MaxTotalWorkerTime bigint
	   ,@AvgAvgElapsedTime bigint
	   ,@MaxAvgElapsedTime bigint
	   ,@AvgTotalElapsedTime bigint
	   ,@MaxTotalElapsedTime bigint
	
	select
		@AvgCPU_Time			= AVG(AvgCPU_Time),
		@MaxAvgCPU_Time			= max(AvgCPU_Time),
		@AvgTotalWorkerTime		= AVG(TotalWorkerTime),
		@MaxTotalWorkerTime		= max(TotalWorkerTime),
		@AvgAvgElapsedTime		= AVG(AvgElapsedTime),
		@MaxAvgElapsedTime		= max(AvgElapsedTime),
		@AvgTotalElapsedTime	= AVG(TotalElapsedTime),
		@MaxTotalElapsedTime	= max(TotalElapsedTime)
	from srv.vProcedureExecInfo;
	
	insert into srv.SQL_TriggerExecStat
	(
		[InsertDate]
	   ,database_id
	   ,object_id
	   ,[ExecutionCount]
	   ,[TotalWorkerTime]
	   ,[TotalElapsedTime])
	select
		getdate()
	   ,database_id
	   ,object_id
	   ,[ExecutionCount]
	   ,[TotalWorkerTime]
	   ,[TotalElapsedTime]
	from srv.vTriggerExecInfo
	where(AvgCPU_Time      > @AvgCPU_Time		  + @koef * (@MaxAvgCPU_Time	  - @AvgCPU_Time))
	  or (TotalWorkerTime  > @AvgTotalWorkerTime  + @koef * (@MaxTotalWorkerTime  - @AvgTotalWorkerTime))
	  or (AvgElapsedTime   > @AvgAvgElapsedTime   + @koef * (@MaxAvgElapsedTime   - @AvgAvgElapsedTime))
	  or (TotalElapsedTime > @AvgTotalElapsedTime + @koef * (@MaxTotalElapsedTime - @AvgTotalElapsedTime));
END

GO



4) создать представления для вывода информации:

4.1) для запросов:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [srv].[vStatementExecTotalInfo]
as
select ExecutionCount as Num
	   ,TotalWorkerTime as TotalWorkerTime
	   ,TotalElapsedTime as TotalElapsedTime
	   ,convert(decimal(8,2),AvgCPU_Time/1000000.) as AvgWorkerSec
	   ,convert(decimal(8,2),AvgElapsedTime/1000000.) as AvgElapsedSec
	   ,...
	   ,QueryHash
	   ,StatementText
	from [SRV].[srv].[vStatementExecInfo];
GO



4.2) для хранимых процедур:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [srv].[vProcedureExecTotalInfo]
as  
select ExecutionCount as Num
	   ,TotalWorkerTime as TotalWorkerTime
	   ,TotalElapsedTime as TotalElapsedTime
	   ,convert(decimal(8,2),AvgCPU_Time/1000000.) as AvgWorkerSec
	   ,convert(decimal(8,2),AvgElapsedTime/1000000.) as AvgElapsedSec
           ,...
	   ,database_id
	   ,object_id
	   ,db_name(database_id) as DB_Name
	   ,OBJECT_SCHEMA_NAME(object_id, database_id) as Schema_Name
	   ,object_name(object_id, database_id) as Procedure_Name
	from [SRV].[srv].[vProcedureExecInfo];
GO



4.3) Аналогичным образом делаются представления и для триггеров (если это нужно). Но в моей практике отслеживать все время триггеры нет необходимости, т к проблемы с ними отразятся на выполнении хранимых процедур и запросов.

В реализованных представлениях очень важны два показателя:

1) AvgWorkerSec — само время выполнения запроса в секундах
2) AvgElapsedSec — время ожидания или ожидания+AvgWorkerSec

В результатах представлений важным показателем является следующее равенство:
AvgWorkerSec=AvgElapsedSec.

Если это не так, то проблема не в самом запросе и не в плане запроса. Причин может быть много. Приведу лишь те, с которыми сталкивался сам:

1) AvgWorkerSec>AvgElapsedSec — здесь кто-то сильно загружает процессор в момент выполнения запроса (как оказалось запускалось сканирование антивирусного приложения, также может быть всему виной распараллеливание плана)
2) AvgWorkerSec<AvgElapsedSec — здесь слишком большое ожидание перед выполнением запроса (оптимизатор долго ищет план-проблема разрастания процедурного кэша или нехватки кэша, сторонний софт нагружал диски многими вставками записей в лог-файл).

Если равенство AvgWorkerSec=AvgElapsedSec соблюдено, то долгое время выполнения запроса лежит в самом запросе и в его плане выполнения.

Что является критерием того, что запрос долго выполняется?
На такой вопрос однозначного ответа нет. Смотря что делает запрос, как часто и где используется? И т. д.

У меня сделана следующая оценка для оперативных запросов, хранимых процедур:

1) до 0,5 — для хранимых процедур это хорошо, проблем нет (нет задержек в выполнении)
2) до 0,1 — для запросов это хорошо, проблем нет (нет задержек в выполнении)
3) 0,5 — 1,0 — для хранимых процедур это нехорошо, проблемы есть (нет видимых для пользователя задержек в выполнении, но они есть, проблему нужно решать, но не срочно)
4) 0,1 — 0,5 — для запросов это нехорошо, проблемы есть (нет видимых для пользователя задержек в выполнении, но они есть, проблему нужно решать, но не срочно)
5) более 1,0 — для хранимых процедур это плохо, проблемы есть (очень вероятно, что есть видимые для пользователя задержки в выполнении, проблему нужно решать срочно)
6) более 0,5 — для запросов это плохо, проблемы есть (очень вероятно, что есть видимые для пользователя задержки в выполнении, проблему нужно решать срочно).

Для не оперативных запросов и хранимых процедур (выгрузка, загрузка данных и т. д.) данная оценка подбирается индивидуально и обычно в разы превосходит оценки для оперативных запросов и хранимых процедур.

Если весь софт работает через хранимые процедуры, то можно вообще отслеживать только хранимые процедуры без запросов, т к работа запросов всегда затронет работу хранимых процедур. Поэтому остановимся на анализе выполнения хранимых процедур более детально.

Создадим теперь систему, которая будет собирать информацию о самых тяжелых хранимых процедурах для последующего анализа и запуска автотрассировки, по следующему алгоритму:

1) создадим таблицу, в которой будем хранить информацию:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[SQL_TopProcedureExecStat](
	[Row_GUID] [uniqueidentifier] NOT NULL,
	[SERVER] [nvarchar](255) NOT NULL,
	[DB_ID] [int] NOT NULL,
	[OBJECT_ID] [int] NOT NULL,
	[ExecutionCount] [bigint] NOT NULL,
	[TotalWorkerTime] [bigint] NULL,
	[TotalElapsedTime] [bigint] NULL,
	[Func] [decimal](8, 2) NULL,
	[AvgWorkerSec] [decimal](8, 2) NULL,
	[AvgElapsedSec] [decimal](8, 2) NULL,
	[DB_NAME] [nvarchar](255) NULL,
	[SCHEMA_NAME] [nvarchar](255) NULL,
	[OBJECT_NAME] [nvarchar](255) NULL,
	[InsertUTCDate] [datetime] NOT NULL,
	[TotalPhysicalReads] [bigint] NULL,
	[TotalLogicalReads] [bigint] NULL,
	[TotalLogicalWrites] [bigint] NULL,
	[AvgPhysicalReads] [bigint] NULL,
	[AvgLogicalReads] [bigint] NULL,
	[AvgLogicalWrites] [bigint] NULL,
	[CategoryName] [nvarchar](255) NULL,
 CONSTRAINT [PK_SQL_TopProcedureExecStat] PRIMARY KEY CLUSTERED 
(
	[Row_GUID] 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

ALTER TABLE [srv].[SQL_TopProcedureExecStat] ADD  CONSTRAINT [DF_SQL_TopProcedureExecStat_Row_GUID]  DEFAULT (newid()) FOR [Row_GUID]
GO

ALTER TABLE [srv].[SQL_TopProcedureExecStat] ADD  CONSTRAINT [DF_SQL_TopProcedureExecStat_SERVER]  DEFAULT (@@servername) FOR [SERVER]
GO

ALTER TABLE [srv].[SQL_TopProcedureExecStat] ADD  CONSTRAINT [DF_SQL_TopProcedureExecStat_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO



2) создадим хранимую процедуру для сбора информации:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [srv].[InsertTopProcedureExecStat]
	@top tinyint=24 --сколько хранить записи (кол-во строк)
	,@CategoryName nvarchar(255)='AvgWorkerSec' --категория, по которой отбираем
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO [srv].[SQL_TopProcedureExecStat]
           ([DB_ID]
           ,[OBJECT_ID]
           ,[ExecutionCount]
           ,[TotalWorkerTime]
           ,[TotalElapsedTime]
           ,[AvgWorkerSec]
           ,[AvgElapsedSec]
		   ,[DB_NAME]
		   ,[SCHEMA_NAME]
		   ,[OBJECT_NAME]
		   ,InsertUTCDate
		   ,CategoryName
		   ,TotalPhysicalReads	
		   ,TotalLogicalReads	
		   ,TotalLogicalWrites	
		   ,AvgPhysicalReads	
		   ,AvgLogicalReads	
		   ,AvgLogicalWrites)
	 select top(@top)
		  [database_id]
	      ,[object_id]
		  ,[Num]
	      ,[TotalWorkerTime]
	      ,[TotalElapsedTime]
	      ,[AvgWorkerSec]
	      ,[AvgElapsedSec]
		  ,[DB_NAME]
		  ,[SCHEMA_NAME]
		  ,[PROCEDURE_NAME]
		  ,InsertUTCDate
		  ,CategoryName
		  ,TotalPhysicalReads	
		  ,TotalLogicalReads	
		  ,TotalLogicalWrites	
		  ,AvgPhysicalReads	
		  ,AvgLogicalReads	
		  ,AvgLogicalWrites
	 from(
		select [database_id]
		     ,[object_id]
			 ,[Num]
		     ,[TotalWorkerTime]
		     ,[TotalElapsedTime]
		     ,[AvgWorkerSec]
		     ,[AvgElapsedSec]
			 ,[DB_NAME]
		     ,[SCHEMA_NAME]
		     ,[PROCEDURE_NAME]
			 ,getUTCDate() as InsertUTCDate
			 ,@CategoryName as CategoryName
			 ,TotalPhysicalReads	
			 ,TotalLogicalReads	
			 ,TotalLogicalWrites	
			 ,AvgPhysicalReads	
			 ,AvgLogicalReads	
			 ,AvgLogicalWrites
		 FROM [srv].[vProcedureExecTotalInfoHour]
		) as t
		order by
		case @CategoryName
						when 'TotalWorkerTime' then TotalWorkerTime
						when 'TotalElapsedTime' then TotalElapsedTime
						when 'AvgWorkerSec' then AvgWorkerSec
						when 'AvgElapsedSec' then AvgElapsedSec
						when 'TotalPhysicalReads' then TotalPhysicalReads
						when 'TotalLogicalReads' then TotalLogicalReads
						when 'TotalLogicalWrites' then TotalLogicalWrites
						when 'AvgPhysicalReads' then AvgPhysicalReads
						when 'AvgLogicalReads' then AvgLogicalReads
						when 'AvgLogicalWrites' then AvgLogicalWrites
				 end
				 desc;
	
	declare @count int=(select count(*) from [srv].[SQL_TopProcedureExecStat] where CategoryName=@CategoryName);
	declare @diff int=@count-@top;

		;with tbl_del as(
			select
			Row_GUID
			from [srv].[SQL_TopProcedureExecStat]
			where InsertUTCDate<DateAdd(hour,-24,getUTCDate())
			and CategoryName=@CategoryName
		)
        delete from [srv].[SQL_TopProcedureExecStat]
		where Row_GUID in (select Row_GUID from tbl_del);

	--если записей в таблице осталось больше, чем указано @top, то удалить самые незначительные
	set @count = (select count(*) from [srv].[SQL_TopProcedureExecStat] where CategoryName=@CategoryName)
	set @diff = @count - @Top - 3
	if(@diff>0)
	begin
		;with tbl_del as(
			select top(@diff)
			Row_GUID
			from [srv].[SQL_TopProcedureExecStat]
			where CategoryName=@CategoryName
			order by
					case @CategoryName
						when 'TotalWorkerTime' then TotalWorkerTime
						when 'TotalElapsedTime' then TotalElapsedTime
						when 'AvgWorkerSec' then AvgWorkerSec
						when 'AvgElapsedSec' then AvgElapsedSec
						when 'TotalPhysicalReads' then TotalPhysicalReads
						when 'TotalLogicalReads' then TotalLogicalReads
						when 'TotalLogicalWrites' then TotalLogicalWrites
						when 'AvgPhysicalReads' then AvgPhysicalReads
						when 'AvgLogicalReads' then AvgLogicalReads
						when 'AvgLogicalWrites' then AvgLogicalWrites
					end
		)
        delete from [srv].[SQL_TopProcedureExecStat]
		where Row_GUID in (select Row_GUID from tbl_del);
	end

	declare @DB_ID int
	declare @OBJECT_ID int
	declare @top1 int = 3
	declare @diff1 int
	declare @count1 int
	-- удалить повторы более @top1 раз конкретной процедуры
		select top (1)
			@count1 = tp.num
		   ,@DB_ID = tp.DB_ID
		   ,@OBJECT_ID = tp.OBJECT_ID
		from
	     (select count(*) as num, DB_ID, OBJECT_ID
			 from [srv].[SQL_TopProcedureExecStat]
			 where CategoryName=@CategoryName
			 group by DB_ID, OBJECT_ID) as tp
		order by tp.num desc;

		set @diff1 = @count1 - @top1;

        if(@diff1) > 0
		begin
			;with tbl_del as(
				select top(@diff1)
				Row_GUID
				from [srv].[SQL_TopProcedureExecStat]
				where DB_ID = @DB_ID and OBJECT_ID = @OBJECT_ID
				and CategoryName=@CategoryName
				order by
					case @CategoryName
						when 'TotalWorkerTime' then TotalWorkerTime
						when 'TotalElapsedTime' then TotalElapsedTime
						when 'AvgWorkerSec' then AvgWorkerSec
						when 'AvgElapsedSec' then AvgElapsedSec
						when 'TotalPhysicalReads' then TotalPhysicalReads
						when 'TotalLogicalReads' then TotalLogicalReads
						when 'TotalLogicalWrites' then TotalLogicalWrites
						when 'AvgPhysicalReads' then AvgPhysicalReads
						when 'AvgLogicalReads' then AvgLogicalReads
						when 'AvgLogicalWrites' then AvgLogicalWrites
					end
			)
			delete from [srv].[SQL_TopProcedureExecStat]
			where Row_GUID in (select Row_GUID from tbl_del);
		end

	-- удалить повторы более 1 раза значения параметра AvgWorkerSec для конкретной процедуры
	if @CategoryName = 'AvgWorkerSec'
	 begin
	    declare @AvgWorkerSec decimal(8,2)
		select top (1)
			@count1 = tp.num
		   ,@DB_ID = tp.DB_ID
		   ,@OBJECT_ID = tp.OBJECT_ID
		   ,@AvgWorkerSec = tp.AvgWorkerSec
		from
	     (select count(*) as num, DB_ID, OBJECT_ID, AvgWorkerSec
			 from [srv].[SQL_TopProcedureExecStat]
			 where CategoryName=@CategoryName
			 group by DB_ID, OBJECT_ID,AvgWorkerSec) as tp
		order by tp.num desc;

		set @diff1 = @count1 - 1;

        if(@diff1) > 0
		begin
			;with tbl_del as(
				select top(@diff1)
				Row_GUID
				from [srv].[SQL_TopProcedureExecStat]
				where DB_ID = @DB_ID and OBJECT_ID = @OBJECT_ID
				and CategoryName=@CategoryName and AvgWorkerSec = @AvgWorkerSec
				order by InsertUTCDate desc
			)
			delete from [srv].[SQL_TopProcedureExecStat]
			where Row_GUID in (select Row_GUID from tbl_del);
		end
	end

	if @CategoryName = 'AvgElapsedSec'
	 begin
	    declare @AvgElapsedSec decimal(8,2)
		select top (1)
			@count1 = tp.num
		   ,@DB_ID = tp.DB_ID
		   ,@OBJECT_ID = tp.OBJECT_ID
		   ,@AvgElapsedSec = tp.AvgElapsedSec
		from
	     (select count(*) as num, DB_ID, OBJECT_ID, AvgElapsedSec
			 from [srv].[SQL_TopProcedureExecStat]
			 where CategoryName=@CategoryName
			 group by DB_ID, OBJECT_ID,AvgElapsedSec) as tp
		order by tp.num desc;

		set @diff1 = @count1 - 1;

        if(@diff1) > 0
		begin
			;with tbl_del as(
				select top(@diff1)
				Row_GUID
				from [srv].[SQL_TopProcedureExecStat]
				where DB_ID = @DB_ID and OBJECT_ID = @OBJECT_ID
				and CategoryName=@CategoryName and AvgElapsedSec = @AvgElapsedSec
				order by InsertUTCDate desc
			)
			delete from [srv].[SQL_TopProcedureExecStat]
			where Row_GUID in (select Row_GUID from tbl_del);
		end
	end
END

GO



Данную хранимую процедуру лучше всего запускать сразу после сбора информации про хранимые процедуры (можно настроить задачу в Агенте для запуска каждые 5-10 минут для запросов и хранимых процедур и триггеров):

exec [srv].[InsertForSQL_StatementExecStat]; --сбор информации по выполненным запросам
exec [srv].[InsertForTriggerExecStat]; --сбор информации по выполненным триггерам
exec [srv].[InsertForProcedureExecStat]; --сбор информации по выполненным хранимым процедурам
--сбор информации о самых тяжелых выполненных хранимых процедурах по критериям
exec [srv].[InsertTopProcedureExecStat] @top=@top, @CategoryName='AvgWorkerSec';
exec [srv].[InsertTopProcedureExecStat] @top=@top, @CategoryName='AvgElapsedSec';

3) запуск трассировки (через задания Агента-каждые 5-10 минут, лучше сразу после сбора информации):
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ];
go

--коэффициент переходящего значения индикатора
declare @koef_red numeric(8,3)=1.3; 
        --если есть записи со значением показателя не меньше заданного
        --коэффициента индикатора
	if(exists(
	SELECT top(1) 1
			FROM [srv].[SQL_TopProcedureExecStat]
			where CategoryName='AvgElapsedSec'
			   or CategoryName='AvgWorkerSec'
			group by CategoryName
			having avg([AvgElapsedSec])>=@koef_red
			    or avg([AvgWorkerSec])>=@koef_red))
	begin
		--запустить автотрассировку
                exec .[srv].[AutoTrace];
	end



Хранимая процедура по автотрассировке реализуется индивидуально. Приведу пример:
Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [srv].[AutoTrace]
	@maxfilesize bigint=200 --максимальный размер файла в МБ
	,@run_minutes int=60	--сколько в минутах будет идти трассировка
	,@file_patch nvarchar(255)=N'Путь к каталогу' --каталог для файла трассировки
	,@file_name nvarchar(255)=N'Profiler' --имя файла
	,@res_msg nvarchar(255)=NULL output --результат в виде сообщений
AS
BEGIN
	SET NOCOUNT ON;

    declare @rc int;
	declare @TraceID int;
	
	if(@run_minutes>=1200) set @run_minutes=1200; --не более 20 часов!
	
	declare @finish_dt datetime=DateAdd(minute,@run_minutes,GetDate()); --до какого времени выполнять
	
	--окончание файла трассировки
	declare @finish_dt_inc nvarchar(255)=N'_'+cast(YEAR(@finish_dt) as nvarchar(255))+'_'+cast(MONTH(@finish_dt) as nvarchar(255))+'_'+cast(DAY(@finish_dt) as nvarchar(255));
	
	declare @File nvarchar(255)=@file_patch+@file_name+@finish_dt_inc; --полное названрие файла трассировки
	
	DECLARE @result bit;
	DECLARE @msgerrors nvarchar(255);
	DECLARE @oldDT datetime;
	
	--Взять последнюю дату с временем
	if(object_id('ИМЯ_БАЗЫ_ДАННЫХ.dbo.TraceTable')<>0)
	begin
		select @oldDT=max(StartTime)
		from ИМЯ_БАЗЫ_ДАННЫХ.dbo.TraceTable
		where StartTime is not null;
	end
	
	--select @oldDT;
	
	--если последняя дата с временем не определена или меньше даты завершения трассировки, то запустить трассировку, иначе-трассировка уже выполнялось в эту дату
	if(@oldDT is null or @oldDT<DATETIMEFROMPARTS(YEAR(@finish_dt), MONTH(@finish_dt), DAY(@finish_dt), 0, 0, 0, 0))
	begin
		--создаем трассировку
		exec @rc = sp_trace_create
			@TraceID=@TraceID output,	--идентификатор трассировки
			@Options=0,					--опции трассировки (по умолчанию)
			@TraceFile=@File,			--куда сохранять трассировку
			@MaxFileSize=@maxfilesize,	--максимальный размер файла трассировки (при достижении максимального размера трассировка останавливается)
			@StopTime=@finish_dt--,		--до какой даты и времени выполнять трассировку
			--@FileCount=2;				--кол-во файлов, при котором удаляются предыдущие (неиспользуется в данном наборе опций трассировки)
	
		--если трассировка создана без ошибок (код 0), то настраиваем трассировку и запускаем ее
		if (@rc = 0)
		begin
			-- Задаем события для трассировки
			declare @on bit
			set @on = 1
			exec sp_trace_setevent @TraceID, 10, 1, @on
			exec sp_trace_setevent @TraceID, 10, 9, @on
			exec sp_trace_setevent @TraceID, 10, 2, @on
			exec sp_trace_setevent @TraceID, 10, 66, @on
			exec sp_trace_setevent @TraceID, 10, 10, @on
			exec sp_trace_setevent @TraceID, 10, 3, @on
			exec sp_trace_setevent @TraceID, 10, 4, @on
			exec sp_trace_setevent @TraceID, 10, 6, @on
			exec sp_trace_setevent @TraceID, 10, 7, @on
			exec sp_trace_setevent @TraceID, 10, 8, @on
			exec sp_trace_setevent @TraceID, 10, 11, @on
			exec sp_trace_setevent @TraceID, 10, 12, @on
			exec sp_trace_setevent @TraceID, 10, 13, @on
			exec sp_trace_setevent @TraceID, 10, 14, @on
			exec sp_trace_setevent @TraceID, 10, 15, @on
			exec sp_trace_setevent @TraceID, 10, 16, @on
			exec sp_trace_setevent @TraceID, 10, 17, @on
			exec sp_trace_setevent @TraceID, 10, 18, @on
			exec sp_trace_setevent @TraceID, 10, 25, @on
			exec sp_trace_setevent @TraceID, 10, 26, @on
			exec sp_trace_setevent @TraceID, 10, 31, @on
			exec sp_trace_setevent @TraceID, 10, 34, @on
			exec sp_trace_setevent @TraceID, 10, 35, @on
			exec sp_trace_setevent @TraceID, 10, 41, @on
			exec sp_trace_setevent @TraceID, 10, 48, @on
			exec sp_trace_setevent @TraceID, 10, 49, @on
			exec sp_trace_setevent @TraceID, 10, 50, @on
			exec sp_trace_setevent @TraceID, 10, 51, @on
			exec sp_trace_setevent @TraceID, 10, 60, @on
			exec sp_trace_setevent @TraceID, 10, 64, @on
			exec sp_trace_setevent @TraceID, 12, 1, @on
			exec sp_trace_setevent @TraceID, 12, 9, @on
			exec sp_trace_setevent @TraceID, 12, 3, @on
			exec sp_trace_setevent @TraceID, 12, 11, @on
			exec sp_trace_setevent @TraceID, 12, 4, @on
			exec sp_trace_setevent @TraceID, 12, 6, @on
			exec sp_trace_setevent @TraceID, 12, 7, @on
			exec sp_trace_setevent @TraceID, 12, 8, @on
			exec sp_trace_setevent @TraceID, 12, 10, @on
			exec sp_trace_setevent @TraceID, 12, 12, @on
			exec sp_trace_setevent @TraceID, 12, 13, @on
			exec sp_trace_setevent @TraceID, 12, 14, @on
			exec sp_trace_setevent @TraceID, 12, 15, @on
			exec sp_trace_setevent @TraceID, 12, 16, @on
			exec sp_trace_setevent @TraceID, 12, 17, @on
			exec sp_trace_setevent @TraceID, 12, 18, @on
			exec sp_trace_setevent @TraceID, 12, 26, @on
			exec sp_trace_setevent @TraceID, 12, 31, @on
			exec sp_trace_setevent @TraceID, 12, 35, @on
			exec sp_trace_setevent @TraceID, 12, 41, @on
			exec sp_trace_setevent @TraceID, 12, 48, @on
			exec sp_trace_setevent @TraceID, 12, 49, @on
			exec sp_trace_setevent @TraceID, 12, 50, @on
			exec sp_trace_setevent @TraceID, 12, 51, @on
			exec sp_trace_setevent @TraceID, 12, 60, @on
			exec sp_trace_setevent @TraceID, 12, 64, @on
			exec sp_trace_setevent @TraceID, 12, 66, @on
			exec sp_trace_setevent @TraceID, 13, 1, @on
			exec sp_trace_setevent @TraceID, 13, 9, @on
			exec sp_trace_setevent @TraceID, 13, 3, @on
			exec sp_trace_setevent @TraceID, 13, 11, @on
			exec sp_trace_setevent @TraceID, 13, 4, @on
			exec sp_trace_setevent @TraceID, 13, 6, @on
			exec sp_trace_setevent @TraceID, 13, 7, @on
			exec sp_trace_setevent @TraceID, 13, 8, @on
			exec sp_trace_setevent @TraceID, 13, 10, @on
			exec sp_trace_setevent @TraceID, 13, 12, @on
			exec sp_trace_setevent @TraceID, 13, 14, @on
			exec sp_trace_setevent @TraceID, 13, 26, @on
			exec sp_trace_setevent @TraceID, 13, 35, @on
			exec sp_trace_setevent @TraceID, 13, 41, @on
			exec sp_trace_setevent @TraceID, 13, 49, @on
			exec sp_trace_setevent @TraceID, 13, 50, @on
			exec sp_trace_setevent @TraceID, 13, 51, @on
			exec sp_trace_setevent @TraceID, 13, 60, @on
			exec sp_trace_setevent @TraceID, 13, 64, @on
			exec sp_trace_setevent @TraceID, 13, 66, @on
	
	
			-- Устанавливаем фильтры
			declare @intfilter int;
			declare @bigintfilter bigint;
	
			exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Приложение SQL Server Profiler - fa35966e-e426-4d1a-8753-8f971cf89495';
			exec sp_trace_setfilter @TraceID, 35, 0, 6, N'%ИМЯ_БАЗЫ_ДАННЫХ%';
			exec sp_trace_setfilter @TraceID, 35, 1, 6, N'%ИМЯ_БАЗЫ_ДАННЫХ%';
	
	
			--Запускаем трассировку
			exec sp_trace_setstatus @TraceID, 1;
	
			--настраиваем задержку времени выполнения
			declare @run_delay int=@run_minutes+1; --увеличим задержку на 1 минуту от заданного времени выполнения для трассировки
			declare @run_delay_hour int=@run_delay/60; --вычислим часы задержки
			declare @run_delay_minute int=@run_delay-(@run_delay/60)*60; --вычислим оставшиеся минуты
	
			declare @run_delay_hour_str nvarchar(2);	--строковое представление часов
			declare @run_delay_minute_str nvarchar(2);	--строковое представление минут
	
			--добавить недостающие нули для строкового представления часов
			if(@run_delay_hour=0) set @run_delay_hour_str='00';
			else if(@run_delay_hour<10) set @run_delay_hour_str='0'+cast(@run_delay_hour as nvarchar(255));
			else if(@run_delay_hour>=10) set @run_delay_hour_str=cast(@run_delay_hour as nvarchar(255));
	
			--select @run_delay_hour, @run_delay_hour_str;
	
			--добавить недостающие нули для строкового представления минут
			if(@run_delay_minute=0) set @run_delay_minute_str='00';
			else if(@run_delay_minute<10) set @run_delay_minute_str='0'+cast(@run_delay_minute as nvarchar(255));
			else if(@run_delay_minute>=10) set @run_delay_minute_str=cast(@run_delay_minute as nvarchar(255));
	
			--select @run_delay_minute, @run_delay_minute_str;
	
			--строковое представление часы:минуты для задержки
			declare @run_delay_str nvarchar(255)=@run_delay_hour_str+':'+@run_delay_minute_str;
	
			--задержка
			WAITFOR DELAY @run_delay_str;
	
			--select @run_delay_str;
	
			--удаляем таблицу трассировки при ее существовании
			if(object_id('ИМЯ_БАЗЫ_ДАННЫХ.dbo.TraceTable')<>0)
			begin
				drop table ИМЯ_БАЗЫ_ДАННЫХ.dbo.TraceTable;
			end
	
			--создаем и заполняем таблицу трассировки из файла трассировки
			SELECT
				*
			INTO ИМЯ_БАЗЫ_ДАННЫХ.dbo.TraceTable
			FROM ::fn_trace_gettable(@File+'.trc', default);
	
			--добавить к полному имени файла расширение
			set @File=@File+'.trc';
	
			--здесь нужно вставить код, чтобы удалить файл трассировки

			declare @str_title nvarchar(max)='Была запущена автотрассировка на сервере '+@@servername,
			@str_pred_mess nvarchar(max)='На '+@@servername+' сервере была запущена автотрассировка. Посмотреть результат можно в таблице ИМЯ_БАЗЫ_ДАННЫХ.dbo.TraceTable';

			--здесь можно отправить уведомление администраторам о запуске автотрассировки
		end
	
		--вернуть результат
		set @res_msg=N'ErrorCode='+cast(@rc as nvarchar(255))+'\r\n'+coalesce(@msgerrors, '');
	end
END

GO



Более подробно как настроить трассировку можно почитать здесь Как создать трассировку (Transact-SQL)

Результат


В данной статье был рассмотрен пример реализации системы сбора о состоянии работы базы данных, которая не нагружает систему. Также данная система в случае обнаружения проблемы запускает настроенную заранее трассировку и сохраняет в таблицу. Такой подход можно расширить и на несколько серверов. Тогда необходимо собирать со всех серверов информацию для последующей отправки отчета администраторам.

Также важно не забывать удалять старые данные из используемых таблиц. Вполне достаточно хранить данные до месяца или даже двух недель.

Еще одно интересное решение есть тут Тестирование производительности баз данных при помощи tSQLt и SQLQueryStress

Далее дан пример реализации общего индикатора производительности MS SQL Server.

Источники:


» sys.dm_exec_trigger_stats
» sys.dm_exec_procedure_stats
» sys.dm_exec_query_stats
» sys.dm_exec_sql_text
» Как создать трассировку (Transact-SQL)
» Пример реализации общего индикатора производительности MS SQL Server
» Тестирование производительности баз данных при помощи tSQLt и SQLQueryStress
Поделиться публикацией

Комментарии 12

    +1

    Здравствуйте. Интересная статья, отличная работа. Делал похожее решение, но без автоматического запуска, только ручной запуск и анализ.


    В своём решении не стал в трассировку включать события с кодом 13 SQL:BatchStarting, в них не нашел полезной информации для профилирования.


    Но включил:


    • 162. User Error Message
    • 148. Deadlock Graph

    См. https://github.com/polarnik/SQLProfilerReportHelper/blob/master/scripts/start%20trace/Script.01.Start%20trace.sql


    Особенностью https://habrahabr.ru/company/npo-comp/blog/243587/ является группировка запросов по текстам, самодельная. Позже научился группировать по query_stats.query_hash, как сделано в текущем решении. Группировка по query_hash это сто шагов вперёд. В проект пока не включено. Надо новый проект сделать для такого способа.


    И собираю данные в одну таблицу. Чтобы потом сделать по ней один отчёт в Excel, с раскраской. Как раз вчера сел за доработку проекта, чтобы отчёт Excel формировался сразу из программы.


    Про триггеры не подумал. Опробую анализ триггеров в ближайшее время. Спасибо большое. Буду рад, если мои наработки по отчёту или группировке запросов также пригодятся вам в работе.

      +1
      Статья интересная-спасибо)
      Главное отслеживать не все подряд, а подойти с вероятностной точки зрения (чего увы математикам и программистам приходит в голову не сразу-у меня тоже не сразу) и много «магии» так называемой эвристики-нужно упреждать появление проблемы при минимуме наблюдений и нагрузки на систему, а не все подряд анализировать.
      На счет триггеров могу сказать, что 100% работа триггеров отразится на выполнение запросов и хранимых процедур. Поэтому может и нет смысла их анализировать все время. Будет что-то хуже выполняться, проанализируете. И если поймете, что в триггере могут быть проблемы, то либо включите наблюдение за триггерами конкретными, либо и без наблюдения разрешите проблему.
        +1

        Спасибо за отзыв.


        В нагрузочном тестировании бывает сразу не отследишь всё подряд, проведёшь тест, садишься писать отчёт, а уже поезд ушел, нет детального лога и взять его не от куда. Контролировать всё и сразу в бывает оправдано.


        Запускаю трассировку по двум базам данных сразу — tempdb и целевой базе данных, tempdb активно используется внутри хранимок. Назвать реальных примеров, когда это было полезным не смогу, но когда-то решил так делать и делаю до сих пор.


        И таблицу с результатами профайлинга создаю в отдельной БД, целевая база данных остаётся неприкосновенной. В текущей статье таблица TraceTable создаётся в целевой базе данных. Она обычно на отдельном диске, а tempdb на отдельном, создаю traceDB на том же диске, что и tempdb, чтобы разделить нагрузку на железо. Чтобы в резервную копию не попадали лишние данные. И чтобы не рос лог транзакций целевой базы данных.


        Заменил бы строки ИМЯ_БАЗЫ_ДАННЫХ.dbo.TraceTable на строки traceDB.dbo.TraceTable, где traceDB — отдельная база данных, только для трассировки, без лога транзакций, без резервных копий.


        И таблицу профайлинга TraceTable создаю каждый раз новую, за счёт использования даты и времени в имени таблицы TraceTable_20161107_0150. Храню для истории, удаляю руками.


        В каждодневном администрировании контролировать всё, профилировать tempdb и хранить таблицы с трейсами, согласен, не нужно. Но создавать таблицу профайлинга, считаю, лучше в другой базе данных.

          0
          Полностью с Вами согласен-возьму на заметку.
          Для конечного совершенства, я бы еще и автоматом таблицы удалял, которые очень старые. Например, которым больше месяца.
            0

            При создании представления для триггеров [srv].[vTriggerExecInfo] используется alias procedure_stats, на работу это не должно влиять. Читаю код: "Не запускал, но осужаю". Наверняка, код работает.

      0
      А чем не устраивают стандартные средва анализа производительности?
      Для решения проблем с медленными запросами пока хватало Монитора актвности со списком Дорогих запросов
      ну и далее план запроса, индексы циклы и т.п.
        0
        А как Вы выясняете, что запросы стали медленными? По факту от пользователей? Данное решение, которое я опубликовал, призвано заранее упреждать такие явления+всегда следить за системой и без участия администраторов.
        +2
        В SQL Server Azure / SQL Server 2016 есть механизм Query Store, который гораздо надежнее и точнее ручного сбора данных раз в 5-10 минут. По сути он делает то же самое, что ваши самописные job-ы, но гораздо точнее (на уровне отдельных statement-ов, пишет статистику по каждому выполнению + использованный план), непрерывно, в фоне, не влияя при этом на производительность.
          +1
          Спасибо за информацию)
          У нас правда пока 2016 не используется, да и Enterprise не везде поставить можно из-за финансовой составляющей, но предложенный Вами метод в будущем хорошо бы сравнить, чтобы понимать откуда берутся такие цифры
            +1
            не влияя при этом на производительность.

            Как мне кажется, необходимо добавить «практически» не влияя на производительность, как минимум диск то Query Store использует :)
            Кроме того Query Store появился с позднего 2014-2016, так же имеет
            Max Size (MB): Specifies the limit for the data space that Query Store will take inside your database.
            .
            Вообщем решение автора может пригодиться.
              0
              Как будет Enterprise сравню
              Возможно стандартное решение от Microsoft хорошее, но может быть не самым оптимальным и хранить много лишней информации.
              Но в любом случае интересно.
              Правда мне кажется из заказчиков, особенно закрытых систем, мало кто готов оплатить Enterprise-версию
                +1
                у enterprise есть триал, так что можно потестить вполне легально… если, конечно, там нет ограничений на боевое применение

          Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

          Самое читаемое