软件测试Part11.docx

软件测试Part11.docx

《软件测试Part11.docx》由会员分享，可在线阅读，更多相关《软件测试Part11.docx（35页珍藏版）》请在冰豆网上搜索。

软件测试Part11

第11章SQL数据库层应用测试分析

在任何商业应用程序中最重要的功能就是显示及存储数据。

.NET程序也不例外，拥有一个适当地被分析的和被调整的SQL层对达到高可测量性至关重要。

对于SQL层的瓶颈程序，配制更多或更好的硬件通常是解决不了问题。

在添置新硬件之前，你首先必须确认出影响可测量性的瓶颈所在。

譬如，性能的问题可以是过度的I/O处理，或者是由于低效的被选索引或查询语句而导致的处理器利用率问题。

在本章里，我们集中于如何探测SQL瓶颈，并讨论我们小组频繁地遇到的一些典型的索引问题。

本章书的主要目标是分享我们作为微软的性能分析小组的经验，而并不能列举所有你可能遇到的SQL层的性能问题。

通过分享我们用于鉴别瓶颈的方法,我们希望,你能精确定位出你自己的问题所在。

在你辨认出问题之后,就更加容易地去研究问题或寻找帮助了。

为了演示，我们将使用IBuySpy范例站点的实例;并且必要的话,我们会着重地介绍问题。

本章书的例子运行的结果是基于以下平台：

双重1-GHZPentiumIII处理器,1-GB内存，加载MicrosoftSQLServer2000企业版SP2，Windows2000AdvancedServerSP2。

11.1开始

要解决性能和可测量性问题,你首先需要了解程序数据库设计。

假设您使用SQLServer2000,您还应该比较好的了解交互式SQL（T-SQL）；SQLServer内部运作，譬如查询优化器如何选择执行计划；数据及索引是如何储存；以及SQLServer如何利用数据和执行计划高速缓存。

为了集中于辨认瓶颈，我们假设你已经在使用SQLServer2000并且在一定程序上熟悉象SQL语句分析器和SQLProfiler等内部组件。

幸运地，有非常多有用的书深入地阐述了一些必要的知识点。

我们列出了以下书籍作为参考资料：

∙SQLServerBooksOnline（installedaspartofSQLServer2000）

∙InsideMicrosoftSQLServer2000byKalenDelaney（MicrosoftPress,2000）.

∙TheGuru’sGuidetoTransact-SQLbyKenHenderson（AddisonWesleyLongman,2000）.

∙MicrosoftSQLServer2000PerformanceTuningTechnicalReference（MicrosoftPress,2001）.

除了尽可能多地去学习SQLServer，你还须能重视在生产等效负荷级别或被期望的负荷级别上的SQL服务器。

通常地，由一个单用户产生的负荷是不足显露在SQL层的可测量性问题。

这在第7章我们介绍作为万维网负载工具的微软应用程序测试的地方提及过。

通过在测试环境中创建精确的反映现实世界情景的负载脚本，你可以在产品出现瓶颈之前发现它。

如果你觉得对于问题的解决办法需要用到索引或查询变动，你可以在把变更应用到实际环境之前先在测试环境中调试以便确认。

11.2瓶颈的确认

在很多情况下，你可以运用简单的逻辑去判断SQL层是否瓶颈。

例如，当有多个网络服务器在一个SQL服务器上传输数据，减少网络服务器的数量应该可以减少整体的程序输出。

如果更改网络服务器的数量并不影响到输出量，那么SQL层就大概是瓶颈的来源。

（很频繁地，负载客户端可能在足够的负载到达网络服务器或SQL服务器之前就达到瓶颈。

因此，在每次负载测试中定期测试负载客户端的资源利用率是至关重要的。

）

找出SQL层是否存在问题并不是一项异常困难的任务。

你可以象之前提及的那样减少网络服务器的数量，或者在每一层监控处理器的利用率。

相反，精确指出问题所在和作出合适的解决办法就不是那么容易了，本章节的内容也不会马上给出你这些技能。

反而，我们会向你展示我们是如何找出SQL层瓶颈，并且希望你能从中吸收到我们的经验。

一开始，我们先介绍一下SQLServer2000里的工具。

11.2.1常用工具

我们仅运用SQLServer2000里的工具就可以解决大部分的SQL相关问题。

查询缓慢可用SQLProfiler去确认；阻塞可以用SQLProfiler和SQL语句分析器去探测；而其他关于内存和磁盘之类的瓶颈问题，可以用系统监测器去辨别。

