Предисловие
Администратору баз данных рано или поздно захочется иметь индикатор производительности, который бы показывал все ли хорошо с запросами. Также известно, что запуск Профайлера на целые сутки существенно загружает систему, и поэтому не может быть оптимальным решением в базе данных, которая используется 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
with info as
(select s.id as ID1,(select ID from [srv].[SQL_StatementExecStat] s2 where InsertDate > dateadd(day,-1,getdate()) --dateadd(hour,-1,getdate()) для представления srv.vStatementExecTotalInfoHour (за час самые тяжелые)
and ID = (select min(ID) from [srv].[SQL_StatementExecStat] where QueryHash = s2.QueryHash and ID > s.ID) and s2.QueryHash = s.QueryHash) as ID2
from [srv].[SQL_StatementExecStat] s)
, info1 as
(select s1.ID as ID1,s1.InsertDate as InsertDate1,s1.[QueryHash] as QueryHash1,s1.[ExecutionCount] as ExecutionCount1
,s1.[TotalWorkerTime] as TotalWorkerTime1,s1.[TotalElapsedTime] as TotalElapsedTime1
,s2.ID as ID2,s2.InsertDate as InsertDate2,s2.[QueryHash] as QueryHash2,s2.[ExecutionCount] as ExecutionCount2
,s2.[TotalWorkerTime] as TotalWorkerTime2,s2.[TotalElapsedTime] as TotalElapsedTime2
from info join [srv].[SQL_StatementExecStat] s1
on info.ID1 = s1.ID left join [srv].[SQL_StatementExecStat] s2 on info.ID2 = s2.ID)
, info2 as
(select * from info1 where ID2 is not null)
, info3 as
(select QueryHash1,case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalWorkerTime2-TotalWorkerTime1)
when ExecutionCount2=ExecutionCount1 then 0 else TotalWorkerTime2 end as NormTotalWorkerTime
,case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalElapsedTime2-TotalElapsedTime1)
when ExecutionCount2=ExecutionCount1 then 0 else TotalElapsedTime2 end as NormTotalElapsedTime
,case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalWorkerTime2-TotalWorkerTime1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 then null else TotalWorkerTime2/ExecutionCount2 end as AvgWorkerTime
,case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalElapsedTime2-TotalElapsedTime1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 then null else TotalElapsedTime2/ExecutionCount2 end as AvgElapsedTime
from info2
)
, info4 as
(select QueryHash1,count(*) as num,sum(NormTotalWorkerTime) as TotalWorkerTime
,sum(NormTotalElapsedTime) as TotalElapsedTime
,avg(AvgWorkerTime) as AvgWorkerTime
,avg(AvgElapsedTime) as AvgElapsedTime
from info3 group by QueryHash1)
, info5 as
(select (select top 1 StatementText from [srv].[SQL_StatementExecStat] where QueryHash = info4.QueryHash1) as StatementText, info4.*
from info4)
, info6 as
(select avg(TotalWorkerTime) as AvgTotalWorkerTime
,avg(TotalElapsedTime) as AvgTotalElapsedTime
,avg(AvgWorkerTime) as AvgWorkerTime
,avg(AvgElapsedTime) as AvgElapsedTime
from info5)
,info7 as
(select StatementText,Num
,convert(decimal(8,2),
Convert(float,AvgWorkerTime)/(select top 1 AvgWorkerTime from info6)
+Convert(float,AvgElapsedTime)/(select top 1 AvgElapsedTime from info6)
)
as func
,convert(decimal(8,2),TotalWorkerTime/1000000./60.) as TotalWorkerMin
,convert(decimal(8,2),TotalElapsedTime/1000000./60.) as TotalElapsedMin
,convert(decimal(8,2),AvgWorkerTime/1000000.) as AvgWorkerSec
,convert(decimal(8,2),AvgElapsedTime/1000000.) as AvgElapsedSec
,QueryHash1
from info5
)
select top 10 * from info7
where func > 0.5
order by
3 desc
GO
4.2) для хранимых процедур:
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [srv].[vProcedureExecTotalInfo]
as
with info as
(select s.id as ID1,(select ID from [srv].[SQL_ProcedureExecStat] s2 where InsertDate > dateadd(day,-1,getdate()) --dateadd(hour,-1,getdate()) для представления srv.vProcedureExecTotalInfoHour (за час самые тяжелые)
and ID = (select min(ID) from [srv].[SQL_ProcedureExecStat] where database_id = s2.database_id
and object_id = s2.object_id and ID > s.ID) and s2.database_id = s.database_id and s2.object_id = s.object_id) as ID2
from [srv].[SQL_ProcedureExecStat] s)
, info1 as
(select s1.ID as ID1,s1.InsertDate as InsertDate1,s1.database_id as database_id,s1.object_id as object_id,s1.[ExecutionCount] as ExecutionCount1
,s1.[TotalWorkerTime] as TotalWorkerTime1,s1.[TotalElapsedTime] as TotalElapsedTime1
,s1.[TotalPhysicalReads] as TotalPhysicalReads1,s1.[TotalLogicalReads] as TotalLogicalReads1,s1.[TotalLogicalWrites] as TotalLogicalWrites1
,s2.ID as ID2,s2.InsertDate as InsertDate2,s2.database_id as datebase_id2,s2.object_id as object_id2,s2.[ExecutionCount] as ExecutionCount2
,s2.[TotalWorkerTime] as TotalWorkerTime2,s2.[TotalElapsedTime] as TotalElapsedTime2
,s2.[TotalPhysicalReads] as TotalPhysicalReads2,s2.[TotalLogicalReads] as TotalLogicalReads2,s2.[TotalLogicalWrites] as TotalLogicalWrites2
from info join [srv].[SQL_ProcedureExecStat] s1
on info.ID1 = s1.ID left join [srv].[SQL_ProcedureExecStat] s2 on info.ID2 = s2.ID)
, info2 as
(select * from info1 where ID2 is not null)
, info3 as
(select database_id,object_id
,case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalWorkerTime2-TotalWorkerTime1)
when ExecutionCount2=ExecutionCount1 then 0
else TotalWorkerTime2
end as NormTotalWorkerTime
,case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalElapsedTime2-TotalElapsedTime1)
when ExecutionCount2=ExecutionCount1 then 0
else TotalElapsedTime2
end as NormTotalElapsedTime
,case when ExecutionCount2>ExecutionCount1 and TotalPhysicalReads2>TotalPhysicalReads1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalPhysicalReads2-TotalPhysicalReads1)
when ExecutionCount2=ExecutionCount1 then 0 else TotalPhysicalReads2
end as NormTotalPhysicalReads
,case when ExecutionCount2>ExecutionCount1 and TotalLogicalReads2>TotalLogicalReads1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalLogicalReads2-TotalLogicalReads1)
when ExecutionCount2=ExecutionCount1 then 0 else TotalLogicalReads2
end as NormTotalLogicalReads
,case when ExecutionCount2>ExecutionCount1 and TotalLogicalWrites2>TotalLogicalWrites1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalLogicalWrites2-TotalLogicalWrites1)
when ExecutionCount2=ExecutionCount1 then 0 else TotalLogicalWrites2
end as NormTotalLogicalWrites
,case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalWorkerTime2-TotalWorkerTime1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 then null
else TotalWorkerTime2/ExecutionCount2 end as AvgWorkerTime
,case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalElapsedTime2-TotalElapsedTime1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 then null
else TotalElapsedTime2/ExecutionCount2
end as AvgElapsedTime
,case when ExecutionCount2>ExecutionCount1 and TotalPhysicalReads2>TotalPhysicalReads1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalPhysicalReads2-TotalPhysicalReads1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 then null else TotalPhysicalReads2/ExecutionCount2
end as AvgPhysicalReads
,case when ExecutionCount2>ExecutionCount1 and TotalLogicalReads2>TotalLogicalReads1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalLogicalReads2-TotalLogicalReads1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 then null else TotalLogicalReads2/ExecutionCount2
end as AvgLogicalReads
,case when ExecutionCount2>ExecutionCount1 and TotalLogicalWrites2>TotalLogicalWrites1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalLogicalWrites2-TotalLogicalWrites1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 then null else TotalLogicalWrites2/ExecutionCount2
end as AvgLogicalWrites
from info2)
, info4 as
(select database_id,object_id,count(*) as num
,sum(NormTotalWorkerTime) as TotalWorkerTime
,sum(NormTotalElapsedTime) as TotalElapsedTime
,sum(NormTotalPhysicalReads) as TotalPhysicalReads
,sum(NormTotalLogicalReads) as TotalLogicalReads
,sum(NormTotalLogicalWrites) as TotalLogicalWrites
,avg(AvgWorkerTime) as AvgWorkerTime
,avg(AvgElapsedTime) as AvgElapsedTime
,avg(AvgPhysicalReads) as AvgPhysicalReads
,avg(AvgLogicalReads) as AvgLogicalReads
,avg(AvgLogicalWrites) as AvgLogicalWrites
from info3 group by database_id,object_id)
, info5 as
(select (select top 1 ProcedureText from [srv].[vProcedureExecInfo] where database_id = info4.database_id and object_id = info4.object_id)
as ProcedureText, info4.*
from info4)
, info6 as
(select avg(TotalWorkerTime) as AvgTotalWorkerTime
,avg(TotalElapsedTime) as AvgTotalElapsedTime
,avg(TotalPhysicalReads) as AvgTotalPhysicalReads
,avg(TotalLogicalReads) as AvgTotalLogicalReads
,avg(TotalLogicalWrites) as AvgTotalLogicalWrites
,avg(AvgWorkerTime) as AvgWorkerTime
,avg(AvgElapsedTime) as AvgElapsedTime
,avg(AvgPhysicalReads) as AvgPhysicalReads
,avg(AvgLogicalReads) as AvgLogicalReads
,avg(AvgLogicalWrites) as AvgLogicalWrites
from info5)
,info7 as
(select ProcedureText,Num
,convert(decimal(8,2),
Convert(float,TotalWorkerTime)/(select top(1) AvgTotalWorkerTime from info6)
)
as func
,convert(decimal(8,2),TotalWorkerTime/1000000./60.) as TotalWorkerTime
,convert(decimal(8,2),TotalElapsedTime/1000000./60.) as TotalElapsedTime
,convert(decimal(8,2),AvgWorkerTime/1000000.) as AvgWorkerSec
,convert(decimal(8,2),AvgElapsedTime/1000000.) as AvgElapsedSec
,TotalPhysicalReads
,TotalLogicalReads
,TotalLogicalWrites
,AvgPhysicalReads
,AvgLogicalReads
,AvgLogicalWrites
,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 info5)
select * from info7
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))+'rn'+coalesce(@msgerrors, '');
end
END
GO
Более подробно как настроить трассировку можно почитать здесь Как создать трассировку (Transact-SQL)
Результат
В данной статье был рассмотрен пример реализации системы сбора о состоянии работы базы данных, которая не нагружает систему. Также данная система в случае обнаружения проблемы запускает настроенную заранее трассировку и сохраняет в таблицу. Такой подход можно расширить и на несколько серверов. Тогда необходимо собирать со всех серверов информацию для последующей отправки отчета администраторам.
Также важно не забывать удалять старые данные из используемых таблиц. Вполне достаточно хранить данные до месяца или даже двух недель.
Еще одно интересное решение есть тут Тестирование производительности баз данных при помощи tSQLt и SQLQueryStress
Источники:
» sys.dm_exec_trigger_stats
» sys.dm_exec_procedure_stats
» sys.dm_exec_query_stats
» sys.dm_exec_sql_text
» Как создать трассировку (Transact-SQL)
» Тестирование производительности баз данных при помощи tSQLt и SQLQueryStress
Автор: jobgemws
