为 SQL 提速

项目
07/10/2015

通过层交互分析优化数据库调用

Mark Friedman

许多应用程序专为使用多个层而设计。在这样的应用程序中，就应用程序的整体响应而言，对数据访问层的调用的性能至关重要。使用多个层可以提高应用程序的灵活性。n 层方法也可以帮助实现关键组件的隔离，这可用于提高可靠性和可伸缩性。将组件隔离到不同层中后，可以更轻松地在可用计算资源间分布，因此使用多个层可以提高可伸缩性。

层交互分析 (TIP) 旨在帮助您了解应用程序所依赖的数据层的性能。TIP 是 Visual Studio 分析工具提供的一种新功能，用于测量和报告 .NET Framework 应用程序在等待对 ADO.NET 兼容数据库的同步调用完成时，所经历的数据层延迟持续时间。对于经常调用数据库而又注重响应时间的应用程序，TIP 可以帮助您了解哪些数据请求是造成响应延迟的主要原因。

本文将介绍 TIP 并演示其报告功能。另外还将讨论 TIP 所依赖的检测技术，并提供一些有效使用 TIP 诊断与数据库活动相关的性能问题的最佳实践。本文将逐步介绍在以下示例环境中使用 TIP：数据密集型的双层 ASP.NET Web 应用程序，并且使用 LINQ to SQL 技术从 Microsoft SQL Server 访问数据。最后将讨论如何使用标准 SQL 管理员性能工具来增加 TIP 性能数据，以便加深对数据层性能的理解。

TIP 入门

TIP 会动态添加检测代码，用于在分析运行期间测量应用程序的数据层调用的持续时间。Visual Studio 分析工具在 Visual Studio 2010 Premium Edition 和 Visual Studio 2010 Ultimate Edition 中提供。

若要启动分析会话，可以从“Analyze”（分析）菜单单击“Launch Performance Wizard”（启动性能向导），也可以从“Debug”（调试）菜单单击“Start Performance Analysis”（启动性能分析），或者使用 Alt+F2 快捷键。在性能向导的第一页上，将要求您选择一种分析方法。

TIP 适用于任何一种分析方法（采样、检测、内存或并发），但默认情况下未启用。若要启用 TIP，您需要在性能向导的第三页上取消选中“在向导完成后启动分析”复选框。（由于 TIP 尚未启用，您现在无法启动应用程序并开始分析。）为了得到最佳结果，建议在一开始选择采样分析方法，这尤其适用于您最注重的是数据层交互数据的情况。这主要是因为采样对应用程序性能的影响最小。

若要启用 TIP 数据收集，请在性能向导中访问刚刚在“Performance Explorer”（性能资源管理器）窗口中创建的性能会话，然后右键单击以查看其属性。在属性对话框中，选择“Tier Interactions”（层交互）选项卡，然后选中“Enable tier interaction profiling”（启用层交互分析）复选框。单击“OK”（确定）按钮可关闭该对话框。此时 TIP 已经启用，您已准备好开始对应用程序进行分析。

若要开始运行实际分析，请单击“Performance Explorer”（性能资源管理器）窗口中的“Launch with Profiling”（启动并分析）工具栏按钮。

有关在 Visual Studio 中启用 TIP 的完整说明，请参见 Habib Heydarian 的博客文章“Walkthrough:Using the Tier Interaction Profiler in Visual Studio Team System 2010”，地址为 https://blogs.msdn.com/b/habibh/archive/2009/06/30/walkthrough-using-the-tier-interaction-profiler-in-visual-studio-team-system-2010.aspx。

TIP 测量性能的方法

TIP 在分析运行期间将代码插入应用程序，用于测量对应用程序所使用的 ADO.NET 数据层的调用。当层交互分析活动时，Visual Studio 分析器检查解决方案中的 Microsoft 中间语言 (MSIL)，查找对 ADO.NET 函数的引用。调用实时 (JIT) 编译器以生成由应用程序运行的本机代码之前，分析器插入向关键 ADO.NET 方法添加检测代码的指令。此检测代码跟踪每次 ADO.NET 调用期间所用的时间量。

随着应用程序执行，TIP 将捕获和记录计时数据。对应用程序进行分析时，此检测代码将记录执行的所有 ADO.NET 调用的持续时间，同时还捕获数据库调用中使用的命令文本的副本。在应用程序运行期间，TIP 会收集使用 ADO.NET 类同步方法（包括 SQL、OLE DB、开放数据库连接 (ODBC) 和 SQL Server Compact (SQL CE) API）执行的所有数据库访问的计时数据。如果应用程序使用 LINQ to SQL 或实体框架 API 访问 SQL 数据库，则 TIP 还将捕获计时数据。

计时数据与分析会话期间收集的所有其他数据一起存储到 Visual Studio 分析器文件 (.vsp) 中。由于调用外部数据库的应用程序执行的是进程外调用，因此 TIP 添加用于检测应用程序的指令对应用程序整体性能产生的影响非常小。

性能优化

在常见设计模式中，将 Web 应用程序划分为表示层、业务逻辑层和数据层。此设计模式会导致将应用程序划分为各个组件，以实现可靠性、可扩展性和可伸缩性。多层应用程序使用业务逻辑组件访问其数据层。这些组件将数据实体在逻辑上组织为一组相关表中的行和列，以此引用数据实体。根据设计，SQL Server 等数据库维护与数据库表关联的物理数据的方式对应用程序是透明的。

出于可靠性和可伸缩性考虑，大型 Web 应用程序通常在池中配置多台计算机，负责与应用程序的各层关联的逻辑处理。使用多台计算机支持多个层在性能分析方面造成了特殊的挑战，这是因为监视任意一台计算机都只能提供不完整的应用程序信息。

例如，使用 SQL Server 之类的数据库系统管理和仲裁对应用程序数据存储的访问，会造成数据层与应用程序逻辑的隔离。驻留在诸如 SQL Server 之类的数据库中的应用程序数据在独立的进程地址空间中维护，具有自己的数据存储。包含应用程序数据的数据库可以与应用程序驻留在同一台物理计算机上，但更倾向于使用网络协议从不同计算机进行访问。

应用程序传递到外部数据库的 SQL 命令以及在数据库上操作的命令为进程外调用。采样分析在等待这些进程外调用完成时，将应用程序视为休眠，但无法确定应用程序等待的原因或者这些延迟的持续时间。检测和并发分析会测量这些延迟的持续时间，但无法确定所发布的 SQL 命令以及这些命令花费如此长的时间来完成的原因。

在与外部数据库通信的多层应用程序中，数据库组件通常在整体应用程序响应时间中占据主要比例。Visual Studio 分析工具包括多种分析方法，其中有采样、检测、内存分配、和并发，但如果没有 TIP 数据，这些方法对于确定与访问外部数据库相关的性能问题都没有多少帮助。

使用 TIP 数据可以深入剖析与数据库相关的延迟，并了解发生延迟的原因。将此数据与数据库供应商提供的其他性能工具一起使用时，您还可以了解采取哪些操作来提高很大程度上依赖于数据库性能的应用程序的性能。

由于 TIP 将检测代码添加到应用程序代码中，因此可以收集与数据库命令相关的计时数据而不受所访问数据库实例的物理位置的影响。例如，对于与应用程序驻留在相同物理计算机上的 SQL Server 实例（这是单元测试中的一种常见情况），可以收集该实例的进程外调用的计时数据。当同一个应用程序准备好针对其他物理计算机上的 SQL Server 实例执行集成或负载测试时，TIP 可以继续收集该配置的测量数据。实际上，使用 TIP 测量可以比较这两个不同配置的性能。

您可以通过 TIP 比较和对照多个外部数据库性能和可用优化选项的影响，包括高速缓存配置、物理数据存储设备、数据库分区、数据库索引和数据库表设计。此外，您可以直接测量在虚拟机上运行 SQL Server 的性能影响。

TIP 报告基本信息

当激活了 TIP 的分析会话完成后，将在“层交互”视图中汇总应用程序与其 ADO.NET 数据层的任意交互的相关计时数据。图 1 显示激活了 TIP 数据收集并且在分析运行期间存在 ADO.NET 活动的分析器示例。

