SQL SERVER2005 and Server2003 CPU Utilized 100%

>> 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

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.

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.

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

>> 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

check the screen shot. I dont find your point 3
sql2005.doc

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

see the screen shot
sql2005-2.doc

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

>> 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.

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

>> 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

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

>> /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

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

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.

>> 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.

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

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.

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?

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.

Are you running the report through Crystal Server?
from our workstation we are running a report which made in crystal report writer-11

Can you show us the content of the SQL query that Crystal is trying to execute?

done.

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.