What is the memory module on a RAID card needed for?

The memory is used for read and write cache which improves the performance of the storage. The basic rule when it comes to cache is buy as much as you can afford. The more cache you have the better the performance of the disks will be as data can be written to the memory if the disks aren't in the correct position.

You'll also want to make sure that the RAID card has a battery built on to protect the data in the write cache if there is a power outage.

The purpose of RAM on a RAID controller is to provide a level of cache to allow write operations to hit stable storage without incurring the latencies involved with rotating disks.

The RAM on the controller can usually be split between read and write cache. A common ratio is 75:25 write:read.

Write caching is helpful because applications can write to low-latency controller memory with no delay. That data then gets flushed to disks, which are an order of magnitude slower.

Read caching helps keep hot or frequently-used data on the controller's memory so that requests don't need to be fetched from slower disks.

For your HP ProLiant server and Smart Array controller, you will want a larger cache and at battery-backed or flash-backed unit for it (aka. BBWC or FBWC). Those improve performance by enabling the write accelerator tied to the cache module. The write accelerator is disabled where there's no BBWC or FBWC available.

See the following questions for specific scenarios and use-cases.

Non-volatile cache RAID controllers: what kind of protection is there against NVCACHE failure?

Incredibly low disk performance on HP ProLiant DL385 G7

Aside from the read/write cache, without the memory, some RAID controllers disable some functions like array expansion. It's definitely better to have it, than to not.