RAM utilization on sql server

Question

RAM utilization on sql server

Viewing 15 posts - 16 through 29 (of 29 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

sanketahir1985 (2/17/2010)
Hii
Do not change any configuration settings without having the root cause why RAM is getting utilized. ByDefault sql server uses available RAM.
2ndly run the perfmon in peak hrs with the foll counters(attached, Change the extention to .htm and import this file in perfmon)save the o/p in .csv file , post the output file if u can.
this perfmon o/p can tell if u need to upgrade RAM or not.

1) You don't need perfmon to tell if you need to update RAM or not. Every piece of information you need can be found from within sql server.

2) more importantly there is no need to check anything. You simply cannot get any performance from a 200GB database with 2GB of RAM in the server. Well, maybe if you had some SSDs to serve up the IO. :w00t:

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

Yes,I agree with TheSQLGuru.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Animal Magic SSChampion Points: 13964 More actions · Answer 3

TheSQLGuru (2/17/2010)
sanketahir1985 (2/17/2010)
Hii
Do not change any configuration settings without having the root cause why RAM is getting utilized. ByDefault sql server uses available RAM.
2ndly run the perfmon in peak hrs with the foll counters(attached, Change the extention to .htm and import this file in perfmon)save the o/p in .csv file , post the output file if u can.
this perfmon o/p can tell if u need to upgrade RAM or not.
1) You don't need perfmon to tell if you need to update RAM or not. Every piece of information you need can be found from within sql server.
2) more importantly there is no need to check anything. You simply cannot get any performance from a 200GB database with 2GB of RAM in the server. Well, maybe if you had some SSDs to serve up the IO. :w00t:

1) personally i would always check my perfmon counters first. if my disk activity (queue) is high and thats being caused by excessive paging then i know that may indicate memory pressure. Also if the page life expectancy (can you check this through sql?) is low and i know there are no reindex operations etc running then i also know this can point towards problems.

2) Yes i would probably agree, although it also depends how much of the data is used in day to day application usage. if 90% of the data is archive data then you have a chance of somewhat ok performance as the active data will be kept in the cache. i know its all ifs and buts tho without knowing the ins and outs of the environment.

3) I would also say that if you have sql on the standard memory setting it is NOT 2gb, its 2TB! it could be that the database is ok and your just starving the OS. try setting the max memory to 1.2gb or something like that and see if it helps.

Ultimitely i think you are going to need more RAM as you really are running pretty close to bone there 😛

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 4

Hi,

you can use the following to check PLE. A value of around 300 is recommended by MS as the minimum for an OLTP system

select

object_name, counter_name, cntr_value

from

master..sysperfinfo

where

object_name = 'SQLServer:Buffer Manager' and

counter_name = 'Page life expectancy'

Anamika SSCertifiable Points: 6202 More actions · Answer 5

Wow!!

fabulous pieces of information all at one place.

i thank everyone individually who is participating in this thread.

I'm on the process. will send you the perfmon o/p soon.

Muthu: I already read your Index deframentation script. but not yet implemented as other works are there in my plate. Soon I'll do it and send you the response.

thanks,

regards,

ami

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 6

For index maintenance, tere's a brilliant and very well regarded script here

http://sqlfool.com/2009/06/index-defrag-script-v30/

Works extremely well 'out of the box' but can be configured to meet any particular requirements you have.

Anamika SSCertifiable Points: 6202 More actions · Answer 7

Hi Sanket Ahir,

Can you give me a hint on counters should be attached on perfmon?

Your message says

2ndly run the perfmon in peak hrs with the foll counters(attached, Change the extention to .htm and import this file in perfmon)save the o/p in .csv file , post the output file if u can.

What are all the counters i should include and how to analyze on the output?

thanks,

regards,

ami

Anamika SSCertifiable Points: 6202 More actions · Answer 8

ooch!

sorry ignore the previous message.

i found out in the attached .txt file

thanks,

regards

ami

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Animal Magic (2/18/2010)
TheSQLGuru (2/17/2010)
sanketahir1985 (2/17/2010)
Hii
Do not change any configuration settings without having the root cause why RAM is getting utilized. ByDefault sql server uses available RAM.
2ndly run the perfmon in peak hrs with the foll counters(attached, Change the extention to .htm and import this file in perfmon)save the o/p in .csv file , post the output file if u can.
this perfmon o/p can tell if u need to upgrade RAM or not.
1) You don't need perfmon to tell if you need to update RAM or not. Every piece of information you need can be found from within sql server.
2) more importantly there is no need to check anything. You simply cannot get any performance from a 200GB database with 2GB of RAM in the server. Well, maybe if you had some SSDs to serve up the IO. :w00t:
1) personally i would always check my perfmon counters first. if my disk activity (queue) is high and thats being caused by excessive paging then i know that may indicate memory pressure. Also if the page life expectancy (can you check this through sql?) is low and i know there are no reindex operations etc running then i also know this can point towards problems.
2) Yes i would probably agree, although it also depends how much of the data is used in day to day application usage. if 90% of the data is archive data then you have a chance of somewhat ok performance as the active data will be kept in the cache. i know its all ifs and buts tho without knowing the ins and outs of the environment.
3) I would also say that if you have sql on the standard memory setting it is NOT 2gb, its 2TB! it could be that the database is ok and your just starving the OS. try setting the max memory to 1.2gb or something like that and see if it helps.
Ultimitely i think you are going to need more RAM as you really are running pretty close to bone there 😛