下面来看看每个工具的用法。

1、SQLProfiler

在默认安装路径下，SQLProfiler可以在“【开始所有程序】|【MicrosoftSQLServer】|【Profiler】”下找到。

（要进行跟踪，用户必须拥有被监测SQL服务器的管理员权限）

运行SQLProfiler之后，选择你想监测的服务器并输入登陆信息。

当建立连接后，会打开一个跟踪属性对话框。

对话框有四页:

常规,事件，数据列名和筛选，分别用来指定跟踪属性。

在“常规”页中，你可以指定跟踪的名字，指定模板，设置把数据储存到一个跟踪文件或一个数据表中。

若选择文件，则必须指定路径和文件名；若保存到SQL服务器，则须指定服务器，数据库及数据表。

在“事件”页中，你可以选择监测的事件。

事件会被归纳并分组存放在列举单项事件的地方。

当选定一个事件时会有该事件的描述显示在页面底部。

监测所有的事件会对服务器造成负担。

我们监测的一些典型的事件有：

⏹存储过程：

　StmtCompleted，Completed，RPC:

Completed和TSQL:

SQL:

StmtCompleted，SQL:

BatchCompleted

当一条语句，存储过程，RPC或者批处理语句执行完毕后，这些事件就会被触发。

通过包含文本，读操作，处理器，及跟踪中的持续数据列，你可以辨认缓慢运行的编码和CPU资源被使用的情况。

了解因建立了用于比较优化效果的基线而带来的读操作是很有价值的。

⏹存储过程：

　SP:

Starting，SP:

StmtStarting和TSQL:

　SQL:

StmtStarting

通过包含完整事件在内的这些事件，你可以描述出一个存储过程甚至一行代码。

这对于长期运行的存储过程是有用的，因为它可以让你在完成之前找出有问题的语句。

另外，若在存储过程运行中出现错误，你可以精确找出引起错误的语句。

⏹会话：

ExistingConnection

通过这个事件，你可以检验关于数据库连接的SQL服务器选项设置。

Session选项可以影响查询的运行方式。

例如，要使用有索引的视图，则ARITHABORT选项必须被设置为开，否则其下的表将被用于检索数据而不是视图上的簇集索引。

⏹存储过程：

Recompile

存储过程运行的步骤是分析，编译和运行，每一步都需要一定的时间。

在理想情况下，分析和编译只执行一次，然后仅利用服务器的资源去运行查询。

错误编码的存储过程会在每次调用的时候重新编译，对持续性产生不良影响甚至导致阻塞。

在跟踪中包含此事件，你可以测量出重编译的数量，并且通过包含SP:

Starting事件到同一个跟踪里，可以找出需要优化的代码。

⏹Timeout锁和Deadlock锁

你必须跟踪这个事件，因为这个事件是直接影响程序的可测量性。

锁超时或者死锁等同于不完整事务，是客户端错误引起的。

⏹错误与警告：

Attention

每一次因超时或客户端断开连接都会产生这一事件。

对于一些需要特别多时间去进行的程序，你可能需要调大超时的时间值来减少超时的次数。

例如，在ADO.NET中你可能要为长时间持续的查询设置CommandTimeOut的值至３０秒（默认值）以上。

⏹错误与警告：

MissingColumnStatistics

SQL服务器可以自动生成和维护基表及索引的统计表。

这些信息仅仅关于是基表或索引中一些特定数据列的值的分配，用于协助查询处理器选择一个执行计划。

丢失或过期的统计表会引致查询处理器选择一个低效率的执行计划，对程序的整体输出造成影响。

⏹错误与警告：

MissingJoinPredicate

此事件在一个交叉连接发生时被触发。

交叉连接非常少用到，并且可能是开发代码中一个潜在的漏洞。

跟踪此事件可以让你确定是否有此方式的查询基表操作出现。

⏹错误与警告：

ExceptionandErrorLog

这些事件对于确定是否有异常的因程序负载引起的错误出现是有用的。

通过监视这一事件，可以确定出错误原因是否负载结果。

例如，你可以将所有配置到服务器的连接的跟踪标记都设置为1204和3605，SQLProfiler就可以捕捉到死锁。

死锁是一个非常严重的问题，会严重影响程序的输出和对客户端造成影响。

更多的帮助可以在SQLServer联机登记的“事件类型”下找到。

在“数据列”页中，你可以选择你想捕捉的数据列，共43列，事件类型和SPID是必需的。

根据活动等级，给出的程序会在SQL服务器中生成，并且视乎环境中的用户数量，可能会累计超大量的数据。

要注意的是并不是所有的数据列都包含在内的，因为对于每个事件，并不是所有的数据列都有意义的。

例如：

“持续时间”只对完成事件如SP:

Completed等有效，在SP:

Starting事件中它是空白的。

在表11-1中列出了一些数据列。

表11-1数据列

数据列

描述

事件

事件的名，例如：

存储过程的开始，建立连接等等。

文本

正在执行的SQL命名的文本。

当此列与特定事件不相关时可为空。

CPU

每毫秒使用的CPU资源。

读操作

执行查询是逻辑读操作的次数

写操作

SQL命令执行的物理写操作的次数

持续时间

事件的持续时间，例如执行一个存储过程的时间。

开始时间

事件触发的时间。

结束时间

事件完成的时间，用于结束事件。

嵌套级别

嵌套操作的深度。

例如：

当一个存储过程调用另一个存储过程时，主程序的嵌套级别为1，被调用的嵌套级别为2。

这相当于@@NestValue全局变量。

应用程序名

连接到服务器的应用程序名字。

主机名

提交正在运行的SQL命令的程序所在的机器名。

用户名

正在运行连接的应用程序的Windows用户

登陆用户

Windows用户名或者SQL服务器登陆名，取决用连接时采用的认证方式。

更多的帮助可以在SQLServer联机登记的“数据列”下找到。

在“筛选”页中，你可以指定过滤字来进一步筛选数据。

例如，如果你只想知道某个特定程序生成的SQL活动资料，你可以在关键字中填上应用程序的名字。

默认情况下，由SQLProfiler触发的事件是排除在外的。

你可以通过测试“筛选”页来熟悉所有的可用筛选。

每当选择一个关键字都会在页面底部给出该关键字的解释。

既然知道了跟踪些什么，那么你可能会想该如何去分析所得到的跟踪结果。

SQLProfiler给出了两种选择去储存数据：

跟踪文件或者SQL基表。

实际上，如果你选择了保存为一个文件，你依然可以随时打开文件并把数据储存到一个SQL基表中。

如何选择视乎个人喜好。

（提示：

你可以通过调用系统函数fn_trace_gettable来直接查询跟踪文件。

例如：

以下查询语句将以基表形式返回所有在文件trace.trc里持续时间超过１秒的事件，

SELECT*FROM:

:

fn_trace_gettable（‘c:

\trace.trc’,default）WHEREDuration>1000

更多的帮助可以在SQLServer联机登记的“fn_trace_gettable”下找到。

）

我们发现对于大量的数据，要比较容易地分析跟踪数据，可以将数据保存为基表并且直接查询该表来得到信息，如：

少数持续时间最长的存储过程，或者一个存储过程所有实例的平均持续时间。

当你判定一些代码执行缓慢的时候，先要制定出查询缓慢的时间标准。

（提示：

我们以后在最小负荷下的程序中，持续时间为1秒或以上的代码为深入研究和潜在进展区。

此标准背后的逻辑是在负荷下这些持续时间会容易提高因而减少程序的输出量。

）

当你运行了越来越多的跟踪操作后，你将会发现你通常是在跟踪某些特定的事件和数据列，或者在指定一些特定的关键字。

SQLProfiler允许你将一些常用的跟踪属性保存成一个跟踪模板，以此来简化跟踪的任务。

定义模板的步骤非常简单：

在“文件”菜单中选择“新跟踪模板”，在弹出的跟踪属性对话框中填入事件，数据列和关键字等，然后在“常规”页选点击“保存”。

除此之外，你可以在“文件”菜单中选择“打开跟踪模板”来打开已经存在的模板，然后你自定义其中的属性再保存为不同名的模板。

2、系统监测器

SQLServer展示了一些活动对象可以用系统监测器来监测。

第8章已经对此工具作了详细的述说，重点在于处理器，内存，磁盘和网络等相关系统级资源的计数器。

本节只针对我们常见的SQL服务器的计数器来描述。

在本章开始时列举的书中则详细介绍了其他计数器。

根据你个人的需要，你可能需要了解其他的计数器。

我们常用的SQL服务器的计数器有：

⏹SQLServer缓冲管理

●缓存命中率

能在缓冲池找到而不必读磁盘（I/O）得到的页所占的百分比。

低值可能是内存不足或者索引错误的征兆。

⏹SQLServer常规统计数据

●用户连接数

系统活跃SQL连接的数量，可用于量化系统并发性级别结果报告中。

⏹SQLServer锁管理

●锁执行频率

每秒请求的锁操作的数量，优化查询来减少读操作可以降低这个值。

●超时锁发生频率

等待请求响应超时的锁操作的数量，理想状态下这个值为零。

●锁等待频率

不能马上响应的锁操作的数量，理想状态下是尽可能接近于零。

●NumberofDeadlocks/sec

死锁的数量。

死锁是一个非常严重的问题，会严重影响程序的输出和对客户端造成影响。

这个值应该为零。

通过监视这些计数器的实时数值，你可以发现锁的类型以及它们关于这些计数器各自的数值。

好的索引及合理的锁操作提示会减少不良的情况发生。

⏹SQLServer内存管理

●未分配内存数量

等待分配内存的处理器的数量，这个数值应尽可能接近零，否则可能表示内存发生瓶颈。

⏹SQLServer:

SQL统计数据

●请求处理频率

每秒提交给服务器的批处理请求的数量，用于量化服务器负载的数量。

●SQL指令编译频率

每秒编译的次数，理想情况下很低。

如果BatchRequests/sec的值与此值接近，则可能存在多个特别的SQL调用。

●SQL指令重编译频率

每秒重编译的次数，也应该是很低。

存储过程在理想状态下只应被编译一次而重用执行计划。

若本计数器的值很高则说明要替换存储过程的代码以减少重编译的次数到最少。

3、SQL查询分析器

SQL查询分析器能应用于很多方面，例如：

执行SQL脚本来配制新代码；统计插入或更新的记录数量；分析查询执行计划；执行各种系统存储计划。

在SQLServer2000的工具中，查询分析器是我们团队用的最多的工具。

我们并不详尽地介绍它，而是在本章后面的部分向你展示一些例子，看看是如何利用查询分析器去分析阻塞和分析执行计划的。

查询分析器执行过程

当插入记录到基表中时，在调试前后统计记录的数量对于测量事务输出是很有用的。

统计大表中所有记录的数量是非常费时的，相反，可以通过查询程序数据库的sysindexes系统基表来得到记录总数，语句如下：

SELECT o.name, rows

FROM sysobjects o INNER JOIN sysindexes i on o.id = i.id

WHERE  i.indid < 2

ORDER BY o.name

读sysindexes基表与查询拥有成千上万条记录的表相比只需要少量的I/O操作。

因此，当你需要统计越多的记录，查询sysindexes表就越有好处。

注意通常并不直接就查询系统基表，只在统计数据的操作时间不能接受时才考虑去查询ysindexes表。

11.2.2阻塞问题

当一个资源已被一个连接锁住时，其他连接就不能获得改资源的锁，就有可能出现阻塞。

只有当每一个连接所请求的锁互相冲突时，才会出现阻塞。

这会导致一个连接等待直至另一个连接释放该锁为止。

举个例，假设连接Ａ请求一个Ａ表的exclusive锁，则其他连接不能在Ａ表加上exclusive锁。

其他连接对Ａ表的exclusive锁的请求必须等到连接Ａ释放才可以。

对于应用程序的类型，即使一个高度调整的程序也会出现阻塞；然而，如果频繁出现阻塞则会导致性能问题。

需要付出很多才能知道如何去解决阻塞问题，而且任何对调整SQL服务器有兴趣的人都必须掌握这个技能。

再一次说明，本书并不会列出所有的方法，但我们会示范一些我们在性能测试中确认阻塞的方法。

1、确认连接模块

典型的阻塞瓶颈出现的症状是当SQL层输出量已达到最大，但资源（CPU和内存）利用率却很低。

当你怀疑阻塞是瓶颈时，辨别第一步是查询主数据库中的sysprocesses表。

下面列出了一个阻塞的连接的脚本返回的信息和根模块发送的最后描述。

程序清单11-1

-- Script returns blocking information from the sysprocesses table 

SELECT spid, blocked, status, waittime, 

    waittype, waitresource, db_name（dbid） DatabaseName, cmd, 

    hostname, loginame

FROM master..sysprocesses 

WHERE blocked !

= 0 

DECLARE @spid int

-- Get the root blocker's spid id

