Post reply

What to expect from SELECT * INTO?

  • July 21, 2015 at 4:57 am

    #319121

    Hi folks,

    I'm not quite a newbie to SQL Server, but I'm new to configuration and performance optimization.

    In my opinion we have some performance issues and I want to find out, whether they are software or hardware related.

    Scenario:

    One table "mytable" with 198 columns and 805317 rows, Data space 1842 MB, no index, no keys.

    SELECT *

    INTO mytable2

    FROM mytable

    I'm executing this query a several times to get the best result possible, I think. Am I right?

    SELECT ... INTO takes about 30 seconds. So 1842 MB / 30 seconds = 61,4 MB/s.

    An Instance of SQL Server 2008 R2 runs on:

    2x Xeon 8 Core, HT enabled

    256 GB RAM

    EVA Storage with lots of Gigabytes attached via Fibrechannel (HP 82Q)

    sorry, can't remember any more details, but feel free to ask.

    Databasefiles are stored on above mentioned storage. I can copy large files to the storage, which is quite fast (about 1 GB < 3 Sec.), so I'm wondering if my SELECT ... INTO's 60 MB/s can't get any better?

    Are there any aproximate values I can refer to?

    Is there anything I can tune?

    I'm grateful for every helping answer 🙂

  • July 21, 2015 at 6:32 am

    #1813815

    There's nothing you can tune with that query. You're telling SQL to create a duplicate copy of the table, so it has to read the existing table from disk (if not already in the buffer pool), allocate the new table, update the allocation structures and system tables, process each row of the existing table, add those rows to the pages allocated for the new table, log the insert to the transaction log, repeat until all rows have been process, flush the transaction log records to the disk and then it's done.

    You can't directly correlate a select into with disk speed, it's not the same as creating a duplicate of a file.

    If you have performance problems, you need to identify the cause first. Are you bottlenecked on CPU? Is the server under memory pressure? Are there signs of IO contention? Is there severe blocking? Are the queries badly written? Is there inadequate indexing?

    Maybe start with this, the first chapter gives some useful advice on profiling a server. http://www.red-gate.com/community/books/accidental-dba

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply