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

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

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

У даній статті наведу реалізацію індикатора продуктивності запитів, збережених процедур і тригерів, а також їх використання для запуску трасування.

Рішення
Спочатку загальний підхід реалізації індикатора продуктивності запитів, збережених процедур і тригерів:

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

А тепер реалізація:

1) створити необхідні таблиці для збору і аналізу інформації:

1.1) для запитів:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
GO

SET ON ANSI_PADDING
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 ON ANSI_PADDING
GO



1.2) для збережених процедур:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 0 then else TotalWorkerTime2 end as NormTotalWorkerTime
case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalElapsedTime2-TotalElapsedTime1)
when ExecutionCount2=ExecutionCount1 0 then else TotalElapsedTime2 end as NormTotalElapsedTime
case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalWorkerTime2-TotalWorkerTime1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 null then else TotalWorkerTime2/ExecutionCount2 end as AvgWorkerTime
case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalElapsedTime2-TotalElapsedTime1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 null then 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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 0 then
else TotalWorkerTime2
end as NormTotalWorkerTime
case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalElapsedTime2-TotalElapsedTime1)
when ExecutionCount2=ExecutionCount1 0 then
else TotalElapsedTime2
end as NormTotalElapsedTime
case when ExecutionCount2>ExecutionCount1 and TotalPhysicalReads2>TotalPhysicalReads1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalPhysicalReads2-TotalPhysicalReads1) 
when ExecutionCount2=ExecutionCount1 0 then else TotalPhysicalReads2
end as NormTotalPhysicalReads
case when ExecutionCount2>ExecutionCount1 and TotalLogicalReads2>TotalLogicalReads1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalLogicalReads2-TotalLogicalReads1)
when ExecutionCount2=ExecutionCount1 0 then else TotalLogicalReads2
end as NormTotalLogicalReads
case when ExecutionCount2>ExecutionCount1 and TotalLogicalWrites2>TotalLogicalWrites1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalLogicalWrites2-TotalLogicalWrites1)
when ExecutionCount2=ExecutionCount1 0 then else TotalLogicalWrites2
end as NormTotalLogicalWrites

case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalWorkerTime2-TotalWorkerTime1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 null then
else TotalWorkerTime2/ExecutionCount2 end as AvgWorkerTime
case when ExecutionCount2>ExecutionCount1 and TotalWorkerTime2>TotalWorkerTime1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalElapsedTime2-TotalElapsedTime1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 null then
else TotalElapsedTime2/ExecutionCount2
end as AvgElapsedTime
case when ExecutionCount2>ExecutionCount1 and TotalPhysicalReads2>TotalPhysicalReads1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalPhysicalReads2-TotalPhysicalReads1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 null then else TotalPhysicalReads2/ExecutionCount2
end as AvgPhysicalReads
case when ExecutionCount2>ExecutionCount1 and TotalLogicalReads2>TotalLogicalReads1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalLogicalReads2-TotalLogicalReads1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 null then else TotalLogicalReads2/ExecutionCount2
end as AvgLogicalReads
case when ExecutionCount2>ExecutionCount1 and TotalLogicalWrites2>TotalLogicalWrites1 and TotalElapsedTime2>TotalElapsedTime1
then (TotalLogicalWrites2-TotalLogicalWrites1)/(ExecutionCount2-ExecutionCount1)
when ExecutionCount2=ExecutionCount1 null then 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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 @int count=(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 @int DB_ID
declare @int OBJECT_ID
declare @top1 int = 3
declare @int diff1
declare @int count1
-- видалити дублікати більше @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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
GO


CREATE PROCEDURE [srv].[AutoTrace]
@maxfilesize bigint=200 --максимальний розмір файлу в МБ
,@run_minutes int=60 --скільки хвилин буде йти трасування
,@file_patch nvarchar(255)='Шлях до каталогу' --каталог для файлу трасування
,@file_name nvarchar(255)='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, '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='ErrorCode='+cast(@rc as nvarchar(255))+'\r\n'+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
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.