SQL 查询、执行计划和“并行性" [英] SQL Queries, execution plans and "Parallelism"

问题描述

所以我(仍然)正在经历一些缓慢的遗留 sql 视图，用于计算(有时)大量数据的一些平均值和标准偏差.我最终得到的是视图加入视图加入视图等.

So I'm (still) going through some slow legacy sql views used to do calculate some averages and standarddeviations on a (sometimes) large set of data. What I end up with are views joining views joining views etc.

所以我虽然会检查查询的执行计划.它立即提示缺少索引，然后我实施了该索引.但它仍然慢得令人无法忍受(速度太慢以至于 VB6 应用程序在查询它的数据时超时；))

So I though I would review the execution plan for my query. And it immediately suggested a missing index, which I then implemented. But it's still unbearably slow (so slow it times out the VB6 app querying it for data ;) )

因此，在进一步研究执行计划后，我发现成本最高的(在我的案例中每个成本约为 8%)是并行"案例.主要是分发流"和重新分区流".这些是什么?

So upon studying the execution plan further, I see that what costs the most (about 8% each in my case) are "Paralellism" cases. Mostly "Distribute Streams" and "Repartition Streams". What are these?

推荐答案

Distribute Streams 和Repartion Streams 是 SQL 优化器选择使用 并行查询处理.如果您怀疑这会导致您的查询出现问题，您可以强制 SQL Server 仅使用一个 CPU 和 MAXDOP 查询提示，如下图所示.

Distribute Streams and Repartion Streams are operations that occur when the SQL optimizer chooses to use Parallel Query Processing. If you suspect that this is causing an issue with your query, you can force SQL Server to only use one CPU with the MAXDOP query hint, as illustrated below.

select *
    from sys.tables
    option (maxdop 1)

这篇关于SQL 查询、执行计划和“并行性"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！