Deciding whether adding RAM will help performance

Question

Deciding whether adding RAM will help performance

webrunner

SSC-Dedicated

Points: 31827
More actions
February 1, 2010 at 4:17 pm

#386223

Hello,
I am working on a server that is having performance issues. I'm trying to set up a plan for going over the various possible things we can check, and if we confirm signs of that problem, what possible remedies we can provide. I have a hunch that more RAM will help at least part of the performance problem, but I feel like I need a better grasp of what to consider when deciding whether to request a RAM increase.
Specifically, I'm seeing large swings in Page Life Expectancy and % Disk Time. Here's some information (Perfmon stats are over 1 week).
Physical RAM: 16 GB (64-bit SQL 2005 set to use between 10 GB min and 12 GB max RAM)
DBs: 1 (there's a small supplemental db, but it is tiny and contributes little to the processing load)
DB size ~25 GB, 80 compatibility mode
Page Life Expectancy: Avg: ~23800 , Max: 66340 , Min: 17
Avail MBytes Avg: 2423, Max: 8164, Min:3
PhysicalDisk(_Total)\% Disk Time Avg: 22, Max: ~5900 Min: 0.01
These counters seem to indicate some memory problems over the course of a week. I can say that I think there are other issues (probable need to archive some data, possible indexing issues, possible design and SQL code issues), but I am looking to decide whether it is worth it to request more RAM if I see that RAM would help (i.e., if there is a real RAM problem), or whether the improvement from RAM will be so small that it will look like the money was wasted.
I haven't monitored Pages/sec yet this time around - I read somewhere that this counter is not necessarily reliable. But I can monitor it if you recommend I should. I can also provide some info on other counters, or run monitoring again including other counters if you think I should cross-check the values to rule in or rule out RAM as an issue. And of course if you have other questions, I'll try to answer those as best as I can.
I guess what I am asking is how to determine the single worst problem if there is one, and how to balance that against how hard that worst problem would be to fix, and should I compromise and possibly fix a smaller problem in a way that will improve things while we work on the other issues.
(Also, as a side note, I am looking for general advice on a methodical approach to diagnose the performance problems. The available data from tracking and tracing in SQL Server is overwhelming, and I need help coming up with a feasible plan that will help me rule in or rule out possible causes one by one. That is a tall order, I know, which is why I am asking only for general advice, such as pointers to good checklists and methodologies.)
Thanks in advance for any help!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 1

I'm not a hardware person by any means but if the server can actually take more memory and SQL Server can actually use it (meaning that both the OS and SQL Server are of the correct editions), then adding extra RAM will never hurt.

However, adding extra RAM may not always help. Like anything else, "It Depends". For example, moving from Standard Edition to the Enterprise Edition on a brand new fire-breathing box with oodles of RAM and an I/O system to envy did absolutely nothing for one of my previous companies I worked for. It was cruddy code that needed to be fixed. Sure, the upgrades made some of the cruddy code that took 24 hours to usually fail run a bit faster on occasion and it would even run on occasion without failure (rarely), but the real problem was the cruddy code.

If you think that's probably not the case, you're probably wrong. 😉 We rewrote the code and what used to take 24 hours to run suddenly ran in only 11 minutes. The first time the testers ran it, they thought it had failed because it ran so fast. That was almost 3 years ago. To date, the code has never failed, either.

Additional RAM is certainly worth the investment but the real performance gains will be when your company makes the investment to identify and start whittling on some of the bad code.

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

webrunner SSC-Dedicated Points: 31827 More actions · Answer 2

Thanks, Jeff,

Yes, I suspect code and database design (including indexes) are probably the bigger problem, but I want to be able to quantify the severity compared with the (at least short-term) benefit of more RAM.

I guess I will need to see how much the RAM will cost, and then either back away from increasing RAM right now in favor of identifying the actual problem code. Do you have any suggested strategies for finding and documenting the "smoking guns" of code and db design issues? I would love to get the kind of gain you got in the example you mentioned, but I'm not sure where to being. I've run the "most expensive queries" type reports and not known how to start improving the worst offenders. Should I examine the execution plans first? Ask whether a given query is needed, etc.?

Thanks again for any help, and sorry if I sound a bit aimless. I just need some guidance on how to make the next steps.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 3

I'm not sure on how to demonstrate the benefit of additional RAM without actually buying it and show how it does or does not make an improvement.

So far as finding the "smoking guns"... it may not be a smoking gun. It may be a "steaming pile". 😉

Finding the most expensive queries is certainly a start. Fixing them, as you're finding out, may be an entirely different story because code is frequently either poorly documented or not documented at all and knowing what the code is supposed to do is about 90% of the battle. Using the execution plans may help you determine if indexes are being used correctly and maybe how to optimize a join here and there but they may not help with the root problem. Cruddy code.

I recently ran into a problem bit of code where there were 3 self joins on a single table. All 4 instances of the table manifested themselves as Index Seeks in the execution plan which is supposedly very close to computational nirvana. The code took 5 to 8 minutes to run.... to return 15 rows! I had a hell of a time convincing folks that even though it was using all index seeks, that it still needed to be rewritten. The rewrite brought it down to sub-second times.

I'm not trying to be elusive but sometimes you just have to start over with code or just try a couple of experiments with the highest cost sections of the code. It's normally not easy but it's always worth it.

If what you say is true and you don't know where to begin to optimize code, it might be very worthwhile to have a consultant who specializes in query tuning/rewriting come in and do some should-to-shoulder time with you.

I will tell you that writing set-based code does NOT mean trying to do something in a single query. Sometimes it takes a little "Divide'n'Conquer" code using a combination of "tricks" that may include storing small interim results in Temp Tables instead of try to do 30 table joins. The only way to learn those tricks is to have someone show you. Like I said, it may be worthwhile to have a consultant come in and show you how.

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

krayknot SSCertifiable Points: 7998 More actions · Answer 4

Yes adding RAM will help but that will also depend on the cache it will provide.

RAM has its own limitations.

Better you improve the application model

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Paul White SSC Guru Points: 150467 More actions · Answer 5

To make sure you improve the thing that will provide most benefit, base your enquiries on This Microsoft Best Practices Article. This is the Waits and Queues methodology - and it's great for this sort of thing.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

webrunner SSC-Dedicated Points: 31827 More actions · Answer 6

Thanks, everyone, for your help. I think the all of the advice points to not rushing to request the RAM right away, but spending some more time analyzing the performance and coming up with a set of recommendations - either RAM only, or a combination of the solutions suggested, possibly including or excluding RAM.

I guess it's true again that "it depends." :unsure:

Thanks again.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Paul White SSC Guru Points: 150467 More actions · Answer 7

It does indeed depend - but on the other hand more RAM never hurt a system 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

webrunner SSC-Dedicated Points: 31827 More actions · Answer 8

Paul White (2/2/2010)
It does indeed depend - but on the other hand more RAM never hurt a system 🙂

That reminds me of a joke I tell myself. No doubt I'm not the first person to think of it:

Question: "Is the answer always, 'It depends'?"

Answer: "Well, it depends."

Thanks again!

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Peter Samson SSC Veteran Points: 216 More actions · Answer 9

Find and fix the root cause. Even if you add more RAM, who's to say it will be enough to be adequate? At some point the responsible person is going to be mad after attempting to spend their way out of a problem and not seeing the expected resolution.

The other approach is to offer up the alternative of adding more RAM, but include a strong caution that it is only a band-aid that may not resolve the underlying problem(s).

Peter

http://seattleworks.com including my blog The SQL Janitor