0

I'm looking to buy an SSD for a SQL Server computer, what benchmarks should I be comparing? High/low queue depth? Random/sequential transfer rates? IOPS?

4 Answers 4

2

IOPS pretty much.

Queue depth is irrelevant - basiaclly some higher end systems work better with more stuff queued. This wont give you any sensible information without context.

Transfer rates depend on IOPS - someone has to read / write the stuff first.

At the end, random IO, always runs down to IOPS. IOPS directy transfer to transfer rates when needed. Note that SQL Server ALWAYS does 64k IOs or multiple of that for enterprise. So, 4kb etc. is not relevant. From that you can pull all other information. Response time too slow, queues up - not enough IOPS available.

5
  • True; small (<<64K) IOPS generally heavily depend on queue depth. Setting your queue depth=4 on any given block device is not going to get you any performance. The queue depth (driver, hba, OS dependant etc.) defaults should at least yield something usable. For tuning I'd always suggest looking at queue depth. But as said, with up to 64KB (32 pages) of IO size the depth is not that relevant any more.
    – pfo
    Apr 13, 2012 at 12:50
  • But SQL Server does not do small reads either. All IO Is in 64kb increments - an extend of 8 pages of 8kb. Plus - queue depth is ONLY relevant in context. I once knew a SAN where they ran 24 discs because each disc go t a 200+ queue length - but that was only sensible on that hardware. EXTREMELY hard to properly analyze. IOPS budget numbers make sense without context.
    – TomTom
    Apr 13, 2012 at 13:19
  • That is not true, since IOPS Numbers are for a given queue depth and maximal latency - usually say < 20ms.
    – pfo
    Apr 13, 2012 at 13:27
  • Something doesn't make sense to me: Queue depth is a property of the benchmark, not of the result. That is, drive A can sustain 40K IOPS at Queue depth 1 and 100K IOPS at queue depth 16 vs drive B which sustains 20K/150K at depths 1 and 16. What number should I compare?
    – ytoledano
    Apr 14, 2012 at 7:45
  • In general queue as much as you can - sql server will do the same anyway. It is quite good at that. Mostly yo uget as much IOPS as you can - the rest is not related to drive speed. For SSD expect queue length to be small ;) That stuff is just brutally fast.
    – TomTom
    Apr 14, 2012 at 8:15
2

The benchmarks you want are IO latency for the following workloads:

  • Random reads
  • Random writes

You might also be interested in the throughput (MB/s) of these workloads:

  • Sequential reads
  • Sequential writes

For all the writes, bear in mind that caching to volatile memory before IO hits the disk can make MB/s seem higher than it really is. SSDs are as good at random IO as they are at sequential IO.

0

There is a good article on TechNet which is no longer being maintained, but which non-the-less give you an understanding of how IOPS and Disk Saturation can affect SQL Server Disk IO performance.

SQL Server Best Practices Article (TechNet)

Summary: The I/O system is important to the performance of SQL Server. When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to deploying SQL Server. This white paper discusses validating and determining the capacity of an I/O subsystem. A number of tools are available for performing this type of testing. This white paper focuses on the SQLIO.exe tool, but also compares all available tools. It also covers basic I/O configuration best practices for SQL Server 2005.

The article goes on to explain that SQL Server will read in multiples of 64 kb which corresponds to an Extent (which itself is 8 x 8pages ; page = 8 kb) Understanding Pages and Extents

SQL Server will read 64 kb of data (that's why you should format your disks with 64kb block size) and depending on the nature of the query in multiples of that: 64 kb, 128 kb, 256 kb, 512 kb and even 1024 kb (Enterprise Edition).

You would want to consider running a tool to test the various IO patterns (read/write; random/sequential; 8 kb - 1024 kb) depending on your SQL Server's edition and then comparing the results of an HDD with an SSD.

There is an old recommendation, that write latency for Transaction Log files should be between 1-5 ms (recommended 1 ms) and that read latency for Data files should be between 5-20 ms (recommended 10 ms).

Measure of disk latency. Lower values are better but this can vary and is dependent on the size and nature of the I/Os being issued. Numbers also vary across different storage configurations (cache size/utilization can impact this greatly).

On well-tuned I/O subsystems, ideal values would be:

•1–5 ms for Log (ideally 1 ms on arrays with cache)
•4–20 ms for Data on OLTP systems (ideally 10 ms or less)
•30 ms or less on DSS (decision support system) type. Latencies here can vary significantly depending on the number of simultaneous queries being issued against the system. Sustained values of more than this when the total throughput is less than expected should be investigated.

Consider these in combination with what is normal for your particular system.

Make sure to monitor disk latencies for trend analysis. The number of I/Os and latency specific to SQL Server data files can be found by using the sys.dm_io_virtual_file_stats dynamic management view in SQL Server 2005.

The article lists various counters to monitor which will provide you with a baseline, from which you can then determine the performance gains if you changes your disks from HDD to SSD.

You need a combination of:

  • Disk Reads/sec
  • Disk Writes/sec
  • Average Disk/sec Read
  • Average Disk/sec Write
  • Average Disk Bytes/Read
  • Average Disk Bytes/Write
  • Disk Read Bytes/sec
  • Disk Write Bytes/sec

Plan to monitor these values on your old HDD system and then compare them to the values on your SSD system and compare the values.

-2

The short answer is "It Depends"

Unfortunately without knowing your usage patterns it is almost impossible to suggest an "ideal" solution.

Some things to keep in mind

  1. Separate your data, logs and O/S. You can put your data and O/S on a spinning disk and put only the logs on your SSD, this will get you a boost at a lower cost. You should really do this anyway, even if you are using spinning disks, separate all the portions out onto their own volumes, RAID 10 each if you can afford it.

  2. You may be better served by adding RAM to your solution, if you can load your entire database into RAM then it's so much faster than an SSD or HDD could ever be. You can accomplish this a few different ways.

  3. If you do go down the route of SSD throughout, make sure your backups are good. Best do this anyway :)

1
  • -1. He specifically asks for HDD / SSD relevant criteria. Assuming he has the rest nailed but problems comparing storage options.
    – TomTom
    Apr 13, 2012 at 11:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .