🙄 👩🏻‍🔬 👨🏼‍🤝‍👨🏻 MS SQL Serverでのクエリ、ストアドプロシージャ、およびトリガーのパフォーマンスインジケータの実装。自動ルーティング 🐼 ☦️ 🛄

まえがき

データベース管理者は、遅かれ早かれ、すべてがクエリで問題ないかどうかを示すパフォーマンスインジケータを必要とします。プロファイラーを1日中起動するとシステムに大きな負荷がかかるため、24時間365日使用されるデータベースでは最適なソリューションにならないことも知られています。

それでは、クエリの状態をどのように判断しますか？そして、人間の介入なしでリクエストの問題を検出するときにトレースを実行する方法は？

この記事では、クエリ、ストアドプロシージャ、トリガーのパフォーマンスインジケーターの実装と、トレースを開始するための使用方法を説明します。

解決策

まず、クエリ、ストアドプロシージャ、およびトリガーのパフォーマンスインジケーターを実装するための一般的なアプローチ：

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.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つのシステムビューが使用されます。

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の 2つのシステムビューが使用されます。

3）情報を収集するためのストアドプロシージャを作成します。

3.1）クエリの場合：

コード

 USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[InsertForSQL_StatementExecStat] @koef decimal(12,2)=0.0 -- , --      , --     0.0, --       5  --         --   ,      AS BEGIN SET NOCOUNT ON; declare @AvgCPU_Time bigint ,@MaxAvgCPU_Time bigint ,@AvgTotalWorkerTime bigint ,@MaxTotalWorkerTime bigint ,@AvgAvgElapsedTime bigint ,@MaxAvgElapsedTime bigint ,@AvgTotalElapsedTime bigint ,@MaxTotalElapsedTime bigint select @AvgCPU_Time = AVG(AvgCPU_Time), @MaxAvgCPU_Time = max(AvgCPU_Time), @AvgTotalWorkerTime = AVG(TotalWorkerTime), @MaxTotalWorkerTime = max(TotalWorkerTime), @AvgAvgElapsedTime = AVG(AvgElapsedTime), @MaxAvgElapsedTime = max(AvgElapsedTime), @AvgTotalElapsedTime = AVG(TotalElapsedTime), @MaxTotalElapsedTime = max(TotalElapsedTime) from srv.vStatementExecInfo; insert into srv.SQL_StatementExecStat ( [InsertDate] ,[QueryHash] ,[ExecutionCount] ,[TotalWorkerTime] ,[StatementText] ,[TotalElapsedTime]) select getdate() ,[QueryHash] ,[ExecutionCount] ,[TotalWorkerTime] ,[StatementText] ,[TotalElapsedTime] from srv.vStatementExecInfo where(AvgCPU_Time > @AvgCPU_Time + @koef * (@MaxAvgCPU_Time - @AvgCPU_Time)) or (TotalWorkerTime > @AvgTotalWorkerTime + @koef * (@MaxTotalWorkerTime - @AvgTotalWorkerTime)) or (AvgElapsedTime > @AvgAvgElapsedTime + @koef * (@MaxAvgElapsedTime - @AvgAvgElapsedTime)) or (TotalElapsedTime > @AvgTotalElapsedTime + @koef * (@MaxTotalElapsedTime - @AvgTotalElapsedTime)); END GO

3.2）ストアドプロシージャの場合：

コード

 USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[InsertForProcedureExecStat] @koef decimal(12,2)=0.0 -- , --      , --     0.0, --       5  --         --   ,      AS BEGIN SET NOCOUNT ON; declare @AvgCPU_Time bigint ,@MaxAvgCPU_Time bigint ,@AvgTotalWorkerTime bigint ,@MaxTotalWorkerTime bigint ,@AvgAvgElapsedTime bigint ,@MaxAvgElapsedTime bigint ,@AvgTotalElapsedTime bigint ,@MaxTotalElapsedTime bigint; select @AvgCPU_Time = AVG(AvgCPU_Time), @MaxAvgCPU_Time = max(AvgCPU_Time), @AvgTotalWorkerTime = AVG(TotalWorkerTime), @MaxTotalWorkerTime = max(TotalWorkerTime), @AvgAvgElapsedTime = AVG(AvgElapsedTime), @MaxAvgElapsedTime = max(AvgElapsedTime), @AvgTotalElapsedTime = AVG(TotalElapsedTime), @MaxTotalElapsedTime = max(TotalElapsedTime) from srv.vProcedureExecInfo; insert into srv.SQL_ProcedureExecStat ( [InsertDate] ,database_id ,object_id ,[ExecutionCount] ,[TotalWorkerTime] ,[TotalElapsedTime] ,[TotalPhysicalReads] ,[TotalLogicalReads] ,[TotalLogicalWrites]) select getdate() ,database_id ,object_id ,[ExecutionCount] ,[TotalWorkerTime] ,[TotalElapsedTime] ,[TotalPhysicalReads] ,[TotalLogicalReads] ,[TotalLogicalWrites] from srv.vProcedureExecInfo where(AvgCPU_Time > @AvgCPU_Time + @koef * (@MaxAvgCPU_Time - @AvgCPU_Time)) or (TotalWorkerTime > @AvgTotalWorkerTime + @koef * (@MaxTotalWorkerTime - @AvgTotalWorkerTime)) or (AvgElapsedTime > @AvgAvgElapsedTime + @koef * (@MaxAvgElapsedTime - @AvgAvgElapsedTime)) or (TotalElapsedTime > @AvgTotalElapsedTime + @koef * (@MaxTotalElapsedTime - @AvgTotalElapsedTime)); END GO

3.3）トリガーの場合：

コード

 USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[InsertForTriggerExecStat] @koef decimal(12,2)=0.0 -- , --      , --     0.0, --       5  --         --   ,      AS BEGIN SET NOCOUNT ON; declare @AvgCPU_Time bigint ,@MaxAvgCPU_Time bigint ,@AvgTotalWorkerTime bigint ,@MaxTotalWorkerTime bigint ,@AvgAvgElapsedTime bigint ,@MaxAvgElapsedTime bigint ,@AvgTotalElapsedTime bigint ,@MaxTotalElapsedTime bigint select @AvgCPU_Time = AVG(AvgCPU_Time), @MaxAvgCPU_Time = max(AvgCPU_Time), @AvgTotalWorkerTime = AVG(TotalWorkerTime), @MaxTotalWorkerTime = max(TotalWorkerTime), @AvgAvgElapsedTime = AVG(AvgElapsedTime), @MaxAvgElapsedTime = max(AvgElapsedTime), @AvgTotalElapsedTime = AVG(TotalElapsedTime), @MaxTotalElapsedTime = max(TotalElapsedTime) from srv.vProcedureExecInfo; insert into srv.SQL_TriggerExecStat ( [InsertDate] ,database_id ,object_id ,[ExecutionCount] ,[TotalWorkerTime] ,[TotalElapsedTime]) select getdate() ,database_id ,object_id ,[ExecutionCount] ,[TotalWorkerTime] ,[TotalElapsedTime] from srv.vTriggerExecInfo where(AvgCPU_Time > @AvgCPU_Time + @koef * (@MaxAvgCPU_Time - @AvgCPU_Time)) or (TotalWorkerTime > @AvgTotalWorkerTime + @koef * (@MaxTotalWorkerTime - @AvgTotalWorkerTime)) or (AvgElapsedTime > @AvgAvgElapsedTime + @koef * (@MaxAvgElapsedTime - @AvgAvgElapsedTime)) or (TotalElapsedTime > @AvgTotalElapsedTime + @koef * (@MaxTotalElapsedTime - @AvgTotalElapsedTime)); END GO

4）情報出力の表現を作成します。

4.1）クエリの場合：

コード

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

4.2）ストアドプロシージャの場合：

コード

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

4.3）同様に、トリガーの表現が作成されます（必要な場合）。しかし、私の実践では、トリガーを常に監視する必要はありません。トリガーに関する問題は、ストアドプロシージャとクエリの実行に影響するためです。

実装されたビューでは、2つのインジケータが非常に重要です。

1）AvgWorkerSec-クエリ実行時間（秒）

2）AvgElapsedSec-タイムアウトまたは待機時間+ AvgWorkerSec

表現の結果において、重要な指標は次の同等性です。

AvgWorkerSec = AvgElapsedSec。

そうでない場合、問題はリクエスト自体にあるのではなく、リクエスト計画にもありません。多くの理由が考えられます。私自身が遭遇したもののみを提供します：

1）AvgWorkerSec> AvgElapsedSec-ここでは、要求の実行時に誰かがプロセッサーに大きな負荷をかけています（判明したように、ウイルス対策アプリケーションのスキャンが開始されたため、計画の並列化の障害である可能性があります）

2）AvgWorkerSec <AvgElapsedSec-リクエストを実行する前の待機時間が長すぎます（オプティマイザーは、長時間にわたって手続き型キャッシュの増加またはキャッシュの不足に関する計画の問題を探していました。

等式AvgWorkerSec = AvgElapsedSecが観察された場合、長い間、要求の実行は要求自体とその実行計画にあります。

要求が完了するまでに長い時間がかかるという基準は何ですか？

この質問に対する単一の答えはありません。それは、リクエストが何をするか、どのくらいの頻度で、どこで使用されるかによって異なります等

運用クエリ、ストアドプロシージャについて次の評価を行いました。

1）最大0.5-ストアドプロシージャの場合、これは問題なく、問題はありません（実行に遅延はありません）

2）最大0.1-これはリクエストに適しています。問題はありません（実行に遅延はありません）

3）0.5-1.0-ストアドプロシージャの場合、これは良くありません、問題があります（実行中にユーザーに見える遅延はありませんが、問題は解決する必要がありますが、緊急ではありません）

4）0.1-0.5-リクエストの場合、これは良くありません、問題があります（実行中にユーザーに見える遅延はありませんが、問題は解決する必要がありますが、緊急ではありません）

5）1.0以上-ストアドプロシージャの場合、これには問題があり、問題があります（実行中にユーザーに見える遅延がある可能性が非常に高いため、問題を早急に解決する必要があります）

6）0.5を超える-これはリクエストには不適切であり、問題があります（実行中にユーザーに見える遅延がある可能性が非常に高いため、問題を緊急に解決する必要があります）。

非操作クエリとストアドプロシージャ（データのアップロード、読み込みなど）の場合、この推定値は個別に選択され、通常は操作クエリとストアドプロシージャの推定値を超えることがあります。

すべてのソフトウェアがストアドプロシージャを介して機能する場合、クエリの操作は常にストアドプロシージャの操作に影響するため、クエリなしでストアドプロシージャのみを監視できます。したがって、ストアドプロシージャの実行の分析について詳しく説明します。

次のアルゴリズムに従って、後続の分析および自動ルーティングのために最も困難なストアドプロシージャに関する情報を収集するシステムを作成します。

1）情報を保存するテーブルを作成します。

コード

 USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[SQL_TopProcedureExecStat]( [Row_GUID] [uniqueidentifier] NOT NULL, [SERVER] [nvarchar](255) NOT NULL, [DB_ID] [int] NOT NULL, [OBJECT_ID] [int] NOT NULL, [ExecutionCount] [bigint] NOT NULL, [TotalWorkerTime] [bigint] NULL, [TotalElapsedTime] [bigint] NULL, [Func] [decimal](8, 2) NULL, [AvgWorkerSec] [decimal](8, 2) NULL, [AvgElapsedSec] [decimal](8, 2) NULL, [DB_NAME] [nvarchar](255) NULL, [SCHEMA_NAME] [nvarchar](255) NULL, [OBJECT_NAME] [nvarchar](255) NULL, [InsertUTCDate] [datetime] NOT NULL, [TotalPhysicalReads] [bigint] NULL, [TotalLogicalReads] [bigint] NULL, [TotalLogicalWrites] [bigint] NULL, [AvgPhysicalReads] [bigint] NULL, [AvgLogicalReads] [bigint] NULL, [AvgLogicalWrites] [bigint] NULL, [CategoryName] [nvarchar](255) NULL, CONSTRAINT [PK_SQL_TopProcedureExecStat] PRIMARY KEY CLUSTERED ( [Row_GUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [srv].[SQL_TopProcedureExecStat] ADD CONSTRAINT [DF_SQL_TopProcedureExecStat_Row_GUID] DEFAULT (newid()) FOR [Row_GUID] GO ALTER TABLE [srv].[SQL_TopProcedureExecStat] ADD CONSTRAINT [DF_SQL_TopProcedureExecStat_SERVER] DEFAULT (@@servername) FOR [SERVER] GO ALTER TABLE [srv].[SQL_TopProcedureExecStat] ADD CONSTRAINT [DF_SQL_TopProcedureExecStat_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO

2）情報を収集するためのストアドプロシージャを作成します。

コード

 USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[InsertTopProcedureExecStat] @top tinyint=24 --   (- ) ,@CategoryName nvarchar(255)='AvgWorkerSec' --,    AS BEGIN SET NOCOUNT ON; INSERT INTO [srv].[SQL_TopProcedureExecStat] ([DB_ID] ,[OBJECT_ID] ,[ExecutionCount] ,[TotalWorkerTime] ,[TotalElapsedTime] ,[AvgWorkerSec] ,[AvgElapsedSec] ,[DB_NAME] ,[SCHEMA_NAME] ,[OBJECT_NAME] ,InsertUTCDate ,CategoryName ,TotalPhysicalReads ,TotalLogicalReads ,TotalLogicalWrites ,AvgPhysicalReads ,AvgLogicalReads ,AvgLogicalWrites) select top(@top) [database_id] ,[object_id] ,[Num] ,[TotalWorkerTime] ,[TotalElapsedTime] ,[AvgWorkerSec] ,[AvgElapsedSec] ,[DB_NAME] ,[SCHEMA_NAME] ,[PROCEDURE_NAME] ,InsertUTCDate ,CategoryName ,TotalPhysicalReads ,TotalLogicalReads ,TotalLogicalWrites ,AvgPhysicalReads ,AvgLogicalReads ,AvgLogicalWrites from( select [database_id] ,[object_id] ,[Num] ,[TotalWorkerTime] ,[TotalElapsedTime] ,[AvgWorkerSec] ,[AvgElapsedSec] ,[DB_NAME] ,[SCHEMA_NAME] ,[PROCEDURE_NAME] ,getUTCDate() as InsertUTCDate ,@CategoryName as CategoryName ,TotalPhysicalReads ,TotalLogicalReads ,TotalLogicalWrites ,AvgPhysicalReads ,AvgLogicalReads ,AvgLogicalWrites FROM [srv].[vProcedureExecTotalInfoHour] ) as t order by case @CategoryName when 'TotalWorkerTime' then TotalWorkerTime when 'TotalElapsedTime' then TotalElapsedTime when 'AvgWorkerSec' then AvgWorkerSec when 'AvgElapsedSec' then AvgElapsedSec when 'TotalPhysicalReads' then TotalPhysicalReads when 'TotalLogicalReads' then TotalLogicalReads when 'TotalLogicalWrites' then TotalLogicalWrites when 'AvgPhysicalReads' then AvgPhysicalReads when 'AvgLogicalReads' then AvgLogicalReads when 'AvgLogicalWrites' then AvgLogicalWrites end desc; declare @count int=(select count(*) from [srv].[SQL_TopProcedureExecStat] where CategoryName=@CategoryName); declare @diff int=@count-@top; ;with tbl_del as( select Row_GUID from [srv].[SQL_TopProcedureExecStat] where InsertUTCDate<DateAdd(hour,-24,getUTCDate()) and CategoryName=@CategoryName ) delete from [srv].[SQL_TopProcedureExecStat] where Row_GUID in (select Row_GUID from tbl_del); --     ,   @top,     set @count = (select count(*) from [srv].[SQL_TopProcedureExecStat] where CategoryName=@CategoryName) set @diff = @count - @Top - 3 if(@diff>0) begin ;with tbl_del as( select top(@diff) Row_GUID from [srv].[SQL_TopProcedureExecStat] where CategoryName=@CategoryName order by case @CategoryName when 'TotalWorkerTime' then TotalWorkerTime when 'TotalElapsedTime' then TotalElapsedTime when 'AvgWorkerSec' then AvgWorkerSec when 'AvgElapsedSec' then AvgElapsedSec when 'TotalPhysicalReads' then TotalPhysicalReads when 'TotalLogicalReads' then TotalLogicalReads when 'TotalLogicalWrites' then TotalLogicalWrites when 'AvgPhysicalReads' then AvgPhysicalReads when 'AvgLogicalReads' then AvgLogicalReads when 'AvgLogicalWrites' then AvgLogicalWrites end ) delete from [srv].[SQL_TopProcedureExecStat] where Row_GUID in (select Row_GUID from tbl_del); end declare @DB_ID int declare @OBJECT_ID int declare @top1 int = 3 declare @diff1 int declare @count1 int --    @top1    select top (1) @count1 = tp.num ,@DB_ID = tp.DB_ID ,@OBJECT_ID = tp.OBJECT_ID from (select count(*) as num, DB_ID, OBJECT_ID from [srv].[SQL_TopProcedureExecStat] where CategoryName=@CategoryName group by DB_ID, OBJECT_ID) as tp order by tp.num desc; set @diff1 = @count1 - @top1; if(@diff1) > 0 begin ;with tbl_del as( select top(@diff1) Row_GUID from [srv].[SQL_TopProcedureExecStat] where DB_ID = @DB_ID and OBJECT_ID = @OBJECT_ID and CategoryName=@CategoryName order by case @CategoryName when 'TotalWorkerTime' then TotalWorkerTime when 'TotalElapsedTime' then TotalElapsedTime when 'AvgWorkerSec' then AvgWorkerSec when 'AvgElapsedSec' then AvgElapsedSec when 'TotalPhysicalReads' then TotalPhysicalReads when 'TotalLogicalReads' then TotalLogicalReads when 'TotalLogicalWrites' then TotalLogicalWrites when 'AvgPhysicalReads' then AvgPhysicalReads when 'AvgLogicalReads' then AvgLogicalReads when 'AvgLogicalWrites' then AvgLogicalWrites end ) delete from [srv].[SQL_TopProcedureExecStat] where Row_GUID in (select Row_GUID from tbl_del); end --    1    AvgWorkerSec    if @CategoryName = 'AvgWorkerSec' begin declare @AvgWorkerSec decimal(8,2) select top (1) @count1 = tp.num ,@DB_ID = tp.DB_ID ,@OBJECT_ID = tp.OBJECT_ID ,@AvgWorkerSec = tp.AvgWorkerSec from (select count(*) as num, DB_ID, OBJECT_ID, AvgWorkerSec from [srv].[SQL_TopProcedureExecStat] where CategoryName=@CategoryName group by DB_ID, OBJECT_ID,AvgWorkerSec) as tp order by tp.num desc; set @diff1 = @count1 - 1; if(@diff1) > 0 begin ;with tbl_del as( select top(@diff1) Row_GUID from [srv].[SQL_TopProcedureExecStat] where DB_ID = @DB_ID and OBJECT_ID = @OBJECT_ID and CategoryName=@CategoryName and AvgWorkerSec = @AvgWorkerSec order by InsertUTCDate desc ) delete from [srv].[SQL_TopProcedureExecStat] where Row_GUID in (select Row_GUID from tbl_del); end end if @CategoryName = 'AvgElapsedSec' begin declare @AvgElapsedSec decimal(8,2) select top (1) @count1 = tp.num ,@DB_ID = tp.DB_ID ,@OBJECT_ID = tp.OBJECT_ID ,@AvgElapsedSec = tp.AvgElapsedSec from (select count(*) as num, DB_ID, OBJECT_ID, AvgElapsedSec from [srv].[SQL_TopProcedureExecStat] where CategoryName=@CategoryName group by DB_ID, OBJECT_ID,AvgElapsedSec) as tp order by tp.num desc; set @diff1 = @count1 - 1; if(@diff1) > 0 begin ;with tbl_del as( select top(@diff1) Row_GUID from [srv].[SQL_TopProcedureExecStat] where DB_ID = @DB_ID and OBJECT_ID = @OBJECT_ID and CategoryName=@CategoryName and AvgElapsedSec = @AvgElapsedSec order by InsertUTCDate desc ) delete from [srv].[SQL_TopProcedureExecStat] where Row_GUID in (select Row_GUID from tbl_del); end end END GO

このストアドプロシージャは、ストアドプロシージャに関する情報を収集した直後に実行するのが最適です（クエリとストアドプロシージャおよびトリガーに対して5〜10分ごとに実行するようにエージェントでタスクを構成できます）。

 exec [srv].[InsertForSQL_StatementExecStat]; --     exec [srv].[InsertForTriggerExecStat]; --     exec [srv].[InsertForProcedureExecStat]; --      --          exec [srv].[InsertTopProcedureExecStat] @top=@top, @CategoryName='AvgWorkerSec'; exec [srv].[InsertTopProcedureExecStat] @top=@top, @CategoryName='AvgElapsedSec';

3）トレースの開始（エージェントのタスクを介して-5〜10分ごとに、情報を収集した直後に行うとよい）：

コード

 USE [__]; go --    declare @koef_red numeric(8,3)=1.3; --         --  if(exists( SELECT top(1) 1 FROM [srv].[SQL_TopProcedureExecStat] where CategoryName='AvgElapsedSec' or CategoryName='AvgWorkerSec' group by CategoryName having avg([AvgElapsedSec])>=@koef_red or avg([AvgWorkerSec])>=@koef_red)) begin --  exec .[srv].[AutoTrace]; end

格納された自動ルーティング手順は個別に実装されます。例を挙げます。

コード

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

トレースの構成方法の詳細については、こちらをご覧ください。トレースの作成方法（Transact-SQL）

結果

, . . . .

. .

tSQLt SQLQueryStress

MS SQL Server .

ソース：

» sys.dm_exec_trigger_stats

» sys.dm_exec_procedure_stats

» sys.dm_exec_query_stats

» sys.dm_exec_sql_text

» (Transact-SQL)

» MS SQL Server

» tSQLt SQLQueryStress

MS SQL Serverでのクエリ、ストアドプロシージャ、およびトリガーのパフォーマンスインジケータの実装。 自動ルーティング

まえがき

解決策

結果

ソース：

More articles:

MS SQL Serverでのクエリ、ストアドプロシージャ、およびトリガーのパフォーマンスインジケータの実装。自動ルーティング