May 26, 2011 at 7:59 am
we are facing performance issues.
system functions mainly as an OLAP/reporting environment, I mean like it is onlt read only server.
Configuration is
SQL servers 2005 Ent Edition (64-bit)
Total Physical Memory: 32 GB (No chance of increasing the Memory)
MAX memory configured is 30 GB
Database size: 6,059GB (or) 6TB
As, of now the degree of parallelism on the sql server 2005 is 4.
my question is what if change the degree of parallelism is to 1.
May 26, 2011 at 4:41 pm
Similar things happened to us. It helped a lot after we did configuration changes as you said.
Why not have a try?
May 26, 2011 at 7:58 pm
john jin (5/26/2011)
Similar things happened to us. It helped a lot after we did configuration changes as you said.Why not have a try?
Sorry, but this is an irresponsible comment and anyone that makes a change like that based on this little information should not be trusted with data. Changing the max degree of parallelism on a 6TB reporting database, depending on what its doing, could have serious performance implications. Who knows, eventually it may turn out that making a change to limit parallelism seems like a good thing to try, but the original post does not contain enough information to compel anyone to make that change.
Danzz,
Have you looked at your wait stats yet?
Here is a good place to start to learn about them: http://www.brentozar.com/sql/wait-stats/[/url]
And here is a good way to pull your top wait types. You do not need to use it through PowerShell, you can just take the SQL out of the article and run it through SSMS:
http://sqlvariant.com/wordpress/index.php/2010/11/get-answers-for-paul-randals-survey-fast/[/url]
Let us know what you find.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 26, 2011 at 8:30 pm
opc.three (5/26/2011)
john jin (5/26/2011)
Similar things happened to us. It helped a lot after we did configuration changes as you said.Why not have a try?
Sorry, but this is an irresponsible comment and anyone that makes a change like that based on this little information should not be trusted with data. Changing the max degree of parallelism on a 6TB reporting database, depending on what its doing, could have serious Let us know what you find.
Oh, Agree with you, don't rush for "try" if it's critical.
I do have some study and research before I switched over to 1 from 4. And of course we are under SOX complaince and get signed off by business owners.
To be honest, at that moment I wasn't 100% sure,even I believed it will improve the performance. Fortunately it did.
To let one query to consume all the CPUs will be fast , sounds good but how about other users / Other queries? In our system I saw huge locks and waits when the configuration was 4.
To your case, as a "read only" database, it may be different, because the lock-type is different. So if the system is not allowed to "Try", you need to think carefully,and do some works as above mentioned
May 27, 2011 at 4:36 am
Especially on a reporting system I would not suggest ditching parallelism. Usually reporting systems get the most help from parallelism.
However, if you've done the research and you're getting excessive waits because of parallel queries then you need to address it. I'd start with tuning the queries and checking the system for any other types of issues, disk alignments, etc. Then, if you're still hitting parallelism waits, I'd adjust the cost threshold for parallelism up. Not too much on a reporting system though. You'll need to experiment with it.
"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
May 27, 2011 at 5:35 am
First find the bottleneck then apply changes
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 31, 2011 at 1:38 pm
Wow - amazing. Did anyone else think of doing that?
June 1, 2011 at 9:16 am
Some things missing in this performance analysis (in addition to definitely checking top wait states)...
- How many logical processors on this server?
- Are you seeing CPU pressure on the server?
- How many concurrent, active users when you see performance slowdowns?
- Is this a physical server or virtual machine?
- What is the Windows version?
- How stable is SQL memory? Are you seeing memory page faults for SQL Server? (I'm thinking leaving 2 GB for processes outside of SQL Server may not be enough).
Ronna Williams
June 1, 2011 at 12:43 pm
Danzz (5/26/2011)
we are facing performance issues.system functions mainly as an OLAP/reporting environment, I mean like it is onlt read only server.
Configuration is
SQL servers 2005 Ent Edition (64-bit)
Total Physical Memory: 32 GB (No chance of increasing the Memory)
MAX memory configured is 30 GB
Database size: 6,059GB (or) 6TB
As, of now the degree of parallelism on the sql server 2005 is 4.
my question is what if change the degree of parallelism is to 1.
1) 32GB is VERY little RAM for a 6TB database
2) 30GB is too high for max mem setting
3) hope you have some seriously fast IO 😀
4) have you done a fileIO stall or waitstats analysis?
5) do NOT change maxdop to 1. that was a silly suggestion for your system
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply