测量查询性能:“执行计划查询成本"vs “所用时间"

2021-12-20 00:00:00 optimization sql sql-server-2005 sql-server sql-execution-plan

我正在尝试确定两个不同查询的相对性能，并有两种可用的测量方法:
1. 运行每个查询并计时
2. 两者都运行，从实际执行计划中得到Query Cost"

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

这是我运行查询时间的代码...

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

我得到的是以下内容:

Stored_Proc     Execution_Time     Query Cost (Relative To Batch)

test_1a         1.673 seconds      17%
test_1b         1.033 seconds      83%

执行时间的结果与查询成本的结果直接矛盾，但我很难确定查询成本"的实际含义.我最好的猜测是它是 Reads/Writes/CPU_Time/etc 的聚合，所以我想我有几个问题:

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:

  1. 是否有明确的来源来解释该措施的含义?

  1. 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?


需要注意的是，这是一个中型 SQL Server，在 MS Server 2003 Enterprise Edition 上运行 MS SQL Server 2005，具有多个处理器和 100 多个并发用户.


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 上获得 Profiler 访问权限，并可以提供额外信息(支持与系统资源相关的查询成本，而不是执行时间本身......)

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...)

Stored_Proc    CPU      Reads    Writes   Duration   

test_1a        1313     3975     93       1386
test_1b        2297     49839    93       1207

令人印象深刻的是，通过更多读取占用更多 CPU 所需的时间更少:)

Impressive that taking more CPU with MANY more Reads takes less time :)

推荐答案

探查器跟踪将其置于透视中.

The profiler trace puts it into perspective.

  • 查询 A:1.3 秒 CPU，1.4 秒持续时间
  • 查询 B:2.3 秒 CPU，1.2 秒持续时间

查询 B 正在使用并行性:CPU > 持续时间例如查询使用 2 个 CPU，平均每个 1.15 秒

Query B is using parallelism: CPU > duration eg the query uses 2 CPUs, average 1.15 secs each

查询 A 可能不是:CPU <持续时间

Query A is probably not: CPU < duration

这解释了相对于批处理的成本:更简单的非并行查询计划的 17%.

This explains cost relative to batch: 17% of the for the simpler, non-parallel query plan.

优化器计算出查询 B 的开销更大，并且会受益于并行性，即使这样做需要额外的努力.

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 使用 2 个 CPU 的 100%(因此 4 个 CPU 为 50%)一秒钟左右.查询 A 使用 100% 的单个 CPU 1.5 秒.

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.

查询 A 的峰值较低，代价是持续时间增加.一个用户，谁在乎?100，也许会有所作为...

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...

相关文章