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?

  • Is the server used for purposes other than sqlserver? – TJ L Sep 15 '10 at 18:25
  • @tj111: No. This is a non-virtual Windows Server 2008 used exclusively as a database server. – Arseni Mourzenko Sep 15 '10 at 18:39

Mostly depends on if your current storage uses a battery-backed-write-cache raid card. If so, it probably won't make much if a difference considering how much of your dataset fits in memory. It will probably help nightly-report type queries more than more transactional application queries.

| improve this answer | |
  • currently, it uses a simple hard disk drive (not even a special one for servers). – Arseni Mourzenko Sep 15 '10 at 18:41
  • 1
    +1 for background (and, given MainMa's added information, I think this would suggest "yes, SSD would be faster") – danlefree Sep 16 '10 at 3:28

When you run the SQL Profiler tool are you showing obvious file I/O performance problems? Have you used the profiler to identify where your bottleneck actually is?

| improve this answer | |
  • +1. No, I haven't profiled the whole thing (only single queries during development a year ago), and yes, it's a good idea to start by identifying the bottleneck. In fact, the machine has a quite powerful CPU, so even if some queries are poorly written, it's ok most of the time. Moreover, the actual hdd seems to be solicited intensively, as shown by Resource Monitor. – Arseni Mourzenko Sep 16 '10 at 10:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.