您的位置:新葡亰496net > 网络数据库 > 采取查询调整器幸免查询长日子运作

采取查询调整器幸免查询长日子运作

发布时间:2019-10-12 01:56编辑:网络数据库浏览(94)

    在SQL Server服务器性能--连接中,有壹个名叫“使用查询调整器防止查询长日子运作”的选项:

    Wait type - CXPACKET 

    新葡亰496net 1

    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.

    表达此设置是多个cost阈值,无单位。倘使有些query的subtreecost大于设置值,则不会进行,而是径直报如下错误:

    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 行
    查询已撤废,因为此询问的估计开支(59)超过了安排的阈值 5。请与系统管理员联系。

    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:

    只是经过链接服务器查询恐怕osql、sqlcmd则能够证实。

    • Your computer has only one processor.
    • Only a single CPU is available to SQL Server because of the affinity mask configuration option.
    • The max degree of parallelism option is set to 1.

    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;

    GO

    reconfigure;

    GO

    sp_configure 'cost threshold for parallelism', 10;

    GO

    reconfigure;

    GO

    本文由新葡亰496net发布于网络数据库,转载请注明出处:采取查询调整器幸免查询长日子运作

    关键词:

上一篇:Scheduler不能够用于查询管理

下一篇:没有了