I have a Microsoft SQL Server 2008 database which contains approximately 3 GB of data. The server has 4 GB of RAM. The database is used both for reading and for writing data (lots of changes). The server is rebooted very rarely.
I want to improve the read-write performance. Will a replacement of an actual HDD (no RAID) by a solid state drive improve performance?
Since over time, SQL Server is known to fill all the remaining RAM, I suppose that if I extend actual RAM to 8 GB, it will be enough to "cache" in RAM all the data used by the SQL Server. So, in my case, there is absolutely no need to buy a solid state drive, or, at least, the performance gain will be quite small. Is that right?