SQL Server enterprise edition 2012. Running on Windows 2008 R2 Enterprise - 64 bit Dual Socket Six Core Intel Xeon E5-2640 2.5GHz Hard Drives 1 -4 are 400 GB SFF SSD Drives configured in a raid 10 (raid 10 yes for SSD drives) Hard Drives 5-8 are 600GB SAS 15K RPM (6 Gb/s) Drive in a raid 5 The simulated SQL Server OLTP workload using the following parameters: • 8K transfer request size • 70% reads, 30% writes • 100% random, 0% sequential • 8K sector boundaries • 100% seek range • 60 outstanding I/Os per target • 1 worker per target The logging component of the simulated SQL Server OLTP workload using the following parameters: • 64K transfer request size • 50% reads, 50% writes • 0% random, 100% sequential • 64K sector boundaries • 100% seek range • 4 outstanding I/Os per target • 1 worker per target IO meter returns Total IO per sec SSD 36521 SAS 763 MB/sec SSD 437 SAS 10 Ave IO resp time ms SSD 1.75 SAS 73.7 Given 2 databases Database 1 data and log on the raid 10 SSD drives Database 2 data and log on the raid 5 SAS 15K RPM Using SSMS on the server A select is run against both clearing all caches prior to execution. This select is on a table with 1 million 900 thousand records and no indexes no primary keys. The select is all columns with no searchable argument. Client execution via client statistics in SSMS database 1 (SSD)averages ~15:50 milliseconds database 2 (SAS)averages ~15:43 milliseconds There is minimal difference when running the query 5 times against each. If I add a searchable argument (where id = some number) the raid 10 performs slightly better by 1 millisecond. These results are contrary to what I expected and to what i think the IO meter shows. Any ideas are welcome and thanks.
As I understand it, the real strength of SSDs is for smaller OLTP-esque random reads, rather than OLAP-style table scans. Which doesn't explain quite why things are so close in speed for the searchable argument. A few thoughts / questions: * Does the search result in a table scan anyway? What does the actual execution plan say is happening during your queries? * Are all the device drivers up to date? * Are you including the time to return / retrieve the results to SSMS, or are you ditching the results at the server side so they never reach SSMS?