Geekly Articles each Day
📜 ⬆️ ⬇️

Implementation of the performance indicator of queries, stored procedures and triggers in MS SQL Server. Autotrace

Foreword


The database administrator will sooner or later want to have a performance indicator that shows whether everything is good with queries. It is also known that the launch of Profiler for the whole day significantly loads the system, and therefore cannot be the optimal solution in the database, which is used 24x7.

So how to determine the status of requests? And how to run a trace when it detects problems with queries without human intervention?

In this article, I’ll provide an implementation indicator for query performance, stored procedures, and triggers, as well as their use to start tracing.

Decision


First, a general approach to implementing a query performance indicator, stored procedures, and triggers:
')
1) create the necessary tables for the collection and analysis of information
2) create views to collect information
3) create stored procedures to collect information
4) create views to display information

And now the implementation:

1) create the necessary tables for the collection and analysis of information:

1.1) for requests:
Code
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) for stored procedures:
Code
 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) for triggers:
Code
 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) create views to collect information (here you can also insert filters, that is, remove unnecessary information (for example, queries and procedures with replication triggers, etc.)):

2.1) for requests:
Code
 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



It uses two system views sys.dm_exec_query_stats and sys.dm_exec_sql_text

2.2) for stored procedures:
Code
 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



It uses two system views sys.dm_exec_Procedure_stats and sys.dm_exec_sql_text

2.3) for triggers:
Code
 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



It uses two system views sys.dm_exec_trigger_stats and sys.dm_exec_sql_text

3) create stored procedures to collect information:

3.1) for requests:
Code
 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) for stored procedures:
Code
 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) for triggers:
Code
 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) create views to display information:

4.1) for requests:
Code
 USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [srv].[vStatementExecTotalInfo] as select ExecutionCount as Num ,TotalWorkerTime as TotalWorkerTime ,TotalElapsedTime as TotalElapsedTime ,convert(decimal(8,2),AvgCPU_Time/1000000.) as AvgWorkerSec ,convert(decimal(8,2),AvgElapsedTime/1000000.) as AvgElapsedSec ,... ,QueryHash ,StatementText from [SRV].[srv].[vStatementExecInfo]; GO



4.2) for stored procedures:
Code
 USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [srv].[vProcedureExecTotalInfo] as select ExecutionCount as Num ,TotalWorkerTime as TotalWorkerTime ,TotalElapsedTime as TotalElapsedTime ,convert(decimal(8,2),AvgCPU_Time/1000000.) as AvgWorkerSec ,convert(decimal(8,2),AvgElapsedTime/1000000.) as AvgElapsedSec ,... ,database_id ,object_id ,db_name(database_id) as DB_Name ,OBJECT_SCHEMA_NAME(object_id, database_id) as Schema_Name ,object_name(object_id, database_id) as Procedure_Name from [SRV].[srv].[vProcedureExecInfo]; GO



4.3) Submissions for triggers are made in a similar way (if necessary). But in my practice, there is no need to track all the time triggers, and problems with them will affect the execution of stored procedures and queries.

In the realized representations two indicators are very important:

1) AvgWorkerSec - the query execution time itself in seconds
2) AvgElapsedSec - waiting or waiting time + AvgWorkerSec

In the results of representations, an important indicator is the following equality:
AvgWorkerSec = AvgElapsedSec.

If this is not the case, then the problem is not in the request itself or in terms of the request. The reasons may be many. I will cite only those encountered myself:

1) AvgWorkerSec> AvgElapsedSec - here someone heavily loads the processor at the time of the request (as it turned out, the scan of the anti-virus application was started, it could also be the whole fault of the parallelization plan)
2) AvgWorkerSec <AvgElapsedSec - there is too much waiting before executing the query (the optimizer takes a long time to find a plan — a problem with the proliferation of a procedural cache or a lack of cache; third-party software loaded disks with many inserts of records into the log file).

