SQL SERVER2005 and Server2003 CPU Utilized 100%

mahmood66
mahmood66 used Ask the Experts™
on
when we are processing a crystal report made report and while running the query we found in server Task manager that CPU usage goes high and some time touches 100% also. is there any tune up we can do which will help us to lower this usage. we are using HP ML370 server with 2GB RAM.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> we are using HP ML370 server with 2GB RAM.

Why this much Lower RAM / Physical Memory.
SQL Server Machines ideally need heavy memory to perform better.

Ok.. With this limitation, you can do one thing to get better performance.

1. Right Click your Server in SSMS --> Properties --> Memory
2. Set Min Server Memory to 1 GB and Max Server Memory to 1.5 GB
3. Pre-grow the tempdb file size to 2 GB
4. Restart this server

You will find some remarkable difference.
Hope this helps
Richard QuadlingSenior Software Developer

Commented:
Is the report gathering data from 2 different servers? If so, Crystal is almost incapable of doing this.

Say you are doing a join across 2 databases. The left produces, say 1000 rows and the right produces 1000 rows, but the join would produce just 10. As Crystal has to process each side separately, it is doing a LOT of work.

A LOT.

Author

Commented:
sorry I checked the memory again. it is 3.82 showing. means 4GB.
in SSGM the server min memory was 0 and max is 2147483647
I dont find your point 3.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Ok.. Then set your Min and Max server memory to 1.5 GB and 3.5 GB respectively.

>> I dont find your point 3.

Steps to achieve it:

1. Right click Tempdb in SSMS.
2. Choose Properties --> Files
3. Set Initial Size to 2 GB and Autogrowth to 500 MB.

Hope this helps
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> in SSGM the server min memory was 0 and max is 2147483647

Set Min Server Memory to 1500 and Max to 3500

Hope this helps

Author

Commented:
check the screen shot. I dont find your point 3
sql2005.doc
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Should have been bit more clearer in my explanation:

1. Expand Server --> Databases --> System Databases
2. Right click Tempdb in SSMS.
3. Choose Properties --> Files
4. Set Initial Size to 2 GB and Autogrowth to 500 MB.

hope this helps

Author

Commented:
see the screen shot
sql2005-2.doc
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Now it was set correctly..

Restart the server and check whether your problem was resolved or not.
Hello,

Have you set the /3GB and /PAE switches in your boot.ini file?


JJ
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Have you set the /3GB and /PAE switches in your boot.ini file?

jjmck, Since we have only 4GB RAM these configurations are not required.
And even if we do those, we wont be getting performance improvement out of that.

Author

Commented:
yesterday I restarted and left the office as it was too late. but I notice that situation is still same.
following is my Boot.ini
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /noexecute=optout /fastdetect
>>jjmck, Since we have only 4GB RAM these configurations are not required>>

Not correct. You need the /3GB switch if you have more than 2GB or RAM. The /PAE switch will allow Windows to see the full 4GB or RAM. Please see the following document - http://support.microsoft.com/kb/274750

JJ
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> You need the /3GB switch if you have more than 2GB or RAM.

Minor Correction.. It was 4 GB and not 2GB of RAM.
Kindly refer the below reference for detailed info.

http://technet.microsoft.com/en-us/library/ms179301(SQL.90).aspx
You mean you only have 2GB of RAM in the server and not 4GB like you previously stated? If so that is your problem right there. You aren't going to be able to do much with SQL 2005 if you only have 2GB.

JJ
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope this clarifies more:

"SQL Server supports Address Windowing Extensions (AWE) allowing use of physical memory over 4 gigabytes (GB) on 32-bit versions of Microsoft Windows operating systems. Up to 64 GB of physical memory is supported."

http://msdn.microsoft.com/en-us/library/ms187499(SQL.90).aspx

Hope you would be aware of 4-Gigabyte Tuning test aka 4GT.

