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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-08 : 09:38:59
|
| bimal vora writes "we recently upgraded the server from dual processor p3 700 intel processor to new system with xeon p3 700 with 1mb cache x 4 processor to handle large database of 13 gb with all rest like 1 gb ecc regis.ram & 18 gb hot swappable raid 5 hard disk with raid controller.we upg.the system to xeon 4 way processor to make system faster to handle large database. we have loaded windows nt 4.0 enterprise version with sql 7.0 enterprise version.we got surprise ,performance degraded & user ( app.150 desktop) strted complaining that system went slow, database access is slow.rest all conf. remain same as earlier one , only diff of cpu & service pack instead of sp1 to sp2.we tried shriking the database to give more space to hard disk. even in task manager , we can see all 4 processors doing some task.aggreegate cpu utilisation is 35 to 40 % . we have done lan card teaming to get bandwith of 200 % . is that per cpu lic.concept comes into picture. since we have sql 7.0 enterprise version.or we have to load some lic.for 4 xeon processor utilisation..so that sql understands all 4 processors or else only 1 cpu will be utilised.is their any command where i can tell sql 7.0 to utilise all 4 processor .out of 1gb system ram nearly 950 mb is eaten up by sql ? is their any way to protect? " |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-08 : 09:54:05
|
| thx 4 abbrvtng evrythg 2 the pnt where its incmprhnsbl, now we cant undrstnd uR prblm...or...Thanks for abbreviating everything, to the point where it is incomprehensible, now we can't understand your problem...I'll take a stab at it anyway.If you have applied all of the licenses to the server and ensured that all 4 processors are being used by SQL Server, you should look at the parallel query execution settings. You may want to limit the threshold for parallel execution. You can find these settings in Enterprise Manager; right-click on the SQL Server, choose Properties, and check the Processor tab. You can also limit the RAM used by SQL Server on the Memory tab.You didn't indicate if the processors were the only thing that was changed. If you added the RAID 5 array and put your transaction logs on it, that could create an I/O bottleneck that more CPU power won't solve.Microsoft has released SP4 for SQL Server 7.0, you should apply that to your server if possible. It may solve the performance problem, it may not, but at least it will fix many other bugs that might plague you later. |
 |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-05-08 : 12:31:57
|
| Like rob suggested, I would suggest moving to SP4 if possible. I worked with two clients that noticed severe performace problems when they applied SP2. We worked with MS tech support on it for about a month. They never really pinpointed the problem, they finally gave up and sent us a copy of the SP3 beta to fix it. If you can't upgrade to SP4 for some reason, I would suggest rolling back to SP1.Jeff BanschbachConsultant, MCDBA |
|
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-08 : 13:44:51
|
| You may also want to check the drives. You should have gotten 10,000 RPM hard drives if possible.It might also be a good idea to look into another 1GB of RAM. You can limit the amount of RAM used by SQL in the MEMORY tab of SERVER PROPERTIES in Enterprise Manager. The utilized processors are visible in the PROCESSOR tab of the SERVER PROPERTIES. I usually boost the max worker threads to 256 / per processor. The default number of threads stays the same regardless of the number of processors engaged.In any event, you are expecting performance at least as good as before. I will assume you have already looked into maximizing efficiency with indexes and splitting high demand tables between file groups. Use perfmon (Performance monitor) to check the processor activity to see that all processors are responding. Also, check for blocking on databases that seem particularly slow. The performance problems don't HAVE to be directly related to your recent upgrades, and may have revealed new weaknesses in your database. |
|
|
|
|
|
|
|
|
performance downgrade for sql by upg. system to xeon