找出 SQL 查询中最慢的块的最佳方法是什么？
我面临一个问题，运行存储过程占用太多资源，有时会导致服务器超时（特别是当 CPU 使用率超过 90% 时）。
任何人都可以建议最好和最快的方法是什么来发现占用大量资源的块，并提出解决它的好方法吗？
- 我正在使用 SQL Server 2005
I am facing a problem that running a stored procedure is taking too much resources which sometimes causes a time out on the server (especially when the CPU usage is more than 90%).
Can anyone suggest what the best and quickest way is to spot the block which takes much resources, and also suggest a good way to solve it, please?
- I am using SQL server 2005
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您想要使用查询分析器。 此处说明。 这将向您显示查询执行路径的图形表示，以及其中哪些部分花费最多时间。
You want to use the Query profiler. Explained here. Which will show you a graphical representation of your queries execution path, as well as which parts of it are taking the most time.
如果您想知道哪个块最慢，请使用以下命令
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
当您运行 SP 时，这将显示每个查询的统计信息。
If you want to know which block is slowest, use the following
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
When you run the SP this will display stats for each query.
如果您使用的是SQl Server Management studio，则可以打开执行计划来显示有关sql server将如何执行查询的信息，包括每个子进程将占用整个进程的百分比。
通常，在执行此操作时，查询的一部分显然会使用大部分资源。
使用此信息，您可以就如何调整数据库做出明智的决定。 （比如向有问题的表添加索引）
If you are using the SQl Server Management studio, you can turn on the execution plan to display information about how the query will be executed by sql server including what percentage of the entire process will be taken up by each sub-process.
often when doing this, there will be a part of the query that is obviously using most of the resources.
using this informationm you can then make an informed decision about how to tune the database. (like adding an index to the offending table(s))
您不需要使用 SQL Profiler 来查看执行计划 - 只需：
You don't need to use SQL Profiler to view an execution plan - just:
如果存储过程中有一堆语句，则打开 SET STATISTICS 选项可能会有点复杂，因为您有许多输出块与输入关联。
SSMS 中查询计划的图形表示非常有用，因为它向您显示每个语句相对于整个批处理/存储过程的成本的百分比成本。 但这是单个值，因此有时运行 Profiler 并打开语句级输出会更有帮助。 如果添加事件 SQL:StmtCompleted 以及 CPU 和 Reads 列，Profiler 将为每个语句提供单独的 IO 和 CPU 成本。
If there are a bunch of statements in the sproc it can be a bit convoluted to turn on the SET STATISTICS options since you have many chunks of output to associate with input.
The graphical representation of a query plan in SSMS is pretty useful since it shows you the % cost of each statement relative to the cost of the entire batch/sproc. But this is a single value, so it can be more helpful at times just to run Profiler and turn on statement level output. Profiler will give you separate IO and CPU cost for each statement if you add event SQL:StmtCompleted and columns CPU and Reads.