SQL Server Performance Forum – Threads Archive

Performance Tuning – Memory needed?

On this site is an article indicating that the Server that runs the SQL server DB should have 1GB of memory for every GB of size of the DB. My DB is 15GB, does this mean that I should have 15GB of memory on this server? I know more memory is better, but do you guys agree with this statememt? If not, what is enough memory for me with this DB size, also knowing the size is increasing by 1GB every 3-6 months or so? Also, I am doing load balancing and am running my overnight refresh on 2 servers to a SQL DB on one of the servers. Should I have 15GB of memory on each of the machines? This does seem like a lot of memory, but I need to justify this to mgmt. if really needed. We are experiencing performance issues and are looking at all options, both from hardware side and performance analysis side. Sorry, I posted this in the wrong forum initially, moved it here…THanks.

Can you show article link, please.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Here is the link: http://www.sql-server-performance.com/sql_server_performance_audit3.asp Look in the "Memory" section for the following paragraph: "Ideally, the amount of physical RAM in a SQL Server should exceed the size of the largest database on the server. This is not always possible, as many databases are very large. If you are sizing a new SQL Server, and assuming your budget is large enough, try to order your SQL Server with enough RAM to hold the entire size of the projected database. Assuming that your database is 4GB or less, then this isn’t generally too much of a problem. But if your database is larger than (or is expected to grow larger than 4GB) then you may be unable to easily or affordably get more than 4GB of RAM. While SQL Server 2000 Enterprise Edition will support up to 64GB of RAM, there aren’t too many affordable servers that support this much RAM."

Brad, author and Forum Administrator, said "Ideally". So, I suggest to run Performance Monitor to trace SQL Memory, disk and processor.
Also, run Profiler to find longer queries to optimize.
In short, if you do all performance tasks and still have performance issues, then you can analyze if need more Memory, CPU or I/O.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Check the following counter if it’s below 300 or not.
———–
SQL Server Buffer Mgr: Page Life Expectancy: This performance monitor counter tells you, on average, how long data pages are staying in the buffer. If this value gets below 300 seconds, this is a potential indication that your SQL Server could use more memory in order to boost performance.
———–

Argyle,
Thanks. I added the counter and will monitor. Right now it is at 15,528, but it is a very slow time period for processing. Here is the hardware of my 2 servers: Server 1 – Holds SQL Server DB
2.6GB RAM
4 X 2.8GB Processors
10GB C: Drive
135GB E: drive Server 2
1.8GB RAM
2 X 1GB Processors
8.4GB C: Drive
85GB E: drive My DB is 15GB, and I use the second server for load balancing. I know the 2nd server likely needs an upgrade, but thanks for letting me know the counters to monitor. There are so many and it is a big help. Does anyone else have specific counters they monitor and can give me a threshold to look for? THanks.

Look Page/splits.
Ideally SQL don’t move pages to swap, if you have a lot of pages (more than 50 for long time) moving to swap, you need more memory.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis, Thanks, but where can I find pages/splits? Also, what should I set my intervals for? Last night I ran using 1 second, but this morning I reuced it to 10 seconds, is this too few? Lastly, I see how to save the file, but it didn’t keep all my data. It only saved the last check. Is there a way I can write all the data to one big log file, so the next day I can use all the data with the timestamps? What format is best to save in, .tsv? Thanks.

Performance Monitor–>Memory—&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />age/sec and Page Fault/sec. I store Performance counters in csv format, each 10 seconds and export into excel, no time limit. I have 3 monitors, CPU, Memory and disk. A nice tool (freeware for 60 days) is sporlights from Quest Central. This tool give you a good picture about all critical information, include swaping, and keep 1 day history. Luis Martin Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis, I do have pages/sec, but not Page Fault/sec. What thresholds in this one should I look for and what are the danger levels? So my 10 sec interval is good, but how do you save in .CSV format? Also, are you saving each interval as a separate file? That would be insane to import one by one continually. If not, where is the setting to write all of the data with timestamps to one file that keeps growing? When you say you have 3 monitors, do you mean 3 separate windows for each server? I added all the counters into one monitor window. Can this view be saved? Sorry for the ignorance. I am new to this and the Help topics have not helped much. I will look up the Quest tool. Thanks again for the advice. It is greatly apprecited. This site is really helpful. Thanks.

In Counters Logs (Performance Monitor), once you created a new counter log, in Log Files tab, choose Text File (comma delimited).
This create a csv file in PerforLog folder (default folder). Each time you run Counters Logs, new file is created with new name (actually something like counter_name_00000XX). I choose 3 Performance Monitors (with counters log) in order to have one file for memory, one for disk and one for processor.
But it is no neccesary, you have all in one. After, said 8 hours, you can stop Counter Log and import to excel. You will have new row each 10 seconds and create a chart for each counter.
Supose you run again Counter log another 8 hours. After that you can export to first excel and then you have 2 days and so on.
Hope That Help
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis, Thanks, your the best. I have been just setting up the "System Monitor" counters. I am doing this from my Dev server and adding counters in the System MOnitor for my 2 production servers. I tried to follow your steps and you are saying to "start" the "Counter Logs", right? I see all the properties and settings there. Do you use the "Trace Log" and if so, what would it be used for? Thanks!!

No I’dont use trace log, because is too difficult to read and there is a lot of 3rd party tools much better (see ours sponsors).
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>