SQL Server - Cost Threshold of Parallelism

Who is online?  112 guests and 0 members
home  »  blogs  »  Amit Bansal  »  SQL Server - Cost Threshold of Parallelism
  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:
 *
 
   

SQLServerGeeks FaceBook group

SQLServerGeeks Events

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

  • Hi Friends, In this blog post, let’s have an insight on how Auditing can be done in SSIS packages? The basic auditing in package includes measures like How many rows were inserted, updated or de...
  • Hi SQL Geeks, Here are the blog posts by Piyush Bajaj for the month of May. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at ht...
  • Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of May. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at htt...
  • Hi Friends, Are you facing any issue on SQL Server 2014 while running the same query which is running fine on previous version of SQL Server? If answer is yes then this may be due to the change in SQL...
  • I have written a SQL Server Change Data Capture GUI interface to facilitate for CDC operations. The project is hosted here https://sqlcdcapp.codeplex.com/ . Feel free to download, review and suggest c...
  • Recently I came across an interesting deadlock scenario I wasn’t aware off. I didn’t have idea that foreign key constraint can also result in deadlocks. The detailed steps to replicate the...
  • Hi Friends, In my earlier blog, I just explain about the importance of filtered indexes. Link for that blog is mention below: http://sqlservergeeks.com/blogs/princerastogi/personal/599/filtered-indexe...
  • A few words about the technology of our site. It's easy to take technology for granted, to focus on content and pay no attention to how it is being delivered. Typically we would encourage this focus. ...