SELECT @spid =  A.spid

FROM master..sysprocesses A

    INNER JOIN master..sysprocesses B

    ON A.spid = B.blocked

WHERE A.blocked = 0 

IF NOT @spid IS NULL 

BEGIN

    -- Returns last statement sent from the connection

    DBCC INPUTBUFFER（@spid）

END

IBuySpy范例站点包括了很多本书没有叙述的关于SQL层阻塞的例子。

为了示范，我们会增加一条存储过程，当每次被执行的时候都重编译一次，来模拟编译的阻塞。

以下的脚本就加了这样一个存储过程。

CREATE  PROCEDURE ProductCategoryList_Recompile

WITH RECOMPILE

AS

SELECT 

    CategoryID,

    CategoryName

FROM 

    dbo.Categories

ORDER BY 

    CategoryName ASC

GO

要观察编译阻塞的情况，我们需要同时运行几个重编译的存储过程实例。

在第7章里提及过，我们可以用ACT来模拟SQL服务器的负载。

申请所需负荷的脚本如下：

程序清单11-2

-- The ACT script that can simulate simultaneous

execution of the stored procedure.

Dim oConn

On Error Resume Next

Set oConn = CreateObject（"ADODB.Connection"）

oConn.Open "driver={SQL Server};Server=SQLServer;" & _

"Database=Store;uid=user;pwd=user"

If err.Number <> 0 Then

    Test.Trace（"Error Opening connection:

 " & _

err.number & ", " & err.description）

ELSE

    oConn.Execute（"EXEC ProductCategoryList_Recompile"）

    If err.Number <> 0 Then

        Test.Trace（"Error Executing:

 " & _

err.number & ", " & err.description）

    End If

End IF

你可以设置模拟浏览器连接为10，然后运行测试5分钟。

当测试SQL服务器的时候，执行程序清单11-1里的脚本将会得到关于连接阻塞的信息，以及根模块发送的最后描述。

在这个例子中，阻塞的连接的“等待资源”列会显示阻塞的信息如下：

TAB:

 7:

1173579219 [[COMPILE]]

（TAB后面的“7”表示储存的数据库，“1173579219”则表示编译的对象。

在此例中，“1173579219”代表ProductCategoryList_Recompile。

你可以通过在储存数据库中运行ELECTObject_Name（1173579219）来获得相关数据。

注意“等待资源”列的数据格式可能在下一个版本或补丁包中是不同的。

现行的检索数据库和存储过程的方法可能不适用了。

）

根模块发送的最后描述应显示为ProductCategoryList_Recompile。

当我们有目的地增加问题到ProductCategoryList_Recompile时，查询sysprocesses基表可以显示出同一个存储过程的重编译问题。

过度重编译的出现只是阻塞的众多原因中的一种，事务运行时间过长，不合理的事务隔离级别，以及不适当的索引都能引起阻塞。

不管是什么原因，本节所说的方法能够辨认出阻塞的存储过程或特别的查询。

当辨认它们之后，你就可以进一步分析查询来精确地指出问题。

2、锁操作

对于SQL服务器的锁类型的深厚的知识是解决阻塞问题的本质需要。

我们建议参考在SQLProfiler中提到的锁操作的相关内容。

了解了这些有助于你解决锁超时和死锁问题。

对于存在SQL阻塞的应用程序，我们有代表性地执行了sp_lock去观察锁的批准或者等待。

这过程很一闪而过，所以我们要在测试过程中反复运行多次来观察锁操作的模型。

sp_lock的输出是屏蔽的，而且需要对照来得到表名和索引名。

因此，我们运行一个收录在《TheGuru’sGuidetoTransact-SQL》书中的与sp_lock_verbose类似的自定义的存储过程，来消除反复的对照任务。

再次地，为了演示，我们介绍一个在存储过程中的锁问题。

脚本如下：

CREATEPROCEDUREProductCategoryList_XLOCK

AS

--Usetransactiontoholdtheexclusivelock

BEGINTRANSACTION

SELECT

CategoryID,

CategoryName

FROM

dbo.Categories（XLOCK）

ORDERBY

CategoryNameASC

WAITFORDELAY'00:

00:

01'–-Holdthelockfor1second

COMMITTRANSACTION

GO

在测试SQL服务器时与sp_lock同时运行此脚本会得到与下面类似的输出：

spiddbidObjIdIndIdTypeResourceModeStatus

-------