查询优化建议

某些查询占用的资源比其他查询占用的资源多。例如，返回大型结果集的查询和那些包含 WHERE 子句（并非唯一子句）的查询总是占用大量资源。与不太复杂的查询相比，查询优化器的智能水平无法消除这些构造的资源开销。SQL Server 使用最佳访问计划，但查询优化会受到可访问内容的限制。

尽管如此，您可以执行下列操作来提高查询性能：

• 添加更多内存。当服务器运行许多复杂查询且其中几个查询执行很慢时，此解决方案尤其有用。
  
  
• 使用多个处理器。多个处理器允许数据库引擎使用并行查询。有关详细信息，请参阅。
  
  
• 重写查询。请注意下列事项：
  
  
  • 如果查询使用游标，则确定是否可以使用效率更高的游标类型（如快速只进游标）或单个查询编写游标查询。单个查询的性能通常优于游标操作。因为一组游标语句通常是一个外循环操作，在此操作中，一旦使用内部语句便开始处理外循环中的每一行，所以可考虑使用 GROUP BY 或 CASE 语句，或者使用子查询来替代。有关详细信息，请参阅和。
    
    
  • 如果应用程序使用循环，可考虑将循环放入查询内。应用程序经常包含带参数化查询的循环，该循环执行许多次并要求运行应用程序的计算机与 SQL Server 之间有网络往返。可改用临时表创建一个更复杂的单一查询。只需要一个网络往返，查询优化器就可以更好地优化该单个查询。有关详细信息，请参阅 和 。
    
    
  • 不要在同一查询中为一个表使用多个别名来模拟索引交集。这已没有必要，因为 SQL Server 会自动考虑索引交集并可以在同一查询中对同一个表使用多个索引。请参阅以下示例查询：
    
    

    	复制代码
    SELECT * FROM lineitem WHERE partkey BETWEEN 17000 AND 17100 AND shipdate BETWEEN '1/1/1994' AND '1/31/1994'

    SQL Server 可以为 partkey 和 shipdate 列使用索引，然后在两个子集之间执行哈希匹配来获取索引交集。
    
    
  • 查询参数化用于允许重复使用缓存的查询执行计划。如果一组查询具有相同的查询哈希和查询计划哈希，则可以通过创建一个参数化查询来提高性能。如果调用具有参数的一个查询，而不是调用具有文字值的多个查询，则会允许重用缓存查询执行计划。有关详细信息，请参阅和。
    
    如果无法修改应用程序，则可以使用带有强制参数化的模板计划指南来获得类似结果。有关详细信息，请参阅。
    
    
  • 只在必要时使用查询提示。如果查询使用在早期版本的 SQL Server 上执行的提示，则应在不指定提示的情况下对其进行测试。提示会阻碍查询优化器选择更好的执行计划。有关详细信息，请参阅 。
    
    
• 使用 query_plan_hash 可捕获、存储和比较一段时间内的查询的查询执行计划。例如，更改系统配置之后，可以将任务关键查询的查询计划哈希值与其原始查询计划哈希值进行比较。通过查询计划哈希值的不同可以了解系统配置更改是否会导致更新重要查询的查询执行计划。如果 sys.dm_exec_requests 中当前长时间运行的查询的查询计划哈希与其基准查询计划哈希（通常认为该查询具有良好的性能）不同，则也可以决定停止执行该查询。有关详细信息，请参阅。
  
  
• 使用 query governor 配置选项。query governor 配置选项可用于防止长时间执行的查询占用系统资源。默认情况下，该选项设置为允许执行所有查询，而不管它们需要多长时间。但是，您还可以设置查询调控器来限制允许所有查询执行所有连接所用的最大秒数，或者仅限制查询执行一个特定连接的时间。因为查询调控器以估计的查询开销而不是实际的占用时间为基础，所以它没有任何运行时开销。它还会在长时间执行的查询开始之前将其停止，而不是将它们运行到某个预定义的限制时间。有关详细信息，请参阅 和 。
  
  
• 通过计划缓存优化查询计划的重新使用。数据库引擎对查询计划进行缓存以备重新使用。如果查询计划不进行缓存，则永远不能重新使用。然而，每次执行未缓存的查询计划时，必须对其进行编译，这就导致性能降低。下列 Transact-SQL SET 语句选项可阻止重新使用已缓存的查询计划。包含这些处于 ON 状态的 SET 选项的 Transact-SQL 批处理无法与 SET 选项处于 OFF 状态时所编译的相同批处理共享其查询计划：
  
  

  SET ANSI_NULL_DFLT_OFF	SET ANSI_NULL_DFLT_ON
  SET ANSI_NULLS	SET ANSI_PADDING
  SET ANSI_WARNINGS	SET ARITHABORT
  SET CONCAT_NULL_YIELDS_NULL	SET DATEFIRST
  SET DATEFORMAT	SET FORCEPLAN
  SET LANGUAGE	SET NO_BROWSETABLE
  SET NUMERIC_ROUNDABORT	SET QUOTED_IDENTIFIER
  SET TEXTSIZE

  此外，由于 SET ANSI_DEFAULTS 选项可用于更改 ANSI_NULLS、ANSI_NULL_DFLT_ON、ANSI_PADDING、ANSI_WARNINGS、CURSOR_CLOSE_ON_COMMIT、IMPLICIT_TRANSACTIONS 和 QUOTED_IDENTIFIER SET 选项，因此该选项会影响已缓存查询计划的重新使用。请注意，SET ANSI_DEFAULTS 可以更改的大部分 SET 选项都列为可能会影响查询计划重新使用的 SET 选项。
  
  可以使用下列方法更改其中某些 SET 选项：
  
  
  • 使用 sp_configure 存储过程进行服务器范围的更改。有关详细信息，请参阅 。
    
    
  • 使用 ALTER DATABASE 语句的 SET 子句。有关详细信息，请参阅 。
    
    
  • 更改 OLE DB 和 ODBC 连接设置。有关详细信息，请参阅。
    
    

注意：

只有在连接时建立 SET 选项并确保它们在连接期间不发生变化，才能避免 SET 选项出现重新编译查询计划的情况。某些 SET 选项必须设置为特定值，才能使用索引视图或计算列的索引。有关详细信息，请参阅。