通过汇总SQL审核记录来评估应用程序性能 [英] Measure application performance by aggregating SQL audit records

问题描述

假设有一个简单的审计表，其中有两列(生产中有更多列):

Suppose there is a simple audit table with two columns (in production there are more columns):

ID | Date

处理请求后，我们将记录添加到此表中. 请求是成批处理的，一个批次中可以有任意多个项目.对于每个项目，我们将添加一条记录.批次之间至少有2秒的延迟(该数目是可配置的).

When the request is processed, we add a record into this table. Requests are processed in batches, there can be any number of items in a batch. For each item, we will add a record. There will be at least 2 second delay between batches (the number is configurable).

性能的衡量标准是我们每单位时间(例如每秒)可以处理请求的速度.考虑以下示例数据(2个群集，项目数仅用于演示目的):

The performance is measured by how fast we can process requests, per unit of time, for example, per second. Consider this sample data (2 clusters, the number of items is equal for demo purposes only):

--2016-01-29 10:27:25.603
--2016-01-29 10:27:25.620
--2016-01-29 10:27:25.637
--2016-01-29 10:27:25.653
--2016-01-29 10:27:25.723
--Avg time between requests = 24ms

--2016-01-29 10:27:34.647
--2016-01-29 10:27:34.667
--2016-01-29 10:27:34.680
--2016-01-29 10:27:34.690
--2016-01-29 10:27:34.707
--Avg time = 12ms

我们可以说，最坏的情况下，每秒可以处理41.67个请求，最好的情况下，每秒可以处理83.33个请求.也很高兴知道平均批处理性能.

We can say that at worst, 41.67 requests can be processed per second, and 83.33 at best. Would be nice to know the average batch performance as well.

问题.是否可以单独使用T-SQL以及如何获取这些指标?

Question. Is it possible to get these metrics using T-SQL alone and how?

为了使结果具有统计意义，丢弃小于10个项目(可配置)的项目可能有用.

推荐答案

也许我已经简化了您的请求，但是请考虑以下内容

Perhaps I've over simplified your request, but consider the following

Declare @YourTable table (ID int,Date datetime)
Insert Into @YourTable values
( 1,'2016-01-29 10:27:25.603'),
( 2,'2016-01-29 10:27:25.620'),
( 3,'2016-01-29 10:27:25.637'),
( 4,'2016-01-29 10:27:25.653'),
( 5,'2016-01-29 10:27:25.723'),
( 6,'2016-01-29 10:27:34.647'),
( 7,'2016-01-29 10:27:34.667'),
( 8,'2016-01-29 10:27:34.680'),
( 9,'2016-01-29 10:27:34.690'),
(10,'2016-01-29 10:27:34.707')


Declare @BatchSecondsGap int = 2  -- Seconds Between Batches
Declare @MinObservations int = 5  -- Batch must n or greater

;with cte as (
      Select *,Cnt = sum(1) over (Partition By Batch)
       From  (
              Select *,Batch = sum(Flg) over (Order By Date)
               From (
                     Select ID,Date
                           ,Flg = case when DateDiff(SECOND,Lag(Date,1,null) over (Order By Date),Date)>=@BatchSecondsGap then 1 else 0 end
                           ,MS  = case when DateDiff(SECOND,Lag(Date,1,Date) over (Order By Date),Date)>=@BatchSecondsGap then 0 else DateDiff(MILLISECOND,Lag(Date,1,Date) over (Order By Date),Date) end
                      From  @YourTable
                     ) A
             ) B
 )
Select Title    = 'Total'
      ,DateR1   = min(Date)
      ,DateR2   = max(Date)
      ,BatchCnt = count(Distinct Batch)
      ,TransCnt = count(*)
      ,MS_Ttl   = sum(MS)
      ,MS_Avg   = avg(MS*1.0)
      ,MS_Std   = stdev(MS)
 From  cte
 Where Cnt>=@MinObservations
Union All
Select Title    = concat('Batch ',Batch)
      ,DateR1   = min(Date)
      ,DateR2   = max(Date)
      ,BatchCnt = count(Distinct Batch)
      ,TransCnt = count(*)
      ,MS_Ttl   = sum(MS)
      ,MS_Avg   = avg(MS*1.0)
      ,MS_Std   = stdev(MS)
 From  cte
 Where Cnt>=@MinObservations
 Group By Batch

返回

下图显示了批次之间的时间不会受到处罚，因此它成为最终结果的简单汇总

The image below illustrates that you won't be penalized for the time between batches, so then it becomes a simple aggregation for the final results

这篇关于通过汇总SQL审核记录来评估应用程序性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！