blocks|key|3013792|text|遵循经过验证的等待和排队方法来确定瓶颈。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3013793|当请求运行并行查询时，分析阻塞的正确方法是深入子任务级别，查看是什么阻塞了每个子任务。一个人永远不应该停留在CXPACKET作为等待类型，或'self块‘作为解释。|style|CODE|3013794|select+w.last_wait_type,+
++++wt.wait_type,+
++++wt.resource_description,+
++++wt.blocking_session_id,+
++++t.pending_io_count,+
++++r.*+
from+sys.dm_os_tasks+t
left+join+sys.dm_os_waiting_tasks+wt+on+wt.waiting_task_address+=+t.task_address
join+sys.dm_os_workers+w+on+t.worker_address+=+w.worker_address
join+sys.dm_exec_requests+r+on+t.session_id+=+r.session_id
where+r.session_id+=+<queryspid>;|code-block|syntax|javascript|3013795|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/library/Cc966413^0|7|5|0|0|1I|8|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@$A|Z|B|10|F|G]]|9|@]|C|$]]|$1|H|3|I|5|J|7|11|8|@]|9|@]|C|$K|L]]|$1|M|3|-4|5|6|7|12|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

Follow the proven <a href="http://technet.microsoft.com/library/Cc966413" rel="nofollow">Waits and Queues</a> methodology to identify the bottleneck.

When a request is running parallel query the proper way to analyze blockage is to dive at the subtask level and see what is blocking each of the sub tasks. One should never stop at <code>CXPACKET</code> as wait type, or 'self block' as an explanation.

<pre><code>select w.last_wait_type, 
 wt.wait_type, 
 wt.resource_description, 
 wt.blocking_session_id, 
 t.pending_io_count, 
 r.* 
from sys.dm_os_tasks t
left join sys.dm_os_waiting_tasks wt on wt.waiting_task_address = t.task_address
join sys.dm_os_workers w on t.worker_address = w.worker_address
join sys.dm_exec_requests r on t.session_id = r.session_id
where r.session_id = &lt;queryspid&gt;;
</code></pre>

blocks|key|3370476|text|过时的统计数据.|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3370477|自阻塞只与并行发生，超长的并行运行(与规范相比)通常意味着过时的状态。这也可能是数据基数的变化。|3370478|步骤1应该在源表上运行一个UPDATE+STATISTICS+WITH+FULLSCAN。|CODE|3370479|entityMap^0|0|8|0|0|D|V|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|P|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|Q|8|@$9|R|A|S|B|J]]|D|@]|E|$]]|$1|K|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|L|$]]

Out of date stats.

Self-blocking only occurs with parallelism, and super long parallel runs (compared to norms) ordinarily means out-of-date stats. It could also be a change in cardinality in the data.

Step 1 should be running an <code>UPDATE STATISTICS WITH FULLSCAN</code> on your source table.

blocks|key|3013839|text|如果它看起来是这样的--一个档案查询--在运行时不会被更新的记录，那么您可以完全关闭阻塞。其他需要完整性但使用您的记录的查询不会受到影响--它们管理自己的锁定。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3013840|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If it's what it seems - an archival query - on records that won't be updated while it's running, you can turn off blocking entirely. Other queries that need integrity but use your records won't be affected - they manage their own locking.

blocks|key|3370543|text|还请确保将字段作为非聚集索引的一部分。如果不这样做，则必须使用RID查找返回表以获取该数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3370544|create+nonclustered+index+ix_whatever+on+YourTable+(date)+
include+(field1,+field2,+...)|code-block|syntax|javascript|3370545|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Also make sure you have your fields as part of the include of your nonclustered index. If you don't, you're going to have to back to the table using an RID lookup to get that data.

<pre><code>create nonclustered index ix_whatever on YourTable (date) 
include (field1, field2, ...)
</code></pre>

A load that has been running in about 2 minutes suddenly turned into a 90 minute run before being manually cancelled.

It's a simple shadow query:

<pre><code>select fields
into shadow_table
from table
where date = '8/23/2011'
</code></pre>

<code>date</code> has a non-clustered index on it.

If I change the query to select 

<ul>
<li><code>top 300000</code> it completes in 2 seconds</li>
<li><code>top 400000</code> it runs in 3 minutes</li>
<li><code>top 500000</code> I got bored waiting and cancelled it</li>
</ul>

Our server team shows a lot of self blocking while it runs.

Can anyone suggest possible bottlenecks to look at?

Increasing rows returned by "select top" suddently makes the query incredibly slower

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

在大约2分钟内运行的负载突然变成90分钟的运行，然后手动取消。这是一个简单的阴影查询：select fieldsinto shadow_tablefrom tablewhere date = '8/23/2011'date有一个非聚集索引。如果我将查询更改为选择top 300000它在2秒内完成top 400000，它...

问增加由"select top“返回的行会使查询变得非常慢。
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问增加由"select top“返回的行会使查询变得非常慢。EN