SQL Server on ssd seems slow

Question

We recently moved SQL Server to SSD and the performance gain is not that great.

SQLIO shows enormous gains (15-100 times faster), getting sequential read/write speed up to 6GB/s.

But when we do a simple query:

select * 
into table2  
from table1

it gets roughly like 60MB/s. 2GB table takes 30 seconds to finish.

Is this normal? What speed is considered average for SSD for such a simple query? Don't believe it's a RAM issue, 50GB of RAM..?

Answer 1

Problem solved, there were IO limits on that VM.

As for SQLIO, it was lieing on hyper-v:

https://social.technet.microsoft.com/Forums/azure/en-US/b8e1674d-f1c7-4e46-b1f1-09fb0d75d82e/strange-blazingfast-io-performance-inside-hyperv-vm?forum=winserverhyperv

https://serverfault.com/questions/287311/does-sqlio-lie-when-run-from-a-hyper-v-guest-on-a-vhd

Answer 2

Depending on your SQL Server version, there are various things that can impact the performance of your query.

1. SQL Server Version: SQL server 2014 allows parallel inserts into heap tables if you use the TABLOCK hint (SELECT INTO only). SQL Server 2016 expands this to allow parallel inserts into heaps or clustered columnstore indexes with INSERT SELECT
2. SQL Server Edition: Enterprise edition will issue more outstanding IO's for read-aheads than standard edition.
3. Fragmentation : Even though SSD's have no seek latency, they still do perform best with large sequential reads. High levels of fragemention will hinder the effectiveness of read-ahead reads.
4. Fill Factor: Rebuild an index with a fill factor of 5% and it will be 20x larger on disk, hence 20x more IO to read it. A freshly rebuilt index will be optimal from both a fragmentation and read-ahead perspective.

Wait statistics analysis can tell you what your current bottleneck is. If you are seeing PAGEIOLATCH_S, then reading is the issue. Rebuild the clustered index on the source table. If you aren't seeing an IO bottleneck, then I would assume that you are CPU bound. Look for 1 CPU to be at 100% (if your query is running single threaded). Solution there is to add the TABLOCK hint to try to get some parallelism.

SELECT INTO is a bulk logged operation, and won't be using TEMPDB, so no need to look into file placement for the log files or tempdb.