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.
I've grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It's a fairly human look at what's involved in sailing on a Trident missile submarine...
Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.
You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...
Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.