SQL Server 2008：单个sql语句的性能成本
我有 2 个存储过程，我想比较并确定其中哪个需要更少的资源并且性能更好。第二个过程是第一个过程的修改，它包含第一个过程的稍微更改的 sql 语句。我的目标是了解查询成本变化的影响。
为此，我使用“包括实际执行计划”选项单独执行每个过程，并分析两个执行计划。我的问题是我无法以简单的方式说出哪个 sql 查询执行得更好。
例如，考虑第一个存储过程的查询的以下执行计划：
该计划显示该查询相对于批处理的成本为 0%，而聚集索引查找运算符相对于查询的成本为 100%。我对第二个过程的相应查询有相同的数字不幸的是，这不足以了解哪个查询的成本最小。
因此，我的问题是：有没有办法确定整个查询的成本。最好是带有查询及其特定成本的表，例如 CPU 成本或 I/O 成本。
I have 2 stored procedures which I want to compare and identify which of them requires less resources and performs better. The second procedure is a modification of the first procedure and it contains slightly changed sql statements of the first procedure. My goal is to understand the impact of the changes in terms of query cost.
In order to do so, I execute each procedure separately with an option "Include actual execution plan" and analyse both execution plans. My problem is that I cannot say which sql query performs better in a simple manner.
For example consider the following execution plan of the query of the first stored procedure:
The plan shows that the query cost is 0% relative to the batch and Clustered Index Seek operator is 100% relative to the query. I have the same numbers for the corresponding query of the second procedure Unfortunately this is not enough to understand which query has the minimal cost.
Therefore, my question: is there a way to determine the cost of the whole query. The best would be the table with a query and its particular cost, e.g. CPU cost or I/O cost.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以使用
SET STATISTICS IO ON(http://msdn.microsoft.com/en-us/library/ms184361.aspx) 和SET STATISTICS TIME ON(http: //msdn.microsoft.com/en-us/library/ms190287.aspx）在运行这两个过程之前。它将显示有关时间和输入/输出的附加信息。请记住，程序的结果可以被缓存，计划也可以被缓存，因此在做出任何结论之前，您必须仔细执行。第一次运行总是比下一次慢。在进行任何测试之前，您应该使用DBCC DROPCLEANBUFFERS和DBCC FREEPROCCACHE清理缓存。您还应该了解聚集索引和非聚集索引、查找和扫描以及计划中提供的其他操作之间的差异。You can use
SET STATISTICS IO ON(http://msdn.microsoft.com/en-us/library/ms184361.aspx) andSET STATISTICS TIME ON(http://msdn.microsoft.com/en-us/library/ms190287.aspx) before running both procedures. It will show additional info about time and input/output. Remember that results of procedures can be cached, plans can be cached, so you have to do it carefully, before making any conclussions. First run can always be slower than next ones. You should clean your cache withDBCC DROPCLEANBUFFERSandDBCC FREEPROCCACHE, before making any tests. You should also read about differences between clustered and non-clustered index, seek and scan and other actions presented in plan.您可以在 SQL 探查器中跟踪查询。您可以在其中获取运行的每个查询的 CPU、IO、总时间等。
You can trace the queries in SQL profiler. There you get CPU, IO, Total time, etc for each query you run.