"4GT can be used with or without PAE. However, some versions of Windows limit the maximum amount of physical memory that can be supported when 4GT is used. On such systems, booting with 4GT enabled causes the operating system to ignore any memory in excess of the limit."

More info on 4GT here:

http://msdn.microsoft.com/en-us/library/bb613473(VS.85).aspx

Hence I would recommend disabling PAE and AWE in 4 GB Machine.
Hope this clarifies better.
AWE is not needed on a sysstem with 4GB or less of RAM but I would use the /3GB and /PAE switches in the boot.ini. /3GB is definately needed for any system that has more than 2GB of RAM. While /PAE is technically only needed for system with over 4GB of RAM, the reality is that most hardware does not present the full 4GB to the OS unless the /PAE switch is present.

JJ
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> /3GB is definately needed for any system that has more than 2GB of RAM. While /PAE is technically only needed for system with over 4GB of RAM, the reality is that most hardware does not present the full 4GB to the OS unless the /PAE switch is present.

Required if SQL Server is the only one application running in the machine.
If you have other applications running, then this might harm in the other way around.
That depends on the type of applications you are running. If the other applications require a lot of kernel mode memory, then yes, the /3GB switch can be detrimental to performance. If the app requires a lot of user mode memory then the /3GB switch will improve performance. If you are running other apps besides SQL, you should always consult the application developer to determine the optimal memory setting for that app. Personally, I wouldn't pair an app that requires a lot of kernel mode memory with SQL as you have two conflicting memory requirement. You are better off running the app on a seprate server.

JJ
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
jjmck,
   I think we need to get some more inputs from mahmood66 on this aspect to come into consensus..
Hope you agree with me..
Yes, I agree. We need to know what (if anything) else is running on this server.

JJ

Author

Commented:
We are developing the application on VB.net + Crystal report writter-11 and I am useing SAGE-PASTAL Evolution. I notice that if I run any report made in CR11 then this cpu usage goes very high. so may be I need some tune up in CR11.
but my RAM of server is 4GB. kindly also let me know what line I should type in boot.ini, I already typed you my boot.ini in earlier converstion.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I notice that if I run any report made in CR11 then this cpu usage goes very high.

1. Does Crystal Reports run in the same server as that of SQL Server.
2. Is the query fine tuned ? If not then it needs to be tuned for better performance.
3. Does any other application other than SQL Server, CR11 are running in that SQL Server machine.

Kindly provide the above inputs so that it would help us help you better.

Author

Commented:
CR-11 report running from workstations. we are not using any other application than SAGE-pastal in this server. but this is also a server for Kaspersky-Anti virus.
I would recommend you modify your boot.ini as follows and see if that improves performance.

multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /noexecute=optout /fastdetect /3GB /PAE
JJ

Author

Commented:
still situation is same, may be it make little difference which is not so vital.
may be I need to increase the RAM or SERVER even??
what you recommend.
Richard QuadlingSenior Software Developer

Commented:
If you take the query from Crystal and run it via EM or QA from your workstation, do you get a similar response?

Can you show us the SQL that is being used?

Are you running the report through Crystal Server?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Use perfmon to find out which resource is causing the contention or problem:

http://www.mssqltips.com/tip.asp?tip=1039

Either it might be RAM or Physical Disk or Network cards, but you can find it out using this.

Author

Commented:
Are you running the report through Crystal Server?
from our workstation we are running a report which made in crystal report writer-11
Richard QuadlingSenior Software Developer

Commented:
Can you show us the content of the SQL query that Crystal is trying to execute?
Senior Software Developer
Commented:
Is it 2 seemingly unconnected queries?

Author

Commented:
done.
Richard QuadlingSenior Software Developer

Commented:
I've come across this issue a lot.

My solution is to create a linked server and setup views on one of the servers to access the data from the other.

Then, as far as Crystal is concerned, all the requests come from a single server and you shouldn't get the utilisation problems.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial