Do I need more memory?
-
Hi there!
I have some SQL servers running around 15 databases which at start had 8 GB of RAM, they now have 12 GB RAM each.
The specs roughly are:
- Intel Xeon 5110 @ 1.6 GHz
- 12 GB RAM
- 12 x 73 GB 15k RPM SAS harddrives in RAID 10
- Microsoft Windows 2003 Server x64
- Microsoft SQL Server Standard Edition (64-bit)
At start they were using all 8 GB of RAM which is why I upgraded them to use 12 GB instead. After a week or so, they were using 12 GB of RAM.
So my question is, do I need more RAM again? The CPU which is quad core isn't using 100 %, it's fine actually and the disk aren't doing much either. Any help or advise is welcome since I'm no pro at this, all is just experience from time to time.
-
Check Buffer Cache values. Also, how big are the Databases?
The DB shouldn't take more than 80% of the available RAM.
-
Are you thinking of the performance monitor SQLServer: Buffer Manager -> Buffer cache hit ratio? I've read somewhere that it should be above 90. My average is 99,86 atm.
The databases have an average size of 2 GB each. Actually I have 13 databases running on it, total space on disk is 26,2 GB. I also have other small databases, which only is used as a log, so I don't select from them but only insert.
-
check buffer cache hit ratio and also look at Lock Pages In Memory option in sql server. There seems to be memory pressure.
Let me know how your DB's are configured and Do they hold BLOB type Data.
Maninder
www.dbanation.com -
Well again the SQLServer:Buffer Manager -> Buffer cache hit ratio some hours later have the values 99,86 (average), 99,82 (minimum), 99,89 (maximum).
The Lock Pages In Memory (had to look it up on Google :)) is disabled on the server (which is default I see?).
My databases are configurered as follows:
- They use Full-text indexing (for 1 search of around 50.000 - 250.000 records).
- They have unrestricted growth of 10 % (both mdf and log files)
- Recovery model is FULL
- Compatibility level is 80 (not 2005 combatible yet)
- Auto Close FALSE
- Auto Create Statistics TRUE
- Auto Shrink FALSE
- Auto Update Statistics TRUE
- Auto Update Statistics Asynchronously FALSE
Rest is default settings/options.
The only BLOB datatypes we use in some tables is NTEXT. We don't use any binary, images, videos etc.
-
I think Microstf recommened enabled the 'lock pages in memory' option for Standard edition on the 64 bit platform. There are some knowledge base articles on this
Gethyn Elliswww.gethynellis.com
-
Lock Pages in memory:
This security setting determines which accounts can use a process to keep data in physical memory, which prevents the system from paging the data to virtual memory on disk. Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM).
Default: None.
I don't like the bold part of it and I'm running with 2 production servers. Atm it's SQL Server 2005 using all the availiable memory:
-
check out this link:
http://blogs.msdn.com/slavao/archive/2005/08/31/458545.aspx
It may or may not be neccessary...
Are you getting "SQL Server memory page out..." error in the error log?
Gethyn Elliswww.gethynellis.com
-
Did you apply any patches or is it a base install.?
Check the latest Cumulative Updates, I remember there is somewhere a Patch that addresses this issue, i had the same for ANALYSIS SERVICES, USed to comsume all the resources and HALT the processes.
After the path it runs like charm.(almost)
Maninder
www.dbanation.com -
Is there only one sql server instance running on this machine? If there are more than one instances then you need to find the instance consuming most of the memory.
In case there is only one instance on this machine then try finding out the process based memory consumption. There are chances of bad coding practices, indexes structure on tables, applications selecting more data than they actually need etc. etc.
Manu
-
jjssilva (6/26/2008)
Check Buffer Cache values. Also, how big are the Databases?The DB shouldn't take more than 80% of the available RAM.
So... what do you do when you run against a Tera-byte database??? 😉 Or, do you mean the database shouldn't take more the 80% of the disk space on the system (instead of Ram)?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the tips so far. I don't at this point have any problems on the server, all databases are running fine and performance is good. But I'm not that experienced when it comes to memory and just see that whenever more RAM is added, it gets eaten :).
And I only run 1 SQL instance.
-
Am I missing something? If max server memory is unlimited, and your database is large, or you don't have much stored procedures, or you have a lot of connections etc. etc. SQL will get as much memory as possible.
As long as you max server memory is unlimited, and you'll meet one of these criteria, adding more memory will be used by SQL
Wilfred
The best things in life are the simple things -
Jeff Moden (6/26/2008)
jjssilva (6/26/2008)
Check Buffer Cache values. Also, how big are the Databases?The DB shouldn't take more than 80% of the available RAM.
So... what do you do when you run against a Tera-byte database??? 😉 Or, do you mean the database shouldn't take more the 80% of the disk space on the system (instead of Ram)?
You tell me, I'm not a pro. 😀
-
jjssilva (6/27/2008)
Jeff Moden (6/26/2008)
jjssilva (6/26/2008)
Check Buffer Cache values. Also, how big are the Databases?The DB shouldn't take more than 80% of the available RAM.
So... what do you do when you run against a Tera-byte database??? 😉 Or, do you mean the database shouldn't take more the 80% of the disk space on the system (instead of Ram)?
You tell me, I'm not a pro. 😀
Heh... gave you the chance to fix what you said... 😉
Obviously, a DB can be thousands of times larger than available RAM (memory). RAM and disk space are not the same thing. If it were said that a "DB shouldn't take more than 80% of the available disk space", then I might agree.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply