Performance Issue

Question

Post reply

Performance Issue

aksh.2411

Right there with Babe

Points: 762
More actions
April 11, 2011 at 1:04 am

#236495

I have 32 bit Windows server 2003 with memory of 8 GB.SQL Server 2005 is running on standard edition with no service pack.
I am facing performance issue.I have noticed that SQL Server background processes taking higher CPU time.
Memory usage for SQLSERVR.EXE is 1693692K in task manager.
I have set min memory as 512MB & max memory as 5 GB.
Any kind of help will be appreciated.
Thanks in advance
Akshay G.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

B.Nitin SSC Journeyman Points: 85 More actions · Answer 1

Hi Akshay,

Few Questions

1.Why are the Service Packs not applied to the box

2. Is AWE enabled on the Server configuration

Regards,

Nitin

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

No doubt about it: apply at least SP3, better even SP4

There are reasons why a SV releases service packs !

What edition of Win2003 are you using (std / ent edtn) ?

If you want your instance to use more than 1.7GB RAM, you'll need to do two actions:

1) add /3GB /PAE in your BOOT.INI file an reboot the server

ref: http://www.sql-server-performance.com/articles/per/awe_memory_sql2000_p1.aspx

2) enable AWE for your sql instance (sp_configure)

ref: http://technet.microsoft.com/en-us/library/ms175581%28SQL.90%29.aspx

http://support.microsoft.com/kb/283037

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

aksh.2411 Right there with Babe Points: 762 More actions · Answer 3

Nitin,

I have not applied Service pack3 or 4 yet because i m waiting for management approval for the same.

I have not enabled AWE.

aksh.2411 Right there with Babe Points: 762 More actions · Answer 4

I am using Win server ent edition

Thanks & regards,

Akshay G

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

Without the switches and AWE, as mentioned in my previous post, sqlserver will not be able to use more than 1.7GB of your ram.

Push your managers approval for the latest SP !!!!

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

BTW : MS announcement dd Jan 2011:

"End of Mainstream Support for SQL Server 2005 and End of Service Pack Support for SQL Server 2008 SP1"

http://blogs.msdn.com/b/sqlreleaseservices/archive/2011/01/27/end-of-mainstream-support-for-sql-server-2005-and-end-of-service-pack-support-for-sql-server-2008-sp1.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

The plan cache in SQL 2005 RTM can use over 75% of your total RAM too. Gets ugly.

If your system is a production one and you quickly need to get running optimally you really should get a professional on board for a day or so to give your server and app(s) a quick review and get things set up properly. Nothing personal here, but you are asking questions that you should not be asking if you are managing a production system.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

john.daisley Grasshopper Points: 16 More actions · Answer 9

ALZDBA (4/11/2011)
No doubt about it: apply at least SP3, better even SP4
If you want your instance to use more than 1.7GB RAM, you'll need to do two actions:
1) add /3GB /PAE in your BOOT.INI file an reboot the server
...

be very careful with that 3gb switch as it reduces memory available to the kernel and can cause the server not to boot. The more memory you have in the box the more likely the 3gb switch is to cause issues. MS recommend not to use this with 16gb or more of ram but it can cause problems with much lower amounts. http://technet.microsoft.com/en-us/library/cc784475(WS.10).aspx

You dont need the 3gb switch for AWE, just enable PAE with the /PAE switch in boot.ini

Regards

John

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 10

john.daisley (4/15/2011)
ALZDBA (4/11/2011)
No doubt about it: apply at least SP3, better even SP4
If you want your instance to use more than 1.7GB RAM, you'll need to do two actions:
1) add /3GB /PAE in your BOOT.INI file an reboot the server
...
be very careful with that 3gb switch as it reduces memory available to the kernel and can cause the server not to boot. The more memory you have in the box the more likely the 3gb switch is to cause issues. MS recommend not to use this with 16gb or more of ram but it can cause problems with much lower amounts. http://technet.microsoft.com/en-us/library/cc784475(WS.10).aspx
You dont need the 3gb switch for AWE, just enable PAE with the /PAE switch in boot.ini
Regards
John

Good remark, John .... for systems of 16GB or more.

OP stated

"I have 32 bit Windows server 2003 with memory of 8 GB"

The URLs I provided explain the RAM sizes and 3gb/pae combinations to be used.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

john.daisley Grasshopper Points: 16 More actions · Answer 11

ALZDBA (4/15/2011)
OP stated
"I have 32 bit Windows server 2003 with memory of 8 GB"
The URLs I provided explain the RAM sizes and 3gb/pae combinations to be used.

@alzdba - You can have problems with 8gb of ram too! I have seen a Win 2003 server with 6gb fall over with this setting. The knowledgebase article I posted a link to (http://technet.microsoft.com/en-us/library/cc784475(WS.10).aspx) states:

Even though the memory manager imposes a hard limit of 16 GB when both 4GT and PAE X86 are enabled, it is possible to encounter problems with lesser amount of memory, such as 8 GB or 12 GB. Therefore, the kernel should be given as much memory as possible.

That is why I made this point and believe it is relevant to the OP.

Regards

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 12

john.daisley (4/15/2011)
ALZDBA (4/15/2011)
OP stated
"I have 32 bit Windows server 2003 with memory of 8 GB"
The URLs I provided explain the RAM sizes and 3gb/pae combinations to be used.
@alzdba - You can have problems with 8gb of ram too! I have seen a Win 2003 server with 6gb fall over with this setting. The knowledgebase article I posted a link to (http://technet.microsoft.com/en-us/library/cc784475(WS.10).aspx) states:
Even though the memory manager imposes a hard limit of 16 GB when both 4GT and PAE X86 are enabled, it is possible to encounter problems with lesser amount of memory, such as 8 GB or 12 GB. Therefore, the kernel should be given as much memory as possible.
That is why I made this point and believe it is relevant to the OP.
Regards

No problem.

I just didn't encounter it on our 32bit boxes.

It is very important to set max server memory configuration for the sqlinstance, so it leaves enough ram for the other processes.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me