Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqldba20
Posting Yak Master
183 Posts |
 Posted - 2008-08-11 : 14:01:07
|
| Folks:Over the weekend we migrated our SQL Server to a new hardware because we had disk IO issues on old hardware because of the disk. On the new hardware we went to RAID 1+0 for data and logs and RAID 1 for tempdb. In terms of CPU, on the new hardware we went from 'Dual Core (4 CPUs)' to 'Quad Core (8 CPUs)'. Now, I am seeing some bottle necks on CPU and not Disk IO's. Do I need to set the MAXDOP (MAx Degree of Parallelism)? If so, how much ? The current config it displays as 0 and I believe that means 8.Thanks ! |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-08-11 : 14:41:16
|
| How are you measuring the CPU Bottleneck?Did you update statistics when you switched hardware? |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-11 : 14:47:09
|
| Did you rebuild highly fragmented index(this will update stats as well)? Find TOP CPU intensive queries with standard reports. We usually go with (n-1) settings for Max DOP but it is recommended to go with default. |
|
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2008-08-11 : 15:04:11
|
| I did the full DBCC DBREINDEX on all the objects and all databases after the hardware move. sodeep: Does 0 means default (8) ? IF I have to go by your setting do I need to set 7 in the 'max degree of parallelism'?Thanks! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-11 : 15:12:18
|
| I wouldn't recommend in Production Environment.? You have to test with it. Also Did you check standard reports for CPu intensive queries? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-11 : 15:14:30
|
| Yes 0 means 8 in your case and (n-1) =7 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-11 : 23:00:50
|
| >> Do I need to set the MAXDOP (MAx Degree of Parallelism)? Did you see wait in those queries? What's wait type? I'll test query with maxdop hint first before changing server option. |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2008-10-02 : 22:42:06
|
| first find out which queries are getting benefit from paralel operation and try to specify hints. When you run queries look for cxpacket waittype. Because setting maxdop at the server level may cause other queries not run optimally. |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2008-10-02 : 22:48:52
|
| when you use maxdop hint in queries look for cxpacket waittimes and run multiple test with various maxdop number, minimum cxpacket waittime means that value of maxdop is optimal for the query. Try to run multiple test before assinging any nummber to maxdop to ensure accuracy. |
|
|
|
|
|
|
CPU Bottlenecks