SQL Server - Cost Threshold of Parallelism

Who is online?  114 guests and 0 members
home  »  blogs  »  Amit Bansal  »  SQL Server - Cost Threshold of Parallelism

SQL Server - Cost Threshold of Parallelism

change text size: A A A

posted  5/29/2012 9:16:52 AM  by Amit Bansal -  Views: [7494] 

Hi Friends, 
 
What triggers me to write this blog today is that in one of my recent consulting engagements, my audience had blurred understanding of this server-level property and the concept of parallelism overall. Here is a simple explanation:
 
Parallelism, in simple words, means that SQL Server can use 2 or more processors to execute your query. Which means, the optimizer produces a parallel plan and you can observe the parallel physical operator (yellow circle with black arrows)
 
 
 
Processors here could be cores or even hyper-threading. You might have one physical processor with 4 cores and SQL Server can use all the cores. Mind you, affinity mask setting and max degree of parallelism should be set correctly so that you can use more than 1 processor.
 
The default value of ‘cost threshold of parallelism’ is 5. My audience had a blurred understanding that this value means that if the query takes more than 5 seconds to execute, the optimizer will switch to parallel plans. That is not correct ! There's a lot more to that 'cost' thing. And other folks had other ideas !
 
SQL Server’s optimizer is a cost based optimizer and it compares the cost of multiple plans before outputting the plan with least cost. And that value of 5 is that cost – BOL says that that value is estimated elapsed time but I have something more to add to it.
 
So when the optimizer calculates that the cost of a serial plan is more than 5, it switches to a parallel plan. I can demonstrate that with a simple example.
Here is a query that uses parallel plan.
  
use AdventureWorks2008R2
GO

select * from sales.SalesOrderDetail
order by LineTotal DESC
GO

If you observe the execution plan and hover your mouse over the SELECT operator, you can see Estimated Subtree Cost is 4.37091. Now, this is a parallel plan and the cost is less than 5. But what is the cost of this plan if it was serial? Let us see that using MAXDOP hint:
use AdventureWorks2008R2
GO

select * from sales.SalesOrderDetail
order by LineTotal DESC
OPTION (MAXDOP 1)
GO

Using the MAXDOP hint, I forced SQL Server to use only one CPU and you can see that the cost is 10.4921. The plan is does not use the parallel physical operator and is a serial plan. This proves that since the cost of serial plan was more than the cost threshold of parallelism value of 5, the optimizer decides to go for a parallel plan.
Does this mean that if we change the value of 5 to 11 (for example), the optimizer will not output parallel plan? Answer is Yes, the optimizer will not output parallel plan. Let us test it.
First change the value:
sp_configure 'cost threshold for parallelism', 11
GO
RECONFIGURE
GO

Run the query again. (Note that there is no hint)
use AdventureWorks2008R2
GO

select * from sales.SalesOrderDetail
order by LineTotal DESC

GO

Does SQL Server produce parallel plan? What is the cost? Is it less than cost threshold of parallelism?

There’s lot more to this subject and I have just given a simple explanation to clarify the meaning of this server-level property.

If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks

Have a SQL Server question? Join the fastest growing SQL Server facebook group at: http://www.facebook.com/groups/458103987564477/

Regards,

Amit Bansal

http://www.twitter.com/A_Bansal
http://www.twitter.com/SQLServerGeeks
http://www.amitbansal.net

Visit my FaceBook page at http://www.facebook.com/AmitRSBansal
Contribute on SQLServerGeeks.com: visit http://www.sqlservergeeks.com/default-category/write-for-us

tags : cost threshold of parallelism, sql server cost based optimizer, sql server optimizer internals, SQL Server parallel query, SQL Server Query Optimizer
  Rate This Blog Entry:  register  or  login

Author

AmitBansal Amit Bansal (Member since: 3/12/2011 4:59:54 PM)
Follow Amit at Twitter @A_Bansal : Amit Bansal is the CTO of eDominer Systems & Peopleware India. He is a consultant, trainer, writer, speaker & evangelist on SQL Server & Business Intelligence. A seasoned speaker; he speaks at major summits and conferences of Microsoft worldwide including TechED, MCT Summits, MSDN & TechNet conferences. Over the last 8 years, he has consulted, trained & mentored more than 6000 IT professionals on SQL Server & Business Intelligence and worked with top notch blue chip companies worldwide including Microsoft, Infosys, Wipro, RBS, HCL, HP, Siemens, IBM, Accenture, etc. He has delivered more than 400 workshops on SQL Server & Business Intelligence. Apart from holding many Microsoft credentials, he is also a Microsoft Most Valuable Professional (MVP) awardee on SQL Server. He is also one of the first Microsoft Certified Trainer in India. Amit has worked with Microsoft in India and US as a Subject Matter Expert in various capacities participating in OD sessions, technical reviewing, etc. He is also a very active community lead in Asia Pacific. Visit www.amitbansal.net to know more. Subscribe to Amit’s blog and be a member of his technical forum at www.SQLServerGeeks.com - an exclusive SQL Server portal. Visit www.peoplewareindia.com for Corporate Training solutions. FaceBook – http://www.facebook.com/people/Amit-Bansal/525339346 LinkedIn – http://www.linkedin.com/pub/amit-bansal/7/121/755

View Amit Bansal 's profile

Comments (4)

Prashant
5/29/2012 11:07:18 AM Prashant said:

Short and simple illustration while covering up the most. Nice blog Amit!

The best explanation about estimated subtree cost I have seen so far is the below one from QP team.

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2006/10/11/what-is-this-cost.aspx

Folks who want to know more can probably refer to the above link :)

by
Marc Jellinek
5/29/2012 11:38:57 AM Marc Jellinek said:

As per SQL Server Books Online "Configure the cost threshold for parallelism Server Configuration Option"  http://msdn.microsoft.com/en-us/library/ms190949.aspx

The cost threshold for parallelism option specifies the threshold at which 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.

 

by
AmitBansal
5/29/2012 12:55:44 PM Amit Bansal said:

Hi MArc, I knew someone would point that out :) - unfortunately there are many of those one-liners in BOL that I do not understand :( and thats why I explained in simple example using Estimated Subtree cost. However, I have updated my blog now to be a bit lenient on that BOL one-liner :)

by
AmitBansal
6/3/2012 4:24:37 AM Amit Bansal said:

Thanks Prashant !

by

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Blogs RSS Feed

Amit Bansal's latest blog posts

Blogs RSS Feed

Latest community blog posts

  • Parse DBCC MemoryStatus using Powershell
    5/19/2013 10:58:46 AM by Ahmad Osama  - Views: 153  - Comments: 0
    A few days back I was resolving a memory issue which caused sql server to stop responding. I was able to figure out the issue by looking into DBCC MemoryStatus output. It was the full text search whic...
  • DB-Migrate v2: Powershell Module to Migrate Databases
    5/14/2013 8:11:24 PM by Ahmad Osama  - Views: 230  - Comments: 0
    This is an update to the DB-Migrate ( http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/797/db-migrate-a-powershell-module-to-migrate-databases ) powershell module to migrate database between sq...
  • The TSQL Classes Stored Procedures: Transaction Handling
    5/12/2013 4:14:12 PM by Ahmad Osama  - Views: 258  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The links to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedure...
  • Reporting With SSRS Part 1 -With Sample Report.
    5/9/2013 4:33:03 AM by Syed Asrar Ali  - Views: 156  - Comments: 0
    Reporting With SSRS Part 1 : In this serires of blogs, i will try to introduce you with some of the key features of the tool which will help you get on board to become a proficient report developer.If...
  • SQL Server – What is IDENTITY_INSERT?
    5/8/2013 2:31:43 PM by Piyush Bajaj  - Views: 1037  - Comments: 3
    Hi Folks, I am sure that all of you would be aware about the role of Identity column in a table, i.e., it is column which keeps on incrementing without supplying the value explicitly during insertion....
  • DB-Migrate: A PowerShell Module to Migrate Databases.
    5/5/2013 8:48:46 PM by Ahmad Osama  - Views: 425  - Comments: 0
    Recently I wrote powershell scripts to move databases, logins and SQL Agent Jobs between instances. Another one I wrote was to fix orphan users. I have now combined all of these different functions in...
  • SQL Server blogs from Ahmad Osama for the month of April
    5/3/2013 10:22:05 AM by Admin Admin  - Views: 339  - Comments: 0
    Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at h...
  • The TSQL Classes Stored Procedures: Error Handling
    5/1/2013 9:05:11 PM by Ahmad Osama  - Views: 501  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The link to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures...
  • Get free space in all files in an Instance
    4/8/2013 9:01:44 AM by Manohar Punna  - Views: 1654  - Comments: 1
    As DBAs we are always challenged of unexpected size growth of log or data files and need to shrink files based on available free space in each file. Most of the times we are stuck and do a lot of scri...
  • SQL Server: Using Powershell to Fix Orphan Users
    4/2/2013 7:05:50 PM by Ahmad Osama  - Views: 1696  - Comments: 0
    One of the major migration activities is to fix orphan users. Though, it can be easily fixed by sp_change_users_login procedure, however what if you need to do this for multiple servers. A powershell ...
  • The TSQL Classes Stored Procedures: Parameter sniffing
    4/1/2013 10:55:43 PM by Ahmad Osama  - Views: 2419  - Comments: 3
    This blog is part of the series The TSQL Classes: Stored Procedure. http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-cachin...