question

Joe_Hell avatar image
Joe_Hell asked

SSD flaw in test plan or ?

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.
ssmsssd
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
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?
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You might want to try re-writing your test case to remove the return-to-ssms side of the calculation. See @Fatherjack's blog post on [How to make sure you see the truth with Mangement Studio][1] from 2011, and the follow-up [Don't even believe SSMS when you think it's telling the truth][2]. [1]: http://www.simple-talk.com/blogs/2011/02/23/how-to-make-sure-you-see-the-truth-with-management-studio/ [2]: http://www.simple-talk.com/blogs/2011/02/28/dont-even-believe-ssms-when-you-think-its-telling-the-truth/
2 Likes 2 ·
Flattered for the reference, thanks. If you are considering time differences as small as you suggest then you cannot be accurate without taking out the cost of SSMS.
2 Likes 2 ·
More and more interesting. If I limit the results to a select count vs select * the SSDs are up to 20X faster.
1 Like 1 ·
Yes it does a table scan. The tables are heaps. The execution plans are very straight forward. select <==table scan The drivers are our corporate std that have been tested to work in our environment. Sound like a political answer? It is, very much so. Yes the client statistics include the time to SSMS. At time high async_network_IO waits because of this.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.