INF: 特殊查询性能问题的疑难解答

文章翻译 文章翻译
文章编号: 243588 - 查看本文应用于的产品
本文的发布号曾为 CHS243588
展开全部 | 关闭全部

本文内容

概要

本文介绍一种 Microsoft SQL Server 中可能遇到的特定类型的性能问题,即大量并发特殊查询的低性能问题。 如果正在解决某个性能问题,但还不能确定这是否为真正的问题所在,请在开始前参见 Microsoft Knowledge Base 中的下列文章:

224587 INF: 解决 SQL Server 中的应用程序性能问题
本文的其它内容假设您已经使用上述文章缩小了问题的范围,并且获取了 Windows NT“性能监视器”日志及 SQL Server“事件探查器”跟踪文件中的特定计数器、事件和其中详细的数据列。

本文中讨论的特定性能问题具有下列特点:
  • 通常情况下,持续时间很短的短特殊查询在大量的并发用户执行时,将导致整体系统性能降低。
  • CPU 使用率很高或达到 100%。
  • 处于低性能时无相关阻塞。

    可以通过检查 sp_who 系统存储过程输出中的 BLK 列来快速检查阻塞。 如果对于大量的系统处理 ID (SPID),BLK 列为非零,则表明正处于阻塞状态。 有关阻塞问题的详细讨论,请参见 Microsoft Knowledge Base 中的下列文章:

    224453 INF: 了解和解决 SQL Server 7.0 阻塞问题
  • 在某些情况下,会加大服务器内存分配的压力并将报告以下信息:

    错误: 701,严重度: 17,状态: 1
    运行此查询的系统内存不足。
    -或-
    Msg 8645、级别 17、状态 1、过程、行 1
    等待执行查询所用的内存资源时发生超时。 请重新运行该查询。
    下述各种情况不一定均会出现上述症状。

更多信息

SQL Server 的系统结构(尤其是查询优化器)从 SQL Server 7.0 开始便有了改进,与以前版本的 SQL Server 相比,应用程序在系统资源的使用上有很大差异。 具体而言,SQL Server 7.0 可能会在 CPU 或内存的使用上有所增加,而以前版本的 SQL Server 通常为磁盘 IO 相关。 这些变化可以归结为两个因素: 哈希联接与合并联接,以及查询编译时间。

以前版本的 SQL Server 仅依赖于嵌套循环迭代来执行联接。 嵌套循环联接本身使用磁盘 IO。 从 SQL Server 7.0 开始,除嵌套循环联接外,还引入了哈希和合并联接。 与嵌套循环联接相比,哈希与合并联接做更多的内存操作。 当使用这些联接技术时,其理论上的结果是 CPU 和内存的使用率要变高。 有关哈希和合并联接的详细信息,请参见 SQL Server 7.0 Books Online 中的 Understanding Hash Joins 和 Understanding Merge Joins 主题。

由于查询优化器比以前版本有更多的选项和信息,查询编译时间将因此受影响: 新的哈希和合并联接技术、改进的搜索算法及列统计。 这些附加信息使得优化器有更多的机会来选择最为有效的计划检索查询数据。 然而,分析与考虑这些新技术和信息需要占用处理时间。 这种增加的 CPU 使用率将导致查询编译时间要长于以前版本。

对于大多数查询来说,编译时间的增加可以通过降低执行时间来补偿。 整体效果是查询运行速度比以前的版本更快。 然而,有一种情况例外:即小而简单的 OLTP 类型查询只需要很短的执行时间。 对于这些情况,产生查询计划的处理过程可能比执行查询要花费更多的时间或相同的时间。 结果,查询将比以前版本执行得稍微慢一些。 由于这种差别通常在毫秒级内,对于某个特定的查询来说,如果其单独执行,便不会发现这种小的差别。 然而,如果许多用户同时执行大量特殊查询,总的系统 CPU 使用率比以前版本的 SQL Server 要高。 SQL Server 7.0 采用一些新技术来减少这种影响,如高速缓冲特殊查询及自动参数化。 然而,可用于自动参数化查询的选择是有限的。 应用程序开发人员可采用下列方法来保证所开发的查询计划参数化并可更有效地重复使用。
  • 参数标记
    当提交查询时,OLE-DB 和 ODBC API 都允许用问号指定参数。 这对于任何应用程序都很有帮助,特别是对查询生成模块的中层应用程序,中层应用程序中不能使用存储过程。 即使指定了不同的参数,用参数标记执行的查询生成的查询计划也可以由任何以后的客户端使用。 有关详细信息,请参见 SQL Server 7.0 Books Online 中的“Parameter Markers”主题。
  • sp_executesql
    当在应用程序中使用参数标记时,OLE DB 提供程序或 ODBC 驱动程序将调用 sp_executesql 存储过程。 然而,应用程序也可直接调用该系统过程或在另一个存储过程中进行调用,以明确用参数表示特殊查询。 在 EXECUTE 语句用于执行动态 SQL 语句的应用程序或批处理文件中,这一点很有用。 与 sp_executesql 不同,EXECUTE 语句不允许进行参数化,这限制了重复使用查询计划。 有关详细信息,请参见下面的讨论以及 SQL Server 7.0 Books Online 中的 sp_executesql (T-SQL) 和 Using sp_executesql 主题。
  • 存储过程
    存储过程有很多优点,其中包括参数化查询以及重复使用执行计划。 有关详细信息,请参见 SQL Server 7.0 Books Online 中的 Stored Procedures 和 Programming Stored Procedures。

识别并解决这些性能问题



如果还没有这么做,请参见 Microsoft Knowledge Base 中有关捕获 Windows NT 性能监视器和 SQL Server 事件探查器数据以协助分析系统性能的下列文章:

224587 INF: 解决 SQL Server 中的应用程序性能问题

查看性能监视器数据



使用“性能监视器”日志来确定有瓶颈的系统资源。 这可以帮助您了解整个系统情况,并将精力集中于查看“事件探查器”数据。 性能良好时查看“性能监视器”数据,随着性能降低查看计数器的趋势。 哪个计数器最先变差呢? 了解此信息有助于确定出现的情况与下列哪个问题有关。 在下面讨论的问题中应考虑如下计数器:

  • 对象: Process
    计数器: Processor
    实例: SQL Server
  • 对象: Processor
    计数器: %Processor Time
    实例: Check each processor instance
  • 对象: SQL Server:Buffer Manager
    计数器: Free Buffers
  • 对象: SQL Server:Buffer Manager
    计数器: Stolen Page Count
  • 对象: SQL Server:Memory Manager
    计数器: Memory Grants Pending
  • 对象: SQL Server:SQL Statistics
    计数器: SQL Compilations/sec

如果 CPU 使用率、SQL Compilations/sec 和 Free Buffers 很高,而 Memory Grants Pending 及 Stolen Page Count 很低,则表明 CPU 处于瓶颈状态。 根据下列步骤来有效地参数化和重新使用查询计划,以避免重新生成查询计划的开销(请参见本文的“通过‘事件类’对‘事件探查器’跟踪数据进行分类”)。 如果 Free Buffers 和 SQL Compilations/sec 很低,而 Stolen Page Count 和 Memory Grants Pending 很高,则表明 SQL Server 内存有限。 集中查找使用哈希联接并可更改为循环联接的查询(请参见本章的“通过‘持续时间’对‘事件探查器’跟踪数据进行分类”)。

有关上述计数器的详细信息,请查找 SQL Server 7.0 Books Online 中的计数器名称。

查看“事件探查器”数据



在解决性能问题时有效地查看“事件探查器”数据是非常有用的。 最重要的是您无需查看每个捕获到的数据,而只是有选择地查看。 “事件探查器”可以帮助高效您查看捕获的数据。 在“属性”选项卡(在文件菜单上,单击属性可得到)上,“事件探查器”允许您通过删除数据列或事件、分组(排序)数据列及使用筛选来限制显示的数据。 在编辑菜单上单击查找,您可以检索整个跟踪或某些特定值的特定列。 也可以在文件菜单上,指向另存为,然后单击跟踪表,将“事件探查器”数据保存到某个 SQL 表中,并对其运行 SQL 查询。

备注:确保您只在以前已保存的跟踪文件的基础上执行筛选。 如果在某个活动跟踪上执行这些步骤,由于已经启动跟踪,将会有丢失已捕获数据的危险。 在文件菜单上,单击另存为,首先将活动跟踪保存至某个文件或表,然后在执行前重新打开此跟踪(在文件菜单上单击打开)。 当处理一个已经保存过的跟踪文件时,筛选并不会永久删除筛选出的数据,而只是不显示这些数据。 您可以根据需要通过添加和删除事件及数据列来帮助进行检索。

另一个重要的问题是要将精力集中于获益最多的地方。 下面所列的因素能将有助于提高应用程序性能,但在程度上并不一定相同。 在开始实施更改前,检查下列所有项以确定其提高性能的潜力。 任何更改所带来的影响主要依赖于两个因素:
  • 执行有问题查询的频率。
  • 查询效率的提高程度。
