分析执行缓慢查询清单

来源：互联网发布：php system 返回值 1 编辑：程序博客网时间：2024/04/30 08:44

SQL Server 2008 R2的 Other Versions 其他版本 SQL Server 2008 SQL Server 2008中 SQL Server 2005 SQL Server 2005中

Queries or updates that take longer than expected to execute can be caused by a variety of reasons.可以由多种原因引起的，需要更长的时间比预期的要执行的查询或更新。 Slow-running queries can be caused by performance problems related to your network or the computer where SQL Server is running.缓慢运行的查询可能会造成您的网络或计算机运行SQL Server相关的性能问题。 Slow-running queries can also be caused by problems with your physical database design.运行慢的查询，也可以造成的物理数据库设计的问题。

There are a number of common reasons for slow-running queries and updates:运行慢的查询和更新的常见原因有很多：

Slow network communication.速度较慢的网络通信。

Inadequate memory in the server computer, or not enough memory available for SQL Server.在服务器计算机上，或没有足够的内存可用于SQL Server的内存不足。

Lack of useful statistics缺少有用的统计信息

Lack of useful indexes.缺少有用的索引。

Lack of useful indexed views.缺少有用的索引视图。

Lack of useful data striping.缺乏有用的数据条带化。

Lack of useful partitioning.缺少有用的分区。

When a query or update takes longer than expected, ask yourself the following questions, which address the reasons for slow-running queries that are listed in the previous section:当查询或更新需要比预期更长的时间，问问自己以下问题，解决运行慢，在上一节中列出的查询的原因：

Tip 提示
To save time, consult this checklist before you contact your technical support provider.为了节省时间，请参阅此清单之前，您的技术支持提供商联系。

1.Is the performance problem related to a component other than queries?查询其他相关组件的性能问题？ For example, is the problem slow network performance?举例来说，网络性能缓慢的问题？ Are there any other components that might be causing or contributing to performance degradation?是否有任何其他组件，可能会引起或导致性能下降？

The Windows System Monitor can be used to monitor the performance of SQL Server and non-SQL Server related components.可以使用Windows系统监视器来监视SQL Server和非SQL Server相关的组件的性能。 For more information, see Monitoring Resource Usage (System Monitor) .欲了解更多信息，请参阅监视资源使用情况（系统监视器）。

2.If the performance issue is related to queries, which query or set of queries is involved?如果性能问题是相关的查询，查询或查询涉及？

Use SQL Server Profiler to help identify the slow query or queries.使用SQL Server Profiler，以帮助识别缓慢的查询或查询。 For more information, see Using SQL Server Profiler .欲了解更多信息，请参阅使用SQL Server Profiler 。 Use the sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views to find similar queries that collectively consume a large number of resources.使用sys.dm_exec_query_stats和sys.dm_exec_requests动态管理的意见，以找到共同消耗了大量的资源的类似的查询。 For more information, see Finding and Tuning Similar Queries by Using Query and Query Plan Hashes .有关详细信息，请参阅使用查询和查询计划哈希值查找和优化类似查询。

3.How do I analyze the performance of a slow-running query?我如何分析一个缓慢运行的查询的性能呢？

After you have identified the slow-running query or queries, you can further analyze query performance by producing a Showplan, which can be a text, XML, or graphical representation of the query execution plan that the query optimizer generates.当你已经确定的缓慢运行的查询或查询，您可以进一步分析产生Showplan运算的查询性能，这可以是文本，XML，或查询的执行计划，查询优化器生成的图形表示。 You can produce a Showplan using Transact-SQL SET options, SQL Server Management Studio, or SQL Server Profiler.您可以使用Transact - SQL SET选项产生Showplan运算，SQL Server Management Studio中，或SQL Server Profiler中。

For information about using Transact-SQL SET options to display text and XML execution plans, see Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL) .有关使用Transact - SQL SET选项来显示文本和XML的执行计划的信息，请参阅通过使用Showplan SET选项（Transact - SQL中）来显示执行计划。

For information about using SQL Server Management Studio to display graphical execution plans, see Displaying Graphical Execution Plans (SQL Server Management Studio) .有关使用SQL Server Management Studio中显示图形执行计划的信息，请参阅显示图形执行计划（SQL Server Management Studio中）。

For information about using SQL Server Profiler to display text and XML execution plans, see Displaying Execution Plans by Using SQL Server Profiler Event Classes .有关使用SQL Server Profiler显示文本和XML的执行计划的信息，请参阅使用SQL Server Profiler事件类别来显示执行计划。

The information gathered by these tools allows you to determine how a query is executed by the SQL Server query optimizer and which indexes are being used.这些工具收集的信息，让您决定如何执行一个查询SQL Server查询优化和正在使用的索引。 Using this information, you can determine if performance improvements can be made by rewriting the query, changing the indexes on the tables, or perhaps modifying the database design.利用这些信息，你能确定性能改进，可以重写查询，更改表上的索引，或者修改数据库的设计。 For more information, see Analyzing a Query .欲了解更多信息，请参阅分析查询。

4.Was the query optimized with useful statistics?是有用的统计数据优化查询？

The query optimizer uses statistics to create query plans that improve query performance.查询优化器使用的统计信息来创建提高查询性能的查询计划。 For most queries, the query optimizer already generates the necessary statistics for a high-quality query plan; in a few cases, you need to create additional statistics or modify the query design for best results.对于大多数查询，查询优化器已生成一个高质量的查询计划的必要的统计，在少数情况下，你需要创建额外的统计数据，或修改查询设计，以取得最佳效果。

For more information, see Using Statistics to Improve Query Performance .欲了解更多信息，请参阅使用统计信息提高查询性能。 This topic gives guidelines to improve the effectiveness of statistics for query performance.本主题提供指引，以提高数据查询性能的有效性。 The guidelines include the following:该指引包括以下内容：

Using the Database-Wide Statistics Options.使用数据库范围的统计选项。 For example, you should verify that the automatic create statistics, AUTO_CREATE_STATISTICS, and automatic update statistics, AUTO_UPDATE_STATISTICS, database-wide options are on.例如，你应该确认自动创建统计信息，AUTO_CREATE_STATISTICS和自动更新统计，AUTO_UPDATE_STATISTICS数据库范围的选项。 If they are off, query plans can be suboptimal and query performance can degrade.如果他们关闭，可以查询计划不理想，并可以降低查询性能。

Determining When to Create Statistics.确定何时创建统计。 In a few cases, you can improve query plans by creating additional statistics with the CREATE STATISTICS (Transact-SQL) statement.在少数情况下，可以提高查询计划通过创建附加的统计与CREATE STATISTICS（Transact - SQL中）语句。 These additional statistics can capture statistical correlations that the query optimizer does not account for when it creates statistics for indexes or single columns.这些额外的统计数字可以捕捉时，它会创建索引或单个列的统计，查询优化器不考虑的统计相关性。

Determining When to Update Statistics.确定何时更新统计信息。 In some cases you can improve the query plan and therefore improve query performance by updating statistics more frequently than when AUTO_UPDATE_STATISTICS is on.在某些情况下，你可以提高查询的计划，并因此提高查询性能比当AUTO_UPDATE_STATISTICS的是更频繁地更新统计。 You can update statistics with the UPDATE STATISTICS statement or the stored procedure sp_updatestats .您可以更新与统计的UPDATE STATISTICS语句或存储过程sp_updatestats 。

Designing Queries That Use Statistics Effectively.设计有效使用情况统计的查询。 Certain query implementations, such as local variables and complex expressions in the query predicate, can lead to suboptimal query plans.某些查询的实现，如查询谓词中的局部变量和复杂的表达式，可以导致不理想的查询计划。 Following query design guidelines for using statistics effectively can help to avoid this.经过使用统计资料的查询设计准则可以有效地避免这种情况。

5.Are suitable indexes available?是否有合适的索引？ Would adding one or more indexes improve query performance?添加一个或多个索引提高查询性能？ For more information, see General Index Design Guidelines , Finding Missing Indexes , and Database Engine Tuning Advisor Overview .欲了解更多信息，请参阅常规索引设计指南，寻找失踪的索引，数据库引擎优化顾问概述。 Database Engine Tuning Advisor can also recommend the creation of necessary statistics.数据库引擎优化顾问还建议建立必要的统计数据。

6.Are there any data or index hot spots?是否有任何数据或索引热点？ Consider using disk striping.考虑使用磁盘条带化。 Disk striping can be implemented by using RAID (redundant array of independent disks) level 0, where data is distributed across multiple disk drives.使用RAID（独立磁盘冗余阵列）级别为0，数据分布在多个磁盘驱动器，可以实现磁盘条带化。 For more information, see Using Files and Filegroups and RAID .有关详细信息，请参阅使用文件和文件组和RAID 。

7.Is the query optimizer provided with the best opportunity to optimize a complex query?查询优化与优化复杂查询提供了最好的机会吗？ For more information, see Query Tuning Recommendations .欲了解更多信息，请参阅查询优化建议。

8.If you have a large volume of data, do you need to partition it?如果你有大量的数据，你需要分区，？ Data manageability is the main benefit of partitioning, but if your tables and indexes on them are partitioned similarly, partitioning can also improve query performance.数据管理是分区的主要好处，但同样如果你的表和索引分区，分区还可以提高查询性能。 For more information, see Understanding Partitioning and Tuning the Physical Database Design .有关详细信息，请参阅了解分区和优化物理数据库设计。