Tuesday 26 February 2013

Cost Threshold for Parallelism


Parallelism, where to start?  Well let’s start right at the beginning shall we with the question, what is parallelism?  Simply put it is a feature of SQL Server which allows queries to utilize more threads in order to complete quicker.  There are several ways to influence SQL Servers decision whether to use parallelism or not and if so how “much” which is referred to as the degree of parallelism.

Cost Threshold For Parallelism
This is a server wide setting which indicates what the minimum query cost has to be before Parallelism is considered by the optimizer (yes UK readers I spelt optimizer with a Z not an S).

Maximum Degree Of Parallelism
This is a server wide setting which indicates the maximum number of processors a query run in parallel can use.

OPTION (MAXDOP X)
This is a query hint which contrary to some beliefs will override the above two settings by reducing the degree of parallelism for queries greater than the cost threshold, but will not make a too small query run in parallel (Thanks to Brent Ozar for pointing this out).

So what settings should you be using, well as always “it depends”, sorry but this really isn’t a one size fits all solution.  Servers will have different workloads, number of cores, some will have hard NUMA some will have Soft NUMA some older servers won’t have NUMA at all.  What you really need to do is understand the impact parallelism has on your environment, today I will look specifically at the sp_configure option ‘cost threshold for parallelism‘.

SQL Server exposes an increasing wealth of information via DMV’s that allow us to make more informed decisions; one idea that I loved was using these DMV’s specifically sys.dm_exec_cached_plans and sys.dm_exec_query_plan to return information from the Plan Cache for queries run in parallel. This idea I took from Jonathan Kehayias and adapted to group the exposed information by StatementSubTreeCost.  This will give you the spread of query costs which you can use to make an educated decision as to if the default cost threshold for parallelism value of 5 is in fact the best for your environment.

/*
      -----------------------------------------------------------------
      Cost Threshold For Parallelism
      -----------------------------------------------------------------
   
      For more SQL resources, check out SQLServer365.blogspot.com

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

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
    
      -----------------------------------------------------------------
*/
-- Set Database Context
USE master;
GO
-- Create table
IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   [object_id] = OBJECT_ID('dbo.PlanCacheForMaxDop')
                        AND [type] = 'U' )
    CREATE TABLE master.dbo.PlanCacheForMaxDop
        (
          CompleteQueryPlan XML ,
          StatementText VARCHAR(4000) ,
          StatementOptimizationLevel VARCHAR(25) ,
          StatementSubTreeCost FLOAT ,
          ParallelSubTreeXML XML ,
          UseCounts INT ,
          PlanSizeInBytes INT
        );
ELSE
      -- If table exists truncate it before population
    TRUNCATE TABLE  master.dbo.PlanCacheForMaxDop;     
GO   

-- Collect parallel plan information
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES  
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
INSERT INTO master.dbo.PlanCacheForMaxDop
SELECT 
     query_plan AS CompleteQueryPlan,
     n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
     n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
     n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
     n.query('.') AS ParallelSubTreeXML, 
     ecp.usecounts,
     ecp.size_in_bytes 
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE  n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;
GO

-- Return parallel plan information
SELECT  CompleteQueryPlan ,
        StatementText ,
        StatementOptimizationLevel ,
        StatementSubTreeCost ,
        ParallelSubTreeXML ,
        UseCounts ,
        PlanSizeInBytes
FROM    master.dbo.PlanCacheForMaxDop;
GO

-- Return grouped parallel plan information
SELECT  MAX(CASE WHEN StatementSubTreeCost BETWEEN 1 AND 5 THEN '1-5'
                 WHEN StatementSubTreeCost BETWEEN 5 AND 6 THEN '5-6'
                 WHEN StatementSubTreeCost BETWEEN 6 AND 7 THEN '6-7'
                 WHEN StatementSubTreeCost BETWEEN 7 AND 8 THEN '7-8'
                 WHEN StatementSubTreeCost BETWEEN 8 AND 9 THEN '8-9'
                 WHEN StatementSubTreeCost BETWEEN 9 AND 10 THEN '9-10'
                 WHEN StatementSubTreeCost BETWEEN 10 AND 11 THEN '10-11'
                 WHEN StatementSubTreeCost BETWEEN 11 AND 12 THEN '11-12'
                 WHEN StatementSubTreeCost BETWEEN 12 AND 13 THEN '12-13'
                 WHEN StatementSubTreeCost BETWEEN 13 AND 14 THEN '13-14'
                 WHEN StatementSubTreeCost BETWEEN 14 AND 15 THEN '14-15'
                 WHEN StatementSubTreeCost BETWEEN 15 AND 16 THEN '15-16'
                 WHEN StatementSubTreeCost BETWEEN 16 AND 17 THEN '16-17'
                 WHEN StatementSubTreeCost BETWEEN 17 AND 18 THEN '17-18'
                 WHEN StatementSubTreeCost BETWEEN 18 AND 19 THEN '18-19'
                 WHEN StatementSubTreeCost BETWEEN 19 AND 20 THEN '19-20'
                 WHEN StatementSubTreeCost BETWEEN 20 AND 25 THEN '20-25'
                 WHEN StatementSubTreeCost BETWEEN 25 AND 30 THEN '25-30'
                 WHEN StatementSubTreeCost BETWEEN 30 AND 35 THEN '30-35'
                 WHEN StatementSubTreeCost BETWEEN 35 AND 40 THEN '35-40'
                 WHEN StatementSubTreeCost BETWEEN 40 AND 45 THEN '40-45'
                 WHEN StatementSubTreeCost BETWEEN 45 AND 50 THEN '45-50'
                 WHEN StatementSubTreeCost > 50 THEN '>50'
                 ELSE CAST(StatementSubTreeCost AS VARCHAR(100))
            END) AS StatementSubTreeCost ,
        COUNT(*) AS countInstance
FROM    master.dbo.PlanCacheForMaxDop
GROUP BY CASE WHEN StatementSubTreeCost BETWEEN 1 AND 5 THEN 2.5
              WHEN StatementSubTreeCost BETWEEN 5 AND 6 THEN 5.5
              WHEN StatementSubTreeCost BETWEEN 6 AND 7 THEN 6.5
              WHEN StatementSubTreeCost BETWEEN 7 AND 8 THEN 7.5
              WHEN StatementSubTreeCost BETWEEN 8 AND 9 THEN 8.5
              WHEN StatementSubTreeCost BETWEEN 9 AND 10 THEN 9.5
              WHEN StatementSubTreeCost BETWEEN 10 AND 11 THEN 10.5
              WHEN StatementSubTreeCost BETWEEN 11 AND 12 THEN 11.5
              WHEN StatementSubTreeCost BETWEEN 12 AND 13 THEN 12.5
              WHEN StatementSubTreeCost BETWEEN 13 AND 14 THEN 13.5
              WHEN StatementSubTreeCost BETWEEN 14 AND 15 THEN 14.5
              WHEN StatementSubTreeCost BETWEEN 15 AND 16 THEN 15.5
              WHEN StatementSubTreeCost BETWEEN 16 AND 17 THEN 16.5
              WHEN StatementSubTreeCost BETWEEN 17 AND 18 THEN 17.5
              WHEN StatementSubTreeCost BETWEEN 18 AND 19 THEN 18.5
              WHEN StatementSubTreeCost BETWEEN 19 AND 20 THEN 19.5
              WHEN StatementSubTreeCost BETWEEN 10 AND 15 THEN 12.5
              WHEN StatementSubTreeCost BETWEEN 15 AND 20 THEN 17.5
              WHEN StatementSubTreeCost BETWEEN 20 AND 25 THEN 22.5
              WHEN StatementSubTreeCost BETWEEN 25 AND 30 THEN 27.5
              WHEN StatementSubTreeCost BETWEEN 30 AND 35 THEN 32.5
              WHEN StatementSubTreeCost BETWEEN 35 AND 40 THEN 37.5
              WHEN StatementSubTreeCost BETWEEN 40 AND 45 THEN 42.5
              WHEN StatementSubTreeCost BETWEEN 45 AND 50 THEN 47.5
              WHEN StatementSubTreeCost > 50 THEN 100
              ELSE StatementSubTreeCost
         END;
GO

Enjoy!

Chris         

1 comment:



  1. My name is Leah Brown, I'm a happy woman today? I told myself that any loan lender that could change my life and that of my family after having been scammed separately by these online loan lenders, I will refer to anyone who is looking for loan for them. It gave me and my family happiness, although at first I had a hard time trusting him because of my experiences with past loan lenders, I needed a loan of $300,000.00 to start my life everywhere as single mother with 2 children, I met this honest and God fearing online loan lender Gain Credit Loan who helped me with a $300,000.00 loan, working with a loan company Good reputation. If you are in need of a loan and you are 100% sure of paying the loan please contact (gaincreditloan1@gmail.com)

    ReplyDelete