April 19, 2011 at 9:26 am
Hi
I want to know about Server Side Tuning on SQL2k5. My Sqlserver configuration are given below:
OS:Windows 2k3 Ent edition With latest Service Pack
SQL server : SQL 2K5 Ent With Latest Service pack
Processor: 2.40 Ghz 8 cpu
Memory: 16 GB
System Model: VMware virtual Platform.
What would be the "max degree of parallelism" value and do i require to enable any affinity mask values or not. Because in my application there is no parallelism hint given.
Regard,
Vinod K
April 20, 2011 at 4:58 am
I would not use affinity unless you've already identified a need to share resources with some other service. Just leave it alone.
I would also leave the max degree of parallelism alone on most servers unless I had identified an issue. But, the one thing I would recommend changing is the cost threshold for parallelism. The default is 5, which is way too low in my opinion. For an OLTP system, I'd consider setting it to somewhere between 30 & 50. Start with 35 and see how things go. For a warehouse or reporting system, I'd probably move it up to 20.
"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
April 20, 2011 at 5:08 am
These should give you at least an overview
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
April 21, 2011 at 7:38 am
For one of the first times I can ever think of I will differ from Grant! I never leave maxdop at 0. On your config (with LOTS of unanswered questions about config, etc) I would set it to 4 to start, possibly dropping to 2. And for CTFP I would use 10 for oltp and 20 for olap boxes.
Max Sql server memory I would set to 12.5GB.
I note you are asking questions that indicate you should probably get a professional to help you review your system if you want optimal performance.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 23, 2011 at 12:17 am
Thank you very much my dear friends.
Will do the tuning as per your recommendation and update you incase of any issue .
Regards
Vinod K
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply