Parallel Query Processing

最新推荐文章于 2024-11-18 11:26:00 发布

转载最新推荐文章于 2024-11-18 11:26:00 发布 · 1k 阅读

SQL Server数据库专栏收录该内容

128 篇文章

订阅专栏

本文探讨了SQL Server中并行查询的工作原理及其如何利用多个CPU提高查询效率。通过使用多个操作系统线程来执行查询任务，SQL Server能够显著缩短查询时间。文中还介绍了影响并行查询线程数量的因素，包括查询计划的复杂性和并行度设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

有时我们会在sysprocesses中看到很多duplicate SPID，但是有着不同的KPID。

spid kpid

------ ------

55 3760

55 3684

55 5956

55 3680

55 3260

这篇文章解释了这个现象：

http://www.sqlservertorque.net/2010/09/does-spid-unique-in-syssysprocesses.html

其中这句话有点意思：KPID is Windows thread id. Each SQL Server task is assigned a Windows thread.

相关的Parallel Query Processing官文：

http://msdn.microsoft.com/en-us/library/ms178065%28v=sql.105%29.aspx

SQL Server provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). Because SQL Server can perform a query or index operation in parallel by using several operating system threads, the operation can be completed quickly and efficiently.

The actual number of threads used by a parallel query is determined at query plan execution initialization and is determined by the complexity of the plan and the degree of parallelism.The degree of parallelism value is set at the server level and can be modified by using the sp_configure system stored procedure. You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.

这里有另外一篇文章解释MAXDOP：

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/01/05/why-do-i-see-more-threads-per-spid-in-sysprocesses-than-maxdop.aspx