A) disk queue length is essentially worthless as a modern IO metric due to SANs, multiple-disk RAIDS, etc.

B) 2GB wasn't a setting from what I recall - it was the PHYSICAL RAM in the box.

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

Animal Magic SSChampion Points: 13964 More actions · Answer 10

TheSQLGuru (2/18/2010)
Animal Magic (2/18/2010)
TheSQLGuru (2/17/2010)
sanketahir1985 (2/17/2010)
Hii
Do not change any configuration settings without having the root cause why RAM is getting utilized. ByDefault sql server uses available RAM.
2ndly run the perfmon in peak hrs with the foll counters(attached, Change the extention to .htm and import this file in perfmon)save the o/p in .csv file , post the output file if u can.
this perfmon o/p can tell if u need to upgrade RAM or not.
1) You don't need perfmon to tell if you need to update RAM or not. Every piece of information you need can be found from within sql server.
2) more importantly there is no need to check anything. You simply cannot get any performance from a 200GB database with 2GB of RAM in the server. Well, maybe if you had some SSDs to serve up the IO. :w00t:
1) personally i would always check my perfmon counters first. if my disk activity (queue) is high and thats being caused by excessive paging then i know that may indicate memory pressure. Also if the page life expectancy (can you check this through sql?) is low and i know there are no reindex operations etc running then i also know this can point towards problems.
2) Yes i would probably agree, although it also depends how much of the data is used in day to day application usage. if 90% of the data is archive data then you have a chance of somewhat ok performance as the active data will be kept in the cache. i know its all ifs and buts tho without knowing the ins and outs of the environment.
3) I would also say that if you have sql on the standard memory setting it is NOT 2gb, its 2TB! it could be that the database is ok and your just starving the OS. try setting the max memory to 1.2gb or something like that and see if it helps.
Ultimitely i think you are going to need more RAM as you really are running pretty close to bone there 😛
A) disk queue length is essentially worthless as a modern IO metric due to SANs, multiple-disk RAIDS, etc.
B) 2GB wasn't a setting from what I recall - it was the PHYSICAL RAM in the box.

Yeah your right it is 2gb in the box, however one post said the memory settings were on default so sql is going to try and take as much of that as it can, hence starving the OS.

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 11

I agree with the SQL and OS are likely battling for RAM.

Keep in mind anything else that may be running on the box when trying to set memory values.

For example, Reporting Services / IIS could be running on the box.

Leaving 800 MB for the OS might not be quite enough, as long as it is test, you can try a few setttings to see what works best.

I could have missed it, but if you are on 32 bit platform, you may want to see if you have any options to upgrade to x64 bit. It could be your hardware is capable, but a 32 bit OS was loaded in.

Greg E

Anamika SSCertifiable Points: 6202 More actions · Answer 12

Hi All,

Thanks for the great support.

Now, how do I set memory for sql server and OS?

the max option in sql says 2147483647 (in MBs)

but the physical memory in the server is 2GB.

I'm bit confused.

Regards,

Ami

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 13

Books On Line will outline setting the memory limit.

There are several good topics around this.

Understanding more about this will help you out more than just telling you what to change.

You set memory for the SQL instance, leaving room for the OS.

Greg E

Animal Magic SSChampion Points: 13964 More actions · Answer 14

Anamika (2/23/2010)
Hi All,
Thanks for the great support.
Now, how do I set memory for sql server and OS?
the max option in sql says 2147483647 (in MBs)
but the physical memory in the server is 2GB.
I'm bit confused.
Regards,
Ami

You just type in the amount you want to use. 2048 would be 2gb but as thats the total amount available you dont want it that high. To split it half and half use 1gb (1024).

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 15

1) I recommend getting a book or two on administering sql server, or find a course or two to take.

2) with only 2GB on the server, if NOTHING else is running on it but the sql server relational engine (other than OS) I would say you could start at 1.5GB max for SQL Server. Go down from there if other stuff is running on the box.

3) I also re-recommend at least a few hours of consulting to help make sure your system is configured properly and other critical things are addressed.

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