SQL Server 2012引入了一个新的performance counter Batch Resp Statistics。这个counter的目的是“to track SQL Batch Response times”。这个counter下面有好几档如下:

 

具体就是:

Batches >=000000ms & <000001ms                                                                                                 

Batches >=000001ms & <000002ms                                                                                                 

Batches >=000002ms & <000005ms                                                                                                 

Batches >=000005ms & <000010ms                                                                                                 

Batches >=000010ms & <000020ms                                                                                                 

Batches >=000020ms & <000050ms                                                                                                 

Batches >=000050ms & <000100ms                                                                                                 

Batches >=000100ms & <000200ms                                                                                                 

Batches >=000200ms & <000500ms                                                                                                 

Batches >=000500ms & <001000ms                                                                                                 

Batches >=001000ms & <002000ms                                                                                                 

Batches >=002000ms & <005000ms                                                                                                 

Batches >=005000ms & <010000ms                                                                                                 

Batches >=010000ms & <020000ms                                                                                                 

Batches >=020000ms & <050000ms                                                                                                 

Batches >=050000ms & <100000ms                                                                                                 

Batches >=100000ms                                                                                                             

 

这些档的含义是怎样的呢?假定对于Batches >=010000ms & <020000ms,它的含义是这样的:

1)    如果batch (也就是一个request)的执行时间是10000ms 20000ms之间的则计数

2)    CPU time Elapsed time 的含义和sys.dm_exec_requests 里面的含义一致。 CPU time 就是 “请求所使用的 CPU 时间”, Elapsed time 就是“请求到达后经过的总时间”。 简单说,CPU time就是batch requestCPU指令真正执行的时间,而Elapsed time 是指CPU时间加上等待的时间。比如当batch等待磁盘I/O,等待锁(就是被阻塞了)的时候,Elapsed time就一般比CPU time 长。这里注意的是因为并发执行的关系,Elapsed time 有时候并不会就恒等于CPU time 加上等待时间,而是应该有些出入。

3)    CPU timeRequests CPU time 在这个范围内的batch request的总数。CPU timetotalms)指CPU time 在这个范围内的时间总和。Elapsed timerequest Elapsed timerequests 的含义类似。

4)     这些counter的值是累计的。也就是说,会一直增加,直到SQL server 重启。

 

 

为了测试这个counter,我使用ostress.exe测试工具。这个工具可以产生指定数目的连接,反复执行指定的脚本。我的测试脚本如下:

1.  首先建立测试的表

 

USE[Test_good]

GO

 

/****** Object:  Table [dbo].[test]    Script Date: 2012/12/26 14:57:56 ******/

DROPTABLE[dbo].[test]

GO

 

/****** Object:  Table [dbo].[test]    Script Date: 2012/12/26 14:57:56 ******/

SETANSI_NULLSON

GO

 

SETQUOTED_IDENTIFIERON

GO

 

SETANSI_PADDINGON

GO

 

CREATETABLE[dbo].[test](

    [colid][int]IDENTITY(1,1)NOTNULL,

    [col2][varchar](400)NULL

)ON[PRIMARY]

 

GO

 

SETANSI_PADDINGOFF

GO

 

2.  然后插入上千万的记录。

  

3.  然后使用如下的脚本测试。为了让测试更有趣,我在语句里面使用了rand()函数,以及动态判断是否需要人为的delay (见waitfor delay 语句)2秒钟:

 

usetest_good

go

declare@col1int=rand()*10000

declare@col2int=rand()*100000

declare@tint

if (@col1>@col2)

begin

set@t=@col1

set@col1=@col2

set@col2=@t

end

begintran

updatetestsetcol2=getdate()wherecolid=(@col1+@col2)/2

declare@iint=rand()*100

if (@i>50)waitfordelay'00:00:02'

committran

select@col1,@col2

select*fromtestwherecolidbetween@col1and@col2

go

select 1

declare@iint=rand()*100

if (@i<30)waitfordelay'00:00:02'

go

select 2

go

declare@iint=rand()*100

if (@i>30)waitfordelay'00:00:02'

go

 

测试的结果如下:

 

 

 

从这个可以看出:

系统的语句主要分为几类(按照Elapsed time):

Batches >=000000ms & <000001ms     672

Batches >=002000ms & <005000ms     309

 

如果觉得上面的图形看起来不够直观,那么还可以使用下面的语句来得到更方便阅读的结果:

(注:语句来自Neil Hambly

sELECT[counter_name],"CPU Time:Total(ms)","CPU Time:Requests","Elapsed Time:Total(ms)","Elapsed Time:Requests"

FROM (SELECT[counter_name],[instance_name],[cntr_value]FROMsys.dm_os_performance_countersWHEREOBJECT_NAMELIKE'%Batch Resp Statistics%')os_pc

PIVOT(AVG([cntr_value])FOR[instance_name]

IN("CPU Time:Total(ms)","CPU Time:Requests","Elapsed Time:Total(ms)","Elapsed Time:Requests"))ASPvt;

 

结果如下:

 

这个结果一目了然:

1)  你可以看到系统所有语句的响应时间的分布情况,真正体现了这个counter resp statistics 的字面含义,也即系统batch request的响应时间统计信息。

2)  你很容易知道系统的大部分的语句的响应时间和语句的总数

3)  通过比较CPU time Elapsed time的差异,你很容易知道batch request 是否经常因为等待(如等待I/O,阻塞等)而导致长的执行总时间。