August 19, 2011 at 5:12 pm
Marios Philippopoulos (8/19/2011)
Another setting to play around with: cost threshold for parallelism.Default value is 5 sec; may be too low for most systems.
Setting it to higher values (say 15-20 sec) may help prevent most queries from parallelizing except for the most expensive ones.
Costs are not measurements of time. A query with a cost of 5 does not take 5 seconds to run.
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
August 20, 2011 at 4:55 am
GilaMonster (8/19/2011)
Marios Philippopoulos (8/19/2011)
Another setting to play around with: cost threshold for parallelism.Default value is 5 sec; may be too low for most systems.
Setting it to higher values (say 15-20 sec) may help prevent most queries from parallelizing except for the most expensive ones.
Costs are not measurements of time. A query with a cost of 5 does not take 5 seconds to run.
Here is the definition I have of the cost-threshold-for parallelism default value (taken from "SQL Server 2008 Query Performance Tuning Distilled" by Grant Fritchey and Sajal Dam: "... If the estimated execution time of the serial plan is more than 5 seconds, then the optimizer considers a parallel plan for the query."
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 20, 2011 at 5:14 am
I'll tell Grant he's wrong. He'll agree with me.
The query optimiser costs were times at one point. They were benchmark times on a specific computer in the MS testing lab during the development of SQL Server 7. On that single piece of hardware (20 or so years ago) a query of cost 5 would have taken 5 seconds.
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
August 20, 2011 at 5:34 am
GilaMonster (8/20/2011)
I'll tell Grant he's wrong. He'll agree with me.The query optimiser costs were times at one point. They were benchmark times on a specific computer in the MS testing lab during the development of SQL Server 7. On that single piece of hardware (20 or so years ago) a query of cost 5 would have taken 5 seconds.
I see, thanks for clarifying.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 20, 2011 at 9:52 am
GilaMonster (8/20/2011)
I'll tell Grant he's wrong. He'll agree with me.The query optimiser costs were times at one point. They were benchmark times on a specific computer in the MS testing lab during the development of SQL Server 7. On that single piece of hardware (20 or so years ago) a query of cost 5 would have taken 5 seconds.
Even though the cost is not in seconds, it could be valuable to change the default value of 5 to a higher value to reduce the number of queries that will consider parallel plans.
If you are getting a high number of CXPACKET waits, and the workload is mostly OLTP type of queries, and the average wait times are high - then reducing the MAXDOP setting and increasing the cost threshold could help.
I would say - test various values until you get to an acceptable level of performance. But, don't just change it and test a couple of queries. I would first set the MAXDOP setting to half the number of physical processors (that is, the number of processors SQL sees - not sockets). Clear the wait stats and monitor for several days. If still very high number of CXPACKET waits with high average wait times - increase the cost threshold, clear the wait stats and monitor for several days, repeat until you get to an acceptable level and can show improved system performance.
One thing I schedule every day before business starts is a job to clear the wait stats. This way, I can get a clear idea of how the wait stats are accumulating through the business day - instead of skewing the values because of nightly maintenance (e.g. index rebuilds, backups, integrity checks, etc...).
To the previous posters about SQL Server Standard only using 4 CPU's - I believe that is a licensing number and does not have any relationship to the actual number of cores it will use. I have built systems with 4 Quad core processors and run SQL Server 2005 Standard and all cores were used. I have not heard that 2008/2008R2 changed that - but if so is there a link that explains that?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 21, 2011 at 5:48 am
awu (8/17/2011)
Thanks Jason, but I am still puzzling over something here, if you right , it's 8, then i can set : max degree of parallelism = 6, leave two CPUs to something else all the time , right ? But on the other hand, it's saying max numbers of CPUs the SQL Server 2008 R2 Standard edition support is 4, is that mean i should set the number less than 4 ? Confusing ....
MAXDOP refers to the max number of CPUs that an operator in a query plan of a single running session can parallelize to.
Setting the MAXDOP to a number lower than the total number of CPUs in the server will not ensure that some CPUs are left unused by the application, as there are likely to be several application sessions running on the server at any given time.
Oops, just realized this was mentioned earlier...:cool:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 21, 2011 at 7:10 am
Jeffrey Williams-493691 (8/20/2011)
GilaMonster (8/20/2011)
I'll tell Grant he's wrong. He'll agree with me.The query optimiser costs were times at one point. They were benchmark times on a specific computer in the MS testing lab during the development of SQL Server 7. On that single piece of hardware (20 or so years ago) a query of cost 5 would have taken 5 seconds.
Even though the cost is not in seconds, it could be valuable to change the default value of 5 to a higher value to reduce the number of queries that will consider parallel plans.
If you are getting a high number of CXPACKET waits, and the workload is mostly OLTP type of queries, and the average wait times are high - then reducing the MAXDOP setting and increasing the cost threshold could help.
I would say - test various values until you get to an acceptable level of performance. But, don't just change it and test a couple of queries. I would first set the MAXDOP setting to half the number of physical processors (that is, the number of processors SQL sees - not sockets). Clear the wait stats and monitor for several days. If still very high number of CXPACKET waits with high average wait times - increase the cost threshold, clear the wait stats and monitor for several days, repeat until you get to an acceptable level and can show improved system performance.
One thing I schedule every day before business starts is a job to clear the wait stats. This way, I can get a clear idea of how the wait stats are accumulating through the business day - instead of skewing the values because of nightly maintenance (e.g. index rebuilds, backups, integrity checks, etc...).
To the previous posters about SQL Server Standard only using 4 CPU's - I believe that is a licensing number and does not have any relationship to the actual number of cores it will use. I have built systems with 4 Quad core processors and run SQL Server 2005 Standard and all cores were used. I have not heard that 2008/2008R2 changed that - but if so is there a link that explains that?
You guys really need to <re>read this => http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server
August 21, 2011 at 2:38 pm
Jeffrey Williams-493691 (8/20/2011)
To the previous posters about SQL Server Standard only using 4 CPU's - I believe that is a licensing number and does not have any relationship to the actual number of cores it will use. I have built systems with 4 Quad core processors and run SQL Server 2005 Standard and all cores were used. I have not heard that 2008/2008R2 changed that - but if so is there a link that explains that?
That is my understanding, and with all due respect to Gail, I did Google it and down load (as mentioned) what I found. I've also researched this before and I've yet to find an official documant that says SQL Std will use only 4 cores. This to me also flies in the face of licensing since we license by socket, and if I buy 4 socket licences and each socket has 4 cores I would be really mad if I then find it only used 4 cores.
So again, does anyone have an MS document that explictely states SQL Std can use multiple cores over 4 but a maximum of 4 sockets.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 22, 2011 at 12:55 am
awu (8/17/2011)
BOL: max degree of parallelism Optionyou can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.
Question is :
What is the "number of processors" here really mean ? It's physcial or logical ? I have box with 2 Physical Inter Xeon Processors, but both are 4 Cores, so 8 Logical Processors, do i have 2 processors or 8 processors ?
we got lots of timeouts, and CXPACKET is No 1 contributed to waitng list, so , i am thinking: should I limit the max degree of parallelism to 1 ( if we have 2 processors ) or to 4 ( if we have 8 processors) ?
Any imput will be highly appreciated !
I wouldnt be in a rush to change this as wait stats dont give the full picture and may be symptomatic of another problem. What other work have you done to establish this is your issue?
Have you enabled "lock pages in memory" as these symptoms can be linked to this?
EDIT: this whole thread has turned into a discussion on licensing and parallelism rather than actually establishing what the problem is.
August 25, 2011 at 2:47 am
For licensing of CPU's / Version read http://www.microsoft.com/sqlserver/en/us/get-sql-server/how-to-buy.aspx and then click the quick reference guide PDF link.
To quote "Per Processor License costs are the same regardless of number of cores in the processor.".
Therefore Standard supports 4 processors regardless of the number of cores and Enterprise supports 8 processors regardless of the number of cores.
Virtualised environments are different so read carefully.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply