Wait type - CXPACKET
It means SQL Query is involved in parallel query execution. This waittype indicates that the SPID is waiting on a parallel process to complete or start.
CXPACKET: Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.
query governor cost limit option:
Use the query governor cost limit option to specify an upper limit on the time period in which a query can run. Queries that have estimated run times greater than this limit, return an error and are not executed. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run without any time limitation.If you use sp_configure to change the value of query governor cost limit, the changed value is server wide. To change the value on a per-connection basis, use the SET QUERY_GOVERNOR_COST_LIMIT statement.The query governor cost limit option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change query governor cost limit only when show advanced options is set to 1. The setting takes effect immediately (without a server restart).
How to configure cost threshold for parallelism Option
Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism采取查询调整器幸免查询长日子运作。. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. Only set cost threshold for parallelism on symmetric multiprocessors.
Longer queries usually benefit from parallel plans; the performance advantage negates the additional time required to initialize, synchronize, and terminate parallel plans. The cost threshold for parallelism option is actively used when a mix of short and longer queries is run. The short queries run serial plans, whereas the longer queries use parallel plans. The value of cost threshold for parallelism determines which queries are considered short, and they should therefore be run using serial plans.
8649，级别 17，状态 1，服务器 LEOSWONDERWORLD，第 1 行
In certain cases, a parallel plan may be chosen even though the query's cost plan is less than the current cost threshold for parallelism新葡亰496net， value. This can happen because the decision to use a parallel or serial plan is based on a cost estimate provided before the full optimization is complete.
The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5.
内需特别注意的一些是，那一个装置貌似对sqlserver management studio(ssms.exe)工具无效，无论是在DB Server当地照旧长途连接，使用ssms进行的查询都不会受此参数影响。
采取查询调整器幸免查询长日子运作。SQL Server ignores the cost threshold for parallelism value under the following conditions:
The cost threshold for parallelism option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change cost threshold for parallelism only when show advanced options is set to 1. The setting takes effect immediately (without a server restart).
The following example sets the cost threshold for parallelism to 10 seconds.
sp_configure 'show advanced options', 1;
sp_configure 'cost threshold for parallelism', 10;