图 1 Visual Studio 分析器层交互报告

报告的上半部分是收集的分析数据的摘要。对于 ASP.NET 应用程序，该视图按 URL 组织。报告按照 URL 对 Web 应用程序 GET 请求的服务器端响应时间分组。

在应用程序层下，报告显示与数据库层（在本例中为 AdventureWorks 示例数据库）的各个连接。其中测量和报告的是 ASP.NET 请求的服务器端处理时间部分，这一部分与使用 ADO.NET 的同步数据库调用相关。在本例中显示了三行摘要，每一行汇总了与所分析网站中三个不同 ASP.NET 页面相关的数据库活动。对于在分析期间标识的每个 ASP.NET 页面，将报告分析运行期间处理的 ASP.NET 请求数以及生成的每个响应消息的服务器端响应时间。

额外的摘要行显示其他 GET 请求的响应时间数据，包括对样式表、Javascript 代码和页面中链接的图像的请求。分析器无法与特定 ASP.NET 请求关联的任何数据库调用都分组在“Other Requests”（其他请求）类别下。

在分析使用数据层的 Windows 桌面或控制台应用程序时，该报告将按进程名称对 ADO.NET 活动进行划分。

在每个网页摘要行下列出单独一行摘要，其中报告在 ASP.NET 处理期间发出的同步数据库请求数，这些请求按照数据库连接进行组织。在本例中，您可以看到在单个数据库连接中处理了六个对 CustomerQuery.aspx 的 ASP.NET 请求。这六个请求在服务器上的总处理用时为 0.959 秒，平均响应时间为 160 毫秒。这些请求发布了 12 个 SQL 查询，用时约 45 毫秒完成。与为此网页生成响应消息相关的用时中，对数据库请求的等待时间仅占约 5%。

如果突出显示其中一个数据库连接摘要行，则“层交互”视图的下半部分将详细列出应用程序发出的特定 SQL 命令。SQL 命令按照发布的命令文本分组，并按照在该页面组中的用时排序。

在本示例中，一条 SQL 命令发布了三次，另一条命令发布了六次，第三个查询发布了三次。对于详细视图，在摘要报告中累计到单行中的各个特定查询的用时将分别报告。您可以查看总用时、该命令在所有实例上的平均用时以及对于每个查询所观察到的最短和最长延迟。

如果双击 SQL 命令详细信息行，则将在“Database Command Text”（数据库命令文本）窗口中显示所发布的 SQL 命令的完整文本。这是应用程序在执行期间通过 ADO.NET 接口传递到数据库的实际命令。如果请求针对的是存储过程的执行，则将显示对存储过程的特定调用。

LINQ to SQL 示例

现在介绍一个使用 TIP 的简单示例，通过它可以了解很大程度上依赖于从数据库访问信息的 ASP.NET 应用程序。

对于使用 LINQ to SQL 访问存储在外部 SQL Server 数据库中的数据的应用程序，TIP 尤为有用，这是因为 LINQ 的目的是让开发人员无需深入了解物理数据库及其性能特征。使用 LINQ to SQL，在对象关系设计器中创建的“实体:关系”(E:R) 图表会生成随后由 Visual Studio 用作模板的类，用于自动构建语法正确的 SQL 命令。

由于使用 LINQ to SQL 时无需考虑大部分 SQL 语言编码方面的注意事项，因此 LINQ to SQL 也常常会掩盖与数据库设计、配置和优化相关的重要性能注意事项。如本例所述，使用 LINQ，您可以方便地创建联接多个表的复杂查询，而无需考虑这样做的性能影响。

使用 TIP，您可以查看 LINQ to SQL 生成的实际 SQL 命令文本，并收集这些 SQL 查询的运行时执行的度量。然后，您可以使用手头的 SQL 命令文本访问其他数据库优化工具，帮助您更好地了解任意特定 LINQ to SQL 操作对性能的影响。

本文中的示例是一个 Web 窗体应用程序，该应用程序使用特定客户 ID 查询 AdventureWorks Sales.Customer 表来检索该客户的订单历史记录。此查询中涉及的 AdventureWorks 表包括 Customer、SalesOrderHeader、SalesOrderDetail 和 StateProvince 表，如图 2 中的对象关系设计器视图所示。

图 2 查询 Sales.Customer 信息所用的 AdventureWorks 表

如果您希望随订单历史记录显示客户的邮寄地址和电子邮件地址信息，则需要访问 CustomerAddress、Address 和 Contact 表。如对象关系设计器中所示，AdventureWorks 表包含 CustomerID、SalesOrder 和 ContactID 等主键和外键，使得这些表可以按照逻辑方式联接起来。

图 3 中显示了使用 LINQ to SQL 创建 AdventureWorks 客户查询的 C# 代码。在本例中，custid 是请求的特定 CustomerID 值。此查询返回一个 customeryquery 集合，其中包含单独的一行数据，提供在 select new 子句中列出的数据字段。

图 3 LINQ to SQL 客户查询

var customerquery = 
  from customers in db.Customers
  from custaddrs in db.CustomerAddresses
  from addrs in db.Addresses
  where (customers.CustomerID == custid &&
         customers.CustomerID == custaddrs.CustomerID &&
         custaddrs.AddressID == addrs.AddressID)

  select new {
    customers.CustomerID,
    customers.CustomerType,
    addrs.AddressLine1,
    addrs.AddressLine2,
    addrs.City,
    addrs.StateProvince,
    addrs.PostalCode,
    customers.TerritoryID
  };

然后，可以将 customeryquery 绑定到 ASP.NET 网页上的控件：

DetailsView1.DataSource = customerquery;
DetailsView1.DataBind();

现在，可以创建查询以检索此客户的订单历史记录：

var orderquery = 
  from orderhdr in db.SalesOrderHeaders
  where (orderhdr.CustomerID == custid)
  orderby orderhdr.OrderDate
  select new {
    Date = orderhdr.OrderDate.ToShortDateString(),
    orderhdr.AccountNumber,
    InvoiceNo = orderhdr.SalesOrderID,
    orderhdr.TotalDue
  };

执行此 LINQ to SQL 操作时，orderquery 将包含与特定客户 ID 关联的 OrderHdr 表中每一行相对应的行。如果客户历史记录指示有多个销售交易，则 orderquery 集合将包含多行。

这些查询表面上非常简明。但是，使用 TIP 便可了解这些看上去简单的 LINQ to SQL 操作的性能影响。

使用 TIP 数据进行优化

现在，让我们进一步了解 customerquery。在运行时，LINQ to SQL 使用 LINQ 语句中隐含的逻辑数据库 SELECT 操作，并使用它生成联接以下四个 AdventureWorks 表中的数据的有效 SQL 命令：Customers、CustomerAddresses、Addresses 和静态 StateProvince 表。在此处的 LINQ to SQL 代码中看不到这一点。

在 Visual Studio 分析器下运行此代码时，TIP 检测会报告此查询执行的次数，并测量网页等待执行的延迟时间。实际上，这是在分析运行期间执行了六次的操作，如图 1 中所示。

此外，如上文所述，LINQ to SQL 代码生成的 SQL 命令在分析应用程序时也可用。图 4 显示了此操作的实际 SQL 命令。

图 4 customerquery 的 SQL 命令

SELECT [t0].[CustomerID], [t0].[CustomerType], [t2].[AddressLine1], [t2].[AddressLine2], [t2].[City], [t3].[StateProvinceID], [t3].[StateProvinceCode], [t3].[CountryRegionCode], [t3].[IsOnlyStateProvinceFlag], [t3].[Name], [t3].[TerritoryID], [t3].[rowguid], [t3].[ModifiedDate], [t2].[PostalCode], [t0].[TerritoryID] AS [TerritoryID2]
FROM [Sales].[Customer] AS [t0]
CROSS JOIN [Sales].[CustomerAddress] AS [t1]
CROSS JOIN [Person].[Address] AS [t2]
INNER JOIN [Person].[StateProvince] AS [t3] ON [t3].[StateProvinceID] = [t2].[StateProvinceID]
WHERE ([t0].[CustomerID] = @p0) AND ([t0].[CustomerID] = [t1].[CustomerID]) AND ([t1].[AddressID] = [t2].[AddressID])

请注意，SQL 命令文本中包括一个令牌（在此处指定为“@p0”），用于表示 LINQ 提供给查询中的客户 ID 参数。

现在，已经得到了由 LINQ 生成的实际 SQL 命令文本，可以了解数据库设计如何影响查询的性能。

此时可以执行的操作是在 SQL Server Management Studio 中执行此 SQL 命令，并检查其执行计划，如图 5 中所示。若要访问此查询的执行计划，需要添加命令以指向合适的数据库：

USE AdventureWorks ;
GO

接下来，从 TIP 报告中复制 SQL 命令文本，请记住使用数据库中的有效 CustomerID 替换“@p0”令牌。然后，在 SQL Server Management Studio 中执行此示例查询并访问执行计划，该执行计划显示查询优化器如何将逻辑请求转换为物理执行计划。

图 5 示例 LINQ to SQL 操作的执行计划

在本例中，查询的执行计划显示 SELECT 语句使用 CustomerID 字段上的聚集索引访问 Customer 表，该语句返回并且只返回表中的一行。在 SQL Server Management Studio 中，您可以将鼠标悬停在某个操作上以查看其属性，或者突出显示该操作并右键单击以查看“Properties”（属性）窗口。使用这种方式，您可以循环逐个查看该命令请求的其余几个操作。接下来的三个 JOIN 中，每个都会增加初始的 Customer SELECT，还将使用聚集索引访问表并返回单独一行。

通过此调查您可以看到，处理此查询总共需要访问四行，每一行来自 AdventureWorks 数据库中的一个不同表。每次访问均使用表的唯一主键有效执行。

与此类似，您可以使用 TIP 查看 orderquery 代码的 SQL 命令并将其提供给 SQL Server Management Studio 以查看其执行计划（参见图 6）。此查询使用 CustomerID 作为外键访问一个名为 OrderHdr 的表，因此需要访问 SalesOrderHeaderID 上的普通非聚集索引以及聚集索引。

图 6 orderquery 的执行计划

这个特殊的查询实例返回九行。LINQ to SQL 代码中的 orderby 子句转换为 SQL ORDER BY 子句，使得在 SELECT 的结果集上执行一个额外的排序操作。根据 SQL Server 计划优化程序的估算，此操作占用了请求总执行成本的 40%。

选择分析上下文

TIP 旨在为现有 Visual Studio 分析方法提供补充，用于收集数据层交互上的特定度量。TIP 是辅助的数据收集工具，必须指定主要分析方法才能收集数据。对于任何使用 ADO.NET 与数据层通信的应用程序，可以在采样、检测和并发分析运行期间收集 TIP 数据。

假设您需要为将收集 TIP 数据的应用程序选择一种主要分析方法，您将使用哪种方法？现在让我们了解选择主要分析方法的一些注意事项。

在性能研究中是否主要关注与数据层交互相关的延迟？如果是，则建议使用采样分析作为主要方法，因为此方法通常是干扰最少的一种分析形式。

如果在性能研究中主要关注的不是数据层延迟，请根据在当前上下文中哪种分析方法能够提供最合适的测量数据来做出选择。例如，如果要研究与多线程并发执行相关的问题，则收集并发数据。如果要研究与 CPU 密集型应用程序相关的问题，则收集采样数据。有关如何选择主要收集方法的更多指南，请参见文章“如何：选择收集方法”。

如果您尚不熟悉数据层代码，则可能需要从主要分析数据中获取帮助，以找到发起这些 ADO.NET 调用的准确代码。TIP 在收集同步 ADO.NET 进程外调用的计时信息时不捕获调用堆栈。如果需要了解在应用程序中对 ADO.NET 方法进行调用的位置，则检测分析最为有用。采样数据也会有所帮助，但其精度不如检测分析。

您可以选择同时收集资源争用数据和层交互度量，但收集争用数据相比采样往往是较高开销的函数，并且争用数据对于确定发起特定 ADO.NET 调用的位置一般没有任何帮助。对于需要 .NET 内存分配分析（通常具有很大的影响）的调查，通常也不会从收集层交互度量中获益。

采样分析

一般而言，在性能研究中数据层交互本身是主要关注对象。在本例中，选择采样作为主要分析方法通常会获得最佳结果。在本例中，由于这通常是对应用程序性能影响最小的分析方法，因此首选采样方法。在定位到发起对性能影响最大的 ADO.NET 调用的源代码时，采样分析也会非常有用。

对于在采样分析期间收集的指令执行样本，在使用在进程外运行的数据层函数时，通常不反映应用程序等待通过 ADO.NET 接口进行的同步调用完成所花费的任何时间。在应用程序的执行线程等待这些进程外调用完成期间，将阻止应用程序线程并且不对其记录任何执行样本。使用采样时，了解哪些应用程序延迟是由于对数据层的同步调用而造成的最佳方法是收集 TIP 数据。

TIP 使用的检测在收集计时数据时不捕获任何调用堆栈。因此，如果您对分层应用程序进行分析并且不完全熟悉代码，则可能难于准确确定发起数据层调用的位置。采样分析同样也有助于确定应用程序代码中对这些 ADO.NET 接口执行调用的位置。如果应用程序频繁调用 ADO.NET 接口，则很有可能会收集一些显示在 ADO.NET 模块（包括 System.Data.dll 和 System.Data.Linq.dll）中用时的样本。

在检查采样数据并将其与层交互度量进行比较时，请记住，在等待同步数据库调用完成而阻止应用程序线程时，将不收集线程的任何采样数据。样本在执行期间会累积，但不包括 TIP 明确测量的进程外延迟。不过，您可以预计在 ADO.NET 方法中收集的执行样本与 TIP 观察和测量的 ADO.NET 命令数之间存在大致的关联。在这些情况下，采样分析有助于您定位到发出 TIP 测量和报告的 ADO.NET 调用的源代码。

请注意，如果应用程序的 SQL 查询返回很大的结果集，随后这些结果集绑定到窗体上的数据绑定控件，则您会在控件的 DataBind 方法中发现非常高的执行采样数。查看采样分析中出现哪些 DataBind 方法也有助于定位到发起 ADO.NET 调用的源代码。

检测分析

收集检测分析时，检测记录的方法的计时数据已经包括了方法中等待进程外调用完成的任意用时。对于选定进行检测的应用程序方法，将测量其各个方法进入和退出的时间，以此来收集在检测分析中记录的计时数据。对于应用程序中使用 ADO.NET 调用与数据层交互的方法，其计时数据中已隐式包含了执行任何进程外调用的延迟。

从 TIP 收集的计时数据单独明确地确定和测量进程外延迟。通过层交互分析测量的延迟，应该为在检测分析运行期间测量的方法内部总用时的子集。了解这一点以后，您应该能够将层交互分析的计时数据与在检测分析的方法级别上收集的计时数据进行匹配，以查明发起数据层调用的方法。

如果使用方法级别的检测足以确定应用程序中发起任意 ADO.NET 调用的位置，则可以毫不犹豫地使用检测分析。但是，检测分析相比采样分析的干扰通常会大得多，这会带来较大的开销，并往往会生成非常大的 .vsp 收集文件。此外，如果应用程序使用多次调用 ADO.NET 函数的方法，则检测收集的数据仅能帮助您定位到方法级别，而无法区分内嵌到单个方法内部的多个 ADO.NET 调用。

获取更多数据

构建多层应用程序这种设计模式有助于提高可靠性和可伸缩性，但当应用程序组件在不同计算机上执行时会带来性能监视上的困难。

对于多层应用程序而言，一个未涵盖其相互关联的各层的简单视图不能提供完整的性能信息。正如您所看到的，TIP 可以提供其他方法所无法提供的关键计时数据。如本文中的示例建议，此计时数据与通过其他标准数据库管理工具得到的性能数据一起使用时可以发挥更大的作用。

Mark Friedman 在 Microsoft 的 Visual Studio Ultimate 团队担任架构师。他已出版了两本关于 Windows 性能的书籍，并且定期发布有关性能问题的博客文章，地址为 blogs.msdn.com/ddperf/。

衷心感谢以下技术专家对本文的审阅： Daryush Laqab 和 Chris Schmich