July 2, 2015 at 10:25 am
Hello All,
Referencing an article regarding MAXDOP and cost threshold for parallelism from Brent Ozar's website:
http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/
We have a 2 physical CPUs that are 4 cores each with hyper threading enabled. When looking through the task manager, under the performance tab, I see 16 CPU threads.
We have set the MAXDOP value is set at 4.
Reading further, cost threshold for parallelism setting is recommended at 50 to start with.
Our setting is at the default 5. Can any expert here explain in simpler terms the impact and advantages of this setting.
I would certainly like to change it but feel reluctant.
Thank you
SQLJay
July 2, 2015 at 11:28 am
Cost threshold for paralellism is a value based on the estimated cost of the execution plan created by the query optimizer. The cost values, once upon a time (we're talking 1998), were loosely based on rough measures of I/O and CPU time. In theory, if a query were to take longer than 5 seconds, it would go parallel. But the estimated cost values have long been divorced from that and are just cost units with no relation to any kind of physical processing. The default value of 5 was set back in 1998 for SQL Server 7. At the time, 5 was considered to be a fairly high cost query so parallel execution would be handy. Times have changed. Queries can very easily get a cost value higher than 5, but the management, memory, cpu needed for parallel execution can completely outweigh the benefits of parallel execution for queries that don't need it. This is especially true with OLTP systems where you're only ever moving small amounts of data around. It's less true of reporting systems where large data volumes can benefit from parallel processing. Also, if you were on 2012 or better, you'd need to worry about cost threshold as it relates to columnstore. But, for most systems, a value of 50 is a fine starting point. If you have queries that are high cost, but less than 50, and you think they might benefit from parallel processing, you could trying lowering that number. Just watch for waits and queues on the CPU.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2015 at 2:51 pm
SQLJay (7/2/2015)
We have a 2 physical CPUs that are 4 cores each with hyper threading enabled. When looking through the task manager, under the performance tab, I see 16 CPU threads.
Don't use Task Manager to monitor SQL Server.
That said, yes, you'll see 16 threads there. That's the 16 schedulers that SQL Server has created, one for each of the cores, 8 physical and 8 from hyperthreading. This has nothing to do with MAXDOP, which is the number of concurrently running threads a query may have.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply