none
SSD over the HDD in SQL

    Question

  • Hi,

    I have to design SQL Server for our non-profit with a limited budget. Can somebody give me advice if there is a visible increase in performance if HHD change to SSD? There is a big price difference between HDD and SSD and I wonder if putting money on something else in a hardware will be more efficient?

    Our DB is small-just 15-20 GB with 50 users, with no more than 10 SSL connections via https. (Rest connections internal.

    Just one MS SQL 2016 Pro Server, no mirroring, or Replication.

    Thank you in advance,

    Tuesday, March 28, 2017 2:41 PM

Answers

  • That depends. Is IO your bottleneck at this time? If so, the answer is probably yes.  If not, you likely will not notice the improvement.

    Is your workload characterize by large scan reads, typical of an OLAP workload. In this case, possibly.

    With 15-20 users and enough RAM, and an OLTP workload, you likely won't notice too much of a difference.

    • Marked as answer by alilbas Tuesday, March 28, 2017 5:35 PM
    Tuesday, March 28, 2017 3:01 PM
  • As Hilary said, it depends. 

    RAM increases the size of the disk cache, which offsets some of the IO performance issues.  How much RAM are you going to have?

    • Marked as answer by alilbas Tuesday, March 28, 2017 5:36 PM
    Tuesday, March 28, 2017 3:17 PM
  • What perf are you worried about. 10-20 GB will fot in memory so read operations can be served from memory. Modifications need to be written to transaction log before the modificaton command is compreted, though...

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by alilbas Tuesday, March 28, 2017 5:35 PM
    Tuesday, March 28, 2017 4:01 PM

All replies

  • That depends. Is IO your bottleneck at this time? If so, the answer is probably yes.  If not, you likely will not notice the improvement.

    Is your workload characterize by large scan reads, typical of an OLAP workload. In this case, possibly.

    With 15-20 users and enough RAM, and an OLTP workload, you likely won't notice too much of a difference.

    • Marked as answer by alilbas Tuesday, March 28, 2017 5:35 PM
    Tuesday, March 28, 2017 3:01 PM
  • As Hilary said, it depends. 

    RAM increases the size of the disk cache, which offsets some of the IO performance issues.  How much RAM are you going to have?

    • Marked as answer by alilbas Tuesday, March 28, 2017 5:36 PM
    Tuesday, March 28, 2017 3:17 PM
  • 32 GB, but I can double that .
    Tuesday, March 28, 2017 3:27 PM
  • What perf are you worried about. 10-20 GB will fot in memory so read operations can be served from memory. Modifications need to be written to transaction log before the modificaton command is compreted, though...

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by alilbas Tuesday, March 28, 2017 5:35 PM
    Tuesday, March 28, 2017 4:01 PM
  • Thank you Everyone!

    I will be more worried about the RAM than HDs performance.

    Tuesday, March 28, 2017 5:32 PM