If the AvgWorkerSec = AvgElapsedSec equality is met, then the long execution time of the query lies in the query itself and in its execution plan.

What is the criterion that the request takes a long time?
There is no definitive answer to such a question. It depends on what the query does, how often and where is it used? Etc.

I have made the following assessment for operational requests, stored procedures:

1) up to 0.5 - for stored procedures this is good, there are no problems (no delays in execution)
2) to 0.1 - for requests it is good, there are no problems (no delays in execution)
3) 0.5 - 1.0 - this is not good for stored procedures, there are problems (there are no delays in execution for the user, but they are there, the problem needs to be solved, but not urgent)
4) 0.1 - 0.5 - this is not good for requests, there are problems (there are no delays in execution for the user, but they are there, the problem needs to be solved, but not urgent)
5) more than 1.0 - this is bad for stored procedures, there are problems (it is very likely that there are visible delays in user execution, the problem needs to be solved urgently)
6) more than 0.5 - for requests it is bad, there are problems (it is very likely that there are visible delays in the user’s execution, the problem must be solved urgently).

For non-operational requests and stored procedures (uploading, loading data, etc.), this estimate is selected individually and usually several times higher than the estimates for operational requests and stored procedures.

If all the software works through stored procedures, then you can generally track only stored procedures without requests, that is, the work of requests will always affect the operation of stored procedures. Therefore, we will focus on the analysis of the implementation of stored procedures in more detail.

We will now create a system that will collect information on the heaviest stored procedures for subsequent analysis and start of autotracing, according to the following algorithm:

1) create a table in which we will store information:
Code
 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) create a stored procedure to collect information:
Code
 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



It is best to start this stored procedure immediately after collecting information about stored procedures (you can configure the task in the Agent to run every 5-10 minutes for requests and stored procedures and triggers):

 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) start the trace (via the Agent's tasks — every 5-10 minutes, preferably immediately after collecting the information):
Code
 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



The stored autotrace procedure is implemented individually. I will give an example:
Code
 USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[AutoTrace] @maxfilesize bigint=200 --     ,@run_minutes int=60 --      ,@file_patch nvarchar(255)=N'  ' --    ,@file_name nvarchar(255)=N'Profiler' --  ,@res_msg nvarchar(255)=NULL output --    AS BEGIN SET NOCOUNT ON; declare @rc int; declare @TraceID int; if(@run_minutes>=1200) set @run_minutes=1200; --  20 ! declare @finish_dt datetime=DateAdd(minute,@run_minutes,GetDate()); --    --   declare @finish_dt_inc nvarchar(255)=N'_'+cast(YEAR(@finish_dt) as nvarchar(255))+'_'+cast(MONTH(@finish_dt) as nvarchar(255))+'_'+cast(DAY(@finish_dt) as nvarchar(255)); declare @File nvarchar(255)=@file_patch+@file_name+@finish_dt_inc; --    DECLARE @result bit; DECLARE @msgerrors nvarchar(255); DECLARE @oldDT datetime; --     if(object_id('__.dbo.TraceTable')<>0) begin select @oldDT=max(StartTime) from __.dbo.TraceTable where StartTime is not null; end --select @oldDT; --           ,   , -      if(@oldDT is null or @oldDT<DATETIMEFROMPARTS(YEAR(@finish_dt), MONTH(@finish_dt), DAY(@finish_dt), 0, 0, 0, 0)) begin --  exec @rc = sp_trace_create @TraceID=@TraceID output, --  @Options=0, --  ( ) @TraceFile=@File, --   @MaxFileSize=@maxfilesize, --    (     ) @StopTime=@finish_dt--, --       --@FileCount=2; --- ,     (     ) --     ( 0),       if (@rc = 0) begin --     declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 10, 1, @on exec sp_trace_setevent @TraceID, 10, 9, @on exec sp_trace_setevent @TraceID, 10, 2, @on exec sp_trace_setevent @TraceID, 10, 66, @on exec sp_trace_setevent @TraceID, 10, 10, @on exec sp_trace_setevent @TraceID, 10, 3, @on exec sp_trace_setevent @TraceID, 10, 4, @on exec sp_trace_setevent @TraceID, 10, 6, @on exec sp_trace_setevent @TraceID, 10, 7, @on exec sp_trace_setevent @TraceID, 10, 8, @on exec sp_trace_setevent @TraceID, 10, 11, @on exec sp_trace_setevent @TraceID, 10, 12, @on exec sp_trace_setevent @TraceID, 10, 13, @on exec sp_trace_setevent @TraceID, 10, 14, @on exec sp_trace_setevent @TraceID, 10, 15, @on exec sp_trace_setevent @TraceID, 10, 16, @on exec sp_trace_setevent @TraceID, 10, 17, @on exec sp_trace_setevent @TraceID, 10, 18, @on exec sp_trace_setevent @TraceID, 10, 25, @on exec sp_trace_setevent @TraceID, 10, 26, @on exec sp_trace_setevent @TraceID, 10, 31, @on exec sp_trace_setevent @TraceID, 10, 34, @on exec sp_trace_setevent @TraceID, 10, 35, @on exec sp_trace_setevent @TraceID, 10, 41, @on exec sp_trace_setevent @TraceID, 10, 48, @on exec sp_trace_setevent @TraceID, 10, 49, @on exec sp_trace_setevent @TraceID, 10, 50, @on exec sp_trace_setevent @TraceID, 10, 51, @on exec sp_trace_setevent @TraceID, 10, 60, @on exec sp_trace_setevent @TraceID, 10, 64, @on exec sp_trace_setevent @TraceID, 12, 1, @on exec sp_trace_setevent @TraceID, 12, 9, @on exec sp_trace_setevent @TraceID, 12, 3, @on exec sp_trace_setevent @TraceID, 12, 11, @on exec sp_trace_setevent @TraceID, 12, 4, @on exec sp_trace_setevent @TraceID, 12, 6, @on exec sp_trace_setevent @TraceID, 12, 7, @on exec sp_trace_setevent @TraceID, 12, 8, @on exec sp_trace_setevent @TraceID, 12, 10, @on exec sp_trace_setevent @TraceID, 12, 12, @on exec sp_trace_setevent @TraceID, 12, 13, @on exec sp_trace_setevent @TraceID, 12, 14, @on exec sp_trace_setevent @TraceID, 12, 15, @on exec sp_trace_setevent @TraceID, 12, 16, @on exec sp_trace_setevent @TraceID, 12, 17, @on exec sp_trace_setevent @TraceID, 12, 18, @on exec sp_trace_setevent @TraceID, 12, 26, @on exec sp_trace_setevent @TraceID, 12, 31, @on exec sp_trace_setevent @TraceID, 12, 35, @on exec sp_trace_setevent @TraceID, 12, 41, @on exec sp_trace_setevent @TraceID, 12, 48, @on exec sp_trace_setevent @TraceID, 12, 49, @on exec sp_trace_setevent @TraceID, 12, 50, @on exec sp_trace_setevent @TraceID, 12, 51, @on exec sp_trace_setevent @TraceID, 12, 60, @on exec sp_trace_setevent @TraceID, 12, 64, @on exec sp_trace_setevent @TraceID, 12, 66, @on exec sp_trace_setevent @TraceID, 13, 1, @on exec sp_trace_setevent @TraceID, 13, 9, @on exec sp_trace_setevent @TraceID, 13, 3, @on exec sp_trace_setevent @TraceID, 13, 11, @on exec sp_trace_setevent @TraceID, 13, 4, @on exec sp_trace_setevent @TraceID, 13, 6, @on exec sp_trace_setevent @TraceID, 13, 7, @on exec sp_trace_setevent @TraceID, 13, 8, @on exec sp_trace_setevent @TraceID, 13, 10, @on exec sp_trace_setevent @TraceID, 13, 12, @on exec sp_trace_setevent @TraceID, 13, 14, @on exec sp_trace_setevent @TraceID, 13, 26, @on exec sp_trace_setevent @TraceID, 13, 35, @on exec sp_trace_setevent @TraceID, 13, 41, @on exec sp_trace_setevent @TraceID, 13, 49, @on exec sp_trace_setevent @TraceID, 13, 50, @on exec sp_trace_setevent @TraceID, 13, 51, @on exec sp_trace_setevent @TraceID, 13, 60, @on exec sp_trace_setevent @TraceID, 13, 64, @on exec sp_trace_setevent @TraceID, 13, 66, @on --   declare @intfilter int; declare @bigintfilter bigint; exec sp_trace_setfilter @TraceID, 10, 0, 7, N' SQL Server Profiler - fa35966e-e426-4d1a-8753-8f971cf89495'; exec sp_trace_setfilter @TraceID, 35, 0, 6, N'%__%'; exec sp_trace_setfilter @TraceID, 35, 1, 6, N'%__%'; --  exec sp_trace_setstatus @TraceID, 1; --    declare @run_delay int=@run_minutes+1; --   1        declare @run_delay_hour int=@run_delay/60; --   declare @run_delay_minute int=@run_delay-(@run_delay/60)*60; --   declare @run_delay_hour_str nvarchar(2); --   declare @run_delay_minute_str nvarchar(2); --   --       if(@run_delay_hour=0) set @run_delay_hour_str='00'; else if(@run_delay_hour<10) set @run_delay_hour_str='0'+cast(@run_delay_hour as nvarchar(255)); else if(@run_delay_hour>=10) set @run_delay_hour_str=cast(@run_delay_hour as nvarchar(255)); --select @run_delay_hour, @run_delay_hour_str; --       if(@run_delay_minute=0) set @run_delay_minute_str='00'; else if(@run_delay_minute<10) set @run_delay_minute_str='0'+cast(@run_delay_minute as nvarchar(255)); else if(@run_delay_minute>=10) set @run_delay_minute_str=cast(@run_delay_minute as nvarchar(255)); --select @run_delay_minute, @run_delay_minute_str; --  :   declare @run_delay_str nvarchar(255)=@run_delay_hour_str+':'+@run_delay_minute_str; -- WAITFOR DELAY @run_delay_str; --select @run_delay_str; --      if(object_id('__.dbo.TraceTable')<>0) begin drop table __.dbo.TraceTable; end --        SELECT * INTO __.dbo.TraceTable FROM ::fn_trace_gettable(@File+'.trc', default); --      set @File=@File+'.trc'; --   ,     declare @str_title nvarchar(max)='     '+@@servername, @str_pred_mess nvarchar(max)=' '+@@servername+'    .      __.dbo.TraceTable'; --        end --  set @res_msg=N'ErrorCode='+cast(@rc as nvarchar(255))+'\r\n'+coalesce(@msgerrors, ''); end END GO



You can read more about how to set up tracing here. How to create a trace (Transact-SQL)

Result


This article has reviewed an example of the implementation of the collection system on the state of the database, which does not load the system. Also, this system, in case of detection of a problem, runs the previously configured trace and stores it in a table. This approach can be extended to multiple servers. Then it is necessary to collect information from all servers for sending the report to administrators.

It is also important not to forget to delete old data from used tables. It is quite enough to store data up to a month or even two weeks.

Another interesting solution is here Testing database performance using tSQLt and SQLQueryStress The

following is an example of the implementation of a general MS SQL Server performance indicator .

Sources:


» Sys.dm_exec_trigger_stats
» sys.dm_exec_procedure_stats
» sys.dm_exec_query_stats
» sys.dm_exec_sql_text
» How to create a trace (Transact-SQL)
» Example of implementation of a general MS SQL Server performance indicator
» Database Performance Testing with tSQLt and SQLQueryStress

Source: https://habr.com/ru/post/314494/

More articles:


All Articles