SQL Server Performance

Load 1TB in less than 1 hour

Discussion in 'Performance-Related Article Discussions' started by gaurav_bindlish, May 28, 2006.

  1. gaurav_bindlish New Member

    http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx

    Excerpt from the article-

    BEST PRACTICES and LESSIONS LEARNED

    Here is a list of things we learned during this project that should be helpful in your projects.

    • Run as many load processes as you have available CPUs. If you have 32 CPUs, run 32 parallel loads. If you have 8 CPUs, run 8 parallel loads.
    • If you have control over the creation of your input files, make them of a size that is evenly divisible by the number of load threads you want to run in parallel. Also make sure all records belong to one partition if you want to use the switch partition strategy.
    • Use BULK insert instead of BCP if you are running the process on the SQL Server machine.
    • Use table partitioning to gain another 8-10%, but only if your input files are GUARANTEED to match your partitioning function, meaning that all records in one file must be in the same partition.
    • Use TABLOCK to avoid row at a time locking.
    • Use ROWS PER BATCH = 2500, or something near this if you are importing multiple streams into one table.


    Gaurav
    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
    http://blogs.msdn.com/gauravbi/default.aspx
  2. smy New Member

    this is a good tryout. do you think it can be done on SQL server 2000?
  3. FrankKalis Moderator

  4. smy New Member

    Its more like a blog entry.
  5. gaurav_bindlish New Member

    This is blog entry by SQL Server Customer Advisory Team which is one of the main interface to external customers of SQL Server.

    I would think that the content is as credible as any other white paper from Microsoft.

    Gaurav
    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
    http://blogs.msdn.com/gauravbi/default.aspx
  6. FrankKalis Moderator

    Thanks! That's what I wanted to know! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  7. satya Moderator

    .. now a days Blogposts are more like that, reliable and dependable [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  8. Luis Martin Moderator

    Thanks for share!.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  9. nigelrivett New Member

    >> Run as many load processes as you have available CPUs. If you have 32 CPUs, run 32 parallel loads. If you have 8 CPUs, run 8 parallel loads.

    Won't this be limited by disk performance rather than cpu?
  10. satya Moderator

  11. joechang New Member

    1TB in 50 min works out to 330MB/sec which a little higher than i have seen, but this is much bigger hardware,
    any ways, this disk load is would only slightly dent a serious disk system

    my suspicion is that it is not a simple matter formatting input data into the SQL Server table format,
    i seem to recall that Oracle lets you format data into the native table format for faster loads? or something like this.
    so the question is: does any one see the need to load data faster than 330MB/sec?
    i have seen the 64P Itanium system do a table scan at 12GB/sec

Share This Page