Justifying a memory upgrade

Solution 1:

I would recommend that you do load tests on the box before and after the memory upgrade via the application. Simulate the load the causes that degradation in performance from the user perspective, and then show the improvements after the memory upgrade (Something like jmeter could do this on a webapp). If you can't do it with the load testing of the application, maybe you can simulate the queries.

Then while doing this, you can also run the counters Farseeker recommends. The reason I think you should do it via the front end is that these are business people, and they probably won't get the whole pagefile explanation or query times etc. They should however understand application response time, since that it what everyone is looking to improve.

However, if the tests cost more than the memory itself (making the test plans, setting up servers to generate the load, etc), maybe you should just ask them to trust your judgment, or do the best tests you can.

Solution 2:

Checking if you need a memory upgrade is usually pretty simple. Some perfmon counters will tell you how many times the OS is dipping into the page file, as well as memory utilisation, pages, etc. Also, as it's SQL Server, you can also use the profiler to see how many disk reads are being done for certain queries. If the memory utilisation is anything < 90% then the SQL server is not configured optimally. Don't use the task manager for this, as it's "free" memory column includes the amount allocated to prefetch.

You need to be able to convince them (and yourself) that this is nessesary through these metrics before you even bother doing before/after tests. The before/after tests usually just back up your original proof. And if your metrics don't suggest you need more RAM then this saves the egg on your face.

However, for before/after queries, I would take a commonly used query (not too simple, something real-life), throw it into the SQL Management Studio, turn on Execution Plan (so you can make sure it's running the same plan each time, and thus you're getting valid results), and time how long they take.

Solution 3:

It may also be worth gathering some perfmon stats for page rates, disk queues etc.

Solution 4:

If you do go ahead with the upgrade, remember that not all RAM is produced perfectly. Even ECC ram can be produced with defects, although serious defects will be rarer. If possible, do an initial verification of the memory using something like Memtest86+ before installing it in the servers. Even better if you can run the same test after installation, but that means more downtime.

Your client won't be happy if your "upgrade" leads to unstable servers.