SQL 测量查询性能：“执行计划查询成本”与“所用时间”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/564717/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Measuring Query Performance : "Execution Plan Query Cost" vs "Time Taken"
提问by MatBailie
I'm trying to determine the relative performance of two different queries and have two ways of measuring this available to me:
1. Run both and time each query
2. Run both and get "Query Cost" from the actual execution plan
我正在尝试确定两个不同查询的相对性能，并有两种测量方法可供我使用：
1. 运行这两个查询并对每个查询计时
2. 运行两者并从实际执行计划中获取“查询成本”
Here is the code I run to time the queries...
这是我为查询计时而运行的代码...
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DECLARE @start DATETIME SET @start = getDate()
EXEC test_1a
SELECT getDate() - @start AS Execution_Time
GO
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DECLARE @start DATETIME SET @start = getDate()
EXEC test_1b
SELECT getDate() - @start AS Execution_Time
GO
What I get is the following:
我得到的是以下内容：
Stored_Proc Execution_Time Query Cost (Relative To Batch)
test_1a 1.673 seconds 17%
test_1b 1.033 seconds 83%
The results of the execution time directly contradict the results of the Query Cost, but I'm having difficulty determining what "Query Cost" actually means. My best guess is that it is an aggregate of Reads/Writes/CPU_Time/etc, so I guess I have a couple of questions:
执行时间的结果与查询成本的结果直接矛盾，但我很难确定“查询成本”的实际含义。我最好的猜测是它是 Reads/Writes/CPU_Time/etc 的聚合，所以我想我有几个问题：
Is there a definative source to explain what this measure means?
What other "Query Performance" metrics do people use, and what are their relative merits?
是否有明确的来源来解释该措施的含义？
人们使用哪些其他“查询性能”指标，以及它们的相对优点是什么？
It may be important to note that this is a medium sized SQL Server, running MS SQL Server 2005 on MS Server 2003 Enterprise Edition with multiple processors and 100+ concurrent users.
需要注意的是，这是一个中型 SQL Server，在 MS Server 2003 Enterprise Edition 上运行 MS SQL Server 2005，具有多个处理器和 100 多个并发用户。
EDIT:
编辑：
After some bother I managed to get Profiler access on that SQL Server, and can give extra info (Which supports Query Cost being related to system resources, not Execution Time itself...)
经过一番麻烦，我设法在该 SQL Server 上获得 Profiler 访问权限，并可以提供额外信息（支持与系统资源相关的查询成本，而不是执行时间本身......）
Stored_Proc CPU Reads Writes Duration
test_1a 1313 3975 93 1386
test_1b 2297 49839 93 1207
Impressive that taking more CPU with MANY more Reads takes less time :)
令人印象深刻的是，通过更多读取占用更多 CPU 所需的时间更少:)
采纳答案by gbn
The profiler trace puts it into perspective.
探查器跟踪将其置于透视中。
- Query A: 1.3 secs CPU, 1.4 secs duration
- Query B: 2.3 secs CPU, 1.2 secs duration
- 查询 A：1.3 秒 CPU，1.4 秒持续时间
- 查询 B：2.3 秒 CPU，1.2 秒持续时间
Query B is using parallelism: CPU > duration eg the query uses 2 CPUs, average 1.15 secs each
查询 B 使用并行性：CPU > 持续时间，例如查询使用 2 个 CPU，每个平均 1.15 秒
Query A is probably not: CPU < duration
查询 A 可能不是：CPU < 持续时间
This explains cost relative to batch: 17% of the for the simpler, non-parallel query plan.
这解释了相对于批处理的成本：更简单的非并行查询计划的 17%。
The optimiser works out that query B is more expensive and will benefit from parallelism, even though it takes extra effort to do so.
优化器计算出查询 B 的开销更大，并且会受益于并行性，即使这样做需要额外的努力。
Remember though, that query B uses 100% of 2 CPUS (so 50% for 4 CPUs) for one second or so. Query A uses 100% of a single CPU for 1.5 seconds.
但请记住，查询 B 使用 2 个 CPU 的 100%（因此 4 个 CPU 为 50%）一秒钟左右。查询 A 使用 100% 的单个 CPU 1.5 秒。
The peak for query A is lower, at the expense of increased duration. With one user, who cares? With 100, perhaps it makes a difference...
查询 A 的峰值较低，但代价是持续时间增加。一个用户，谁在乎？100，也许它会有所作为......
回答by Aditya Acharya
SET STATISTICS TIME ON
SELECT *
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
SET STATISTICS TIME OFF;
And see the message tab it will look like this:
并查看消息选项卡，它将如下所示：
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms.
(778 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
回答by Quassnoi
The results of the execution time directly contradict the results of the Query Cost, but I'm having difficulty determining what "Query Cost" actually means.
执行时间的结果与查询成本的结果直接矛盾，但我很难确定“查询成本”的实际含义。
Query costis what optimizer thinks of how long your query will take (relative to total batch time).
Query cost是优化器认为您的查询需要多长时间（相对于总批处理时间）。
The optimizer tries to choose the optimal query plan by looking at your query and statistics of your data, trying several execution plans and selecting the least costly of them.
优化器通过查看您的查询和数据统计信息、尝试多个执行计划并选择其中成本最低的来尝试选择最佳查询计划。
Hereyou may read in more detail about how does it try to do this.
在这里，您可以更详细地了解它是如何尝试做到这一点的。
As you can see, this may differ significantly of what you actually get.
如您所见，这可能与您实际得到的有很大不同。
The only real query perfomance metric is, of course, how long does the query actually take.
当然，唯一真正的查询性能指标是查询实际需要多长时间。
回答by NMK
Use SET STATISTICS TIME ON
用 SET STATISTICS TIME ON
above your query.
在您的查询之上。
Below near result tab you can see a message tab. There you can see the time.
在靠近结果选项卡的下方，您可以看到一个消息选项卡。在那里你可以看到时间。
回答by atik sarker
Query Execution Time:
查询执行时间：
DECLARE @EndTime datetime
DECLARE @StartTime datetime
SELECT @StartTime=GETDATE()
` -- Write Your Query`
SELECT @EndTime=GETDATE()
--This will return execution time of your query
SELECT DATEDIFF(MILLISECOND,@StartTime,@EndTime) AS [Duration in millisecs]
Query Out Put Will be Like:
查询输出将类似于：
To Optimize Query Cost :
优化查询成本：
Click on your SQL Management Studio
单击您的 SQL Management Studio
Run your query and click on Execution plan beside the Messages tab of your query result. you will see like
运行您的查询并单击查询结果的消息选项卡旁边的执行计划。你会看到像
回答by LCJ
I understand it's an old question – however I would like to add an example where cost is same but one query is better than the other.
我知道这是一个老问题——但是我想添加一个示例，其中成本相同但一个查询比另一个更好。
As you observed in the question, % shown in execution plan is not the only yardstick for determining best query. In the following example, I have two queries doing the same task. Execution Plan shows both are equally good (50% each). Now I executed the queries with SET STATISTICS IO ONwhich shows clear differences.
正如您在问题中所观察到的，执行计划中显示的百分比并不是确定最佳查询的唯一标准。在以下示例中，我有两个查询执行相同的任务。执行计划显示两者都一样好（各占 50%）。现在我执行了SET STATISTICS IO ON显示明显差异的查询。
In the following example, the query 1 uses seekwhereas Query 2 uses scanon the table LWManifestOrderLineItems. When we actually checks the execution time however it is find that Query 2 works better.
在以下示例中，查询 1 使用seek而查询 2 使用scan表 LWManifestOrderLineItems。然而，当我们实际检查执行时间时，会发现查询 2 效果更好。
Also read When is a Seek not a Seek?by Paul White
另请阅读何时寻找不是寻找？保罗·怀特
QUERY
询问
---Preparation---------------
-----------------------------
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON --IO
SET STATISTICS TIME ON
--------Queries---------------
------------------------------
SELECT LW.Manifest,LW.OrderID,COUNT(DISTINCT LineItemID)
FROM LWManifestOrderLineItems LW
INNER JOIN ManifestContainers MC
ON MC.Manifest = LW.Manifest
GROUP BY LW.Manifest,LW.OrderID
ORDER BY COUNT(DISTINCT LineItemID) DESC
SELECT LW.Manifest,LW.OrderID,COUNT( LineItemID) LineCount
FROM LWManifestOrderLineItems LW
WHERE LW.Manifest IN (SELECT Manifest FROM ManifestContainers)
GROUP BY LW.Manifest,LW.OrderID
ORDER BY COUNT( LineItemID) DESC
Statistics IO
统计IO
Execution Plan
执行计划
