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..?

  • What client tool do you use? If the client (like SSMS) have to "render" the data, that is a very good result to do that in 30 seconds but have nothing to do with the IO.
    – g.pickardou
    Mar 6 '18 at 12:25
  • Are the data files of that database moved to the SSD?
    – CodeCaster
    Mar 6 '18 at 12:30
  • SSMS. What do you mean render? I believe this is read and write operation to the data file.
    – Atomix
    Mar 6 '18 at 12:32
  • VM is all on SSD
    – Atomix
    Mar 6 '18 at 12:32
  • 1
    @JeroenMostert Your are right I overlooked the INTO
    – g.pickardou
    Mar 6 '18 at 12:37

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.

  • 1;4; I tried setting 2014 compatibily mode, added with tablock, and created clustered index with no fill factor. Now I get like ~100MB/s. Improvement. 2; Yeah we are using Standard, so that may be 3; are you talking about windows hard drives fragmentation or a cloud vendor is doing someking of fragmentation? I heard you cannot fragment ssd drives in windows... Tried looking at wait stats using brent ozars sp_blitzfirst tool. Finding PAGEIOLATCH_S but those wait times does not seem big, gonna investigate more.
    – Atomix
    Mar 7 '18 at 6:58
  • Adding wait stats results when running the query: imgur.com/OyIXXXQ and execution plan: imgur.com/BwdL4L7 .Can you see something bad?
    – Atomix
    Mar 7 '18 at 7:33
  • I was referring to index fragmentation. ALTER INDEX all ON table1 REBUILD. Mar 7 '18 at 15:33
  • Wait stats look good. query plan looks good. How many CPU's? if you SET STATISTICS IO ON; SET STATISTICS TIME ON, what does that show? Mar 7 '18 at 15:39
  • 4 CPU. imgur.com/rjrgBET
    – Atomix
    Mar 8 '18 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.