如果在一天中并不经常执行某查询,而只将其执行时间减少 1.5 至 1.2 秒,效果并不明显。 然而,如果大量并发用户经常执行该查询,则性能的提高是非常显著的。 相反,如果某个单个查询不经常使用,而将其执行时间减少 6 分钟至 3 秒,则在整体性能上并不会有很明显的提高。 在执行下面讨论的补救措施之前,使用“事件探查器”中的分组和筛选技术来估计某个特定查询或过程的影响。 首先集中于影响最显著的更改,然后迭代估计其它查询及过程,直到性能上有足够的提高为止。

如果您还没有捕获“事件探查器”跟踪数据,请参见 Microsoft Knowledge Base 中的下列文章,了解如何使用适当的事件和数据列创建一个跟踪:

224587 INF: 解决 SQL Server 中的应用程序性能问题
将“事件探查器”跟踪数据保存至文件或表中后,在“事件探查器”中将其重新打开,并开始检查其内容。
  • 根据持续时间对“事件探查器”跟踪数据进行分类:

    1. 文件菜单上,单击属性
    2. 数据列选项卡上,使用向上按钮移动标题下的持续时间。 使用向下按钮删除标题下的所有其它列。
    3. 事件选项卡上,删除除 TSQL SQL:StmtCompletedTSQL RPC:Completed 之外的所有其它事件。 这使您只集中于要执行的查询。
    4. 单击确定
    根据持续时间进行分组,您可以很容易看到哪些运行效率最低的 SQL 语句、批处理或过程。 有一点很重要,即不仅要看到问题何时发生,而且要在性能良好时创建一个基线。 您可以根据启动时间进行筛选,以便将跟踪数据分为多个部分,将性能良好时分为一个部分,而将性能降低时分为另一个独立的部分。 查找性能良好时持续时间最长的查询。 这最有可能是问题的根源所在。 当整个系统性能降低时,即使是好的查询在等待系统资源时也会显示很长的持续时间。

    查看执行计划查找经常出现长持续时间的查询。 如果使用了哈希联接,请考虑使用 LOOP JOIN 查询提示强迫查询使用嵌套循环联接。 如果使用循环联接的查询的执行时间小于等于或者稍微大于哈希联接的执行时间,则当计算机具有高内存及 CPU 使用率时,选用循环联接可能会更好。 通过减少资源瓶颈(CPU 和/或内存)的压力,可以提高整个系统性能。 有关 LOOP JOIN 提示的详细信息,请参见 SQL Server 7.0 Books Online 中的 SELECT (T-SQL) 主题。

    如果您发现某个查询具有不符合常规的长持续时间,请参见 Microsoft Knowledge Base 中的下列文章:

    243589 INF: 解决 SQL Server 7.0 上的低速运行的查询问题
  • 根据“事件类”对“事件探查器”跟踪数据进行分类:

    1. 文件菜单上,单击属性
    2. 数据列选项卡上,使用向上按钮移动标题(上面有事件类)下的事件类文本。 使用向下按钮删除标题下的所有其它列。
    3. 事件选项卡上,确保包含了所有的事件。
    4. 单击确定

    根据事件类列进行分组显示出发生于 SQL Server 上的事件的类型及频率。 在此列中检索下列事件:

    • MISC: Prepare SQL and Exec Prepared SQL; CURSORS: CursorPrepare

      Prepare SQL 事件表明一个默认结果集(客户端游标)被用来准备执行一条使用 SQLPrepare/SQLExecute (对于 ODBC)或 ICommandText::Prepare/ICommandText::Execute (对于 OLE DB)以默认的游标选项(只进、只读、行集大小 = 1)提交的 SQL 语句。Cursorprepare 事件表明一个服务器端游标被用来准备执行一条使用 SQLPrepare/SQLExecute (对于 ODBC) 或 ICommandText::Prepare/ICommandText::Execute (对于 OLE DB)以非上述默认的游标选项提交的 SQL 语句。 Exec Prepared SQL 事件表明以上述方式准备执行的语句被执行了。 如果您发现经常出现这些事件,则您的应用程序在打开结果集时使用了“准备/执行”模型。 若如此,则下一个问题是:

      您正在正确使用准备/执行模型吗?

      理想情况下,应用程序应一次准备一个 SQL 语句而执行多次。 这样,每次执行语句时,便节省了优化器编译新计划的时间。 每次执行准备好的语句时,您也节省了查询编译的时间。 如果一次计划只执行一个查询,则不必准备 SQL 语句。 准备一个 SQL 语句然后执行需要三个网络通信来回: 准备语句、执行语句、撤消语句。 准备服务器端游标至少需要 5 个阶段: 准备游标、执行(打开)游标、从游标中提取数据行(一步或多步)、关闭游标、撤消游标。 简单执行查询只需要一个网络通信来回。 如果不重用该语句,则准备执行的额外开销并无益处。

      为了解应用程序如何很好地使用准备/执行模型,比较准备事件及执行事件发生的次数。 Exec Prepared SQL 事件的次数应大于 Prepare SQL + CursorPrepare 事件的总次数(至少为 3 到 5 倍为好)。 这表明很好地重复使用准备好的语句可克服创建它们的负担。 如果 Prepare SQL + CursorPrepare 事件的数量大约等于 Exec Prepared SQL 事件的数量,则表明应用程序没有很好地使用准备/执行模型。 您不应准备一个将只执行一次的语句。 相反,应一次准备某个语句,然后尽可能重复使用。 如果必要的话,更改你的应用程序,通过一次准备语句并尽可能的重用该语句来更好的应用准备/执行模型。

      注意:应特意编写应用程序以使其有效地使用准备/执行模型。 一个准备好语句的句柄的生存周期是由 ODBC 中使 HSTMT (或 OLE DB 中使 ICommandText 对象)打开的时间长短控制的。 通常做法是:得到 HSTMT、准备 SQL 语句、执行准备好的语句,然后释放 HSTMT,因而也丢失了准备计划的句柄。 如果这样,您没有利用准备/执行模型的任何优点。 事实上,由于前面所述的网络阶段的额外开销,性能反而会降低。 应用程序需要通过某种方法,来缓冲具有已准备好语句句柄的对象或 HSTMT 并访问它们以便重复使用。 驱动程序或提供程序并不会自动完成此任务,需要应用程序来实现、维护并使用该信息。 如果应用程序不能完成此任务,可考虑使用参数标记而非准备及执行来完成。 使用参数标记

      使用参数标记,应用程序能最优化使用不同的输入输出值多次执行同一 Transact-SQL 语句的情况。 第一次执行某个查询时,它是一个参数化查询,SQL Server 为查询产生并缓冲参数化计划。 对于随后使用同一或不同参数调用同一查询,SQL Server 无需产生新的查询计划,它可以通过在当前参数中替换来重复使用现有的计划。 这样,每次重复使用现有查询时,便节省了产生查询计划的时间。

      当在调用 SQLExecDirect (ODBC) 或 ICommandText::Execute (OLE DB)中使用参数标记时,驱动程序或提供程序将自动将 SQL 语句打包,并作为 sp_executesql 调用执行。 无需特地单独准备和执行该语句。 当 SQL Server 接收到对 sp_executesql 的调用时,它自动检查现有匹配计划的过程缓冲,在可能情况下重复使用该计划。如果没发现计划,将创建一个新计划。

      为确定应用程序目前是否使用参数标记,您可以在“事件探查器”跟踪的文本列中检索 sp_executesql。 然而,由于 sp_executesql 可能被直接调用,并非所有的实例指明参数标记的使用情况。

      有关准备/执行模型的详细信息,请参见 SQL Server 7.0 Books Online 中的 Execution Plan Caching and Reuse 主题。 有关参数标记的详细信息,请参见 SQL Server 7.0 Books Online 中的 Parameter Markers 主题。
    • SP:Completed

      使用 EXECUTE 命令执行的动态 SQL 语句将作为具有动态 SQL 文本的 SP:Completed 事件显示。 展开 SP:Completed 事件并检索所有具有动态“SQL 文本”的位置。 如果具有大量的这类事件,则可以通过使用 sp_executesql 替代 EXECUTE 语句来提高应用程序性能。 如果以不同的参数重新执行相同的查询,则 sp_executesql 将允许 SQL Server 重复使用执行计划。 如果使用 EXECUTE 语句,则计划没有参数化,所以只有以完全相同的参数重新执行查询时,才能重复使用该计划。

      为确定通过 EXECUTE 语句使用动态 SQL 的查询或过程,注意事件出现的 ConnectionID 和 Start Time。 然后从标题中删除事件类文本来取消对跟踪数据的分组。 这将返回按时间顺序排列的跟踪数据。 您可以在特定 Connection ID 上的筛选选项卡上筛选跟踪,删除除 SP:StartingSP:Complete 以外的所有“事件类”,以使语句易读。 然后在前面的 Start Time 的编辑菜单上,单击查找进行检索。 这将准确显示动态 SQL 事件开始的位置。 如果其位于存储过程中,则事件将在过程的 SP:StartingSP:Completed 事件之间发生。 否则,其作为特殊查询执行,您可以使用数据列(如:应用程序名NT 用户名 等)来确定命令执行的位置。 也可以重新添加“事件类”(如:SQL:BatchCompletedSQL:RPCCompleted)来确定命令的文本及其执行的上下文。

      确定执行 EXECUTE 语句的位置后,考虑用一个对 sp_executesql 的调用来替换这个语句。 例如,考虑下列 EXECUTE 命令与动态 SQL 一起使用的情况。 某个过程以表名、ID 及 idValue 作为输入参数,并从基于 ID 值的表执行一个 SELECT 语句。 使用 EXECUTE 语句的过程如下所示:
      drop proc dynamicUsingEXECUTE
      go
      create proc dynamicUsingEXECUTE
      @table sysname,
      @idName varchar(10),
      @idValue varchar(10)
      as
      declare @query nvarchar(4000)
      -- Build query string with parameter.
      -- Notice the use of escape quotes.
      select @query = 'select * from ' + @table + ' where ' + @idName + ' = ''' + @idValue + ''''
      exec (@query)
      go
      假设该查询不是自动参数化的,如果您以不同的 @idValue 参数值在 pubs 示例数据库的 titles 表中执行该过程,则 QL Server 需要为每个执行过程产生一个独立的查询。
      exec dynamicUsingEXECUTE 'titles', 'title_id', 'MC2222'
      go
      exec dynamicUsingEXECUTE 'titles', 'title_id', 'BU7832'
      备注: 在这种情况下,查询相当简单,SQL Server 可以将该查询自动参数化并真正重新使用该执行计划。 然而,如果是 SQL Server 不能自动参数化的复杂查询,当 @idValue 参数变化时,SQL Server 将不能重复使用计划。 该简单查询仅用来限制示例的复杂性。

      您可以重新编写该过程以使用 sp_executesql 来替代 EXECUTE 语句。 对参数替换的支持使 sp_executesql 更为有效,因为它产生更可能由 SQL Server 重复使用的执行计划。
      drop proc dynamicUsingSP_EXECUTESQL
      go
      create proc dynamicUsingSP_EXECUTESQL
      @table sysname, @idName varchar(10), @idValue varchar(10)
      as
      declare @query nvarchar(4000)
      -- Build query string with parameter
      select @query = 'select * from ' + @table + ' where ' + @idName + ' = ''' + @idValue + ''''
      -- Now execute with parameter
      exec sp_executesql @query,
      @idValue varchar(10)
      @idValue
      go
      
      exec dynamicUsingEXECUTE 'titles', 'title_id', 'MC2222'
      go
      exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id', 'BU7832'
      在这种情况下,第一次执行 sp_executesql 语句,SQL Server 为从 titles (以 title_id 作为参数)中的 SELECT 产生一个参数化计划。 第二次执行时,它仅以新的参数值重复使用该计划。

      有关 sp_executesql 的详细信息,请参见 SQL Server 7.0 Books Online 中的 sp_executesql (T-SQL) 和 Using sp_executesql。
    • SP:RECOMPILES

      该事件指明某个存储过程在执行过程中被重新编译。 大量重新编译事件表明 SQL Server 是在查询编译上(而不是在查询执行上)花费资源。 有关解决存储过程重新编译问题的详细信息,请参见 Microsoft Knowledge Base 中的下列文章:

      243586 INF: 存储过程重新编译的疑难解答
    • 非上述任何事件

      如果您未看到上述任何事件,则应用程序只在 SQL Server 上执行了特殊查询。 除非 SQL Server 确定其能够自动参数化某些查询,或者重复使用相同的参数,否则每个所执行的查询均需要产生一个新的执行计划。 “性能监视器”应显示大量的 SQL Compilations/sec。如上所述,对大量并发用户来说,需要大量占用 CPU 资源。 为解决此问题,请查找执行频率最高的查询,并为这些查询创建存储过程,或者使用参数标记或 sp_executesql

参考资源



有关 SQL Server 7.0 中查询性能问题的其它信息,请查阅 SQL Server 7.0: 查询性能的疑难诊断器,地址是 http://support.microsoft.com/?scid=ph;en-us;2862

有关使用“事件探查器”的详细信息,请参见 SQL Server 7.0 Books Online。

属性

文章编号: 243588 - 最后修改: 2006年7月6日 - 修订: 3.1
这篇文章中的信息适用于:
  • Microsoft SQL Server 7.0 标准版
  • Microsoft SQL Server 2000 标准版
关键字:?
kbhowto kbinfo KB243588
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。

提供反馈

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com